本文主要是介绍数据库系统原理与设计-第三章 sql查询语言，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
本章将使用MySQL作为数据库管理系统
文章目录
- 3.1 sql概述
- 3.1.1 SQL发展
- 3.1.2 SQL特点
- 3.1.3 SQL查询基本概念
- 3.2 单表查询
- 3.2.1 投影运算
- 查询指定列
- 消除重复元组
- 查询所有列
- 给属性列取别名
- 查询经过计算的列
- 3.2.2 选择运算
- 比较运算
- 范围查询
- 集合查询
- 空值查询
- 字符匹配查询
- 逻辑查询
- 单表查询
- 3.2.3 排序运算
- 3.2.4 查询表
- 3.2.5 聚合查询
- SQL提供的**聚合函数**(aggregate function)包括：
- 在SQL查询中，往往需要对数据进行**分类运算**（即分组运算）
- 3.3 连接查询
- 3.3.1 等值与非等值连接
- 等值连接
- 自然连接
- 非等值连接
- 3.3.2 自表连接
- 3.3.3 外连接
- 左外连接
- 右外连接
- 全外连接：连接结果中包含左、右关系中的所有元组
- 3.4 嵌套子查询
- 3.4.1 使用IN的子查询
- 3.4.2 使用比较运算符的子查询
- *3.4.3 使用存在量词EXISTS的子查询
- *3.4.4 复杂子查询实例
- 3.5 集合运算
- UNION
- INTERSECT
- EXCEPT
- 3.6 SQL查询一般格式
3.1 sql概述
SQL(Structured Query Language):结构化查询语言.是关系数据库的标准语言。
SQL是一个通用的、功能极强的关系数据库语言。
- 标准SQL命令包括：
- 数据操纵语言DML
- 查询：SELECT
- 插入：INSERT
- 修改：UPDATE
- 删除：DELETE
- 数据定义语言DDL
- 创建对象：CREATE
- 删除对象：DROP
- 修改对象：ALTER
- 数据控制语言DCL
- 权限授予：GRANT
- 权限收回：REVOKE
- 数据操纵语言DML
3.1.1 SQL发展
- SQL语言由4部分组成
- 数据定义语言DDL(Data Definition Language)
- 定义数据库的逻辑结构，包括数据库、基本表、视图和索引等，扩展DDL还支持存储过程、函数、对象、触发器等的定义
- DDL包括3类语言，即定义、修改和删除
- 数据操纵语言DML(Data Manipuplation Language)
- 对数据库的数据进行检索和更新，其中更新操作包括插入、删除和修改数据
- 数据控制语言DCL(Data Control Language)
- 对数据库的对象进行授权、用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等
- 其它
- 主要是嵌入式SQL语言和动态SQL语言的定义，规定了SQL语言在宿主语言中使用的规则
- 扩展SQL还包括数据库数据的重新组织、备份与恢复等功能
- 数据定义语言DDL(Data Definition Language)
3.1.2 SQL特点
- 综合统一
- 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体
- 高度非过程化：描述做什么，不涉及怎么做。
- 面向集合的操作方式
- 采用集合操作方式，其操作对象、操作结果都是元组的集合
- 同一种语法结构提供两种使用方式
- SQL语言既是自含式语言，又是嵌入式语言。在两种不同的使用方式下，其语法结构基本上是一致的
- 语言简洁，易学易用
- SQL语言的动词非常少，主要包括：
- 数据查询 ：SELECT；
- 数据更新： INSERT、UPDATE、DELETE；
- 数据定义 ：CREATE、DROP、ALTER；
- 数据控制 ：GRANT、REVOKE
- SQL语言的动词非常少，主要包括：
3.1.3 SQL查询基本概念
- SQL语言支持三级模式结构，外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件
- 基本表
- 数据库中独立存在的表称为基本表
- 在SQL中一个关系对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引
- 索引存放在存储文件中
- 视图
- 指从一个或几个基本表(或视图)导出的表，是虚表
- 只存放视图的定义而不存放对应数据
- 查询表
- 指查询结果对应的表
- 存储文件
- 指数据库中存放关系的物理文件
3.2 单表查询
本章所用的数据库为学生成绩管理数据库ScoreDB，其数据库模式如图3-2 ～图3-6所示，关系数据如图3-8 ～图3-12所示
3.2.1 投影运算
- SQL基本结构包括3个子句：
- SELECT子句
- 对应投影运算，指定查询结果中所需要的属性或表达式
- FROM子句
- 对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表（FROM后面是查询表必须给查询表起别名）
- WHERE子句
- 对应选择运算（包括连接运算所转化的选择运算），指定查询结果元组所需要满足的选择条件
- SELECT子句
- SELECT和FROM是必须的，其他是可选的
- 基本语法为：
SELECT A1, A2, ..., AnFROM R1, R2, ..., RmWHERE P- A1, A2, …, An代表需要查找的属性或表达式
- R1, R2, …, Rm代表查询所涉及的表
- P代表谓词(即选择条件)，如果省略WHERE子句，表示P为真
- SQL的查询结果中允许包含重复元组
- 这里描述的SQL查询执行过程只是逻辑上的，在具体执行时会进行优化处理，查询优化的内容详见第8章。
- SQL执行过程（逻辑上的理解）：
- 首先对R1, R2, …, Rm执行笛卡尔积
- 然后在笛卡尔积中选择使得谓词P为真的记录
- 再在A1, A2, …, An属性列中进行投影运算，不消除重复元组
- 如需消除重复元组，必须使用关键字DISTINCT
查询指定列
- 选取表中的全部列或指定列，通过SELECT确定要查询的属性
- [例3.1] 查询所有班级的班级编号、班级名称和所属学院
SELECT classNo, className, institute
FROM Class - 该查询的执行过程是：
- 从Class表中依次取出每个元组
- 对每个元组仅选取classNo、className和institute三个属性的值，形成一个新元组
- 最后将这些新元组组织为一个结果关系输出
消除重复元组
- 需要消除重复元组，使用DISTINCT关键字
- [例3.2] 查询所有学院的名称。
SELECT institute
FROM Class- 上述查询不消除重复元组，其查询结果如图3-14所示
- 消除重复元组，查询结果如图3-15所示
SELECT DISTINCT institute
FROM Class
查询所有列
- 可使用两种方法：
- 将所有的列在SELECT子句中列出(可以改变列的显示顺序)；
- 使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性
- [例3.3] 查询所有班级的全部信息。
SELECT classNo, className, classNum, grade, institute
FROM Class - 或
SELECT *
FROM Class
给属性列取别名
-
可为属性列取一个便于理解的列名，如用中文来显示列名
-
为属性列取别名特别适合经过计算的列
-
[例3.4] 查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名
SELECT institute 所属学院, classNo 班级编号, className 班级名称 FROM Class -
查询结果如图3-16所示。该查询可使用AS关键字取别名：
SELECT institute AS 所属学院, classNo AS 班级编号,
className AS 班级名称
FROM Class
查询经过计算的列
- 可使用属性、常数、函数和表达式
- [例3.5]查询每门课程的课程号、课程名以及周课时(周课时为课时数除以16)，并将课程名中大写字母改为小写字母输出。
SELECT courseNo 课程号, lower(courseName) 课程名, courseHour/16 AS 周课时
FROM Course - 函数lower()将大写字母改为小写字母
3.2.2 选择运算
- WHERE子句可实现关系代数中的选择运算
- WHERE常用的查询条件有：
- 比较运算：>、>=、<、<=、=、<>(或!=)
- 范围查询：[NOT] BETWEEN <值1> AND <值2>
- 集合查询： [NOT] IN <集合>
- 空值查询：IS [NOT] null
- 字符匹配查询： [NOT] LIKE <匹配字符串>
- 逻辑查询：AND、OR、NOT
比较运算
-
使用比较运算符 >、>=、<、<=、=、<>(或!=)
-
[例3.6] 查询2015级的班级编号、班级名称和所属学院。
SELECT classNo, className, instituteFROM ClassWHERE grade=2007 -
该查询的执行过程可能有多种方法：
- 全表扫描法
- 依次取出Class表中的每个元组
- 判断该元组的grade属性值是否等于2015
- 若是则将该元组的班级编号、班级名称和所属学院属性取出，形成一个新元组
- 最后将所有新元组组织为一个结果关系输出
- 该方法适用于小表，或者该表未在grade属性列上建索引
- 索引搜索法
- 如果该表在grade属性列上建有索引，且满足条件的记录不多，则可使用索引搜索法来检索数据
- 具体使用何种方法由数据库管理系统的查询优化器来选择，详见第8章内容
- 全表扫描法
-
[例3.7] 在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。
SELECT studentNo, studentName, birthdayFROM StudentWHERE year(getdate()) - year(birthday)>=19- 函数getdate()获取当前系统的日期
- 函数year()提取日期中的年份
- 请大家思考：如何表达年龄大于或等于19周岁？
范围查询
-
BETWEEN…AND用于查询属性值在某一个范围内的元组
-
NOT BETWEEN…AND用于查询属性值不在某一个范围内的元组
-
BETWEEN后是属性的下限值，AND后是属性的上限值
-
[例3.8] 在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩
SELECT studentNo, courseNo, scoreFROM ScoreWHERE score BETWEEN 80 AND 90- 该查询也可以使用逻辑运算AND实现，见例3.22
-
[例3.9] 在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, scoreFROM ScoreWHERE score NOT BETWEEN 80 AND 90- 该查询也可以使用逻辑运算OR实现，见例3.23
集合查询
-
IN用于查询属性值在某个集合内的元组
-
NOT IN用于查询属性值不在某个集合内的元组
-
IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合(该部分内容详见3.4节的内容)。
-
[例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, classNoFROM StudentWHERE native NOT IN ('南昌', '上海')- 该查询也可以使用逻辑运算AND实现，见例3.21
空值查询
-
空值表示未知或不确定的值，空值表示为null
-
IS null用于查询属性值为空值
-
IS NOT null用于查询属性值不为空值
-
IS不能用“=”替代
-
[例3.12] 在课程Course表中查询先修课程为空值的课程信息.
SELECT *FROM CourseWHERE priorCourse IS NULL -
[例3.13] 在课程Course表中查询有先修课程的课程信息。
SELECT *FROM CourseWHERE priorCourse IS NOT NULL
字符匹配查询
-
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 ClassWHERE className LIKE '%会计%'- 注意：匹配字符串必须用一对引号括起来
-
[例3.15] 在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名
SELECT studentNo, studentNameFROM StudentWHERE studentName LIKE '王__'- 注意：在中文SQL-Server中，如果匹配字符串为汉字，则一个下划线代表一个汉字；如果是西文，则一个下划线代表一个字符。
-
[例3.17] 在学生Student表中查询蒙古族的同学学号和姓名
SELECT studentNo, studentNameFROM StudentWHERE nation LIKE '蒙古族'- 注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样
- 该查询等价于下面的查询：
SELECT studentNo, studentNameFROM StudentWHERE nation='蒙古族' -
如果查询字串中本身要包含 % 和 _ ，必须使用“ESCAPE <换码字符>”短语，对通配符进行转义处理。
- [例3.18] 在班级Class表中查询班级名称中含有“16_”符号的班级名称
SELECT classNameFROM ClassWHERE className LIKE '%16\_%' ESCAPE '\'- “ESCAPE ‘\’”表示\为换码字符
- 紧跟在\符号后的 _不是通配符，而是普通的用户要查询的符号
- 如果将#字符作为换码字符，则该查询可改写为：
SELECT className FROM Class WHERE className LIKE '%16#_%' ESCAPE '#' - [例3.18] 在班级Class表中查询班级名称中含有“16_”符号的班级名称
逻辑查询
-
SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算
-
[例3.20] 在Student表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期。
SELECT studentNo, studentName, birthdayFROM StudentWHERE year(birthday)=1998 AND nation='汉族' -
注意：在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算
- 如在选课Score表中查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：
SELECT *FROM ScoreWHERE courseNo='001' AND courseNo='002'- 要实现该查询，需要使用连接运算或嵌套子查询
- 通过连接运算表示该查询，参见例3.34、例3.37
- 通过嵌套子查询，参见例3.45、例3.46
单表查询
- SQL基本结构：select…from…where…
- SQL执行过程
- 查询列（投影）
- 指定列（所有列）（消除重复元组）
- 计算列（别名）
- WHERE子句（选择）
- 比较运算：>、>=、<、<=、=、<>(或!=)
- 范围查询：[NOT] BETWEEN <值1> AND <值2>
- 集合查询： [NOT] IN <集合>
- 空值查询：IS [NOT] null
- 字符匹配查询： [NOT] LIKE <匹配字符串> % _
- 逻辑运算 AND OR NOT
3.2.3 排序运算
-
使用ORDER BY 子句实现排序运算，其语法为：
ORDER BY <表达式1> [ASC | DESC] [, <表达式2> [ASC | DESC] … ]- 其中：
- <表达式1>, <表达式2>, … 可以是属性、函数或表达式
- 缺省按升序(ASC)排序
- 按降序排序，必须指明DESC选项
- 该运算含义是：
- 在查询结果中首先按<表达式1>的值进行排序
- 在<表达式1>值相等的情况下再按<表达式2>值排序
- 依此类推
- 其中：
-
[例3.25] 在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的月份降序排序输出。
SELECT studentNo, studentName, classNo, birthdayFROM StudentWHERE sex='女'ORDER BY classNo, month(birthday) DESC- 其中：month()函数表示提取日期表达式的月份
- 查询结果如图3-22所示
3.2.4 查询表
- FROM子句后面可以是基本关系、视图，还可以是查询表
-
[例3.26] 查询1999年出生的“女”同学基本信息。
-
分析：可以先将学生表中的女生记录查询出来，然后再对查询表进行选择、投影操作。
SELECT studentNo, studentName, birthday
FROM (SELECT * FROM Student WHERE sex=‘女’) AS a
WHERE year(birthday)=1999 -
在FROM子句后是一个子查询，表示对子查询的查询结果——查询表进行查询
-
必须为查询表取一个名称(称为元组变量)，如使用AS a取名为a
FROM (SELECT * FROM Student WHERE sex=‘女’) a -
该查询等价于下面的查询：
SELECT studentNo, studentName, birthdayFROM studentWHERE year(birthday)=1999 AND sex='女' -
- With语句：
with a as(
SELECT *
FROM Student
WHERE sex='女')
SELECT studentNo, studentName, convert(varchar(10),birthday, 111) as birthdayFROM aWHERE year(birthday)=1999
3.2.5 聚合查询
- SQL查询提供了丰富的数据分类、统计和计算的功能
- 统计功能通过聚合函数来实现
- 分类功能通过分组子句来实现
- 统计和分组结合在一起实现丰富的查询功能
SQL提供的聚合函数(aggregate function)包括：
- count( [DISTINCT | ALL] {* | <列名>} )：统计关系的元组个数或一列中值的个数；
- sum( [DISTINCT | ALL] <列名> )：统计一列中值的总和(此列必须为数值型)；
- avg( [DISTINCT | ALL] <列名> )：统计一列中值的平均值(此列必须为数值型)；
- max( [DISTINCT | ALL] <列名> )：统计一列中值的最大值；
- min( [DISTINCT | ALL] <列名> )：统计一列中值的最小值。
- 指定DISTINCT谓词，表示在计算时首先消除<列名>取重复值的元组，然后再进行统计
- 指定ALL谓词或没有DISTINCT谓词，表示不消除<列名>取重复值的元组 - [例3.27] 查询学生总人数。
SELECT count(*)
FROM Student
或
SELECT count(*) 学生人数
FROM Student
- [例3.28] 查询所有选课学生的人数。
SELECT count(studentNo) 学生人数 FROM Score- 查询结果是80
- 由于一个学生可以选修多门课程，学号存在重复，为消除重复的元组，使用DISTINCT短语，将查询修改为：DISTINCT
SELECT count(DISTINCT studentNo) 学生人数 FROM Score- 查询结果为10
- [例3.29] 查询学号为“1500003”同学所选修课程的平均分。
SELECT avg(score) 平均分 FROM Score WHERE studentNo='1500003'- 在聚合函数遇到空值时，除count(*)外所有的函数皆跳过空值，只处理非空值。
在SQL查询中，往往需要对数据进行分类运算（即分组运算）
-
分组运算的目的是为了细化聚合函数的作用对象
-
如不对查询结果分组，则聚合函数作用于整个查询结果
-
如对查询结果进行分组，则聚合函数分别作用于每个组，查询结果按组聚合输出
-
SQL通过GROUP BY和HAVING子句实现分组运算
- GROUP BY对查询结果按某一列或某几列进行分组，值相等的分为一组；
- HAVING对分组的结果进行选择，仅输出满足条件的组。 该子句必须与GROUP BY子句配合使用
-
[例3.30] 查询每个同学的选课门数、平均分和最高分。
SELECT studentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分 FROM Score GROUP BY studentNo- 结果按学号StudentNo分组，将具有相同StudentNo值的元组作为一组
- 然后对每组进行相应的计数、求平均值和求最大值
-
[例3.31] 查询平均分在80分以上的每个同学的选课门数、平均分和最高分。
SELECT StudentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分 FROM Score GROUP BY StudentNo HAVING avg(score)>=80- 按学号StudentNo分组，将StudentNo值相同的元组作为一组
- 然后对每组进行计数、求平均值和求最大值
- 并判断平均值是否大于等于80，如果是则输出该组，否则丢弃该组，不作为输出结果
- 注意：例3.30和例3.31中是将重修的课程作为不同的课程来处理
3.3 连接查询
- 在实际应用中，往往会涉及到多个关系的查询，需用到连接运算或子查询
- 连接运算是关系数据库中使用最广泛的一种运算，包括等值连接、自然连接、非等值连接、自表连接和外连接等
3.3.1 等值与非等值连接
- 该运算在WHERE子句中加入连接多个关系的连接条件
- 格式为：
WHERE [<表1>.]<属性名1> <比较运算符> [<表2>.]<属性名2>
[ <逻辑运算符>
[<表3>.]<属性名3> <比较运算符> [<表4>.]<属性名4> … ] - 比较运算符包括：
- >、>=、<、<=、=、<>(或!=)
- 当比较运算符为=时，表示等值连接
- 其他运算为非等值连接
- WHERE子句的连接谓词中的属性称为连接属性
- 连接属性之间必须具有可比性
等值连接
-
[例3.32] 查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。
- 该查询的结果为学号、姓名、籍贯、班级编号和班级名称，在SELECT子句中必须包含这些属性
- 由于班级名称和所属学院在班级表Class中，学号、姓名、籍贯、班级编号在学生表Student中，FROM子句必须包含Class表和Student表
- 由于班级编号classNo既是班级表的主码，也是学生表的外码，这2个表的连接条件是claaaNo相等，在WHERE子句中必须包含连接条件Student.classNo=Class.classNo
- 本查询要查询出会计学院的学生记录，在WHERE子句中还必须包括选择条件institute=‘会计学院’
- 本查询语句为：
SELECT studentNo, studentName, native, Student.classNo, className FROM Student, Class WHERE Student.classNo=Class.classNo AND institute='会计学院' -
在连接操作中，如果涉及到多个表的相同属性名，必须在相同的属性名前加上表名加以区分
- 如Student.classNo、Class.classNo
- WHERE子句中
- Student.classNo=Class.classNo为连接条件
- institute=‘会计学院’ 为选择条件
-
可为参与连接的表取别名(称为元组变量)，在相同的属性名前加上表的别名。
- 将Student表取别名为a，Class表取别名为b，班级编号分别用a.classNo和b.classNo表示。本例可以改写为：
SELECT studentNo, studentName, native, b.classNo, classNameFROM Student [AS] a, Class [AS] bWHERE a.classNo=b.classNo AND institute='会计学院'- 对于不同的属性名，可以不在属性名前加上表名(别名)。
-
[例3.33] 查找选修了课程名称为“计算机原理”的同学学号、姓名。
- 查询结果为学号、姓名，在SELECT子句中必须包含这些属性
- 学号和姓名在学生表中，课程名称在课程表中，FROM子句必须包含学生表Student、课程表Course
- 学生表与课程表之间是多对多联系，需通过成绩表转换为两个多对一的联系，FROM子句必须包含成绩表Score
- 课程号既是课程表的主码，也是成绩表的外码，这2个表的连接条件是课程号相等；学号既是学生表的主码，也是成绩表的外码，这2个表的连接条件是学号相等。在WHERE子句中涉及三个关系的连接，其连接条件为：
Course.courseNo=Score.courseNo AND Score.studentNo=Student.studentNo - 查找选修“计算机原理”课程的同学，在WHERE子句中必须包括选择条件courseName=‘计算机原理’
- 本查询语句为：
SELECT a.studentNo, studentName FROM Student a, Course b, Score c WHERE b.courseNo=c.courseNo // 表b与表c的连接条件AND c.studentNo=a.studentNo // 表c与表a的连接条件AND b.courseName='计算机原理' -
本例使用了元组变量，其连接条件为：
b.courseNo=c.courseNo AND c.studentNo=a.studentNo -
[例3.34] 查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。
- 查询结果为学号、姓名、课程号和相应成绩，在SELECT子句中必须包含这些属性
- 学号和姓名在学生表中，课程号和成绩在成绩表中，FROM子句必须包含学生表Student和成绩表Score
- 学号既是学生表的主码，也是成绩表的外码，这2个表的连接条件是学号相等，WHERE子句必须包含这个连接条件
SELECT a.studentNo, studentName, b.courseNo, b.score FROM Student a, Score b WHERE a.studentNo=b.studentNo // 表a与表b的连接条件-
为表示同时选修“001”和“002” 课程的选择条件
- 首先在WHERE子句中直接包含选择条件courseNo=‘001’ 以查找出所有选修了“001”课程的同学
SELECT a.studentNo, studentName, b.courseNo, b.score FROM Student a, Score b WHERE a.studentNo=b.studentNo // 表a与表b的连接条件AND b.courseNo=‘001’- 注意：不能直接表示同时选修“001”和“002”课程的选择条件
AND b.courseNo=‘001’
AND b.courseNo=‘002’ ×！ - 其次，基于成绩表Score构造一个查询表c，查找出选修了编号为“002” 课程的所有同学
(SELECT * FROM Score WHERE courseNo=‘002’) c - 最后，将选修了编号为“001”课程的元组与查询表c的元组关于学号进行等值连接（连接条件是什么？）
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, (SELECT * FROM Score WHERE courseNo='002') c WHERE b.courseNo='001' AND a.studentNo=b.studentNo // 表a与表b的连接条件AND a.studentNo=c.studentNo // 表a与表c的连接条件 -
如果连接成功，表示该同学同时选修了这两门课程
-
要求按学号排序输出，需要排序语句ORDER BY
- 本查询语句为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, (SELECT * FROM Score WHERE courseNo='002') c WHERE b.courseNo='001' AND a.studentNo=b.studentNo // 表a与表b的连接条件AND a.studentNo=c.studentNo // 表a与表c的连接条件 ORDER BY a.studentNo- 该查询也可以表示为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.scoreFROM Student a, (SELECT * FROM Score WHERE courseNo='001') b, (SELECT * FROM Score WHERE courseNo='002') cWHERE a.studentNo=b.studentNo // 表a与表b的连接条件AND a.studentNo=c.studentNo // 表a与表c的连接条件ORDER BY a.studentNo- 该查询还可以表示为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, Score c WHERE a.studentNo=b.studentNo // 表a与表b的连接条件AND a.studentNo=c.studentNo // 表a与表c的连接条件AND b.courseNo=‘001’ // 表b上的选择条件AND c.courseNo='002' // 表c上的选择条件 ORDER BY a.studentNo- 注意：不能在同一个表b上同时表示选修“001”和“002”课程
AND b.courseNo=‘001’
AND b.courseNo=‘002’ ×！
-
[例3.35] 查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分，并按学号排序输出。
SELECT a.studentNo, studentName, sum(creditHour) FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND c.courseNo=b.courseNo AND score>=60 GROUP BY a.studentNo, studentName -- 输出结果的需要 HAVING sum(creditHour)>=28 ORDER BY a.studentNo- 本例输出结果中需要同时包含学号和姓名
- 因此，GROUP BY子句需要按“a.studentNo, studentName”进行聚合，不能仅按“a.studentNo”进行聚合，否则无法输出studentName
- 本查询既使用了WHERE子句，也使用了HAVING子句，都是选择满足条件的元组，但其选择的范围是不一样：
(1) WHERE子句：作用于整个查询对象，对元组进行过滤。
(2) HAVING子句：仅作用于分组，对分组进行过滤。 - 本例的查询过程是：
① 首先在Score 表中选择课程成绩大于等于60分的元组(只有60分及以上才能获得学分)，将这些元组与Student和Score 表进行连接，形成一个新关系；
② 在新关系中按学号进行分组，统计每组的总学分；
③ 将总学分大于等于28的组选择出来形成一个结果关系；
④ 将结果关系输出。
注意: 本例没有考虑一个学生选修同一门课程多次且都及格的情况
自然连接
- SQL不直接支持自然连接，完成自然连接的方法是在等值连接的基础上消除重复列
- [例3.36] 实现成绩表Score和课程表Course的自然连接。
SELECT studentNo, a.courseNo, score, courseName,
creditHour, courseHour, priorCourse
FROM Score a, Course b
WHERE a.courseNo=b.courseNo // 表a与表b的连接条件 - 本例课程编号在两个关系中同时出现，但在SELECT子句中仅需出现1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元组变量
非等值连接
- 非等值连接使用的比较少。
- 在关系代数部分已经举过了一个非等值连接的例子（P53-54，例2.16），这里就不再举例了。
- 在数据库ScoreDB中，查找课程号为AC001课程的考试中比学号为1503045的学生考得更好的所有学生的姓名和成绩。
3.3.2 自表连接
-
若某个表与自己进行连接，称为自表连接
-
[例3.37] 查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。
- 学生姓名在学生表中，FROM子句必须包含学生表(取别名为a)
- 可以考虑两个成绩表，分别记为b和c
- b表用于查询选修了编号为“001”课程的同学
- c表用于查询选修了编号为“002”课程的同学
- FROM子句还必须包含两个成绩表b和c，且在WHERE子句中包含两个选择条件：
b.courseNo=‘001’ AND c.courseNo=‘002’ - 一方面，成绩表b与成绩表c在学号上做等值连接(自表连接)，如果连接成功，表示学生同时选修了编号为“001”和“002”的课程
- 另一方面，学生表与成绩表b (或成绩表c)在学号上做等值连接。WHERE子句包含两个连接条件：
b.studentNo=c.studentNo AND a.studentNo=b.studentNo - 本查询语句为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, Score c WHERE b.courseNo='001' AND c.courseNo='002'AND a.studentNo=b.studentNo AND b.studentNo=c.studentNo ORDER BY a.studentNo- 本查询结果与例3.34相同
- 在该查询中，FROM子句后面包含了两个参与自表连接的成绩表Score，必须定义元组变量加以区分
- 自表连接的条件是b.studentNo=c.studentNo
-
[例3.38] 在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。
SELECT a.studentName, a.classNo, a.birthdayFROM Student a, Student bWHERE b.studentName='李宏冰' AND a.classNo=b.classNo或
SELECT a.studentName, a.classNo, a.birthdayFROM Student a, ( SELECT * FROM Student WHERE studentName='李宏冰' ) bWHERE a.classNo=b.classNo
3.3.3 外连接
-
在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。
-
[例3.39] 查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。
SELECT className, institute, studentNo, studentNameFROM Class a, Student bWHERE a.classNo=b.classNo AND grade=2015ORDER BY className- 从查询结果中可以看出：
- 班级表中的“金融管理15-01班”没有出现在查询结果中，原因是该班没有学生
- 在实际应用中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询
- 外连接分为左外连接、右外连接和全外连接
- 在FROM子句中，写在左边的表称为左关系，写在右边的表称为右关系
左外连接
- 连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代
- [例3.40] 使用左外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出.
SELECT className, institute, studentNo, studentNameFROM Class a LEFT OUTER JOIN Student b ON a.classNo=b.classNoWHERE grade=2015ORDER BY className, studentNo
右外连接
- 连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代
- [例3.41] 使用右外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出.
SELECT className, institute, studentNo, studentNameFROM Class a RIGHT OUTER JOIN Student b ON a.classNo=b.classNoWHERE grade=2015 ORDER BY className, studentNo
全外连接：连接结果中包含左、右关系中的所有元组
- 对左关系中没有连接上的元组，其右关系中的相应属性用空值替代
- 对右关系中没有连接上的元组，其左关系中的相应属性用空值替代
- [例3.42] 使用全外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。
SELECT className, institute, studentNo, studentNameFROM Class a FULL OUTER JOIN Student b ON a.classNo=b.classNoWHERE grade=2015ORDER BY className, studentNo
3.4 嵌套子查询
- 在SQL查询中，一个SELECT-FROM-WHERE查询语句称为一个查询块
- 将一个查询块嵌入到另一个查询块的WHERE子句或HAVING子句中，称为嵌套子查询
- 子查询的结果是集合, 因此使用子查询是集合成员的检查
- 如判断元组是否属于某个集合，集合的比较运算，以及测试是否为空集等
- 具体表现在如下几个方面：
- 元素与集合间的属于关系
- 集合之间的包含和相等关系
- 集合的存在关系
- 元素与集合元素之间的比较关系
- SQL允许多层嵌套子查询，但在子查询中，不允许使用ORDER BY子句，该子句仅用于最后结果排序
- 嵌套查询分为相关子查询和非相关子查询
- 非相关子查询指子查询的结果不依赖于上层查询
- 相关子查询指当上层查询的元组发生变化时，其子查询必须重新执行
3.4.1 使用IN的子查询
-
[例3.43] 查询选修过课程的学生姓名。
- 本例查询的含义是：
- 在学生表Student中，将学号出现在成绩表Score中(表明该学生选修过课程)的学生姓名查询出来
SELECT studentName FROM Student WHERE Student.studentNo IN(SELECT Score.studentNo FROM Score)- 在本例中，WHERE子句用于检测元素与集合间的属于关系
- 其中Student.studentNo为元素，IN为“属于”
- 嵌套语句“SELECT Score.studentNo FROM Score”的查询结果为选修过课程的所有学生的学号集合
- 该嵌套SELECT语句称为子查询
- 本例查询的含义是：
-
该查询属于非相关子查询，其查询过程为：
(1) 从Score表中查询出学生的学号studentNo，构成一个中间结果关系r；
(2) 从Student表中取出第一个元组t；
(3) 如果元组t的studentNo属性的值包含在中间结果关系r中（即t.studentNo∈r），则将元组t的studentName属性的值作为最终查询结果关系的一个元组；否则丢弃元组t；
(4) 如果Student表中还有元组，则取Student表的下一个元组t，并转第(3)步；否则转第(5)步；
(5) 将最终结果关系显示出来。 -
[例3.44] 查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。
SELECT studentNo, studentName, classNo FROM Student WHERE studentNo IN ( SELECT studentNo FROM ScoreWHERE courseNo IN ( SELECT courseNo FROMCourseWHERE courseName LIKE '%系统%' ))- WHERE子句中的IN可以实现多重嵌套，本例是一个三重嵌套的例子，该查询的执行过程可以通过图3-29来表示
- 该查询也属于非相关子查询
- 使用IN的非相关子查询的查询过程归纳如下：
- 首先执行最底层的子查询块，将该子查询块的结果作为中间关系；
- 执行上一层(即外一层)查询块，对于得到的每个元组，判断该元组是否在它的子查询结果中间关系中：
- 如果在，取出该元组中的相关属性作为最终输出结果(或该查询块的查询结果中间关系)的一个元组
- 否则舍弃该元组
- 如果已经执行完最上层查询块，则将最终结果作为一个新关系输出；否则返回第(2)步重复执行
- 等价于
SELECT studentNo, studentName, classNo FROM Student WHERE studentNo IN ( SELECT studentNo FROM Score b, Course cWHERE b.courseNo=c.courseNo AND courseName LIKE '%系统%' ) - WHERE子句中的IN可以实现多重嵌套，本例是一个三重嵌套的例子，该查询的执行过程可以通过图3-29来表示
-
[例3.44] 查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。
- 等价于
SELECT DISTINCT a.studentNo, studentName, classNo FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNoAND b.courseNo=c.courseNo AND courseName LIKE '%系统%'- 注意：并不是每一个IN子查询都可以转化为连接运算来实现！
-
[例3.45] 查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。
-
分析：
- 在SELECT子句中必须包含studentNo、studentName、courseName和score四个属性
- 学号、姓名在学生表中，课程成绩在成绩表中，课程名在课程表中，在FROM子句中必须包含学生表、课程表和成绩表，分别为这三张表取元组变量a、b、c
- 学生表、成绩表和课程表需做连接操作，在WHERE子句中必须包含连接条件：
- a.studentNo=c.studentNo AND b.courseNo=c.courseNo
- 要查询同时选修过“计算机原理”和“高等数学”两门课程的同学，在WHERE子句中必须包含如下的选择条件：
- 对于学生表，其学号必须是选修过“计算机原理”课程的学号，使用子查询：
a.studentNo IN ( SELECT studentNo FROM ScoreWHERE courseNo IN ( SELECT courseNo FROM CourseWHERE courseName='计算机原理' ) )- 对于学生表，其学号还必须是选修过“高等数学”课程的学号，使用子查询：
a.studentNo IN ( SELECT studentNo FROM ScoreWHERE courseNo IN ( SELECT courseNo FROM CourseWHERE courseName='高等数学' ) )- 这两个子查询必须同时满足，使用AND逻辑运算符
- 本查询语句为：
SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNoAND a.studentNo IN ( SELECT studentNo FROM ScoreWHERE courseNo IN ( SELECT courseNo FROM CourseWHERE courseName='计算机原理' ) ) AND a.studentNo IN ( SELECT studentNoFROM ScoreWHERE courseNo IN ( SELECT courseNo FROM CourseWHERE courseName='高等数学' ) ) ORDER BY a.studentNo, score DESC- 该查询也可以表示为如下形式：
SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNoAND a.studentNo IN ( SELECT studentNo FROM Score x, Course yWHERE x.courseNo=y.courseNo AND courseName='计算机原理' )AND a.studentNo IN ( SELECT studentNo FROM Score x, Course yWHERE x.courseNo=y.courseNo AND courseName='高等数学' ) ORDER BY a.studentNo, score DESC
3.4.2 使用比较运算符的子查询
- 元素与集合元素之间还存在更为复杂的关系，如比较关系，常用到谓词ANY(或SOME)和ALL
- ANY表示子查询结果中的某个值
- ALL表示子查询结果中的所有值
- 注意：
- 如果子查询中的结果关系仅包含一个元组，则可将ALL和ANY去掉，直接使用比较运算符
- ANY也可以用SOME替代
- [例3.47] 查询所选修课程的成绩大于所有002号课程成绩的同学学号及相应课程的课程号和成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE score>ALL( SELECT scoreFROM ScoreWHERE courseNo='002' )
-
[例3.48] 查询成绩最高分的学生的学号、课程号和相应成绩
SELECT studentNo, courseNo, score FROM Score WHERE score=( SELECT max(score)FROM Score )- 聚合函数可直接用在HAVING子句中(如例3.35)
- 聚合函数也可用于子查询中(如例3.48)，
- 聚合函数不可以直接使用在WHERE子句中。如
SELECT *
FROM Score
WHERE score=max(score) ×
-
在比较运算符中，=ANY 等价于 IN，!=ALL 等价于 NOT IN
*3.4.3 使用存在量词EXISTS的子查询
- SQL查询提供量词运算
- 量词有两种：
- 一是存在量词
- 二是全称量词
- 在离散数学中，全称量词可用存在量词替代
- SQL仅提供存在量词的运算，使用谓词EXISTS表示
- 全称量词转化通过NOT EXISTS谓词来实现
- WHERE子句中的谓词EXISTS用来判断其后的子查询的结果集合中是否存在元素
- 谓词EXISTS大量用于相关子查询中
- [例3.50] 查询选修了“计算机原理”课程的同学姓名、所在班级编号。
- 该查询可直接通过连接运算实现，也可以通过IN子查询来实现。还可以通过存在量词实现：
SELECT studentName, classNo FROM Student x WHERE EXISTS ( SELECT * FROM Score a,Course bWHERE a.courseNo=b.courseNo AND a.studentNo=x.studentNo AND courseName='计算机原理' )- 本查询涉及Student、Score和Course三个关系，属于相关子查询，查询过程如下：
(1) 首先取Student表的第一个元组x，并取其学号x.studentNo；
(2) 执行子查询，该子查询对表Score和Course进行连接，并选择其学号为x.studentNo，其课程名为“计算机原理”的元组；
(3) 如果子查询中可以得到结果(即存在元组)，则将Student表中元组x的学生姓名和所在班级编号组成一个新元组放在结果集合中；否则(即不存在元组)，直接丢弃元组x；
(4) 如果Student表中还有元组，则取Student表的下一个元组x，并取其学号x.studentNo，转第(2)步；否则转第(5)步；
(5) 将结果集合中的元组作为一个新关系输出 - 子查询的目标列通常是*
- 存在量词EXISTS只判断其后的子查询的结果集合中是否存在元素，没有必要给出查询结果的列名
- 相关子查询在SQL中属于复杂的查询，其子查询的查询条件依赖于外层查询的元组值
- 当外层查询的元组值发生变化时，其子查询要重新依据新的条件进行查询
- 使用EXISTS的相关子查询处理过程是：
(1) 首先取外层查询的第一个元组；
(2) 依据该元组的值，执行子查询；
(3) 如果子查询的结果非空(EXISTS量词返回真值)，将外层查询的该元组放入到结果集中；否则(EXISTS量词返回假值)，舍弃外层查询的该元组；
(4) 取外层查询的下一个元组，返回第(2)步重复上述过程，直到外层查询所有的元组处理完毕；
(5) 将结果集合中的元组作为一个新关系输出 - 本例可直接使用连接或IN运算来实现
- [例3.51] 查询选修了所有课程的学生姓名。
-
分析：
-
本查询要使用全称量词，含义是：
- 选择这样的学生，任意一门课程他都选修了
- 设谓词P(x, c)表示学生x选修了课程c，本查询可表示为：
-
选择这样的学生x，使
- SQL中没有全称量词，使用存在量词和取非运算来实现，转换公式如下：
- 谓词 表示学生x没有选修课程c。
- 根据该转换公式，可将上述查询描述为：
-
查询这样的学生x，不存在他没有选修的课程c
-
SELECT studentName
FROM Student x
WHERE NOT EXISTS ( SELECT * FROM Course cWHERE NOT EXISTS --判断学生x.studentNo没有选修课程c.courseNo( SELECT * FROM ScoreWHERE studentNo=x.studentNo AND courseNo=c.courseNo ))
-
[例3.52] 查询至少选修了学号为1600002学生所选修的所有课程的学生姓名。
-
分析：
-
本查询的含义是选择这样的学生，凡是1600002学生选修了的课程，他也选修了。
-
本例要使用蕴涵量词，SQL不提供蕴涵量词，可通过使用存在量词和取非运算来实现，转换公式如下：
- 用谓词R©表示1600002学生选修了c课程
- 用谓词P(x, c)表示学生x选修了c课程
- 本查询可表示为：选择这样的学生x，使
-
将该公式进行转换：
-
根据转换公式，上述查询描述为
- 选择这样的学生x，不存在某门课程c，1600002学生选修了，而学生x没有选修。
SELECT studentName FROM Student x WHERE NOT EXISTS ( SELECT * FROM Score y // 不能用Course表WHERE studentNo='1600002' --查询学生'1600002'所选修课程的情况AND NOT EXISTS --判断学生x.studentNo没有选修课程y.courseNo( SELECT * FROM ScoreWHERE studentNo=x.studentNo AND courseNo=y.courseNo )) -
-
请读者思考例3.51与例3.52之间的区别与联系
-
记所有课程的集合为A，并记A中满足谓词R的课程集合为B，即
-
例3.52，B表示学号为1600002学生所选修的所有课程。假设谓词P(x, c)表示学生x选修了课程c，
-
例3.51的查询是：选择这样的学生x，使
-
例3.52可表达为：选择这样的学生x，使
- 它等价于
- 它等价于
-
-
[例3.53] 查询至少选修了学号为1600002学生所选修的所有课程的学生学号、姓名以及该学生所选修所有课程的课程名和成绩，按学生姓名、课程名排序输出。
- 分析：
- 本查询需输出选课学生的学号、姓名以及所选修所有课程的课程名和成绩，在SELECT子句中必须包含学号、姓名、课程名和成绩
- 学号和姓名在学生表中，课程名在课程表中，成绩在成绩表中，在FROM子句中必须包含学生表、课程表和成绩表，分别取元组变量x、y、z。
- 学生表、课程表和成绩表需做连接操作，在WHERE子句中必须包含连接条件：
x.studentNo=z.studentNo AND y.courseNo=z.courseNo - 查询至少选修了学号为1600002的学生所选修的所有课程，必须首先查询学号为1600002的学生所选修的所有课程情况，使用子查询：
SELECT * FROM Score b
WHERE studentNo=‘1600002’ - 对学生表中的某个同学x.studentNo的选课记录集合，必须包含学号为1600002的学生的选课记录集合，即学号为1600002学生选修的课程，x.studentNo同学也要选修，在子查询中还必须包含一个条件表示这种包含关系：
SELECT * FROM Score b WHERE studentNo='1600002' AND EXISTS --表示x.studentNo同学也选修了学号为1600002学生选修的课程( SELECT * FROM ScoreWHERE studentNo=x.studentNo AND courseNo=b.courseNo )- 对上述查询使用双重否定：不存在1600002学生选修的某门课程，而x.studentNo学生没有选修
SELECT x.studentNo, studentName, courseName, score FROM Student x, Course y, Score z WHERE x.studentNo=z.studentNo AND y.courseNo=z.courseNo AND NOT EXISTS( SELECT * FROM Score bWHERE studentNo='1600002' --查询学生'1600002'所选修课程的情况AND NOT EXISTS --判断学生x.studentNo没有选修课程b.courseNo( SELECT * FROM ScoreWHERE studentNo=x.studentNo AND courseNo=b.courseNo )) ORDER BY studentName, courseName // 排序输出 -
[例3.53’] 查询至少选修了学号为1600002学生所选修的所有课程的学生学号、姓名以及该学生所选修的1600002学生选修过的所有课程的课程名和成绩。
SELECT x.studentNo, studentName, courseName, score FROM Student x, Course y, Score z WHERE x.studentNo=z.studentNo AND y.courseNo=z.courseNo AND NOT EXISTS( SELECT * FROM Score bWHERE studentNo='1600002' --查询学生'1600002'所选修课程的情况AND NOT EXISTS --判断学生x.studentNo没有选修课程b.courseNo( SELECT * FROM ScoreWHERE studentNo=x.studentNo AND courseNo=b.courseNo ))AND y.courseNo IN( SELECT courseNo FROM Score WHERE studentNo='1600002')
*3.4.4 复杂子查询实例
3.5 集合运算
- SQL支持集合运算
- SELECT语句查询的结果是集合
- 传统的集合操作主要包括并UNION、交INTERSECT、差EXCEPT运算
- 在执行集合运算时要求参与运算的查询结果的列数一样，其对应列的数据类型必须一致
UNION
-
[例3.57] 查询“信息管理学院”1999年出生的同学的学号、出生日期、班级名称和所属学院以及“会计学院”1998年出生的同学的学号、出生日期、班级名称和所属学院。UNION
SELECT studentNo, birthday, className, institute FROM Student a, Class b WHERE a.classNo=b.classNo AND year(birthday)=1999 AND institute='信息管理学院' UNION SELECT studentNo, birthday, className, institute FROM Student a, Class b WHERE a.classNo=b.classNo AND year(birthday)=1998 AND institute='会计学院'-
该查询实际上是查询“信息管理学院”1999年出生的或“会计学院”1998年出生的同学的学号、出生日期、班级名称和所属学院，上述SQL语句可以改写为：
SELECT studentNo, birthday, className, institute FROM Student a, Class b WHERE a.classNo=b.classNo AND ( year(birthday)=1999 AND institute=‘信息管理学院'OR year(birthday)=1998 AND institute='会计学院' ) ORDER BY institute
-
INTERSECT
-
[例3.58] 查询同时选修了“001”号和“005”号课程的同学的学号和姓名INTERSECT
SELECT a.studentNo, studentName FROM Student a, Score b WHERE a.studentNo=b.studentNo AND courseNo='001' INTERSECT SELECT a.studentNo, studentName FROM Student a, Score b WHERE a.studentNo=b.studentNo AND courseNo='005'-
本例也可用下面的SQL语句实现
SELECT a.studentNo, studentName FROM Student a, Score b WHERE a.studentNo=b.studentNo AND courseNo='001'AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo='005' )
-
EXCEPT
-
注意：SQL Server数据库不支持交运算INTERSECT，交运算完全可以用其它运算替代
-
[例3.59] 查询没有选修“计算机原理”课程的同学的学号和姓名。EXCEPT
SELECT studentNo, studentName FROM Student EXCEPT SELECT DISTINCT a.studentNo, studentName FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND courseName='计算机原理'-
本例也可用下面的SQL语句实现NOT IN
SELECT studentNo, studentName FROM Student WHERE studentNo NOT IN( SELECT studentNo FROM Score x, Course yWHERE x.courseNo=y.courseNo AND courseName='计算机原理' ) -
注意：SQL Server数据库不支持差运算EXCEPT，差运算完全可以用其它运算替代
-
3.6 SQL查询一般格式
-
SELECT共有6个子句，其中SELECT和FROM是必须的，其它是可选项，必须严格按照如下顺序排列：
SELECT [ALL | DISTINCT] <目标列表达式> [AS] [<别名>][, <目标列表达式> [AS] [<别名>] ... ] FROM {<表名> | <视图名> | <查询表>} [AS] [<别名>][, {<表名> | <视图名> | <查询表>} [AS] [<别名>] ... ] [ WHERE <条件表达式> ] [ GROUP BY <列名1> [, <列名2> ... ][ HAVING <条件表达式> ] ] [ ORDER BY <列名表达式> [ASC | DESC] [, <列名表达式> [ASC | DESC] ... ] ]其中：
- (1) <目标列表达式>可以是下面的可选格式：
- [ {<表名> | <别名>} . ] *
- [ {<表名 >| <别名>} . ]<列名>
- <函数>
- <聚合函数>
- (2) FROM子句指定查询所涉及的表、视图或查询表.
- 为操作方便，常给表取一个别名，称为元组变量
- (3) WHERE子句给出查询条件，随后的<条件表达式>中可以使用下面的谓词运算符：
- 比较运算符：>，>=，<，<=，=，<>，!=；
- 逻辑运算符：AND，OR，NOT；
- 范围运算符：[NOT] BETWEEN…AND；
- 集合运算符：[NOT] IN；
- 空值运算符：IS [NOT] null；
- 字符匹配运算符：[NOT] LIKE；
- 存在量词运算符：[NOT] EXISTS。
- 在WHERE <条件表达式>中可以包含子查询，但不可以直接使用聚合函数，若要使用聚合函数，必须引出一个子查询，
- (4) GROUP BY子句表示的含义是：
- 首先按<列名1>进行分组，<列名1>值相同的元组分为一组；
- 在同组情况下，再按<列名2>进行分组，<列名2>值相同的元组分为一组；依次类推
- 包含GROUP BY时，SELECT通常选择GROUP BY的分组属性以及聚合属性（通常将聚合函数作用于聚合属性，如avg(score)、sum(creditHour)、count(*)等）输出。
- (5) HAVING子句给出分组后的选择条件，用来选择满足条件的分组。
- 随后的<条件表达式>中可直接使用聚合函数，也可以使用子查询。
- [例3.62] 查询平均分最高的课程的课程号、课程名和平均分。
SELECT a.courseNo, courseName, avg(score) 最高平均分 FROM Course a, Score b WHERE a.courseNo=b.courseNo GROUP BY a.courseNo, courseName HAVING avg(score)=( SELECT max(avgScore)FROM ( SELECT avg(score) avgScoreFROM ScoreGROUP BY courseNo ) x --这里必须起别名)
- [例3.62] 查询平均分最高的课程的课程号、课程名和平均分。
- 随后的<条件表达式>中可直接使用聚合函数，也可以使用子查询。
- (6) ORDER BY子句实现对查询结果的排序
- 它是SQL查询的最后一个操作，必须放在最后；
- 其中的<列名表达式>可以是列名，也可以是表达式；
- 如果是表达式，则先计算表达式的值，然后排序输出；
- 排序有升序ASC和降序DESC，默认为升序。
(7) 集合运算 - SELECT语句之间可以进行集合运算，包括并UNION、交INTERSECT、差EXCEPT运算。
- (1) <目标列表达式>可以是下面的可选格式：
-
SELECT语句的含义
- 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。
如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数。
如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。
如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。
- 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。
-
SELECT语句执行顺序
- SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。
8、select 集合输出。
- SQL Select语句完整的执行顺序：
这篇关于数据库系统原理与设计-第三章 sql查询语言的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
