授课日期
  年  月  日  第6周
授课形式
讲课
授课时数
4
章节名称
第08章 简单查询
教学目的与要求
①掌握使用Join…On…子句进行多表自然连接查询
②掌握左外连接、右外连接和完全外连接
③掌握自连接查询
④掌握子查询的分类（单行子查询、多行子查询）
⑤掌握在Where子句中使用子查询作为查询条件
⑥掌握在From子句中使用子查询作为数据源
⑦掌握使用子查询在Insert Into语句中为数据表插入另一表中的已有数据。
⑧掌握使用子查询在update语句中进行基于外表条件的数据修改。
⑨掌握使用子查询在delete from语句中进行基于外表条件的数据删除。
教学重点
连接查询、自查询
教学难点
自连接查询
教学方法和手段
讲授法结合课堂实例分析讨论
教学过程与组织
导入新课
我们已经学习了简单的数据查询。前面所讲的数据查询，数据的来源都在一张表中。更多的情况下，需要查询的数据往往分布在多张表，这就需要进行关系代数的连接运算，在连接的结果上进行查询。
讲授新课
第09章 连接查询和子查询
第01节 多表连接查询
多表查询又称作连接查询，或称作联合查询。连接查询又可分成内连接查询、外连接查询等等。内连接查询中，又可分为等值连接、非等值连接、自连接等子类。外连接中，又可分为左外连接、右外连接等。
9.1.1 内连接（自然连接）
两个表的内连接查询是指，从两个表中的相关字段中提取信息作为查询的条件，如果满足查询的条件，就从两个表中的相关记录中，选择需要的信息，连接成一个元组，置于查询结果集之中，这就是内连接的主要作用。内连接语句的格式和语法如下：
FROM 表1 [INNER] JOIN 表2 ON 连接条件表达式
连接查询的语句，主要是在单表查询的语句中，对数据源部分进行了语法成份的扩展。用以申明是哪两个表联合查询。ON之后的条件表达式，说明了连接的条件。连接条件表达式的格式有特殊要求。其一般格式为：
[表名1.]列名1 比较运算符 [表名2.]列名2
如果某个列所表示的数据在查询语句涉及到的多张表中存在，且列名相同，使用时就应该在其前面冠上表名，并以圆点（.）隔开。当确认某个列名只出现在一张表时，它前面的表名才可以省略。
连接查询语句中，SELECT之后的列名列表也应这样表示：
SELECT [表名.]列名1, …, [ 表名.]列名n FROM …
连接条件中，两个表中的比较列，必须是语义相同的列，才可以构成有意义比较条件。
例：查询每个学生的情况和选课情况
Select * From Student Inner Join SC On Student.SNo = SC.SNo
例：在上例中，只选取学号、姓名、课程号和成绩
Select SC.SNo, Student.SName, SC.CNo, SC.Grade From Student Inner Join SC On Student.SNo = SC.SNo
例：查询系编号为g001和g005各学生的选课情况和每门课的成绩，将每个系的名单排在一起
Select Student.SName, SC.CNo, SC.Grade From Student Inner Join SC On Student.SNo=SC.SNo Where Student.Depart='g001' Or Student.Depart = 'g005' Order By Student.Depart;
例：在上例中，使用表的别名
Select S.SName, SC.CNo, SC.Grade From Student S Inner Join SC On S.SNo=SC.SNo Where S.Depart = 'g001' Or S.Depart = 'g005' Order By S.Depart;
例：查询计算机系中选修了课程名为VB的课程的学生姓名和成绩
Select S.SName, C.CName, SC.Grade From Student S Join SC On S.SNo=SC.SNo Join Course C On C.CNo =SC.CNo Where S.Depart='g001' And C.CName='VB'
例：查询所有选修了VB课程的学生姓名和所在系编号
Select S.SName, S.Depart From Student S Join SC On S.SNo=SC.SNo Join Course C On C.CNo=SC.CNo Where C.CName='VB'
9.1.2 自连接
连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。
自连接的处理思想是，将物理上的一个表，从逻辑上视同两个表。使用自连接必须为同一张表取两个别名，让一个表变成表名不同的两个表，仅此而已。其余的工作与两表连接查询无任何区别。
例：查询与冯巩在同一个系学习的学生的姓名和系编号
Select S2.SName, S2.Depart From Student S1 Join Student S2 On S1.Depart=S2.Depart Where S1.SName='冯巩' And S2.SName<>'冯巩'
9.1.3 外连接
外连接的思想是，当一个表中的元组，如果在另一表中不到与其连接条件相匹配的元组时，并不将此元组的数据抛弃，而是将该元组的相关信息（结果中需要的列值）与连接的另一个表的空值列（全部取空值），也在形式上连接成新的元组，并将它置于查询结果中。这样一来，就达到了用户想保留某些信息的目的。
外连接是左外连接和右外连接的统称。语句结构和语法规则如下：
FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 连接条件
LEFT [OUTER] JOIN 称作左外连接，RIGHT [OUTER] JOIN称作右外连接。
左外连接将在查询结果中，除了输出满足自然连接条件的结果外，还输出表1里面那些在表2不到对应匹配的数据行的相关信息。
右外连接将在查询结果中，除了输出满足自然连接条件的结果外，还输出表2里面那些在表1不到对应匹配的数据行的相关信息。
例：查询每个学生的学号、姓名、所选课程、成绩，即使有学生没有选课，也要列出他的信息
Select S.No, S.SName, SC.CNo, SC.Grade From Student S Left Join SC On S.SNo=SC.SNo
例：将例5-42的左外连接改成右外连接，观察查询结果有什么不同
Select S.No, S.SName, SC.CNo, SC.Grade From Student S Right Join SC On S.SNo=SC.SNo
9.1.4 交叉连接
交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。
语法如下：
SELECT 字段列表 FROM 表1 CROSS JOIN 表2
或
SELECT 字段列表FROM 表1 ，表2
例：列出所有学生的有可能的选课组合
SELECT S.SNO, S.SNAME, C.CNO, C.CNAME FROM STUDENT S CROSS JOIN COURSE C
在实际应用中使用交叉连接产生的结果集一般没有什么意义，但在数据库的数学模式上有重要的作用。
第02节 子查询
8.2.1子查询的概念和分类
在SQL语言中，将一个SELECT FROM WHERE语句称作一个查询块。如果将一个SELECT语句嵌套在下列语句之中，则称这样一类语句为子查询语句或内层查询语句，而位于外面（包含子查询语句）的语句则称为主查询语句或外层查询语句。可以作为主查询的语句的有以下几个：
（1）SELECT语句；
（2）INSERT语句；
（3）UPDATE语句；
（4）DELETE语句。
sql中union多表合并子查询可以嵌套。
子查询往往作为在某一查询的Where条件句或Having子句的一部分，或者作为查询的某个数据源。子查询分为两种：单行子查询（子查询的返回结果只有一行）和多行子查询（字查询的结果在两行或者两行以上）。
8.2.2 使用子查询的结果作比较
当确认一个子查询语句的查询结果是一个单一值时，这样的子查询语句可以置于任何带比较运算符的条件表达式的一端，构成一个条件表达式。比较运算符可以是=、<、>、<=、>=、<>、!=等中的一个。
例： 查询选修了C02号课程且成绩比此课程的平均分高的学生的学号、成绩
Select SNo, Grade From SC Where CNo='C02' And Grade > (Select Avg(Grade) from SC Where CNo='C02')
该语句的执行过程如下（1）先执行括号内的子查询；（2）将子查询的结果代入外层查询中；（3）执行外层查询语句。
需要注意的是，当子查询的结果作为外层查询语句的条件的一部分，且外层查询语句的条件使用到了“=、<、>、<=、>=、<>、!=”这样的运算符，必须使用单行子查询。
8.2.3 使用子查询的结果作集合
当确认一个子查询语句的结果集中的值的行数大于一（多行子查询）的情况下，它不能与比较运算符一起使用，而只能使用集合运算符IN或NOT IN，将一个表达式的值与查询返回的结果集进行比较。如果表达式的值在子查询的结果集合中，条件为真，反之，条件为假。
注意，子查询语句中只能返回一组类型相同的值，且要求其返回值的数据类型与测试表达式的数据类型一致。
例：查询与冯巩在同一个系学习的学生的学号、姓名和系编号
Select SNo, SName, Depart From Student Where Depart In (Select Depart From Student Where SName='冯巩')
例：查询C06号课程的成绩90分以上的学号和姓名
Select SNo, SName From Student Where SNo In (Select SNo From SC Where Grade>=90 And CNo='C06')
或者
Select S.SNo, S.SName From Student S Join SC On S.SNo=SC.SNo Where SC.Grade>90 And SC.CNo='C06'
例：查询选修了'数据库基础'课程的学生学号和姓名
Select S.SNo, S.SName From Student S Join SC On S.SNo=SC.SNo Join Course C On C.CNo=SC.CNo Where CName='数据库基础'
或者
Select Sno, SName From Student Where SNo In (Select SNo From SC Where CNo=(Select CNo From Course Where CName='数据库基础'))
8.2.3 使用子查询进行逻辑测试
使用子查询进行逻辑测试，是使用存在性谓词EXISTS。在这个谓词后面带一个子查询语句，这一语句执行后并不返回具体数据，而只返回一个逻辑值，或者返回“真”，或者返回“假”。
例：查询选修了课程C20的学生姓名。
Select SName From Student Where Exist (Select * From SC Where SNo=Student.SNo And CNo='C20')
该语句的处理过程如下：
（1）到外层Student表的第1行，根据其SNo值处理内层查询；
（2）用外层的值执行内层查询，如果有符合条件的数据，则Exist返回True，否则返回False。如果Exist返回True，则外层结果中的当前数据为符合条件的结果；否则，是不符合条件的结果；
（3）顺序处理外层Student表的第2行、第3行……直到处理完所有的行。
如果在Exist前加上Not，则表示，当子查询语句中不存在任何满足条件的记录时，返回Ture，当子查询语句存在满足条件的记录时，返回False。
例：查询没有选修C01课程的学生姓名和所在系编号
Select SName, Depart From Student S Where Not Exist (Select * From SC Where SC.SNo=S.SNo And CNo='C01')
第03节 多个相同模式的查询结果的并、交、差
我们在关系代数中提到了两个相同关系模式的关系，能够进行交、并、差运算。在T-SQL中，相同关系模式的关系之间的交、并、差运算，分别是通过INTERSECT、UNION、EXCEPT运算符实现的。
8.3.1 Union运算符
使用UNION语句可以把两个或两个以上的查询产生的结果集合并为一个结果集。语法格式如下：
SELECT语句
UNION  [ALL]
SELECT语句
说明：
（1）UNION中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以相同的顺序出现。
（2）最后结果集中的列名来自第一个SELECT语句。
（3）若UNION中包含ORDER BY子句，则将对最后的结果集排序。
（4）在合并结果集时，默认从最后的结果集中删除重复的行，除非使用ALL关键字。
例：在同一列中显示所有的学生的学号和课程号、课程名
SELECT SNo, SName FROM Student
UNION
SELECT CNo, CName FROM Course
8.3.2 Intersect运算符
INTERSECT运算符返回INTERSECT左右两边的两个查询结果集的交集。语法格式如下：
SELECT语句
INTERSECT
SELECT语句
说明：
（1）INTERSECT中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以相同的顺序出现。
（2）最后结果集中的列名来自第一个SELECT语句。
（3）若INTERSECT中包含ORDER BY子句，则将对最后的结果集排序。
8.3.3 Except运算符
EXCEPT运算符返回EXCEPT左右两边的两个查询结果集的差集，即第一个结果集与第二个结果集的差集。语法格式如下：
SELECT语句
EXCEPT
SELECT语句
说明：
（1）EXCEPT中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以相同的顺序出现。
（2）最后结果集中的列名来自第一个EXCEPT语句。
（3）若EXCEPT中包含ORDER BY子句，则将对最后的结果集排序。
例：查询没有选课的学生的学号
SELECT SNo From Student
EXCEPT
SELECT SNo From SC
第04节 基于外表条件的数据更新
以前学习的对表的插入、修改和删除，都是基于本表条件的。事实上存在更复杂的情况：基于外表条件的数据修改和删除。
8.4.1 基于外表条件的数据修改
例： 将选修了'计算机原理'课程，且成绩低于60分的人，每人加5分
Update SC Set Grade=Grade+5 Where Grade<60 And SNo In (Select SNo From SC Join Course C On SC.CNo=C.CNo Where CName='计算机原理')
例： 取消'高等数学'课程全部已录入成绩，但保留选课记录
Update SC Set Grade=NULL Where SNo In (Select SNo From SC Join Course C On SC.CNo=C.CNo Where CName='高等数学')
8.4.2 基于外表条件的数据删除
例：删除SC表中，'动画设计'课程低于50分的选课记录
Delete From SC Where Grade<50 And CNo In (Select CNo From SC Join Course C On SC.CNo = C.CNo Where CName = '动画设计')
8.4.3 将查询结果插入新表
使用SELECT INTO语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。
语法格式为：
SELECT  目标列集合 INTO 新表 FROM 源表
[WHERE 条件1]
[GROUP BY 表达式1 [HAVING 条件2]]
[ORDER BY 表达式2[ASC|DESC]]
例：创建一个新表SC001，包含该表不设置约束，包含学号、姓名、课程号、课程名、成绩5列，并将系编号为g001的系的全体学生所修的课程信息插入新表SC001。
SELECT S.SNo SNo, S.SName, C.CNo, C.CName, SC.Grade INTO SC001 FROM Student S JOIN SC ON S.SNo=SC.SNo JOIN COURSE C ON SC.CNo=C.CNo WHERE S.Dept= 'g001'
作业
1、 查询每个系的系编号、教师总人数、平均薪水。
2、 查询编号为“05”的系中，比平均薪水低的教师的教工编号、姓名、薪水。
3、 查询职称为“助教”和“讲师”的教工的教工编号、姓名、性别、年龄、系主任姓名、所在系电话，要求使用自然连接。
4、 查询每个系的系编号、系名称、电话、主任教工号、主任姓名，如果某个系暂时没有主任，也要将这样的系显示出来。
5、 查询同姓同名的老师，显示他们的教工编号、姓名、性别、所在系编号，要求使用自连接。
6、 查询“文雄军”老师所在的系的编号、系名称，要求使用子查询。
7、 查询比院系编号为“06”的院系的教工数量更多的院系的编号、名称、教工数量、教工平均薪水，要求使用子查询。
8、 创建一张新表TeacherComputer，表的结构和Teacher表的结构完全一样，同时插入“计算机系”的所有教师信息，要求使用子查询
9、 查询职称为“助教”的教工的教工编号、姓名、性别、年龄、系主任姓名，并按照薪水从低到高排序。
教学内容的深化和拓展
课堂小结
教学后记

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。