查询语句连接,嵌套

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第二章 SQL,SQL概述,SQL数据定义功能,SQL数据查询功能,SQL数据修改功能,SQL数据控制功能,嵌入式SQL,1,连接查询-等值与非等值查询,若一个查询涉及两个以上的表，则称为连接查询。连接查询包括等值连接、自然连接、非等值连接、自身连接、外连接查询和符合条件连接查询。,一、等值与非等值查询,连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：,. ,当连接运算符为时，称为等值连接。使用其他运算符称为非等值连接。,连接谓词中的列名称为连接字段,例32 查询每个学生及其选修课程的情况,学生情况存于,STUDENT,表中，选修课程的情况存于,SC,表中，,这两个表的联系通过公共属性,SNO,实现的。,2,连接查询-等值与非等值查询,SELECT STUDENT.* ,SC.*,FROM STUDENT,SC,WHERE STUDENT.SNO=SC.SNO,如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀,查询每个学生的选课情况，要求显示学生的学号，姓名，课程编号；,查询每个学生的选课情况，要求显示学生的学号，姓名，课程名称，课程学分，成绩；,统计每个学生的选课数目，要求显示学生的姓名，选课数目,统计每门课程的选课人数，要求显示课程的名称，选课人数，结果按照人数的升序显示,3,连接查询-自身连接,连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接,例34 查询与李勇在同一个系学习的学生的详细资料,select b.*,from student a,student b,where a.Sdept=b.Sdept and a.Sname=李勇,4,连接查询-自身连接,A表,5,关于自身连接的思考（1）,例：查询至少选修了两门课的学生学号,观察下面代码的运行结果,select distinct a.sno,from sc a,sc b,where a.sno=b.sno and o!=o,select a.sno,from sc a,sc b,where a.sno=b.sno and o!=o,6,关于自身连接的思考（2）,例：既选修了C01又选修C02课程的学生学号（用自身连接）,既选修了C01又选修C02课程的学生学号,姓名（用自身连接）,select a.sno,from sc a,sc b,where a.sno=b.sno and o=c01 and o=c02,select a.sno,student.sname,from sc a,sc b,student,where a.sno=b.sno and o=c01 and o=c02 and,a.sno=student.sno,7,WERE,子句可以有多个连接条件，称为复合条件连接,例35 查询选修了2号课程，且成绩在90分以上的学生学号和姓名,例36 查询年龄比李勇大的学生的学号，姓名，年龄,连接查询-复合条件连接,select student.sno,sname,from student,sc,where student.sno=sc.sno and o=c02,and score90,select b.sno,b.sname,b.sage,from student a,student b,where a.sage3,ORDER BY COUNT_PNO,SUM_QTY DESC;,13,期中考中的部分题目,查询名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的课程的所有信息,查询每门课都及格的学生的学号，平均成绩,select *,from course,where cname like %_d_ _escape,select sno,avg(score),from sc,where score =60,group by sno,select sno,avg(score),from sc,group by sno,having min(score)=60,14,嵌套查询,集合成员资格,select,Sname,from Student where,sno,in,(select,sno,from sc where,cno,=c01),集合之间的比较,集合基数的测试,测试集合是否为空,测试集合是否存在重复元组,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,15,集合成员资格（）,in 子查询,表达式 ,not,in,（子查询）,判断表达式的值是否在子查询的结果中。,示例,选修了C01号课程的学生的学号及姓名。,select,SNO，SNAME,from,S,where,SNO,in,(,select,SNO,from,SC,where,CNO = C01 ）,16,集合成员资格（ ）,查询与“刘晨”在同一个系学习的学生。,1、,确定刘晨所在的系名,select Sdept,from Student,where Sname =刘晨,2、查找所有在IS系学习的学生,select Sno,Sname,Sdept,from student,where sdept= is ,Sdept,-,is,Sno Sname Sdept,- - -,2003002 刘晨 IS,2003004 张立 IS,17,集合成员资格（,）,构造嵌套后：,select Sno,Sname,Sdept,from Student,where Sdept IN,(select Sdept,from Student,where Sname =刘晨),18,列出选修了C01号和C02号课程的学生的学号。,select,SNO,from,SC,where,SC.CNO =C01,and,SNO,in,（,select,SNO,from,SC,where,CNO = C02）,查询没有选修任何课程的学生的学号，姓名,在选课名单中查询没有选修c01号课程的学生的学号，姓名,在选课名单中查询没有选修c01，也没有选修c02课程的学生学号，姓名,集合成员资格（,IV,）,19,带有比较运算符的子查询,当用户确切知道内层查询返回的是单值，可以用,=,等比较运算符,查询与刘晨在同一个系学习的学生,select,Sno,Sname,Sdept,from Student,where Sdept =,(select,Sdept,from Student,where,Sname,=,刘晨),注意：子查询要写在比较符之后,20,集合之间的比较（）,some/any/all子查询,表达式 比较运算符,any,（子查询）,表达式的值至少与子查询结果中的一个值相比满足比较运算符,。,表达式 比较运算符,all,（子查询）,表达式的值与子查询结果中的所有的值相比都满足比较运算符,。,21,集合之间的比较（）,any 大于子查询结果中的某个值,all大于子查询结果中的所有值,any 小于子查询中的某个值,all小于子查询中的所有值,=any等于子查询中的某个值,=all等于子查询中的任意一个值,!=any不等于子查询中的某个值,!=all不等于子查询中的任意一个值,22,集合之间的比较（,）,查询其他系中比信息系某个学生年龄小的学生姓名年龄,等价于：,select Sname,Sage,from student,where Sage any(select Sage,from student,where Sdept=is),and Sdeptis,select Sname,Sage,from student,where Sage (select max(Sage),from student,where Sdept=is) and Sdeptis,23,集合之间的比较（,IV,）,查询其他系中比信息系中所有学生年龄都小的学生姓名，年龄,等价于：,select Sname,Sage,from student,where Sage all (select Sage,from student,where Sdept=is),and Sdeptis,select Sname,Sage,from student,where Sage =,all,（,select,avg,(SCORE),from,SC,group by,SNO）,思考：查询每个系年龄最小的学生的学号，姓名，年龄，系名,25,集合之间的比较（,VI,）,事实上，用集函数实现子查询通常比直接用ANY或者ALL查询效率要高,26,集合基数的测试（1）,测试集合是否为空,not,exists,（子查询）,判断子查询的结果集合中是否有任何元组存在。,列出选修了c01号课程的学生的学号及姓名。,select Sname,from student,where exists (,select *,from sc,where sno=student.sno and cno =C01),27,集合基数的测试（2）,使用存在量词,exists,后，若内层查询结果非空，则外层的,where,子句返回真值，否则返回假值。,由,exists,引出的子查询，目标表达式一般都是*，因为带,exists,的子查询只返回真值或假值,理解带有,exists,的子查询的处理过程。,28,集合基数的测试（3）,查询没有选修,c01,号课程的学生姓名,由于带有,exists,量词的相关子查询只关心内层查询是否有返回值，并不需要具体值，因此其效率并不一定低于不相关子查询，有时是高效的方法,select Sname,from student,where not exists (,select *,from sc,where sno=student.sno and cno =C01),29,集合基数的测试（4）,列出选修了001号和002号课程的学生的学号。,思考：查询选修了全部课程的学生姓名,select SNO,from SC SC1,where SC1.CNO = c01 and exists,(select SNO,from SC SC2,where SC2. CNO = c02,and SC2.SNO = SC1.SNO),30,列出至少选修了2003001号学生选修的所有课程的学生名。,select,SNAME,from,Student as S,where,not exists,（,select,CNO,from,COURSE,where,exists,（,select,*,from,SC,where,SC.CNO = COURSE.CNO,and,SC.SNO =2003001）,and not exists,（,select,*,from,SC,where,SC.CNO = COURSE.CNO,and,SC.SNO = S.SNO）,),任意课程，001号学生选之，所求学生选之。,不存在任何一门课程，001号学生选之，所求学生没有选之。,31,