1、专题：SQL高级查询,除法SQL实现及其它,sno sname 1 张三 2 李四 3 王五 4 马六,表S,表C,cno cname 1 数据库 2 离散数学 3 操作系统,表SC,sno cno grade 1 1 78 1 2 87 2 1 66 3 2 80 3 3 56 4 3 68 4 1 90 1 3 77 4 2 81 2 2 83,select * from sc order by sno,sno cno grade 1 1 78 1 2 87 1 3 77 2 1 66 2 2 83 3 2 80 3 3 56 4 1 90 4 2 81 4 3 68,select * f
2、rom sc order by cno,sno cno grade 1 1 78 2 1 66 4 1 90 1 2 87 3 2 80 4 2 81 2 2 83 3 3 56 4 3 68 1 3 77,查询选修了全部课程的学生号码,关系代数表达式：Sno，Cno（SC）Cno（C） 结果应该是（1, 4）两个学号 SQL表达式： Select sno from sc where cno in(select cno from c) (1, 2, 3) 这样对吗？显然不对！因为结果只是选修了某一门或某几门课程的学生学号，而不是选修了全部课程的学生学号！,查询选修了全部课程的学生号码,关系代数
3、表达式：Sno，Cno（SC）Cno（C） 怎么办？说起来也简单，既然是学了全部课程，那么全部课程有几门？ Select count(*) from c 得到全部课程数3 再按学号分组统计每个学号对应的修课数目： Select sno,count(*) from sc group by sno,查询选修了全部课程的学生号码,Select sno,count(*) from sc group by sno 结果：,sno count(*) 1 3 2 2 3 2 全部课程数是3 4 3,Select sno from sc group by sno having count(*) = （Sele
4、ct count(*) from c）,查询被全部学生选修的课程号码,关系代数表达式：Cno，Sno（SC）sno（S） Select count(*) from S 得到全部学生数4 再按课号分组统计每个课号对应的修课人数： Select cno,count(*) from sc group by cno,查询被全部学生选修的课程号码,Select cno,count(*) from sc group by cno 结果：,cno count(*) 1 3 2 4 全部学生数是4 3 3,Select cno from sc group by cno having count(*) = （S
5、elect count(*) from S）,麻烦的是类似这样的查询要求：查询至少选修了2号学生所学的全部课程的学生学号 同样我们不能这样做： select sno from sc where cno in (select cno from sc where sno=2) 我们需要EXISTS来帮忙！,10,exists是用来判断是否存在的，当exists(查询)中的查询存在结果时则返回真，否则返回假。not exists则相反 它所在的查询属于相关子查询，即子查询的查询条件依赖于外层父查询的某个属性值，处理过程一般为： 取外层查询的第一个元组，根据它与内层查询相关的属性值处理内层查询，若wh
6、ere子句返回true，则将此元组放入结果表，然后取下一个元组，重复这个过程直到全部检查完为止,11,即：exists做为where 条件时，是先对where 前的主查询进行查询，然后用主查询的结果一个一个的代入exists的子查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出,12,使用EXISTS，首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。 而执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。 因此，使用EXISTS比使用IN通常查
7、询速度快。,13,查询至少选修了学号是2号的学生所选修的全部课程的学生号码,它表示的语义为：不存在这样的课程，学号为2号的学生选了，而学号为h的学生却没有选 Not exists (select * from sc as y where sno=2 and not exists(select * from sc where sno=h and cno=o) 注意，千万不能写成下面这样！ Not exists (select * from sc as y where sno=2 and exists(select * from sc where sno=h and cno!=o),14,理解了上
8、式，完整的SQL查询为：,select distinct sno from sc as x where not exists(select * from sc as y where sno=2 and not exists(select * from sc where sno=x.sno and cno=o),sno 1 2 4,表SC,select * from sc as y where sno=2,sno cno grade 2 1 66 2 2 83,sno cno grade 1 1 78 1 2 87 1 3 77 2 1 66 2 2 83 3 2 80 3 3 56 4 1 9
9、0 4 2 81 4 3 68,select distinct sno from sc as x where not exists(select * from sc as y where sno=2 and not exists(select * from sc where sno=x.sno and cno=o),真难理解啊！好理解的解法还有木有？,有！请看以下查询： select * from sc as x ,(select * from sc where sno=2) as y where o=o,sno cno grade 1 1 78 1 2 87 2 1 66 3 2 80 3
10、3 56 4 3 68 4 1 90 1 3 77 4 2 81 2 2 83,sno cno grade 2 1 66 2 2 83,sno cno grade sno_1 cno_1 grade_1 1 1 78 2 1 66 1 2 87 2 2 83 2 1 66 2 1 66 2 2 83 2 2 83 3 2 80 2 2 83 4 1 90 2 1 66 4 2 81 2 2 83,X,Y,查询结果：,根据分析，改写原SQL语句：,select * from sc as x ,(select * from sc where sno=2) as y where o=o完整表达式如下
11、： Select x.sno from sc as x ,(select * from sc where sno=2) as y where o=o group by x.sno having count(*)=(select count(*) from sc where sno=2),最牛最简单的除法!,Select * from s where not exists (Select cno from sc where sno=2 Except Select cno from sc where sno=s.sno),每次子查询代入父查询S表的一个学号，若差集为空，不就表示s.sno这个学号的
12、学生至少学了2号学生所学的全部课程了吗？ not exists （空） 返回true，那么父查询就显示该学生！否则就不显示！不管显示与否，继续代入s表的下一个学号，直到处理完s的所有记录，整个查询结束。,What! 还不理解？,暂时算了，发给你慢慢琢磨吧！,查询每一门课程及其先修课程，查询结果中显示课程号、课程名和先修课程名,由于SQL是按行处理数据的，所以在单个course（cno，cname，cpno）表中只能得到课程号、课程名和先修课程号（而不是先修课程名）。为了得到先修课程名，必须将先修课程号转换成先修课程名，而先修课程号作为一门课程，它只不过是course表中的另外一条记录，需要两个
13、course表（别名分别取A、B）做一个等值连接。等值条件是什么？,0000001 DB_Design 0000006 0000002 C语言基础 0000027 0000003 UNIX 0000013 0000004 C#程序设计 0000002 0000005 现代物流概论 NULL 0000006 数据库原理 0000010 0000007 JAVA程序设计 0000002 0000008 电子商务 0000027 0000009 实用英语 NULL 0000010 数据结构 0000002 0000011 邓小平理论 NULL 0000012 体育 NULL 0000013 操作系统
14、 0000002 0000014 经济基础知识 NULL 0000027 计算机基础 NULL 0000032 多媒体技术 0000027 0000034 高等数学 NULL 0000039 基础会计 NULL 0000045 软件工程 0000010 0000052 财务会计 0000039,0000001 DB_Design 0000006 0000002 C语言基础 0000027 0000003 UNIX 0000013 0000004 C#程序设计 0000002 0000005 现代物流概论 NULL 0000006 数据库原理 0000010 0000007 JAVA程序设计 0
15、000002 0000008 电子商务 0000027 0000009 实用英语 NULL 0000010 数据结构 0000002 0000011 邓小平理论 NULL 0000012 体育 NULL 0000013 操作系统 0000002 0000014 经济基础知识 NULL 0000027 计算机基础 NULL 0000032 多媒体技术 0000027 0000034 高等数学 NULL 0000039 基础会计 NULL 0000045 软件工程 0000010 0000052 财务会计 0000039,A(course) cno cname cpno ,B(course) cn
16、o cname cpno ,Select A.cno,A.cname,B.cname from sc A,sc B where A.cpno=B.cno,查询和“刘涛”在一个班级的学生的信息,很简单的查询，关键在于熟悉表的结构以及表间关系！首先，刘涛在哪个班级呢？ select class from students where sname=刘涛 刘涛所在的班级名字不就知道了，一个或几个字符串而已。接下来一步搞定： select * from students where class in （ ） 或自连接（单独一个students表不行！） select s1.* from students
17、 s1,students s2where s1.class= s2.class and s2.sname=刘涛,查询选修了计算机基础课的学生的学号、姓名,1、连接方式 select students.sno,sname from students,sc,course where students.sno=sc.sno and o=o and cname=计算机基础 2、子查询嵌套方式 select sno,sname from students where sno in ( select sno from sc where cno in ( select cno from course whe
18、re cname=计算机基础),查询没有选修课程的学生的信息,1、子查询in方式 select * from students where sno not in (select distinct sno from sc) 2、子查询exists方式 select * from students where not exists (select * from sc where students.sno=sc.sno),查询每个学生超过该门课程平均成绩的学号,课号,根据查询要求，分析此查询只涉及sc表 term sno cno grade point 1 0301101 0000011 88 1
19、1 0301102 0000011 75 1.5 1 0301102 0000027 79 1.5 1 0311101 0000008 86 1 1 0311101 0000009 58 0 1 0311101 0000011 85 1 1 0311101 0000027 87 1,查询每个学生超过该门课程平均成绩的学号,课号,根据cno分组，求每门课的平均成绩 Select cno,avg(grade) from sc group by cno,cno avg(grade) 0000001 82.5 0000002 95.0 0000006 74.0 0000007 87.0 0000008
20、 74.625 0000009 69.57 0000010 72.0 0000011 80.0 0000013 86.0 0000027 81.57 0000034 75.625 0000039 71.75 0000052 75.5,term sno cno grade point 1 0301101 0000011 88 1 1 0301102 0000011 75 1.5 1 0301102 0000027 79 1.5 1 0311101 0000008 86 1 1 0311101 0000009 58 0 1 0311101 0000011 85 10311101 0000027 8
21、7 1 sc表 比较条件：cno相等，gradeavg(grade),查询每个学生超过该门课程平均成绩的学号,课号,select sno, cno from sc as sc1 where grade (select avg(grade) avgrade from sc group by cno having o=cno),查询每个学生超过该门课程平均成绩的学号,课号,select sno, cno from sc as sc1 where grade (select avg(grade) avgrade from sc group by cno having o=cno),查询既选修了“计算
22、机基础”又选修了“C语言基础”的学生的学号,方法1：自连接 select sc1.sno from sc sc1,sc sc2 where sc1.sno=sc2.sno and o=(select cno from course where cname=计算机基础)and o=(select cno from course where cname=C语言基础),查询既选修了“计算机基础”又选修了“C语言基础”的学生的学号,方法二：求交集 select sno from sc,course where o=o and cname=计算机基础 intersect select sno from sc,course where o=o and cname=C语言基础,查询既选修了“计算机基础”又选修了“C语言基础”的学生的学号,方法三：子查询 select sno from sc,course where o=o and cname= 计算机基础 and sno in (select sno from sc,course where o=o and cname=C语言基础),
