《查询语句连接嵌套》由会员分享，可在线阅读，更多相关《查询语句连接嵌套（31页珍藏版）》请在金锄头文库上搜索。
1、第二章第二章 SQLSQL概述概述SQL数据定义功能数据定义功能SQL数据查询功能数据查询功能SQL数据修改功能数据修改功能SQL数据控制功能数据控制功能嵌入式嵌入式SQL1连接查询连接查询-等值与非等值查询等值与非等值查询 若一个查询涉及两个以上的表，则称为连接查询。连接查询包括等值连接、自然连接、非等值连接、自身连接、外连接查询和符合条件连接查询。一、等值与非等值查询 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . 当连接运算符为时，称为等值连接。使用其他运算符称为非等值连接。 连接谓词中的列名称为连接字段 例32 查询每个学生及其选修课程的情况 学生情况存于ST
2、UDENT表中，选修课程的情况存于SC表中， 这两个表的联系通过公共属性SNO实现的。2连接查询连接查询-等值与非等值查询等值与非等值查询 SELECT STUDENT.* ,SC.* FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO 如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀查询每个学生的选课情况，要求显示学生的学号，姓名，课程编号；查询每个学生的选课情况，要求显示学生的学号，姓名，课程名称，课程学分，成绩；统计每个学生的选课数目，要求显示学生的姓名，选课数目统计每门课程的选课人数，要求显示课程的名称，选课人数，结果按照人数的升序显示3连接查询连
3、接查询-自身连接自身连接连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接 例34 查询与李勇在同一个系学习的学生的详细资料 select b.*from student a,student bwhere a.Sdept=b.Sdept and a.Sname=李勇 4连接查询连接查询-自身连接自身连接A表5关于自身连接的思考（关于自身连接的思考（1）例：查询至少选修了两门课的学生学号观察下面代码的运行结果 select distinct a.snofrom sc a,sc bwhere a.sno=b.sno and o!=oselect a.snofrom sc a,sc bw
4、here a.sno=b.sno and o!=o6关于自身连接的思考（关于自身连接的思考（2）例：既选修了C01又选修C02课程的学生学号（用自身连接）既选修了C01又选修C02课程的学生学号,姓名（用自身连接） select a.snofrom sc a,sc bwhere a.sno=b.sno and o=c01 and o=c02select a.sno,student.snamefrom sc a,sc b,studentwhere a.sno=b.sno and o=c01 and o=c02 and a.sno=student.sno7WERE子句可以有多个连接条件，称为复合条
5、件连接例35 查询选修了2号课程，且成绩在90分以上的学生学号和姓名例36 查询年龄比李勇大的学生的学号，姓名，年龄 连接查询连接查询-复合条件连接复合条件连接select student.sno,snamefrom student,scwhere student.sno=sc.sno and o=c02 and score90select b.sno,b.sname,b.sagefrom student a,student bwhere a.sage3 ORDER BY COUNT_PNO,SUM_QTY DESC;13期中考中的部分题目期中考中的部分题目 查询名称中含有4个字符以上，且倒数
6、第3个字符是d，倒数第2个字符是_的课程的所有信息查询每门课都及格的学生的学号，平均成绩 select * from course where cname like %_d_ _escapeselect sno,avg(score)from scwhere score =60group by snoselect sno,avg(score)from scgroup by snohaving min(score)=6014嵌套查询嵌套查询集合成员资格集合成员资格select Sname from Student where sno in(select sno from sc where cno=
7、c01)集合之间的比较集合之间的比较集合基数的测试集合基数的测试l测试集合是否为空l测试集合是否存在重复元组 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询15集合成员资格（集合成员资格（）in 子查询子查询表达式表达式 not in （子查询）（子查询）判断表达式的值是否在子查询的结果中。示例示例l选修了C01号课程的学生的学号及姓名。 select SNO，SNAME from S where SNO in (select SNO from SC where CNO = C
8、01 ）16集合成员资格（集合成员资格（ ）l查询与“刘晨”在同一个系学习的学生。 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,Sdeptfrom Studentwhere Sdept IN (s
9、elect Sdept from Student where Sname =刘晨)18l列出选修了C01号和C02号课程的学生的学号。 select SNO from SC where SC.CNO =C01 and SNO in （select SNO from SC where CNO = C02）查询没有选修任何课程的学生的学号，姓名在选课名单中查询没有选修c01号课程的学生的学号，姓名在选课名单中查询没有选修c01，也没有选修c02课程的学生学号，姓名集合成员资格（集合成员资格（IVIV ）19带有比较运算符的子查询带有比较运算符的子查询当用户确切知道内层查询返回的是单值，可以当用户确
10、切知道内层查询返回的是单值，可以用用,=,=,!=,等比较运算符等比较运算符l查询与刘晨在同一个系学习的学生select Sno,Sname,Sdeptfrom Studentwhere Sdept = (select Sdept from Student where Sname =刘晨) 注意：子查询要写在比较符之后 20集合之间的比较（集合之间的比较（）some/any/all子查询子查询l表达式表达式 比较运算符比较运算符 any （子查询）（子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 。l表达式表达式 比较运算符比较运算符 all （子查询）（子查询） 表达式的值
11、与子查询结果中的所有的值相比都满足比较运算符。21集合之间的比较（集合之间的比较（）any 大于子查询结果中的某个值all大于子查询结果中的所有值any 小于子查询中的某个值all小于子查询中的所有值=any等于子查询中的某个值=all等于子查询中的任意一个值!=any不等于子查询中的某个值!=all不等于子查询中的任意一个值22集合之间的比较（集合之间的比较（ ）l查询其他系中比信息系某个学生年龄小的学生姓名年龄等价于：select Sname,Sagefrom studentwhere Sage any(select Sage from student where Sdept=is) an
12、d Sdeptisselect Sname,Sagefrom studentwhere Sage (select max(Sage) from student where Sdept=is) and Sdeptis23集合之间的比较（集合之间的比较（ IVIV ）l查询其他系中比信息系中所有学生年龄都小的学生姓名，年龄l等价于：select Sname,Sagefrom studentwhere Sage all (select Sage from student where Sdept=is) and Sdeptisselect Sname,Sagefrom studentwhere Sag
13、e (select min(Sage) from student where Sdept=is) and Sdeptis24集合之间的比较（集合之间的比较（ V V ）示例示例l找出平均成绩最高的学生号。 select SNO from SC group by SNO having avg(SCORE) = all（select avg(SCORE) from SC group by SNO）思考：查询每个系年龄最小的学生的学号，姓名，年龄，系名25集合之间的比较（集合之间的比较（ VIVI ）事实上，用集函数实现子查询通常比直接用事实上，用集函数实现子查询通常比直接用ANY或者或者ALL查询
14、效率要高查询效率要高 26集合基数的测试（集合基数的测试（1）测试集合是否为空测试集合是否为空not exists （子查询）（子查询）判断子查询的结果集合中是否有任何元组存在。l列出选修了c01号课程的学生的学号及姓名。select Snamefrom studentwhere exists ( select * from sc where sno=student.sno and cno =C01)27集合基数的测试（集合基数的测试（2）l使用存在量词exists后，若内层查询结果非空，则外层的where子句返回真值，否则返回假值。l由exists引出的子查询，目标表达式一般都是*，因为带e
15、xists的子查询只返回真值或假值l理解带有exists的子查询的处理过程。28集合基数的测试（集合基数的测试（3）l查询没有选修c01号课程的学生姓名l由于带有exists量词的相关子查询只关心内层查询是否有返回值，并不需要具体值，因此其效率并不一定低于不相关子查询，有时是高效的方法select Snamefrom studentwhere not exists ( select * from sc where sno=student.sno and cno =C01)29集合基数的测试（集合基数的测试（4）l列出选修了001号和002号课程的学生的学号。l思考：查询选修了全部课程的学生姓名
16、 select SNOfrom SC SC1where SC1.CNO = c01 and exists (select SNOfrom SC SC2 where SC2. CNO = c02 and SC2.SNO = SC1.SNO)30l列出至少选修了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
