数据库应用技术教程 课件 第4章 数据查询3

上节知识回顾1、查询选课人数大于等于3人的课程号和选课人数。4.3集合查询如果有多个不同的查询结果集，但又希望将它们按照一定的关系连接在一起，组成一组数据，这就可以用集合运算来实现。UNION（并）、INTERSECT（交）、EXCEPT（差）参加联合查询操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。集合并运算（UNION）集合并运算是将来自不同查询结果集合组合起来，形成一个查询结果集（并集），UNION操作会自动将重复元组去除。例44：查询选修了1号课程或选修了2号课程的学生的学号。集合交运算（INTERSECT）集合交运算是将来自不同查询结果集合中公共的元组组合起来，形成一个查询结果集（交集）。INTERSECT操作会自动将重复的元组去除。例45：查询既选修了1号课程又选修了2号课程的学生的学号。集合差运算（EXCEPT）集合差运算是将属于左查询结果集但不属于右查询结果的元组组合起来，形成一个查询查询集（差集）。例46：查询选修了1号课程但没有选修2号课程的学生的学号。集合运算举例例47：查询学生表，列出除数学系的女生外，所有学生的学号、姓名、所在系和性别。4.4连接查询若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询连接查询包括内连接、自身连接、外连接等。连接基础知识连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：

[<表名1.>][<列名1>]<比较运算符>[<表名2.>][<列名2>]必须语义相同!内连接内连接语法如下：

SELECT…FROM表名[INNER]JOIN被连接表

ON连接条件执行连接操作的过程：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，…重复这个过程，直到表1中的全部元组都处理完毕为止。

示例例48．查询每个学生及其选课的详细信息。SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno改进例1SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno示例例49.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='计算机系'

表别名可以为表提供别名，其格式如下：

FROM<源表名>[AS]<表别名>为表指定别名可以简化表的书写。当为表指定了别名，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。示例例50.查询信息系修了“数据库”课程的学生信息，要求列出学生姓名、课程名和成绩。SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.Sno

JOINCoursecONc.Cno=SC.CnoWHERESdept='信息系'

ANDCname='数据库'

示例例51．查询所有修了C++课程的学生的修课情况，要求列出学生姓名、所在系和选课成绩。SELECTSname,Sdept，GradeFROMStudentSJOINSCONS.Sno=SC.Sno

JOINCourseCONC.Cno=SC.cnoWHERECname='C++'示例例52.有分组的多表连接查询。统计每门课（列出课程名）的学生考试平均成绩。SELECTCname,AVG(grade)asAverageGradeFROMCoursecJOINSCONC.Cno=SC.CnoGROUPBYCname示例例53.有分组和行选择条件的多表连接查询。统计信息系每门课程的选课人数、平均成绩、最高成绩和最低成绩。SELECTCno,COUNT(*)ASTotal,AVG(Grade)asAvgGrade,MAX(Grade)asMaxGrade,MIN(Grade)asMinGrade

FROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=‘信息系'

GROUPBYCno2.自身连接为特殊的内连接相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。

FROM表1ASA1--在内存中生成“A1”表

JOIN表1ASA2--在内存中生成“A2”表示例例54.查询与刘超华在同一个系学习的学生的姓名和所在的系。SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname='刘超华'

ANDS2.Sname!='刘超华'示例例55.查询与“数据库”学分相同的课程的课程名和学分。SELECTC1.Cname,C1.CreditFROMCourseC1JOINCourseC2ONC1.Credit=C2.CreditWHEREC2.Cname='数据库'3.外连接只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。

外连接的语法格式为：

FROM表1LEFT|RIGHT[OUTER]JOIN表2ON<连接条件>示例例56.查询学生的修课情况，包括修了课程的学生和没有修课的学生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSC ONStudent.Sno=SC.Sno示例例57.查询哪些课程没有人选，列出其课程号，课程名。SELECTC.cno,CnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL示例例58.查询数学系没有选课的学生，列出学生的学号，姓名和性别。SELECTSno,Sname,SsexFROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='数学系'ANDSC.SnoISNULL示例例59.统计信息系每个学生的选课门数，包括没有选课的学生，结果按选课门数递减排序。SELECTS.Sno学号,COUNT(SC.Cno)