数据库操作

数据库连接

启动、停止数据库

net start mysql80
net stop mysql80

客户端连接

mysql [-h 127.0.0.1] [-P 3306] -u root -p

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建数据库

CREATE DATABASE <database_name>;	-- if database exists, it will report error

CREATE DATABASE IF NOT EXISTS <database_name>; 	-- if database exists, it will not report error

CREATE DATABASE <database_name> DEFAULT CHARSET utf8mb4; -- 指定字符集

删除数据库

DROP DATABASE IF EXISTS <database_name>;

选择数据库

USE <database_name>;

模式、目录、环境

数据库系统提供三层结构的关系命名机制：目录-模式-关系/视图

目录可理解为用户（包括数据库管理员）或应用
一个管理员可建立多个数据库模式
一个数据库模式有多个关系模式/视图

SQL 标准中未提供对目录操作，但提供对模式的操作

CREATE schema
DROP schema

SQL 环境包括目录、模式和用户标识（授权标识符），用户提交的 SQL 语句在该环境中运行

数据定义语言 DDL

SQL 的数据定义语言 (DDL) 能够定义每个关系的信息，包括：

关系模式
属性取值类型、取值范围（属性域）
完整性约束（主外码）
关系的安全性和权限信息
还包括其它信息：
- 每个关系维护的索引集合
- 每个关系在磁盘上的物理存储结构

即对于表结构进行操作

数据类型

数值类型

无符号表示需要写在类型后方，如：`INT UNSIGNED
对于浮点数需要设置精度和标度，精度是数字最长整体长度，标度是保留小数点位数，如：DOUBLE(4,1) ，整数 3 位，小数 1 位

字符串类型

BOLB：表示二进制数据
TEXT：表示文本数据
CHAR(10) 不管存储多少字符都会占用 10 个字节，空位置使用空格占位。性能较好
VARCHAR(10) 表示最长字符串长度，随着数据长度占用字节数也不同。性能较差

注意 CHAR 和 VARCHAR 两种数据的使用场景

日期时间类型

DATE：日历日期
TIME：一天中的时间
TIMESTAMP：DATE + TIME
INTERVAL：一段时间

SQL 允许对上述日期时间类型进行算术运算和比较运算

一个 DATE/TIME/TIMESTAMP 的值减去另一个 DATE/TIME/TIMESTAMP 值，得到一个 INTERVAL 类型的值
INTERVAL 类型的值可以加 DATE/TIME/TIMESTAMP 类型的值上

大对象类型

大对象（照片, 视频等）被存储为 large object（最大 4 GB）

BLOB：二进制大对象。对象是非解释性的二进制数据的集合（对象的解释由数据库系统外的应用来完成）
CLOB：字符大对象。对象是字符数据的集合

当一个 SQL 查询返回一个大对象时，往往是返回一个“定位器”，而不是大对象本身，然后利用“定位器”逐步取出该对象，定位器可理解为 HANDLE

用户自定义类型

CREATE TYPE 创建用户自定义类型

CREATE TYPE Dollars AS NUMBERIC(12, 2);

类似于 typedef

DROP TYPE：删除
ALTER TYPE：修改

用户自定义域类型

CREATE DOMAIN：创建用户自定义域类型

CREATE DOMAIN person_name CHAR(20) NOT NULL;

用户自定义域和用户自定义类型有两大区别：

域可以有约束或默认值
- NOT NULL / CHECK / DEFAULT
域不是强类型
- 基本类型相容的一个域类型的值可以被赋给另一个域类型

类型转换

`CAST()`

CAST(e AS t)：将表达式 $e$ 转换为类型 $t$

SELECT CAST(ID AS NUMBERIC(5)) AS inst_id
FROM instructor
ORDER BY inst_id;

`COALESCE()`

COALESCE() 解决输出空值的情况，该函数接收任意数量的参数（所有参数必须是相同类型），并返回第一个非空参数

SELECT ID, COALESCE(salary, 0) AS salary
FROM instructor;

查询表

查询所有表名

SHOW TABLES;

查询表结构

DESC <table_name>;

查询建表语句

SHOW CREATE TABLE <table_name>;

创建表

CREATE TABLE <table_name> (
	field1 TYPE1 [CONSTRAINT] [COMMENT 'field1_comment'],
	field2 TYPE2 [CONSTRAINT] [COMMENT 'field2_comment']
) [COMMENT 'table_comment'];

注：最后一个字段后面没有逗号

创建用户表

CREATE TABLE tb_user(
	id INt COMMENT '编号',
	name VARCHAR(50) COMMENT '姓名',
	age INt COMMENT '年龄',
	gender VARCHAR(1) COMMENT '性别'
	) COMMENT '用户表';

创建模式相同的表

创建一张和 student 相同模式的表

CREATE TABLE student_copy LIKE student;

默认值

创建学生表

CREATE TABLE student (
	ID VARCHAR(5),
	name VARCHAR(20) NOT NULL,
	dept_name VARCHAR(20),
	tot_cred NUMBERIC(3,0) DEFAULT 0,
	PRIMARY KEY (ID),
	FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);

调整表

修改表名

ALTER TABLE <old_table_name> RENAME TO <new_table_name>;

添加字段

ALTER TABLE <table_name> ADD <field_name> <TYPE> [CONSTRAINT] [COMMENT 'comment'];

修改字段类型

ALTER TABLE <table_name> MODIFY <field_name> <TYPE>;

修改字段数据类型和字段名

ALTER TABLE <table_name> CHANGE <old_field_name> <new_field_name> <TYPE> [CONSTRAINT] [COMMENT 'comment'];

删除字段

ALTER TABLE <table_name> DROP <field_name>;

删除表

DROP TABLE [IF EXISTS] <table_name>;

在删除表时，表中的数据也会被全部清除

清空内容

DELETE FROM <table_name>;

实际上是 DML

删除并重建表

TRUNCATE TABLE <table_name>;

数据查询 DQL

对数据库中表的数据记录进行查询操作

基础

书写格式

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数
;

SQL 语句不区分大小写

执行顺序

根据 FROM 子句计算出一个关系；
应用 WHERE 子句中的谓词，在 WHERE 中不允许使用 SELECT 的别名；
满足 WHERE 谓词的元组通过 GROUP BY 子句形成分组；
HAVING 子句若存在，就将其作用于每一分组。不符合 HAVING 子句谓词的分组将被抛弃，但是在 HAVING 中允许使用 SELECT 的别名
剩余的分组被 SELECT 子句用来应用聚集函数产生查询结果元组。

`SELECT`

选择子句列出的是查询语句需要的属性，对应关系代数中的投影操作

可以包含算术表达式，算术表达式中可以有 +/ - / * / / 运算符和对常量和属性的操作

`DISTINCT`

SQL 在查询结果和关系中默认允许重复，可以加上关键字 DISTINCT 进行去重

例：

SELECT DISTINCT name FROM student;

DISTINCT 可以后接多个属性，表示选出在多个属性上都不重复的元组

`ALL`

使用 ALL 显示指定不去重（一般的结果都是不去重的）

SELECT ALL name FROM student;

`*`

表示所有属性

SELECT * FROM student;

`WHERE`

WHERE 子句表示结果必须满足的限定条件，对应关系代数的选择操作（元组的选择）

WHERE 子句中可以包含逻辑运算符 AND, OR,和 NOT
逻辑运算符的运算对象可以是包含比较运算符 > , >= , <，<= ，= 和 <> 的表达式
- 也可以使用 BETWEEN AND / BEFORE / AFTER 等
允许使用比较运算符来比较字符串、算术表达式以及日期类型等

`FROM`

FROM 分句列出了查询中用到的关系，对应关系代数中笛卡尔积操作

SELECT * FROM student, teacher;

生成每一个可能 student-teacher 对, 所有属性来自两个表
如果多关系中存在相同属性，则在 SELECT、WHERE 子句中须作区分，如：student.id 和 teacher.id

`JOIN`

自然连接

自然连接会匹配两个关系中所有共同属性的相同值的元组, 去掉重复属性列
自然连接结果＝共同属性＋第一个关系属性＋第二个关系属性

例：

SELECT name, course_id
FROM instructor, teaches
WHERE instructor.ID = teaches.ID;
-- 两者等价
SELECT name, course_id
FROM instructor
NATURAL JOIN teaches;

但是需要注意，需要保证两个表中同名的字段表示的是同一个属性，否则会产生歧义（即谨防无关的属性具有相同的名字）

`AS`

SQL 允许对关系和属性进行更名操作，使用 AS 子句，AS 可以省略

SELECT name AS n FROM student;

SELECT name n FROM student;

字符串操作

LIKE 运算符可实现模式匹配

%：匹配任意（0 或多个）子字符串
_：匹配任意一个字符
SQL 字符串用单引号；关系代数字符串用双引号
匹配模式是大小写敏感的（SQL 标准）；但部分数据库不区分字符串大小写，如 MySQL、SQL Server
当匹配模式中含有特殊字符（如％ 、_、 \）时，须使用转义字符

实例：

intro% 匹配任意以“intro”开头的字符串
%Comp%匹配任意包含“Comp”子串的字符串
_ _ _ 匹配只含三个字符的字符串（实际上之间没有空格，这里为了展示效果插入了空格）
_ _ _ % 匹配至少含三个字符的字符串（空格同上）

字符串操作函数

串联（使用||）
大小写转换（LOWER()，UPPER()）
字符串长度（LENGTH()），提取子串（SUBSTR()）

`ORDER BY`

默认使用升序

ORDER BY name ASC	-- 升序
ORDER BY name DESC	-- 降序
-- 可以在多个属性上排序
ORDER BY name ASC, age DESC

集合运算

以下操作自动消除冗余，如果要保留冗余需要添加 ALL，如 UNION ALL / INTERSECT ALL / EXCEPT ALL

`uniON`

找出在 2009 年秋季开课，或者在 2010 年春季开课或两个学期都开课的所有课程 id 号

(SELECT course_id FROM section WHERE sem = 'fall' AND year = 2009)
UNION
(SELECT course_id FROM section WHERE sem ='spring' AND year = 2010)

`INTERSECT`

找出在 2009 年秋季和 2010 年春季同时开课的所有课程 id 号

(SELECT course_id FROM section WHERE sem ='fall' AND year = 2009)
INTERSECT
(SELECT course_id FROM section WHERE sem ='spring' AND year = 2010)

`EXCEPT`

找出在 2009 年秋季学期开课但不在 2010 年春季学期开课的所有课程 id 号

(SELECT course_id FROM section WHERE sem ='fall' AND year = 2009)
EXCEPT
(SELECT course_id FROM section WHERE sem ='spring' AND year = 2010)

空值

属性值可以被置为空值，以 NULL 表示
空值表示一个未知值或者该值不存在
所有涉及到空的算术表达式的结果为 NULL
- 例: 5 + NULL 返回 NULL
使用 NULL 的谓词可以用来测试空值（IS NULL, IS NOT NULL）
- 涉及空值的任何比较运算的结果返回 unknown（MySQL 中作为 NULL 处理）

三值逻辑

三值逻辑可以处理 unknown：

OR
- (unknown OR true) = true
- (unknown OR false) = unknown
- (unknown OR unknown) = unknown
AND
- (true AND unknown) = unknown
- (false AND unknown) = false
- (unknown AND unknown) = unknown
NOT
- (NOT unknown) = unknown

如果谓词 P 等于 unknown，则 P IS unknown 为真

如果 WHERE 子句的谓词结果为 unknown，可当做 false 来处理

空值相同

如果元组在所有属性上取值相等，那么它们就被当作是相同元组，即使某些值为空
- 如：{('A',NULL)} 和{('A',NULL)}
在去除重复元组时，只包留上述元组的一个拷贝
但是使用 = 判断结果为 unknown
- 即： {('A',NULL)} = {('A',NULL)} 为 unknown

也就是 DISTINCT 和谓词中对待 NULL 的逻辑不同

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入、返回单个值的函数

AVG()：平均值，输入必须是数字集
MIN()：最小值，返回标量（单列单行）
MAX()：最大值，返回标量（单列单行）
SUM()：总和，输入必须是数字集
COUNT()：计数

`GROUP BY`

在 GROUP BY 子句中所有属性相同的元组被分在一组

每个系的平均工资

SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name;

以下两种说法等价：

在 SELECT 子句中出现、但没有出现在 GROUP BY 子句中的属性，只能出现在聚集函数的内部（如 SUM()、COUNT()、AVG() 等）
出现在 SELECT 中，但不在聚集函数内部的属性，必须出现在 GROUP BY 中

聚合函数（如 SUM, AVG, COUNT）必须作用于“组”：

若显式写 GROUP BY col → 按 col 分多组；
若未写 GROUP BY → 隐式将全表视为一个组（全局组）。

WHERE 不能直接使用聚合函数

原因：WHERE 在分组和聚合之前执行。
正确做法：
- 用 HAVING 过滤聚合结果（在 GROUP BY 后）；
- 或用子查询在 WHERE 中间接引用聚合值。

-- 示例：工资高于平均值
SELECT * FROM teacher
WHERE salary > (SELECT AVG(salary) FROM teacher);

SELECT 中混用普通列与聚合函数？

不行！除非普通列出现在 GROUP BY 中。

-- 错误
SELECT name, SUM(salary) FROM teacher;
-- 正确
SELECT name, SUM(salary) FROM teacher GROUP BY name;

无 GROUP BY 的聚合查询是合法的

它是对整表（一个隐式组）做汇总，返回单行结果：

SELECT SUM(salary) FROM teacher;  -- 全局求和

`HAVING`

HAVING：分组限定条件
WHERE：元组限定条件

找出所有教师平均工资超过 42000 美元的系的名字和平均工资

SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;

HAVING 子句中的谓词在形成分组之后才起作用，因此可以使用聚集函数
与 SELECT 子句类似，任何出现在 HAVING 子句中但没有被聚集的属性，必须出现在 GROUP BY 子句中

空值

聚集函数根据以下原则处理空值：

除了 COUNT(*) 之外，所有的聚集函数都忽略输入集合中的空值
如果聚集函数输入集合只有空值（即空集）
- COUNT 函数运算返回 0
- 其他聚集函数都返回 NULL

嵌套子查询

子查询是嵌套在另一个查询中的 SELECT-FROM-WHERE 表达式
通常用于对集合的成员资格（是否在集合中）、集合的比较以及集合的基数进行检查
- 集合成员资格测试
- 集合的比较
- 空关系测试
- 重复元组存在性测试
- FROM 子句中的子查询
- WITH 子句
- 标量子查询

找出在 2009 年秋季和 2010 年春季学期同时开课的所有课程 id

子查询：找到 2010 年春季开课的课程
父查询：在子查询的结果中查询 2009 秋季开课的课程

SELECT DISTINCT course_id
FROM section
WHERE semester = 'fall' AND year = 2009 AND
	course_id IN (
		SELECT course_id
		FROM section
		WHERE semester = 'spring' AND year = 2010
	);

找出所有在 2009 年秋季学期开课但不在 2010 年春季学期开课的课程 id

SELECT DISTINCT course_id
FROM section
WHERE semester = 'fall' AND year = 2009 AND
	course_id NOT IN (
		SELECT course_id
		FROM section
		WHERE semester = 'spring' AND year = 2010
	);

多关系查询

找出（不同的）学生总数，他们选修了 ID 为 10101 的教师所讲授的课程段

SELECT COUNT(DISTINCT ID)
FROM takes
WHERE (course_id, sec_id, semester, year) IN
	(
		SELECT course_id, sec_id, semester, year
		FROM teaches
		WHERE teaches.ID = 10101
	);

`EXISTS` 空关系测试

用于测试一个子查询结果是否为空集（是否存在元组）

EXISTS 结构在作为参数的子查询非空时返回 true 值

若 EXISTS r 逻辑表达式为 true，则 $r\neq \emptyset$
若 NOT EXISTS r 逻辑表达式为 true，则 $r=\emptyset$

`FROM` 子查询

找出系平均工资超过$42,000 的那些系中教师的平均工资

SELECT dept_name, AVG_salary
FROM (
	SELECT dept_name, AVG(salary) AVG_salary
	FROM instructor
	GROUP BY dept_name
)
WHERE AVG_salary > 42000;

`LATERAL`

使得 FROM 子句中的子查询使用来自其他关系的相关变量（比如使用 EXISTS 就不需要写 LATERAL）

查询每位老师的姓名及其工资和所在系的平均工资

SELECT name, salary, AVG_salary
FROM instructor i1, LATERAL (
	SELECT AVG(salary) AVG_salary
	FROM instructor i2
	WHERE i2.dept_name = i1.dept_name
);

目前只有少数 SQL 实现支持 LATERAL 子句

标量子查询

标量子查询：该子查询返回包含单个属性的单个元组（COUNT、MAX）

标量子查询可以出现在 SELECT、WHERE、HAVING 子句中
如果子查询被执行后其结果中有不止一个元组，则产生一个运行错误

列出所有系名及其教师数

-- 将子查询放在SELECT中
SELECT dept_name,
	(SELECT COUNT(*)
	FROM instructor i
	WHERE d.dept_name = i.dept_name)
	) AS num_instructors
FROM department d;

列出薪水的 10 倍大于部门预算的所有老师名字

-- 将子查询放在WHERE中
SELECT name
FROM instructor i
WHERE salary * 10 > (
	SELECT budget
	FROM department d
	WHERE d.dept_name = i.dept_name
);

无 `FROM` 子句的标量

某些查询语句需要计算，无需引用任何关系

查询平均每位教师所讲授（无论是学年还是学期）的课程段数，其中由多位教师所讲授的课程段对每位教师计数一次

SELECT (
	SELECT COUNT(*) FROM teaches
) / (
	SELECT COUNT(*) FROM instructor
);

临时结果集（CTE）

WITH 语句用于定义一个临时的结果集（CTE），可以在后续的 SELECT、INSERT、UPDATE 或 DELETE 中引用

WITH cte_name AS (
    -- 查询语句
)
SELECT * FROM cte_name;

提高 SQL 可读性和模块化
支持递归（当 CTE 引用自身时）

数据增删改 DML

增

将一个新元组插入 course

-- 按照默认顺序
INSERT INTO course
VALUES ('A', 'NameA', 1);

-- 可以指定元素顺序
INSERT INTO course (course_id, title, credits)
VALUES ('A', 'NameA', 1);

-- 可以置空元素
INSERT INTO course
VALUES (NULL, 'NameA', 1);

可以将 SELECT 返回的元组作为插入参数

INSERT INTO student
	SELECT ID, name, dept_name, 0
	FROM instructor;

（可以在 SELECT 子句中加入标量）

会在执行插入前执行完毕 SELECT/FROM/WHERE，也就是可以复制自身

删

删除所有元组

DELETE FROM instructor;

删除 FINance 系的教师

DELETE FROM instructor WHERE dept_name = 'FINance';

删除平均工资低于大学平均工资的教师

DELETE FROM instructor
WHERE salary < (SELECT AVG(salary) FROM instructor);

注：删除元组时，平均工资会发生变化。所以会先计算并标记需要删除的元组，然后再统一删除

改

`CASE`

给工资超过$100,000 的教师涨 3%的工资,其余教师涨 5%

可以写两条 UPDATE 语句，但是需要注意执行顺序，否则有些数据会更新两次
也可以使用 CASE 语句进行条件更新

UPDATE instructor
SET salary = CASE
	WHEN salary <= 100000 THEN salary * 1.05
	ELSE salary * 1.03
	END

CASE 的一般格式如下：

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	ELSE result0
END

标量子查询的更新

为所有的学生重新计算并更新 tot_creds 值

UPDATE student s
SET tot_cred = (
	SELECT CASE
		WHEN SUM(credits) IS NOT NULL THEN SUM(credits)
		ELSE 0
	END
	FROM takes NATURAL JOIN course
	WHERE s.ID = takes.ID AND
		takes.grade <> 'F' AND
		takes.grade IS NOT NULL
);

连接表达式

连接条件

决定了两个关系中哪些属性相匹配，以及连接结果中是否出现重复属性

`JOIN`

连接操作 JOIN 以两个关系为输入，将另一个关系作为结果返回
一个连接操作是两个关系中的某些元组在符合某些条件下相匹配的笛卡尔积，同时还指定了连接结果中出现的属性有哪些
连接操作通常在 FROM 子句中使用

`USING`

可以指定依据哪一个属性作为匹配项
要求一定要有完全相同的列匹配，使用上有限制（相当于手动的自然连接）

SELECT *
FROM course
JOIN prereq	USING (course_id);

`JOIN ON`

在 ON 子句中指定连接条件，在 WHERE 子句中明确其它限定条件，使得 SQL 语句更加简洁易懂
ON 条件子句在外连接的表现和 WHERE 子句不同

SELECT *
FROM course
	JOIN prereq ON course.course_id = prereq.course_id;

-- 不使用JOIN的等价语句
SELECT *
FROM course, prereq
WHERE course.course_id = prereq.course_id;

自然连接

相当于让系统自动指定 JOIN ON 中 ON 的属性，选择相同的属性列进行连接

连接类型

决定了如何处理连接条件（属性）不匹配的元组

外连接

外连接（OUTER JOIN）是一种扩展的连接操作，可以避免连接操作结果信息的丢失
外连接过程：先执行连接操作，然后将两个关系中不匹配的元组都加入到最后的结果关系中，并使用 NULL 作为属性值补全，从而保留了在连接中丢失的元组

左外连接

确保一定包含左侧表元组，如果在右侧表中没有对应列，则使用 NULL 填充

SELECT *
FROM course
NATURAL LEFT OUTER JOIN prereq;

右外连接

确保一定包含右侧表元组，如果在左侧表中没有对应列，则使用 NULL 填充

SELECT *
FROM course
NATURAL RIGHT OUTER JOIN prereq;

全外连接

确保一定包含两侧表的所有元组，如果各个表在对面表中没有对应列，则使用 NULL 填充

内连接

内连接（INNER JOIN）：不保留未匹配元组的连接运算，连接操作默认为内连接

视图

在某些情况下，让所有用户看到数据库的整个逻辑模型（存储于数据库的所有关系模式）是不合适的
视图提供了向用户隐藏特定数据的机制
任何像这种不是逻辑模型的一部分，但作为“虚关系”对用户可见的关系称为视图

定义视图

使用 CREATE VIEW 创建视图
[(<column>)] 用于指定别名
<SELECT ...> 需要为有效的 SQL 表达式
WITH CHECK OPTION 用于指定视图的约束策略

CREATE VIEW <view_name> [(<column1>, <column2>, ...)]
AS <SELECT ...>
[WITH CHECK OPTION];

一旦定义了视图，就可以用视图名指代该视图生成的虚关系
视图的定义与通过查询表达式创建一个新关系是不同的
- 视图定义会导致一个表达式被存储，当使用这个视图时，查询过程中这个表达式将会被代入使用
创建视图时也可以使用其他视图（嵌套视图）

一个不显示教师薪水的视图

CREATE VIEW faculty AS
	SELECT ID, name, dept_name
	FROM instructor;

创建一个显示每个系薪水总和的视图，并使用别名

CREATE VIEW dept_total_salary (dept_name, total_salary) AS
	SELECT dept_name, SUM(salary)
	FROM instructor
	GROUP BY dept_name;

使用视图

将视图名当做普通表操作即可

查询不显示教师薪水的视图

SELECT name
FROM faculty
WHERE dept_name = 'Biology';

视图展开

视图展开（View expansion）是一种定义视图含义的方法，其中视图是用其他视图来定义的

一个视图可能被用到定义另一个视图的表达式中

如果视图 $v2$ 用于 $v1$ 的定义中，称 $v1$ 直接依赖 $v2$
如果 $v1$ 直接依赖 $v2$ 或者从 $v1$ 到 $v2$ 有一条依赖路径，称 $v1$ 依赖 $v2$
如果一个视图 $v$ 依赖其自身，称该视图 $v$ 是递归的

物化视图

物化视图（materialized view）：特定数据库系统允许视图关系被存储，并保证用于定义视图的实际关系改变，视图也跟着修改，这样的视图被称为物化视图

物化一个视图：创建一个物理表（关系），表中包含视图定义的查询结果中的所有元组
如果用于定义视图的实际关系改变，物化视图的结果也会过时
- 当实际关系更新时，需要更新视图。保持物化视图一直在最新状态的过程称为物化视图维护，也称视图维护
- 及时的视图维护 / 延迟的视图维护 / 周期性的视图维护
- 增量的视图维护

视图更新

视图关系更新

一般情况下，不允许对视图关系进行更新

数据更新

如果定义视图的查询语句对下列条件都能满足，称 SQL 视图是可（插入）更新的：

FROM 子句只有一个关系
SELECT 子句只包含关系的属性名，不包含任何表达式、聚集函数或 DISTINCT 声明
任何没有出现在 SELECT 子句中的属性内容可以取空值
查询中不包含 GROUP BY 或者 HAVING 子句

在之前定义的视图 faculty 中新增一个元组

INSERT INTO faculty
VALUES ('1024', 'musuyin', 'computer');

这个操作被描述为对关系 instructor 进行插入：

INSERT INTO instructor
VALUES ('1024', 'musuyIN', 'Computer', NULL);	-- 使用NULL填充了被隐藏的salary

更新约束

默认情况下，如果插入数据不会影响视图展示数据，也允许更新
如果使用 WITH CHECK OPTION，则会拒绝不满足视图 WHERE 条件的元组更新和插入

CREATE VIEW histORy_instructors AS
	SELECT *
	FROM instructor
	WHERE dept_name = 'HistORy'
	WITH CHECK OPTION;

此时如果插入/更新不满足视图 WHERE 的元组，则会失败
如果能插入/更新，要么是显式满足条件；要么是空置，自动补全 WHERE 指定的条件

UPDATE histORy_instructors SET salary = 80000
WHERE ID = '25566';

-- 会自动转换为
UPDATE instructors SET salary = 80000
WHERE ID = '25566' AND dept_name= 'HistORy';

INSERT INTO histORy_instructors (ID, name, salary)
VALUES ('69987', 'White', 80000);

-- 转换为
INSERT INTO instructors (ID, name, salary, dept_name)
VALUES ('69987', 'White', '80000', 'HistORy');

事务

事务（TransactiON）由查询和（或）更新语句的序列组成
事务的开始是隐式的，以 commit 或 rollback 结束一个事务
事务具有 ACID 特性（原子性、一致性、隔离性和持久性）

完整性约束

完整性约束防止的是对数据的意外破坏，它保证授权用户对数据库所做的修改不会破坏数据的一致性

约束类型

实体完整性
- 主键约束：PRIMARY KEY
- 不为空：NOT NULL
- 唯一约束：UNIQUE
参照完整性
- 外键约束：FOREIGN KEY
用户定义完整性
- 谓词：CHECK (P)

实体完整性

保证关系中的每个元组都是可识别的和唯一的

`PRIMARY KEY`

可以认为是 NOT NULL + UNIQUE，可以是组合

`NOT NULL`

属性值不可以为空

`UNIQUE`

UNIQUE(A1, A2, ...)：$A_1, A_2, \dots, A_m$ 形成一个超码

候选码允许为 NULL：NULL = NULL 为 unknown

参照完整性

保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现

假设关系 $r1$ 和 $r2$ 的属性集分别为 $R1$ 和 $R2$，$K1$ 和 $K2$ 分别为 $R1$ 和 $R2$ 的子集：

如果对 $r2$ 中任意元组 $t2$，均存在 $r1$ 中元组 $t1$ 使得 $t1.K1=t2.K2$，那么称关系 $r2$ 中的 $K2$ 属性集参照关系 $r1$ 中 $K1$ 属性集
若 $K1$ 是关系 $r1$ 的主码，那么称 $K2$ 为参照关系 $r1$ 中 $K1$ 的外码

级联操作

CREATE TABLE course (
	dept_name VARCHAR(20),
	FOREIGN KEY (dept_name) REFERENCES department
	ON DELETE CASCADE
	ON UPDATE CASCADE,
)；

CASCADE：删除 department 中的元组时，也会删除 course 中关联的元组
SET NULL：删除 department 中的元组时，将 course 中关联的元组的 dept_name 置空
SET DEFAULT：删除 department 中的元组时，将 course 中关联的元组的 dept_name 置为默认值

外码可以为 NULL，值为 NULL 的外码自动被认为满足约束

用户定义完整性

`CHECK`

CHECK(P)，使得关系中每个元组都必须满足谓词 $P$
- $P$ 可以是包括子查询在内的任意谓词，但实现开销较大

确保 semester 是 fall, winter, spring, SUMmer 中的一个

CREATE TABLE section (
	...
	semester VARCHAR(6);

	CHECK (semester IN ('fall', 'winter', 'spring', 'SUMmer'))
);

事务中对完整性约束的违反

在不违反完整性约束的情况下插入一个元组

CREATE TABLE person (
	ID CHAR(10),
	name CHAR(40),
	spouse CHAR(10),
	PRIMARY KEY (ID),
	FOREIGN KEY (spouse) REFERENCES person(ID)
);

此时想要插入元组，但是 spouse 字段对应的外键约束还没有创建，不能直接插入。解决方法：

先设为 NULL，插入配偶元组后再更新（但是当有 NOT NULL 约束时不可行）
推迟完整性约束检查到事务结束时进行

-- 在创建时声明
CREATE TABLE person (
	FOREIGN KEY (spouse) REFERENCES person(ID) INITIALLY DEFERRED;
);

-- 创建后设置
SET CONSTRAINTS <constraint_list> DEFERRED;

断言

断言就是一个谓词，它表达了希望数据库总能满足的一个条件
属性域约束和参照完整性约束是断言的特殊形式

CREATE ASSERTION <assertion_name> CHECK <predicate>;

对于 student 关系中的每个元组，它在属性 tot_cred 上的取值必须等于该生所成功修完课程的学分总和

CREATE ASSERTION credits_earned_CONSTRAINT CHECK (
		NOT EXISTS (
			SELECT ID FROM student
			WHERE tot_cred <> (
				SELECT SUM(credits)
				FROM takes
				NATURAL JOIN course
				WHERE student.ID = takes.ID
					AND grade is NOT NULL
					AND grade <> 'F'
			)
		)
	);

添加约束

ALTER TABLE <table_name> ADD <constraint>

为 Takes 表添加外键约束，确保 ID（学生编号）必须存于 Student 表中，并设置级联删除。

ALTER TABLE Takes
ADD CONSTRAINT fk_takes_student
FOREIGN KEY (ID) REFERENCES Student(ID)
ON DELETE CASCADE;

为 Course 表的 credits 列添加检查约束，确保学分 ≥1 且 ≤10。

ALTER TABLE Course
ADD CONSTRAINT ck_course_credits
CHECK (credits >= 1 AND credits <= 10);

授权

对数据库用户在某些数据上的权限形式：

SELECT：允许读取，但不允许修改数据
INSERT：允许插入新数据，但不允许修改已存在的数据
UPDATE：允许修改数据，但不允许删除数据
DELETE：允许删除数据

修改数据库模式的权限类型：

INDEX：允许创建和删除索引
RESOURCES：允许创建新的关系
ALTERATION：允许添加或删除关系中的属性
DROP：允许删除关系

最高权限：数据库管理员，类似于 Linux root 用户

授权规范

GRANT 用于授权

对视图的授权并不代表对视图相关的实际关系的授权
权限授予人必须已经具有对指定项目的权限（或者是数据库管理员）

GRANT <privilege_list>
ON <table_name | view_name>
TO <user | user_list>
[WITH GRANT OPTION];

<user | user_list> 包含：

用户 ID
public，现在和将来所有有效用户
角色

WITH GRANT OPTION：允许被授予权限的用户将该权限授予其他用户

权限

每种类型的授权都称为一个权限

SELECT：允许读取关系，或者使用视图完成查询的权限
INSERT：插入元组的权限，可指定属性列
UPDATE：使用 SQL UPDATE 语句更新的权限，可指定属性列
DELETE：删除元组的权限
ALL：允许所有权限

授权示例

GRANT SELECT ON student TO public;
-- 授予特定字段
GRANT UPDATE(salary) ON instructor TO U1, U2;
GRANT ALL ON instructor TO U3;

-- 允许U4可以将该权限授予其他用户
GRANT INSERT(ID) ON instructor TO U4 WITH GRANT OPTION;

权限转移

用户具有权限的充分必要条件是：当且仅当存在从授权图的根（即代表数据库管理员的顶点）到代表该用户顶点的路径

![[images/PASted image 20251123160609.png]]

权限收回

REVOKE 用于回收权限

REVOKE <privilege_list>
ON <table_name | view_name>
FROM <user | user_list>
[restrict | CASCADE];

<privilege_list>：可以是 ALL，表示收回的权限
<user | user_list>：可以是 public，除了隐含授权的用户，其他用户的权限都会被收回
默认级联收回权限（CASCADE），RESTRICT 可用于避免一些不合适的权限级联收回（部分数据库默认 RESTRICT）

实例

revoke UPDATE (budget) ON department FROM U1;

-- 收回分发的权利，但是没有收回本身的权利
revoke grant OPTION fOR SELECT ON department FROM U2;

如果某些权限被不同的授权者授予同一个用户两次，那么在一次权限回收后该用户可能仍保有这个权限
一个权限被回收后，基于这一权限的其他权限（如视图）也将被回收

![[images/PASted image 20251123162350.png]]

角色 Role

创建角色

CREATE ROLE rider;

授予用户角色

角色可以被授予给用户，同时也可以被授予给其他角色

GRANT rider to musuyin;	-- 授予 musuyin 骑士的角色！

-- 可以形成角色链
CREATE ROLE rider;
CREATE ROLE kamen_rider;
GRANT rider TO kamen_rider;
GRANT kamen_rider TO musuyin;

授予角色权限

权限可以被授予给角色

GRANT SELECT ON takes TO instructor;

视图授权

创建视图后，将视图名作为表名传入授权语句即可

视图创建者必须对原表具有 SELECT 权限，否则不能创建视图
被授予视图权限的成员，不具有对原表的权限

模式授权

SQL 标准为数据库模式指定了一种基本的授权机制：只有模式的拥有者才能够执行对模式的任何修改
SQL 提供了 REFERENCES 权限，允许用户在创建关系时声明外码

GRANT REFERENCES (dept_name) ON department TO A;

报表查询

GROUP BY () [WITH {CUBE | ROLLUP}]

`ROLLUP`

GROUP BY ROLLUP(A, B, C)
-- 旧式写法
GROUP BY (A, B, C) WITH ROLLUP

首先会对 $(A,B,C)$ 进行 GROUP BY，接着对 $(A,B)$ 进行 GROUP BY，然后对 $(A)$ 进行 GROUP BY，最后对全表进行 GROUP BY

ORDER BY 不能在 ROLLUP 中使用，两者为互斥关键字
如果分组中的列包含 NULL 值，ROLLUP 的结果可能不正确，原因在于 ROLLUP 进行分组统计时，NULL 具有特殊意义
- 因此在进行 ROLLUP 时可以先将 NULL 转换成一个不可能存在的值，或者没有特别含义的值，比如：IFNULL(xxx,0)

`CUBE`

GROUP BY CUBE(A, B, C)
-- 旧式写法
GROUP BY (A, B, C) WITH CUBE

首先会对 $(A,B,C)$ 进行 GROUP BY，接着分别对 $(A,B)$、$(A,C)$、$(B,C)$ 进行 GROUP BY，然后对 $(A)$、$(B)$、$(C)$ 进行 GROUP BY，最后对全表进行 GROUP BY

CUBE 在 ROLLUP 的基础上进一步从各种维度上给出细化的统计汇总结果

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一套 SQL 语句

存储过程（StORed Procedure）

是一组预编译的 SQL 语句集合。
可以接收输入参数（IN）、输出参数（OUT）或输入输出参数（INOUT）。
不能直接在 SELECT 语句中调用。
主要用于执行一系列操作（如增删改查、事务控制等）。

函数（FunctiON）

也是一种封装 SQL 逻辑的对象。
必须有返回值，且只能使用 IN 参数。
可以在 SQL 语句中直接调用（如 SELECT my_func()）。
通常用于计算并返回一个标量值。

子程序与当前数据库关联：

要明确地把子程序与给定数据库关联起来，在创建子程序时指定其名字为 db_name.sp_name
当一个子程序被调用时，一个隐含的 USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的 USE 语句是不允许的。
可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序
- 比如，要引用一个与 test 数据库关联的存储程序 $p$ 或函数 $f$，可以 CALL test.p() 或 test.f()
数据库移除的时候，与它关联的所有存储子程序也都被移除。

存储过程

通常存储过程有助于提高应用程序的性能
存储过程被创建、编译之后，就存储在数据库中
- 但是，MySQL 实现的存储过程略有不同，MySQL 存储过程按需编译。
存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只需发送存储过程的名称和参数
存储的程序对任何应用程序都是可重用的和透明的。
- 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
存储的程序是安全的
- 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限

创建存储过程

创建存储子程序需要 CREATE ROUTINE 权限
修改或移除存储子程序需要 ALTER ROUTINE 权限。这个权限自动授予子程序的创建者
执行子程序需要 EXECUTE 权限。这个权限自动授予子程序的创建者

存储过程不返回值，或者通过 OUT 标签返回；可以返回数据集

MySQL 默认分号 ; 是语句结束符，但存储过程内部也用分号，因此需临时修改分隔符（如 DELIMITER $$）

-- 由于存储过程中会使用到sql语句，需要以;结尾，为了避免结束符重复，所以需要先重命名结束符
DELIMITER $$

CREATE PROCEDURE proc_name(
    IN param1 INT,
    OUT param2 VARCHAR(50),
    INOUT param3 DECIMAL(10,2)
)
BEGIN
    -- 过程体
    DECLARE var1 INT DEFAULT 0;
    SET var1 = param1 + 10;
    SET param2 = CONCAT('Result: ', var1);
    SELECT num INTO param3 FROM t WHERE p = param1;	-- 使用INto保存结果
END$$

DELIMITER ;

使用存储过程

-- 调用存储过程
CALL proc_name(@in_val, @out_val, @inout_val);
-- 使用参数
SELECT @out_val, @inout_val;

删除存储过程

DROP PROCEDURE [IF EXISTS] pro_name;

查看存储过程

-- 查看指定名称的存储过程的创建语句
SHOW CREATE PROCEDURE <fc_name>;
-- 查看指定状态的存储过程
SHOW PROCEDURE STATUS [LIKE 'pattern'];

函数

创建函数

参数被认为是 IN 参数
函数必须有返回值，即函数体内必须包含一个 RETURN value 语句 R

DELIMITER $$

CREATE FUNCTION func_name(param1 INT)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE result INT;
    SET result = param1 * 2;
    RETURN result;
END$$

DELIMITER ;

特性

在创建函数时，必须指定其行为特性（MySQL 要求），用于优化和安全控制

特性	说明
`NO SQL`	函数不包含 SQL 语句
`READS SQL DATA`	包含读取数据的语句（如 `SELECT`）
`MODIFIES SQL DATA`	包含写入数据的语句（如 `INSERT`/`UPDATE`）
`CONTAINS SQL`	包含 SQL 但不读写数据（如 `SET`）
`DETERMINISTIC`	对相同输入总返回相同结果（如 `ABS(x)`）
`NOT DETERMINISTIC`	结果可能变化（如 `NOW()`）

过程体限制

不能直接返回结果集（虽然可以 SELECT，但客户端需处理多结果集）。
不能在函数中执行修改数据的操作，除非明确声明 MODIFIES SQL DATA（但即使如此，在 SELECT 中调用仍可能受限）。
不能在函数中使用动态 SQL（PREPARE/EXECUTE）（MySQL 限制）。
不能在函数中使用事务控制语句（如 COMMIT、ROLLBACK）。
递归调用深度有限制（默认 0，即禁止递归；可通过 max_sp_recursion_depth 设置）。

使用函数

-- 在SELECT子句中调用
SELECT func_name(1);

-- 调用后赋值
SET @x = func_name(10)

函数的创建需要指定返回值类型；同时应当在定义体中指明返回的结果（RETURN）
一般都是在 SELECT 中调用函数

删除函数

DROP FUNCTION [IF EXISTS] <fc_name>;

查看函数

-- 查看指定名称的函数的创建语句
SHOW CREATE FCUNTION <fc_name>;
-- 查看指定状态的函数
SHOW FUNCTION STATUS [LIKE 'pattern'];

主要区别

特性	存储过程	函数
返回值	无（但可通过 `OUT`/`INOUT` 参数返回）	必须有 `RETURN` 值
调用方式	`CALL` 语句	可在 `SELECT`、`WHERE` 等中直接使用
参数类型	支持 `IN`、`OUT`、`INOUT`	仅支持 `IN`
事务控制	可包含 `COMMIT`/`ROLLBACK`	通常不允许（受特性限制）
DML 操作	允许 `INSERT`/`UPDATE`/`DELETE`	受特性限制（如 `NO SQL` / `READS SQL DATA`）
使用场景	执行复杂业务逻辑、批量操作	计算并返回单一值

参数声明

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列 ()
每个参数默认都是一个 IN 参数。要指定为其它参数，可在参数名之前使用关键词 OUT 或 INOUT
- 指定参数为 IN, OUT, 或 INOUT 只对 procedure 是合法的

参数类型

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

全局变量

以 @ 开头，如 @var
生命周期为整个会话
不需要声明，直接赋值：SET @x = 10;

局部变量

使用 DECLARE 声明，必须在 BEGIN 后、其他语句前
作用域仅限当前块（BEGIN…END）
使用 SET 赋值
DEFAULT 子句指定默认值（常量或表达式），不指定则初始值为 NULL

DECLARE <var_name,...> <TYPE> [DEFAULT value];

DECLARE v_COUNT INT DEFAULT 0;
DECLARE v_name VARCHAR(100);

-- 也可以同时给多个变量赋值
SET v_COUNT = (SELECT COUNT(*) FROM users);

使用变量

使用 select INto

把选定的多个字段直接存储到变量
只有一条记录的字段可以被取回

SELECT <col_name> INTO <var_name> FROM <table_name> ...;

流程控制

分支处理

`IF`

一个 IF 需要一个 END IF 做对应

IF expression THEN
	statements;
END IF;

-- 有分支的选择语句
IF expression THEN
	statements;
ELSEIF elseif-expression THEN
	elseif-statements;
...
ELSE
	else-statements;
END IF;

`CASE`

即 switch，一个 CASE 需要一个 END CASE 对应

CASE case_expression
	WHEN expression_1 THEN commands_1
	WHEN expression_2 THEN commands_2
	...
	ELSE commands
END CASE;

当将单个表达式与唯一值的范围进行比较时，简单 CASE 语句比 IF 语句更易读。另外，简单 CASE 语句比 IF 语句更有效率
当根据多个值检查复杂表达式时，IF 语句更容易理解
如果选择使用 CASE 语句，则必须确保至少有一个 CASE 条件匹配。否则，需要定义一个错误处理程序来捕获错误。IF 语句则不需要处理错误

`WHILE`

一个 WHILE 需要一个 END WHILE 对应

WHILE expression DO
	statements
END WHILE;

`REPEAT`

也就是 do while，一个 REPEAT 需要一个 END REPEAT 对应

REPEAT
	statements;
UNTIL expression
END REPEAT;

`LEAVE`

LEAVE 语句用于立即退出循环，而无需等待检查条件。

即 break

`ITERATE`

ITERATE 语句允许跳过剩下的整个代码并开始新的迭代

即 continue

`LOOP`

即 while (true) 循环，需要显式使用 LEAVE 进行退出

[loop_label:] LOOP
    -- 循环体（SQL 语句）
    IF condition THEN
        LEAVE loop_label;  -- 退出循环
    END IF;

    -- 可选：ITERATE 用于跳过本次剩余代码，进入下一次循环
END LOOP [loop_label];

loop_label 是可选的标签（label），但强烈建议使用，尤其在嵌套循环中，便于明确控制流程
- 标签（label）不是必须的，但在嵌套循环中必须使用标签来指定退出哪一层
LEAVE label 相当于其他语言中的 break。
ITERATE label 相当于 continue，跳过当前循环剩余部分，直接进入下一轮

条件定义和处理

在数据库（以 MySQL 为例）的存储过程和函数中，定义条件（Condition）和处理程序（Handler） 是用于 异常处理（Exception Handling） 的核心机制。允许捕获运行时错误（如主键冲突、除零错误、表不存在等），并执行自定义逻辑（如记录日志、回滚事务、返回友好提示等），从而增强程序的健壮性。

条件（condition）

表示一个特定的错误或警告状态。
可以是 MySQL 内置的 SQLSTATE 值（如 '23000' 表示违反唯一约束），也可以是 MySQL 错误码（如 1062）。
可以为某个错误定义一个别名（命名条件），便于在处理程序中引用

处理程序（Handler）

当指定的条件被触发时，自动执行的一段代码
使用 DECLARE ... HANDLER FOR ... 语法声明
类型有三种：
- CONTINUE：执行完 handler 后，继续执行后续语句
- EXIT：执行完 handler 后，退出当前 BEGIN...END 块（不是整个过程！）
- UNDO：MySQL 不支持（仅在标准 SQL 或某些数据库如 IBM DB2 中存在）

创建

创建条件

DECLARE <condition_name> CONDITION FOR SQLSTATE 'SQLSTATE_value';
-- 或
DECLARE <condition_name> CONDITION FOR mysql_error_code;

创建处理程序

DECLARE <handler_action> HANDLER FOR condition_value[, condition_value]...
    statement;

handler_action：CONTINUE / EXIT
condition_value 可以是：
- SQLSTATE 'xxxxx'
- MySQL 错误码（如 1062）
- 命名条件（如 duplicate_entry）
- 关键字：SQLEXCEPTION（所有非 WARNING 错误）、SQLWARNING、NOT FOUND（如游标结束、SELECT 无结果）

常见内置关键字

这些关键字不需要提前 DECLARE CONDITION，可直接在 HANDLER 中使用

关键字	含义
`SQLEXCEPTION`	捕获所有 SQL 异常（`SQLSTATE` 以 ‘01’ 开头以外的错误）
`SQLWARNING`	捕获警告（`SQLSTATE` 以 ‘01’ 开头）
`NOT FOUND`	捕获“未找到”类错误（如游标 `FETCH` 到末尾、`SELECT INTO` 无结果）

完整示例

DELIMITER $$

CREATE PROCEDURE InsertUser(IN uid INT, IN uname VARCHAR(50))
BEGIN
    -- 声明变量
    DECLARE error_flag INT DEFAULT 0;

    -- 声明条件（可选）
    DECLARE duplicate_key CONDITION FOR 1062;

    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR duplicate_key
    BEGIN
        SET error_flag = 1;
        SELECT '主键冲突，请使用其他ID' AS message;
    END;

    -- 执行插入
    INSERT INTO users(id, name) VALUES (uid, uname);

    IF error_flag = 0 THEN
        SELECT '插入成功' AS message;
    END IF;
END$$

DELIMITER ;

注意事项

HANDLER 的作用域
- 在哪个 BEGIN...END 块中声明，就只对该块及其内部子块有效。
- 如果在一个块中没有匹配的 handler，会向上传递到外层块。
执行顺序
- 当错误发生时，MySQL 会查找最近作用域内匹配的 handler。
- 如果有多个 handler 匹配同一错误，更具体的优先（如错误码 > SQLEXCEPTION）。
不能捕获所有错误
- 一些致命错误（如语法错误、权限不足）在存储过程编译或调用初期就报错，不会进入过程体，因此无法被 handler 捕获。
ROLLBACK 需显式写
- MySQL 的 handler 不会自动回滚事务，需手动写 ROLLBACK。
避免无限循环
- 如果在 CONTINUE HANDLER 中又触发了相同错误，可能导致无限循环。

递归查询

对于递归查询来说，需要在语句中定义两部分：

基本语句
递归语句

WITH RECURSIVE cte_name AS (
    -- 1. 锚点成员（Anchor Member）：初始数据集，不引用自身
    SELECT ... FROM ... WHERE ...

    UNION ALL  -- 通常用 UNION ALL，避免去重开销

    -- 2. 递归成员（Recursive Member）：引用 cte_name 自身
    SELECT ... FROM cte_name JOIN ... ON ...
)
SELECT * FROM cte_name;

文件目录结构查询，输出文件夹的全路径

![[images/PASted image 20251124095231.png]]

WITH RECURSIVE full_path_table AS (
	(
	SELECT filesystem.dirname, filesystem.dirname AS parpath
	FROM filesystemm
	WHERE parname IS NULL
	)
	UNION ALL
	(
	SELECT filesystem.dirname, CONCAT(parpath, filesystem.dirname) AS parpath
	FROM filesystem, full_path_TABLE
	WHERE filesystem.parname = full_path_TABLE.dirname
	)
)
SELECT * FROM full_path_TABLE;

CONCAT：进行字符串拼接

游标

游标（Cursor） 是一种用于逐行处理查询结果集的机制。允许在存储过程或函数中像编程语言中的“迭代器”一样，一行一行地读取和操作数据

注意：能用集合操作就不要用游标！游标效率低、资源消耗大，应作为最后手段

工作流程

声明游标（DECLARE CURSOR）
打开游标（OPEN）
读取数据（FETCH）
关闭游标（CLOSE）

此外，还需配合 异常处理（Handler） 来检测是否读取完毕

为每个活跃用户增加 10 积分，并记录日志

DELIMITER $$

CREATE PROCEDURE UpdateUserpoints()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(50);

    -- 声明游标
    DECLARE user_cursor CURSOR FOR
        SELECT id, name FROM users WHERE active = 1;

    -- 声明结束处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN user_cursor;

    -- 循环处理
    process_users: LOOP
        FETCH user_cursor INTO user_id, user_name;
        IF done THEN
            LEAVE process_users;
        END IF;

        -- 更新积分
        UPDATE users SET points = points + 10 WHERE id = user_id;

        -- 记录日志
        INSERT INTO point_log(user_id, message)
        VALUES (user_id, CONCAT(user_name, ' added 10 points'));

    END LOOP;

    -- 关闭游标
    CLOSE user_cursor;
END$$

DELIMITER ;

游标类型

类型	说明	MySQL 支持？
只读游标（Read-Only）	不能通过游标修改数据	默认
可滚动游标（Scrollable）	可向前/向后移动（如 `FETCH PRIOR`）	不支持
敏感游标（Sensitive）	结果集随基表变化而动态更新	不支持
非敏感游标（Insensitive）	结果集是静态快照	不支持

MySQL 的游标是只读、不可滚动、非敏感的，只能从前向后顺序读取一次

触发器

触发器（Trigger）是数据库中一种特殊的存储程序，它在特定数据操作事件（如 INSERT、UPDATE、DELETE）发生时自动执行，无需显式调用。触发器常用于实现数据完整性约束、审计日志、业务规则自动化等场景

特性	说明
自动执行	由 DML 操作（`INSERT`/`UPDATE`/`DELETE`）触发，用户无法直接调用
与表绑定	必须定义在具体表上
事务内执行	触发器代码与触发它的操作在同一事务中，可回滚
无参数	不能接收外部输入参数
不可返回结果集	不能包含 `SELECT *` 等返回结果集的语句（MySQL 中会报错）

触发器类型

按触发时机分：

BEFORE：在操作执行前触发（可用于校验或修改新值）
AFTER：在操作执行后触发（常用于日志记录）

按操作类型分：

INSERT
UPDATE
DELETE

按触发粒度分：

行级触发器（Row-level）：每影响一行就触发一次（MySQL 只支持行级）
语句级触发器（Statement-level）：整个语句只触发一次（ORacle/PostgreSQL 支持，MySQL 不支持）

关键字

在触发器体中，可通过以下关键字访问旧值和新值：

操作	`OLD`	`NEW`
`INSERT`	不可用	新插入的行
`UPDATE`	修改前的值	修改后的值
`DELETE`	被删除的行	不可用

OLD 和 NEW 是伪记录，通过 OLD.column_name / NEW.column_name 访问字段

创建触发器

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
    -- 触发器逻辑
    -- 可使用 OLD.col, NEW.col
END$$

DELIMITER ;

自动更新“最后修改时间”（BEFORE UPDATE）

-- 假设 users 表有 last_modified 字段
DELIMITER $$

CREATE TRIGGER trg_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END$$

DELIMITER ;

在 BEFORE 触发程序中，AUTO_INCREMENT 字段的 NEW 值为 0，不是实际插入新纪录时自动生成的自增型字段值
在 AFTER 触发器中，不能对同一张表执行 INSERT / UPDATE / DELETE

数据库操作

数据库连接

启动、停止数据库

客户端连接

查询所有数据库

查询当前数据库

创建数据库

删除数据库

选择数据库

模式、目录、环境

数据定义语言 DDL

数据类型

数值类型

字符串类型

日期时间类型

大对象类型

用户自定义类型

用户自定义域类型

类型转换

CAST()

COALESCE()

查询表

查询所有表名

查询表结构

查询建表语句

创建表

创建模式相同的表

默认值

调整表

修改表名

添加字段

修改字段类型

修改字段数据类型和字段名

删除字段

删除表

删除表

清空内容

删除并重建表

数据查询 DQL

基础

书写格式

执行顺序

SELECT

DISTINCT

ALL

*

WHERE

FROM

JOIN

自然连接

AS

字符串操作

ORDER BY

集合运算

uniON

INTERSECT

EXCEPT

空值

三值逻辑

空值相同

聚集函数

GROUP BY

HAVING

空值

嵌套子查询

多关系查询

EXISTS 空关系测试

相关子查询

FROM 子查询

LATERAL

标量子查询

无 FROM 子句的标量

临时结果集（CTE）

数据增删改 DML

增

删

改

CASE

标量子查询的更新

连接表达式

`CAST()`

`COALESCE()`

`SELECT`

`DISTINCT`

`ALL`

`*`

`WHERE`

`FROM`

`JOIN`

`AS`

`ORDER BY`

`uniON`

`INTERSECT`

`EXCEPT`

`GROUP BY`

`HAVING`

`EXISTS` 空关系测试

`FROM` 子查询

`LATERAL`

无 `FROM` 子句的标量

`CASE`

`JOIN`

`USING`

`JOIN ON`

`PRIMARY KEY`

`NOT NULL`

`UNIQUE`

`CHECK`

`ROLLUP`

`CUBE`