发布日期: 2025-06-12

更新日期: 2025-06-12

文章字数: 10.4k

Chap.3 关系数据库标准语言SQL

3.1 SQL概述

SQL(Structured Query Language) , 中文叫 结构化查询语言 , 是关系数据库的标准语言.

3.1.1. SQL发展

SQL在上世纪70年代被发明, 在80年代后正式通过审批并成为关系数据库的标准语言.

可见SQL这玩意是非常庞大的. 目前没有任何一个数据库系统能够支持SQL的全部特性 .

3.1.2 SQL的特点

SQL之所以能够经久不衰成为关系数据库的标准语言, 理由如下:

综合统一: SQL能够独立完成数据库生命周期中的全部活动.
- 定义
- 查询
- 更新
- 维护重构
- 安全性 / 完整性控制等
高度非过程化: 不像其余类型数据库语言的面向过程, SQL只需要让应用设计者指明 做什么 即可.
面向集合的操作模式: 关系型数据库用表来表示关系, 因此SQL的查询 / 更新对象都可以是元组的集合(其实也就是一个表)
同一种语法结构能提供多种使用方式
- 能够独立交互使用, 也可以嵌入到高级语言中
语言简洁, 易学易用 ~~(?)~~

总结两点:

SQL是操作数据库的, 因此它无法独立完成应用开发, 不同于其它的大型高级语言.
SQL不能等同于DBMS, 它只是DBMS提供给用户的交互语言.

3.1.3 SQL的基本概念

对应着我们第二章说的数据库三级模式(内模式 -> 模式 -> 外模式), SQL提供了一些对应的名词(也就是实现了一些对应模式的结构):

基本表: 本身独立存在的表
- 对应模式这一层
- 一个关系就是一个基本表
- 一个基本表对应一个存储文件
- 一个表可以带若干索引(也就是外键)
存储文件: 关系在物理存储介质上的存储方式
- 对应 内模式 这一层
- 由SQL自身实现, 对用户透明
视图: 由一个或几个基本表导出的表
- 对应 外模式 这一层
- 只存放外模式的定义, 而不存放外模式的数据
- 是一个虚表

除此之外, SQL针对数据库需要实现的功能, 提供了如下命令:

数据定义语言(DDL) : 负责创建 / 修改 / 删除数据库中各种对象(模式, 基本表, 视图, 索引等)
查询语言(QL) : 按照指定的组合, 条件表达式查找已存在的数据
数据操纵语言(DML) : 对已经存在的数据库进行元组的插入 / 删除 / 修改等操作
数据控制语言(DCL) : 用来授予 / 收回某种权限, 并对数据库进行监视.

下面, 就从上述四种语言的角度, 分别捣鼓捣鼓.

3.1.4 本章基于的关系模型

我们在下方梳理四种语言的时候, 需要一个具体的案例来为各位读者演示其具体语句的含义.
这里给出本章基于的关系模型供各位参考.

3.2 数据定义语言 DDL

SQL的数据定义分以下三类:

模式定义
表定义
视图和索引的定义

3.2.1 模式相关定义语言

3.2.1.1 模式的创建

这里会跟我们之前提到的一个地方有点冲突.
现代数据库中, 一个 数据库(DATABASE) 是允许存在多个 模式(SCHEMA) 的. 这通常用于不同逻辑的分区(相当于在一个大文件夹底下单独分了几个小文件夹, 小文件夹下面又能有很多表).

定义模式的语句如下:

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

两点需要注意:

创建模式必须具有数据库管理员(DBA)权限, 或者具有DBA授予的创建模式的权限.
如果没有指定 <模式名> , 则模式名隐含为 <用户名>

注意
第二点的原因正是我们所说的数据库多模式的目的, 用于逻辑分区. 比如一个学校数据库(School)中可以创建两种模式: 校内职工(Worker) 和公开可见(Public) , 这二中模式里面的关系(表)显然可能不同对吧.

我们还需要特别强调一下, MySQL中一个数据库只能有一种模式, 这是其与其它数据库系统的显著区别!!!
究其根本在于 MySQL 是一个开源的中小型数据库系统, 因此未能像 Postgre SQL 中实现这种逻辑分流的结构.
这也就意味着, Schema 与 Database 在MySQL中其实是同义词.

一个模式下可下属多个表, 多个视图, 多个授权定义等.
那问题来了, 我该怎么把一个表 / 视图 / 授权定义附在一个模式下面?
有以下三种方法:

直接在定义表的时候显式的指出模式名

Create table"S-T".Student(......);

在创建模式的时候同时创建表 / 视图 / 授权定义

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

设置所属模式

在创建基本表的时候, 系统会根据当前的 搜索路径 来指定表所在的模式, 如果当前搜索路径为空, 则系统会报错.
设置搜索路径的语句如下:

SET search_path TO "S-T";
Create table Student(......);

3.2.1.2 模式的删除

删除模式要简单不少:

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

后面会有可选项, 我们解释一下:

CASCADE: 级联, 代表着只要模式删了, 模式下面的所有数据库对象全删掉(Linux中的递归删除)
RESTRICT: 限制, 只有模式SCHEMA下面啥都没有的时候, 才能把这个模式删除.(否则会报错)

3.2.2 基本表的相关定义语言

3.2.2.1 基本表的创建

基本表, 也就是我们熟悉的 关系(表) , 通常通过如下语言进行定义:

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

上面这个定义方式需要解释一下:

列名: 就是你自己定义的 属性名 .
数据类型: 你希望用什么数据类型来表示这一个属性.
- 全部的数据类型实在太多了, 我们下面会给一个表.
列级完整性约束条件: 其实就是这一列需要满足什么完整性条件
- 主键约束: PRIMARY KEY
- 唯一性约束: UNIQUE
- 非空值约束: NOT NULL
- 取值范围的规定等
表级完整性约束条件: 对于这一整个表(包括其中有些元素), 需要满足什么完整性条件
- 外键的定义
- 主键如果不是某个属性, 而是一个属性集合, 则需要在这里定义

我们这里给个例子:

CREATE TABLE SC
(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),
    /* 主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno)REFERENCES Course(Cno)
    /* 表级完整性约束条件，Cno是外码，被参照表是Course*/
);

3.2.2.2 基本表的修改

修改基本表用 ALTER 关键字来进行.

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

解释一下:

ADD就不说了, 跟上面的定义基本上是一样的.
DROP用于删除原有表中的一些东西:
- 删除某个属性(列)的时候, 可以用两种关键字:
  - CASCADE: 把这列删了, 外面如果有引用这一列的对象, 也删了.
  - RESTRICT: 如果外面有引用这一列的对象, 则禁止删除这列.
- 用于删除某个完整性约束的时候, 直接删除即可.
ALTER用于修改原有的列定义.

注意
虽然SQL标准定义上说删除列的时候可以用CASCADE关键字, 但是事实上, 大部分数据库对于外键引用是要单独处理的.
即只要有外键引用就不允许删除该列.

3.2.2.3 删除基本表

删除基本表与删除模式很类似:

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

使用CASCADE则会一并删除所有引用改表的对象.
使用RESTRICT, 则如果这个被删除的表还有别的引用关系, 就拒绝删除, 报错.

3.2.3 索引的相关定义语言

索引是个啥?
引入这个玩意的意义很简单, 就是要增加对指定属性的搜索速度.

通常而言, 数据库系统会把怎么建立索引, 具体实现等方式都包了, 而只需要用户给一句建立语句即可.
并且, 有些数据库会自动为主键完整性(PRIMARY KEY) / 唯一完整性(UNIQUE)的属性建立索引.

常见的索引包括:

B / B+树索引
散列(Hash)索引
顺序文件索引
位图索引

3.2.3.1 索引的创建

SQL中索引通常这样创建:

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

解释一下:

<次序> : 指定索引值的排列次序
- 升序: ASC
- 降序: DESC
- 缺省值: ASC(升序)
可以选择两种关键字:
- UNIQUE: 每个索引值只对应唯一的数据记录
- CLUSTER: 建立聚簇索引

注意
聚簇索引类似于将数据分组, 每组建立一个索引值.
对于范围查找的情况效率会远高于唯一索引.
但是聚簇索引有其局限性:
一个基本表上只能有一个聚簇索引
经常更新的属性上不适合建立聚簇索引(排序和重组织代价高)
已有大规模数据表中建立聚簇索引的代价很高

3.2.3.2 索引的修改

修改索引比较简单, 只能修改索引名:

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

3.2.3.3 索引的删除

删除索引也很简单:

DROP INDEX <索引名>;

3.2.4 数据字典

我们之前的章节中提过这个玩意, 现在了解完数据定义语言之后, 可以再把这个东西拿出来了.
它记录了数据库中所有定义信息:

关系模式
视图
索引
完整性约束
操作权限
统计信息

执行数据定义语言时, 实际上就是在更新数据字典表内的相关信息.

3.3 数据查询语言 QL

SQL中, 数据查询这个事情是最复杂的, 它涉及到很多很多的额外机制.
但在开始介绍之前, 我们先要把最基本的结构搞明白:

SELECT A1, A2, ..., An
FROM R1, R2, ..., Rn
WHERE <查询条件表达式>

3.3.1 单表查询

单表查询是比较简单的情况, 查询只涉及到一个表.

3.3.1.1 选择表中的若干列

就是把指定的属性拿出来单成一个表:

SELECT Sno,Sname
FROM Student;

特殊的, 如果要直接把所有列都拿出来, 可以这么写:

SELECT *
FROM Student;

当然, 时至今日, 查询还可以支持一些别的机制, 比如:

查询经过计算的值 / 经过函数处理的值
单独多加一个常量
改变列的标题

3.3.1.2 选择表中的若干元组

可以通过如下写法来选择表中的若干元组:

SELECT [ALL | DISTINCT] <目标列表达式>[,<目标列表达式>]

这里的关键字:

ALL: 缺省值, 代表着直接全部取出, 不做处理
DISTINCT: 会对取出的表项做去重处理

当然, 如果只能这么拿, 未免显得太简陋了.
SQL支持通过使用条件表达式来进行条件筛选的拿法:

SELECT [ALL | DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…
[ WHERE <条件表达式> ]

只给出这么一张表未免太生硬了点, 我们给几个常用的例子:

比较大小:

WHERE Sdept=‘CS’; //专业是CS
WHERE Sage<20; //年龄小于20
WHERE Grade<60; //成绩小于60

确定范围:

WHERE Sage BETWEEN 20 AND 23; //年龄在20~23之间
WHERE Sage>=20 AND Sage<=23; //与上文同义

WHERE Sage NOT BETWEEN 20 AND 23; //年龄不在20~23之内
WHERE Sage<20 OR Sage>23; //与上文同义

确定集合:

WHERE Sdept IN ('CS','IS'); //专业取值在集合('CS', 'IS')内
WHERE Sdept='CS' OR Sdept='IS'; //与上文同义

WHERE Sdept NOT IN ('IS','CS'); //专业取值不在集合('CS', 'IS')内
WHERE Sdept!='CS' AND Sdept!='IS'; //与上文同义

字符匹配:

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

这里这个匹配串需要提一下:
它可以是一个完整的字符串, 也可以含有通配符 % 和 _ .

其中 % 可以代指任意长度的字符串; _ 则只能代指任意单个字符.

比如: a%b 在系统中可以代指以a开头, 以b结尾的任何字符串.

WHERE Sno LIKE '202215121'; //学号为202215121的元组
WHERE Sno = ' 202215121 '; //与上文同义

WHERE Sname LIKE '刘%'; //姓刘的所有元组
WHERE Sname NOT LIKE '刘%'; //不姓刘的所有元组
WHERE Sno LIKE '_0%'; //学号第二位是0的所有元组

有个非常常见的问题了, 如果我要搜 % 和 _ 字符呢?
后面那个 换码字符 就是这个意思: 自定义一个换码字符, 然后把它写成 <换码字符>% 和 <换码字符>_

WHERE Cname LIKE 'DB\_Design' ESCAPE '\' ;

涉及空值的查询:

WHERE Grade IS NULL;
WHERE Grade IS NOT NULL;

多重条件查询: 就是把前面几种用谓词合并起来, 有 OR 和 AND 两种谓词.

WHERE (Ssex = '男' OR Sage > 18) AND Sdept = 'IS';

3.3.1.3 按照查询结果排序 ORDER BY

如标题所说, 这个子句是来解决按照查询结果属性列来排序的.

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

可以按照一个 / 多个属性列排序, 排序与建索引的时候的关键字同义, ASC表升序, DESC表降序.

注意
对于升序, 会将NULL排在最后; 对于降序, 会将NULL排在最前.

给个例子:

SELECT *
FROM Student
ORDER BY Sdept, Sno DESC;

意思是会按照学生专业升序, 学号降序排列查询结果.

3.3.1.4 聚集函数

聚集函数其实就是对表中的一些基本信息进行统计, 并显示出来的.
它的查询目的不在于某个具体的元组 / 属性, 而在于整个表的一些总体统计数据.

常见的聚集函数包括:

COUNT(*) //统计元组个数
COUNT([DISTINCT|ALL] <列名>) //统计一个列中值的个数
SUM([DISTINCT|ALL] <列名>) //一列值的总和
AVG([DISTINCT|ALL] <列名>) //一列值的平均值
MAX([DISTINCT|ALL] <列名>) //一列值的最大值
MIN([DISTINCT|ALL] <列名>) //一列值的最小值

举几个例子:

SELECT AVG(Grade)
FROM SC //选课表
WHERE Cno='2';
//选修2号课程学生的平均成绩

SELECT MAX(Grade), MIN(Grade)
FROM SC
WHERE Cno='2';
//选修2号课程学生的最高 / 最低成绩

注意
聚集函数不能用在WHERE的条件语句中, 如果希望利用聚集函数做筛选, 则需要使用下面讲到的GROUP BY语句.

3.3.1.5 分组子句 GROUP BY / HAVING

分组子句的出现意义其实在于细化聚集函数的作用域. 这句话啥意思呢?

我们考虑上面说过的情况, 如果我们就是希望查询每一门课程的平均成绩, 怎么办?
我们会发现好像写不出来一个很高效的子句, 总不能一个个WHERE吧.
GROUP BY相当于为查询结果提供了一个分组依据, 尝试将查询结果 按照某一列 分组, 这样聚集函数就可以分别作用在它们的上面, 而不是整个查询结果上面.

考虑这个例子:

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

要什么? 课程号和对于学生号的计数.
从哪选? 选课表SC中选.
怎么分组? 按照课程号分组.

那意思很明确了, 我查的就是每门课程选修的学生数目.

到这里, 可能读者有个想法, 我能不能用聚集函数来作为筛选条件? 比如筛选出平均分80以上的学生?
可以, 但是这里需要用到的子句不是WHERE, 而是GROUP BY的HAVING.
我们先看错误案例:

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

注意
它看起来语义没什么问题, 但是犯了个很严重的错误, 聚集函数不能用在WHERE子句中 .

正确写法是这样:

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

读者可以这样理解 WHERE 与 HAVING的区别:

WHERE作用于整个表 / 整个视图上, 这个作用域是变不了的.
HAVING作用于根据GROUP BY分出来的每个分组上.

在明确了以上内容后, 我们给出GROUP BY语句的通用形式:

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

3.3.1.6 限制查询结果的元组数量 LIMIT

这玩意乍看有点无厘头, 但是如果跟前面我们提过的 ORDER BY 组合起来看就好了, 它可以只展示某一个排名范围内的查询结果.

其标准语法如下:

LIMIT <行数1> [OFFSET <行数2>];

含义为:

取行数1 行的元组.
忽略前行数2 的元组.

举个例子, 我们要课程2中成绩排名3~7名的学生学号和姓名:

SELECT Sno, Sname
FROM SC
WHERE Cno = '2'
ORDER BY Grade
LIMIT 5 OFFSET 2

3.3.2 连接查询

上面讲了很多, 但读者仔细想想其实都是针对一个表的查询操作, 现在我们要更进一步, 针对多个表进行同时查询.
哎, 这就想起来了, 上一章我们好像叨叨过一个叫做连接的运算, 忘记的读者回去看看哈. 连接

3.3.2.1 等值 / 非等值连接查询

我们考虑这么一种情况, 我们要所有学生的选课情况以及其全部信息:
我们要做的, 首先是要考虑将数据源扩展成两个表(学生表Student, 选课表SC)
其次是要将学生表中学号与选课表学号相同的表项连起来.

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

挺好, 但是我们会发现, 由于采用的是等值连接, 它不会自动去掉重复列.
这导致学号被显示了两次, 有点愣愣的.

因此其实我们完全可以用自然连接的方式来做这个事情:

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno;

相当于去掉了相同的属性列对吧.

对了, 这里读者别被连接限制住了思想, 我们仍然可以在连接后面加一些别的筛选条件:

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

在这一小节的最后说一下当前数据库对于这种连接采取的主要三种方法:

嵌套循环法(NESTED-LOOP): 就是两层循环嵌套, 对于表1的一个元组, 会依次扫描表2的每个表项进行连接操作. 从算法时间复杂度的角度来看, 这种是最慢的( $ n^2 $ )
排序合并法(SORT-MERGE): 类似于排序算法中的 归并排序 , 它先将两个表按照指定连接列进行排序, 随后再归并即可.
索引连接(INDEX-JOIN): 对两个表的指定连接列都建立索引, 随后按照索引范围来建立符合条件的连接.

3.3.2.2 自身连接

自身连接很好理解, 我自己跟自己需要建立连接展示某种关系对吧.
这种跟上面直接连接又一点点区别, 即这次连接的两个表是同一个名字, 因此需要做区分:
考虑这个例子: 查询每门课的间接先修课

SELECT FIRST.Cno, SECOND.Cno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno = SECOND.Cno

3.3.2.3 外连接

外连接这个概念我们在之前的连接运算中写过, 就是保留左边 / 右边 / 两边不符合连接要求的表项, 并将对应的对方表项填成NULL.
在SQL中, 这个连接方式用 OUTER JOIN 表示.

LEFT OUTER JOIN
//列出左边表中所有的元组

RIGHT OUTER JOIN
//列出右边表中所有的元组

FULL OUTER JOIN
//列出所有表中所有的元组

看这个例子: 查询所有选课学生的信息, 包括没选课的学生.

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC
ON (Student.Sno = SC.Sno)

注意
细心的读者应该看到了, 这里使用的关键词是 ON , 不是 WHERE .
因为 WHERE 本质上做的是一个过滤操作, 而 OUTER JOIN 恰恰不需要这个过滤操作.
这里还请读者记忆一下.

3.3.2.4 多表查询

这个就很简单了, 只需要把多个表都写到FROM里面就行:

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

博主大概能知道读者的感受, 这玩意其实初看是有点乱的, 得多用.
来, 看俩例子回顾理解一下:

(1) 查询选修课程号2或4课程的学生学号和姓名, 写出关系代数表达式和SQL语句.

关系代数表达式: $ \pi_{Sno, Sname}(\sigma_{Cno = ‘2’ \lor Cno = ‘4’}(Student \bowtie SC)) $

SQL:

SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND (SC.Cno='2' OR SC.Cno='4');

(2) 查询平均成绩80分以上的女生姓名（假设姓名不重复）, 写出关系代数表达式和SQL语句.

关系代数表达式: $ \pi_{Sname}(\sigma_{Avg(Grade)>80 \land Ssex = ‘女’}(Student \bowtie SC)) $

SQL:

SELECT Student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Ssex = '女'
GROUP BY Student.Sname
HAVING AVG(SC.Grade) > 80;

3.3.3 嵌套查询

我们之前说了一个标准的查询语句结构:

SELECT
FROM
WHERE

它能起到一个筛选的作用, 将已有的模式转化成一个虚拟的表.
既然是这样, 那理论上这个虚拟的表也可以进行查询啊, 这就引出了我们嵌套查询的概念:

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询，允许多层嵌套查询 .

注意
子查询不能使用ORDER BY语句, 这点还请读者着重注意!

3.3.3.1 带有 IN 谓词的嵌套查询

经常使用 IN 谓词来表示不相关子查询(即子查询可以自行运行, 与父查询无关).
这种查询其实是一个将查询分步的思想:

看这个例子: 查询与 “刘晨” 在同一个系学习的学生.
我们把这个查询分成两步:

查询 “刘晨” 所在系
查询在这个系内的学生.

//Step 1
SELECT Sdept
FROM Student
WHERE Sname = '刘晨'

//Step 2
SELECT Student*
FROM Student
WHERE Sname IN ( Step 1 );

//两步合一块
SELECT Student*
FROM Student
WHERE Sdept IN (
  SELECT Sdept
  FROM Student
  WHERE Sname = '刘晨'
);

3.3.3.2 带比较运算符的嵌套查询

这种查询其实是条件筛选查询的一种特殊情况. 当我们明确查询结果是一个明确的值时, 自然可以使用比较运算符号来进行查询:

看这个例子: 找出每个学生超过他选修课程平均成绩的课程号.

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

3.3.3.3 带 ANY(SOME) / ALL 的子查询

这俩谓词是对比较运算符的一种扩展: 简而言之, ANY(SOME)就是对子查询中的结果取或关系, 而ALL则是取与关系.

> ANY //大于子查询结果中的某个值
> ALL //大于子查询结果中的所有值
= ANY //等于子查询结果中的某个值
= ALL //等于子查询结果中的所有值(通常没有意义)

其它的运算符我们就不写了, 相信读者能够理解这俩的作用.

这里其实可以提一句, 大部分的ANY和ALL其实都可以通过我们之前说过的 聚集函数 来实现.
举个例子: 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

如果我们用ALL:

SELECT x.Sname, x.Sage
FROM Student x
WHERE x.Sage < ALL(
  SELECT y.Sage
  FROM Student y
  WHERE y.Sdept = 'CS'
) AND x.Sdept != 'CS';

但是其实这个玩意完全可以用聚集函数来解决:

SELECT x.Sname, x.Sage
FROM Student x
WHERE x.Sage < (
  SELECT MIN(y.Sage)
  FROM Student y
  WHERE y.Sdept = 'CS'
) AND x.Sdept != 'CS';

3.3.3.4 带有 EXISTS 谓词的子查询

EXISTS, 即存在量词 $ \exists $ 的意思. 它的子查询很有意思, 不会返回任何具体的元组, 而是只看有没有结果返回真值.
如果查询结果为空, 就返回false; 反之, 则返回true.

举个例子读者大概能明白: 查询所有选修了1号课程的学生姓名.

SELECT s.Sname
FROM Student s
WHERE EXISTS(
  SELECT *
  FROM SC sc
  WHERE sc.Sno = S.sno AND sc.Cno = '1'
);

它相当于将学生表Student的每个元组提取出来, 放到另一个选课表SC中查, 找到满足条件的就返回true, 否则返回false. 外层根据内层返回的true / false决定要不要输出这个学生的姓名.

接下来会涉及到一个比较复杂的东西, 叫 全称量词 . 这玩意烦就烦在数据库里面没实现这个东西, 因此我们只能用这个存在量词来实现它.

举个例子: 查询选修了全部课程的学生姓名.

我们先不写SQL呢, 先把这东西转成存在量词: 选修了全部课程 => $ \neg $ 至少有一门课没选

所以就这么写:

SELECT s.Sname
FROM Student s
WHERE NOT EXISTS(
  SELECT *
  FROM Course c
  WHERE NOT EXISTS(
    SELECT *
    FROM SC
    WHERE SC.Sno = s.Sno AND c.Cno = SC.Cno
  )
);

这一大串非常不好理解, 我们解释一下:
读者可以把外层的SELECT FROM理解成循环, 我们从里向外看:

最内层: 这个双层循环当前的学生选了当前这个课程.
次外层: 存在有一个课程, 当前最外层的学生没有选这个课程.
最外层: 存在这么个学生, 这个学生没有 没有选的课程 .

这我们就表示出来了全称量词, 我去, 这可真费劲.

有没有一种更加简单的方法来模拟全称代词?
有的, 兄弟, 有的.

我们通常可以用计数的方式来模拟全称代词, 比如把上面那一坨简化成:

SELECT S.Sno
FROM SC S
GROUP BY S.Sno
HAVING COUNT(DISTINCT S.Cno) = (SELECT COUNT(*) FROM Course);

我选修的课程数目跟课程数总量一样, 那不就是我选了所有的课程嘛?
就是这么个理.

3.3.4 集合查询

集合查询就是我们之前讲的几个基本运算, SQL中给的集合查询语句包括:

并集: UNION
交集: INTERSECT
差集: EXCEPT

但是集合查询有严格的限制, 即对应的表列数(也可以说目数)必须相同, 对应的列数据结构还要一致.

3.3.4.1 并集 UNION

直接给例子了: 查询计算机科学系的学生及年龄不大于19岁的学生.

SELECT *
FROM Student
WHERE Sdept = 'CS'
UNION
SELECT *
FROM Student
WHERE Sage <= 19;

其实就是一个OR的事情:

SELECT *
FROM Student
WHERE Sdept = 'CS' OR Sage <= 19;

注意
UNION会自动去除重复的元组, 而 UNION ALL不会.

3.3.4.2 交集 INTERSECT

直接给例子:查询计算机科学系并且年龄不大于19岁的学生.

SELECT *
FROM Student
WHERE Sdept = 'CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage <= 19;

其实就是一个AND的事情:

SELECT *
FROM Student
WHERE Sdept = 'CS' AND Sage <= 19;

3.3.4.3 差集 EXCEPT

查询计算机科学系的学生与年龄不大于19岁的学生的差集.

SELECT *
FROM Student
WHERE Sdept = 'CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <= 19;

实际上, A-B不就是 $ A \cap \overline{B} $ 嘛, 也就是计算机科学系大于19岁的学生:

SELECT *
FROM Student
WHERE Sdept = 'CS' AND Sage > 19;

3.3.5 基于派生表的查询

啥叫派生表呢, 就是在 FROM 那里先构建一个表, 然后在这个虚拟的派生表里面查.

还是举例子吧: 找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, (
  SELECT Sno, Avg(Grade)
  FROM SC
  GROUP BY Sno
) AS Avg_sc(avg_sno,avg_grade)
WHERE 
  SC.Sno = Avg_sc.avg_sno AND
  SC.Grade >=Avg_sc.avg_grade;

这个例子其实在嵌套查询里面出现过, 读者可以去看看对比一下哈.

我们发现这里后面加了一个 Avg_sc(avg_sno,avg_grade), 这是为啥?
我们这个操作相当于新建了一个表, 因此也必然需要给这个表赋表名和列名对吧.

当然, SQL还是很智能的, 如果没有用聚集函数, 那么不加列名也可以, SQL会使用子查询中SELECT中的名字自动当成新表列的名字.

3.3.6 总结

哎呀, 这查询语句不愧是最复杂的一部分, 基本上捋完了哈.
相信读者应该现在有点迷迷瞪瞪的, ~~笔者也是~~ .

给个总结:

SELECT语句的一般形式如下:

SELECT [ALL|DISTINCT]
  <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
  [,<表名或视图名> [别名]] …
  |(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]
[ LIMIT <行数1> [OFFSET <行数2>] ];

3.4 数据操纵语言 DML

我们把最麻烦, 最多样的查询搞完了, 操纵语言就会显得简单许多.
主要针对的就是 增 / 删 / 改 操作

3.4.1 插入数据

插入数据一般分两种:

单独插入一个元组
插入子查询结果

有了查询的基础, 上面这两种应该都很好理解. 前者相当于单独插入一行, 后者一次插入多行.

3.4.1.1 插入元组

插入元组的通用语句格式为:

INSERT
INTO <表名> [(<属性列1>[<属性列2 >…])]
VALUES (<常量1>[, <常量2>, ...]);

从这个定义其实能看出来, 插入单个元组的时候有两种方式:

你可以直接把属性列列上去, 然后按照你列的顺序在后面直接写要插入的常量
也可以直接不写属性列, 直接写常量. 但这种方式的前提是 你写的顺序必须跟表本身的顺序一致 .

针对两种情况分别举两个例子:

显式写出属性顺序:

INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('202215128', '陈冬', '男', 'IS', 18);

隐式不写出属性顺序:

INSERT
INTO Student
VALUES ('202215126', '张成民', '男', 18, 'CS');

当然, 还有一种方式是你可以只显式的写出部分属性列, 并插入部分值.
这个时候其它属性列会自动填入默认值或者NULL.

注意
要明确, 当你这个属性列未填入被设置成 NOT NULL的时候, 这样插入会报错的.
很好理解嘛, 你都说让它不空了, 还不填它, 那肯定出问题.

给个例子:

INSERT
INTO SC(Sno,Cno)
VALUES ('202215128', '1');

这样插入选课表, 会给选课表的 Grade 属性自动赋一个NULL.

3.4.1.2 插入子查询结果

这种就相当于批量化插入, 或者插入若干个需要利用聚集函数来计算出的值.
这时候需要保证 SELECT目标列与INTO子句的属性列匹配 .

比如我们要求每个系学生的平均年龄:

//Step1 建表
CREATE TABLE Dept_age{
  Sdept VARCHAR(15) //系名
  Avg_age INT //平均年龄
}

//Step2 插入查询结果
INSERT
INTO Dept_age(Sdept, Avg_age)
  SELECT Sdept, AVG(Sage)
  FROM Student
  GROUP BY Sdept;

3.4.2 修改数据

修改数据的标准格式如下:

UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];

思路很清晰, 通过UPDATE后面的表名和WHERE后面的条件来确定修改位置, SET是具体修改方法.
类似的, 还是有三种修改方式:

修改某一个元组的值
修改多个元组的值
带子查询的修改语句

3.4.2.1 修改某一个元组的值

直接给例子: 将某个学生的年龄修改为22岁:

UPDATE Student
SET Sage = 22
WHERE Sno = '202215121';

3.4.2.2 修改多个元组的值

直接给例子: 将整个学生表中学生的年龄增加1岁:

UPDATE Student
SET Sage = Sage + 1;

3.4.2.3 带子查询的修改语句

这个需求很好理解, 我总不能一动要么动一个, 要么动整个表吧, 我如果只要动一部分呢?
那就还是要用我们的查询语句:

给例子: 将计算机科学系全部学生的成绩置为100:

UPDATE SC
SET Grade = '100'
WHERE Sno IN (
  SELECT Sno
  FROM Student
  WHERE Sdept = 'CS'
);

3.4.3 删除数据

删除数据就很简单了:

DELETE
FROM <表名>
[WHERE <条件>];

就是删除表名中符合条件的元组.
跟修改类似, 有三种方式:

删除某一个元组
删除多个元组
删除带查询语句的元组

3.4.3.1 删除某一个元组

直接给例子: 删除学号为202215128的学生记录.

DELETE
FROM Student
WHERE Sno = '202215128';

3.4.3.2 删除多个元组

直接给例子: 删除所有的学生选课记录.

DELETE
FROM SC;

3.4.3.3 带子查询的删除语句

直接给例子: 删除计算机科学系所有学生的选课记录.

DELETE
FROM SC
WHERE Sno IN (
  SELECT Sno
  FROM Student
  WHERE Sdept = 'CS'
);

3.5 空值的处理

所谓空值, 就是我们很熟悉的 NULL , 这玩意出现一般有三种情况:

属性应该有值, 但是现在暂时不知道这个值(未考试考生的成绩)
属性本身不应该有值(缺考考生的成绩)
由于某种原因不便填写(个人隐私)

之所以要把这玩意但拉出来, 就是因为它稍微有点特殊.

3.5.1 空值的判断与相关约束条件

空值的判断相关语句就两个:

IS NULL
IS NOT NULL

3.5.2 空值相关的运算

算数运算: 只要存在操作数是空值的情况, 结果就是空值.
比较运算: 只要存在操作数为空值的情况，比较结果就是UNKNOWN
逻辑运算: 三值逻辑(TRUE, FALSE, UNKNOWN)

值得提一下的是这个逻辑运算:

3.6 视图

视图是一个虚表. 它只存放视图的定义, 不存放视图对应的数据.
通常意味着某一种虚表要经常用到(比如某个应用程序中)
基表中的数据变化时, 视图中的数据同样会发生变化!

3.6.1 定义视图

3.6.1.1 建立视图

建立视图的语句格式如下:

CREATE VIEW <视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];

有两个地方需要讲一下:

关于列名是否要写:
- 要么全不写, 那么你下方的子查询查到哪些列, 你的视图就会包含哪些列.(列名也相同)
- 要么全写, 通常要写列名的时候有几种情况:
  - 子查询中有列名相同的情况
  - 某个目标列是列表达式或者聚集函数
  - 需要更合适的名字
这个 WITH CHECK OPTION 是啥意思?
- 说白了就是, 给你的这个视图加上了一个限制条件, 这个视图以后只能插入 / 修改符合你的查询语句条件的元组.

举个例子: 建立信息系学生的视图.

CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage, Sdept
FROM Student
WHERE Sdept = 'IS';

如果我们加上了 WITH CHECK OPTION , 这就意味着, 你之后插入 / 修改的元组必须还都满足 Sdept = ‘IS’ 这个条件.

注意
我们可以发现, 上面这个例子中的视图, 实际上就是把一个表做了一点点筛选, 然后再呈现.
这种视图被称作 行列子集视图 .

再写几个带表达式 / 带聚集函数的视图:

//所有学生的生日视图(带表达式)
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2025-Sage
FROM Student;

//学生的平均成绩视图(也叫分组视图)
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

当然, 既然视图是子表, 那自然也可以从视图中进一步导出下一个视图. 这个我们就不说了, 无非就是查询语句改一下而已.

3.6.1.2 删除视图

删除视图要简单很多:

DROP VIEW <视图名>[CASCADE];

跟之前的删除很像, 加上 CASCADE 会使得从这个视图导出的其余视图一并被删除.
反之, 如果这个视图下面还有导出视图, 那就拒绝删除.

3.6.2 查询视图

从用户角度来看 , 查询视图与查询基本表是相同的操作, 只需要把表名换成视图名, 表列换成视图列即可.

对于关系数据库而言: 使用 视图消解法 进行查询:

进行有效性检查
转化成等价的针对基本表的查询
执行修正后的查询

视图消解法有时候会出问题:
举个例子: 我们有这么个视图, 它是通过聚集函数导出的.

CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

假设我们要这么查找:

SELECT *
FROM S_G
WHERE Gavg>=90;

这时候, 表面上这个查询确实没问题, 但是别忘了, 这个Gavg是通过聚集函数导出的 , 所以这种查询实际上犯了我们之前三令五申的一个错误:
它把聚集函数放到WHERE后面了.

这时候就得需要我们手动来了, 比如转换成导出表:

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

3.6.3 视图的增 / 删 / 改

更新视图与更新基本表从用户视角也是相同的, 通过 INSERT / UPDATE / DELETE 来进行.

需要注意的一点是: 不是什么视图都可以随便修改的! , 通常原则如下:

如果视图是通过多个基本表通过连接操作导出的, 则不允许更新(一更新涉及到很多表, 容易出错)
如果视图涉及到分组和聚集操作, 也不允许更新(你只更新一个函数值, 但是不告诉原表怎么产生的这个新函数值, 那怎么行?)
只有视图是 行列子集视图 , 即是从单表导出, 并且不涉及到聚集函数等操作的情况下, 才允许更新.

3.6.4 视图的作用

视图, 在我们之前的章节中讲过数据库的三个模式, 不知道读者还记得否?
这个视图, 通常而言跟 外模式 有些关系, 即它是对接应用设计的.

回过头来, 我们就能明白为什么说一个模式能有很多个外模式, 因为从基本表能够导出各种各样不同的视图, 进而对接各种各样的应用.
一定程度上保证了数据库的逻辑独立性和数据安全性对吧.

这一章应该是整个数据库中最复杂的一章, 主要针对的是长久发展以来的SQL语言, 语法比较细碎, 并且还涉及到了外模式和模式之间的一些关系.

但这一章正是应用数据库的核心. 还望读者能够耐心读完它.

MUG-chen

http://mug-chen.github.io/posts/3029.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 MUG-chen !

数据库系统概论 Database

数据库系统概论-Chap.4

数据库系统概论 Chap.4 数据库安全性

2025-06-13 Study Notes 计算机专业课总复习篇

数据库系统概论 Database

数据库系统概论-Chap.2

数据库系统概论 Chap.2 关系模型

2025-06-09 Study Notes 计算机专业课总复习篇

数据库系统概论 Database

数据库系统概论-Chap.3

Chap.3 关系数据库标准语言SQL

3.1 SQL概述

3.1.1. SQL发展

3.1.2 SQL的特点

3.1.3 SQL的基本概念

3.1.4 本章基于的关系模型

3.2 数据定义语言 DDL

3.2.1 模式相关定义语言

3.2.1.1 模式的创建

3.2.1.2 模式的删除

3.2.2 基本表的相关定义语言

3.2.2.1 基本表的创建

3.2.2.2 基本表的修改

3.2.2.3 删除基本表

3.2.3 索引的相关定义语言

3.2.3.1 索引的创建

3.2.3.2 索引的修改

3.2.3.3 索引的删除

3.2.4 数据字典

3.3 数据查询语言 QL

3.3.1 单表查询

3.3.1.1 选择表中的若干列

3.3.1.2 选择表中的若干元组

3.3.1.3 按照查询结果排序 ORDER BY

3.3.1.4 聚集函数

3.3.1.5 分组子句 GROUP BY / HAVING

3.3.1.6 限制查询结果的元组数量 LIMIT

3.3.2 连接查询

3.3.2.1 等值 / 非等值连接查询

3.3.2.2 自身连接

3.3.2.3 外连接

3.3.2.4 多表查询

3.3.3 嵌套查询

3.3.3.1 带有 IN 谓词的嵌套查询

3.3.3.2 带比较运算符的嵌套查询

3.3.3.3 带 ANY(SOME) / ALL 的子查询

3.3.3.4 带有 EXISTS 谓词的子查询

3.3.4 集合查询

3.3.4.1 并集 UNION

3.3.4.2 交集 INTERSECT

3.3.4.3 差集 EXCEPT

3.3.5 基于派生表的查询

3.3.6 总结

3.4 数据操纵语言 DML

3.4.1 插入数据

3.4.1.1 插入元组

3.4.1.2 插入子查询结果

3.4.2 修改数据

3.4.2.1 修改某一个元组的值

3.4.2.2 修改多个元组的值

3.4.2.3 带子查询的修改语句

3.4.3 删除数据

3.4.3.1 删除某一个元组

3.4.3.2 删除多个元组

3.4.3.3 带子查询的删除语句

3.5 空值的处理

3.5.1 空值的判断与相关约束条件

3.5.2 空值相关的运算

3.6 视图

3.6.1 定义视图

3.6.1.1 建立视图

3.6.1.2 删除视图

3.6.2 查询视图

3.6.3 视图的增 / 删 / 改

3.6.4 视图的作用