查询语句连接,嵌套课件

第二章SQLSQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能嵌入式SQL连接查询-等值与非等值查询

若一个查询涉及两个以上的表，则称为连接查询。连接查询包括等值连接、自然连接、非等值连接、自身连接、外连接查询和符合条件连接查询。一、等值与非等值查询连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：[<表名1>.]<列名><比较运算符>[<表名2.>]<列名2>当连接运算符为＝时，称为等值连接。使用其他运算符称为非等值连接。连接谓词中的列名称为连接字段例32查询每个学生及其选修课程的情况学生情况存于STUDENT表中，选修课程的情况存于SC表中，这两个表的联系通过公共属性SNO实现的。连接查询-等值与非等值查询

SELECTSTUDENT.*,SC.*FROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO

如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀查询每个学生的选课情况，要求显示学生的学号，姓名，课程编号；查询每个学生的选课情况，要求显示学生的学号，姓名，课程名称，课程学分，成绩；统计每个学生的选课数目，要求显示学生的姓名，选课数目统计每门课程的选课人数，要求显示课程的名称，选课人数，结果按照人数的升序显示连接查询-自身连接A表李勇男18CS刘晨男20MA王敏女21CS张瑶涵女17IS刘晨女19IS李勇男18CS刘晨男20MA王敏女21CS张瑶涵女17IS刘晨女19IS关于自身连接的思考（1）例：查询至少选修了两门课的学生学号观察下面代码的运行结果

selectdistincta.snofromsca,scbwherea.sno=o!=oselecta.snofromsca,scbwherea.sno=o!=o关于自身连接的思考（2）例：既选修了C01又选修C02课程的学生学号（用自身连接）既选修了C01又选修C02课程的学生学号,姓名（用自身连接）selecta.snofromsca,scbwherea.sno=o='c01'o='c02'selecta.sno,student.snamefromsca,scb,studentwherea.sno=o='c01'o='c02'anda.sno=student.sno连接查询-复合条件连接连接操作除了可以是两表连接，自身连接，还可以是两张表以上的连接，后者称为多表连接例37查询每个学生的学号、姓名、选修的课程名及成绩例38查询选修了课程名为“信息系统”的学生学号和姓名

selectstudent.sno,sname,cname,scorefromstudent,sc,coursewherestudent.sno=o=oselectstudent.sno,snamefromstudent,sc,coursewherestudent.sno=o=ame='信息系统'连接查询-外连接

观察如下语句运行结果的不同：selectstudent.sno,cno,scorefromstudent,scwherestudent.sno=sc.snoselectstudent.sno,cno,scorefromstudent,scwherestudent.sno*=sc.sno第二个语句称为外连接，表示方法为：在连接谓词的某一边加符号*。*所在边的表中的记录全部显示连接查询-外连接观察如下代码的运行结果：selectsc.sno,cname,scorefromsc,o=oselectsc.sno,cname,scorefromsc,o=*o关于连接查询的思考（2）在SPJ数据库中：统计天津地区的项目使用零件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。SELECTSPJ.JNO,COUNT(DISTINCTSPJ.PNO)ASCOUNT_PNO,SUM(QTY)ASSUM_QTYFROMJ,SPJWHEREJ.CITY=’天津’ANDJ.JNO=SPJ.JNOGROUPBYSPJ.JNOHAVINGCOUNT(DISTINCTPNO)>3ORDERBYCOUNT_PNO,SUM_QTYDESC;期中考中的部分题目查询名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的课程的所有信息查询每门课都及格的学生的学号，平均成绩

select*fromcoursewherecnamelike'%_d\__'escape'\'selectsno,avg(score)fromscwherescore>=60groupbysnoselectsno,avg(score)fromscgroupbysnohavingmin(score)>=60嵌套查询集合成员资格selectSnamefromStudentwheresnoin(selectsnofromscwherecno=‘c01’)集合之间的比较集合基数的测试测试集合是否为空测试集合是否存在重复元组

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询集合成员资格（Ⅱ）查询与“刘晨”在同一个系学习的学生。

1、确定刘晨所在的系名

selectSdeptfromStudentwhereSname='刘晨'2、查找所有在IS系学习的学生selectSno,Sname,Sdeptfromstudentwheresdept='is'Sdept----isSnoSnameSdept------------------------------------2003002刘晨IS2003004张立IS集合成员资格（Ⅲ）构造嵌套后：selectSno,Sname,SdeptfromStudentwhereSdeptIN(selectSdeptfromStudentwhereSname='刘晨')列出选修了C01号和C02号课程的学生的学号。selectSNO

fromSC

whereSC.CNO='C01'

andSNOin

（selectSNO

fromSC

whereCNO='C02'）查询没有选修任何课程的学生的学号，姓名在选课名单中查询没有选修c01号课程的学生的学号，姓名在选课名单中查询没有选修c01，也没有选修c02课程的学生学号，姓名集合成员资格（IV

）集合之间的比较（Ⅰ）some/any/all子查询表达式比较运算符

any（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符。表达式比较运算符

all（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符。集合之间的比较（Ⅱ）>any大于子查询结果中的某个值>all大于子查询结果中的所有值<any小于子查询中的某个值<all小于子查询中的所有值=any等于子查询中的某个值=all等于子查询中的任意一个值!=any不等于子查询中的某个值!=all不等于子查询中的任意一个值集合之间的比较（Ⅲ）查询其他系中比信息系某个学生年龄小的学生姓名年龄等价于：selectSname,SagefromstudentwhereSage<any(selectSagefromstudentwhereSdept='is')andSdept<>'is'selectSname,SagefromstudentwhereSage<(selectmax(Sage)fromstudentwhereSdept='is')andSdept<>'is'集合之间的比较（V

）示例找出平均成绩最高的学生号。

selectSNO

fromSC

groupbySNO

having

avg(SCORE)>=all

（select

avg(SCORE)

fromSC

groupbySNO）思考：查询每个系年龄最小的学生的学号，姓名，年龄，系名集合之间的比较（VI

）事实上，用集函数实现子查询通常比直接用ANY或者ALL查询效率要高

＝<>或!=<<=>>=anyin<max<=max>min>=minallnotin<min<=min>max>=max集合基数的测试（3）查询没有选修c01号课程的学生姓名由于带有exists量词的相关子查询只关心内层查询是否有返回值，并不需要具体值，因此其效率并不一定低于不相关子查询，有时是高效的方法selectSnamefromstudentwherenotexists(select*fromscwheresno=student.snoandcno='C01')集合基数的测试（4）列出选修了001号和002号课程的学生的学号。思考：查询选修了全部课程的学生姓名

selectSNOfromSCSC1whereSC1.CNO='c01'andexists (selectSNO fromSCSC2 whereSC2.CNO='c02' andSC2.SNO=SC1.SNO)列出至少选修了2003001号学生选修的所有课程的学生名。

selectSNAME

fromStudentasS

where

notexists

（selectCNO

fromCOURSE

where

exists

（select*

fromSC