数据库原理与应用教学课件（共12章）第4章-多表查询.ppt
52页
1 1回顾回顾WHERE子句对记录排序GROUP BY子句HAVING子句COMPUTE与COMPUTE BY2 2思考：思考：StuIDStuNameDepName0001Mary计算机系0002Tom数学系0003Mike英语系有Student表及Department表如下：Department(DepID,DepName)Student(StuID,StuName,StuAge,StuSex,StuCity,StuScore,DepID)3 3第四章表的管理连接查询和子查询4 4目标目标连接查询子查询5 54.5 4.5 连接查询连接查询 在很多情况下，需要从多个表中提取数据，组合成一个结果集如果一个查询需要对多个表进行操作，则将此查询称为连接查询 连接查询包括内连接、外连接和交叉连接INNER,OUTER,CROSS) 6 6连接运算的语法连接运算的语法SELECT 列名 ,nFROM 表1 CROSS|INNER|LEFT | RIGHTOUTER JOIN 表2 ON 表1.连接字段 = 表2.连接字段WHERE search_condition7 7内连接内连接 内连接常使用等号连接每个表共有列的值来匹配两个表中的行。
只有每个表中都存在相匹配列值的记录才出现在结果集中在内连接中，所有表是平等的，没有前后之分8 8内连接内连接salesbuyer_idbuyer_id prod_idprod_id qtyqty114323151553711421003buyersbuyer_namebuyer_nameAdam BarrSean ChaiEva CoretsErin OMeliabuyer_idbuyer_id1234查询结果buyer_namebuyer_nameAdam BarrAdam BarrErin OMeliaEva Coretsbuyer_idbuyer_id qtyqty11431553711Erin OMelia41003SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id9 9内连接内连接语法：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字段。
1010内连接内连接语法：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 StuID,StuName,Department.DepID,DepNameFROM Student JOIN DepartmentON Student.DepID = Department.DepIDWHERE DepName = 计算机系1111两表以上的多表连接两表以上的多表连接示例：有关系模式如下Student（StuID,StuName,StuAge,depID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)显示成绩90分以上的学生姓名，课程名及成绩。
语法：SELECT 列名列表 FROM A JOIN B ON 连接条件1JOIN C ON 连接条件2WHERE search_condition1212两表以上的多表连接两表以上的多表连接示例：有关系模式如下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 列名列表 FROM A JOIN B ON 连接条件1JOIN C ON 连接条件2WHERE search_conditionStudent.StuID = SC.StuIDCourse.CourseID =SC.CourseIDWHERE Score=901313习题：习题：示例：有关系模式如下Department（DepID,DepName）Student（StuID,StuName,StuAge,DepID）Course(CourseID,CourseName)SC(StuID,CourseID,Score)查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩。
1414习题：习题：示例：有关系模式如下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.CourseIDJOIN Department ON Department.DepID = Student.DepID查询学生的学号、姓名、所在系的名称、课程名及该课程的考试成绩1515外连接外连接 与内连接相对，参与外连接的表有主次之分以主表的每一行数据去匹配从表中的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中 外连接可以分为左向外连接、右向外连接和完整外部连接3种情况LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL JOIN)。
语法：SELECT 列名 ,nFROM 表1 LEFT|RIGHTOUTER JOIN 表2 ON 表1.连接字段 = 表2.连接字段WHERE search_condition1616左向外连接左向外连接 左向外连接以连接（JOIN）子句左侧的表为主表，主表中所有记录都将出现在结果集中如果主表中的记录在右表中没有匹配的数据，则结果集中右表的列值为NULL1717左向外连接左向外连接salesbuyer_idbuyer_id prod_idprod_idqtyqty114323151553711421003buyersbuyer_namebuyer_nameAdam BarrJane LeeEva CoretsErin OMeliabuyer_idbuyer_id1234查询结果buyer_namebuyer_nameAdam BarrAdam BarrErin OMeliaEva Coretsbuyer_idbuyer_idqtyqty11431553711Erin OMelia41003Jane LeeNULLNULLSELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id =sales.buyer_id1818右向外连接右向外连接右向外连接以连接（JOIN）子句右侧的表为主表，主表中所有记录都将出现在结果集中。
如果主表中的记录在左表中没有匹配的数据，则结果集中左表的列值为NULL解决方案：SELECT DepName，StuID，StuNameFROM Student RIGHT OUTER JOIN DepartmentON Student.DepID = Department.DepID示例：系表与学生表连接，当有学生属于相应的系时，则显示该系的系名，学生学号及学生姓名，如果某个系没有学生，则显示一条包含系名，而学生学号，学生姓名均为NULL的信息1919完整外连接完整外连接 完整外部连接包括连接表中的所有行，无论它们是否匹配在SQL Server中，可以使用FULL OUTER JOIN或FULL JOIN关键字定义完整外部连接相当于左外连接与右外连接的并集)2020交叉连接交叉连接 在交叉连接查询中，两个表中的每两行都可能互相组合成为结果集中的一行交叉连接并不常用，除非需要穷举两个表的所有可能的记录组合示例：SELECT buyer_name, qty FROM buyers CROSS JOIN salesResultbuyer_namebuyer_nameAdam BarrAdam BarrAdam BarrAdam Barrqtyqty1553711Adam Barr1003Sean Chai15Sean Chai5Sean Chai37Sean Chai11Sean Chai1003Eva Corets15.salesbuyer_idbuyer_id prod_idprod_idqtyqty114323151553711421003buyersbuyer_idbuyer_id1234buyer_namebuyer_nameAdam BarrSean ChaiEva CoretsErin OMelia21214.6 4.6 子查询子查询 子查询就是在一个SELECT语句中又嵌套了另一个SELECT语句。
在WHERE子句和HAVING子句中都可以嵌套SELECT语句示例：查询和mary在同一个系的所有学生姓名 解决方案：Declare DepID intSELECT DepID = DepID FROM Student WHERE StuName = marySELECT StuName FROM Student WHERE DepID = DepIDWHERE子句和HAVING子句的形式:=，比较运算符IN关键字EXISTS关键字2222在在WHEREWHERE子句中使用子句中使用= =语法： SELECT 列名列表 FROM 表名1 WHERE 列名x =(SELECT 列名x FROM 表名2 WHERE子句)示例：查询和mary在同一个系的所有学生姓名解决方案： SELECT StuName FROM Student WHERE DepID =(SELECT DepID FROM Student WHERE StuName = mary)2323在在WHEREWHERE子句中使用子句中使用= =语法： SELECT 列名列表 FROM 表名1 WHERE 列名x =(SELECT 列名x FROM 表名2 WHERE子句)示例：查询计算机系的所有学生姓名。
2424在在WHEREWHERE子句中使用子句中使用= =语法： SELECT 列名列表 FROM 表名1 WHERE 列名x =(SELECT 列名x FROM 表名2 WHERE子句)示例：查询计算机系的所有学生姓名解决方案： SELECT StuName FROM Student WHERE DepID =(SELECT Dep。
