数据库系统原理与设计-第三章 sql查询语言-CSDN博客

本文链接：https://blog.csdn.net/rc4gyyc/article/details/136909395

本章将使用MySQL作为数据库管理系统

文章目录

3.1 sql概述
3.2　单表查询
3.4　嵌套子查询
3.5 集合运算
3.6　SQL查询一般格式

3.1 sql概述

SQL(Structured Query Language):结构化查询语言.是关系数据库的标准语言。
SQL是一个通用的、功能极强的关系数据库语言。

标准SQL命令包括：
- 数据操纵语言DML
  - 查询：SELECT
  - 插入：INSERT
  - 修改：UPDATE
  - 删除：DELETE
- 数据定义语言DDL
  - 创建对象：CREATE
  - 删除对象：DROP
  - 修改对象：ALTER
- 数据控制语言DCL
  - 权限授予：GRANT
  - 权限收回：REVOKE

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还包括数据库数据的重新组织、备份与恢复等功能

3.1.2 SQL特点

综合统一
- 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体
高度非过程化：描述做什么，不涉及怎么做。
面向集合的操作方式
- 采用集合操作方式，其操作对象、操作结果都是元组的集合
同一种语法结构提供两种使用方式
- SQL语言既是自含式语言，又是嵌入式语言。在两种不同的使用方式下，其语法结构基本上是一致的
语言简洁，易学易用
- SQL语言的动词非常少，主要包括：
  - 数据查询：SELECT；
  - 数据更新： INSERT、UPDATE、DELETE；
  - 数据定义：CREATE、DROP、ALTER；
  - 数据控制：GRANT、REVOKE

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和FROM是必须的，其他是可选的
基本语法为：
```
  SELECT A1, A2, ..., An
  FROM R1, R2, ..., Rm
  WHERE 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, institute
   FROM Class
   WHERE grade=2007

该查询的执行过程可能有多种方法：
- 全表扫描法
  - 依次取出Class表中的每个元组
  - 判断该元组的grade属性值是否等于2015
  - 若是则将该元组的班级编号、班级名称和所属学院属性取出，形成一个新元组
  - 最后将所有新元组组织为一个结果关系输出
  - 该方法适用于小表，或者该表未在grade属性列上建索引
- 索引搜索法
  - 如果该表在grade属性列上建有索引，且满足条件的记录不多，则可使用索引搜索法来检索数据
- 具体使用何种方法由数据库管理系统的查询优化器来选择，详见第8章内容
[例3.7] 在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。
```
   SELECT studentNo, studentName, birthday
   FROM Student
   WHERE year(getdate()) - year(birthday)>=19
```
- 函数getdate()获取当前系统的日期
- 函数year()提取日期中的年份
- 请大家思考：如何表达年龄大于或等于19周岁？

范围查询

BETWEEN…AND用于查询属性值在某一个范围内的元组
NOT BETWEEN…AND用于查询属性值不在某一个范围内的元组
BETWEEN后是属性的下限值，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

集合查询

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, classNo
   FROM Student
   WHERE native NOT IN ('南昌', '上海')
```
- 该查询也可以使用逻辑运算AND实现，见例3.21

空值查询

空值表示未知或不确定的值，空值表示为null
IS null用于查询属性值为空值
IS NOT null用于查询属性值不为空值
IS不能用“=”替代
[例3.12] 在课程Course表中查询先修课程为空值的课程信息.
```
   SELECT *
   FROM Course
   WHERE priorCourse IS NULL
```

[例3.13] 在课程Course表中查询有先修课程的课程信息。

   SELECT *
   FROM Course
   WHERE 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 Class
   WHERE className LIKE '%会计%'
```
- 注意：匹配字符串必须用一对引号括起来
[例3.15] 在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名
```
   SELECT studentNo, studentName
   FROM Student
   WHERE studentName LIKE '王__'
```
- 注意：在中文SQL-Server中，如果匹配字符串为汉字，则一个下划线代表一个汉字；如果是西文，则一个下划线代表一个字符。
[例3.17] 在学生Student表中查询蒙古族的同学学号和姓名
```
   SELECT studentNo, studentName
   FROM Student
   WHERE nation LIKE '蒙古族'
```
- 注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样
- 该查询等价于下面的查询：
```
   SELECT studentNo, studentName
   FROM Student
   WHERE nation='蒙古族'
```
如果查询字串中本身要包含 % 和 _ ，必须使用“ESCAPE <换码字符>”短语，对通配符进行转义处理。
- [例3.18] 在班级Class表中查询班级名称中含有“16_”符号的班级名称
```
   SELECT className
   FROM Class
   WHERE className LIKE '%16\_%' ESCAPE '\'
```
  - “ESCAPE ‘\’”表示\为换码字符
  - 紧跟在\符号后的 _不是通配符，而是普通的用户要查询的符号
- 如果将#字符作为换码字符，则该查询可改写为：
```
SELECT className
FROM Class
WHERE className LIKE '%16#_%' ESCAPE '#'
```

逻辑查询

SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算

[例3.20] 在Student表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期。

   SELECT studentNo, studentName, birthday
	   FROM Student
	   WHERE year(birthday)=1998 AND nation='汉族'

注意：在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算
- 如在选课Score表中查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：
```
 SELECT *
 FROM Score
 WHERE 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, birthday
   FROM Student
   WHERE 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, birthday
   FROM student
   WHERE year(birthday)=1999 AND sex='女'
```
With语句：

with a as(
SELECT * 
FROM Student 
WHERE sex='女')
SELECT studentNo, studentName, convert(varchar(10),birthday, 111) as birthday
   FROM  a
   WHERE 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, className
  FROM Student [AS] a, Class [AS] b
  WHERE 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.score
 FROM Student a, 
              (SELECT * FROM Score WHERE courseNo='001') b, 
              (SELECT * FROM Score WHERE courseNo='002') c
 WHERE 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.birthday
 FROM Student a, Student b
 WHERE b.studentName='李宏冰' AND a.classNo=b.classNo

或

 SELECT a.studentName, a.classNo, a.birthday
 FROM Student a, 
              ( SELECT * FROM Student WHERE studentName='李宏冰' ) b
 WHERE a.classNo=b.classNo

3.3.3 外连接

在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。
[例3.39] 查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。
```
   SELECT className, institute, studentNo, studentName
   FROM Class a, Student b
   WHERE a.classNo=b.classNo AND grade=2015
   ORDER BY className
```
- 从查询结果中可以看出：
- 班级表中的“金融管理15-01班”没有出现在查询结果中，原因是该班没有学生
- 在实际应用中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询
- 外连接分为左外连接、右外连接和全外连接
- 在FROM子句中，写在左边的表称为左关系，写在右边的表称为右关系

左外连接

连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代
[例3.40] 使用左外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出.

SELECT className, institute, studentNo, studentName
   FROM Class a LEFT OUTER JOIN Student b ON a.classNo=b.classNo
   WHERE grade=2015
   ORDER BY className, studentNo

右外连接

连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代
[例3.41] 使用右外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出.

   SELECT className, institute, studentNo, studentName
   FROM Class a RIGHT OUTER JOIN Student b ON a.classNo=b.classNo
  WHERE grade=2015 
  ORDER BY className, studentNo

全外连接：连接结果中包含左、右关系中的所有元组

对左关系中没有连接上的元组，其右关系中的相应属性用空值替代
对右关系中没有连接上的元组，其左关系中的相应属性用空值替代
[例3.42] 使用全外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。

   SELECT className, institute, studentNo, studentName
   FROM Class a FULL OUTER JOIN Student b 
                 ON a.classNo=b.classNo
   WHERE grade=2015
   ORDER 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 Score
        WHERE courseNo IN 
             ( SELECT courseNo FROMCourse
                WHERE courseName LIKE '%系统%' )
     )
```
- WHERE子句中的IN可以实现多重嵌套，本例是一个三重嵌套的例子，该查询的执行过程可以通过图3-29来表示
- 该查询也属于非相关子查询
- 使用IN的非相关子查询的查询过程归纳如下：
  - 首先执行最底层的子查询块，将该子查询块的结果作为中间关系；
  - 执行上一层(即外一层)查询块，对于得到的每个元组，判断该元组是否在它的子查询结果中间关系中：
    - 如果在，取出该元组中的相关属性作为最终输出结果(或该查询块的查询结果中间关系)的一个元组
    - 否则舍弃该元组
  - 如果已经执行完最上层查询块，则将最终结果作为一个新关系输出；否则返回第(2)步重复执行
- 等价于
```
SELECT studentNo, studentName, classNo
FROM Student
WHERE studentNo IN 
      ( SELECT studentNo 
          FROM Score b, Course c
          WHERE b.courseNo=c.courseNo 
                AND courseName LIKE '%系统%' 
       )
```

[例3.44] 查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。

等价于

SELECT DISTINCT a.studentNo, studentName, classNo
FROM Student a, Score b, Course c
WHERE a.studentNo=b.studentNo
       AND 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 Score
	   WHERE courseNo IN ( SELECT courseNo FROM Course
             WHERE courseName='计算机原理' ) )
```
- 对于学生表，其学号还必须是选修过“高等数学”课程的学号，使用子查询：
```
a.studentNo IN 
( SELECT studentNo FROM Score
   WHERE courseNo IN ( SELECT courseNo FROM Course
         WHERE courseName='高等数学' ) ) 
```
- 这两个子查询必须同时满足，使用AND逻辑运算符
本查询语句为：


SELECT  a.studentNo, studentName, courseName, score
FROM Student a, Course b, Score c
WHERE  a.studentNo=c.studentNo AND b.courseNo=c.courseNo
      AND   a.studentNo IN 
		    ( SELECT studentNo FROM Score
		       WHERE courseNo IN 
                      ( SELECT courseNo FROM Course
                         WHERE courseName='计算机原理' ) ) 
      AND   a.studentNo IN 
           ( SELECT studentNoFROM Score
              WHERE courseNo IN 
                     ( SELECT courseNo FROM Course
                        WHERE 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.courseNo
	 AND   a.studentNo IN 
      	 ( SELECT studentNo FROM Score x, Course y
          WHERE x.courseNo=y.courseNo 
                 AND courseName='计算机原理' )
	 AND   a.studentNo IN 
          ( SELECT studentNo FROM Score x, Course y
            WHERE 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 score
        FROM Score
        WHERE 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 b
       WHERE 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 c
        WHERE NOT EXISTS   
              --判断学生x.studentNo没有选修课程c.courseNo
              ( SELECT * FROM Score
                WHERE 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 Score
                  WHERE 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 Score
                  WHERE 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 b
                    WHERE studentNo='1600002'   --查询学生'1600002'所选修课程的情况
                           AND NOT EXISTS    --判断学生x.studentNo没有选修课程b.courseNo
                                    ( SELECT * FROM Score
                                       WHERE 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 b
                     WHERE studentNo='1600002'   --查询学生'1600002'所选修课程的情况
                           AND NOT EXISTS    --判断学生x.studentNo没有选修课程b.courseNo
                                    ( SELECT * FROM Score
                                       WHERE 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 y
           WHERE 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) avgScore
             FROM Score
             GROUP BY courseNo ) x --这里必须起别名
     )
```
- (6) ORDER BY子句实现对查询结果的排序
  - 它是SQL查询的最后一个操作，必须放在最后；
  - 其中的<列名表达式>可以是列名，也可以是表达式；
  - 如果是表达式，则先计算表达式的值，然后排序输出；
  - 排序有升序ASC和降序DESC，默认为升序。
    (7) 集合运算
  - SELECT语句之间可以进行集合运算，包括并UNION、交INTERSECT、差EXCEPT运算。
SELECT语句的含义
- 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。
  如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数。
  如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。
  如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。
SELECT语句执行顺序
- SQL Select语句完整的执行顺序：
  1、from子句组装来自不同数据源的数据；
  2、where子句基于指定的条件对记录行进行筛选；
  3、group by子句将数据划分为多个分组；
  4、使用聚集函数进行计算；
  5、使用having子句筛选分组；
  6、计算所有的表达式；
  7、使用order by对结果集进行排序。
  8、select 集合输出。