第3 章
SQL
学习目标
结构化查询语言(StructuredQueryLanguage,SQL)是关系数据库的标准语言,本章主
要讲授SQL 查询语言在数据库中的应用。目前,几乎所有的关系型数据库管理系统,如
Oracle、Sybase、MicrosoftSQLServer和Access等均采用SQL标准。因此,本章的教学目
标主要有两个,一是要求读者掌握对数据库的基本操作,并了解数据库管理系统的基本功
能;二是要求读者熟练掌握SQL 查询语句,并运用SQL 查询语句完成对数据库的查询
操作。
学习方法
本章重在实验,因此要求读者结合课堂讲授的知识,强化上机实训,通过实训加深对学
过的有关概念和知识点的理解,以便达到融会贯通的学习目标。
学习指南
本章的重点是3.2节、3.3节和3.4节,难点是3.4节。
本章导读
(1)各种关系代数运算功能在SQL查询语句中是如何表达的?
(2)连接操作在SQL中是如何表达的? 它们分别应用于什么情况下?
(3)在使用分组聚合查询时需要注意什么?
(4)理解相关子查询与非相关子查询的概念,它们有什么区别、分别应用于什么情
况下?(
5)理解子查询的概念,IN 子查询和存在量词EXISTS子查询有什么区别、分别应用
于什么情况下?
(6)理解查询表的概念,查询表与子查询有何异同点?
3.1 SQL 概述
SQL于1974年由Boyce等提出,并于1975—1979年在IBM 公司研制的System R数
据库管理系统上实现,现已成为国际标准。
自从SQL成为国际标准以来,很多数据库厂商都对SQL进行了再开发和扩展,但查询
第
3
章 SQL61
(SELECT )、INSERT )、删除(创建(CREATE)
插入(修改(UPDATE )、DELETE )、以及对
象删除(DROP)等标准SQL语句仍然可被用来完成几乎所有的数据库操作。
3.1.1
SQL
发展
SQL是关系数据库的标准语言,是数据库领域中一种主流语言,它经历了如下几个
阶段
(
。
1)SQL-86:第一个SQL标准,由美国国家标准局(AmericanNationalStandardInstitute,
ANSI) 1987年国际标准化组织(nentoargnztootnadztoI通过。
公布,ItrainlOaiainfrSadriain,SO)
该标准也称为SQL-1。
(2)SQL-92:在1992年,由ISO和ANSI对SQL-86进行了重新修订,发布了第二个
SQL标准SQL-92,该标准也称为SQL-2。
(3)SQL-99:随着信息技术的应用,数据库理论和技术得到了广泛的应用和发展。在
1999年,ISO发布了反映最新数据库理论和技术的标准SQL-99,该版本在SQL-2的基础
上,扩展了诸多功能,包括递归、触发、面向对象技术等。该标准也称为SQL-3。
(4)SQL-2003:该标准是最新的标准,也称为SQL-4,于2003年发布,包括9部分:
①ISO/IEC9075-1:Framework(SQL/Framework);
②ISO/IEC9075-2:Foundation(SQL/Foundation);
③ISO/IEC9075-3:CalLevelInterface(SQL/CLI);
④ISO/IEC9075-4:PersistentStoredModules(SQL/PSM);
⑤ISO/IEC9075-9:ManagementofExternalData(SQL/MED);
⑥ISO/IEC9075-10:ObjectLanguageBindings(SQL/OLB);
⑦ISO/IEC9075-11:InformationandDefinitionSchemas(SQL/Schemata);
⑧ISO/IEC9075-13:JavaRoutinesandTypesUsingtheJavaProgramming
Language(SQL/JRT);
⑨ISO/IEC9075-14:XML-RelatedSpecifications(SQL/XML )。
目前,许多数据库厂商都支持SQL-92的绝大多数标准,以及SQL-99和SQL-2003的
部分标准,并对SQL进行了扩展。这些扩展的SQL,不仅遵循标准SQL规定的功能,而且
还增强了许多功能,并赋予SQL不同的名字,如Oracle产品将SQL称为PL/SQL,Sybase
和MicrosoftSQLServer产品将SQL称为Transact-SQL 。
SQL由4部分组成,包括数据定义语言DDL 、数据操纵语言DML 、数据控制语言DCL
和其他,其功能如下:
(1)数据定义语言(datadefinitionlanguage,DDL):主要用于定义数据库的逻辑结构,
包括数据库、基本表、视图和索引等,扩展DDL还支持存储过程、函数、对象、触发器等的定
义。DDL包括3类语句,即定义、修改和删除。
(2)数据操纵语言(datamanipuplationlanguage,DML):主要用于对数据库的数据进
行检索和更新,其中更新操作包括插入、删除和修改数据。
(3)数据控制语言(datacontrollanguage,DCL):主要用于对数据库的对象进行授权、
用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等。
(4)其他:主要是嵌入式SQL和动态SQL的定义,规定了SQL在宿主语言中使用的
62
数据库系统原理与设计(第
4
版)
规则。扩展SQL 还包括数据库数据的重新组织、备份与恢复等功能。
3.1.2
SQL
特点
SQL 因其简单、灵活、易掌握,受到了广大用户的欢迎,SQL 既可以作为交互式数据库
语言使用,也可以作为程序设计语言的子语言使用,它是一个兼有关系代数和元组演算特征
的语言,其特点如下所述。
1.
综合统一
(1)SQL 集数据定义语言DDL 、数据操纵语言DML 和数据控制语言DCL 的功能于一
体,语言风格统一,可以独立完成数据库生命周期中的全部活动,包括建立数据库、定义关系
模式、创建视图和索引、数据的查询和更新(插入、删除和修改)、数据库重构、数据库安全性
控制等一系列操作,这就为数据库应用系统开发提供了良好的环境。例如,用户在数据库投
入运行后,还可根据需要随时地逐步地修改模式,并不影响数据库的运行,从而使系统具有
良好的可扩充性。
(2)在关系模型中实体(集)和实体(集)间的联系(集)均用关系表示,这种数据结构的
单一性带来了数据操作符的统一,即对实体(集)及实体(集)间联系(集)的每一种操作(如查
找、插入、删除和修改)都只需要一种操作符。
2.
高度非过程化
非关系数据模型的数据操纵语言是面向过程的语言,在完成某项操作请求时必须指定
存取路径。而用SQL 进行数据操作时,用户只需提出“做什么”,不必指明“怎么做”,因此用
户无需了解存取路径,存取路径的选择以及SQL 语句的操作过程由系统自动完成。这不仅
大大减轻了用户负担,而且有利于提高数据独立性。
3.
面向集合的操作方式
SQL 采用集合操作方式,其操作对象、操作结果都是元组的集合。而非关系数据模型
采用的是面向记录的操作方式,其操作对象是一条记录。
(1)非关系数据模型采用的是面向记录的操作方式,任何一个操作的对象都是一条记
录。例如,查询所有平均成绩在80 分以上的学生姓名,用户必须说明完成该操作请求的具
体处理过程,即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。
(2)SQL 采用集合操作方式,不仅查询操作的对象是元组的集合,而且一次更新(即插
入、删除和修改)操作的对象也可以是元组的集合。
4.
同一种语法结构提供两种使用方式
(1)SQL 既是自含式语言,又是嵌入式语言,且在两种不同的使用方式下,SQL 的语法
结构基本上是一致的。
(2)作为自含式语言,它能够独立地用于联机交互的使用方式,用户可以在终端键盘上
直接输入SQL 命令来对数据库进行操作。
(3)作为嵌入式语言,SQL 语句能够嵌入到高级语言(如Java、VC 、VB 、Delphi等)程序
中,供程序员设计应用程序时使用。
5.
语言简洁,易学易用
SQL 功能强大,但十分简洁,易学易用。SQL 的动词非常少,主要包括:
(1)数据查询:SELECT 。
第
3
章 SQL63
(2)数据定义:CREATE 、DROP 、ALTER 。
(3)数据更新:INSERT 、DELETE 、UPDATE 。
(4)数据控制:GRANT 、REVOKE 。
3.1.3
SQL
查询基本概念
SQL支持关系数据库管理系统的三级模式结构,其外模式对应视图和部分基本表,模
式对应基本表,内模式对应存储文件,如图3-1所示。
图3-
1
SQL对关系数据库模式的支持
用户使用SQL可对基本表、视图和查询表进行操作。
(1)基本表。数据库中独立存在的表称为基本表。在SQL中一个关系对应一个基本
表,一个(或多个)基本表对应一个存储文件;一个表可以带若干索引,索引也存放在存储文
件中
(
。
2)视图。视图是指从一个或几个基本表(或视图)导出的表,是虚表,只存放视图的定
义,不存放对应的数据。
(3)查询表。查询表是指一个查询的查询结果所对应的表。
(4)存储文件。存储文件是指数据库中存放关系的物理文件,其逻辑结构组成了关系
数据库的内模式,其物理结构对用户是透明的。
本章只介绍SQL查询语言,SQL数据定义语言、数据更新语言及数据库编程将在第7
章介绍,SQL数据控制语言以及SQL数据定义中的完整性控制功能将在第9章介绍。
3.单表查询
2
一条SQL查询语句可以完成宿主语言中用若干条语句才能完成的功能。SQL中最重
要的部分是查询语句,查询语句的执行离不开数据库模式。本章所用的数据库为学生成绩
管理数据库ScoreDB,其数据库模式如图3-2~图3-6所示,其中带下画线的属性或属性集
合为主码,斜体的属性为外码,其数据库模式导航图如图3-7所示;其实例数据如图3-8~
图3-12所示。
图3-
2
班级表Clas
64
数据库系统原理与设计(第
4
版)
图3-
3
学生表Student
图3-
4
课程表Course
图3-
5
学期表Term
图3-
6
成绩表Score
图3-
7
学生成绩管理数据库模式导航图
图3-
8
班级表Clas
的数据
第3 章 SQL 65
图3-9 课程表Course的数据
本章给出的所有例题的操作执行结果都是在SQLServer2019数据库中执行得到的。
3.2.1 投影运算
SQL查询语句的基本结构包括3条子句,即SELECT、FROM 和WHERE,其中:
. SELECT子句对应于关系代数中的投影运算,用来指定查询结果中所需要的属性或
表达式。
. FROM 子句对应于关系代数中的笛卡儿积,用来给出查询所涉及的表,这些表可以
是基本表、视图或查询表。
. WHERE子句对应于关系代数中的选择运算,用来指定查询结果元组所需要满足的
选择条件。
对于SQL查询语句,SELECT 和FROM 子句是必需的,其他是可选的,其基本语
法为
SELECT A 1, A 2, …, An
FROM R 1, R 2, …, Rm
WHERE P
其中,A1,A2,…,An 代表需要查找的属性或表达式;R1,R2,…,Rm 代表查询所涉及的表;
P 代表谓词(即选择条件),如果省略WHERE子句,表示不需要对元组进行选择。SQL的
查询结果中允许包含重复元组,这与关系代数中的投影运算的语义不一样。
SQL查询的执行过程为:首先对R1,R2,…,Rm 执行笛卡儿积,然后在笛卡儿积中选
择使得谓词P 为真的记录,再在A1,A2,…,An 属性列中进行投影运算,不消除重复元
组。如果需要消除重复元组,则必须使用专门的关键字DISTINCT。
上面描述的SQL查询执行过程只是逻辑上的,在具体执行时会进行优化处理,查询优
化的内容详见第8章。
1.查询指定列
选取表中的全部列或指定列,称为关系代数的投影运算。通过SELECT子句确定要查
询的属性或表达式。
66
数据库系统原理与设计(第
4
版)
图3-10
学生表Student的数据
图3-11
学期表Term
的数据
第
3
章 SQL67
图3-12
成绩表Score的数据
68 数据库系统原理与设计(第4 版)
【例3.1】 查询所有班级的班级编号、班级名称和所属学院。
SELECT classNo, className, institute
FROM Class
该查询的执行过程是:从Class表中依次取出每个元组,对每个元组仅选取classNo、
className和institute共3个属性的值,形成一个新元组,最后将这些新元组组织成一个结
果关系输出。该查询的结果如图3-13所示。
图3-13 例3.1的查询结果
2.消除重复元组
SQL查询默认是不消除重复元组,因为消除重复元组要消耗系统资源。如果需要消除
重复元组,则可以使用DISTINCT关键字。
【例3.2】 查询所有学院的名称。
SELECT institute
FROM Class
上述查询不消除重复元组,其查询结果如图3-14所示。如果需要消除重复元组,则可
使用如下查询,其查询结果如图3-15所示。
SELECT DISTINCT institute
FROM Class
图3-14 例3.2的查询结果一图3-15 例3.2的查询结果二
3.查询所有列
查询所有的属性列,SQL可以使用两种方法:一是将所有的列在SELECT子句中列出
第3 章 SQL 69
(可以改变列的显示顺序);二是使用*符号,*表示所有属性,此时按照表定义时的顺序显
示所有属性。
【例3.3】 查询所有班级的全部信息。
SELECT classNo, className, classNum, grade, institute
FROM Class
或
SELECT *
FROM Class
4.给属性列取别名
可以为属性列取一个便于理解的列名,如用中文来显示列名,为属性列取别名特别适合
那些经过计算的列。
【例3.4】 查询所有班级的所属学院、班级编号和班级名称,要求用中文显示列名。
SELECT institute 所属学院, classNo 班级编号, className 班级名称
FROM Class
该查询的结果如图3-16所示。该查询也可以使用AS关键字来取别名,如
SELECT institute AS 所属学院, classNo AS 班级编号, className AS 班级名称
FROM Class
5.查询经过计算的列
SELECT子句中可以使用属性、常量、函数和表达式,如果是函数或表达式,则先计算
函数或表达式的值,然后将计算的结果显示出来。
【例3.5】 查询每门课程的课程号、课程名以及周课时(周课时等于课时数除以16),并
将课程名中大写字母改为小写字母输出。
SELECT courseNo 课程号, lower(courseName) 课程名, courseHour/16 AS 周课时
FROM Course
其中,函数lower()表示将大写字母改为小写字母,其查询结果如图3-17所示。
图3-16 例3.4的查询结果图3-17 例3.5的查询结果
70 数据库系统原理与设计(第4 版)
3.2.2 选择运算
WHERE子句可以实现关系代数中的选择运算,用于查询满足选择条件的元组,这是
查询中涉及最多的一类查询。WHERE子句中常用的查询条件运算符如下所示。
. 比较运算:>、>=、<、<=、=、<>(或!=)。
. 范围查询:[NOT]BETWEEN…AND。
. 集合查询:[NOT]IN。
. 空值查询:[NOT]ISNULL。
. 字符匹配查询:[NOT]LIKE。
. 逻辑查询:AND、OR、NOT。
1.比较运算
使用比较运算符>、>=、<、<=、=、<>(或!=)实现相应的比较运算。
【例3.6】 查询2021级的班级编号、班级名称和所属学院。
SELECT classNo, className, institute
FROM Class
WHERE grade=2021
其查询结果如图3-18所示。
图3-18 例3.6的查询结果
该查询的执行过程可能有多种方法:一种是全表扫描法,即依次取出Class表中的每个
元组,判断该元组的grade属性值是否等于2021,若是则将该元组的班级编号、班级名称和
所属学院属性取出,形成一个新元组,最后将所有新元组组织为一个结果关系输出,该方法
适用于小表,或者该表未在grade属性列上创建索引。另一种是索引搜索法,如果该表在
grade属性列上建有索引,且满足条件的记录不多,则可以使用索引搜索法来检索数据。具
体使用何种方法由数据库管理系统的查询优化器来选择,详见第8章内容。
【例3.7】 在学生Student表中查询年龄大于或等于20岁的同学学号、姓名和出生
日期。
SELECT studentNo, studentName, birthday
FROM Student
WHERE year(getdate())-year(birthday)>=20
其中,函数getdate()可获取当前系统的日期,函数year()用于提取日期中的年份。
2.范围查询
BETWEEN…AND 可用于查询属性值在某一个范围内的元组,NOTBETWEEN…
AND可用于查询属性值不在某一个范围内的元组。BETWEEN 后面是属性的下限值,
第3 章 SQL 71
AND后面是属性的上限值。
【例3.8】 在选课Score表中查询成绩为80~90分的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE score BETWEEN 80 AND 90
该查询也可以使用逻辑运算AND实现,见例3.22。
【例3.9】 在选课Score表中查询成绩不为80~90分的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE score NOT BETWEEN 80 AND 90
该查询也可以使用逻辑运算OR实现,见例3.23。
3.集合查询
IN可用于查询属性值在某个集合内的元组,NOTIN 可用于查询属性值不在某个集合
内的元组。IN 后面是集合,可以是具体的集合,也可以是查询出来的元组集合(该部分内容
详见3.4.1节)。
【例3.10】 在选课Score表中查询选修了001、005或003课程的同学学号、课程号和
相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE courseNo IN ('001', '005', '003')
该查询也可以使用逻辑运算OR实现,见例3.19。
【例3.11】 在学生Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍
贯和所属班级(编号)。
SELECT studentName, native, classNo
FROM Student
WHERE native NOT IN ('南昌', '上海')
该查询也可以使用逻辑运算AND实现,见例3.21。
4.空值查询
SQL支持空值运算,空值表示未知或不确定的值,空值表示为NULL。ISNULL可用于
查询属性值为空值;ISNOTNULL可用于查询属性值不为空值。这里的IS不能用“=”替代。
【例3.12】 在课程Course表中查询先修课程为空值的课程信息。
SELECT *
FROM Course
WHERE priorCourse IS NULL
查询结果如图3-19所示。
72 数据库系统原理与设计(第4 版)
图3-19 例3.12的查询结果
【例3.13】 在课程Course表中查询有先修课程的课程信息。
SELECT *
FROM Course
WHERE priorCourse IS NOT NULL
5.字符匹配查询
对于字符型数据,LIKE可用于字符匹配查询。LIKE的语法格式为
[NOT] LIKE <匹配字符串> [ESCAPE <换码字符>]
查询的含义是:如果在LIKE前没有NOT,则查询指定的属性列值与<匹配字符串>
相匹配的元组;如果在LIKE前有NOT,则查询指定的属性列值不与<匹配字符串>相匹
配的元组。<匹配字符串>可以是一个具体的字符串,也可以包括通配符%和_。其中,符
号%表示任意长度的字符串,如ab%,表示所有以ab 开头的任意长度的字符串;再如
zhang%ab,表示以zhang开头,以ab结束,中间可以包含任意多个字符的字符串。
符号_(下画线)表示任意一个字符,如ab_,表示所有以ab开头的3个字符的字符串,
其中第3个字符为任意字符;再如a__b表示所有以a开头,以b结束的4个字符的字符串。
其中第2、第3个字符为任意字符。
【例3.14】 在班级Class表中查询班级名称中含有“会计”的班级信息。
SELECT *
FROM Class
WHERE className LIKE '%会计%'
注意:匹配字符串必须用一对引号括起来。
【例3.15】 在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名。
SELECT studentNo, studentName
FROM Student
WHERE studentName LIKE '王__'
注意:在中文SQL-Server中,如果匹配字符串为汉字,则1条下画线代表1个汉字;如
果是西文,则1条下画线代表1个字符。
【例3.16】 在学生Student表中查询名字中不含有“福”的同学学号和姓名。
SELECT studentNo, studentName
FROM Student
WHERE studentName NOT LIKE '%福%'
第3 章 SQL 73
【例3.17】 在学生Student表中查询蒙古族的同学学号和姓名。
SELECT studentNo, studentName
FROM Student
WHERE nation LIKE '蒙古族'
注意:如果匹配字符串中不含有%和_,则LIKE与比较运算符“=”的查询结果一样。
因此,该查询等价于下面的查询:
SELECT studentNo, studentName
FROM Student
WHERE nation='蒙古族'
如果用户的查询字符串中本身要包含通配符%和_,必须使用ESCAPE <换码字符>
短语,对通配符进行转义处理。
【例3.18】 在班级Class表中查询班级名称中含有“22_”符号的班级名称。
SELECT className
FROM Class
WHERE className LIKE '%22\_%' ESCAPE '\'
图3-20 例3.18的
查询结果
ESCAPE\' '表示\为换码字符,这样紧跟在\符号后的_不是通配符,
而是普通的用户要查询的符号。查询结果如图3-20所示。
如果将#字符作为换码字符,则该查询可改写为:
SELECT className
FROM Class
WHERE className LIKE '%16#_%' ESCAPE '#'
6.逻辑运算
SQL提供了复合条件查询,使用AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑
或和逻辑非运算。
【例3.19】 在选课Score表中查询选修了001、005或003课程的同学学号、课程号和
相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE courseNo='001' OR courseNo='005' OR courseNo='003'
在例3.10中使用的是集合运算,本例中采用逻辑“或”运算。
注意:在逻辑运算中,不可以对同一个属性进行逻辑“与”的等值运算。例如,如果要在
Score表中查询同时选修了001和002课程的同学的选课信息,如下查询是错误的,得不到结果。
SELECT *
FROM Score
WHERE courseNo='001' AND courseNo='002'
74 数据库系统原理与设计(第4 版)
要实现该查询,需要使用连接运算或嵌套子查询进行表示。通过连接运算表示该查询,
请参见例3.34和例3.36;通过嵌套子查询表示该查询,请参见例3.44和例3.45。
【例3.20】 在Student表中查询2003年出生且民族为“汉族”的同学学号、姓名、出生
日期。
SELECT studentNo, studentName, birthday
FROM Student
WHERE year(birthday)=2003 AND nation='汉族'
【例3.21】 在Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和
所属班级(编号)。
SELECT studentName, native, classNo
FROM Student
WHERE native!='南昌' AND native!='上海'
【例3.22】 在选课Score表中查询成绩为80~90分的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE score>= 80 AND score<=90
【例3.23】 在选课Score表中查询成绩不为80~90分的同学学号、课程号和相应
成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE score<80 OR score>90
3.2.3 排序运算
SQL支持排序运算,通过使用ORDERBY子句实现,其语法为:
ORDER BY <表达式1> [ASC | DESC] [, <表达式2> [ASC | DESC] … ]
其中,<表达式1>,<表达式2>,…,可以是属性、函数或表达式,默认按升序(ASC)排
序;若要按降序排序,必须指明DESC选项。
该运算表示的含义是:在查询结果中首先按<表达式1>的值进行排序,在<表达式1>
值相等的情况下再按<表达式2>值排序,以此类推。
【例3.24】 在学生Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍
贯和所属班级(编号),并按籍贯的降序排序输出。
SELECT studentName, native, classNo
FROM Student
WHERE native!='南昌' AND native!='上海'
ORDER BY native DESC
第3 章 SQL 75
查询结果如图3-21所示。
【例3.25】 在学生Student表中查询“女”学生的学号、姓名、所属班级(编号)和出生日
期,并按班级编号的升序、出生日期的月份降序排序输出。
SELECT studentNo, studentName, classNo, birthday
FROM Student
WHERE sex='女'
ORDER BY classNo, month(birthday) DESC
其中,month()函数表示提取日期表达式的月份。查询结果如图3-22所示。
图3-21 例3.24的查询结果 图3-22 例3.25的查询结果
3.2.4 查询表
查询表是指一个查询的查询结果所对应的表。SQL中的FROM 子句后面可以是基本
表、视图,还可以是查询表。
【例3.26】 查询2004年出生的“女”同学基本信息。
分析:可以先构造一个查询从学生表中将女生记录查询出来,并将查询结果作为一个
查询表,然后再对该查询表进行选择、投影操作。
SELECT studentNo, studentName, birthday
FROM (SELECT * FROM Student WHERE sex='女') AS a
WHERE year(birthday)=2004
该查询在FROM 子句的后面是一个查询表,表示对该查询表进行查询,必须为查询表
取一个名称(该名称称为元组变量),如使用ASa 取名为a,也可以省略“AS”,写成
FROM (SELECT * FROM Student WHERE sex='女') a
该查询等价于下面的查询:
SELECT studentNo, studentName, birthday
FROM Student
WHERE year(birthday)=2004 AND sex='女'
76 数据库系统原理与设计(第4 版)
3.2.5 聚合查询
SQL查询提供了丰富的数据分类、统计和计算的功能,其统计功能是通过聚合函数来
实现,分类功能是通过分组子句来实现,并且统计和分组往往结合在一起实现丰富的查询
功能。
1.聚合函数
SQL查询提供的聚合函数(aggregatefunction)主要包括以下几个。
.count([DISTINCT|ALL]{*|<表达式>}):统计关系的元组个数或关系中
指定表达式的值的个数。
.sum([DISTINCT|ALL]<表达式> ):统计关系中指定表达式的值的总和(此
表达式必须为数值型)。
.avg([DISTINCT|ALL]<表达式> ):统计关系中指定表达式的值的平均值(此
表达式必须为数值型)。
. max([DISTINCT|ALL]<表达式> ):统计关系中指定表达式的值的最大值。
. min([DISTINCT|ALL]<表达式> ):统计关系中指定表达式的值的最小值。
如果使用DISTINCT关键字,表示在计算时首先消除关系中<表达式>取重复值的元
组,然后再进行统计;如果使用ALL关键字(这是默认方式),表示不消除关系中<表达式>取
重复值的元组。
【例3.27】 查询蒙古族的学生总人数。
SELECT count(*)
FROM Student
WHERE nation='蒙古族'
查询结果如图3-23(a)所示,可以看出,输出的查询结果没有列名。为了便于理解,可以
对计算列取一个列名,上述查询可修改为如下,其查询结果如图3-23(b)所示。
SELECT count(*) 学生人数
FROM Student
WHERE nation='蒙古族'
图3-23 例3.27的查询结果
【例3.28】 查询所有选课学生的人数。
SELECT count(studentNo) 学生人数
FROM Score
该查询的结果是80。由于一个学生可以选修多门课程,学号存在重复,上述查询没有
消除重复元组。为了消除重复的元组,必须使用DISTINCT关键字,可将查询修改为:
第3 章 SQL 77
SELECT count(DISTINCT studentNo) 学生人数
FROM Score
此时的查询结果为10。
【例3.29】 查询学号为2200003同学所选课程的平均分。
SELECT avg(score) 平均分
FROM Score
WHERE studentNo='2200003'
在聚合函数遇到空值时,除count(*)外,其他函数皆跳过空值,只处理非空值。
2.分组聚合
在SQL查询中,往往需要对数据进行分类运算(即分组运算),分组运算的目的是细化
聚合函数的作用对象。如果不对查询结果进行分组,则聚合函数作用于满足WHERE选择
条件的整个查询结果,最后输出聚合计算结果;如果对满足WHERE选择条件的查询结果
进行分组,则聚合函数分别作用于每个组中的所有元组,最后按组输出聚合计算结果。SQL
语句中通过使用GROUPBY和HAVING子句来实现分组运算,其中:
. GROUPBY子句对满足WHERE选择条件的查询结果按某一列或某几列进行分
组,值相等的分为一组;
. HAVING子句对分组的聚合计算结果进行选择,仅输出满足HAVING条件的组的
聚合计算结果。该子句必须与GROUPBY子句配合使用。
【例3.30】 查询至少有6门选修课程已获得学分(成绩大于或等于60分才能获得学
分)的学生情况,要求输出学号以及该同学已获得学分的选课门数、平均分和最高分。
SELECT studentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分
FROM Score
WHERE score>=60
GROUP BY studentNo
HAVING count(*)>=6
图3-24 例3.30的查询结果
查询结果如图3-24所示。
该查询首先按WHERE子句中的查询条件score>=
60筛选出已获得学分的学生选课元组;然后按学号
studentNo进行分组,将具有相同studentNo值的元组
作为一组,并对每组中的元组进行相应的聚合计算,包
括计数(统计选课门数)、求平均分和最高分;最后按
HAVING条件(即计数结果大于或等于6)对分组聚合
计算结果元组进行筛选,并输出符合要求的结果元组。
【例3.31】 查询学生人数不少于3人的每个民族的人数和平均年龄。
SELECT nation, count(*) 人数, avg(year(getdate())-year(birthday)) 平均年龄
FROM Student
GROUP BY nation
HAVING count(*)>=3
78 数据库系统原理与设计(第4 版)
该查询按民族nation进行分组,将具有相同nation值的元组作为一组,然后对每组中
的元组进行相应的计数(即计算学生人数)和求平均值(即对表达式year(getdate())-year
(birthday)求平均值),并判断每组的计数结果(即学生人数)是否大于或等于3,如果是则输
出该组的聚合计算结果元组,否则丢弃该组元组,不作为输出结果。
注意:例3.29和例3.30的结果中是将重修课程所取得的多个成绩都参与计算的。对
于一门课程,不管你重修该课程多少次,应该是只有一个成绩参与计算,通常是将该课程多
次选修中最高的一个成绩或最后一次选修的成绩认定为该课程的成绩。
3.3 连接查询
前面的查询实例都是针对一个关系进行操作,而在实际应用中,往往会涉及多个关系的
查询,这时需用到连接运算或子查询,本节介绍连接运算。
连接运算是关系数据库中使用最广泛的一种运算,包括等值连接、自然连接、非等值连
接、自表连接和外连接等。
3.3.1 等值与非等值连接
等值与非等值连接运算是在WHERE子句中加入连接多个关系的连接条件,其格式为:
WHERE [表1.]<属性名1> <比较运算符> [表2.]<属性名2>
[<逻辑运算符> [表3.]<属性名3> <比较运算符> [表4.]<属性名4> …]
比较运算符包括>、>=、<、<=、=、<>(或!=)。当比较运算符为=时,表示等值
连接;其他运算为非等值连接。WHERE子句的连接谓词中的属性称为连接属性,连接属
性之间必须具有可比性。
1.等值连接
【例3.32】 查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。
分析:
(1)该查询的结果为同学的学号、姓名、籍贯、班级编号和所在班级名称,在SELECT
子句中必须包含这些属性。
(2)由于班级名称和所属学院在班级表Class中,学号、姓名、籍贯、班级编号在学生表
Student中,因此FROM 子句必须包含Class表和Student表。
(3)由于班级编号classNo既是班级表的主码,也是学生表的外码,这2个表的连接条
件是classNo相等,因此在WHERE 子句中必须包含连接条件Student.classNo=Class.
classNo。
(4)本查询要查询出会计学院的学生记录,因此在WHERE子句中还必须包括选择条
件institute='会计学院'。
(5)本查询语句为:
SELECT studentNo, studentName, native, Student.classNo, className
FROM Student, Class
WHERE Student.classNo=Class.classNo AND institute='会计学院'
第3 章 SQL 79
在连接操作中,如果涉及多个表的相同属性名,必须在相同的属性名前加上表名加以区
分,如Student.classNo、Class.classNo。WHERE 子句中的Student.classNo= Class.
classNo为连接条件,institute='会计学院'为选择条件。
为了简化,可为参与连接的表取别名(称为元组变量),这样在相同的属性名前加上表的
别名就可以了,本例可以改写为:
SELECT studentNo, studentName, native, b .classNo, className
FROM Student AS a , Class AS b
WHERE a .classNo=b .classNo AND institute='会计学院'
或者
SELECT studentNo, studentName, native, b .classNo, className
FROM Student a , Class b
WHERE a .classNo=b .classNo AND institute='会计学院'
将Student表取别名为a,Class表取别名为b,班级编号分别用a.classNo和b.classNo
表示。对于多个表中的不同属性名,可以不要在属性名前加上表名。
注意:SQL不直接支持自然连接,完成自然连接的方法是在等值连接的基础上消除重
复列。本例中班级号classNo在学生表Student、班级表Class两个关系中同时出现,但在
SELECT子句中仅需要出现1次,因此使用b.classNo,也可以使用a.classNo。其他列名是
唯一的,不需要加上元组变量。
【例3.33】 查找选修了课程名称为“计算机原理”的同学的学号、姓名。
分析:
(1)该查询的结果为学号、姓名,在SELECT子句中必须包含这些属性。
(2)由于学生的学号和姓名在学生表中,课程名称在课程表中,因此FROM 子句必须
包含学生表Student、课程表Course;由于学生表与课程表之间是多对多联系,需要通过成
绩表转换为两个多对一的联系,因此FROM 子句还必须包含成绩表Score。
(3)由于课程号既是课程表的主码,也是成绩表的外码,这2个表的连接条件是课程号
相等;学号既是学生表的主码,也是成绩表的外码,这2个表的连接条件是学号相等。因此
在WHERE子句中涉及三个关系的连接,其连接条件为:
Course.courseNo=Score.courseNo AND Score.studentNo=Student.studentNo
(4)本查询要查找出选修了课程名称为“计算机原理”的课程的同学情况,因此在
WHERE子句中还必须包括选择条件courseName='计算机原理'。
(5)本查询语句为:
SELECT a .studentNo, studentName
FROM Student a , Course b , Score c
WHERE b .courseNo=c .courseNo AND c .studentNo=a .studentNo
AND b .courseName='计算机原理'
本例使用了元组变量,其连接条件为: