SQL Server数据库基础课件第六单元 高级查询

第六单元高级查询任务6.1多表连接查询任务6.2使用子查询6.1.1情景描述6.1.2问题分析6.1.3解决方案6.1.4知识总结6.1.5应用实践任务6.1多表连接查询在学生信息管理系统里，需要每学期为每位老师安排教学任务。针对学校的具体情况，需要在排课时间段显示还没有排课的老师的信息，保证每个老师都有教学任务。6.1.1情景描述为了解决上述问题，需要完成以下任务：1．查询显示所有老师的排课信息，不管有没有教学任务，都要显示老师的信息，如果有排课任务，就在授课表中有对应的课程编号信息；如果没有排课任务，就在对应的课程编号字段显示为NULL。2．再用条件“课程编号ISNULL”过滤没有教学任务的教师信息。3．执行查询语句。6.1.2问题分析1．打开SQLServerManagementStudio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“学生管理”；2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；3．在“查询编辑器”上输入以下代码：6.1.3解决方案4．单击工具栏上的【执行】按钮，如图所示。SELECTjs.教师编号,教师姓名,职称,sk.班级代码,sk.课程编号FROM教师ASjsLEFTJOIN授课ASskONjs.教师编号=sk.教师编号WHEREsk.课程编号ISNULL内连接外连接交叉连接6.1.4知识总结内连接（INNERJOIN）将两个表中满足连接条件的记录组合在一起，通过比较数据源表间公共列的值，从多个源表检索符合条件的行的操作。内连接查询可以通过两种方式实现一种在WHERE子句中指出连接条件SELECT字段列表FROM表名列表WHERE连接条件另外一种使用INNERJOIN关键字查询。SELECT字段列表FROM表名1INNERJOIN

表名2ON表名1.字段名=表名2.字段名内连接SELECT学号,姓名,性别,班级名称FROM学生,班级

WHERE学生.班级编号=班级.班级代码【例6-1】在学生管理数据库里查询显示学生的学号、姓名、性别、班级名称。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：FROM子句中的表名是要查询的字段所在的表。可以通过“表名.列名”来引用表中的列，防止两个表中都有同样的字段，引起歧义。可以在表名的后面使用AS关键字来为表定制表别名，那么就可以用“表别名.列名”来引用表中的列。

SELECT班级代码,班级名称,b.专业代码,专业名称FROM班级ASb,专业ASzWHEREb.专业代码=z.专业代码【例6-2】在学生管理数据库里查询显示班级代码、班级名称、专业代码、专业名称。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：表名后面用AS关键字对表重命名，用b代替班级表，用z代替专业表。使用“表别名.列名”来区分班级表和专业表共有的“专业代码”列。SELECTxs.学号,姓名,kc.课程编号,课程名称,成绩FROM选课ASxk,学生ASxs,课程ASkcWHERExk.学号=xs.学号ANDxk.课程编号=kc.课程编号【例6-3】在学生管理数据库里查询显示学号、姓名、课程编号、课程名、成绩。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：查询显示的结果在选课表、学生表和课程表三张表中。表名后面用AS关键字对表重命名，用xk代替选课表，用xs代替学生表，用kc代替课程表。选课表和学生表都有“学号”字段，在查询语句中使用到“学号”字段，就用“表别名.列名”来区是哪个表中的列；同理，也要区分选课表和课程表的公共字段“课程编号”。在WHERE的连接条件中是三个表的连接，区分表之间关联的列，选课表和学生表之间的关联，选课表与课程表之间有关联，使用AND运算符将两个关联条件组合在一起。

SELECT学号,姓名,性别,班级名称FROM学生ASxsINNERJOIN班级ASbjONxs.班级编号=bj.班级代码【例6-4】在学生管理数据库里查询显示学生的学号、姓名、性别、班级名称。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：INNER可以省略。两个表的公共字段为学生表的“班级编号”和班级表的“班级代码”，公共字段保存的信息是相同的，字段名可以不相同。SELECTjs.教师编号,js.教师姓名,js.学历,xb.系部名称

FROM教师ASjsJOIN系部ASxbONxb.系部代码=js.系部代码【例6-5】在学生管理数据库里查询显示教师的编号、姓名、学历、系部名称。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：INNER省略。为了方便查看每个字段所在的表，可以在每个字段前面加上表别名。

SELECTxs.学号,xs.姓名,kc.课程编号,kc.课程名称,xk.成绩FROM选课ASxkJOIN学生ASxsONxk.学号=xs.学号JOIN课程ASkcONxk.课程编号=kc.课程编号【例6-6】在学生管理数据库里查询显示学号、姓名、课程编号、课程名、成绩。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：INNER省略。为了方便查看每个字段所在的表，可以在每个字段前面加上表别名。显示的字段在三张表中，先让两个表使用JOIN命令，在ON连接条件的后面再用JOIN连接第三张表，后面用连接条件ON指出与第三张表的连接条件。

外连接（OUTERJOIN）经常用于相连接的表中至少有一个表需要显示所有的行，除了包括满足搜索条件的连接表中的所有行，甚至包括在其它连接表中没有匹配行的一个表中的行。由于显示所有行的表不同，又分为：左外连接右外连接完全外连接。外连接左外连接（LEFTOUTERJOIN）包括JOIN子句中左侧表中的所有行，不管是否满足连接条件都会显示出来。右表中的行与左表中的行没有匹配时，将为来自右表的所有结果集列赋以NULL值。语法规则如下：左外连接参数说明如下：字段列表：查询显示的字段名的列表。表名1，表名2：查询来源的两个表，中间用LEFTOUTERJOIN关键字连接，OUTER关键字可以省略。ON：ON关键字后面是连接条件。表名1.字段名，表名2.字段名：两个表的公共列。SELECT字段列表FROM表名1LEFTOUTERJOIN表名2ON表名1.字段名=表名2.字段名SELECTxs.学号,xs.姓名,xk.课程编号,xk.成绩FROM学生ASxsLEFTJOIN选课ASxkONxk.学号=xs.学号【例6-7】在学生管理数据库里查询显示所有学生的学号、姓名、课程编号、成绩。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：OUTER省略。为了方便查看每个字段所在的表，可以在每个字段前面加上表别名。学生所有行都被显示出来，学号为10、11、12的学生在“选课”表中没有满足条件的行，在对应表的位置上赋以NULL。外连接主要用在显示一个表的所有行的情况下，显示所有行的表放在LEFTJOIN的左边，就是左外连接，此题可以查看所有学生的选课信息，那么就可以看出有三个学生一门课都没有选。SELECTbj.班级代码,bj.班级名称,xs.学号,xs.姓名

FROM班级ASbjLEFTJOIN学生ASxsONbj.班级代码=xs.班级编号【例6-8】在学生管理数据库里查询还没有学生的班级信息。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：题目要求查询没有学生的班级信息，班级表中的所有行都被显示，如果有学生，对应的学号、姓名就有值；没有学生，对应位置的值为NULL。可以看出，班级编号为6的班级一个学生都没有。如果信息量太大，可以在语句最后用“WHERE学号ISNULL”来过滤空行，只显示没有学生的班级记录。为了方便查看每个字段所在的表，可以在每个字段前面加上表别名。OUTER省略。

右外连接（RIGHTOUTERJOIN）包含JOIN子句中最右侧表的所有行。如果右侧表中的行与左侧表中的行不匹配时，则将结果集中来自左侧表的所有列分配NULL值。语法规则如下：右外连接SELECT字段列表FROM表名1RIGHTOUTERJOIN表名2ON表名1.字段名=表名2.字段名参数说明如下：字段列表：查询显示的字段名的列表。表名1，表名2：查询来源的的两个表，中间用RIGHTOUTERJOIN关键字连接，OUTER关键字可以省略。ON：ON关键字后面是连接条件表名1.字段名，表名2.字段名：两个表的公共列。SELECTxk.课程编号,xk.成绩,xs.学号,xs.姓名FROM选课ASxkRIGHTJOIN学生ASxsONxk.学号=xs.学号【例6-9】在学生管理数据库里查询显示所有学生的课程编号、成绩、学号、姓名。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：显示所有行的表放在RIGHTJOIN的右边，就是右外连接，此题还是可以查看所有学生的选课信息，那么就可以看出有三个学生一门课都没有选。RIGHTJOIN右边的学生表所有行都被显示出来，学号为10、11、12的学生在选课表中没有满足条件的行，在对应表的位置上赋以NULL。SELECTbj.班级代码,bj.班级名称,xs.学号,xs.姓名

FROM学生ASxsRIGHTJOIN班级ASbjONbj.班级代码=xs.班级编号WHERExs.学号ISNULL【例6-10】在学生管理数据库里查询还没有学生的班级信息。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：题目要求没有学生的班级信息，班级表中的所有行都被显示，无论与左边表有没有匹配行，如果没有，对应的学生表中的字段被赋以NULL，最后用WHERE条件过滤学生为空的记录。左外连接和右外连接可以实现相同的功能，左外连接就把显示所有行的表放在LEFTJOIN的左边，右外连接就把显示所有行的表放在RIGHTJOIN的右边。

完全外连接（FULLOUTERJOIN）包括JOIN两边表的所有行，不论另一个表是否有匹配的值。如果没有匹配值，则分别将来自两侧表的所有列分配NULL值。语法规则如下：完全外连接SELECT字段列表FROM表名1FULLOUTERJOIN表名2ON表名1.字段名=表名2.字段名参数说明如下：字段列表：查询显示的字段名的列表。表名1，表名2：查询来源的的两个表，中间用FULLOUTERJOIN关键字连接，OUTER关键字可以省略。ON：ON关键字后面是连接条件表名1.字段名，表名2.字段名：两个表的公共列。SELECTjs.教师编号,js.教师编号,js.职称,xb.系部代码,xb.系部名称

FROM教师ASjsFULLJOIN系部ASxbONjs.系部代码=xb.系部代码【例6-11】在学生管理数据库里查询所有教师和所有系部的教师编号、教师姓名、教师职称、系部代码、系部名称。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：题目要求所有教师和所有系部的信息，两个表中的所有行都要显示，所以用完全外连接。教师表的所有记录都显示，系部表中没有匹配记录时即该教师没有分配系部的时候，对应系部信息值为NULL，如教师编号为9和10的记录。系部表的所有记录都显示，教师表中没有匹配记录时即该系部还没有教师的时候，对应值为NULL，如系部代码为5的记录。完全外连接就是对两个表先做左外连接，再做右外连接，然后对两个结果集求并集，读者可以自行实验。

交叉连接返回一个表的所有行与另外一个表的所有行的一一组合，结果集的个数为两个表的记录的个数的乘积。交叉连接可以通过两种方式实现：一种直接在FROM子句中写出连接的表名SELECT字段列表FROM表名列表另外一种使用CROSSJOIN关键字查询SELECT字段列表FROM表名1CROSSJOIN表名2交叉连接SELECT课程编号,课程名称,开课学期,课程分类,学号,姓名,性别FROM课程,学生【例6-12】在学生管理数据库里查询“课程”和“学生”表的记录的两两组合。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：FROM子句有两个表，没有WHERE条件的情况下，实现了两个表的两两组合。课程表有5条记录，学生表有10条记录，查询结果有50条记录。课程表的编号为1的记录与10个学生记录分别组合，然后再用课程编号为2的记录与10个学生的记录分别组合，依次方式，直到所有课程与所有学生的记录都组合一遍。SELECT课程编号,课程名称,开课学期,课程分类,学号,姓名,性别FROM课程CROSSJOIN学生【例6-13】在学生管理数据库里查询“课程”和“学生”表的记录的两两组合，使用CROSSJOIN实现。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：两个表用CROSSJOIN连接，没有用ON来指出连接条件。在销售数据库中查询还没有供过货的供应商的名单。1．打开SQLServerManagementStudio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“销售”；2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；3．在“查询编辑器”上输入以下代码：6.1.5应用实践SELECTgys.供应商ID,gys.名称,gys.地址,gys.电话,jh.商品IDFROM进货ASjhRIGHTJOIN供应商ASgysONgys.供应商ID=jh.供应商IDWHEREjh.商品IDISNULL4．单击工具栏上的【执行】按钮，如图所示，在进货表中没有对应的供应商的商品信息，对应的商品ID为NULL，则表示从未向此供应商进货。6.2.1情景描述6.2.2问题分析6.2.3解决方案6.2.4知识总结6.2.5应用实践任务6.2使用子查询根据学生信息管理系统的需求，开发团队需要显示“软件技术”专业和“硬件技术”专业的由哪些班级组成，可用子查询的方式实现。6.2.1情景描述为了解决上述问题，需要完成以下任务：1．根据条件专业名称，查找专业表中对应的专业代码；2．根据找到的专业代码，在班级表中查找专业代码对应的班级信息。3．把两条语句合并为子查询的语句。6.2.2问题分析

6.2.3解决方案1．打开SQLServerManagementStudio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“学生管理”；2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；3．在“查询编辑器”上输入以下代码：4．单击工具栏上的【执行】按钮，如图所示。SELECT*FROM班级

WHERE专业代码IN(SELECT专业代码

FROM专业

WHERE专业名称IN('软件技术','硬件技术'))子查询可以把一个复杂的查询分解成一系列的逻辑步骤，利用单个语句的组合解决复杂的查询问题，子查询经常使用关系运算符、IN关键字和EXISTS关键字。子查询使用关系运算符：如果子查询返回的是一个值，可以使用关系运算符进行比较。子查询使用IN关键字：有些子查询会产生一个值，这样可以在查询条件中使用关系运算符进行比较，但是有些子查询返回的是一组值，那么就要用IN关键字来匹配子查询的结果。子查询使用EXISTS关键字：带EXISTS关键字的子查询不返回任何实际数据，EXISTS只关注子查询是否返回行，仅仅测试子查询是否有记录返回。如果子查询返回的记录为空，EXISTS子查询返回FALSE，如果子查询返回的记录不为空，EXISTS子查询返回TRUE。NOTEXISTS子查询刚好相反，如果子查询返回的有记录，则返回FALSE，如果子查询返回的没有记录，则返回TRUE。6.2.4知识总结SELECT学号,姓名,性别,出生日期FROM学生WHERE班级编号=(SELECT班级代码FROM班级WHERE班级名称='计算机1202')【例6-14】在学生管理数据库里查询软件1202班的学生的学号、姓名、性别、出生日期。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：语句中小括号内的子查询，也叫内部查询，作为连接条件，括号外面的语句是外部查询。先由内部查询得出此班的班级代码，再由这个班级代码得到该班的学生信息。SELECT*FROM教师

WHERE系部代码<>(SELECT系部代码

FROM系部

WHERE系部名称='通信系')AND系部代码ISNOTNULL【例6-15】在学生管理数据库里查询所有分配了系部、并且系部名称不是“通信系”的教师信息。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：括号内是子查询。外部查询有两个条件，“有系部信息”通过条件“系部代码ISNOTNULL”来过滤，“不是通信系”这个条件就要用到括号内的子查询，求出通信系的系部代码，再用不等号<>或者!=比较结果。SELECT*FROM选课

WHERE成绩>(SELECTAVG(成绩)FROM选课

WHERE课程编号=5)【例6-16】在学生管理数据库里查询成绩高于课程编号为5的课程平均成绩的选课记录。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：括号内是子查询，子查询中也可以使用聚合函数。首先用子查询求出课程编号为5的课程平均成绩，再求出来成绩比此平均成绩还高的选课信息。

SELECT*FROM学生WHEREDATEDIFF(yy,出生日期,GETDATE())>(SELECTAVG(DATEDIFF(yy,出生日期,GETDATE()))FROM学生WHERE性别='男')【例6-17】在学生管理数据库里查询年龄大于男生平均年龄的学生信息。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：子查询得出的结果是男生的平均年龄。外部查询的条件用函数计算出学生的年龄大于子查询的结果。

SELECT学号,姓名,性别,出生日期

FROM学生WHERE学号IN(SELECT学号

FROM选课

WHERE成绩>85)【例6-18】在学生管理数据库里查询成绩高于85分的学生的学号、姓名、性别、出生日期。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：查询显示的字段和查询的条件不在一张表中，所以要分两步，先在选课表中查询成绩大于85的学号；再由子查询的结果做为外部查询的条件。子查询的结果有3个，用IN来匹配结果集。

SELECT*FROM教师

WHERE系部代码IN(SELECT系部代码

FROM系部

WHERE系部名称IN('通信系','计算机系'))【例6-19】在学生管理数据库里查询通信系和计算机系的所有教师的信息。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：首先用子查询得到通信系和计算机系的系部代码；再根据系部代码得到这两个系的教师信息。如果把IN换成NOTIN，则得到不是这两个系的教师的信息。

SELECT*FROM选课

WHERE学号IN(SELECT学号

FROM学生

WHERE班级编号=2)【例6-20】在学生管理数据库里查询班级编号为2的学生的选课成绩。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。SELECT*FROM选课

WHEREEXISTS(SELECT*FROM学生

WHERE班级编号=2AND选课.学号=学生.学号)【例6-21】在学生管理数据库里查询班级编号为2的学生的选课成绩，用EXISTS实现。①打开SQLServerManagementStudio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：②单击工具栏上的【执行】按钮，运行结果如图所示。说明：EXISTS关键字查询可以用IN来实现，与图6-23比较，查询结果相同。EXISTS子查询中的查询条件“选课.学号=学生.学号”不能省略。SELECT*FROM学生WHERENOT