Sqlserver多表查询

作者: gw

------------------------数据查询----------------------------

--(select sno ,avg(grade) as avg_grade from sc group by sno) as st2

--select student.sno from student left outer join sc on(student.sno=sc.cno);

--select student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from student left outer join sc on(student.sno=sc.sno);

--select student.sno,sname,ssex,sage,sdept,sc.cno,sc.grade from student , sc where student.sno=sc.sno;

--select student.sno ,sname,cno,grade from student ,sc where student.sno=sc.sno and sc.cno=‘2‘ and sc.grade>80

--查询每个学生的学号,姓名,选修的课程名及成绩
--select student.sno, sname ,course.cname, sc.grade from sc,student,course where student.sno=sc.sno and sc.cno=course.cno

--select sno,sname,sdept from student where sdept in( select sdept from student where sname=‘何大勇‘);

--查询每个学生大于它的平均成绩的的课程的课程名 1
--select distinct sno ,cno from sc x where grade >= (select avg(grade) from sc y where y.sno=x.sno )

-- 用查询的结果作为一个新的集合
--查询每个学生大于它的平均成绩的的课程的课程名 2
--select st1.sno,st1.cno from
-- sc st1,(select sno ,avg(grade) as avg_grade from sc group by sno) as st2
-- where st1.sno=st2.sno and st1.grade>=st2.avg_grade;

--查询每个学生的平均成绩
--select sno,avg(grade) as avg_grade from sc x group by sno;

--查询平均成绩大于80的学生的学号
--select sno,avg(grade) as avg_grade from sc x group by sno having avg(grade)>80;

--select distinct sno ,cno from sc x where x.grade>=(select avg(y.grade) from sc y where y.cno = x.cno);

--select cno,avg(grade )as m_grade from sc group by cno ;

-- 查询大于该课程平均成绩的学生的学号,成绩,课程号
--select distinct sno,grade,cno from sc x where x.grade>=(select avg(y.grade) from sc y where x.cno=y.cno );

--select sname , sage,sdept from student where sage<any (select sage from student where sdept=‘cs‘) and sdept!=‘cs‘;

--select sname , sage,sdept from student where sage>any (select sage from student where sdept=‘cs‘) and sdept!=‘cs‘;

--利用top只返回前三行
--select top 3 * from student;

--select b.sname ,a.cno , a.sno from
-- student b, (select cno,sno from sc) as a
-- where b.sno=a.sno;

--创建系部数据库
--create table dept(dno int ,dname varchar(20),dmoster varchar(10),primary key(dno));

--drop table dept;

--insert into table dept (dno,dmoster) values(10299,‘113‘);

--select * from student;

--sname,cno from student,sc
--where not exists (select * from sc where sno=student.sno and cno=‘1‘)and sc.sno=student.sno

--臃肿的查询语句
--select distinct sname from student,sc
--where exists (select * from sc b where sno=student.sno and b.cno=‘1‘)and sc.sno=student.sno

--select sname from student
--where exists (select * from sc b where sno=student.sno and b.cno=‘1‘)

--下面这句话是直接做一个笛卡尔积
--select sname,cno from student,sc

--查询和高渐离在一个系的其他学生 1
--select sno,sname,sdept from student s1 where exists
--(select * from student s2 where s2.sdept=s1.sdept and s2.sname=‘高渐离‘) and s1.sname!=‘高渐离‘

--select* from student ;

-- 查询和高渐离在一个系的其他学生 2
--select sno,sname,sdept from student where sdept in(select sdept from student s2 where s2.sname=‘高渐离‘) and sname !=‘高渐离‘

-- 查询和高渐离在一个系的其他学生 3
--select sno,sname,sdept from student where sdept=(select sdept from student s2 where s2.sname=‘高渐离‘) and sname !=‘高渐离‘

--select sname from student
-- where not exists (select * from course
-- where not exists (select * from sc
-- where sno=student.sno and cno=course.cno))

--
--
--
--

-- 一直不理解exists这个语句 下面是从百度知道上搜索出来的
--EXISTS或者NOT EXISTS是把主查询的字段传到后边的查询中作为条件，返回值是TRUE或者FALSE。
--EXISTS TRUE，那么就是查询条件成立，结果会显示出来。
--NOT EXISTS TRUE，则为FALSE，查询连接条件不成立。
--select * from course where not exists(select * from grade where grade.课程代号=course.课程代号)
--这个语句，是查询course表中课程代号在grade中没有出现的数据。
--看看grade表，课程编号有01到06，而COURSE表，有01到07，那么07在GRADE表是不存在的,那么,是符合条件的。
--同样select * from course where exists(select * from grade where grade.课程代号=course.课程代号)
--则是查询COURSE的记录条件为编号在GRADE中存在。那么很明显，结果是K01到K06的数据。
--另外，EXISTS和NOT EXISTS的作用可以用IN或NOT IN实现，但是exists效率要高。
-- 因为EXISTS和NOT EXISTS返回的结果是TRUE或者FALSE，那么则在子查询中，遇到第一个符合条件的结果，
--就会退出查询，而不会进行全表的检索。
--而NOT IN或者IN，要把子查询中的SELECT字句全部查询出来才行。

--子查询中，遇到第一个符合条件的结果，就会退出查询，而不会进行全表的检索。

--select * from sc where sno in(‘2014003‘);

--select sno from sc s1 where cno =all (select * from sc where sno in(‘2014003‘));

/*
例1：查询所有选修了1号课程的学生的姓名。
解法1：利用exists
首先取Student表中的一个元组，然后在SC表中依次找SC.Sno=该元组的Sno，并且对应的Cno=‘1‘，
如果存在，则外层查询的where子句返回为真，则Student表中的该元组可以输出。然后依次遍历
Student表中的其他元组。
举个例子：对于在学生表中学号等于2002151121这个元组，在SC表中第一条记录即符合条件，
然后where 子句返回 true，所以该条元组可以输出。然后依次遍历。
select Sname
from Student
where exists
(
select *
from SC
where Sno = Student.Sno AND Cno=‘1‘
);
解法2：利用连接查询
select Sname
from Student,SC
where Student.Sno=SC.Sno AND SC.Cno=‘1‘;

例2：查询没有选修1号课程的学生的姓名。
解：
select Sname
from Student
where not exists
(
select *
from SC
where Sno=Student.Sno AND Cno=‘1‘
);

例3：查询选修了全部课程的学生姓名。
select Sname
from Student
where not exists
(
select *
from Course
where not exists
(
select *
from SC
where Sno=Student.Sno AND
Cno=Course.Cno
) );

--!!!在exists(not exists) 子句中 如果有多条记录 依次对每条记录进行判断
--!!!某条记录满足条件就返回 true 剩下的就不用查询了 在结合外层的exists(not exists)
--!!!如果外层为exists 该条记录被选中 如果外层为not exists ,not true该条记录被舍弃

查找语义：查询这样的学生，没有一门课程是他不选修的。
查找过程：
首先，选取Student表中的一个元组，然后在依次判断Course表中的每个元组是否可以输出，只要有
一个课程可以输出，则最外层查询的where子句返回为false；而在判断某个课程是否可以输出时，
则要利用第三层查询，利用当前的学号和当前的课程号，在SC表中查询，如果存在，则第二层查询
where子句返回false。至此，每一门课程都不可以输出时，这个学号对应的元组才可以输出。表示
这个学生选修了全部的课程。

例4：至少选修了学生2014006选修的全部课程的学生号码。
--
select distinct Sno
from SC SCX
where not exists
( select *
from SC SCY
where SCY.Sno=‘2014006‘ AND
not exists
( select *
from SC SCZ
where SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno));
*/

/*
select distinct sno from sc s1
where not exists
(select * from sc s2 where s2.sno=‘2014006‘and not exists
(select * from sc s3 where s1.sno=s3.sno and s2.cno=s3.cno)
)

*/

/*
查询语义：不存在这样的课程y，学生2014006选修了y，而学生x没选。
查询过程：先在SCX表中选一条记录，比方说第一条，然后再看SCY表中，只有SCY表中全部不能
输出，第一层查询的where子句才返回true，第一条记录就可以输出；所以就要一次查看SCY表
中的每一个元组，前三个，因为学号首先不满足=200215122所以必然不能输出，第四个的话，
就要看其AND后面的not exists返回什么值，而这又取决于第三层查询中是否存在满足学号
等于SCX.Sno且课程号=SCY.Cno的元组，经查看，有 ，则返回false，所以第四个也不能输
出，第五个类似，所以，第一层查询的not exists返回true。所以第一条记录可以输出。
*/

/*
--如何判断两个集合 是否 A包含B
--依次判断B的每一个元素是否在A中存在
--B中没有一个元素 不在A中存在
select * from K where not exists
(select * from B where not exists
(select * from A where A.filed=b.filed and K...) )

*/

--集合的并操作可以用or来代替
--select distinct sno from sc where cno=‘4‘ or cno=‘5‘

--select sno from sc where cno=‘4‘ union select sno from sc where cno=‘5‘

--集合的交操作可以用and来代替
--select * from student where sage>=22 and sdept=‘cs‘

--集合的差操作可以用not in来代替 not in 后面是要除去的集合
--select * from student where sage>=22 and sdept not in
-- (select sdept from student s1 where sdept=‘cs‘)

--查询计算机系 所有学生中除去年龄大于21岁的
--select * from student s1 where sdept=‘cs‘ and sage not in
-- (select sage from student s2
-- where s2.sage>=22 and s1.sno=s2.sno )