《数据库》课件第4章-多表查询.ppt

1、WHERE子句子句对记录排序对记录排序GROUP BY子句子句HAVING子句子句COMPUTE与与COMPUTE BYStuIDStuNameDepName0001Mary计算机系0002Tom数学系0003Mike英语系有有Student表及表及Department表如下：表如下：Department(DepID,DepName)Student(StuID,StuName,StuAge,StuSex,StuCity,StuScore,DepID)第四章第四章表的管理表的管理连接查询和子查询连接查询和子查询连接查询连接查询子查询子查询 在很多情况下，需要从多个表中提取数据，组在很多情况下，需

2、要从多个表中提取数据，组合成一个结果集。如果一个查询需要对多个表进行合成一个结果集。如果一个查询需要对多个表进行操作，则将此查询称为连接查询。操作，则将此查询称为连接查询。连接查询包括内连接、外连接和交叉连接。连接查询包括内连接、外连接和交叉连接。(INNER,OUTER,CROSS)SELECT 列名列名,nFROM 表表1 CROSS|INNER|LEFT|RIGHTOUTER JOIN 表表2 ON 表表1.连接字段连接字段=表表2.连接字段连接字段WHERE search_condition 内连接常使用等号连接每个表共有列的值来匹内连接常使用等号连接每个表共有列的值来匹配两个表中的行

3、。只有每个表中都存在相匹配列配两个表中的行。只有每个表中都存在相匹配列值的记录才出现在结果集中。在内连接中，所有值的记录才出现在结果集中。在内连接中，所有表是表是平等平等的，没有前后之分。的，没有前后之分。sales114323151553711421003buyersAdam BarrSean ChaiEva CoretsErin OMelia1234查询结果查询结果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003SELECT buyer_name,sales.buyer_id,qty FROM buyers

4、 INNER JOIN sales ON buyers.buyer_id=sales.buyer_id语法：语法：SELECT 列名列名,nFROM 表表1 INNER JOIN 表表2 ON 表表1.连接字段连接字段=表表2.连接字段连接字段WHERE search_condition示例：有示例：有Student表及表及Department表如下表如下Department(DepID,DepName)Student(StuID,StuName,StuAge,StuSex,StuCity,StuScore,DepID)要显示计算机系学生的要显示计算机系学生的StuID，StuName，Dep

5、ID,DepName字段。字段。语法：语法：SELECT 列名列名,nFROM 表表1 INNER JOIN 表表2 ON 表表1.连接字段连接字段=表表2.连接字段连接字段WHERE search_condition示例：有示例：有Student表及表及Department表如下表如下Department(DepID,DepName)Student(StuID,StuName,StuAge,StuSex,StuCity,StuScore,DepID)要显示计算机系学生的要显示计算机系学生的StuID，StuName，DepID,DepName字段。字段。解决方案：解决方案：SELECT St

6、uID,StuName,Department.DepID,DepNameFROM Student JOIN DepartmentON Student.DepID=Department.DepIDWHERE DepName=计算机系计算机系示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,depID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)显示成绩显示成绩90分以上的学生姓名，课程分以上的学生姓名，课程名及成绩。名及成绩。语法：语法：SELECT 列名列表列名列表 FROM A JOIN

7、 B ON 连接条件连接条件1JOIN C ON 连接条件连接条件2WHERE search_condition示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,depID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)显示成绩显示成绩90分以上的学生姓名，课程分以上的学生姓名，课程名及成绩。名及成绩。解决方案：解决方案：SELECT StuName,CourseName,ScoreFROM Student JOIN SC ON JOIN Course ON 语法：语法：SELECT 列名列

8、表列名列表 FROM A JOIN B ON 连接条件连接条件1JOIN C ON 连接条件连接条件2WHERE search_conditionStudent.StuID=SC.StuIDCourse.CourseID=SC.CourseIDWHERE Score=90示例：有关系模式如下示例：有关系模式如下Department（DepID,DepName）Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。查询

9、学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。示例：有关系模式如下示例：有关系模式如下Department（DepID,DepName）Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)解决方案：解决方案：SELECT SC.StuID,StuName,DepName,CourseName,ScoreFROM Student JOIN SC ON Student.StuID=SC.StuIDJOIN Course ON Course.CourseID=SC.Co

10、urseIDJOIN Department ON Department.DepID=Student.DepID查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。与内连接相对，参与外连接的表有主次之分。以主表的每与内连接相对，参与外连接的表有主次之分。以主表的每一行数据去匹配从表中的数据列，符合连接条件的数据将直一行数据去匹配从表中的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。值后再返回到结果集中。外连接

11、可以分为左向外连接、右向外连接和完整外部连接外连接可以分为左向外连接、右向外连接和完整外部连接3种情况。种情况。(LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL JOIN)。语法：语法：SELECT 列名列名,nFROM 表表1 LEFT|RIGHTOUTER JOIN 表表2 ON 表表1.连接字段连接字段=表表2.连接字段连接字段WHERE search_condition 左向外连接以连接（左向外连接以连接（JOIN）子句左侧的表为主表，主表）子句左侧的表为主表，主表中所有记录都将出现在结果集中。如果主表中的记录在右表中所有记录都将出现在结果集中。如果主表中的

12、记录在右表中没有匹配的数据，则结果集中右表的列值为中没有匹配的数据，则结果集中右表的列值为NULL。sales114323151553711421003buyersAdam BarrJane LeeEva CoretsErin OMelia1234查询结果查询结果Adam BarrAdam BarrErin OMeliaEva Corets11431553711Erin OMelia41003Jane LeeNULLNULLSELECT buyer_name,sales.buyer_id,qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer

13、_id=sales.buyer_id右向外连接以连接（右向外连接以连接（JOIN）子句右侧的表为主表，主）子句右侧的表为主表，主表中所有记录都将出现在结果集中。如果主表中的记录表中所有记录都将出现在结果集中。如果主表中的记录在左表中没有匹配的数据，则结果集中左表的列值为在左表中没有匹配的数据，则结果集中左表的列值为NULL。解决方案：解决方案：SELECT DepName，StuID，StuNameFROM Student RIGHT OUTER JOIN DepartmentON Student.DepID=Department.DepID示例：系表与学生表连接，当有学生属于相应的系时，则显

14、示例：系表与学生表连接，当有学生属于相应的系时，则显示该系的系名，学生学号及学生姓名，如果某个系没有学生，示该系的系名，学生学号及学生姓名，如果某个系没有学生，则显示一条包含系名，而学生学号，学生姓名均为则显示一条包含系名，而学生学号，学生姓名均为NULL的的信息。信息。完整外部连接包括连接表中的所有行，无论它们是否匹完整外部连接包括连接表中的所有行，无论它们是否匹配。在配。在SQL Server中，可以使用中，可以使用FULL OUTER JOIN或或FULL JOIN关键字定义完整外部连接。关键字定义完整外部连接。(相当于左外连接与相当于左外连接与右外连接的并集右外连接的并集)在交叉连接查

15、询中，两个表中的每两行都可能互相在交叉连接查询中，两个表中的每两行都可能互相组合成为结果集中的一行。交叉连接并不常用，除非需组合成为结果集中的一行。交叉连接并不常用，除非需要穷举两个表的所有可能的记录组合。要穷举两个表的所有可能的记录组合。示例示例：SELECT buyer_name,qty FROM buyers CROSS JOIN salesResultAdam BarrAdam BarrAdam BarrAdam Barr1553711Adam Barr1003Sean Chai15Sean Chai5Sean Chai37Sean Chai11Sean Chai1003Eva Cor

16、ets15.sales114323151553711421003buyers1234Adam BarrSean ChaiEva CoretsErin OMelia 子查询就是在一个子查询就是在一个SELECT语句中又嵌套了另一个语句中又嵌套了另一个SELECT语句。在语句。在WHERE子句和子句和HAVING子句中都可以嵌套子句中都可以嵌套SELECT语句。语句。示例：示例：查询和查询和mary在同一个系的所有学生姓名。在同一个系的所有学生姓名。解决方案：解决方案：Declare DepID intSELECT DepID=DepID FROM Student WHERE StuName=ma

17、rySELECT StuName FROM Student WHERE DepID=DepIDWHERE子句和子句和HAVING子句的形式子句的形式:=，比较运算符，比较运算符IN关键字关键字EXISTS关键字关键字语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x =(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：示例：查询和查询和mary在同一个系的所有学生姓名。在同一个系的所有学生姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE DepID=(SELECT DepID FROM

18、Student WHERE StuName=mary)语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x =(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：示例：查询计算机系的所有学生姓名。查询计算机系的所有学生姓名。语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x =(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：示例：查询计算机系的所有学生姓名。查询计算机系的所有学生姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE

19、 DepID=(SELECT DepID FROM Student WHERE StuName=mary)示例：示例：SELECT StuID FROM Student WHERE StuScore (SELECT AVG(StuScore)FROM Student WHERE DepID=1)解决方案：将成绩超过解决方案：将成绩超过1系的平均成绩的学生找出来。系的平均成绩的学生找出来。语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x NOTIN(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：有关系模式如下示例：有关系模式如下

20、Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)要查找选修了要查找选修了1号课程的所有学生号课程的所有学生姓名姓名语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x NOTIN(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,Cours

21、eID,Score)要查找选修了要查找选修了1号课程的所有学生号课程的所有学生姓名姓名解决方案：解决方案：SELECT StuName FROM Student WHERE StuID IN(SELECT StuID FROM SC WHERE CourseID=1)语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名列名x NOTIN(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：示例：将选择数据库课程的学生姓名找出来。将选择数据库课程的学生姓名找出来。语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名

22、列名x NOTIN(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)解决方案：解决方案：SELECT StuName FROM StudentWHERE StuID IN（SELECT StuID FROM SC WHERE CourseID=（SELECT CourseID FROM Course WHERE CourseName=DB）示例：示例：将选择数据库课程的学生姓名找出来。将选择数据库课程的学生姓名找出来。示例示例2：有关系模式：有关系模式Student（StuID,StuName,StuAge,StuScore,DepID），），要显示院系平均入学成绩大于所有

23、学生平均成绩的记录。要显示院系平均入学成绩大于所有学生平均成绩的记录。解决方案：解决方案：SELECT DepID,AVG(StuScore)FROM Student GROUP BY DepID HAVING AVG(StuScore)(SELECT AVG(StuScore)FROM Student)示例示例3：有关系模式：有关系模式SC（StuID,CourseID,Score），），Course(CourseID,CourseName,Credit)查找选修了全部课程的学生学号。查找选修了全部课程的学生学号。解决方案：解决方案：SELECT StuID FROM SC GROUP BY

24、 StuID HAVING COUNT(*)=(SELECT COUNT(*)FROM Course)示例示例4：有关系模式如下：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(StuID,CourseID,Score)示例示例2：查询选修：查询选修“数据库原理数据库原理”课程成绩最高的学生姓名课程成绩最高的学生姓名示例示例4：有关系模式如下：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(Stu

25、ID,CourseID,Score)示例示例2：查询选修：查询选修“数据库原理数据库原理”课程成绩最高的学生姓名课程成绩最高的学生姓名解决方案：解决方案：SELECT StuName FROM Student JOIN SC ON Student.StuID=SC.StuIDJOIN Course on Course.CourseID=Sc.CourseIDWHERE CourseName=database AND Score=(SELECT max(Score)FROM SC WHERE CourseID=(SELECT CourseID FROM Course WHERE CourseNa

26、me=database)示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(StuID,CourseID,Score)示例示例3：将计算机系所有学生成绩置为：将计算机系所有学生成绩置为0.解决方案：解决方案：UPDATE SCSET Score=0WHERE StuID IN(SELECT StuID FROM StudentWHERE DepName=计算机计算机)EXISTS是存在量词。带有是存在量词。带有EXISTS谓词的子查谓词的子查询不返回任何数据，只返回逻辑真值

27、和逻辑假值。询不返回任何数据，只返回逻辑真值和逻辑假值。SELECT 列名列表列名列表 FROM 表名表名1 WHERE NOT EXISTS(SELECT*FROM 表名表名2 WHERE子句子句)BOOL值值SELECT StuID,StuName FROM Student WHERE EXISTS （SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=1)StuIDStuNameDepID12001jack112002Tom212003Mary1父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）S

28、tuIDCourseIDGrade12001190120012921200327612001SELECT StuID,StuName FROM Student WHERE EXISTS （SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=1)StuIDStuNameDepID12001jack112002Tom212003Mary1父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）StuIDCourseIDGrade12001190120012921200327612001SELECT StuID,S

29、tuName FROM Student WHERE EXISTS （SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=1)StuIDStuNameDepID12001jack112002Tom212003Mary1父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）StuIDCourseIDGrade12001190120012921200327612001SELECT StuID,StuName FROM Student WHERE EXISTS （SELECT*FROM SC WHERE StuID

30、=Student.StuID AND CourseID=1)StuIDStuNameDepID12001jack112002Tom212003Mary1父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）StuIDCourseIDGrade12001190120012801200329012002SELECT StuID,StuName FROM Student WHERE EXISTS （SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=1)StuIDStuName12001jack结果集结果集示例：

31、有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(StuID,CourseID,Score)使用使用EXISTS谓词谓词查询所有成绩都在查询所有成绩都在85分以上的学生的学号和姓名。分以上的学生的学号和姓名。示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(StuID,CourseID,Score)使用使用EXISTS谓词谓词查询所有成绩都在查询所有成绩都

32、在85分以上的学生的学号和姓名。分以上的学生的学号和姓名。解决方案：解决方案：SELECT StuID,StuNameFROM StudentWHERE EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score85)解决方案：解决方案：SELECT StuID,StuNameFROM StudentWHERE EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score85)StuIDStuNameDepID12001jack112002Tom212003Mary1StuIDCourse

33、IDGrade120011901200128012003290父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）12001解决方案：解决方案：SELECT StuID,StuNameFROM StudentWHERE NOT EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score85)StuIDStuNameDepID12001jack112002Tom212003Mary1StuIDCourseIDGrade120011901200128012003290父查询对应的表（父查询对应的表（Studen

34、t）子查询对应的表（子查询对应的表（SC）12001解决方案：解决方案：SELECT StuID,StuNameFROM StudentWHERE NOT EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score85)StuIDStuNameDepID12001jack112002Tom212003Mary1StuIDCourseIDGrade120011901200128012003290父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）12002解决方案：解决方案：SELECT DISTINCT S

35、tuID,StuNameFROM Student JOIN SC ON Student.StuID=SC.StuIDWHERE NOT EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score85)StuIDStuNameDepID12001jack112002Tom212003Mary1StuIDCourseIDGrade120011901200128012003290父查询对应的表（父查询对应的表（Student）子查询对应的表（子查询对应的表（SC）12002 SELECT StuID FROM Student WHERE NOT

36、EXISTS (SELECT*FROM Course WHERE NOT EXISTS (SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=Course.CourseID)FOR(StuID=12001;StuID=n;StuID+)FOR(CourseID=1;CourseID=n;CourseID+)SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=Course.CourseIDFOR(StuID=12001;StuID=n;StuID+)FOR(CourseID=1;Cou

37、rseID=n;CourseID+)SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=Course.CourseIDStuIDStuNameDepID12001jack112002Tom2StudentCourseCourseIDCourseName1Database2OSStuIDCourseIDGrade120011901200128012002290SCFFOR(StuID=12001;StuID=n;StuID+)FOR(CourseID=1;CourseID=n;CourseID+)SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=Course.CourseIDStuIDStuNameDepID12001jack112002Tom2StudentCourseCourseIDCourseName1Database2OSStuIDCourseIDGrade120011901200128012002290SCFFUT连接查询连接查询子查询子查询查询语句（高级）查询语句（高级）