四、关系数据库标准语言SQL_1

主要内容

• 4.1 SQL概述
  • SQL简介
• 4.2 SOL的系统结构
• 4.3 SQL数据定义
  • SQL的数据定义
• 4.5 SQL数据查询
• 4.6 SQL数据更新
• 4.7 SQL中的视图
• 4.8 SQL的数据控制
• 4.9 嵌入式SQL
• 小结

4.1 SQL概述

主要内容

• SQL简介
• SQL的特点
• SQL的系统结构

SQL简介

• SQL（Structured Query Language）

  结构化查询语言

  • 是一种介于关系代数与关系演算之间的语言；
  • 是一个通用的、功能极强的关系数据库语言；
  • 目前已成为关系数据库的标准语言，大多数关系数据库产品都支持SQL语言；
  • 其前身是1974年Boyce和Chamberlin提出的，并在System R上实现的SQURARE语言。
  • 不区分大小写

• SQL语言的版本包括：

  • SQL-86
  • SQL-89
  • SQL-92（SQL2）
  • SQL:1999（SQL3）增加了面向对象的概念，超1000页
  • SQL:2003（SQL4）
  • SQL:2008
  • SQL:2011

  2016年12月14日发布了SQL:2016

  2023年6月1日发布了SQL:2023

• SQL语言按功能划分为四部分：

  • 数据定义：定义表、视图和索引
  • 数据操纵：查询、插入、删除和修改
  • 数据控制：访问权限管理、事务管理
  • 嵌入式SQL：SQL语句嵌入到程序语言中使用

SQL的特点

• 综合统一

  • 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的于一体，可以完成数据库生命周期中的全部活动。
  • 关系模型中实体和实体间的联系都用关系来表示，使得操作符单一，每种操作只使用一个操作符。

• 高度非过程化

  • 使用SQL语言，只需要提出“做什么”，而无需指明“怎么做”，无需了解存取路径，提高了数据的独立性。

• 面向集合的操作方式

  • SQL语言采用集合操作方式，查询、插入、删除、修改操作的对象都是集合。

• 以同一种语法结构提供两种使用方式

  • 作为独立的语言（交互式SQL）

    提供联机交互工具，在终端键盘上直接键入SQL命令对数据库进行操作，由DBMS来进行解释；

  • 作为嵌入式语言（嵌入式SQL）

    SQL语句能嵌入到高级语言程序中，使应用程序具备利用SQL访问数据库的能力。

  • 不同方式下，SQL的语法结构基本上是一致的，提供了极大的灵活性和方便性

• 语言简洁，易学易用

  • 完成核心功能只用了9个动词。

  

4.2 SQL的系统结构

• SQL语言支持数据库的三级模式结构

  • 在SQL中，关系模式称为基本表（Base Table），基本表的集合形成数据库模式，对应三级模式结构的模式
  • 基本表在物理上和存储文件相对应，所有存储文件的集合为物理数据库
  • 外模式由**视图（View）**组成

  

• SQL的表分为两种：基本表和视图

• 基本表（Base Table，Table）

  • 独立存在的表
  • 一个关系模式对应一个基本表

• 视图（View）

  • 是从一个或多个基本表中导出的表，仅有逻辑上的定义，不实际存储数据，是一种虚表。
  • 视图的定义存储在数据字典中，在使用的时候，根据定义从基本表中导出数据供用户使用。
  • 视图可以像基本表一样进行查询和某些更新操作。

4.3 SQL的数据定义

主要内容

• SQL的数据定义功能
• SQL模式的定义
• SQL模式的删除
• 定义基本表
• 修改基本表
• 删除基本表
• 建立索引
• 删除索引

SQL的数据定义功能

• 定义表、定义视图和定义索引
• 在SQL2中还增加了对SQL数据库模式的定义。

SQL模式

• 现代关系数据库管理系统提供了一个层次化的数据库对象命名机制

  • 一个关系数据库管理系统的实例（Instance）中可以建立多个数据库

  • 一个数据库中可以建立多个模式（Schema）

  • 一个模式下通常包括多个表、视图和索引等数据库对象

    

    在 MySQL 中，“数据库” ≈ “模式”（Schema）。也就是说在 MySQL中，“数据库”和“模式”（Schema）是同一个概念。

SQL模式的定义

• 一个SQL模式（SQL Schema）由模式名、权限标识符和模式中元素的描述符组成。

  • 权限标识符指明拥有该模式的用户或账号
  • 模式元素包含一个数据库应用的表、视图和索引等

• 属于同一应用的表、视图和索引等可以定义在同一模式中。

• 定义模式后，实际上定义了一个命名空间，可以进一步定义该模式包含的数据库对象，如表、视图和索引等。

• 在定义模式时可先给出模式名和权限标识符，以后再定义其中的元素，语法格式：

  CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
  

• 可以在创建模式的同时在模式定义中进一步创建基本表、视图、定义授权等

  CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
  [<表定义子句>|<视图定义子句>|<授权定义子句>];
  

• 示例：

  例：定义学生数据库模式 SST ，用户为 SDBA

  CREATE SCHEMA SST AUTHORIZATION SDBA;
  CREATE SCHEMA AUTHORIZATION WANG;
  

  没有指定 “模式名”，则默认为用户名 WANG

  • 上述的语法格式是标准 SQL 或 PostgreSQL的语法，用于：创建一个数据库模式（Schema），并指定该模式的所有者是某个用户。

    但是在 MySQL ，这种写法是不支持的。

    MySQL不适用 AUTHORIZATION 关键字，也不支持给 schema指定“拥有者”，因为：在 MySQL中，数据库（schema）和用户是完全分开的，靠授权来建立关系，而不是指定所有者

  下面介绍正确的MySQL语法：

  -- 创建用户 SDBA，密码为 123456
  CREATE USER 'SDBA'@'localhost' IDENTIFIED BY '123456';-- 创建数据库 SST
  CREATE DATABASE SST;-- 授权 SDBA 可以操作 SST 库中的所有对象
  GRANT ALL PRIVILEGES ON SST.* TO 'SDBA'@'localhost';-- 刷新权限，确保生效
  FLUSH PRIVILEGES;
  

  例：创建模式时定义模式元素

  CREATE SCHEMA AUTHORIZATION ross
  CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT REFERENCES t2(c1))
  CREATE TABLE t2(c1 INT PRIMARY KEY,c2 INT REFERENCES t1(c1));
  

  这个语句说明，创建了数据库模式 ross，而且用户名为ross，同时该模式里面有两张表，t1 和 t2，t1的主键是c1，外键是 c2（c2参照t2的c1）。t1的主键是c1，c2是外键（参照t1的c1）。

SQL模式的删除

• 删除模式语句：

  DROP SCHEMA <模式名> [CASCADE|RESTRICT]
  

  • CASCADE（级联式）方式

    在删除模式的同时把该模式所属的基本表、视图和索引等元素全部一起删除。

  • RESTRICT（限制式）方式

    只有当模式中没有任何元素时，才能删除该模式，否则拒绝该删除操作。

关于模式的补充

• 不同的系统对于 Schema的定义和使用有所不同

• 在SQL Server2005之后的版本中，创建数据库时会包含一些默认的Schema：dbo，guest，sys，INFORMATIONI_SCHEMA，另外有一些角色Schema等

• 创建数据库对象（如TABLE），如果没有指定Schema，则:

  1. 创建在当前登录用户默认的Schema上；
  2. 若没有默认的Schema，则创建在dbo Schema上；
  3. 如果指定了Schema，则按照指定的做。

• Schema 的查找顺序

  • 假设有个登录用户Sue，默认Schema为Sue，现需查找使用某个表mytable，

    SELECT * FROM mytable;
    

    那么系统查找该表的顺序是：

    1. sys.mytabl(sys Schema)
    2. Sue.mytable(Default Schema)
    3. dbo.mytable(dbo Schema)

• 系统默认的Schema不能删除

定义基本表

CREATE TABLE <表明>
(<列明> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件> ]]……[,<表级完整性约束条件>]);

• <表名>：所要定义的基本表的名字
• <列名>：组成该表的各个属性（列）
• <列级完整性约束条件>：涉及相应属性列的完整性约束条件
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

【例】建立学生表Student，表中属性有：学号Sno，姓名Sname，年龄Sage，性别Ssex，学生所在系Sdept

CREATE TABLE Student(Sno CHAR(6) NOT NULL UNIQUE,Sname CHAR(8),Sage INT,Ssex CHAR(2),Sdept CHAR(12),CONSTRAINT C1 CHECK (Ssex IN ('男','女')),CONSTRAINT S_PK PRIMARY KEY(Sno)
);

• CONSTRAINT子句定义列级或表级约束，格式为

  CONSTRAINT <约束名> <约束>

• SQL常用的数据类型

  SMALLINT 短整数

  INTEGER或INT 长整数

  REAL 浮点数

  DOUOBLE PRECITION 双精度浮点数

  FLOAT(n) 浮点数，精度为n位

  NUMBER(P[,q]) 定点数，共p位，其中小数点后有q位

  CHAR(n) 长度为n的定长字符串

  VARCHAR(n) 最大长度为n的变长字符串

  BIT(n) 长度为n的二进制位串

  BIT VARCHAR(n) 最大长度为n的二进制位串

  DATE 日期型，格式为YYYY-MM-DD

  TIME 时间型，格式位HH：MM：SS

  TIMESTAMP 日期加时间

• SQL2中增加了定义域的语句，可以用域名代替指定列的数据类型。

• 如果有一个或多个表的属性的域是相同的，通过对域的修改可以很容易地改变属性的数据类型。

• 域定义语句的格式为：

  CREATE DOMAIN <域名> <数据类ixing>
  

  • 【例】

    CREATE DOMAIN Sdept_TYPE CHAR(12);
    

    域Sdept_TYPE创建后，定义学生表时，对列Sdept的类型定义可以用域名代替：Sdept Sdept_TYPE

  • 【例】

    CREATE TABLE Course(Cno CHAR(6) NOT NULL,Cname CHAR(20),Ccredit INT,CONSTRAINT C_PK PRIMARY KEY (Cno));
    CREATE TABLE SC(Sno CHAR(6) NOT NULL,Cno CHAR(6) NOT NULL,Grade INT CHECK (Grade BETWEEN 0 AND 100),CONSTRAINT SC_PK PRIMARY KEY (Sno,Cno),CONSTRAINT SC_FK1 FOREIGN KEY (Sno) REFERENCES Student(Sno),CONSTRAINT SC_FK2 FOREIGN KEY (Cno) REFERENCES Course(Cno));
    

    也可以如下写

    CREATE TABLE Course(Cno CHAR(6) NOT NULL,Cname CHAR(20),Ccredit INT,PRIMARY KEY (Cno));
    CREATE TABLE SC(Sno CHAR(6) NOT NULL,Cno CHAR(6) NOT NULL,Grade INT CHECK (Grade BETWEEN 0 AND 100)PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno));
    

• 在定义基本表时，表所属的数据库模式一般被隐式指定，也可以显示地在定义表时指定表所属的数据库模式名。

  • 如下语句在定义学生表时，同时指出学生表所在的模式为学生数据库模式SST。

    CREATE TABLE SST.Student(Sno CHAR(6) NOT NULL UNIQUE,……);
    

• 在创建模式语句中同时创建表

  在数据库中，我们可以在创建数据库模式（Schema）的时候，顺便创建这个模式下的表（Table），而不是先建模式、再建表。

  CREATE TABLE SST.Student(Sno CHAR(6) NOT NULL UNIQUE,……);
  

• 常用完整性约束

  • 主键约束：PRIMARY KEY
  • 唯一性约束：UNIQUE
  • 非空值约束：NOT NULL
  • 参照完整性约束：就是上述例子里的一些外键

修改基本表

ALTER TABLE <表名>[ADD <列名> <数据类型> [<完整性约束>]][DROP <列名> [CASCADE|RESTRICT]][ALTER <列名> <数据类型>];

• ADD子句用于增加新列，包括列名、数据类型和列级完整性约束
• DROP子句用于删除指定的列名，
  • CASCADE表示删除列时自动删除引用该列的视图和约束
  • RESTRICT表示没有视图和约束引用时才能删除该列，否则拒绝删除操作
  • 但在MYSQL一般直接删除即可，因为MySQL 不支持 在 DROP COLUMN 后使用 CASCADE 或 RESTRICT。如果删除列有依赖（如被视图、索引、外键引用），MySQL 会自动报错，提示不能删，需要你手动先删除依赖对象。
• ALTER子句用于修改列的定义，如修改列的数据类型或修改列的宽度等

【例】在学生表Student增加一列，列名为班级。

ALTER TABLE StudentADD Class CHAR(8);

• 不论基本表中原来是否已有数据，新增加的列一律为空值；不能在其上指定NOT NULL（除非配合DEFAULT有默认值）

【例】修改学生表Student中姓名列的长度为20。

ALTER TABLE Student ALTER Sname CHAR(20);

或者（ 下面是MySQL 的正确写法。上面是标准SQL）

ALTER TABLE Student MODIFY Sname CHAR(20);

• 修改原有的列定义有可能会破坏已有数据

ALTER 语句用于修改基本表的结构，例如添加、删除或修改表中的列（如：ALTER TABLE table_name ADD COLUMN column_name datatype）。

DELETE （后续会提到）语句用于删除基本表中的行（如：DELETE FROM table_name WHERE condition）。

因此：ALTER 修改的是表的定义，影响的是表的结构。DELETE 删除的是表中的数据，影响的是表的内容。

删除基本表

DROP TABLE <表名> [RESTRICT|CASCADE];

• 若选择RESTRICT，则删除的基本表不能被其他表的约束所引用（如有CHECK,FOREIGN KEY等约束），不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不嫩被删除。
• 若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图等都将被一起删除。
• 一般在缺省的情况下默认为RESTRICT，与具体实现有关。
• 在MySQL中则直接DROP TABEL <表名>，类似DROP TABLE <表名> RESTRICT。如果有依赖，那么MySQL会自动报错。

建立索引

• 索引是一种数据结构

  • 索引技术是数据库管理系统的核心问题之一；

  • 在表上建立索引，可以提供不同的存取路径，可以加快查询速度。

  • 可根据需要在一个表上建立一个或多个索引；

  • DBA或表的创建者有权建立和删除索引；

  • 索引的更新和维护是由DBMS自动完成的；

  • 系统在存取数据时会自动选择是否使用索引，或者是以合适的索引作为存取路径，用户不必也不能选择索引；

  • 有些DBMS能自动在某些特殊属性列上建立索引

    • PRIMARY KEY
    • UNIQUE

    下面给出例子，在该表中

    1. 主键 = 聚焦索引（Clusterd Index）

       • Col2 是主键，所以它也是这张表的聚集索引
       • 聚集索引结构为 B+树
       • 叶子节点中存储的是整行数据（整行！）

       所以从主键查数据，不需要“回表”，因为数据已经就在叶子节点里了。

    2. 数据与聚集索引的存储方式

       • 表数据与聚集索引是同一份结构（不是分开存，是在同一磁盘同一片区域）
       • 聚集索引的 B+树 本质上就是表的数据存储顺序
       • 图中 B+ 树的叶子节点：5 → 22 → 23 → 34 → 77 → 89 → 91 是实际数据的主键顺序

    3. 二级索引（辅助索引）

       • 你又创建了一个二级索引，比如对 Col1 建立唯一索引或普通索引
       • 二级索引也是 B+树结构，但它的叶子节点不存整行数据
       • 二级索引的叶子节点中只存两样东西：
         1. 被索引的列值（比如 Col1 的值）
         2. 对应记录的主键值（Col2）

    4. 回表

       比如执行语句：

       SELECT * FROM table WHERE Col1 = 5;
       

       步骤如下：

       1. 去 Col1 的二级索引 B+树中查找值为 5
       2. 找到了，对应的主键值是 22
       3. 再去 主键（Col2）的聚集索引 B+树中找 22
       4. 找到整行数据：(Col1=5, Col2=22)

       这就是回表机制：二级索引中找到主键，再通过主键找数据。

    

    • 左边蓝色表格：
      • 数据库中的一张表，包含两列Col1和Col2。
      • 第一列是地址（比如0x07），第二列是实际数据指（比如34）
    • 右边橙色结点的树形结构：
      • 表示某列（如Col2）上的索引结构
      • 树的结构类似于B+树或二叉查找树；
      • 结点中的数值对应Col2的内容（如34，23，5，23……）；
      • 每个叶子节点通过箭头指向表中对应的元组的行位置
    • 假设我们要在Col2上查找值为77的记录：
      1. 数据库先走索引树，从根节点开始查找：
         • $找到34 \rightarrow 小于 \rightarrow 到89 \rightarrow 大于 \rightarrow 到77 $
      2. 访问叶子节点 77，箭头指向表中地址 0x56 这一行
      3. 最终从表中读取出完整行。

    这就是索引带来的效率提升：避免从头到尾扫描整张表。（可能这个示例没体现出来）

  • 格式

    CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名>[<次序>][,<列名>[<次序>]]……)
    

    • <表名>指定要建立索引的基本表名字
    • 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
    • <次序>指定索引值的排列次序，升序ASC，降序DESC。缺省值（默认）：ASC（也就是说索引里面是按照值升序或降序）
    • UNIQUE表明此索引的每一个索引值只对应唯一的数据记录（强制某一列（或列组合）的值不能重复，保证数据唯一性，可以有NULL）
    • CLUSTER表示要建立的索引是聚焦索引（Cluster Index）

• 【例】在学生表Student的学号列上按升序建立惟一索引。

  CREATE UNIQUE INDEX S_SNO ON Student(Sno);
  

• 【例】在表Student上按班级降序、年龄升序建立索引。

  CREATE INDEX SCLASS_AGE ON Student(Class DESC,Sage ASC);
  

• 唯一值索引（UNIQUE）

  • 对于已含重复值的属性列不能建立UNIQUE索引

  • 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新纪录在该列上是否取了重复值。

    这相当于增加了一个UNIQUE约束。

  • 【例】

    CREATE UNIQUE INDEX SnoIdx ON Student(Sno);
    CREATE UNIQUE INDEX CnoIdx ON Course(Cno);
    CREATE UNIQUE INDEX SCNO ON SC(Sno ASC,Cno DESC);
    

• 聚集索引（Cluster Index）

  • 索引次序与基本表中元组的物理次序一致的索引；建立聚集索引后，基表中数据也需要按指定的聚集属性值的升序或降序存放，即：聚集索引的索引项顺序与表中记录的物理顺序一致，二者在一块存储区域

  • 例：

    CREATE CLUSTER INDEX Stusname ON Student(Sname ASC);
    

    在Student的Sname（姓名）列上建立了一个聚焦索引，而且Student表中的记录将按照Sname值的升序存放。

    MySQL 中不支持手动创建“聚集索引”！

    在 MySQL 的 InnoDB 存储引擎中：

    • 聚集索引是自动创建的，不允许手动指定为 CLUSTER INDEX
    • 聚集索引 ≈ 主键（PRIMARY KEY）
      • 若表有主键，聚集索引就是主键；
      • 若无主键，则找一个唯一非空索引；
      • 都没有，InnoDB 自动生成一个隐藏主键（row_id）

删除索引

• DROP INDEX <索引名> ON <表名>

  • 删除索引时，系统会从数据字典中删除有关该索引的描述。

  • 例：删除学生表上建立的S_SNO索引

    DROP INDEX S_SNO ON Student;
    

索引的选择

• 索引为性能所带来的好处是有代价的。
  • 对某个属性建立索引，能极大提高对该属性上的值的检索效率；在使用该属性进行连接操作时，还可以加快连接速度。
  • 带索引的表在数据库中会占据更多的空间。
  • 索引的维护需要一些额外的计算代价。对数据进行插入、删除和更新操作时，所花费的时间会更长。
• 应根据数据的性质和基于表的查询性质，来决定是否创建索引，应确保对性能的提高程度大于在存储空间和处理资源方面的额外开销。

索引是关系数据库的内部实现技术，属于内模式的范畴。不做多的描述。

4.5 SQL数据查询

主要内容

• 1.查询语句的一般格式
• 2.单表查询
• 3.连接查询
• 4.嵌套查询
• 5.集合查询
• 6.基于派生表的查询

1.查询语句的一般格式

SELECT [ALL|DISTINCT] <目标表达式> [,<目标列表达式>]……FROM <表明或视图名>[,<表名或视图名>]……[WHERE <条件式1>][GROUP BY <列名>[HAVING <条件表达式2>]][ORDER BY <列名>[ASC|DESC]]

• **SELECT子句：**指定要显示的属性列
• **FROM子句：**指定查询对象（基本表或视图）
• **WHERE子句：**指定查询条件
• **GROUP BY子句：**对查询结果按指定列的值分组，按属性列值相等的元组一个组。通常会在每组中使用聚集函数。
• **HAVING短语：**筛选出满足条件的元组。
• **ORDER BY子句：**对查询结果表按指定列值的升序或降序排序。
• DISTINCT表示去掉重复元组，ALL则容许重复数组。

查询语句的基本结构

SELECT A1,A2,……,An
FROM R1,R2,……,Rm
WHERE P

等价于

${\Pi }_{A1,A2,\dots \dots ,An}({\sigma }_p(R_1\times R_2\times \dots \dots \times R_m))$

示例数据库：学生——课程数据库（下面的举例都用到该表）

• 学生表：

  $Student(Snp,Sname,Sage,Ssex,Sdept)$

  Sno	Sname	Sage	Ssex	Sdept
  200413	刘敏	21	女	数学
  200701	刘明亮	19	男	计算机系
  200702	李和平	20	男	外语
  200703	王茵	21	女	计算机系
  200704	张小芳	21	女	数学
  200705	李斌	21	男	计算机系

• 课程表：

  $Course(Cno,Cname,Credit,Cpno)$

  Cno	Cname	Ccredit
  1	C语言	4
  2	英语	3
  3	数据库	4
  4	数学	4
  5	数据结构	4
  6	数据处理	2
  7	PASCAL语言	4

• 学生选课表：

  $SC(Sno,Cno,Grade)$

  Sno	Cno	Grade
  200701	1	98
  200701	2	92
  200701	4	88
  200702	1	81
  200703	1	89
  200703	2	90
  200705	6	87

2.单表查询

• 查询仅涉及一个表，是一种最简单的查询操作
  • 选择表中的若干列
  • 选择表中的若干元组
  • 对查询结果排序
  • 使用聚焦函数
  • 对查询结果分组

单表查询示例

• 【例】查询学生的学号和姓名。

  SELECT Sno,Sname FROM Student;
  

• 【例】查询计算机系学生的学号和姓名。

  SELECT Sno,Sname FROM StudentWHERE Sdept = '计算机系';
  

• 【例】查询年龄在18岁到25岁之间的学生信息

  SELECT * FROM Student WHERE Sage BETWEEN 18 AND 25;
  

• 【例】查全体学生的姓名及其出生年份

  SELECT Sname,2025 - Sage FROM Student;
  

  

• 【例】查询已经选修了课程的学生学号，并按学号升序排列。

  SELECT DISTINCT SnoFROM SCORDER BY Sno ASC;
  

• 【例】查询每门课的选修人数。

  SELECT Cno,COUNT(*)FROM SCGROUP BY Cno;
  

  

  1. COUNT(*)是什么？

     • 作用：COUNT(*)是一种 聚合函数，用于统计某个分组中“行的数量”。COUNT(*) 的行为要结合 GROUP BY 一起看。
     • 在该语句中：它表示“每门课程(Cno)被多少学生选修了”。因为GROUP BY Cno;，所以分组的是Cno，所以统计的是每组Cno有多个学生。

  2. GROUP BY是什么？

     • 作用：GROUP BY 用于将查询结果按某个字段进行分组，每组只输出一行。
     • 在该语句中：按照Cno来分组，也就是说：
       • 同一个课程编号的所有成绩记录（即多个学生选这门课）被归到一组。
       • 每个Cno组统计一次 COUNT(*)。

  3. 什么是聚合函数？

     聚合函数是一类对一组值进行计算，并返回单个结果值的函数，常用于统计分析。

• 【例】查询平均成绩在85分以上的学生的学号和平均成绩

  SELECT Sno,AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade) > 85;
  

  • HAVING子句是用于对分组结果进行筛选的，作用类似于 WHERE，但它是作用在 GROUP BY 之后的结果上。

  • HAVING和WHERE的区别：

    关键字	作用对象	是否能用聚合函数
    WHERE	原始表的行记录	不能
    HAVING	GROUP BY后的分组	可以

• 【例】查询成绩在75~85分之间的学生的学号和成绩

  SELECT Sno,Grade FROM SCWHERE Grade >=75 AND Grade <=85;
  

• 【例】查询年龄为19岁的所有姓李的学生姓名

  SELECT Sname FROM StudentWHERE Sname LIKE '李%' AND Sage = 20;
  

  1. LIKE是什么？

     • 功能：LIKE是 SQL 中用于模糊匹配字符串的操作符。

       它常用于 WHERE 子句，搭配 通配符 来进行部分匹配查询。

  2. 统配符说明

     通配符	含义	示例
     %	表示任意个（0个或多个）字符	%李表示以“李”开头。
     _	表示任意单个字符	李_表示“李”后跟一个字

  3. '李%'的含义

     • 李%表示：以“李”开头的字符串

• 【例】查询缺考学生的学号和课程号

  SELECT Sno,Cno FROM SCWHERE Grade = NULL;
  

查询经过计算的值

• SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式

  • 算术表达式
  • 字符串常量
  • 函数
  • 列别名等

• 例：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名。

  SELECT Sname,'Year of Birth:',2025 - Sage,LOWER(Sdept) FROM Student;
  

  

  我这里所在系名称是中文，所以体现不出LOWER()函数“用小写字母表示所在系名”

使用列别名改变查询结果的列标题

SELECT Sname AS NAME,'Year of Birth:' BIRTH,2000 - Sage BIRTYEAR,LOWER(Sdept) DEPARTMENTFROM Student;

查询结果：

1. Sname AS NAME
   • 将学生姓名列Sname重名为NAME(即：列标题改为 NAME,可以省略AS)
   • 该列输出的是学生的姓名
2. Year of Birth:
   • 这是一个 常量字符串，不是表里的字段
   • 这个常量值有个别名叫 BIRTH
   • 所以这一列每一行都会显示 Year of Birth:，列名是BIRTH
3. 后面两列同理。

使用DISTINCT短语消除取值重复的行

假设SC表中有下列数据

进行如下SQL查询语句

SELECT DISTINCT Sno FROM SC;

结果：

进行如下SQL查询语句

SELECT Sno FROM SC;

或

SELECT ALL Sno FROM SC;

结果：

• 注意DISTINCT短语的作用范围是所有目标列

  错误写法

  SELECT DISTINCT Cno,DISTINCT Grade FROM SC;
  

  正确写法

  SELECT DISTINCT Cno,Grade FROM SC;
  

WHERE子句常用的查询条件

关键字	说明
比较表达式	<列名1> 比较算符 <列名2（或常量）> 比较运算符：=、>、>=、<、<=、<>(或!=)
逻辑表达式	<条件表达式1> 逻辑算符 <条件表达式2> 逻辑算符：AND、OR、NOT
BETWEEN	<列名1> （NOT）BETWEEN <常量1或列名2> AND <常量2或列名3>
IN	<列名1> （NOT）IN（常量、表列 或 SELECT语句）
LIKE	<列名> （NOT）LIKE ‘匹配字符串’ 匹配符：“_”表示匹配一个字符，“%”表示匹配任意字符串
NULL	<列名> IS （NOT）NULL
EXISTS	（NOT）EXISTS（SELECT语句）

确定范围

• 使用谓词 BETWEEN……AND…… 或 NOT BETWEEN……AND……

• 【例】查询年龄在20~23岁之间的学生的姓名、系别和年龄。

  SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
  

• 【例】查询年龄不在20~23岁之间的学生姓名、系别和年龄

  SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
  

确定集合

• 使用谓词 IN <值表> ，NOT IN <值表>

  • <值表>：用逗号分隔的一组取值

• 【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

  SELECT Sname,Ssex FROM Student WHERE Sdept IN ('IS','MA','CS');
  

  或

  SELECT Sname,Ssex FROM Student WHERE Sdept = 'IS' OR Sdept = 'MA' OR Sdept = 'CS';
  

字符串匹配

• [NOT] LIKE '匹配串' [EXCAPE '<换码字符>']

• <匹配串>：指定匹配模板

  • 匹配模板：固定字符串或含通配符的字符串

    • 当匹配模板为固定字符串时，可用=运算符取代LIKE谓词，用!=或<>运算符取代NOT LIKE。

  • 通配符

    • %代表任意长度（长度可以为0）的字符串

    • _（下划线）代表任意单个字符

    • 例：

      a%b表示以a开头，以b结尾的任意长度的字符串；

      a_b表示以a开头，以b结尾的长度为3的任意字符串。

  • ESCAPE短语：

    • 当用户要查询的字符串本身就含有%或_时，要使用ESCAPE '<转码字符>'短语对通配符进行转义。

字符串匹配示例

• 匹配模板为固定字符串

  SELECT * FROM Student WHERE Sno LIKE '95001';
  

  等价于

  SELECT * FROM Student WHERE Sno = '95001';
  

• 匹配模板为含通配符的字符串

  【例】查询所有姓刘学生的姓名、学号和性别。

  SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';
  

  【例】查询姓“欧阳”且全名为三个汉字的学生姓名

  SELECT Sname FROM Student WHERE Sname LIKE '欧阳_';
  

• 使用换码字符将通配符转义为普通字符

  【例】查询DB_Design课程的课程号和学分。

  SELECT Cno,Credit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\'; 
  

  【例】查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

  SELECT * FROM Course WHERE Cname LIKE 'DB\__i__' ESCAPE '\';
  

涉及空值的查询

• 使用谓词 IS NULL 或 IS NOT NULL

• IS NULL不能使用 = NULL代替

  【例】某些学生选秀课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生和相应的课程号。

  SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
  

  【例】查所有有成绩的学生学号和课程号。

  SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
  

空值

• SQL允许属性有一个特殊值NULL称作空值。

  • 未知值：有值但是不知道是什么，例如未知生日
  • 不适用的值：例如配偶的名字
  • 保留的值：无权知道的值，例如未公布的电话号码

• 空值的运算

  • 空值不同于空白或零值。没有两个相等的空值。空值和任何值进行算术运算，结果仍为空值。
    • 执行计算时消除空值很重要，因为包含空值列的某些计算（如平均值）会不准确。
  • 当使用逻辑运算符和比较运算符，有可能返回结果UNKNOWN（与TRUE和FALSE相同的布尔值）

  

空串

• 空串是指长度为零的字符串
  • 当 m 为 0 或 负数时，RIGHT('123',m)返回空字符串RIRIM('')返回空字符串

多重条件查询

• 用逻辑运算符AND和OR来连接多个查询条件

  • AND的优先级高于OR
  • 可以用括号改变优先级

• 可用来实现多种其他谓词

  • [NOT] IN
  • [NOT] BETWEEN …… AND ……

  【例】查询计算机系年龄在20岁以下的学生姓名。

  SELECT Sname FROM Student WHERE Sdept = '计算机系' AND Sage < 20;
  

对查询结果排序

• 使用ORDER BY子句

  • 可以按一个或多个属性列排序
  • 升序：ASC；降序：DESC；缺省值（默认）为升序

• 空值将作为最大值排序

  • ASC：排序列为空值的元组最后显示
  • DESC：排序列为空值的元组最后显示

• 【例】查询选修了1号课程的学生的学号及其成绩，查询结果按分数降序排列。

  SELECT Sno,Grade FROM SC WHERE Cno = '1' ORDER BY Grade DESC;
  

  

使用聚集函数（聚合函数）

• SQL提供了许多聚合函数，用来实现统计查询

  • 计数

    COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<列名>)

  • 计算总和 SUM([DISTINCT|ALL]<列名>)

  • 计算平均值 AVG([DISTINCT|ALL]<列名>)

  • 求最大值 MAX([DISTINCT|ALL]<列名>)

  • 求最小值 MIN([DISTINCT|ALL]<列名>)

• 选项DISTINCT表示在计算时要取消指定列中的重复值；ALL表示不取消重复值；默认为ALL。

  【例】查询学生总人数。

  SELECT COUNT(*) FROM Student;
  

  【例】查询选修了课程的学生人数

  SELECT COUNT(*) FROM SC;
  

  【例】计算1号课程的学生平均成绩。

  SELECT AVG(Grade) FROM SC WHERE Cno = '1';
  

  【例】查询1号课程的最高分数。

  SELECT MAX(Grade) FROM SC WHERE Cno = '1';
  

对查询结果分组

• 使用GROUP BY子句分组

• 细化聚合函数的作用对象

  • 未对查询结果分组时（没有使用GROUP BY），聚集函数将作用于整个查询结果。
  • 对查询结果分组后（使用GROUP BY），聚集函数将分别作用于每个组（会和GROUP BY结合起来）。

• 分组方法

  • 按指定的一列或多列值分组，值相等的为一组

• 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚合函数。

• GROUP BY子句的作用对象是查询的中间结果表

• 使用HAVING短语筛选最终输出结果

  • 只有满足HAVING短语指定条件的组才可以输出

  【例】求各个课程号及相应的选课人数。

  SELECT Cno,COUNT(*)	-- COUNT(Sno)FROM SC GROUP BY Cno;
  

  【例】查询选修了3门以上课程的学生学号

  SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;
  

  【例】查询平均成绩大于等于90分的学生学号和平均成绩

  错误示范：

  SELECT Sno,AVG(Grade) FROM SC WHERE AVG(Grade) >= 90 GROUP BY Sno;
  

  正确示范：

  SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90;
  

  说明：

  1. 错误点：WHERE是在 分组（GROUP BY）之前执行 的，此时还没进行GROUP BY分组，也就是没有办法计算每个学生的AVG(Grade)。AVG(Grade)是在GROUP BY之后才出现的东西，WHERE根本还看不到它。

  2. 用HAVING正确处理：因为我们想筛选的是“每个学生的平均成绩”，这是聚合后的结果，所以我们要在GROUP BY后使用HAVING来处理。

  3. 用法对比	WHERE	HAVING
     执行时机	在GROUP BY之前	在GROUP BY和聚合函数之后
     能否使用聚合函数	不能使用聚合函数（AVG，SUM等）	可以使用聚合函数
     用于筛选	原始记录	聚合后的分组

  4. 聚合之后用 HAVING，聚合之前用 WHERE。

  【例】查询有3门以上及3门课程是90分以上及90分的学生的学号及（90分以上的）课程数

  SELECT Sno,COUNT(*) '90及90分以上的课程数' FROM SC WHERE Grade >= 90GROUP BY SnoHAVING COUNT(*) >= 3;
  

• HAVING短语于WHERE子句的区别

  • 作用对象不同
    • WHERE子句作用于基表或视图的原始记录，从中选择满足条件的元组（tuple）。
    • HAVING短语作用于组，和GROUP BY搭配，从中选择满足条件的组（group）。
  • WHERE子句中不能使用聚集函数；而HAVING短语中可以使用聚集函数。

• 分组查询补充示例

  假设有下面的表 orderhist

  

  执行语句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistWHERE orderhistGROUP BY productid;
  

  结果：

  执行语句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistWHERE productid = 2GROUP BY productid;
  

  结果：

  执行语句

  SELECT productid,SUM(quantity) AS total_quantityFROM orderhistGROUP BY productidHAVING SUM(quantity)>=30;
  

  结果：

3.连接查询

• 同时涉及两个或两个以上表的查询称为连接查询

  • 用来连接两个表的条件称为连接条件或连接谓词
  • 连接谓词中的列名称为连接字段
  • 连接条件中各连接字段的类型必须是可比的，但不必是相同的。

• 连接条件的一般格式

  • [<表名1>.]<列名> <比较运算符> [<表名2>.]<列名2>

    其中比较运算符为：=、>、<、>=、<=、!=

• SQL中连接查询的主要类型

  • 广义笛卡尔积
  • 等值（含自然连接）
  • 非等值连接查询
  • 自身连接查询
  • 外连接查询
  • 复合条件连接查询

广义笛卡尔积

• 不带连接谓词（即没有WHERE子句）。

• 广义笛卡尔积是两表元组的交叉乘积，其连接的结果会产生没有意义的元组， 实际上很少使用。

  【例】

  SELECT Student.*,SC.* FROM Stuodent,SC;
  

等值连接

• 连接运算符=的连接操作

  • [<表名1>.]<列名1> = [<表名2>.]<列名2>
  • 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以省略表名。

  【例】

  假设有如下表

  

  执行语句

  SELECT Student.*,SC.*FROM Student,SCWHERE Student.Sno = SC.Sno;
  

  结果：

  

自然连接

• 等值连接的一种特殊情况，把目标列中重复属性去掉

  【例】假设有如下表

  

  执行语句

  SELECT * FROM Student NATURAL JOIN SC;
  

  结果：

  

非等值连接查询

• 连接运算符不是=的连接操作

  [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

• 比较运算符：>、<、>=、<=、!=

自身连接

• 一个表与其自己进行连接，称为表的自身连接

• 需要给表起别名以示区别

• 由于所有属性名都是同名属性，因此必须使用别名前缀

  【例】假设有如下表

  

  查询每一门课的间接先修课（即先修课的先修课）

  SELECT FIRTST.Cno,SECOND.CpnoFROM Course AS FIRST,Course AS SECONDWHERE FIRST.Cno = Course.Cno;
  

  

  结果：

  

内连接

• 典型的连接运算，使用像=或<>（!=）之类的比较运算符

• 只保留两个表中满足连接条件的元组（行），不满足条件的会被过滤掉，不出现在结果中。

• 内连接包括等值连接和自然连接

• 内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行

• 【例】假设有如下表

  

  执行语句

  SELECT buyer_name,,sales.buyer_id,qtyFROM buyers INNER JOIN salsON buyers.buyer_id = sals.buyer_id;
  

  结果：

  

外连接

• 外连接概念：外连接是相对于“内连接”而言的，它不仅包含两个表中满足连接条件的元组，还包括某一方（或两方）中不满足条件的元组，这些不匹配的部分会用 NULL 补齐。

• 外连接与内连接的区别

  • 内连接操作只输出满足连接条件的元组
  • 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

• 外连接分类

  • 左外连接**（LEFT OUTER JOIN）**
  • 右外连接**（RIGHT OUTER JOIN）**
  • 全外连接**（FULL OUTER JOIN）**
  • SQL2支持的外连接

  【例】有如下表，查询每个学生的选课情况，包括没有选课的学生

  

  执行语句

  SELECT Student.Sno,Student.Sname,Ssex,Ssage,Sdept,Cno,GradeFROM Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno)
  

  结果：
  

复合条件连接

• WHERE子句钟含多个连接条件时，称为复合条件连接。

  • 复合条件连接可以看作（普通）连接后得到的关系（表）又进行一次选择运算

  【例】查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

  SELECT Student.Sno,Sname FROM Student,SCWHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND Grade > 90;
  

多表连接

• 连接操作涉及到两个以上的表的连接

  【例】查询每个学生的学号、姓名、选修的课程名及成绩。

  SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
  

  结果：
  

4.嵌套查询

• 嵌套查询概述
• 嵌套查询分类与求解方法
• 引出子查询的谓词
  • 带有IN谓词的子查询
  • 带有比较运算符的子查询
  • 带有ANY或ALL谓词的子查询
  • 带有EXISTS谓词的子查询

嵌套查询概述

• 一个SELECT-FROM-WHERE语句称为一个查询块

• 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

• 【例】

  SELECT Sname		-- 这是外层查询也叫做父查询FROM StudentWHERE Sno IN(SELECT Sno 	-- 这是内层查询也叫做子查询FROM SC WHERE Cno = '2');			
  

• 嵌套查询的实现

  一般是从里到外，先进行子查询，再把其结果用于父查询作为条件

• 层层嵌套方式反映了 SQL语言的结构化

• 有些嵌套查询可以用连接运算代替

• 子查询的限制：不能使用ORDER BY子句

嵌套查询的分类与求解方法

• 不相关子查询

  • 概念：子查询的查询条件不依赖于父查询
  • 由里向外逐层处理。每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

• 相关子查询

  • 概念：子查询的查询条件依赖于父查询
  • 先去外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。

• 【例】找出每个学生所选修课程成绩超过该门课成平均成绩的课程号。

  SELECT Sno,Cno  -- 外层查询/父查询FROM SC x -- x是SC的别名WHERE Grade >= (SELECT AVG(Grade) FROM SC yWHERE y.Cno = x.Cno);
  

  说明：

  1. 该嵌套查询时相关子查询，因为子查询中使用了外层查询的x.Cno值，即子查询依赖于父查询中当前元组的课程编号。

  2. **外层查询：**从成绩表SC（别名为x）中选取每条记录的Sno（学生编号）和Cno（课程编号）。

  3. **内层子查询：**针对外层每条记录的x.Cno，计算该课程的平均成绩AVG(Grade)。

  4. **条件比较：**将当前记录的Grade与该课程的平均成绩作比较，如果 当前成绩 >= 平均成绩，则保留该记录。

  5. 这里提供一个示例以供理解，假设SC表如下

     Sno	Cno	Grade
     01	C1	90
     02	C1	70
     03	C1	80
     01	C2	85
     02	C2	88

     那么，平均成绩：

     • C1的平均成绩是(90+70+80)/3 = 80
     • C2 的平均成绩是 (85+88)/2 = 86.5

     保留的记录是：

     • C1中成绩 ≥ 80 的有：01,03
     • C2中成绩 ≥ 86.5 的有：02

     因此返回的结果表为：

     Sno	Cno
     01	C1
     03	C1
     02	C2

  6. 子查询中没有显示使用 GROUP BY Cno，那它是如何按课程号计算平均成绩的。

     实际上确实没有 GROUP BY，这就是相关子查询的妙用

     观察代码

     SELECT Sno, Cno
     FROM SC x
     WHERE Grade >= (SELECT AVG(Grade)FROM SC yWHERE y.Cno = x.Cno
     );

     虽然子查询中没有 GROUP BY，但子查询里有：

     WHERE y.Cno = x.Cno;
     

     这意味着：

     • 每当外层取到一条记录（例如x.Sno='01'且x.Cno = 'C1'），
     • 子查询就会被“重新执行一次”，
     • 这一次子查询只计算 SC表中课程号等于C1 的所有 Grade 的平均值。

     也就是说，不是全表平均，而是“按当前课程号”去算的平均。

     可以理解为下面这个过程：

     “我拿出SC表的第一行，发现是课程C1，那我现在就去SC表中找所有课程是C1的成绩，求平均，回来比较一下当前这行成绩是否 ≥ 这个平均值。”

     整个过程依赖外层的x.Cno，这就造成了“按Cno分类”的效果，虽然没有写GROUP BY，但逻辑上是“每个Cno分别计算一次”。

     如果真的写了 AVG(Grade) 而没有这个 WHERE y.Cno = x.Cno 呢？
     那结果就是计算整张SC表所有成绩的平均，不区分课程了，那就错了。

  7. 总结：子查询虽然没有 GROUP BY，但依靠 WHERE y.Cno = x.Cno 实现了“对每门课程分别求平均”的效果，是“相关子查询”自动按上下文变量进行筛选的结果。

带有IN谓词的子查询

【例】

查询与“刘晨”在同一个系学习的学生。

• 此查询要求可以分布来完成

  1. 确定“刘晨”所在系名

     SELECT Sdept FROM StudentWHERE Sname = '刘晨';
     

     结果：

     

  2. 查找所有在IS系学习的学生。

     SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept = 'IS';
     

     结果：

     

• 构造嵌套查询

  将第一步查询嵌入到第二步查询的条件中

  SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept IN(SELECT Sdept FROM StudetnWHERE Sname = '刘晨');
  

• 父查询和子查询中的表均可以使用别名

  SELECT Sno,Sname,Sdept FROM Student S1WHERE S1.Sdept IN (SELECT Sdept FROM Student S2WHERE S2.Sname = '刘晨');
  

• 用自身连接完成本查询要求

  SELECT S1.Sno,S1.Sname,S1.SdeptFROM Student.S1,Student S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨';
  

【例】查询选修了“C语言”课程的学生的学号和姓名

SELECT Sno,SnameFROM StudentWHERE Sno IN(SELECT SnoFROM SC WHERE Cno IN(SELECT Cno FROM CourseWHERE Cname = 'C语言'));

• 或者用连接查询

  SELECT Student.Sno,Student.SnameFROM Student,SC,CourseWHERE Student.Sno = SC.SnoAND SC.Cno = Course.CnoAND Course.Cname = 'C语言';
  

带有比较运算符的子查询

• 当能确切知道内层查询返回单值时，可用比较运算符（>、<、=、>=、<=、!=或<>）

• 【例】假设一个学生只能在一个系学习，并且必须属于一个系，则在左例可以用=代替IN。

  则下面两个SQL语句等价

  SELECT Sno,Sname,SdeptFROM Student S1WHERE S1.Sdept IN(SELECT Sdept S2FROM StudentWHERE S2.Sname = '刘晨');
  

  SELECT Sno,Sname,SdeptFROM Student S1WHERE S1.Sdept =(SELECT Sdept S2FROM StudentWHERE S2.Sname = '刘晨');
  

• 子查询一定要跟在比较符之后

• 错误的例子

  SELECT Sno,Sname,SdeptFROM StudentWHERE (SELECT SdeptFROM StudentWHERE Student = '刘晨') = Sdept;
  

带有ANY或ALL谓词的子查询

• 谓词语义

  • ANY 任意一个值
  • ALL 所有值

• 配合比较运算符使用

  > ANY 大于子查询结果中的某个值

  > ALL 大于子查询结果中的所有值

  < ANY 小于子查询结果中的某个值

  < ALL 小于子查询结果中的所有值

  >= ANY 大于等于子查询结果中的某个值

  >= ALL 大于等于子查询结果中的所有值

  <= ANY 小于等于子查询结果中的某个值

  <= ALL 小于等于子查询结果的所有值

  = ANY 等于子查询结果的某个值

  = ALL 等于子查询结果中的所有值（通常没有实际意义）

  !=(<>) ANY 不等于子查询结果中的某个值

  !=(<>) ALL 不等于子查询结果中的任何一个值

• ANY 和 ALL 谓词有时可以用**聚集函数（聚合函数）**来实现

  • 用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数。

  • ANY与ALL与聚合函数的对应关系如下：

    

【例】查询其他系中比CS系任意一个（某个）学生年龄小的学生姓名和年龄

• 用ANY谓词实现

  SELECT Sname,SageFROM StudentWHERE Sage < ANY(SELECT SageFROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

• 用聚集函数实现

  SELECT Sname,SageFROM StudentWHERE Sage < (SELECT MAX(Sage)FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

【例】查询其他系中比CS系所有学生年龄都小的学生姓名及年龄

• 用ALL谓词实现

  SELECT Sname,SageFROM StudetnWHERE Sage < ALL(SELECT Sage FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

• 用聚集函数实现

  SELECT Sname,SageFROM Student WHERE Sage <(SELECT MIN(Sage)FROM StudentWHERE Sdept = 'CS')AND Sdept != 'CS';
  

带有EXISTS谓词的子查询

1. EXISTS谓词
2. NOT EXISTS谓词
3. 不同形式的查询间的替换
4. 用EXISTS或NOT EXISTS实现全称量词
5. 用EXISTS或NOT EXISTS实现逻辑蕴含

1.EXISTS谓词（存在量词$\exists$）

• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”；
• 若内层查询结果非空，则返回TRUE；NOT EXISTS相反
• 若内层查询结果为空，则返回FALSE；NOT EXISTS相反
• 由EXISTS引出的子查询，其目标列表达式通常用*，因为带EXISTS的子查询只返回TRUE或FALSE，给出列名无实际意义。

2.NOT EXISTS谓词

【例】查询所有选修1号课程的学生姓名

• 思路分析：在Student中依次取每个元组的Sno值，用此值去检查SC关系；若SC中存在这样的元组，即其Sno值等于此Student.Sno值，且Cno = '1'，则取此Student.Sname送入结果关系

  用嵌套查询

  SELECT SnameFROM StudentWHERE EXISTS(SELECT *FROM SCWHERE Student.Sno = SC.SnoAND SC.Cno = '1');
  

  或用连接运算

  SELECT Sname FROM Student,SCWHERE Student.Sno = SC.Sno AND Cno = '1';
  

【例】查询没有选修1号课程的学生姓名

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM SCWHERE Student.Sno = SC.SnoAND Cno = '1');

思路分析：从Student表中一条一条地取出学生元组，也就是说，我们对每个学生执行一次NOT EXISTS条件判断。比如，先从Student表中取出Sno='1' ，然后去SC表中查找这个学生是否选修了课程1号，如果查到记录，说明他选了，那么返回值为FALSE，不输出该学生；如果没查到记录，说明他没选，那么返回值为TRUE，输出该学生。

3. 不同形式的查询间的替换

• 一些带有EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换。
• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
• 带有EXISTS谓词的相关子查询只关心内层查询是否有返回值，不需要查具体值，效率不低于相关子查询。

【例】查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换

SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname = '刘晨');

等价于

SELECT Sno,Sname,SdeptFROM Student S1WHERE EXISTS(SELECT *FROM Student S2WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨');

4. 用EXISTS/NOT EXISTS实现全称量词

• SQL语言中没有全称量词$\forall$（For all）

• 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

  $(\forall x)P\equiv \neg (\exists x(\neg P))$

  解释：“所有人都满意”=“不存在一个人不满意”

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

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM CourseWHERE Cno NOT EXISTS(SELECT * FROM SCWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno));

说明：

• 外层：Student WHERE NOT EXISTS(……)

  意思是：不存在某些课程……

• 内层：Course WHERE Cno NOT EXISTS

  意思是：该学生没有选这门课。

  所以合起来就是

  对某个学生来说，不存在一门课，他 没有选 ——
  换句话说：所有的课，他都选了！

• 思路分析：

  1. 先从Student表中挑选一行元组；

  2. 然后在SC中找出满足Student.Sno = SC.Sno条件的n行元组（表示该学生选了n门课）；

  3. 然后从Course里面每一行元组（即每一门课）：

     • 每次从Course表中取出一个Cno（课程编号）；
     • 然后在上述n行SC元组中查找是否存在满足SC.Cno = Course.Cno的记录：
       • 如果找到，说明该学生选了这门课，则最内层子查询返回FALSE，在Course（只是过程的一个临时表）中删掉 找到的Cno 对应的课程元组。
       • 如果没找到，说明该学生没选这门课，则最内层子查询返回TRUE，在Course不删掉 找到的Cno 对应的课程元组。

  4. 所有课程都遍历完后，我们观察Course表（只是过程的一个临时表）中被保留下来的记录：

     • 如果 存在一门课没被删掉（即，存在没选的课），那么外层 NOT EXISTS(...) 为 False → 该学生被排除
     • 如果 所有的课都被删掉了（即，该学生选了所有课程）那么外层 NOT EXISTS(...) 为 True → 该学生被选中。

  5. 然后再从Student表中挑选下一行元组，从2.继续开始

• 假设有如下三个表

  1. 学生表（Student）

     Sno	Sname
     S1	张三
     S2	李四
     S3	王五

  2. 课程表（Course）

     Cno	Cname
     C1	数据库
     C2	算法
     C3	操作系统

  3. 选课表（SC）

     Sno	Cno
     S1	C1
     S1	C2
     S1	C3
     S2	C1
     S2	C2
     S3	C1

  查询目标：找出那些把全部课程都选了的学生姓名。

  那么按照SQL语句解释

  • 学生S1（张三）

    1. 从Student表中取出S1对应的一行元组

    2. 在SC表中找到S1的选课记录：C1、C2、C3（共三条记录）

    3. 遍历Course表（临时）中的课程：

    课程	是否出现在 SC(S3) 中？	最内层 NOT EXISTS	是否从临时Course中删掉
    C1	是	False	删掉
    C2	是	False	删掉
    C3	是	False	删掉

    4. 所有课程都被删掉 $\to$ Course子查询为空 $\to$ 外层 NOT EXISTS(...) 为 True
    5. 张三被选中，加入最终结果

  • 学生 S2（李四）

    1. 从 Student 表中取出 S2

    2. 在 SC 表中找到 S2 的选课记录：C1、C2

    3. 遍历 Course 表（临时）中的课程：

       课程	是否出现在 SC(S2) 中？	最内层 NOT EXISTS	是否从临时Course中删掉
       C1	是	False	删掉
       C2	是	False	删掉
       C3	否	True	保留

    4. 还有 C3 没删掉 → 有课没选 → 外层 NOT EXISTS(...) 为 False

    5. 李四被排除

  • 学生 S3（王五）

    1. 从 Student 表中取出 S3

    2. 在 SC 表中找到 S3 的选课记录：C1

    3. 遍历 Course 表中的课程：

       课程	是否出现在 SC(S3) 中？	最内层 NOT EXISTS	是否从临时Course中删掉
       C1	是	False	删掉
       C2	否	True	保留
       C3	否	True	保留

    4. 还剩 C2、C3 → 有多门课没选 → 外层 NOT EXISTS(...) 为 False

    5. 王五被排除

5. 用EXISTS/NOT EXISTS实现逻辑蕴含

• SQL语言中没有蕴含（Implication）逻辑运算

• 可以利用谓词演算将逻辑蕴含谓词等价转换为：

  ​ $p\to q\equiv \neg p\vee q$

【例】查询至少选修了学生95001选修的全部课程的学生的学号。

• 解题思路：

  • 用逻辑蕴含表达：查询学号为x的学生，对所有的课程y，只要95001学生选修了课程y，则x也选修了y。

  • 形式化表示：

    用$p$表示谓词 “学生95001选修了课程y”

    用$q$表示谓词 “学生x选修了课程y”

    则上述查询： $(\forall y)p\to q$

  • 等价变化

    $$\begin{gathered} (\forall y)p\to q\equiv \neg (\exists y)(p\to q) \\ \equiv \neg (\neg (\neg (\neg p\vee q)))\equiv \neg (\exists y(p\vee \neg q)) \end{gathered}$$

  • 变化后语义：：对于学生x，不存在这样的课程y，学生95001选修了y，而学生x没有选。

  解：

  SELECT DISTINCT SnoFROM SCWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno = '95001'AND NOT EXISTS(SELECT * FROM SC SCZWHERE SCZ.Cno = SCY.CnoAND SCX.Sno = SCZ.Sno));
  

5. 集合查询

• 标准SQL直接支持的集合操作种类
  • 并操作（UNION）
• 一般商用数据库支持的集合操作种类
  • 并（UNION）
  • 交（INTERSECT）
  • 差（MINUS，EXCEPT）

并操作

• 语法形式

  <查询块>
  UNION [ALL]
  <查询块>
  

• 两个查询结果的属性列个数相同，对应项的数据类型必须能通过隐式转换相互兼容。

• 使用UNION合并多个结果集是，系统会自动去掉重复元组。

• 使用UNION ALL操作符，可以保留重复元组。

【例】查询计算机系的学生或者年龄不大于19岁的学生。

SELECT *FROM StudentWHERE Sdept = '计算机系'
UNION
SELECT *FROM StudetnWHERE Sage <= 19;

等价于

SELECT DISTINCT *FROM StudentWHERE Sdept = '计算机系'OR Sage <= 19;

【例】设数据库中有一教师表Teacher(Tno,Tname,……)。查询学校中所有师生的姓名。

SELECT SnameFROM Student;
UNION
SELECT TnameFROM Teacher;

UNION结果集中的列名与UNION运算中的第一个SELECT语句的 结果集 中的列名相同，其他的SELECT语句的结果集列名将被忽略。所以这个执行语句的结果集中的列名是Sname

交操作

• 标准SQL中没有提供集合交操作，但可用其他法间接实现。

  【例】查询计算机系的学生与年龄不大于19岁的学生的交际。

  本例实际上就是查询计算机系中年龄不大于19岁的学生。

  SELECT DISTINCT *FROM StudentWHERE Sdept = '计算机系'AND Sage <= 19;
  

  等价于

  SELECT *FROM StudentWHERE Sdept = '计算机系'
  INTERSECT
  (SELECT *FROM StudentWHERE Sage <= 19);
  

  【例】查询选修课程1的学生集合与选修课程2的学生的交集。

  本例实际上是查询既选修了课程1又选修了课程2的学生。

  SELECT Sno FROM SC WHERE Cno = '1' ANDSno IN (SELECT Sno FROM SC WHERE Cno = '2';
  

  【例】查询学生姓名与教师姓名的交集。查询学校中与教师同名的学生姓名。

  SELECT DISTINCT SnameFROM StudentWHERE Sname IN(SELECT TnameFROM Teacher);
  

查操作

• 标准SQL没有提供集合差操作，但可用其他方法间接实现。

【例】查询学生姓名与教师姓名的差集。实际上是查询学校中未与教师同名的学生姓名。

SELECT SnameFROM Student
EXCEPT
SELECT TnameFROM Teacher;

等价于

SELECT DISTINCT Sname FROM StudentWHERE Sname NOT IN(SELECT TnameFROM Teacher);

【例】查询选修课程1但没有选修课程2的学生学号

SELECT SnoFROM SCWHERE Cno = '1'AND Sno NOT IN(SELECT SnoFROM SCWHERE Cno = '2'); 

对集合操作结果的排序

• 在执行集合操作是，默认按照最后结果表中第一列数据的升序方式排列记录了。
• 各SELECT子句不能含有ORDER BY子句，但是可以将ORDER BY子句放在最后的SELECT语句后面，以便对最后的结果表排序。
• ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序。
• 任何情况下，ORDER BY子句只能出现在最后。
• 对集合操作结果排序时，ORDER BY子句最好用数据指定排序的列属性，以免出错。

错误写法：

SELECT *FROM StudentWHERE Sdept = '计算机系'ORDER BY Sno
UNION
SELECT *FROM StudentWHERE Sage <=19ORDER BY Sno;

正确写法：

SELECT *FROM StudentWHERE Sdept = '计算机系'
UNION
SELECT *FROM StudentWHERE Sdept <=19
ORDER BY 1 ASC;

集合查询小结

• 参与集合运算的中间结果集的属性列个数必须一致，且对应属性的类型必须兼容；
• 参与运算的属性名不一定相同；
• 最终结果集采用第一个中间结果集的属性名；
• 默认自动删除结果中的重复元组；
• ORDER BY子句要求放在整个子句的最后；
• 标准SQL没有提供集合交、差操作，但可用其他方法间接实现。

6.基于派生表的查询

• 子查询出现在FROM子句中，这是子查询生成的临时派生表（Derived Table）成为主查询的查询对象

  【例】找出每个学生超过他自己选修课程平均成绩的课程号。

  SELECT Sno,CnoFROM SC,(SELECT Sno,AVG(Grade) avg_grade FROM SC GROUP BY Sno) AS Avg_scWHERE SC.Sno = Avg_sc.SnoAND SC.Grade >= Avg_sc.avg_grade;