《sql server数据库应用与实践教程》课件第4章-多表查询.ppt

1、StuIDStuNameDepName0001Mary计算机系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 INNER JOIN sales ON buyers.buyer_id=sales.buyer_id语法：语法：SEL

4、ECT 列名列名,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 StuID,StuName,Department.De

5、pID,DepNameFROM Student JOIN DepartmentON Student.DepID=Department.DepIDWHERE DepName=计算机系计算机系示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,depID）Course(CourseID,CourseName)SC(StuID,CourseID,Grade)显示成绩显示成绩90分以上的学生姓名，课程分以上的学生姓名，课程名及成绩。名及成绩。解决方案：解决方案：SELECT StuName,CourseName,GradeFROM Student JOIN

6、 SC ON Student.StuID=SC.StuIDJOIN Course ON Course.CourseID=SC.CourseIDWHERE Grade=90语法：语法：SELECT 列名列表列名列表 FROM A JOIN B ON 连接条件连接条件1JOIN C ON 连接条件连接条件2WHERE search_condition示例：有关系模式如下示例：有关系模式如下Department（DepID,DepName）Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,S

7、core)解决方案：解决方案：SELECT SC.StuID,StuName,DepName,CourseName,GradeFROM Student JOIN SC ON Student.StuID=SC.StuIDJOIN Course ON Course.CourseID=SC.CourseIDJOIN Department ON Department.DepID=Student.DepID查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。与内连接相对，参与外连接的表有主次之分。以主表的每与内连接相对，参与外连接的

8、表有主次之分。以主表的每一行数据去匹配从表中的数据列，符合连接条件的数据将直一行数据去匹配从表中的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。值后再返回到结果集中。外连接可以分为左向外连接、右向外连接和完整外部连接外连接可以分为左向外连接、右向外连接和完整外部连接3种情况。种情况。(LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL JOIN)。语法：语法：SELECT 列名列名,nFROM 表表1 LEFT|RIGHTOUTER JOIN 表表2 O

9、N 表表1.连接字段连接字段=表表2.连接字段连接字段WHERE search_condition 左向外连接以连接（左向外连接以连接（JOIN）子句左侧的表为主表，主表）子句左侧的表为主表，主表中所有记录都将出现在结果集中。如果主表中的记录在右表中所有记录都将出现在结果集中。如果主表中的记录在右表中没有匹配的数据，则结果集中右表的列值为中没有匹配的数据，则结果集中右表的列值为NULL。sales114323151553711421003buyersAdam BarrJane LeeEva CoretsErin OMelia1234查询结果查询结果Adam BarrAdam BarrErin

10、OMeliaEva Corets11431553711Erin OMelia41003Jane LeeNULLNULLSELECT buyer_name,sales.buyer_id,qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id=sales.buyer_id右向外连接以连接（右向外连接以连接（JOIN）子句右侧的表为主表，主）子句右侧的表为主表，主表中所有记录都将出现在结果集中。如果主表中的记录表中所有记录都将出现在结果集中。如果主表中的记录在左表中没有匹配的数据，则结果集中左表的列值为在左表中没有匹配的数据，则结果集中左表的

11、列值为NULL。示例：示例：SELECT DepName，StuID，StuNameFROM Student RIGHT OUTER JOIN DepartmentON Student.DepID=Department.DepID解决方案：以系表为主表匹配学生表，当有学生属于相应的解决方案：以系表为主表匹配学生表，当有学生属于相应的系时，则显示该系的系名，学生学号及学生姓名，如果某个系时，则显示该系的系名，学生学号及学生姓名，如果某个系没有学生，则显示一条包含系名，而学生学号，学生姓名系没有学生，则显示一条包含系名，而学生学号，学生姓名均为均为NULL的信息。的信息。完整外部连接包括连接表中的

12、所有行，无论它们是否匹完整外部连接包括连接表中的所有行，无论它们是否匹配。在配。在SQL Server中，可以使用中，可以使用FULL OUTER JOIN或或FULL JOIN关键字定义完整外部连接。关键字定义完整外部连接。(相当于左外连接与相当于左外连接与右外连接的并集右外连接的并集)在交叉连接查询中，两个表中的每两行都可能互相在交叉连接查询中，两个表中的每两行都可能互相组合成为结果集中的一行。交叉连接并不常用，除非需组合成为结果集中的一行。交叉连接并不常用，除非需要穷举两个表的所有可能的记录组合。要穷举两个表的所有可能的记录组合。示例示例：SELECT buyer_name,qty FR

13、OM buyers CROSS JOIN salesResultAdam BarrAdam BarrAdam BarrAdam Barr1553711Adam Barr1003Sean Chai15Sean Chai5Sean Chai37Sean Chai11Sean Chai1003Eva Corets15.sales114323151553711421003buyers1234Adam BarrSean ChaiEva CoretsErin OMelia 子查询就是在一个子查询就是在一个SELECT语句中又嵌套了另一个语句中又嵌套了另一个SELECT语句。在语句。在WHERE子句和子句和

14、HAVING子句中都可以嵌套子句中都可以嵌套SELECT语句。语句。示例：示例：查询和查询和mary在同一个系的所有学生姓名。在同一个系的所有学生姓名。解决方案：解决方案：Declare DepID intSELECT DepID=DepID FROM Student WHERE StuName=marySELECT StuName FROM Student WHERE DepID=DepIDWHERE子句和子句和HAVING子句的形式子句的形式:=，比较运算符，比较运算符IN关键字关键字EXISTS关键字关键字语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE 列名

15、列名x =(SELECT 列名列名x FROM 表名表名2 WHERE子句子句)示例：示例：查询和查询和mary在同一个系的所有学生姓名。在同一个系的所有学生姓名。示例：示例：查询计算机系的所有学生姓名。查询计算机系的所有学生姓名。解决方案：解决方案：SELECT StuName FROM Student WHERE DepID=(SELECT DepID FROM Student WHERE StuName=mary)解决方案：解决方案：SELECT StuName FROM Student WHERE DepID=(SELECT DepID FROM Department WHERE De

16、pName=计算计算机机)示例：示例：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子句子句)示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepID）Cours

17、e(CourseID,CourseName)SC(StuID,CourseID,Score)要查找选修了要查找选修了1号课程的所有学生号课程的所有学生姓名姓名解决方案：解决方案：SELECT StuName FROM Student WHERE StuID IN(SELECT StuID FROM SC WHERE CourseID=1)示例：示例：SELECT StuName FROM StudentWHERE StuID IN（SELECT StuID FROM SC WHERE CourseID=（SELECT CourseID FROM Course WHERE CourseName=

18、DB）解决方案：解决方案：将选择数据库课程的学生姓名找出来。将选择数据库课程的学生姓名找出来。语法：语法：SELECT 列名列表列名列表 FROM 表名表名1 WHERE NOT EXISTS(SELECT*FROM 表名表名2 WHERE子句子句)返回第一步返回第一步SELECT StuID,StuName FROM Student WHERE EXISTS（SELECT*FROM SC WHERE StuID=Student.StuID AND CourseID=1)示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepName）Cours

19、e(CourseID,CourseName)SC(StuID,CourseID,Score)示例示例1：查询所有成绩都在查询所有成绩都在85分以上的学生的学号和姓名。分以上的学生的学号和姓名。解决方案：解决方案：SELECT StuID,StuNameFROM StudentWHERE NOT EXISTS(SELECT*FROM SCWHERE StuID=Student.StuID AND Score(SELECT AVG(StuScore)FROM Student)合并查询（合并查询（UNION）SELECT INTO子句子句示例：有关系模式如下示例：有关系模式如下Student（Stu

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

21、 WHERE CourseID=(SELECT CourseID FROM Course WHERE CourseName=database)示例：有关系模式如下示例：有关系模式如下Student（StuID,StuName,StuAge,DepName）Course(CourseID,CourseName)SC(StuID,CourseID,Grade)示例示例3：将计算机系所有学生成绩置为：将计算机系所有学生成绩置为0.解决方案：解决方案：UPDATE SCSET Grade=0WHERE StuID IN(SELECT StuID FROM StudentWHERE DepName=计算

22、机计算机)以以SchoolInfo数据库为基础，写出以下查询：数据库为基础，写出以下查询：Department(DepID,DepName)Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Grade)1.查询查询计算机系计算机系的所有学生信息。的所有学生信息。2.查询选修了查询选修了“英语英语”课程的所有学生学号及姓名。课程的所有学生学号及姓名。3.查询查询“英语英语”成绩最高的学生学号，姓名。（子查询）成绩最高的学生学号，姓名。（子查询）4.查询查询“计算机系计算机系”年龄最小的学

23、生姓名。（子查询）年龄最小的学生姓名。（子查询）5.查询非查询非“计算机系计算机系”的所有年龄小于的所有年龄小于“计算机系计算机系“最大年最大年龄的学生信息。（子查询）龄的学生信息。（子查询）以以SchoolInfo数据库为基础，写出以下查询：数据库为基础，写出以下查询：Department(DepID,DepName)Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Grade)6.查询学生平均年龄均超过查询学生平均年龄均超过20岁的系的系号及系名。岁的系的系号及系名。7.按系分组显示平均年龄大于全校学生平均年龄的组的系号按系分组显示平均年龄大于全校学生平均年龄的组的系号及平均年龄。及平均年龄。8.查询选修了所有课程的学生学号。（使用查询选修了所有课程的学生学号。（使用group by语句）语句）9.将计算机系的学生年龄加一岁。将计算机系的学生年龄加一岁。10.删除删除“数据库数据库”这门课。这门课。连接查询连接查询子查询子查询查询语句（高级）查询语句（高级）