后端开发基础（四）- SQL

简介

SQL（Structured Query Language），结构化查询语言，用于在关系数据库数据库中存储和处理信息。关系数据库以表格形式存储信息，行和列分别表示不同的数据属性和数据值之间的各种关系。

分类

SQL 命令可以进行如下分类

DQL（Data Query Language，数据查询语言）

数据查询语言 (DQL) 由用于检索存储在关系数据库中的数据的指令组成。软件应用程序使用 SELECT 命令从 SQL 表中筛选并返回特定结果。

DML（Data Manipulation Language，数据操作语言）

数据操作语言 (DML) 语句用于在关系数据库中写入新信息或修改现有记录。例如，应用程序使用 INSERT 命令在数据库中存储新记录。

DDL（Data DefINition Language，数据定义语言）

数据定义语言 (DDL) 是指设计数据库结构的 SQL 命令。数据库工程师使用 DDL 根据业务需求创建和修改数据库对象。例如，数据库工程师可以使用 CREATE 命令创建数据库对象，如表、视图和索引。

TCL（Transaction Control Language，事物控制语言）

关系引擎使用事务控制语言 (TCL) 自动进行数据库更改。例如，数据库使用 ROLLBACK 命令撤消错误的事务。

DCL（Data Control Language，数据控制语言）

数据库管理员使用数据控制语言 (DCL) 来管理或授权其他用户的数据库访问权限。例如，数据库管理员可以使用 GRANT 命令来允许某些应用程序操作一个或多个表。

DQL

核心语句：

SELECT column1, column2, ...
FROM table_name
WHERE 条件
GROUP BY 分组列
HAVING 聚合条件
ORDER BY 排序列 ASC|DESC
LIMIT 数量;

SELECT 基本查询

SELECT * FROM students;
SELECT name, age FROM students;
SELECT name AS 姓名, age AS 年龄 FROM students;

• *：选择所有列
• 可用AS为字段取别名

DISTINCT 去重

SELECT DISTINCT city FROM customers;

去除重复值，只显示唯一记录

WHERE 条件筛选

SELECT * FROM products WHERE price > 100;
SELECT * FROM employees
WHERE department = 'HR' AND salary BETWEEN 4000 AND 7000;

常见运算符：

• 比较运算：=, <>, >, <, >=, <=
• 范围运算：BETWEEN x AND y
• 集合判断：IN (…)
• 模糊匹配：LIKE ‘a%’, LIKE ‘%b’, LIKE ‘_a%’
• 空值判断：IS NULL, is NOT NULL
• 逻辑连接：AND, OR, NOT

通配符一般和 LIKE 一起使用：

• %：代表零或多个字符
• _：代表单个字符
• [charlist]：代表在括号中的单个字符*
• [^charlist]：代表不在括号中的任何单一字符*
• [char-char]：代表在范围中的单个字符*
• {}：表示任何转义字符

*，不支持在 PostgreSQL 和 MySQL 数据库中。**，仅支持在 Oracle 数据库中。

ORDER BY 排序

SELECT name, age FROM students ORDER BY age DESC;

• 默认升序：ASC
• 降序：DESC

限制返回数量

不同数据库系统对这个功能的实现不同：

SQL Server / MS Access 使用 top：

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL 使用 LIMIT：

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number OFFSET number;

Oracle 12 使用 fetch first：

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

聚合函数

函数	说明
COUNT(*)	统计记录数量
SUM(column)	求和
AVG(column)	平均值
MAX(column)	最大值
MIN(column)	最小值

示例：

SELECT COUNT(*) FROM orders;
SELECT COUNT(DISTINCT city) FROM customers; -- 忽略重复项
SELECT AVG(score) FROM students WHERE class = '三班';

聚合函数忽略空值，除了 COUNT()。COUNT(*)不忽略空值，COUNT(column)忽略空值。

GROUP BY 分组聚合

配合聚合函数使用，将查询结果按一列或者多列的值分组。

示例：

SELECT gender, COUNT(*)
FROM emp_user
GROUP BY gender;

HAVING 条件过滤（用于聚合后的结果）

HAVING 用于组的过滤，WHERE 用于行的过滤。

示例：

SELECT gender, COUNT(*)
FROM emp_user
GROUP BY gender
HAVING COUNT(*) > 2;

JOIN 连接查询

多种连接查询

• INNER JOIN：内连接，取交集
• LEFT JOIN（LEFT OUTER JOIN）：左连接，取左集和交集
• RIGHT JOIN（RIGHT OUTER JOIN）：右连接，取右集和交集
• FULL JOIN（FULL OUTER JOIN）：全连接，取左右集和交集

有以下示例数据：

Product

ProductID	ProductName	CategoryID
1	A	1
2	B	1
3	C	2
4	D	4

Category

CategoryID	CategoryName
1	A
2	B
3	C

SELECT ProductID, ProductName, CategoryName
FROM Product
INNER JOIN Category ON Product.CategoryID = Category.CategoryID;

结果：

ProductID	ProductName	CategoryName
1	A	A
2	B	A
3	C	B

SELECT ProductID, ProductName, CategoryName
FROM Product
LEFT JOIN Category ON Product.CategoryID = Category.CategoryID;

结果：

ProductID	ProductName	CategoryName
1	A	A
2	B	A
3	C	B
4	D	NULL

SELECT ProductID, ProductName, CategoryName
FROM Product
RIGHT JOIN Category ON Product.CategoryID = Category.CategoryID;

结果：

ProductID	ProductName	CategoryName
1	A	A
2	B	A
3	C	B
NULL	NULL	C

SELECT ProductID, ProductName, CategoryName
FROM Product
FULL JOIN Category ON Product.CategoryID = Category.CategoryID;

结果：

ProductID	ProductName	CategoryName
1	A	A
2	B	A
3	C	B
4	D	NULL
NULL	NULL	C

还有一个比较特殊，SELF JOIN，子连接，用于将一张表和自身进行连接

语法

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

比如以下例子用于举出同一城市的客户之间的连接方式：

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

子查询

嵌套查询，可将内层查询（子查询）的结果用于外层查询，子查询可以在表中，以下运算符常用于连接子查询：

• IN：存在集合中为 TRUE
• EXISTS：如果子查询返回一个或多个，则为 TRUE，否则为 FALSE
• ALL：全部，比如>ALL()，大于所有子查询
• ANY：任何，比如=ANY()，等于任意子查询

集合操作

集合操作有以下几种：

• UNION：并
• INTERSECT：交
• EXCEPT：差

进行集合操作要求两个集合的列数相同，每列对应项的数据类型也要相同。

UNION 会默认选择不同的值，如果要选择重复的值使用 UNION ALL。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

CASE 表达式

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

示例：

SELECT name,
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS 等级
FROM students;

DML

INSERT 插入数据

插入完整一行

INSERT INTO students (id, name, age)
VALUES (1, 'Alice', 20);

插入部分列

INSERT INTO students (name, age)
VALUES ('Bob', 22);  -- id 会自动生成（若设置了自增）

一次插入多行

INSERT INTO students (name, age)
VALUES
  ('Tom', 21),
  ('Jerry', 19);

从查询插入（常用于表复制）

INSERT INTO graduated_students (name, age)
SELECT name, age FROM students WHERE age > 25;

UPDATE 更新数据

修改单列

UPDATE students
SET age = 21
WHERE name = 'Alice';

修改多列

UPDATE students
SET name = 'Tommy', age = 23
WHERE id = 2;

没有 WHERE 会全表更新

UPDATE students SET age = 18;  -- 所有学生年龄都变成18

DELETE 删除数据

删除指定记录

DELETE FROM students WHERE id = 3;

删除多条记录

DELETE FROM students WHERE age < 18;

删除所有记录

DELETE FROM students;

SELECT INTO 选择插入

将一个表中的数据复制到新表中。

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

使用AS可以给列重命名。
使用以下命名可以创建一个旧表结构的空表：

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

INSERT INTO SELECT

将一个表中的数据插入到另一个表中。

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

DDL

数据库

1. 创建数据库

CREATE DATABASE databasename;

2. 删除数据库

DROP DATABASE databasename;

3. 备份数据库

完整备份数据库

BACKUP DATABASE databasename
TO DISK = 'filepath';

只备份更改部分

BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

表

1. 创建表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

使用另一个表创建表

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

2. 删除表

DROP TABLE table_name;

3. 清空表

TRUNCATE TABLE table_name;

4. 修改表

添加列

ALTER TABLE table_name
ADD COLUMN column_name datatype;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

重命名列

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

修改列数据类型

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

数据类型

以 MYSQL 8.0 为例

字符串数据类型

数据类型	描述
CHAR(size)	定长字符串，以字符为单位，size 的范围为 0 到 255
VARCHAR(size)	变长字符串，以字符为单位，size 的范围为 0 到 65,535
BINARY(size)	定长二进制字符串，以字节为单位，等同于 CHAR()
VARBINARY(size)	变长二进制字符串，以字节为单位，等同于 VARCHAR()
TEXT(size)	字符串，单位是字符，size 的范围为 0 到 65,535
BLOB(size)	Binary Large Objects，二进制大对象，单位是字节，size 的范围为 0 到 65,535
TINYTEXT	最大为 255 个字符
TINYBLOB	最大为 255 个字节
MEDIUMTEXT	最大为 16,777,215 个字符
MEDIUMBLOB	最大为 16,777,215 个字节
LONGTEXT	最大为 4,294,967,295 个字符
LONGBLOB	最大为 4,294,967,295 个字节

数字数据类型

数据类型	描述
BIT(size)	位值整形，size 为 1 到 64
TINYINT	迷你整形，有符号范围为 -128 到 127，无符号范围为 0 到 255，
BOOL	布尔类型，0 为 false，非 0 为 true
BOOLEAN	同 BOOL
SMALLINT	小整形，有符号范围为 -32768 到 32767，无符号范围为 0 到 65535
MEDIUMINT	中整形 ，有符号范围为 -8388608 到 8388607，无符号范围为 0 到 16777215
INT	整形，有符号范围为 -2147483648 到 2147483647，无符号范围为 0 到 4294967295
INTEGER	同 INT
BIGINT	大整形 ，有符号范围为 -9223372036854775808 到 9223372036854775807，无符号范围为 0 到 18446744073709551615
FLOAT	单精度浮点，取值范围为(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
DOUBLE	双精度浮点，取值范围为 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL(size, d)	总位数由 size 指定，小数点后的位数由 d 参数指定，size 的最大位数为 65，d 的最大位数为 30，size 的默认值为 10，d 的默认值为 0
DEC(size, d)	同 DECIMAL

所有数字数据类型都可以有一个额外的选项：UNSIGNED 或 ZEROFILL。如果添加了 UNSIGNED 或者 ZEROFILL，MySQL 将不允许该列使用负值。

日期时间数据类型

数据类型	描述
DATE	日期，格式为 YYYY-MM-DD，范围为 1000-01-01 到 9999-12-31
DATETIME(fsp)	日期时间，格式为 YYYY-MM-DD hh:mm:ss，范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP(fsp)	Unix 时间戳，格式为 YYYY-MM-DD hh:mm:ss，范围为 1970-01-01 00:00:01 UTC 到 2038-01-09 03:14:07 UTC
TIME(fsp)	时间，格式为 hh:mm:ss，范围为 -838:59:59 到 838:59:59
YEAR	四字年份，范围为 1901 到 2155，以及 0000

约束

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
    constraint constraint_name1 constraint(column1, column2)
    constraint constraint_name2 constraint(column1, column2)
);

常用约束：

约束名称	描述
NOT NULL	非空，确保列不能具有 NULL 值
UNIQUE	唯一，确保列中的所有值都不同
PRIMARY KEY	主键，组合 NOT NULL 和 UNIQUE，表中数据的唯一标识，每张表只能有一个主键约束
FOREIGN KEY	外键，防止表之间的链接被破坏
CHECK	检查，确保列中的值满足特定条件
DEFAULT	默认，为列设置默认值
AUTO_INCREMENT	自增，为列设置自增的值

为已有列添加约束：

ALTER TABLE table_name
MODIFY COLUMN Age int NOT NULL;

为已有的表添加约束：

ALTER TABLE tabel_name
ADD CONSTRAINT constraint_name constraint(column1, column2);

为已有的表删除约束：

ALTER TABLE tabel_name
DROP CONSTRAINT constraint_name

索引

创建允许重复值的索引：

CREATE INDEX index_name
ON table_name (column1, column2, ...);

创建不允许重复值的索引：

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

删除索引

DROP INDEX index_name ON table_name;

视图

视图是基于 SQL 语句结果集的虚拟表，可以对基础的表进一步封装。

创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

修改视图

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

删除视图

DROP VIEW view_name;

对表中的数据进行增删改都会对原表的数据进行修改，如果视图是原表聚合后的结果则无法更改。

DCL

命令	含义	功能说明
GRANT	授权	赋予用户某些操作权限
REVOKE	撤权	撤销某用户的权限

GRANT 授权

1. 基本语法

GRANT 权限列表 ON 数据库对象 TO '用户名'@'主机' [WITH GRANT OPTION];

• 权限列表：如 SELECT, INSERT, UPDATE, DELETE, ALL
• ‘用户名‘@’主机’：表示具体用户，如 ‘tom‘@’localhost’
• WITH GRANT OPTION：允许该用户再授权给别人（谨慎使用）

2. 示例

-- 给用户 tom 授予对 test_db.students 表的查询权限
GRANT SELECT ON test_db.students TO 'tom'@'localhost';

-- 给用户 alice 所有权限
GRANT ALL PRIVILEGES ON test_db.* TO 'alice'@'%';

-- 允许 tom 再授权给别人
GRANT SELECT ON test_db.students TO 'tom'@'localhost' WITH GRANT OPTION;

REVOKE 撤权

1. 基本语法

REVOKE 权限列表 ON 数据库对象 FROM '用户名'@'主机';

2. 示例

-- 撤销 tom 对 students 表的查询权限
REVOKE SELECT ON test_db.students FROM 'tom'@'localhost';

-- 撤销 alice 的所有权限
REVOKE ALL PRIVILEGES ON test_db.* FROM 'alice'@'%';

权限种类

权限名称	说明
SELECT	查询权限
INSERT	插入记录权限
UPDATE	修改记录权限
DELETE	删除记录权限
CREATE	创建新表或数据库
DROP	删除表或数据库
ALTER	修改表结构
INDEX	创建/删除索引
ALL PRIVILEGES	所有权限

权限可授予在不同级别：全库级（db.*）、表级（db.table）、列级（db.table(column)）

用户与权限管理

1. 创建用户

CREATE USER 'tom'@'localhost' IDENTIFIED BY '123456';

2. 删除用户

DROP USER 'tom'@'localhost';

3. 查看权限

SHOW GRANTS FOR 'tom'@'localhost';

4. 查看所有用户

SELECT user, host FROM mysql.user;

TCL

TCL 语句	含义	作用
BEGIN / START TRANSACTION	开始一个事务	显式启动事务（MySQL 默认自动提交）
COMMIT	提交事务	所有更改永久保存
ROLLBACK	回滚事务	撤销自上次提交后的所有更改
SAVEPOINT	设置保存点	用于回滚到某个位置
ROLLBACK TO SAVEPOINT	回滚到保存点	撤销到指定保存点后的更改
SET AUTOCOMMIT	设置自动提交	控制是否自动提交事务

事务主要配合 UPDATE、INSERT、DELETE 等 DML 操作使用，DDL（如 CREATE/DROP）无法回，比如：

START TRANSACTION;  -- 开始事务

UPDATE users SET age = 25 WHERE id = 1;
SAVEPOINT sp1;  -- 保存点

UPDATE users SET age = 30 WHERE id = 2;
ROLLBACK TO sp1;  -- 只撤销第二次更新

COMMIT;  -- 提交事务

事务的四大特征（ACID）

特性	含义	说明
A - 原子性（Atomicity）	要么都执行，要么都不执行	一致完成或完全失败
C - 一致性（Consistency）	数据保持一致	操作前后都符合约束规则
I - 隔离性（Isolation）	多事务互不干扰	防止读/写冲突
D - 持久性（Durability）	提交后永久保存	即使断电也保留更改

隔离级别

并发事务会存在以下问题：

• 脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据）
• 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样
• 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同

SQL 标准定义了 4 种隔离级别

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	✅	✅	✅	最低级别，可能看到未提交数据
READ COMMITTED	❌	✅	✅	Oracle 默认级别
REPEATABLE READ	❌	❌	✅	MySQL 默认级别
SERIALIZABLE	❌	❌	❌	最严格，但效率最低

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;