SQL CRUD¶

Create, Read, Update, and Delete (CRUD) are the four basic operations of persistent storage.

CRUD and SQL

CRUD	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

CREATE¶

通用格式：

SQL
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = 
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...

假设我们已经创建了上述Students Courses Association表，那么我们便可以使用 INSERT INTO 来向这些空表中插入数据：

SQL
INSERT INTO Students VALUES (0, '2023000000', 'Alice', 'Computer Science');

也可以 指定字段名：

SQL
INSERT INTO Students (sid, name, major)
VALUES ('2023000002', 'Bob', 'EE');

没有指定值得字段将默认被分配 NULL 或 DEFAULT 值，如果字段是 NOT NULL 属性，则会引发错误。

也可以 一次性插入多条记录：

SQL
INSERT INTO Students (sid, name, major) VALUES
('2023000003', 'Carol', 'CS'),
('2023000004', 'Eve', 'CS');

SQL
mysql> DROP TABLE Association;
mysql> TRUNCATE TABLE Students; -- 清空 Students 数据表，重置自增变量
mysql> exit;

Note

在 MySQL 中，TRUNCATE TABLE Students; 的作用是清空 Students 数据表中的所有数据，同时重置表的自增变量（AUTO_INCREMENT）：

清空数据：TRUNCATE TABLE 会删除 Students 表中的所有记录，效果类似于 DELETE FROM Students;，但不同的是 TRUNCATE TABLE 是一种更高效的操作，因为它不会一条一条地删除数据，而是直接清空整个表。
重置自增变量：如果 Students 表中有使用自增（AUTO_INCREMENT）字段，比如主键 id，TRUNCATE TABLE 会将这个字段的计数器重置为初始值（通常是 1）。这样，插入新记录时，AUTO_INCREMENT 字段会从 1 开始重新计数。

需要注意的是，TRUNCATE TABLE 操作无法被撤销（不可逆），一旦执行，数据将无法恢复。因此，在执行此操作前需要谨慎。

SELECT¶

基础操作¶

暴力地取出整张表

SQL
SELECT * FROM Courses;

在生产环境中该操作应谨慎，因为若记录特别多或某个字段特别长，这将消耗大量的网络传输和内存消耗，且可能出现数据暴露等问题。

查看指定的列

SQL
SELECT id, title, credit FROM Courses;

WHERE¶

滤前聚合

我们可以使用 WHERE 进行筛选，例如查找所有开课时间为 2023-Spring 且学分大于 1 的课程：

SQL
SELECT * FROM Courses WHERE time = '2023-Spring' AND credit > 1;

IN

在使用 WHERE 进行过滤时我们可以指定 离散范围，如我们只想查询学分为 1 或 2 的课程：

SQL
SELECT * FROM Courses WHERE credit IN (1, 2);

IN的局限性

IN的查询是离散的，比如我想要查找学分在1～5之间的课程，只能写IN(1,2,3,4,5)，太麻烦！

如果你想查询学分在 1 到 10 之间的课程，可以使==用 BETWEEN 运算符来指定一个范围==，而不是使用 IN。BETWEEN 可以用于查询一段 连续的范围。下面是如何实现这一点的 SQL 语句：

SQL
SELECT * FROM Courses WHERE credit BETWEEN 1 AND 10;

这条语句将查询 Courses 表中所有学分 (credit) 在 1 到 10 之间的记录，包括 1 和 10。

LIKE

可以使用 LIKE 进行模糊匹配，例如查找所有上课时间在 2022 年的课程：

SQL
SELECT * FROM Courses WHERE time LIKE '2022%';

更多例子可参见 SQL LIKE Operator (w3schools.com)

NULL

由前所述，没有被分配值且没有默认值得字段在插入时会被分配 NULL 这个空值来表示记录中的某个字段没有数据，可以使用 ISNULL 来进行判断。如找出所有没有登记分数得选课记录：

SQL
SELECT * FROM Association WHERE ISNULL(grade);

注意不能使用 grade = NULL 来判断是否为 NULL：

SQL
mysql> SELECT * FROM Association WHERE grade = NULL;
Empty set (0.00 sec)

判断非空可以使用 NOT INSULL(<column_name>)，如：

SQL
SELECT * FROM Association WHERE NOT ISNULL(grade);

而不是

SQL
mysql> SELECT * FROM Association WHERE grade <> NULL;
Empty set (0.00 sec)

在SQL中，!= 的表示是 <>

Note

判断是空：ISNULL(<column_name>)
判断非空：NOT ISNULL(<column_name>)

COUNT, MAX, MIN, SUM, AVG¶

基础数学操作

可以查看 记录数：

SQL
SELECT COUNT(*) FROM Association;

类似的可以查看 最大值，最小值，合，平均值，例如计算所有课程得平均学分：

SQL
SELECT AVG(credit) FROM Courses;

GROUP BY¶

过滤聚合

我们想看看在所有课程中 不同的等级分别有多少人：

SQL
SELECT grade, COUNT(*) FROM Association GROUP BY grade;

在查询中，我们可以给字段、表、和查询赋予一个临时的，仅在这次查询中使用的名称。

例如对于上面的查询，我们给 COUNT(*) 一个 临时名称 student_count：

SQL
SELECT grade, COUNT(*) student_count FROM Association GROUP BY grade;

在 GROUP BY 时，你 通常不能选择没有受到 GROUP BY 修饰的字段，例如如下查询：

SQL
mysql> SELECT student_id, grade, COUNT(*) student_count FROM Association GROUP BY grade;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'summer.Association.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这是因为同一个 grade 可能包括多个 student_id，因此其定义不良好：

问题的本质

在 SQL 中，GROUP BY 是用来将查询结果按照某一列或多列的值进行分组，然后对每个分组执行聚合函数（例如 COUNT、SUM、AVG 等）。

例如：

SQL
SELECT grade, COUNT(*) 
FROM Association 
GROUP BY grade;

这个查询会按照 grade 列进行分组，然后对每个 grade 计算学生的数量。

为什么报错？

让我们看一下这个报错的查询：

SQL
SELECT student_id, grade, COUNT(*) 
FROM Association 
GROUP BY grade;

这个查询会报错，因为 student_id 字段没有用于 GROUP BY 中，也没有被任何聚合函数处理。

在分组操作中，SQL 必须明确地知道如何处理每一个字段。对于 grade 列，SQL 可以通过 GROUP BY grade 来理解，你希望按照 grade 分组。对于 COUNT(*)，SQL 也明白你希望对每个 grade 组的记录进行计数。

但是，对于 student_id，问题出现了：因为 student_id 并没有被聚合，也没有在 GROUP BY 中定义，那么 SQL 该如何确定它在每个分组中的值呢？

例如，假设有多个学生的 grade 都是 A，但他们的 student_id 不同。SQL 不知道你想选择哪个 student_id，因为多个 student_id 可能对应同一个 grade。因此，查询没有明确的意义。

举个具体的例子

假设 Association 表的数据如下：

student_id	course_id	grade
1	101	A
2	102	A
3	103	B
4	104	B
5	105	C

当你写 SELECT student_id, grade, COUNT(*) FROM Association GROUP BY grade; 时，你要求 SQL 按照 grade 来分组并统计每个 grade 的记录数。

但是每个 grade 组内有多个 student_id，例如 grade 为 A 的组里有 student_id 为 1 和 2 的记录。

SQL 不知道应该在这个分组结果中展示 student_id 为 1 还是 2，因为你没有指定任何逻辑告诉 SQL 如何选择。所以，这个查询无法确定 student_id 应该展示哪个值，从而导致了报错。

投机取巧的解决方式

为了让 SQL 明确地知道如何处理 student_id，你可以：

使用聚合函数: 如果你只关心任意一个 student_id，可以使用聚合函数如 MIN() 或 MAX() 来选择一个。例如：
SQL
1 2 3
SELECT MIN(student_id), grade, COUNT(*) FROM Association GROUP BY grade;
这里 MIN(student_id) 会选择每个 grade 组中的最小 student_id。
不在 SELECT 中选择未分组或未聚合的字段: 只选择 GROUP BY 中的列和聚合列，如：
SQL
1 2 3
SELECT grade, COUNT(*) FROM Association GROUP BY grade;
这样可以避免歧义。
使用子查询或 JOIN: 如果你需要更多字段，可以先聚合，再通过 JOIN 的方式获取额外的信息。

真正的解决方式

当我们希望选择其他列时，可以考虑使用 JOIN 选项（见后文）。

HAVING¶

滤后聚合

可能想要对 聚合算子的结果 进行过滤，比如试图选出大于 20 个人的等级：

SQL
mysql> SELECT grade, COUNT(*) FROM Association GROUP BY grade WHERE COUNT(*) > 20;
ERROR 1111 (HY000): Invalid use of group function

WHERE是不行的！它没法对聚合后的结果进行filter！

此时我们可以用 HAVING 来过滤聚合算子的结果：

SQL
SELECT grade, COUNT(*) FROM Association GROUP BY grade HAVING COUNT(*) > 20;

执行过程

分组: SQL 先根据 grade 对数据进行分组。
聚合: 然后计算每个 grade 组的学生数量 (COUNT(*))。
过滤: 最后，HAVING 子句对聚合结果进行过滤，只保留学生数量大于 20 的组。

WHERE 和 HAVING 的区别

WHERE 子句

作用范围: WHERE 子句用于在数据分组和聚合之前过滤表中的行。
限制: WHERE 不能用于过滤聚合函数的结果，因为在执行 WHERE 时，聚合操作尚未发生。

例如：

SQL
SELECT * FROM Association WHERE grade = 'A';
-- 这条语句在分组和聚合之前就过滤出了所有 grade 为 'A' 的行。

HAVING 子句

作用范围: HAVING 子句用于在数据分组和聚合之后过滤分组的结果。
功能: HAVING 允许你对聚合函数的结果进行过滤。

示例

假设你有如下的 Association 表：

grade	student_id
A	1
A	2
A	3
B	4
B	5
C	6

如果你想要统计每个 grade 的学生数量，并且只选择学生数量大于 2 的 grade，你不能使用 WHERE，因为 WHERE 子句无法访问聚合后的结果。

错误示例（使用 WHERE 过滤聚合结果）：

SQL
SELECT grade, COUNT(*) 
FROM Association 
GROUP BY grade 
WHERE COUNT(*) > 2;

这会导致 SQL 错误，因为 WHERE 子句是在聚合之前执行的，COUNT(*) 尚未计算出来。

正确示例（使用 HAVING 过滤聚合结果）：

SQL
SELECT grade, COUNT(*) AS student_count
FROM Association
GROUP BY grade
HAVING COUNT(*) > 2;

执行过程:

分组: SQL 先根据 grade 对数据进行分组。
聚合: 然后计算每个 grade 组的学生数量 (COUNT(*))。
过滤: 最后，HAVING 子句对聚合结果进行过滤，只保留学生数量大于 2 的组。

Warning

WHERE：在数据分组和聚合之前过滤表中的行
HAVING：在数据分组和聚合之后过滤表中的行

DISTINCT¶

去重

DISTINCT 的作用就是去除重复值，只保留唯一的值在查询结果中。如果你只关心有哪些不同的 grade 出现在表中，那么 DISTINCT 就非常有用。

SQL
SELECT DISTINCT grade FROM Association;

假设你有一个 Association 表，其中包含学生的成绩（grade）信息：

student_id	course_id	grade
1	101	A
2	102	B
3	103	A
4	104	C
5	105	B
6	106	A

现在，你想查询这个表中所有不同的 grade（等级）种类。你可以使用以下查询：

SQL
SELECT DISTINCT grade FROM Association;

这个查询会返回表中所有不同的 grade，并且每种 grade 只会出现一次：

grade
A
B
C

在这个示例中，尽管 grade 列中有多行记录是相同的（如 A 出现了三次，B 出现了两次），但 DISTINCT 关键字确保每种 grade 只在结果中出现一次。

使用场景

DISTINCT 通常用于你只想知道某个字段中存在哪些不同的值时。例如：

查询不同的课程类型：SELECT DISTINCT course_type FROM Courses;
查询不同的部门名称：SELECT DISTINCT department FROM Employees;

如果你不使用 DISTINCT，例如：

SQL
SELECT grade FROM Association;

那么你会得到如下结果，包含重复的 grade 值：

grade
A
B
A
C
B
A

这个查询会返回 grade 列中每一行的值，包括所有重复的 grade。

ORDER BY¶

排序

当我们希望对查询结果排序时，可以使用 ORDER BY，例如对课程的学分进行排序：

SQL
SELECT title, credit FROM Courses ORDER BY credit;

默认结果为升序。

我们也可以人为规定降序（DESC关键字）：

SQL
SELECT title, credit FROM Courses ORDER BY credit DESC;

使用 ORDER BY 进行升序排序

假设你有一个 Courses 表，其中包含课程的标题和学分信息：

course_id	title	credit
1	Mathematics	3
2	Computer Science	4
3	History	2
4	Physics	5
5	Chemistry	3

使用下面的查询（ORDER BY 默认升序）：

SQL
SELECT title, credit 
FROM Courses 
ORDER BY credit;

这个查询会返回按 credit 升序排列的课程列表：

title	credit
History	2
Mathematics	3
Chemistry	3
Computer Science	4
Physics	5

使用 ORDER BY 进行降序排序

如果你想按课程的学分从大到小排序，可以使用 DESC 关键字：

SQL
SELECT title, credit 
FROM Courses 
ORDER BY credit DESC;

这个查询会返回按 credit 降序排列的课程列表：

title	credit
Physics	5
Computer Science	4
Mathematics	3
Chemistry	3
History	2

我们也可以 对多个字段排序，如优先按照学分降序，再按照课程名升序：

SQL
SELECT title, credit FROM Courses ORDER BY credit DESC, title ASC;

也可以对 GROUP BY 的结果排序，例如按照获得某等级的学生个数排升序：

SQL
SELECT grade, COUNT(*) student_count FROM Association GROUP BY grade ORDER BY student_count;

Note

ORDER BY关键字默认升序排列，如果要降序排列，需要使用DESC关键字。

LIMIT¶

指定偏移与范围

SQL
LIMIT StartNum, NumCount; -- 从Start开始取，取Count个

LIMIT NumCount; -- <=> LIMIT 0, NumCount;

例子：具体索引

LIMIT 可以对结果指定偏移与范围，比如我们查找学号最小的 10 位学生：

SQL
SELECT sid, name FROM Students ORDER BY sid LIMIT 10;

原理：升序排列，自上而下越来越大，用LIMIT取前 10 个。

例子：长页字段

假如这样的查询是要分给一个后端，每页10个，我们想要去查第 4 页的结果：

SQL
SELECT sid, name FROM Students ORDER BY sid LIMIT 30, 10;

表示从第 30 位学生开始，查询 10 条记录。

SUB QUERY¶

tmp复用，嵌套查询

对于每次查询得到的结果集合，我们可以将其视为一个临时的数据表，可以（必须）对他起一个临时名称（别名）后继续进行 SELECT 等操作。而这样嵌套在查询中的查询称为 Sub Query，具有很强的表达能力，而且十分符合人类的思维直觉。

例如，我们希望查询 选课超过了平均值的学生的选课记录，可以将查询分为 3 步：

查询学生选课的数量：

SQL
SELECT COUNT(*) cnt FROM Association GROUP BY student_id;

对这些数量求平均：

SQL
SELECT AVG(q1.cnt) FROM (
    SELECT COUNT(*) cnt FROM Association GROUP BY student_id
) q1;

其中 q1 是对第一个子查询的别名

从 Association 表中筛选出相应的记录：

SQL
SELECT student_id, COUNT(*) cnt FROM Association GROUP BY student_id HAVING cnt > (
    SELECT AVG(q1.cnt) FROM (
        SELECT COUNT(*) cnt FROM Association GROUP BY student_id
    ) q1
);

子查询也可以用在 WHERE 语句中，例如希望查询选课数量排前 10 名学生的全部信息，可以将查询分为 3 步：

从 Association 表中查出选课数量排前 10 名的学生的 id：

SQL
SELECT student_id, COUNT(*) cnt
    FROM Association
    GROUP BY student_id
    ORDER BY cnt DESC
    LIMIT 10; -- DESC + LIMIT(10)

从中选出 id：

SQL
SELECT student_id FROM (
    SELECT student_id, COUNT(*) cnt
        FROM Association
        GROUP BY student_id
        ORDER BY cnt DESC
        LIMIT 10
) top_ten;

其中 top_ten 是对第一个子查询的别名

从 Students 表中查出这些同学的信息：

SQL
SELECT * FROM Students WHERE id in (
    SELECT top_s.student_id FROM (
        SELECT student_id, COUNT(*) cnt
            FROM Association
            GROUP BY student_id
            ORDER BY cnt DESC
            LIMIT 10
        ) top_ten
);

JOIN¶

Example¶

JOIN 是我们可以对多个表的结果按照某些列的约束关系进行拼接，常见的模式由：

alt text

例如，我们希望查看在选课记录中查看成绩的同时显示学生的名字：

SQL
SELECT Students.name, course_id, grade
    FROM Students
    INNER JOIN Association
    ON Students.id = student_id AND NOT ISNULL(grade);

其中 INNER JOIN 表示内连接，即只保留两个表中都存在的记录，ON 后面的条件表示两个表中的记录如何对应。

Tips

INNER JOIN 给出了两张表在指定字段交集上的笛卡尔积，用某些关键字将表衔接起来，你可以认为 INNER JOIN 等价于：

Python
results = []
for record1 in TABLE1:
    for record2 in TABLE2:
        if P(record1, record2):
            results += [record1.concat(record2)]
return result

也就是选出两张表笛卡尔积中符合谓词 P 的记录集合，数据库在执行时并不是这样实现的。

对于 INNER JOIN，我们可以认为其与 WHERE = 获得的结果一样，但实现原理却并不相同，JOIN 时使用 hashtable 进行比较，而 WHERE = 则是取笛卡尔积再过滤（即上述 Python 表示的代码）。因此前者效率为 O(\(logN\))，后者的效率为 O(\(N^2\))。

除了 INNER JOIN 外还有其他的 JOIN 方式，例如我们想看看每位同学都选了几门课，于是写了这样的查询：

SQL
SELECT Students.sid, COUNT(Association.student_id)
    FROM Students
    INNER JOIN Association
    ON Students.id = Association.student_id
    GROUP BY Students.id;

结果发现第 50 名同学选了 0 门课并没有返回。这是因为 INNER JOIN 只返回至少存在一个匹配的记录。此时我们可以使用 LEFT JOIN，即无论是否存在匹配，都显示左侧表中的全部记录。对于不存在对应右表记录的左表记录，填充 NULL：

SQL
SELECT Students.sid, COUNT(Association.student_id)
    FROM Students
    LEFT JOIN Association
    ON Students.id = Association.student_id
    GROUP BY Students.id;

这样就会显示全部学生。

JOIN details¶

在 SQL 中，JOIN 操作用于将两个或多个表中的数据结合在一起。不同的 JOIN 类型有不同的行为，决定了如何结合这些数据。这里是一些常用的 JOIN 类型：

INNER JOIN: 只返回两个表中匹配的记录。即，只有在两个表中都存在的记录才会出现在结果中。
LEFT JOIN (或 LEFT OUTER JOIN): 返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，则结果中的右表列会填充 NULL。
RIGHT JOIN (或 RIGHT OUTER JOIN): 返回右表中的所有记录，以及左表中匹配的记录。如果左表中没有匹配的记录，则结果中的左表列会填充 NULL。
FULL JOIN (或 FULL OUTER JOIN): 返回两个表中的所有记录，对于没有匹配的记录，结果中相应的表列会填充 NULL。

假设你有两个表：

Students 表记录了所有学生的信息：

id	sid
1	50
2	51
3	52

Association 表记录了学生和课程的关联：

student_id	course_id
1	101
1	102
2	103

INNER JOIN 示例

SQL
SELECT Students.sid, COUNT(Association.student_id) AS course_count
FROM Students
INNER JOIN Association
ON Students.id = Association.student_id
GROUP BY Students.id;

结果：

sid	course_count
50	2
51	1

这个查询只返回那些在 Association 表中有记录的学生。因为 INNER JOIN 只包含两个表中都有的记录，所以 sid 为 52 的学生被排除了（因为他没有选课记录）。

LEFT JOIN 示例

SQL
SELECT Students.sid, COUNT(Association.student_id) AS course_count
FROM Students
LEFT JOIN Association
ON Students.id = Association.student_id
GROUP BY Students.id;

结果：

sid	course_count
50	2
51	1
52	0

这个查询返回了所有学生，不管他们是否有选课记录。LEFT JOIN 确保了左表（Students）中的所有记录都会出现在结果中。如果右表（Association）中没有匹配记录，则相应的计数为 0。

Warning

注意区分谁是“左表”！！！

INNER JOIN: 只包括在两个表中都存在的记录。如果某个学生没有在 Association 表中有对应的记录（比如学生 ID 为 52 的学生），那么他不会出现在结果中。
LEFT JOIN: 包括左表中的所有记录。即使右表中没有匹配的记录，也会显示左表中的记录，并用 NULL 或默认值填充右表的列。在这里，COUNT(Association.student_id) 对于没有匹配的记录会是 0。

Functions¶

作为一门（编程）语言，MySQL 提供了极为丰富的函数库，下面举几个字符串函数的例子。

Tips

SUBSTRING: 用于从字符串中提取特定位置的子字符串。
CHAR_LENGTH: 用于计算字符串的长度。
REPLACE: 用于在字符串中替换某些字符或子字符串。

SUBSTRING

查看姓名从第 1 个字符开始的 3 个字符：

SQL
SELECT SUBSTRING(name, 1, 3) FROM Students;

CHAR_LENGTH

看看谁的名字比较长：

SQL
SELECT CHAR_LENGTH(name) name_len, name
    FROM Students
    ORDER BY name_len DESC
    LIMIT 10;

REPLACE

对字符串进行替换，比如我认为 Logic 是敏感词，在查询时将其替换为 **：

SQL
SELECT cid, REPLACE(title, 'Logic', '**') FROM Courses;

举个例子🌰

例子，假设 Students 表中包含以下数据：

id	name
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

你想查看每个学生名字从第 1 个字符开始的 3 个字符，可以使用以下查询：

SQL
SELECT id, name, SUBSTRING(name, 1, 3) AS short_name
FROM Students;

结果：

id	name	short_name
1	Alice	Ali
2	Bob	Bob
3	Charlie	Cha
4	David	Dav
5	Eve	Eve

假设你想找出名字最长的学生，并按照名字长度排序。可以使用以下查询：

SQL
SELECT id, name, CHAR_LENGTH(name) AS name_len
FROM Students
ORDER BY name_len DESC
LIMIT 10;

结果：

id	name	name_len
3	Charlie	7
4	David	5
1	Alice	5
2	Bob	3
5	Eve	3

假设 Courses 表中包含以下数据：

cid	title
101	Introduction to Logic
102	Advanced Logic
103	Discrete Mathematics
104	Computational Theory
105	Logic and Algorithms

你想将课程标题中的敏感词 "Logic" 替换为 "**"，可以使用以下查询：

SQL
SELECT cid, REPLACE(title, 'Logic', '**') AS sanitized_title
FROM Courses;

结果：

cid	sanitized_title
101	Introduction to **
102	Advanced **
103	Discrete Mathematics
104	Computational Theory
105	** and Algorithms

UPDATE¶

SQL
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

例如我要更新 History of Musical Theater 课程的学分：

SQL
UPDATE Courses SET credit = 3 WHERE title = 'History of Musical Theater';

UPDATE 过程中可以直接使用表中的值：

SQL
UPDATE Courses SET credit = credit + 1 WHERE title = 'History of Musical Theater';

DELETE¶

SQL
-- Single-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

-- Multiple-Table Syntax
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

删除 History of Musical Theater 课程的选课记录：

SQL
DELETE Association
    FROM Association, Courses
    WHERE Association.course_id = Courses.id AND Courses.title = 'History of Musical Theater';

在多表删除中，只有 DELETE 和 FROM 之间列举的表中的记录会被删除 (在这里就是Association)，FROM 后出现但是不在 DELETE 和 FROM 之间的表仅用作查询的参考。

值得注意的是，DELETE并不会立即释放磁盘空间：

SQL
SHOW TABLE STATUS LIKE 'Association';

其中：

Data_length 为数据文件的大小（以字节为单位）
Index_length 为索引文件的大小（以字节为单位）
Data_length + Index_length 表示表的总大小

我们删除一些数据：

SQL
DELETE FROM Association;

在此查询，发现即使 Association 空了，但是表的大小没有发生变化，为了释放空间，我们可以使用：

SQL
OPTIMIZE TABLE Association;
-- or
ANALYZE TABLE Association;

APPENDIX¶

`EXPLAIN ANALYZE`¶

你可能关心查询是如何被数据库执行的，这时就可以用 EXPLAIN ANALYZE，让 MySQL 打印执行计划，例如：

SQL
EXPLAIN ANALYZE
SELECT Students.sid, COUNT(Association.student_id)
    FROM Students
    LEFT JOIN Association
    ON Students.id = Association.student_id
    GROUP BY Students.id;

SQL Constraints¶

我们在前面看到的 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY 等均为约束，用于对字段指定数据规则。

UNIQUE 不只可以约束一列，也可以约束多列（联合唯一）；
DEFAULT 约束可以用于指定默认值，用于插入时没有指定值得情况；

让我们创建一个示例表，展示常见的 SQL 约束，如 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, 和 DEFAULT。这个表将包含学生信息和他们选修的课程，展示如何在表定义中使用这些约束。

1) 创建 Students 表

这个表包含学生的基本信息，我们将使用 PRIMARY KEY、UNIQUE 和 NOT NULL 约束。

SQL
CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 主键，自动递增
    sid INT NOT NULL UNIQUE,             -- 学生ID，必须唯一 (UNIQUE)且不能为空(NOT NULL)
    name VARCHAR(100) NOT NULL,          -- 学生姓名，不能为空 (NOT NULL)
    email VARCHAR(100) UNIQUE,           -- 邮箱，必须唯一 (UNIQUE)
    registration_date DATE DEFAULT CURRENT_DATE -- 注册日期，默认为当前日期 (DATA DEFAULT)
);

2) 创建 Courses 表

这个表包含课程信息，展示了 PRIMARY KEY 和 UNIQUE 约束的用法。

SQL
CREATE TABLE Courses (
    cid INT AUTO_INCREMENT PRIMARY KEY,  -- 课程ID，主键，自动递增
    title VARCHAR(100) NOT NULL UNIQUE,  -- 课程标题，必须唯一 (UNIQUE)且不能为空(NOT NULL)
    credit INT DEFAULT 3                 -- 学分，默认值为 3 (DEFAULT)
);

3) 创建 Association 表

这个表用来表示学生与课程的关联，展示了 FOREIGN KEY 和联合 UNIQUE 约束的用法。

SQL
CREATE TABLE Association (
    student_id INT NOT NULL,             -- 学生ID，不为空 (NOT NULL)
    course_id INT NOT NULL,              -- 课程ID，不为空 (NOT NULL)
    enrollment_date DATE DEFAULT CURRENT_DATE,  -- 选课日期，默认当前日期 (DATE DEFAULT)
    PRIMARY KEY (student_id, course_id), -- 联合主键，确保一个学生不能重复选同一门课
    FOREIGN KEY (student_id) REFERENCES Students(id),  -- 外键，引用 Students 表的 id 列
    FOREIGN KEY (course_id) REFERENCES Courses(cid)    -- 外键，引用 Courses 表的 cid 列
);

解释:

PRIMARY KEY:
- Students 表中的 id 列和 Courses 表中的 cid 列使用了 PRIMARY KEY 约束，确保每行都有一个唯一标识符。
- 在 Association 表中，我们使用 student_id 和 course_id 的组合作为联合主键，确保一个学生在 Association 表中不能重复选同一门课程。
UNIQUE:
- 在 Students 表中，sid 列和 email 列使用了 UNIQUE 约束，确保每个学生的学号和邮箱都是唯一的。
- Courses 表中的 title 列也有 UNIQUE 约束，确保每门课程的名称是唯一的。
NOT NULL:
- Students 表中的 sid 和 name 列，Courses 表中的 title 列，以及 Association 表中的 student_id 和 course_id 列都使用了 NOT NULL 约束，确保这些列不能存储空值。
DEFAULT:
- 在 Students 表中的 registration_date 列和 Courses 表中的 credit 列使用了 DEFAULT 约束。如果插入时没有指定值，系统会自动使用默认值。
FOREIGN KEY:
- 在 Association 表中，student_id 和 course_id 列分别作为外键，引用 Students 表中的 id 列和 Courses 表中的 cid 列。这确保了 Association 表中的学生和课程必须存在于各自的表中。

SQL CRUD¶

CREATE¶

SELECT¶

基础操作¶

WHERE¶

COUNT, MAX, MIN, SUM, AVG¶

GROUP BY¶

HAVING¶

DISTINCT¶

ORDER BY¶

LIMIT¶

SUB QUERY¶

JOIN¶

Example¶

JOIN details¶

Functions¶

UPDATE¶

DELETE¶

APPENDIX¶

EXPLAIN ANALYZE¶

SQL Constraints¶

`EXPLAIN ANALYZE`¶