第5章数据查询


视频讲解


建立数据库的目的是查询数据，因此，可以说数据查询是数据库的核心操作，也是SQL数据操作的主要内容。查询语句的功能是从数据库中检索满足条件的数据。查询的数据源可以是一张表(或视图)，也可以是多张表(或视图)，查询的结果是由0行(没有满足条件的数据)或多行记录组成的一个记录集合(结果表)，并允许选择一列或多列作为输出结果的目标列。SELECT语句还可以对查询的结果进行排序、汇总等。
5.1基本查询

5.1.1SELECT语句的基本格式
SQL 提供了SELECT 语句进行数据库的查询，该语句具有灵活的使用方法和丰富的功能。其一般格式为： 

SELECT［ALL|DISTINCT］ ［TOP n［PERCENT］］<目标列表达式> ［，<目标列表达式>，…］

［INTO<新表名>］

FROM <表或视图名> ［，<表或视图名>］…

［WHERE <条件表达式>］

［GROUP BY<列名1> ［HAVING <条件表达式>］］

［ORDER BY<列名2>［ASC | DESC］］


在上述结构中，SELECT子句用于指定输出字段，FROM子句用于指定数据的来源，WHERE子句用于指定数据的选择条件，GROUP BY子句用于对检索到的结果进行分组，HAVING子句用于指定组的筛选条件，ORDER BY子句用于对查询的结果进行排序。在这些子句中，SELECT和FROM两个子句是必需的，其他子句是可选的，各子句的顺序必须和上面格式中的一样。
SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。下面以TMS数据库为例说明SELECT语句的各种用法。
5.1.2简单查询
简单查询指查询过程中只涉及一个表的查询语句。简单查询是最基本的查询语句。
1. 从表中选择所有的数据内容
查询表的所有数据内容就是将表的所有行和所有列对应的数据全部列出来。将表中的所有属性列选出来可以有两种方法。一种方法是在SELECT关键字后面按照所要显示的列的顺序列出所有列名，如果列的显示顺序与其在基本表中的顺序相同，也可以简单地将<目标列表达式>指定为*。




【例5.1】 查询全体学生的详细信息。

SELECT * FROM S


也可以写成： 

SELECT SNO, SNAME, SEX, BIRTHYEAR, SDEPT, SCLASS 

FROM S



图5.1例5.1执行结果

执行结果如图5.1所示。
2. 显示所有的列，有限行
要显示所有的列，但要求结果表只包括有限的行，可在SELECT语句中引入TOP子句实现。
SELECT语句中使用TOP n关键字输出查询结果集的前n行，使用TOP n PERCENT输出查询结果集的前面一部分，其中n为输出元组总数占结果集总元组数的百分比。
【例5.2】 要查看TMS数据库中，表S的数据组成情况，可通过列出该表前5行的所有列来了解。

SELECT TOP 5 * FROM S

执行上述语句可以显示表S 中的所有列，但对返回结果表的行数进行了限制(TOP子句)，即只返回前5行。查询结果如图5.2所示。
TOP子句在和ORDER BY 子句一起使用来返回排序后的前n个记录时是非常有用的。例如，要得到年龄最大的学生信息，可用下列语句实现： 

SELECT TOP 1 *

FROM S

ORDER BY BIRTHYEAR ASC

查询结果如图5.3所示。



图5.2例5.2查询结果（一）




图5.3例5.2查询结果（二）



注意： 上面的方法在有多人出生在相同年份时，仅能查询出自然顺序在第一行的元组，更精确的查询则需要使用到聚合函数，将在后面进行讨论。
【例5.3】 查询学生表中前面20%的学生信息。

SELECT TOP 20 PERCENT *

FROM S


在此查询中输出（前面总人数的20%）个元组。
关于对结果集进行排序的更多内容将在后文中讨论。
3. 选择表中特定的列
实际上，人们很少选择一个表中的所有信息，大多数情况下只对其中的某些行或列的信息感兴趣。可以通过在SELECT子句的<目标列表达式>中指定要查询的属性列名，有选择地列出感兴趣的列。
【例5.4】 要查看全体学生的学号、姓名和出生年份，可用下列语句实现： 

SELECT SNO，SNAME，BIRTHYEAR

FROM S


查询结果如图5.4所示。
注意： SELECT列名列表中的列名必须用逗号分开，逗号前、后有没有空格都可以。
4. 对结果表进行排序
通过ORDER BY子句，可对返回的结果表的行按照在ORDER BY子句中指定的一列或多列进行升序(ASC)或降序(DESC)排列，默认为升序排列。通常，ORDER BY子句中的列名必须在结果表的列名之中。
【例5.5】如果按出生年份降序排列例5.4的查询结果，可用下列语句实现： 

SELECT SNO，SNAME，BIRTHYEAR

FROM S

ORDER BY BIRTHYEAR DESC

查询结果如图5.5所示。



图5.4例5.4查询结果




图5.5例5.5查询结果



如果在ORDER BY子句中有多列，则它们排序的优先顺序是它们在ORDER BY子句中从左到右出现的顺序。ORDER BY子句中的第一列决定了各行排列的主次序，后面的列再对其进行更细致的排列。第一列的值相同的行，其顺序由ORDER BY子句中的第二列决定，以此类推。ASC 或DESC可以对每列分别进行设置。
【例5.6】如果先按出生年份降序、再按学号升序排列例5.5的查询结果，可用下列语句实现： 

SELECT SNO，SNAME，BIRTHYEAR

FROM S

ORDER BY BIRTHYEAR DESC, SNO ASC


查询结果如图5.6所示。
ORDER BY子句中的列可以是列名，也可以是一个整数，该数表示相应的列在SELECT子句目标列中的位置，如例5.6语句中的ORDER BY子句可以有下列几种等价形式： 
（1） ORDER BY 3 DESC，1 ASC。
（2） ORDER BY BIRHYEAR,1 ASC。
（3） ORDER BY 3 DESC，SNO ASC。
在一个查询语句中只能有一个ORDER BY 子句。在SELECT、FROM、WHERE、ORDER BY构成的查询语句中，ORDER BY子句要放在查询语句的最后面。

5. 查询经过计算的值 
SELECT子句的目标列表达式为表达式，即其不仅可以是目标列，还可以是算术表达式、字符串常量、函数、列别名等。
【例5.7】 查询全体学生的姓名及其年龄。

SELECT SNAME， '年龄： '，YEAR(GETDATE())-BIRTHYEAR

FROM S

查询结果如图5.7所示。



图5.6例5.6查询结果




图5.7例5.7查询结果



6. 使用列别名 
从例5.7查询结果可以看到，如果目标列表达式是经过处理的列，如字符串常量、算数表达式、函数等，查询结果集中该列显示为“无列名”。此时，可以使用列别名代替原列名。
在SELECT子句中可以通过以下4种方式来定义列别名。
（1） 使用AS关键字，如 SELECT SNO AS 学号 FROM S。
（2） 带单引号的列别名，如 SELECT SNO '学号' FROM S。
（3） 带双引号的列别名，如 SELECT SNO "学号" FROM S。
（4） 不带引号的列别名，如SELECT SNO 学号 FROM S。
如果列别名包含空格、特殊符号等，那么必须将列别名放在双引号或者单引号内。
在下列4种情况下通常会使用列别名。
（1）  字段为英文，为方便查看，可以使用中文列别名代替英文字段。
（2）  多表查询时出现相同的列名。如果对多个数据表进行查询，查询结果中可能会出现相同的列名，很容易出现误解，这时候应采用列别名来解决上述问题。
（3） 在查询结果中添加列，在表中出现计算产生新的列时，可以使用列别名。
（4） 统计结果中出现的列，使用聚集函数语句对数据查询时，需要对产生的统计字段使用列别名。
因此，例5.7的查询也可以用下面语句完成： 

SELECT SNAME NAME, 

'年龄： ''AGE OF STUDENT',

YEAR(GETDATE())-BIRTHYEAR AS AGE

FROM S


查询结果如图5.8所示。
需要注意的是，字段别名可以使用在ORDER BY子句中，但不能使用在WHERE、GROUP BY或HAVING语句中。
7. 禁止结果表返回重复行
在对一个表进行查询时，得到的结果中可能会有相同的元组，SELECT语句提供了DISTINCT 关键字可以保证结果表中行唯一，即DISTINCT可以删除结果表中的重复行。该关键字必须紧跟在关键字SELECT之后，在一个SELECT语句范畴内只可使用一次，且只对返回行有效(对SELECT子句指定的列集合中相同的行进行删除)。
【例5.8】 列出学生表中所有的系名。

SELECT SDEPT 

FROM S； 

查询结果如图5.9所示。



图5.8例5.7查询结果（列别名方法）




图5.9例5.8查询结果



要去掉重复的元组，需要用以下语句： 

SELECT DISTINCT SDEPT 

FROM S



图5.10例5.8查询结果

（去掉重复元组）

该语句的执行结果如图5.10所示。
8. 查询满足给定条件的元组
可以通过WHERE子句指定返回的结果表中各行需要满足的条件。WHERE子句指定的条件表达式可能含有一个或多个的谓词或选择条件，它们由各种运算符组合而成，常用的运算符如表5.1所示。在WHERE子句中使用的列并不一定要求出现在SELECT子句的输出列表中。


表5.1常用的运算符



查 询 条 件谓词

比较运算符=，>，<，>=，<=，<>
逻辑运算符AND，OR，NOT
谓词IN，BETWEEN AND，LIKE
空值IS NULL


1) 比较条件
比较条件最为简单，同Java、C等高级语言一样，用比较运算符比较两个常量或变量的大小。用比较运算符构成的条件形式是： 

列名运算符 常量

列名运算符 列名

常量运算符 列名


如果比较字符型的列，该值就必须用单引号引起来。而且，对字符的比较是大小写敏感的。
【例5.9】 查询软件学院全体学生的名单。

SELECT SNAME

FROM S

WHERE SDEPT = '软件'


查询结果如图5.11所示。
对于数值型的列，如INTEGER、SMALLINT 或 DECIMAL，被比较的值不能放在单引号之内。
【例5.10】 查询在1999年以前出生(不包括1999)的学生姓名及出生年份。

SELECT SNAME, BIRTHYEAR 

FROM S

WHERE BIRTHYEAR<1999(或NOT BIRTHYEAR >=1999)

查询结果如图5.12所示。



图5.11例5.9查询结果




图5.12例5.10查询结果




2)  复合查询条件
可以用AND、OR、NOT等逻辑运算符构造更复杂的查询条件。如图5.13所示，AND的运算规律是同真时，结果为真； OR的运算规律是只要有一个为真，结果即为真。



图5.13AND运算和OR运算


【例5.11】 查询出生在1998—2000年的学生姓名、所在系和出生年份。

SELECT SNAME, SDEPT, BIRTHYEAR

FROM S

WHERE BIRTHYEAR>=1998 AND BIRTHYEAR<=2000


该语句的查询结果如图5.14所示。
【例5.12】 查询软件学院、机械学院和交通学院的学生姓名和所在学院名。

SELECT SNAME, SDEPT

FROM S

WHERE SDEPT='软件' OR SDEPT='机械' OR SDEPT='交通'； 


该语句的查询结果如图5.15所示。
有时，需要用小括号()和逻辑运算符来构建复合查询条件。例如，要查询软件学院和机械学院的女生姓名、性别和所在学院，可以用下列语句实现：  

SELECT SNAME, SEX, SDEPT

FROM S

WHERE (SDEPT='软件' OR SDEPT='机械') AND SEX='女'




图5.14例5.11查询结果




图5.15例5.12查询结果





3)  谓词
SQL 中的谓词指的是返回值是逻辑值的函数。对于函数而言，返回值可以是数字、字符串或者日期等，但谓词的返回值全部是逻辑值(TRUE/FALSE/UNKNOW)，谓词是一种特殊的函数。
（1） IN 谓词。
IN谓词可用来确定一个值是否属于一个集合。这个值可以是数字、字符、日期或时间。字符、日期或时间必须用单引号引起来。
【例5.13】在例5.12结果中查询软件学院、机械学院和交通学院的学生姓名和所在学院名，也可以用下列语句实现。

SELECT SNAME, SDEPT

FROM S

WHERE SDEPT IN('软件' ,'机械,'交通')


若查询既不是软件学院，也不是机械学院和交通学院的学生信息，则WHERE条件可以写为： 

WHERE SDEPT NOT IN('软件' ,'机械,'交通')


（2） BETWEEN 谓词。
在WHERE子句中用BETWEEN谓词可以判断一个值是否在按升序给定的两个值之间(包括和这两个值相等)。较低的值紧跟BETWEEN谓词书写，较高或相等的值写在AND后。下列WHERE子句： 

WHERE AGE BETWEEN 12 AND 15

等价于

WHERE AGE >= 12 AND AGE <= 15


字符和数字混合的数据也可以用BETWEEN谓词。如： 

WHERE SCLASS BETWEEN '机械151' AND '软工181'


【例5.14】在例5.10结果中查询出生在1998—2000年的学生姓名、所在系和出生年份，也可以用下列语句实现。

SELECT SNAME, SDEPT, BIRTHYEAR

FROM S

WHERE BIRTHYEAR BETWEEN 1998 AND 2000




（3） LIKE 谓词。
用LIKE谓词可以实现字符匹配。一般语法格式如下： 

属性列 ［NOT］ LIKE '<匹配串>' ［ESCAPE '<换码字符>'］


其含义是查询指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。
① %(百分号)： 代表任意长度(可以为0)的字符串。例如，a%b表示以a开头、以b结尾的任意长度的字符串。ab、afb、acdeb等都满足该匹配串。
② _(下画线)： 代表任意单个字符或汉字。例如，a_b表示以a开头、以b结尾的长度为3的任意字符串。afb、amb等都满足该匹配串。
【例5.15】查询所有姓王的同学的姓名和所在学院。

SELECT SNAME,SDEPT

FROM S

WHERE SNAME LIKE '王%'



图5.16例5.15

查询结果

查询结果如图5.16所示。
LIKE谓词可以和逻辑运算符NOT组合使用，例如，查询不是2018级的学生学号和姓名，可以用下列语句实现： 

SELECT SNO, SNAME

FROM S

WHERE SNO NOT LIKE '2018%'


由于%和_在LIKE谓词中做通配符，因此，如果要查询的内容含有%或_，则需要使用ESCAPE‘<换码字符>’短语对通配符进行转义，所用的转义字符必须要用ESCAPE子句指定，并且该转义字符在一个WHERE子句中可使用多次。
【例5.16】 查询以DB_开头，且倒数第三个字符为i的课程的详细情况。

SELECT *

FROM C

WHERE CNAME LIKE 'DB＼_%i_ _' ESCAPE '＼'


在此SELECT语句中，反斜杠为转义字符，跟在它后面的下画线不再是通配符，而是下画线本身。
查询结果如图5.17所示。


图5.17例5.16查询结果



4) 空值(NULL)应用
NULL 只和IS 或IS NOT进行逻辑运算，它既不是数字0，也不是空串。所以，下列的SELECT语句是错误的： 

SELECT * FROM SC WHERE GRADE = NULL


当执行该语句时，数据库管理系统会提示错误信息。
【例5.17】 查询成绩为空的学生的学号和相应的课程号。

SELECT SNO, CNO

FROM SC

WHERE GRADE IS NULL


5.1.3聚合函数与分组
聚合函数对一组值执行计算，并返回单个值。除 COUNT 外，聚合函数都会忽略 Null 值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
1. SQL中的聚合函数及应用
SQL中的聚合函数及其应用见表5.2。


表5.2SQL中的聚合函数及应用



聚 合 函 数应用

SUM (列表达式)求和
AVG (列表达式)求平均值
MIN (列表达式) 求最小值
MAX (列表达式)求最大值
COUNT (*)返回满足条件的记录数
COUNT (<列名>) 返回满足条件的列中非空值的个数
COUNT (DISTINCT <列名>) 返回满足条件的列中不重复的非空值的个数


SUM，AVG，MIN，MAX，COUNT(*)，COUNT(DISTINCT...) 等聚合函数的参数可以是一列中的一行或多行，而且SUM和AVG两个函数的参数必须是数值型的。
【例5.18】聚合函数COUNT()几种使用方式的比较。

SELECT COUNT(*)'COUNT(*)',

COUNT(GRADE)'COUNT(GRADE)',

COUNT(DISTINCT GRADE)'COUNT(DISTINCT GRADE)'

FROM  SC



查询结果如图5.18所示。
该语句返回SC表中的行数、成绩的数目以及不重复的成绩数目。
【例5.19】 查询女生的人数。

SELECT COUNT(*)

FROM S

WHERE SEX ='女'

通常，函数COUNT(*)的自变量是行集合，其结果是集合中的记录个数。其查询结果如图5.19所示。



图5.18例5.18查询结果





图5.19例5.19

查询结果



再看下面的例子。
【例5.20】 查询0211号课程的选课人数、平均成绩、最高成绩、最低成绩。

SELECT COUNT(SNO) AS 选课人数,

 AVG(GRADE) AS 平均成绩,

 MAX(GRADE) AS 最高成绩,

 MIN(GRADE) AS 最低成绩

FROMSC

WHERE CNO='0211'


该语句的查询结果如图5.20所示。
2.  分组GROUP BY子句
1) 简单分组查询
GROUP BY 子句将查询结果表的各行按一列或多列取值相等的原则进行分组。使用GROUP BY子句时，一个聚合数对每组生成一个值。对查询结果分组是为了细化聚合函数的作用对象。如果未对查询结果分组，聚合函数将作用于整个查询结果。在实际应用中，经常需要将查询结果进行分组，然后再对每个分组进行统计。
【例5.21】 查询学生表中男、女生各多少人。

SELECT SEX AS 性别,COUNT(SNO) AS 人数

FROM S

GROUP BY SEX

查询结果如图5.21所示。



图5.20例5.20查询结果





图5.21例5.21

查询结果



【例5.22】 查询每门课程的选课人数，列出课程号及相应的人数。

SELECT CNO AS 课程号,COUNT(SNO) AS 选课人数

FROM SC

GROUP BY CNO


查询结果如图5.22所示。
【例5.23】 查询每个学院的男、女生各多少人。

SELECT SDEPT, SEX, COUNT(SNO) 

FROM S

GROUP BY SDEPT, SEX

ORDER BY SDEPT

查询结果如图5.23所示。



图5.22例5.22查询结果




图5.23例5.23查询结果



例5.23中，GROUP BY子句含有两列，即两列的值都相等的为一组。在结果表中的前两行有相同的SDEPT列的值(‘机械’) ，但是SEX列的值不同。GROUP BY子句的结果保证该子句指定的列组合的唯一。
注意： ① 用AS子句命名的列不能用在GROUP BY子句中。
② GROUP BY子句不能保证对结果表进行排序。要对结果表排序，需要用ORDER BY 子句。
2) 带HAVING子句的分组查询
如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。
【例5.24】 查询选修了两门以上课程的学生学号。

SELECT SNO

FROM SC

GROUP BY SNO

HAVING COUNT(CNO)>2


WHERE子句与HAVING子句的根本区别在于处理的对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING子句作用于组，从中选择满足条件的组。
注意：  如果一个SELECT子句中有聚合函数(如MIN、MAX、SUM、AVG、COUNT(*)、COUNT(DISTINCT 列名))，并且还有其他列(即没有用到聚合函数的列)存在，那么，SELECT子句所有没有用到聚合函数的列都必须包含在GROUP BY子句中(如例5.23)。相反，GROUP BY子句中的列不一定要出现在SELECT列表中(如例5.24)。聚合函数也可以用在HAVING子句中。在HAVING子句中可以在表中任何一列上使用聚合函数，该列也可以不出现在SELECT子句中。此外，聚合函数不能嵌套使用。
5.1.4输出结果选项
SELECT语句中的INTO子句用于把查询结果存放到一个新建的表中，新建表名由<新表名>给出，新建表的列由SELECT 子句中指定的列构成。
【例5.25】 将所有女生的学号、姓名和所在院系存入表S_FEMALE中。


图5.24例5.25查询结果


SELECT SNO,SNAME,SDEPT

INTO S_FEMALE

FROM S

WHERE SEX='女'


上例查询执行完成后即得到一个名为S_FEMALE的新表,表中数据如图5.24所示。
5.1.5SELECT语句完整的语法
SELECT语句完整的语法结构如下。

SELECT<目标列表达式> ［，<目标列表达式>］

［INTO  <新表名>］

FROM<表或视图名> ［，<表或视图名>］

［WHERE<条件表达式>］

［GROUP BY<列名1> ［HAVING <条件表达式>］］

［ORDER BY<列名2>［ASC | DESC］］


SQL不同于其他编程语言的最明显特征是处理代码的顺序。在多数编程语言中，代码按编码顺序被处理，但是在SQL中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但是几乎总是最后被处理。
SQL中的每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会被返回给调用者。如果没有在查询中指定某一子句，则将跳过相应的步骤。
整个语句的执行过程如下。
(1) 读取FROM子句中基本表和视图的数据，若为多个基本表或视图，则对它们执行笛卡儿积。
(2) 选取满足WHERE子句中给出的条件表达式的元组。
(3) 按GROUP BY子句中指定列的值分组，同时提取满足HAVING子句中条件表达式的那些组。
（4） 按SELECT子句中给出的列名或表达式求值输出。
（5） ORDER BY子句对输出的目标列进行排序，按ASC升序排列，或按DESC降序排列。
（6） 创建新表，将查询到的输入插入新表中。
【例5.26】 下列语句的执行过程如图5.25所示。

SELECT SNO,AVG(GRADE) 'AVE OF SCORE'

FROM SC

WHERESNO<>'20180201'

GROUP BY SNO

HAVING AVG(GRADE)>70

ORDER BY 2 




图5.25SELECT语句的执行过程



5.2多表查询

5.2.1连接查询
查询过程中只涉及一个表的查询称为简单查询。但通常需要借助一个数据库的多个表之间的联系，以从这些表中获得更多的信息。如图5.26所示，S表中的每个学号在SC表中都有其相应的一行或多行来表示该学生的选课情况。观察两个表的数据可以发现，在S表中的任意一行，可以通过学号（SNO）与SC表中的某一行相联系，正是这种联系使人们有可能只使用一个查询语句，从多个表中获取更多的信息。这种一个查询同时涉及两个或两个以上的表称为连接查询。连接查询是关系数据库中最主要的查询，主要包括交叉连接、内连接、外连接等。


图5.26连接查询的实现原理

1. 交叉连接
交叉连接也称为笛卡儿积，它是没有连接条件下的两个表的连接，包含了所连接的两个表中所有元组的全部组合。该连接方式在实际应用中很少使用，语法格式如下： 

SELECT <目标列表达式> ［,...n］

FROM <表1> CROSS JOIN <表2>


【例5.27】 查询所有学生可能的选课情况。

SELECT SNAME,CNAME

FROM S CROSS JOIN C


2. 内连接
内连接指从两个表的笛卡儿积中选出符合连接条件的元组。它使用INNER JOIN连接运算符，并使用ON关键字指定连接条件。内连接是一种常用的连接方式，如果在JOIN关键字前面没有指定连接类型，那么默认的连接类型就是内连接。内连接的语法格式如下： 

SELECT <目标列表达式> ［,...n］

FROM <表1> INNER JOIN <表2>

ON <连接条件表达式> ［,...n］


注意： 连接条件涉及的列的数据类型不必相同，但这些数据类型必须相容。计算连接条件的方式与计算其他搜索条件的方式相同，并且使用相同的比较规则。
当不指定连接条件时，则返回FROM子句中列出的表中行的所有组合，即使这些行可能完全不相关，这就是前面介绍的交叉连接，交叉连接查询的结果称为表的交叉积。
内连接只保留交叉积中满足指定连接条件的行。如果某行在一个表中存在，但在另一个表中不存在，则结果表中不包括该信息。
从概念上讲，DBMS执行连接操作的过程是： 首先取表1的第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2完全查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，逐一查找满足条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中的另一个元组。重复这个过程，直到表1中的全部元组都处理完毕为止。
【例5.28】 在TMS数据库中，查找所有学生的学号、姓名和他们所选的课程号及成绩。


图5.27例5.28查询结果


SELECT S.SNO,SNAME,CNO,GRADE

FROM S JOIN SC 

ON S.SNO=SC.SNO


该语句的查询结果如图5.27所示。
因为列名SNO同时出现在S和SC两个表中，因此需要对其加以限制，即指明是哪个表里的SNO。将SELECT子句中出现的来自多个表的相同列名加以限定，可以避免可能出现的潜在错误。
通常情况下，连接两个表至少需要一个连接条件，要连接三个表，则至少需要两个连接条件。为保证不出现无连接的表，连接条件的最小数目通常是需要连接的表的数目减1。其他条件可以通过AND或OR操作符进行添加。虽然可以用任意列来构造连接条件，但经常用一个表的主键和另一个表的外键进行连接操作。
【例5.29】 查询选修了0211号课程的学生姓名和该门课程的成绩。

SELECT SNAME,GRADE

FROM S JOIN SC

ON S.SNO=SC.SNO

WHERE CNO='0211'

查询结果如图5.28所示。
在上例中，JOIN没有指定类型，则系统默认为内连接。选修了0211号课程为选择条件，可写在WHERE子句中，也可写在ON子句中用逻辑运算符与连接条件相连，不同情况下执行效率有差别。
【例5.30】 查询选修了0211号课程的学生姓名和该门课程的课程名和成绩。

SELECT SNAME, CNAME, GRADE

FROM S JOIN SC ON S.SNO=SC.SNO

JOIN C ON SC.CNO=C.CNO

WHERE C.CNO='0211'

查询结果如图5.29所示。



图5.28例5.29

查询结果




图5.29例5.30查询结果




上例也可以用下列语句实现： 

SELECT SNAME, CNAME, GRADE

FROM S JOIN SC ON S.SNO=SC.SNO AND CNO='0211'

JOIN C ON SC.CNO=C.CNO


连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，这种连接称为表的自身连接。
【例5.31】查询至少选修了课程号为0121和0125两门课的学生学号。

SELECT a.SNO

FROM SC a JOIN SC b 

ON a.SNO=b.SNO 

AND a.CNO='0121' 

AND b.CNO='0125'


注意： 上述语句的FROM子句中为参加连接的表定义了别名，这样，就可以在SELECT子句和WHERE子句中的属性名前分别用这些别名加以区分。而表SC有两个相关名，是因为该表在FROM子句中用到了两次。
3. 外连接
外连接是内连接和左表/右表中不在内连接中的那些行的并置。外连接中不仅包含那些满足连接条件的元组，而且某些表中不满足条件的元组也会出现在结果集中。也就是说，外连接只限制其中一个表的元组，而不限制另外一个表的元组。    
外连接只能用于对两个表执行连接时。当对两个表执行外连接时，可任意将一个表指定为左表而将另一个表指定为右表，外连接有三种类型： 左外连接、右外连接和全外连接。
(1)  左外连接。
左外连接包括内连接和左表中未包括在内连接中的那些行，对连接条件左边的表不加限制。语法格式如下： 

SELECT <目标列表达式> ［,...n］

FROM <表1> LEFT ［OUTER］ JOIN <表2>

ON <连接条件表达式>


【例5.32】 查询全体学生信息及他们的选课情况。

SELECT S.*, SC.*

FROM S LEFT OUTER JOIN SC 

ON S.SNO=SC.SNO


查询结果如图5.30所示。


图5.30例5.32查询结果


在上例中，若只进行内连接,则没有选课的学生信息就不会出现在结果表中(因不满足连接条件)。使用左外连接，则左表(即S表)中不符合连接条件的学生信息也会体现在结果表中。
(2) 右外连接。
包括内连接和右表中未包括在内连接中的那些行。语法格式如下： 

SELECT <目标列表达式> ［,...n］

FROM <表1> RIGHT ［OUTER］ JOIN <表2>

ON <连接条件表达式>


(3) 全外连接。
包括内连接以及左表和右表中未包括在内连接中的那些行。语法格式如下： 

SELECT <目标列表达式> ［,...n］

FROM <表1> FULL ［OUTER］ JOIN <表2>

ON <连接条件表达式>


5.2.2子查询
为了提高SQL语句的查询功能，通常需要将一个查询语句嵌入另一个SQL查询语句的WHERE子句或HAVING子句的条件中，这种查询语句称为嵌套查询或子查询。
假设要查询和李融同学同一个学院的学生信息，如果不用子查询，则需要首先求出李融同学所在的学院名，此处需要用到第一个SELECT语句； 然后用这个学院名去构造第二个SELECT语句的WHERE条件，并通过执行第二个SELECT语句得到需要的结果。这个过程需要写两个SELECT语句，并分别执行。
这个问题也可以用一个包含子查询的SELECT语句完成。将第一个语句放在第二个语句的WHERE子句中需要学院名称的位置。在这里，第二个SELECT就作为一个外部(父)查询，内部的查询就被称为子查询。当子查询用在WHERE或HAVING子句中时，必须用小括号括起来。如下所示： 

SELECT *

FROM S

WHERE SDEPT = (SELECT SDEPT 

FROM S 

WHERE SNAME='李融')； 


上例中,子查询先运行，其结果用于构造主查询的WHERE条件，这种子查询称为无关子查询。
1. 无关子查询
无关子查询的执行不依赖父查询。它的执行过程是首先执行子查询语句，将得到的子查询结果集传递给父查询语句使用。无关子查询中对父查询没有任何引用。
(1) 带有比较运算符的子查询。
带有比较运算符的子查询指主查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、<、=、>=、<=和< >等比较运算符。
【例5.33】 查询成绩最高的学生学号、该成绩的课程号和成绩。

SELECT SNO, CNO, GRADE

FROM SC

WHERE GRADE = (SELECT MAX (GRADE)

FROM SC) 



图5.31例5.33查询结果


子查询首先要确定最高的成绩是多少，并用它和每个学生的成绩进行比较。如果某个学生的成绩和最高成绩相等，则该判定条件为真，相应的行就被返回。查询结果如图5.31所示。
注意： 带有比较运算符的子查询不能返回一个以上的值。   
(2) 带有集合比较运算符的子查询。
子查询返回单值时可以用比较运算符，但返回多值时要用到集合比较运算符。集合比较运算符及其含义如表5.3所示。


表5.3集合比较运算符及其含义



运算符含义

ALL　　如果一系列的比较都为 TRUE，则为 TRUE
ANY　　如果一系列的比较中任何一个为 TRUE，则为 TRUE
BETWEEN　　如果操作数在某个范围之内，则为 TRUE
EXISTS　　如果子查询结果包含一些行(结果不空)，则为 TRUE
IN　　如果操作数等于表达式列表中的一个，则为 TRUE
NOT　　对任何布尔运算的值取反
SOME　　如果在一系列比较中有些为 TRUE，则为 TRUE

带有IN谓词的子查询指主查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常用到的谓词。用IN谓词的嵌套查询可以返回零个、一个或多个IN谓词左边的列(或列组合)的值。
【例5.34】 查询选修了0211号课程的学生姓名和所在学院。

SELECT SNAME, SDEPT

FROM S

WHERE SNO IN (SELECT SNO

 FROM SC

 WHERE CNO='0211' )


查询结果如图5.32所示。
【例5.35】 查询没有选修任何课程的学生信息。

SELECT *

FROM S

WHERE SNO NOT IN (SELECT SNO 

FROM SC )

查询结果如图5.33所示。



图5.32例5.34查询结果




图5.33例5.35查询结果



该例中子查询首先查找出SC表中的学生学号(即选修了课程的学生学号)，主查询则列出学号不在子查询结果中的学生信息，即没有选修任何课程的学生信息。
对IN谓词，如果任何子查询返回的任意一个非空值都和主查询所要查找的列的非空值相匹配， 那么，数据表中包含该值的行都会出现在最终的结果表中。
子查询结果中的空值将不和主查询中的空值匹配。当在IN前使用逻辑运算符NOT，如例5.35中所示，若子查询只返回非空值，那么，不和子查询相匹配的主查询的行都会出现在结果表中。如果NOT IN子查询中返回一个空值，那么主查询将总是返回一个空的结果表。因此，需要对NOT IN子查询中的空值加以注意。
【例5.36】 查询年龄最小的学生的姓名和出生年份。

SELECT SNAME, BIRTHYEAR

FROMS

WHERE BIRTHYEAR>=ALL(SELECT BIRTHYEAR

FROM S )


查询结果如图5.34所示。
(3) HAVING 子句中的子查询。
除了WHERE子句外，子查询还可以用在分组数据的HAVING子句中。
【例5.37】查询所选课程的平均成绩高于所有学生成绩平均值的学生学号和平均成绩。

SELECT SNO,AVG(GRADE)

FROM SC

GROUP BY SNO

HAVING AVG(GRADE) > (SELECT AVG(GRADE)

FROM SC)

该语句的子查询结果为70.9，即HAVING子句筛选出平均成绩大于70.9分的分组。
最后的查询结果如图5.35所示。



图5.34例5.36查询结果




图5.35例5.37查询结果



注意： 除非另外指定了 TOP语句，否则在子查询中不能含有ORDER BY子句，因为其执行结果是传递给外部主查询的，对用户不可见。子查询返回值的个数必须和外部SELECT的操作符相匹配； 返回的项目数也必须和与之比较的项目数相同。

2.  相关子查询
相关子查询是引用了外部查询列的子查询。逻辑上讲，子查询会为外部查询的每行计算一次。而在物理上，它是一个动态的过程，会随情况的变化有所不同，有不止一种物理方法来处理相关子查询。在相关子查询中，子查询的执行依赖父查询，多数情况下子查询的WHERE子句中引用了父查询的表。相关子查询的执行过程与无关子查询不同，无关子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。
相关子查询执行过程如下： 
① 从父查询中取出一个元组，将子查询中引用的元组相关列的值传给内层子查询； 
② 执行子查询，得到子查询操作的结果或结果集； 
③ 父查询根据子查询返回的结果或结果集得到满足条件的行； 
④ 外层父查询依次取出下一个元组，重复步骤①~③，直到外层的元组全部处理完毕。　　
(1) 带有比较运算符的相关子查询。
与无关子查询类似，相关子查询中同样可以使用比较运算符及集合比较运算符。
【例5.38】 查询每个学生比其自身平均成绩高的所有成绩，并给出该学生的学号、满足条件的课程号和成绩。

SELECT SNO,CNO,GRADE

FROM SC a

WHERE GRADE>(SELECT AVG(GRADE)

FROM SC b

WHERE b.SNO=a.SNO)



图5.36例5.38查询结果

查询结果如图5.36所示。
在此查询语句中，子查询用于计算一个学生所有选修课程的平均成绩，此学生即为外层父查询所扫描到的学生(b.SNO=a.SNO)，若学生的平均成绩满足父查询WHERE条件，则返回其相关信息。由于父子两层查询都引用了表SC，为加以区别，分别起别名为a和b。因为子查询计算哪个学生成绩是与父查询相关的，所以这种子查询称为相关子查询。SQL Server的一种执行过程如下。
① 父查询顺序扫描表SC，取出第一行元组，将该元组的SNO值(a.SNO=' 20180201')传递给子查询。
② 执行子查询，此时的子查询即相当于执行语句： 

SELECT AVG(GRADE)

FROM SC b

WHERE b.SNO= '20180201'


即计算学号为20180201的学生所选课程的平均成绩，得到值为70.67。
③ 将子查询得到的该学生平均成绩70.67返回给父查询，则此时父查询WHERE语句即为： 

WHERE GRADE >70.67


若当前元组满足WHERE语句条件，则将其保留在父查询的查询结果集中。详细过程如图5.37所示： 



图5.37相关子查询执行过程示例


④ 父查询依次取下一行元组,重复上述步骤，直到SC表所有元组扫描完成。

(2) 带有谓词EXISTS的相关子查询。
EXISTS是一个非常强大的谓词，它允许高效地检查指定查询是否产生某些行。EXISTS的输入是一个子查询，它通常会关联到外部查询，但不是必须的，根据子查询是否返回行，该谓词返回TRUE或 FALSE。不同于其他谓词和逻辑表达式，无论输入子查询是否返回行，EXISTS都不返回 UNKNOWN。
在使用谓词EXISTS的子查询中，只要子查询返回非空结果，则父查询的WHERE子句将返回逻辑真，否则返回逻辑假。至于返回结果集是什么数据对于子查询是无关紧要的，所以在子查询中的目标列表达式都用符号*（即使给出字段名也没有实际意义）。
【例5.39】 查询选修了0211号课程的学生姓名。

SELECT SNAME

FROM S

WHERE EXISTS (SELECT *

 FROM SC 

 WHERE SNO=S.SNO AND CNO='0211')


查询结果如图5.38所示。
SQL Server执行该子查询的一种执行过程如下。
① 父查询顺序扫描表S，取出第一行元组，将该元组的SNO值(即S.SNO=' 20140123 ')传递给子查询并执行子查询，则此时子查询即为执行语句： 

SELECT *

 FROM SC 

WHERE SNO=' 20140123 ' AND CNO='0211'

查询结果如图5.39所示。



图5.38例5.39查询结果




图5.39子查询执行结果



② 子查询返回非空结果集，则父查询的WHERE子句返回逻辑真，即输出当前学生的姓名； 若子查询返回空集，则父查询的WHERE子句返回逻辑假，不输出当前学生的姓名。
③ 父查询依次扫描S表，取出下一行元组，重复上述步骤，直到S表所有元组扫描完成。

【例5.40】 查询选修了全部课程的学生姓名。

SELECT SNAME

FROM S

WHERE NOT EXISTS (SELECT *

 FROM C 

 WHERE NOT EXISTS (SELECT * 

FROM SC

WHERE SNO=S.SNO AND CNO=C.CNO)


一般情况下，有些带EXSITS或NOT EXISTS的子查询不能被其他形式的子查询等价替换，但所有带IN、比较运算符、ANY和ALL的子查询都能用带EXISTS的子查询等价替换。由于带EXISTS的相关子查询只关心内层查询是否有返回值，并不需要查询具体值，有时也是一种高效的方法。
5.2.3联合查询
SELECT语句返回的结果是若干条记录的集合。集合有其固有的一些运算，如并、交、差等。从集合运算的角度看，可以将每个 SELECT语句当作一个集合，于是，可以对任意两个 SELECT语句进行集合运算。SQL中提供了并(UNION)、交(INTERSECT)和差(EXCEPT)等几种集合运算。下面分别介绍这几种运算。
1. 集合并(UNION)运算
两个查询的并(UNION)指将两个查询的返回结果集合并到一起，同时去掉重复的记录。显然，并运算的前提是两个查询返回的结果集在结构上要一致，即结果集的字段个数要相等以及字段的数据类型要相容。
【例5.41】 查询软件学院的所有学生名及开设的所有课程名。


图5.40例5.41

查询结果


SELECT SNAME

FROM S

WHERE SDEPT='软件'

UNION

SELECT CNAME

FROM C

WHERE CDEPT='软件'


查询结果如图5.40所示。
2. 集合交(INTERSECT)运算
两个查询的交(INTERSECT)指将两个查询的返回结果集中相同的元组组合起来。同样地，交运算也要求两个查询返回的结果集在结构上要一致。
【例5.42】 查询软件学院选修了0211号课程的学生学号。

SELECT SNO

FROM S

WHERE SDEPT='软件'

INTERSECT

SELECT SNO

FROM SC

WHERE CNO='0211' 


3. 集合差(EXCEPT)运算
两个查询的差(EXCEPT)指将属于左查询结果集但不属于右查询结果集的元组组合起来。差运算同样要求两个查询返回的结果集在结构上要一致。
【例5.43】 查询选修了0121号课程但没选修0127号课程的学生学号。

SELECT SNO

FROM SC

WHERE CNO='0121'

EXCEPT

SELECT SNO

FROM SC

WHERE CNO='0127' 


总之，SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。现将其各子句功能总结如下： 
(1)  SELECT子句指定查询结果集的目标列。目标列可以直接从数据源中投影得到列名、与列名相关的表达式或数据统计的函数表达式，如算术表达式、标量函数、聚合函数等。目标列也可以是常量，如文字(数字或文本)等。如果目标列中使用了两个基本表(或视图)中相同的列名，要在列名前加表名限定，即使用“<表名>.<列名>”形式表示。
(2)  FROM子句用于指明查询的数据源。查询操作需要的数据源指基本表(或视图)。如果在查询中需要一表多用，则每次使用都需要一个表的别名标识，并在各自使用中用不同的表别名表示。
(3)  WHERE子句通过条件表达式描述关系中元组的选择条件。DBMS处理语句时，以元组为单位，逐个考察每个元组是否满足条件，将不满足条件的元组筛选掉。
（4）  GROUP BY子句的作用是按分组列的值对结果集分组。分组可以使同组的元组集中在一起，使数据能够分组统计。当SELECT子句后的目标列中有统计函数时，如果查询语句中有分组子句，则统计为分组统计，否则为对整个结果集统计。GROUP BY子句后可以接HAVING子句表达式选择条件，组选择条件为带有函数的条件表达式，它决定着整个组记录的取舍条件。
（5）  ORDER BY子句的作用是对结果集进行排序。查询结果集可以按多个排序列进行排序，每个排序列后都可以跟一个排序要求： 当排序要求为ASC时，结果集的元组按排序列值的升序排序； 当排序要求为DESC时，结果集的元组按排序列值的降序排列。
5.3本章小结


SQL是关系数据库的标准语言，其核心部分和关系代数是等价的，但它还有一些重要的功能已经超越了关系代数的表达能力。借助于SQL，人们可以实现数据操纵、数据定义和数据控制等功能。
SQL的数据操纵功能包括数据查询和数据更新两部分。SQL的数据查询用SELECT语句实现，既可以进行简单查询，也可进行多表连接查询和嵌套查询(子查询)。在查询中可以运用标量函数和聚合函数实现相关计算，并可按查询结果的列进行分组和排序。SQL应用是数据库课程学习的重点，通过本章学习，读者应能够熟练掌握SQL的语法，并能在实践中熟练运用SQL实现各种查询要求。
由于SQL是非过程化的语言，要实现SQL对数据库查询等操作的过程控制，可将SQL嵌入其他高级语言中。需要注意的是，各数据库厂商支持的SQL在遵循标准的基础上，也常常会进行不同的扩充或修改。
习题5

应用题
设有一个SPJ数据库，包括S，P，J，SPJ 4个关系模式，具体描述如下文及表5.4~表5.7所示。

设备供应商S(SNO，SNAME，STATUS，CITY)

零件P(PNO，PNAME，COLOR，WEIGHT)

工程项目J(JNO，JNAME，CITY)

供应情况SPJ(SNO，PNO，JNO，QTY)



表5.4S(设备供应商)



字 段 名SNOSNAMESTATUSCITY
数据类型charcharcharchar
长度101228
描述供应商编号供应商名称供应状态所在城市



表5.5P(零件)



字 段 名PNOPNAMECOLORWEIGHT
数据类型charcharcharInteger
长度10125
描述零件编号零件名称零件颜色零件重量



表5.6J(工程项目)



字 段 名JNOJNAMECITY
数据类型charcharchar
长度10108
描述工程编号工程名所在城市



表5.7SPJ(供应情况)



字 段 名SNOPNOJNOQTY
数据类型charcharcharInteger
长度101010
描述供应商编号零件编号工程编号供应数量

用SQL语句完成如下查询： 
(1) 查询所有供应商的姓名和所在城市； 
(2) 查询重量大于50g的所有红色零件的名称； 
(3) 查询工程编号以“J210_”开头的所有工程的工程号和所在城市； 
(4) 查询没有给任何工程供应零件的设备供应商号； 
(5) 查询使用供应商S1(供应商号)所供应零件的工程号码； 
(6) 查询工程J2(工程编号)使用的各种零件的名称及其数量，并按数量降序排序； 
(7) 查询上海厂商供应的所有零件编号(要求使用子查询完成)； 
(8) 查询使用上海产的零件的工程名称； 
(9) 查询上海供应商的数量； 
(10) 查询每个城市的工程数量，给出城市名和数量； 
(11) 查询每项工程使用的零件总数，给出工程名和零件的总数； 
(12) 查询使用3种以上零件的工程编号。