SQL语言

📝 备注

SQL的功能远低于C语言和Java，为什么使用它？

关系代数的用途十分广泛。

SQL的编译十分便捷，易于优化。

SQL语句分类：

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用于定义数据库对象
DML	Data Manipulation Language	数据操作语言，用于数据库中数据的增删改
DQL	Data Query Language	数据查询语言，用于查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用于创建数据库用户、控制数据库访问权限

SQL的数据类型

数字类型	大小	描述
`TINYINT`	1 byte	小整数集
`SMALLINT`	2 bytes	大整数集
`MEDIUMINT`	3 bytes	大整数集
`INT`	4 bytes	大整数集
`BIGINT`	8 bytes	极大整数集
`FLOAT`	4 bytes	单精度浮点数集
`DOUBLE`	8 bytes	双精度浮点数集
`DECIMAL`	依赖精度值（总数位）和标度值（小数位）	精确定点数

字符串类型	大小	描述
`CHAR()`	0-255 bytes	定长字符串
`VARCHAR()`	0-65,535 bytes	变长字符串
`TINYBLOB`	0-255 bytes	二进制数据
`TINYTEXT`	0-255 bytes	短文本字符串
`BLOB`	0-65,535 bytes	二进制长文本数据
`TEXT`	0-65,535 bytes	长文本数据
`MEDIUMBLOB`	0-16,777,215 bytes	二进制中等长度文本数据
`MEDIUMTEXT`	0-16,777,215 bytes	中等长度文本数据
`LONGBLOB`	0-4,294,967,295 bytes	二进制极大文本数据
`LONGTEXT`	0-4,294,967,295 bytes	极大文本数据

日期类型	大小	格式	描述
`DATA`	3	`YYYY-MM-DD`	日期值
`TIME`	3	`HH:MM:SS`	时间值或持续时间
`TEAR`	1	`YYYY`	年份值
`DATETIME`	8	`YYYY-MM-DD HH:MM:SS`	混合日期和时间值
`TIMESTAMP`	4	`YYYY-MM-DD HH:MM:SS`	时间戳

特别地，空值NULL值出现在值本身未知、值不适用于目标对象、用作保留值等情况下。

对NULL进行算术操作，结果仍为NULL

对NULL进行逻辑操作，结果为UNKNOWN

空值NULL不是常量，不可将其用作操作数。

布尔类型	约定值
`TRUE`	1
`FALSE`	0
`UNKNOWN`	1/2

布尔操作的法则：

AND操作符返回两者约定值较小的那个。
OR操作符返回两者约定值较大的那个。
布尔变量v反转后为1-v。

例如，执行SQL语句：
1
SELECT * FROM Movies WHERE length<=120 OR length>120;
若某些元组的length值为NULL，条件的返回值则为UNKNOWN，将不会出现在结果中。

DDL

数据定义语言，用于定义数据库对象

查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


/* -- 数据库操作 -- */
-- 查询所有数据库
SHOW DATABASES;

-- 查询当前数据库
SELECT DATABASE();

-- 查询当前数据库中所有表
SHOW TABLE;


/* -- 表操作 -- */
-- 查询表结构
DESC 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

创建

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


/* -- 数据库操作 -- */
-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

-- 使用数据库
USE 数据库名;


/* -- 表操作 -- */
-- 创建表
CREATE TABLE 表名 (
    字段1 字段1类型 PRIMARY KEY, [COMMENT '这是主键'],
    字段2 字段2类型 [COMMENT '字段2注释'],
    字段3 字段3类型 [COMMENT '字段3注释'],
    ...
) [COMMENT '表注释'];

-- 示例
CREATE TABLE movie (
    name CHAR(30),
    address VARCHAR(255),
    cert INT PRIMARY KEY [COMMENT '主键'],
    networth INT
);

CREATE TABLE studio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presc INT,
    FOREIGN KEY (presc) REFERENCES movie(cert) [COMMENT '外键']
);

MySQL中建议使用字符集UTF8mb4。

参见：MySQL的数据类型

修改

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


--- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT '注释'] [约束];

--- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

--- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT '注释'] [约束];

--- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

删除

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


--- 删除字段
ALTER TABLE 表名 DROP 字段名;

-- 删除表
DROP TABLE [IF EXISTS] 表名;

-- 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

--- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

DML

数据操作语言，用于数据库中数据的增删改。

添加数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- 给指定字段添加数据
INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1,值2,...);

-- 给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2,...);

/* -- 批量添加数据 -- */
-- 指定字段名添加
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(...);

-- 全部添加
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

插入数据时，指定的字段顺序需要与值的顺序相对应。

字符串和日期型数据应包含在引号中。

插入数据的大小应该在字段的指定范围内。

修改数据

1
2
3


UPDATE 表名 SET 字段名1=值1,字段名2=值2,... [WHERE 条件];
-- 作用是修改满足条件的一行数据对应字段的值，不是修改字段名
-- 如果没有修改条件，则会修改整张表中所有数据

删除数据

1
2
3


DELETE FROM 表名 [WHERE 条件];
-- 如果没有修改条件，则会修改整张表中所有数据
-- DELETE语句不能删除某一个字段的值，可以使用UPDATE将该字段值设为NONE

DQL

数据查询语言，用于查询数据库中表的记录。它完整的参数可包含如下内容：

1
2


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

基本条件查询

1

SELECT L FROM R WHERE C [ORDER BY 排序规则];

它与关系代数 $\pi_{L}(\sigma_{C}(R))$ 对应。

特别的，查询语句：

1

SELECT 1 FROM R WHERE C;

表示在这里不关心返回的具体数值，只关心满足条件的行是否存在。

常用运算符总结：

运算符	功能	运算符	功能
>	大于	>=	大于等于
<	小于	<=	小于等于
=	等于	<> 或 !=	不等于
`IS NULL`	数据为空	`NOT` 或 `!`	逻辑非
`AND` 或 `&&`	逻辑与	`OR` 或 `\|\|`	逻辑或
`IN(...)`	在`in`之后的列表中的值（多选一）	`LIKE ' '`	模糊匹配
`BETWEEN...AND...`	在某个范围之间（含端点）

LIKE后可接一个通配符，用于模糊匹配字段：

"Star ____"将匹配字段中含有一个Star和4个字符的字符串。

%"s%将匹配包含's的字符串。

SQL允许使用ESCAPE命令来排除特定的字符，例如'x%%x%' ESCAPE 'x'将匹配以%开头和结尾的字符串。

查询结果可通过参数ORDER BY排序，默认为升序排列。

ORDER BY DESC指定为降序排列。
ORDER BY ASC为升序排列。

连接查询

FROM后可接多个表，例如

1

SELECT name FROM Movies, MovieExec WHERE title="Star Wars" AND producerC# = cert#;

其中title和producerC#位于表Movies中、cert#位于表MovieExec中，则查询结果将返回producerC#与cert#字段相同，且title为"Star Wars"的内容。

除了可以使用WHERE语句外，还可以使用INNER JOIN（INNER可省略）：

1
2
3
4


SELECT s.sno, s.sname, s.sdept
FROM 
    student AS s INNER JOIN sc ON s.sno = sc.sno 
    INNER JOIN course AS c ON sc.cno = c.cno;

使用INNER JOIN必须保证待连接的多个表具有相同的属性名。

反身查询

若要在同一张表中查询元组内部元素之间的关系，则需对该表设置两个副本再进行查询。

例如，查询哪两个Star有相同的address，则输入：

1
2
3


SELECT Star1.name, Star2.name 
FROM MovieStar Star1, MovieStar Star2
WHERE Star1.address = Star2.address AND Star1.name < Star2.name;

必须设置两个副本Star1和Star2，否则条件判断将始终为TRUE。

对应的关系代数为：

$$\pi_{A_1, A_5}(\sigma_{A_2 = A_6 \ \mathrm{AND}\ A_1 < A_5}(\rho_{M(A_1, A_2, A_3, A_4)}(\mathrm{MovieStar}) \times \rho_{N(A_5, A_6, A_7, A_8)}(\mathrm{MovieStar})))$$

子查询

查询也可成为其他查询的一部分，像这样嵌套在查询内部的查询称作子查询。

子查询可返回单个常量，用于WHERE语句；也可以返回一个关系；还可以在FROM语句中后接元组变量。

这里主要展示返回标量值的子查询。

标量值（Scalar）：元组的一个组成部分。例如元组 Movies(title, year, length, genre, studioName, producerC#)中，若使用SQL查询：
1
SELECT producerC# FROM Movies WHERE title='Star Wars';
返回值即为一个标量值。

示例：

1
2


SELECT name FROM MovieExec 
WHERE cert# = (SELECT producerC# FROM Movies WHERE title='Star Wars');

分组与聚合

聚合：将关系中的某些列合并。

聚合操作符	作用
`SUM`	对数值列求和
`AVG`	对数值列求平均值
`MIN`	数值列的最小值
`MAX`	数值列的最大值
`COUNT`	列中值的数量

分组：将元组的值分为若干组。GROUP BY后接一组分组属性，元组根据分组属性的值进行分组。

分组后筛选：HAVING后接关于组的条件。

使用分组后，不能直接用WHERE替代HAVING：

WHERE是在分组之前对单个行记录进行筛选。

HAVING是在分组之后对聚合结果（如 MAX(score)）进行筛选。

设关系StarsIn表示演员出演电影的情况，则若要查询出演过两部及以上电影的影星，则可以使用GROUP BY + HAVING：

1
2
3


SELECT starName FROM StarsIn
GROUP BY starName
HAVING COUNT(*) >= 2;

等价于下列的WHERE语句：

1
2
3
4


SELECT DISTINCT S1.starName FROM StarsIn S1, StarsIn S2
-- 同一个影星但电影不同
WHERE S1.starName = S2.starName           
    AND (S1.movieTitle <> S2.movieTitle OR S1.movieYear <> S2.movieYear);

存在查询

SQL语句中，EXISTS语句可用来判断是否存在满足特定条件的行。

有返回值（不管是什么）：TRUE
无返回值：FALSE

常与WHERE联合使用，作为查询条件：

1

WHERE EXISTS (SELECT 1 FROM R WHERE C)

实际上，
1
WHERE EXISTS (SELECT * FROM R WHERE C);
与上式的效果一致，但更推荐使用前者。