第5章
数 据 查 询
本章要点
投影查询。
选择查询。
分组查询和统计计算。
排序查询。
连接查询。
子查询。
SELECT查询的其他子句。
TSQL中最重要的部分是它的查询功能，查询语言用来对已经存在于数据库中的数据按照特定的行、列、条件表达式或者一定次序进行检索。本章介绍投影查询、选择查询、分组查询和统计计算、排序查询、连接查询、子查询、SELECT查询的其他子句等内容。
TSQL对数据库的查询使用SELECT语句。SELECT语句具有灵活的使用方式和强大的功能。
语法格式:
SELECT select_list/*指定要选择的列*/
FROM table_source /*FROM子句，指定表或视图*/
［ WHERE search_condition ］/*WHERE子句，指定查询条件*/
［ GROUP BY group_by_expression ］/*GROUP BY子句，指定分组表达式*/
［ HAVING search_condition ］/*HAVING子句，指定分组统计条件*/
［ ORDER BY order_expression ［ ASC | DESC ］］ /*ORDER子句，指定排序表达式和顺序*/
5.1投 影 查 询
投影查询通过SELECT语句的SELECT子句来表示，由选择表中的部分或全部列组成结果表。
语法格式：
SELECT ［ ALL | DISTINCT ］ ［ TOP n ［ PERCENT ］ ［ WITH TIES ］ ］ <select_list>
select_list指出了结果的形式，其格式为
{*/*选择当前表或视图的所有列*/
| { table_name | view_name | table_alias } . * /*选择指定的表或视图的所有列*/
| { colume_name | expression | $IDENTITY | $ROWGUID }
/*选择指定的列并更改列标题，为列指定别名，还可用于为表达式结果指定名称*/
［ ［ AS ］ column_alias ］
| column_alias = expression
} ［ , ...n ］
1. 投影指定的列
使用SELECT语句可选择表中的一列或多列，如果是多列，各列名中间要用逗号分开。
语法格式：
SELECT column_name ［ , column_name...］
FROM table_name
WHERE search_condition
其中，FROM子句用于指定表，WHERE子句给出检索条件。
【例5.1】查询student表中所有学生的学号、姓名和专业。
USE stsc
SELECT stno, stname, speciality
FROM student
查询结果：
stno stname speciality
----- ------ ---------
121001李贤友 通信
121002周映雪 通信
121005刘刚 通信
122001郭德强 计算机
122002谢萱 计算机
122004孙婷 计算机
2. 投影全部列
在SELECT子句指定列的位置上使用*号时，则为查询表中所有列。
【例5.2】查询student表中所有列。
USE stsc
SELECT *
FROM student
该语句与下面语句等价:
USE stsc
SELECT stno, stname, stsex, stbirthday, speciality, tc
FROM student
查询结果：
stno stname stsex stbirthday specialitytc
---------------------------------------
121001 李贤友 男 1991-12-30通信 52
121002 周映雪 女 1993-01-12通信 49
121005 刘刚 男 1992-07-05通信 50
122001 郭德强 男 1991-10-23计算机 48
122002 谢萱 女 1992-09-11计算机 52
122004 孙婷 女 1992-02-24计算机 50
3. 修改查询结果的列标题
为了改变查询结果中显示的列标题，可以在列名后使用AS子句。
语法格式：
AS column_alias
其中,column_alias指定显示的列标题，AS可省略。
【例5.3】查询student表中通信专业学生的stno、stname、tc，并将结果中各列的标题分别修改为学号、姓名、总学分。
USE stsc
SELECT stno AS '学号', stname AS '姓名', tc AS '总学分'
FROM student
查询结果：
学号姓名 总学分
----------- ------
121001李贤友 52
121002周映雪 49
121005刘刚 50
122001郭德强 48
122002谢萱 52
122004孙婷 50
4. 去掉重复行
去掉结果集中的重复行可使用DISTINCT关键字。
语法格式：
SELECT DISTINCT column_name ［ , column_name...］
【例5.4】查询student表中speciality列，消除结果中的重复行。
USE stsc
SELECT DISTINCT speciality
FROM student
查询结果：
speciality
--------
计算机
通信
5.2选 择 查 询
选择查询通过WHERE子句实现，WHERE子句给出查询条件，该子句必须紧跟在FROM子句之后。
语法格式：
WHERE <search_condition>
其中,search_condition为查询条件。<search_condition>的语法格式如下：
{ ［ NOT ］ <predicate> | (<search_condition> ) }
［ { AND | OR } ［ NOT ］ { <predicate> | (<search_condition>) } ］
} ［ ,...n ］
其中,predicate为判定运算。<predicate>的语法格式如下：
{ expression { = | < | <= | > | >= | <> | != | !< | !> } expression /*比较运算*/
| string_expression ［ NOT ］ LIKE string_expression ［ ESCAPE 'escape_character' ］
/*字符串模式匹配*/
| expression ［ NOT ］ BETWEEN expression AND expression /*指定范围*/
| expression IS ［ NOT ］ NULL /*是否空值判断*/
| CONTAINS ( { column | * },'<contains_search_condition>') /*包含式查询*/
| FREETEXT ({ column | * },'freetext_string') /*自由式查询*/
| expression ［ NOT ］ IN ( subquery | expression ［,...n］ )/*IN子句*/
| expression { = | < | <= | > | >= | <> | != | !< | !> } { ALL | SOME | ANY } ( subquery )
/*比较子查询*/
| EXIST ( subquery ) /*EXIST子查询*/
}
现将WHERE子句的常用查询条件列于表5.1中，以使读者更清楚地了解查询条件。
表5.1WHERE子句的常用查询条件
查 询 条 件谓词
比较<=, <, =,>=, >,
!=, <>, !>, !<
指定范围BETWEEN AND, NOT BETWEEN AND
确定集合IN, NOT IN
字符匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
多重条件AND, OR
说明： 在SQL中，返回逻辑值的运算符或关键字都称为谓词。
1. 表达式比较
比较运算符用于比较两个表达式的值。
语法格式：
expression { = | < | <= | > | >= | <> | != | !< | !> } expression
其中，expression是除text、ntext和image之外类型的表达式。
【例5.5】查询student表中专业为计算机或性别为女的学生。
USE stsc
SELECT *
FROM student
WHERE speciality='计算机' or stsex='女'
查询结果：
stno stname stsexstbirthday speciality tc
------------------------------------------
121002周映雪女 1993-01-12通信 49
122001郭德强男 1991-10-23计算机 48
122002谢萱女 1992-09-11计算机 52
122004孙婷女 1992-02-24计算机 50
2. 范围比较
BETWEEN、NOT BETWEEN、IN是用于范围比较的3个关键字，用于查找字段值在(或不在)指定范围的行。
【例5.6】查询score表中成绩为82、91、95的记录。
USE stsc
SELECT *
FROM score
WHERE grade in (82,91,95)
查询结果：
stno cno grade
---------------
121001205 91
121005801 82
122002801 95
3. 模式匹配
字符串模式匹配使用LIKE谓词。
语法格式：
string_expression ［ NOT ］ LIKE string_expression ［ ESCAPE 'escape_character'］
其含义是查找指定列值与匹配串相匹配的行，匹配串(即string_expression)可以是一个完整的字符串，也可以含有通配符。通配符有以下两种。
%： 代表0个或多个字符。
_： 代表一个字符。
LIKE匹配中使用通配符的查询也称模糊查询。
【例5.7】查询student表中姓孙的学生情况。
USE stsc
SELECT *
FROM student
WHERE stname LIKE '孙%'
查询结果：
stno stname stsex stbirthday speciality tc
-------------------------------------------
122004孙婷 女 1992-02-24计算机 50
4. 空值使用
空值是未知的值。判定一个表达式的值是否为空值时，使用IS NULL关键字。
语法格式：
expression IS ［ NOT ］ NULL
【例5.8】查询已选课但未参加考试的学生情况。
USE stsc
SELECT *
FROM score
WHERE grade IS NULL
查询结果：
stno cno grade
----------------
122001 801NULL
5.3分组查询和统计计算
检索数据常常需要进行分组查询和统计计算，本节介绍使用聚合函数、GROUP BY子句、HAVING子句进行分组查询和统计计算的方法。
1. 聚合函数
聚合函数实现数据统计或计算，用于计算表中的数据，返回单个计算结果。除COUNT函数外，聚合函数忽略空值。
SQL Server提供的常用的聚合函数如表5.2所示。
聚合函数一般参数的语法格式如下：
( ［ ALL | DISTINCT ］ expression )
其中，ALL表示对所有值进行聚合函数运算，ALL为默认值; DISTINCT表示去除重复值; expression指定进行聚合函数运算的表达式。
表5.2常用的聚合函数
函数名功能
AVG求组中数值的平均值
COUNT求组中项数
MAX求最大值
MIN求最小值
SUM返回表达式中数值的总和
STDEV返回给定表达式中所有数值的统计标准偏差
STDEVP返回给定表达式中所有数值的填充的统计标准偏差
VAR返回给定表达式中所有数值的统计方差
VARP返回给定表达式中所有数值的填充的统计方差
【例5.9】查询102课程的最高分、最低分、平均成绩。
USE stsc
SELECT MAX(grade) AS '最高分',MIN(grade) AS '最低分',AVG(grade) AS '平均成绩'
FROM score
WHERE cno='102'
该语句采用MAX求最高分、MIN求最低分、AVG求平均成绩。
查询结果：
最高分 最低分 平均成绩
------------------------------
9272 83
【例5.10】求学生的总人数。
USE stsc
SELECT COUNT(*) AS '总人数'
FROM student
该语句采用COUNT(*)计算总行数，总人数与总行数一致。
查询结果：
总人数
------
6
【例5.11】查询计算机专业学生的总人数。
USE stsc
SELECT COUNT(*) AS '总人数'
FROM student
WHERE speciality='计算机'
该语句采用COUNT(*)计算总人数，并用WHERE子句将指定的条件限定为计算机专业。
查询结果：
总人数
------
3
2. GROUP BY子句
GROUP BY子句用于将查询结果表按某一列或多列值进行分组。
语法格式：
［ GROUP BY ［ ALL ］ group_by_expression ［,...n］
［ WITH { CUBE | ROLLUP } ］ ］
其中，group_by_expression为分组表达式，通常包含字段名； ALL显示所有分组； WITH指定CUBE或ROLLUP操作符，在查询结果中增加汇总记录。
注意： 聚合函数常与GROUP BY子句一起使用。
【例5.12】查询各门课程的最高分、最低分、平均成绩。
USE stsc
SELECT cno AS '课程号', MAX(grade)AS '最高分',MIN (grade)AS '最低分', AVG(grade)AS '平均成绩'
FROM score
WHERE NOT grade IS null
GROUP BY cno
该语句采用MAX、MIN、AVG等聚合函数，并用GROUP BY子句对cno(课程号)进行分组。
查询结果：
课程号最高分 最低分 平均成绩
-----------------------------------
102 92 72 83
203 94 81 87
205 91 65 80
801 95 73 86
提示： 如果SELECT子句的列名表包含聚合函数，则该列名表只能包含聚合函数指定的列名和GROUP BY子句指定的列名。
【例5.13】求选修各门课程的平均成绩和选修人数。
USE stsc
SELECT cno AS '课程号', AVG(grade) AS '平均成绩', COUNT(*) AS '选修人数'
FROM score
GROUP BY cno
该语句采用AVG、COUNT等聚合函数，并用GROUP BY子句对cno (课程号)进行分组。
查询结果：
课程号平均成绩选修人数
---------------------------
10283 3
20387 2
20580 3
80186 6
3. HAVING子句
HAVING子句用于对分组按指定条件进一步进行筛选，最后只输出满足指定条件的分组。
语法格式：
［ HAVING <search_condition> ］
其中，search_condition为查询条件，可以使用聚合函数。
当WHERE子句、GROUP BY子句、HAVING子句在一个SELECT语句中时，执行顺序如下。
(1) 执行WHERE子句，在表中选择行。
(2) 执行GROUP BY子句，对选取行进行分组。
(3) 执行聚合函数。
(4) 执行HAVING子句，筛选满足条件的分组。
【例5.14】查询选修课程2门以上且成绩在80分以上的学生的学号。
USE stsc
SELECT stno AS '学号', COUNT(cno) AS '选修课程数'
FROM score
WHERE grade>=80
GROUP BY stno
HAVING COUNT(*)>=2
该语句采用COUNT聚合函数、WHERE子句、GROUP BY子句、HAVING子句进行查询。
查询结果：
学号 选修课程数
---------------
121001 3
121005 3
122002 2
122004 2
【例5.15】查询至少有4名学生选修且以8开头的课程号和平均分数。
USE stsc
SELECT cno AS '课程号', AVG (grade) AS '平均分数'
FROM score
WHERE cno LIKE '8%'
GROUP BY cno
HAVING COUNT(*)>4
该语句采用AVG聚合函数、WHERE子句、GROUP BY子句、HAVING子句进行查询。
查询结果：
课程号平均分数
---- ---------
80186
5.4排 序 查 询
SELECT语句的ORDER BY子句用于对查询结果按升序(ASC,默认)或降序(DESC)排列行，可按照一个或多个字段的值进行排序。
语法格式：
［ ORDER BY { order_by_expression ［ ASC | DESC ］ } ［ ,...n ］
其中，order_by_expression是排序表达式，可以是列名、表达式或一个正整数。
【例5.16】将计算机专业的学生按出生时间先后排序。
USE stsc
SELECT *
FROM student
WHERE speciality='计算机'
ORDER BY stbirthday
该语句采用ORDER BY子句进行排序。
查询结果：
stno stname stsex stbirthday speciality tc
---------------------------------------------
122001郭德强男 1991-10-23计算机 48
122004孙婷女 1992-02-24计算机 50
122002谢萱女 1992-09-11计算机 52
【例5.17】将通信专业学生按“数字电路”课程成绩降序排序。
USE stsc
SELECT a.stname, b.cname, c.grade
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND b.cname='数字电路' AND a.speciality='通信'
ORDER BY c.grade DESC
该语句采用谓词连接和ORDER BY子句进行排序。
查询结果：
stname cnamegrade
--------------------------
李贤友 数字电路 92
刘刚 数字电路 87
周映雪 数字电路 72
5.5连 接 查 询
当一个查询涉及两个或多个表的数据，需要指定连接列进行连接查询。
连接查询是关系数据库中的重要查询，在TSQL中，连接查询有两大类表示形式： 一类是用连接谓词表示形式； 另一类是使用关键字JOIN表示形式。
5.5.1连接谓词
在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。连接谓词又称为连接条件。
语法格式：
［<表名1.>］ <列名1> <比较运算符> ［<表名2.>］ <列名2>
比较运算符有<、<=、=、>、>=、!=、<>、!<、!>。
连接谓词还有以下形式：
［<表名1.>］ <列名1> BETWEEN ［<表名2.>］ <列名2>AND［<表名2.>］ <列名3>
由于连接多个表存在公共列，为了区分是哪个表中的列，引入表名前缀指定连接列。例如，student.stno表示student表的stno列，score.stno表示score表的stno列。
为了简化输入，TSQL允许在查询中使用表的别名，可在FROM子句中为表定义别名，然后在查询中引用。
经常用到的连接如下。
等值连接。表之间通过比较运算符“=”连接起来，称为等值连接。
非等值连接。表之间使用非等号进行连接，称为非等值连接。
自然连接。如果在目标列中去除相同的字段名，称为自然连接。
自连接。将同一个表进行连接，称为自连接。
【例5.18】查询学生的情况和选修课程的情况。
USE stsc
SELECT student.*, score.*
FROM student, score
WHERE student.stno=score.stno
该语句采用等值连接。
查询结果：
stno stname stsex stbirthday speciality tcstno cno grade
-----------------------------------------------------------
121001 李贤友男 1991-12-30 通信 52 121001 102 92
121001 李贤友男 1991-12-30 通信 52 121001 205 91
121001 李贤友男 1991-12-30 通信 52 121001 801 94
121002 周映雪女 1993-01-12 通信 49 121002 102 72
121002 周映雪女 1993-01-12 通信 49 121002 205 65
121002 周映雪女 1993-01-12 通信 49 121002 801 73
121005 刘刚男 1992-07-05 通信 50 121005 102 87
121005 刘刚男 1992-07-05 通信 50 121005 205 85
121005 刘刚男 1992-07-05 通信 50 121005 801 82
122001 郭德强男 1991-10-23 计算机 48 122001 801 NULL
122002 谢萱女 1992-09-11 计算机 52 122002 203 94
122002 谢萱女 1992-09-11 计算机 52 122002 801 95
122004 孙婷女 1992-02-24 计算机 50 122004 203 81
122004 孙婷女 1992-02-24 计算机 50 122004 801 86
【例5.19】对上例进行自然连接查询。
USE stsc
SELECT student.*, score.cno, score.grade
FROM student, score
WHERE student.stno=score.stno
该语句采用自然连接。
查询结果：
stnostname stsex stbirthday speciality tccnograde
-----------------------------------------------------
121001李贤友男 1991-12-30通信 5210292
121001李贤友男 1991-12-30通信 5220591
121001李贤友男 1991-12-30通信 5280194
121002周映雪女 1993-01-12通信 4910272
121002周映雪女 1993-01-12通信 4920565
121002周映雪女 1993-01-12通信 4980173
121005刘刚男 1992-07-05通信 5020585
121005刘刚男 1992-07-05通信 5080182
122001郭德强男 1991-10-23计算机 48801NULL
122002谢萱女 1992-09-11计算机 5220394
122002谢萱女 1992-09-11计算机 5280195
122004孙婷女 1992-02-24计算机 5020381
122004孙婷女 1992-02-24计算机 5080186
【例5.20】查询选修了“微机原理”课程且成绩在80分以上的学生姓名。
USE stsc
SELECT a.stno, a.stname, b.cname, c.grade
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND b.cname='微机原理' AND C.grade>=80
该语句实现了多表连接，并采用别名以缩写表名。
查询结果：
stno stname cnamegrade
--------------------------
121001 李贤友微机原理 91
121005 刘刚微机原理 85
说明： 本例中为student表指定的别名是a, 为course表指定的别名是b, 为score表指定的别名是c。
【例5.21】查询选修了801课程的成绩高于学号121002的成绩的学生姓名。
USE stsc
SELECT a.cno, a.stno, a.grade
FROM score a, score b
WHERE a.cno='801' AND a.grade>b.grade AND b.stno='121002' AND b.cno='801'
ORDER BY a.grade DESC
该语句实现了自连接，使用自连接需要为一个表指定两个别名。
查询结果：
cno stno grade
----------------
801 122002 95
801 121001 94
801 122004 86
801 121005 82
5.5.2以JOIN为关键字指定的连接
TSQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。
JOIN连接在FROM子句的< joined_table >中指定。
语法格式：
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| <joined_table>
}
其中，
<join_type>为连接类型，ON用于指定连接条件。<join_type>的格式如下：
INNER|{LEFT|RIGHT|FULL}［OUTER］［<join_hint>］JOIN
INNER表示内连接； OUTER表示外连接； CROSS表示交叉连接。此为JOIN关键字指定的连接的3种类型。
1. 内连接
内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。
内连接是系统默认的，可省略INNER关键字。
【例5.22】查询学生的情况和选修课程的情况。
USE stsc
SELECT *
FROM student INNER JOIN score ON student.stno=score.stno
该语句采用内连接，查询结果与例5.18相同。
【例5.23】查询选修了102课程且成绩在85分以上的学生情况。
USE stsc
SELECT a.stno, a.stname, b.cno, b.grade
FROM student a JOIN score b ON a.stno=b.stno
WHERE b.cno='102' AND b.grade>=85
该语句采用内连接，省略INNER关键字，使用了WHERE子句。
查询结果：
stno stname cno grade
------------------------
121001 李贤友 102 92
121005 刘刚 102 87
2. 外连接
在内连接的结果表只有满足连接条件的行才能作为结果输出。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接有以下3种。
左外连接(LEFT OUTER JOIN)： 结果表中除了包括满足连接条件的行外，还包括左表的所有行。
右外连接(RIGHT OUTER JOIN)： 结果表中除了包括满足连接条件的行外，还包括右表的所有行。
完全外连接(FULL OUTER JOIN)： 结果表中除了包括满足连接条件的行外，还包括两个表的所有行。
【例5.24】采用左外连接查询教师任课情况。
USE stsc
SELECT tname, cno
FROM teacher LEFT JOIN lecture ON (teacher.tno= lecture.tno)
该语句采用左外连接。
查询结果：
tnamecno
----------
刘林卓 102
周学莉 NULL
吴波 203
王冬琴 205
李伟 801
【例5.25】采用右外连接查询教师任课情况。
USE stsc
SELECT tno, cname
FROM lecture RIGHT JOIN course ON (course.cno=lecture.cno)
该语句采用右外连接。
查询结果：
tno cname
---------------
102101数字电路
204101数据库系统
204107微机原理
NULL计算机网络
801102高等数学
注意： 外连接只能对两个表进行。
3. 交叉连接
【例5.26】采用交叉连接查询教师和课程的所有可能组合。
USE stsc
SELECT teacher.tname,course.cname
FROM teacher CROSS JOIN course
该语句采用交叉连接。
查询结果：
tnamecname
------------------
刘林卓 数字电路
周学莉 数字电路
吴波 数字电路
王冬琴 数字电路
李伟 数字电路
刘林卓 数据库系统
周学莉 数据库系统
吴波 数据库系统
王冬琴 数据库系统
李伟 数据库系统
刘林卓 微机原理
周学莉 微机原理
吴波 微机原理
王冬琴 微机原理
李伟 微机原理
刘林卓 计算机网络
周学莉 计算机网络
吴波 计算机网络
王冬琴 计算机网络
李伟 计算机网络
刘林卓 高等数学
周学莉 高等数学
吴波 高等数学
王冬琴 高等数学
李伟 高等数学
5.6子查询
在SQL中，一个SELECT…FROM…WHERE语句称为一个查询块。在WHERE子句或HAVING子句所指定的条件中，可以使用另一个查询块的查询结果作为条件的一部分，这种将一个查询块嵌套在另一个查询块的子句指定条件中的查询称为嵌套查询。例如：
SELECT *
FROM student
WHERE stno IN
( SELECT stno
FROM score
WHERE cno='203'
)
在本例中，下层查询块SELECT stno FROM score WHERE cno='203'的查询结果，作为上层查询块SELECT * FROM student WHERE stno IN的查询条件，上层查询块称为父查询或外层查询，下层查询块称为子查询或内层查询。嵌套查询的处理过程是由内向外，即由子查询到父查询，子查询的结果作为父查询的查询条件。
TSQL允许使用SELECT多层嵌套，即一个子查询可以嵌套其他子查询，以增强查询能力。
子查询通常与IN、EXISTS谓词和比较运算符结合使用。
5.6.1IN子查询
IN子查询用于进行一个给定值是否在子查询结果集中的判断。
语法格式：
expression ［ NOT ］ IN ( subquery )
当表达式expression与子查询subquery的结果集中的某个值相等时，IN谓词返回TRUE，否则返回FALSE； 若使用了NOT，则返回的值相反。
【例5.27】查询选修了课程号为203的课程的学生情况。
USE stsc
SELECT *
FROM student
WHERE stno IN
( SELECT stno
FROM score
WHERE cno='203'
)
该语句采用IN子查询。
查询结果：
stno stname stsexstbirthdayspeciality tc
-------------------------------------------
122002 谢萱 女 1992-09-11计算机52
122004 孙婷 女 1992-02-24计算机50
【例5.28】查询选修某课程的学生多于4人的任课教师姓名。
USE stsc
SELECT tname AS '教师姓名'
FROM teacher
WHERE tno IN
(SELECT tno
FROM lecture
WHERE cno IN
(SELECT b.cno
FROM course a, score b
WHERE a.cno=b.cno
GROUP BY b.cno
HAVING COUNT(b.cno)>4
)
)
该语句采用IN子查询，在子查询中使用了谓词连接、GROUP BY子句和HAVING子句。
查询结果：
教师姓名
-----------
李伟
5.6.2比较子查询
比较子查询是指父查询与子查询之间用比较运算符进行关联。
语法格式：
expression { < | <= | = | > | >= | != | <> | !< | !> } { ALL | SOME | ANY } ( subquery )
其中，expression为要进行比较的表达式； subquery是子查询； ALL、SOME和ANY是对比较运算的限制。
【例5.29】查询比所有计算机专业学生年龄都小的学生。
USE stsc
SELECT *
FROM student
WHERE stbirthday >ALL
( SELECT stbirthday
FROM student
WHERE speciality='计算机'
)
该语句采用比较子查询。
查询结果：
stno stname stsex stbirthday speciality tc
----------------------------------------------
121002 周映雪 女 1993-01-12通信 49
【例5.30】查询课程号801的成绩高于课程号205成绩的学生。
USE stsc
SELECT stno AS '学号'
FROM score
WHERE cno='801' AND grade>= ANY
( SELECT grade
FROM score
WHERE cno='205'
)
该语句采用比较子查询。
查询结果：
学号
---------
121001
121002
121005
122002
122004
5.6.3EXISTS子查询
EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE; 如果为NOT EXISTS，其返回值与EXIST相反。
语法格式：
［ NOT ］ EXISTS ( subquery )
【例5.31】查询选修205课程的学生姓名。
USE stsc
SELECT stname AS '姓名'
FROM student
WHERE EXISTS
( SELECT *
FROM score
WHERE score.stno=student.stno AND cno= '205'
)
该语句采用EXISTS子查询。
查询结果：
姓名
---------
李贤友
周映雪
刘刚
【例5.32】查询所有任课教师的姓名和所在的学院。
USE stsc
SELECT tname AS '教师姓名', school AS '学院'
FROM teacher
WHERE tno IN
(SELECT tno
FROM lecture a
WHERE EXISTS
(SELECT *
FROM course b
WHERE a.cno=b.cno
)
)
该语句采用EXISTS子查询。
查询结果：
教师姓名 学院
--------------------
刘林卓 通信学院
吴波 计算机学院
王冬琴 计算机学院
李伟 数学学院
提示： 子查询和连接往往都要涉及两个表或多个表，其区别是连接可以合并两个表或多个表的数据，而带子查询的SELECT语句的结果只能来自一个表。
5.7SELECT查询的其他子句
SELECT查询的其他子句包括UNION、EXCEPT和INTERSECT、INTO子句、CTE子句、FROM子句和TOP谓词等，下面分别介绍。
1. UNION
使用UNION可以将两个或多个SELECT查询的结果合并成一个结果集。
语法格式：
{ <query specification> | (<query expression> ) }
UNION ［ ALL ］ <query specification> | (<query expression> )
［ UNION ［ ALL ］ <query specification> | (<query expression> ) ［...n］ ］
说明： <query specification>和<query expression>都是SELECT查询语句。
使用UNION合并两个查询的结果集的基本规则：
所有查询中的列数和列的顺序必须相同；
数据类型必须兼容。
【例5.33】查询总学分大于50分及学号小于121051的学生。
USE stsc
SELECT *
FROM student
WHERE tc>50
UNION
SELECT *
FROM student
WHERE stno<121051
该语句采用UNION将两个查询的结果合并成一个结果集。
查询结果：
stnostnamestsexstbirthday specialitytc
-------------------------------------------
121001 李贤友 男1991-12-30 通信 52
121002 周映雪 女1993-01-12 通信 49
121005 刘刚 男1992-07-05 通信 50
122002 谢萱 女1992-09-11 计算机 52
2. EXCEPT和INTERSECT
EXCEPT和INTERSECT用于比较两个查询结果，返回非重复值。其中，EXCEPT 从左查询中返回右查询没有找到的所有非重复值； INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。
语法格式：
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
说明：
<query specification>和<query expression>都是SELECT查询语句。
使用 EXCEPT 或 INTERSECT 的两个查询的结果集组合起来的基本规则：
所有查询中的列数和列的顺序必须相同；
数据类型必须兼容。
【例5.34】查询学过801课程但未学过102课程的学生。
USE stsc
SELECT a.stno AS '学号', a.stname AS '姓名'
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND c.cno='801'
EXCEPT
SELECT a.stno AS '学号', a.stname AS '姓名'
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND c.cno='102'
该语句从 EXCEPT 操作数左侧的查询返回右侧查询没有找到的所有非重复值。
查询结果：
学号姓名
-----------
122001郭德强
122002谢萱
122004孙婷
【例5.35】查询既学过801课程又学过102课程的学生。
USE stsc
SELECT a.stno AS '学号', a.stname AS '姓名'
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND c.cno='801'
INTERSECT
SELECT a.stno AS '学号', a.stname AS '姓名'
FROM student a, course b, score c
WHERE a.stno=c.stno AND b.cno=c.cno AND c.cno='102'
该语句输出从INTERSECT 操作数左右两边的两个查询语句找到的所有非重复值。
查询结果：
学号 姓名
------------
121001 李贤友
121002 周映雪
121005 刘刚
3. INTO子句
INTO子句用于创建新表并将查询所得的结果插入新表中。
语法格式：
［ INTO new_table ］
说明：
new_table是要创建的新表名，创建的新表的结构由SELECT所选择的列决定，新表中的记录由SELECT的查询结果决定，若SELECT的查询结果为空，则创建一个只有结构而没有记录的空表。
【例5.36】由student表创建st表，包括学号、姓名、性别、专业和学分。
USE stsc
SELECT stno, stname, stsex, speciality, tc INTO st
FROM student
该语句通过INTO子句创建新表st，新表的结构和记录由SELECT…INTO语句决定。
4. CTE子句
CTE子句用于指定临时结果集，这些结果集称为公用表表达式(common table expression, CTE)。
语法格式：
［ WITH <common_table_expression> ［ ,...n ］ ］
AS ( CTE_query_definition )
其中：
<common_table_expression>::=
expression_name ［ ( column_name ［ ,...n ］ ) ］
说明：
expression_name： CTE的名称。
column_name： 在CTE中指定的列名，其个数要和CTE_query_definition返回的字段个数相同。
CTE_query_definition： 指定一个其结果集填充CTE的SELECT 语句。CTE下方的SELECT语句可以直接查询CTE中的数据。
注意： CTE源自简单查询，并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中。CTE可以包括对自身的引用，这种表达式称为递归公用表表达式。
【例5.37】使用CTE从score表中查询学号、课程号和成绩，并指定新列名分别为c_stno、c_cno和c_grade，再使用SELECT语句从CTE和student表中查询姓名为“孙婷”的学号、课程号和成绩。
USE stsc;
WITH cte_st(c_stno, c_cno, c_grade)
AS (SELECT stno, cno, grade FROM score)
SELECT c_stno, c_cno, c_grade
FROM cte_st, student
WHERE student.stname='孙婷' AND student.stno =cte_st.c_stno
该语句通过CTE子句查询姓名为“孙婷”的学号、课程号和成绩。
查询结果：
c_stno c_cnoc_grade
-------------------
12200420381
12200480186
【例5.38】计算从1到10的阶乘。
WITH Cfact(n, k)
AS (
SELECT n=1, k=1
UNION ALL
SELECT n=n+1, k=k*(n+1)
FROM Cfact
WHERE n<10
)
SELECT n, k FROM Cfact
该语句通过递归公用表表达式计算从1到10的阶乘。
查询结果：
n k
-----------------
1 1
2 2
3 6
4 24
5 120
6 720
7 5040
8 40320
9 362880
10 3628800
5. FROM子句
FROM子句指定用于SELECT的查询对象。
语法格式：
［ FROM {<table_source>} ［,...n］ ］
<table_source> ::=
{
table_or_view_name ［ ［ AS ］ table_alias ］/*查询表或视图，可指定别名*/
| rowset_function ［ ［ AS ］ table_alias ］ /*行集函数*/
［ ( bulk_column_alias ［ ,...n ］ ) ］
| user_defined_function ［ ［ AS ］ table_alias ］ /*指定表值函数*/
| OPENXML <openxml_clause> /*XML文档*/
| derived_table ［ AS ］ table_alias ［ ( column_alias ［ ,...n ］ ) ］/*子查询*/
| <joined_table> /*连接表*/
| <pivoted_table> /*将行转换为列*/
| <unpivoted_table> /*将列转换为行*/
}
说明：
table_or_view_name： 指定SELECT语句要查询的表或视图。
rowset_function： 一个行集函数，行集函数通常返回一个表或视图。
derived_table: 由SELECT查询语句的执行而返回的表，必须为其指定一个别名，也可以为列指定别名。
joined_table： 连接表。
pivoted_table： 将行转换为列。
<pivoted_table>的格式如下：
<pivoted_table> ::=
table_source PIVOT <pivot_clause> ［AS］ table_alias
<pivot_clause> ::=
( aggregate_function ( value_column ) FOR pivot_columnIN (<column_list>) )
<unpivoted_table>： 将列转换为行。
<unpivoted_table>的格式如下：
<unpivoted_table> ::=
table_source UNPIVOT <unpivot_clause> table_alias
<unpivot_clause> ::=
( value_column FOR pivot_column IN ( <column_list> ) )
【例5.39】查找student表中1992年12月31日以前出生的学生的姓名和性别，并列出其专业属于通信还是计算机，1表示是，0表示否。
USE stsc
SELECT stname, stsex,通信,计算机
FROM student
PIVOT
(
COUNT(stno)
FOR speciality
IN (通信,计算机)
)AS pvt
WHERE stbirthday<'1992-12-31'
该语句通过PIVOT子句将通信、计算机等行转换为列。
查询结果：
stname stsex通信计算机
--------------------------
郭德强 男 0 1
李贤友 男 1 0
刘刚 男 1 0
孙婷 女 0 1
谢萱 女 0 1
【例5.40】将teacher表中“职称”和“学院”列转换为行输出。
USE stsc
SELECT tno,tname,选项,内容
FROM teacher
UNPIVOT
(
内容
FOR 选项 IN
(title,school)
) unpvt
该语句通过UNPIVOT子句将“职称”和“学院”列转换为行。
查询结果：
tnotname选项内容
------------------------------
102101 刘林卓 title教授
102101 刘林卓 school 通信学院
102105 周学莉 title讲师
102105 周学莉 school 通信学院
204101 吴波 title教授
204101 吴波 school 计算机学院
204107 王冬琴 title副教授
204107 王冬琴 school 计算机学院
801102 李伟 title副教授
801102 李伟 school 计算机学院
6. TOP谓词
使用SELECT语句进行查询时，有时需要列出前几行数据，可以使用TOP谓词对结果集进行限定。
语法格式：
TOP n ［ percent ］ ［ WITH TIES］
说明：
TOP n： 获取查询结果的前n行数据。
TOP n percent： 获取查询结果的前n%行数据。
WITH TIES： 包括最后一行取值并列的结果。
注意： TOP谓词写在SELECT单词后面。使用TOP谓词时，应与ORDER BY子句一起使用，列出前几行才有意义。如果选用WITH TIES选项，则必须使用ORDER BY子句。
【例5.41】查询总学分前2名的学生情况。
USE stsc
SELECT TOP 2 stno,stname,tc
FROM student
ORDER BY tc DESC
该语句通过TOP谓词与ORDER BY子句一起使用，获取前2名的学生情况。
查询结果：
stno stname tc
-----------------
121001李贤友 52
122002谢萱 52
【例5.42】查询总学分前3名的学生情况(包含专业)。
USE stsc
SELECT TOP 3 WITH TIES stno,stname,speciality,tc
FROM student
ORDER BY tc DESC
该语句通过TOP谓词，选用WITH TIES选项并与ORDER BY子句一起使用，获取前3名的学生情况。其中，孙婷与刘刚并列第3。
查询结果：
stno stname speciality tc
---------------------------
121001李贤友 通信 52
122002谢萱 计算机 52
122004孙婷 计算机 50
121005刘刚 通信 50
5.8综 合 训 练
1. 训练要求
本章介绍TSQL中数据定义语言(DDL)、数据操纵语言(DML)和数据查询语言(DQL)。数据库查询是数据库的核心操作，重点讨论了SELECT查询语句对数据库进行各种查询的方法。下面结合stsc学生成绩数据库进行数据查询的综合训练。
(1) 查询student表中通信专业学生的情况。
(2) 查询score表中学号为122002，课程号为203的学生成绩。
(3) 查找学号为121005，课程名为“高等数学”的学生成绩。
(4) 查找选修了801课程且为计算机专业学生的姓名及成绩，查出的成绩按降序排列。
(5) 查找学号为121001的学生所有课程的平均成绩。
2. TSQL语句编写
根据题目要求，进行语句编写。
(1) 编写TSQL语句如下。
USE stsc
SELECT *
FROM student
WHERE speciality='通信'
查询结果：
stno stname stsex stbirthday speciality tc
--------------------------------------------
121001 李贤友 男1991-12-30通信52
121002 周映雪 女1993-01-12通信49
121005 刘刚 男1992-07-05通信50
(2) 编写TSQL语句如下。
USE stsc
SELECT *
FROM score
WHERE stno='122002' and cno='203'
查询结果：
stno cnograde
---------------
122002 20394
(3) 编写TSQL语句如下。
USE stsc
SELECT *
FROM score
WHERE stno='121005' and cno IN
( SELECT cno
FROM course
WHERE cname='高等数学'
)
该语句在子查询中，由课程名查出课程号； 在外查询中，由课程号(在子查询中查出)和学号查出成绩。
查询结果：
stno cnograde
------------------
121005 80182
(4) 编写TSQL语句如下。
USE stsc
SELECT a.stname,c.grade
FROM student a,course b,score c
WHERE b.cno='801' and a.stno=c.stno and b.cno=c.cno
ORDER BY grade DESC
该语句采用连接查询和ORDER子句进行查询。
查询结果：
stname grade
------------
谢萱 95
李贤友 94
孙婷 86
刘刚 82
周映雪 73
郭德强 NULL
(5) 编写TSQL语句如下。
USE stsc
SELECT stno,avg(grade) AS 平均成绩
FROM score
WHERE stno='121001'
GROUP BY stno
该语句采用聚合函数和GROUP子句进行查询。
查询结果：
stno 平均成绩
--------------
121001 92
5.9小结
本章主要介绍了以下内容。
(1) TSQL中最重要的部分是它的查询功能，查询是TSQL的核心，查询使用SELECT语句，包含SELECT子句、FROM子句、WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句等。
(2) 投影查询、选择查询和排序查询。
投影查询通过SELECT语句的SELECT子句来表示，由选择表中的部分或全部列组成结果表。
选择查询通过WHERE子句实现，WHERE子句给出查询条件，该子句必须紧跟在FROM子句之后。
排序查询通过ORDER BY子句实现，查询结果按升序(默认或ASC)或降序(DESC)排列行，可按照一个或多个字段的值进行排序。
(3) 连接查询是关系数据库中的重要查询。在TSQL中，连接查询有两大类表示形式： 一类是使用连接谓词表示形式； 另一类是使用关键字JOIN表示形式。
在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。
在使用JOIN关键字指定的连接中，在FROM子句中用JOIN关键字指定连接的多个表的表名，用ON子句指定连接条件。JOIN关键字指定的连接类型有3种： INNER JOIN表示内连接，OUTER JOIN表示外连接，CROSS JOIN表示交叉连接。
外连接有以下3种： 左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和完全外连接(FULL OUTER JOIN)。
(4) 将一个查询块嵌套在另一个查询块的子句指定条件中的查询称为嵌套查询。在嵌套查询中，上层查询块称为父查询或外层查询，下层查询块称为子查询(subquery)或内层查询。子查询通常包括IN子查询、比较子查询和EXIST子查询。
(5) SELECT查询的其他子句包括UNION、EXCEPT、INTERSECT、INTO、CTE、FROM子句和TOP谓词等。
习题5
一、 选择题
1. 使用student表查询年龄最小的学生的姓名和年龄，下列实现此功能的查询语句中，正确的是。
A. SELECT Sname, Min(Sage) FROM student
B. SELECT Sname, Sage FROM student WHERE Sage=Min(Sage)
C. SELECT TOP1 Sname, Sage FROM student
D. SELECT TOP1 Sname, Sage FROM student ORDER BY Sage
2. 设在某SELECT语句的WHERE子句中，需要对Grade列的空值进行处理。下列关于空值的操作中，错误的是。
A. Grade IS not null
B. Grade IS null
C. Grade=null
D. Not(Grade IS null)
3. 设在SQL Server中，有学生表(学号,姓名,年龄),其中，姓名为varchar(10)类型。查询姓“张”且名字是3个字的学生的详细信息，正确的语句是。
A. SELECT *FROM 学生表 WHERE 姓名 LIKE '张_'
B. SELECT *FROM 学生表 WHERE 姓名 LIKE '张__'
C. SELECT *FROM 学生表 WHERE 姓名 LIKE '张_' AND LEN(姓名)=3
D. SELECT*FROM学生表WHERE姓名LIKE '张__' AND LEN(姓名)=3
4. 设在SQL Server中，有学生表(学号,姓名,所在系)和选课表(学号,课程号,成绩)。查询没选课的学生姓名和所在系，下列语句中能够实现该查询要求的是。
A. SELECT姓名,所在系FROM 学生表 a LEFT JOIN 选课表 b
ON a.学号= b.学号WHERE a.学号IS NULL
B. SELECT姓名,所在系FROM 学生表 a LEFT JOIN 选课表 b
ON a.学号= b.学号WHERE b.学号IS NULL
C. SELECT姓名,所在系FROM 学生表 a RIGHT JOIN 选课表 b
ON a.学号= b.学号WHERE a.学号IS NULL
D. SELECT姓名,所在系FROM 学生表 a RIGHT JOIN 选课表 b
ON a.学号= b.学号WHERE b.学号IS NULL
5. 下述语句的功能是将两个查询结果合并成一个结果，其中正确的是。
A. SELECT sno, sname, sage FROM student WHERE sdept='cs'
ORDER BY sage
UNION
SELECT sno, sname, sage FROM student WHERE sdept='is'
ORDER BY sage
B. SELECT sno, sname, sage FROM student WHERE sdept='cs'
UNION
SELECT sno, sname, sage FROM student WHERE sdept='is'
ORDER BY sage
C. SELECT sno, sname, sage FROM student WHERE sdept='cs'
UNION
SELECT sno, sname FROM student WHERE sdept='is'
ORDER BY sage
D. SELECT sno, sname, sage FROM student WHERE sdept='cs'
ORDER BY sage
UNION
SELECT sno, sname, sage FROM student WHERE sdept='is'
二、 填空题
1. 在EXISTS子查询中，子查询的执行次数是由决定的。
2. 在IN子查询和比较子查询中，是先执行层查询，再执行层查询。
3. 在EXISTS子查询中，是先执行层查询，再执行层查询。
4. UNION操作用于合并多个SELECT查询的结果，如果在合并结果时不希望去掉重复数据，应使用关键字。
5. 在SELECT语句中同时包含WHERE子句和GROUP子句，则先执行子句。
三、 问答题
1. 什么是SQL？简述SQL的分类。
2. SELECT语句中包括哪些子句？简述各个子句的功能。
3. 什么是连接谓词？简述连接谓词表示形式的语法规则。
4. 内连接、外连接有什么区别？左外连接、右外连接和全外连接有什么区别？
5. 简述常用聚合函数的函数名称和功能。
6. 在一个SELECT语句中，当WHERE子句、GROUP BY子句和HAVING子句同时出现在一个查询中时，SQL的执行顺序如何？
7. 在SQL Server中使用GROUP BY子句有什么规则？
8. 什么是子查询？IN子查询、比较子查询、EXIST子查询有何区别？
四、 应用题
1. 查询student表中总学分大于或等于50分的学生的情况。
2. 查找谢萱“高等数学”的成绩。
3. 查找选修了“数字电路”的学生姓名及成绩，并按成绩降序排列。
4. 查找“数据库系统”和“微机原理”的平均成绩。
5. 查询每个专业最高分的课程名和分数。
6. 查询通信专业的最高分的学生的学号、姓名、课程号和分数。
7. 查询有2门以上(含2门)课程均超过80分的学生姓名及其平均成绩。
8. 查询至少选学了3门课程的学生姓名。
实验5数 据 查 询
实验5.1数据查询1
1. 实验目的及要求
(1) 理解SELECT语句的语法格式。
(2) 掌握SELECT语句的操作和使用方法。
(3) 具备编写和调试SELECT语句以进行数据库查询的能力。
2. 验证性实验
对storeexpm数据库中的EmplInfo表进行数据查询，验证和调试查询语句的代码。
1) 使用两种方式，查询EmplInfo表的所有记录
(1) 使用列名表。
USE storeexpm
SELECT EmplID, EmplName, Sex, Birthday, Native, Wages, DeptID
FROM EmplInfo
(2) 使用*。
USE storeexpm
SELECT *
FROM EmplInfo
2) 查询EmplInfo表中有关员工号、姓名和籍贯的记录
USE storeexpm
SELECT EmplID, EmplName, Native
FROM EmplInfo
3) 使用两种方式，查询籍贯为上海和四川的员工信息
(1) 使用IN关键字。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE Native IN ('上海', '四川')
(2) 使用OR关键字。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE Native = '上海' OR Native = '四川'
4) 通过两种方式查询EmplInfo表中工资在3500~4500元的员工
(1) 通过指定范围关键字。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE Wages BETWEEN 1500 AND 4000
(2) 通过比较运算符。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE Wages>=1500 AND Wages<=4000
5) 查询籍贯是北京的员工的姓名、出生日期和部门号
USE storeexpm
SELECT EmplName, Birthday, DeptID
FROM EmplInfo
WHERE Native LIKE '北京%'
6) 查询各个部门的员工人数
USE storeexpm
SELECT DeptID AS 部门号, COUNT(EmplID) AS 员工人数
FROM EmplInfo
GROUP BY DeptID
7) 查询每个部门的总工资和最高工资
USE storeexpm
SELECT DeptID AS 部门号, SUM(Wages) AS 总工资, MAX(Wages) AS 最高工资
FROM EmplInfo
GROUP BY DeptID
8) 查询员工工资，按照工资从高到低的顺序排列
USE storeexpm
SELECT *
FROM EmplInfo
ORDER BY Wages DESC
9) 按从高到低的顺序排列员工工资，查询前3名员工的信息
USE storeexpm
SELECT TOP 3 EmplName, Wages
FROM EmplInfo
ORDER BY Wages DESC
3. 设计性试验
对storeexpm数据库中的GoodsInfo表进行数据查询，设计、编写和调试查询语句的代码，完成以下操作。
1) 使用两种方式，查询GoodsInfo表的所有记录
(1) 使用列名表。
(2) 使用 *。
2) 查询GoodsInfo表有关商品号、商品名称和库存量的记录
3) 使用两种方式，查询商品类型为“笔记本计算机”和“服务器”的商品信息
(1) 使用IN关键字。
(2) 使用OR关键字。
4) 通过两种方式查询GoodsInfo表中单价在1000~8000元的商品
(1) 通过指定范围关键字。
(2) 通过比较运算符。
5) 查询商品类型为“平板”的商品信息
6) 查询各类商品的库存量
7) 查询各类商品的品种个数和最高单价
8) 查询各商品的单价，按照从高到低的顺序排列
9) 按从高到低的顺序排列商品的单价，查询前3类商品的信息
4. 观察与思考
(1) LIKE的通配符“%”和“_”有何不同？
(2) IS能用“=”来代替吗？
(3) “=”与IN在什么情况下作用相同？
(4) 空值的使用可分为哪几种情况？
(5) 聚集函数能否直接使用在SELECT子句、WHERE子句、GROUP BY子句、HAVING子句中吗？
(6) WHERE子句与HAVING子句有何不同?
(7) COUNT(*)、COUNT(列名)、COUNT(DISTINCT列名)三者的区别是什么？
实验5.2数据查询2
1. 实验目的及要求
(1) 理解连接查询、子查询以及联合查询的语法格式。
(2) 掌握连接查询、子查询以及联合查询的操作和使用方法。
(3) 具备编写和调试连接查询、子查询以及联合查询语句以进行数据库查询的能力。
2. 验证性实验
对storeexpm数据库进行数据查询，验证和调试数据查询的代码。
1) 对员工表EmplInfo和部门表DeptInfo进行交叉连接，观察所有的可能组合
USE storeexpm
SELECT *
FROM EmplInfo CROSS JOIN DeptInfo
或
USE storeexpm
SELECT *
FROM EmplInfo, DeptInfo
2) 查询每个员工及其所在部门的情况
(1) 使用JOIN关键字的表示方式。
USE storeexpm
SELECT *
FROM EmplInfo INNER JOIN DeptInfo ON EmplInfo.DeptID = DeptInfo.DeptID
(2) 使用连接谓词的表示方式。
USE storeexpm
SELECT *
FROM EmplInfo, DeptInfo
WHERE EmplInfo.DeptID = DeptInfo.DeptID
3) 采用自然连接查询员工及其所属的部门的情况
USE storeexpm
SELECT EmplInfo.*, DeptName
FROM EmplInfo JOIN DeptInfo ON EmplInfo.DeptID = DeptInfo.DeptID
该语句进行自然连接，去掉了结果集中的重复列。
4) 查询部门号D001的员工工资高于员工号为E003的工资的员工情况
(1) 使用JOIN关键字的表示方式。
USE storeexpm
SELECT a.EmplID, a.EmplName, a.Wages, a.DeptID
FROM EmplInfo a JOIN EmplInfo b ON a.Wages>b.Wages
WHERE a.DeptID ='D001' AND b.EmplID ='E003'
ORDER BY a.Wages DESC
(2) 使用连接谓词的表示方式。
USE storeexpm
SELECT a.EmplID, a.EmplName, a.Wages, a.DeptID
FROM EmplInfo a, EmplInfo b
WHERE a.Wages>b.Wages AND a.DeptID ='D001' AND b.EmplID ='E003'
ORDER BY a.Wages DESC;
5) 分别采用左外连接、右外连接、全外连接查询员工所属的部门
(1) 采用左外连接。
USE storeexpm
SELECT EmplName, DeptName
FROM EmplInfo LEFT JOIN DeptInfo ON EmplInfo.DeptID=DeptInfo.DeptID
该语句采用关键字LEFT JOIN进行左外连接，当左表有记录而在右表中没有匹配记录时，右表对应列被设置为空值。
(2) 采用右外连接。
USE storeexpm
SELECT EmplName, DeptName
FROM EmplInfo RIGHT JOIN DeptInfo ON EmplInfo.DeptID=DeptInfo.DeptID
该语句采用关键字RIGHT JOIN进行右外连接，当右表有记录而在左表中没有匹配记录时，左表对应列被设置为空值。
(3) 采用全外连接。
USE storeexpm
SELECT EmplName, DeptName
FROM EmplInfo FULL JOIN DeptInfo ON EmplInfo.DeptID=DeptInfo.DeptID
该语句采用关键字FULL JOIN进行全外连接。
6) 查询销售部和财务部的员工名单
USE storeexpm
SELECT EmplID, EmplName, DeptName
FROM EmplInfo a, DeptInfo b
WHERE a.DeptID=b.DeptID AND DeptName='销售部'
UNION
SELECT EmplID, EmplName, DeptName
FROM EmplInfo a, DeptInfo b
WHERE a.DeptID=b.DeptID AND DeptName='财务部'
该语句采用集合操作符UNION进行并运算以实现集合查询。
7) 分别采用IN子查询和比较子查询财务部和经理办的员工信息
(1) 采用IN子查询。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE DeptID IN
(SELECT DeptID
FROM DeptInfo
WHERE DeptName='财务部' OR DeptName='经理办'
)
该语句采用IN子查询。
(2) 采用比较子查询。
USE storeexpm
SELECT *
FROM EmplInfo
WHERE DeptID=ANY
(SELECT DeptID
FROM DeptInfo
WHERE DeptName IN ('财务部', '经理办')
)
该语句采用比较子查询，其中，关键字ANY用于对比较运算符“=”进行限制。
8) 列出比所有D001部门员工年龄都小的员工及其出生日期
USE storeexpm
SELECT EmplID AS 员工号, EmplName AS 姓名, Birthday AS 出生日期
FROM EmplInfo
WHERE Birthday>ALL
(SELECT Birthday
FROM EmplInfo
WHERE DeptID='D001'
);
该语句采用比较子查询，其中，关键字ANY用于对比较运算符“>”进行限制。
9) 查询销售部的员工姓名
USE storeexpm
SELECT EmplName AS 姓名
FROM EmplInfo
WHERE EXISTS
(SELECT *
FROM DeptInfo
WHERE EmplInfo.DeptID=DeptInfo.DeptID AND DeptID='D001'
);
该语句采用EXISTS子查询。
3. 设计性试验
在数据库storeexpm中，设计、编写和调试查询语句的代码，完成以下操作。
1) 对商品表GoodsInfo和订单明细表DetailInfo进行交叉连接，观察所有的可能组合
2) 查询商品销售情况
(1) 使用JOIN关键字的表示方式。
(2) 使用连接谓词的表示方式。
3) 采用自然连接查询商品销售情况
4) 查询员工销售情况
(1) 使用JOIN关键字的表示方式。
(2) 使用连接谓词的表示方式。
5) 对员工表EmplInfo和订单表OrderInfo分别进行左外连接、右外连接、全外连接
(1) 左外连接。
(2) 右外连接。
(3) 全外连接。
6) 查询销售部的员工姓名、销售日期及销售总金额，并按销售总金额降序排列
7) 查询刘建新的销售总金额
8) 查询销售部和财务部的员工号
4. 观察与思考
(1) 使用JOIN关键字的表示方式和使用连接谓词的表示方式有什么不同？
(2) 内连接与外连接有何区别？
(3) 举例说明IN子查询、比较子查询和EXIST子查询的用法。
(4) 关键字ALL、SOME和ANY对比较运算有何限制？