MySQL part2——结构化查询语言SQL

结构化查询语言SQL

关系数据模型

关系模型三要素：

关系数据结构
完整性约束
关系操作

码：关系中某些属性集合具有区分不同元组的作用，称为码

超码：如果关系的某一组属性的值能唯一标识每个元组，则称该组属性为超码（super key）。
候选码：如果一个超码的任意真子集都不是超码，则称该超码为候选码。候选码是极小的超码。
主键：每个关系都有至少一个候选码，人为指定其中一个作为主键
外码：设 F 是关系 R 的属性子集。若 F 与关系 S 的主键 K 相对应，则称 F 是 R 的外键（foreign key）。

SQL分类

DDL：数据定义语言定义了不同的数据库，表，视图，索引等数据库对象还可以创建修改删除等操作数据库表的结构 CREATE ALTER DROP RENAME TRUMCATE

DML：数据操作语言增删改查数据库记录 INSERT DELETE UPDATE SELECT

DCL：数据控制语言定义数据库，表，字段，用户访问权限和安全级别的 COMMIT ROLLBACK SAVEPOINT

DQL：数据库查询语言查询数据库中表的记录

DTL：数据库事务语言

SQL基本语法

SQL以分号结尾，可以用空格和缩进，windows不区分大小写，linux下大小写敏感，建议关键字大写

注释 –单行注释– # 单行注释

/* 多行注释*/

字符串类型和日期类型的数据可以用 ’’ 表示

列的别名，使用双引号 ”“ 不建议省略as

书写规范：

数据库名，表名，表别名，字段名，字段别名等小写

SQL关键字，函数名，绑定变量等大写

不能以SQL关键字来命名，如果要用，需要加上‘’着重号标记

DDL-数据定义语言

数据定义语言定义了不同的数据库，表，视图，索引等数据库对象还可以创建修改删除等操作数据库表的结构

DDL-数据库操作

查询数据库
1
SHOW DATABASES;
查询当前数据库
1
SELECT DATABASE{}；

创建数据库

1	CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 指定使用的字符集] [COLLATE 指定排序规则]；

删除数据库

1	DROP DATABASE [IF EXISTS] 数据库名;

使用数据库
1
USE 数据库名；

修改数据库

1	ALTER DATABASE 数据库名修改部分;

表操作

DDL-表操作-查询

查询当前数据库所有表
1
SHOW TABLES
查询表结构

DESCRIBE 表名; 简写如下
1
DESC 表名;
SHOW CREATE TABLE 语句会返回创建表时所用的 SQL 语句
查询指定表的建表语句
1
SHOW CREATE TABLE 表名；

DDL-表操作-创建

CREATE TABLE 表名{
	字段1  字段1类型[COMMENT 字段1注释],
	字段2  字段5类型[COMMENT 字段2注释],
	字段3  字段3类型[COMMENT 字段3注释],
	......  
}[COMMENT 表注释];

快速复制表结构

-- 只复制结构不复制数据
CREATE TABLE new_employee LIKE employee;

-- 复制结构及数据
CREATE TABLE new_employee AS SELECT * FROM employee;

-- 复制部分数据
CREATE TABLE high_salary_emp AS 
SELECT * FROM employee WHERE salary > 10000;

DDL-表操作-修改

1	ALTER TABLE 表名 ADD\|DROP\|MODIFY\|CHANGE 字段名类型 (长度) [COMMENT 注释] [约束];

添加字段

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

案例：

emp表添加一个新的字段昵称为 nickname 类型 varchar(20)

1	ALTER TABLE emp ADD nickname VARCHAR(20) COMMENT "昵称";

修改数据类型

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

修改字段名和字段类型

1	ALTER TABLE 表名 CHANGE 旧字段名新字段名类型(长度) [COMMENT 注释] [约束];

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

修改表名

1	ALTER TABLE 表名 RENAME TO 新表名;

添加 / 删除主键

1 2	ALTER TABLE users ADD PRIMARY KEY (id); ALTER TABLE users DROP PRIMARY KEY;

DDL-表操作-删除

删除表
1
DROP TABLE [IF EXIST] 表名；
删除指定表，并重新创建
1
TRUNCATE TABLE 表名；

DML-数据操作语言

数据操作语言增删改查数据库记录

添加数据 INSERT
修改数据 UPDATE
删除数据 DELETE

DML-插入

给指定的字段添加数据

1	INSERT INTO (字段名1，字段名2) VALUES (值1，值2。。)

给全部字段添加数据

1	INSERT INTO 表名 VALUES (值1，值2。。)

批量添加数据

1
2
3

INSERT INTO 表名 (字段名1，字段名2。。。) VALUES (值1，值2...) (值1，值2...) (值1，值2...);

INSERT INTO 表名 VALUES (值1，值2...) (值1，值2...) (值1，值2...);

注意：

插入数据时，指定的字段顺序需要与值的顺序一一对应
字符串和日期的数据类型都应该包含在引号中
插入的数据大小，应该在这个字段的范围内

DML-修改

修改数据表中的字段值：

1	UPDATE 表名 SET 字段名1=值1，字段名2=值2..... [WHERE 条件]；

修改语句的条件可以有，如果没有就是针对整张表

DML-删除

删除数据表中的数据

1	DELETE FROM 表名 [WHERE 条件]；

DELETE的条件可以有，如果没有，就是删除整张表的数据
DELETE不能删除某一个字段的值，可以用UPDATE

DQL-数据查询语言

数据查询语言，查询数据库中表的记录

关键字 SELECT

DQL 语法

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

DQL-基础查询

SELECT 
	字段列表
FROM
	表名列表

查询多个字段

1	SELECT 字段1，子段2。。。 FROM 表名；

整表查询

1	SELECT * FROM 表名;

设置别名

1	SELECT 字段1 [AS 别名1]，字段2 [AS 别名2] ... FROM 表名；

as 列的别名可以用一对“”引起来

去除重复记录

1	SELECT DISTINCT 字段列表 FROM 表名；

查询常数

查询常数指的是在查询结果里返回固定值，而非从表字段获取数据

1	SELECT '这是一个常数' AS constant_value;

假定有一个 employees 表，包含 id 和 name 字段，你能够在查询结果里既显示表数据，又显示常数。

1	SELECT id, name, '固定部门' AS department FROM employeses;

此查询会返回 employees 表中的 id 和 name 字段，同时为每一行添加一个名为 department 的列，其值均为 固定部门。

DQL-条件查询

基本语句

1	SELECT 字段列表 FROM 表名 WHERE 条件列表;

比较运算符

等于（=）

不等于（<> 或！=）

大于（>）

小于（<）

大于等于（>=）

小于等于（<=）

BETWEEN…AND某个范围内 （BETWEEN之后跟最小值 AND之后跟着最大值）

IN 在IN之后的列表中的值，多选一

LIKE 模糊匹配（_匹配单个字符，%匹配多个字符）

-- 查询名字包含"张"的员工
SELECT * FROM employee WHERE name LIKE '%张%';

-- 查询名字以"张"开头且长度为3的员工
SELECT * FROM employee WHERE name LIKE '张__';

-- 查询手机号第二位是5的员工
SELECT * FROM employee WHERE phone LIKE '_5%';

IS NULL （NULL）

-- 查询奖金为NULL的员工
SELECT * FROM employee WHERE bonus IS NULL;

-- 查询奖金不为NULL的员工
SELECT * FROM employee WHERE bonus IS NOT NULL;

-- 使用IFNULL处理NULL值
SELECT name, salary, IFNULL(bonus, 0) AS total_income FROM employee;

逻辑运算符

AND 或 && 与

OR 或 || 或

NOT 或 ! 非

-- 查询年龄
SELECT name FROM employee WHERE age = 55;
SELECT * FROM employee WHERE age = 55;
-- 查询有身份证号的员工信息
SELECT * FROM employee WHERE idcard is not null;
-- 查询年龄不为55的员工
SELECT * FROM employee WHERE age != 55;
-- 模糊查询名字为两个字的员工信息
SELECT * FROM employee WHERE name LIKE '__';
-- 查询身份证号最后一位为8的
SELECT * FROM employee WHERE idcard LIKE '%8'

DQL-聚合函数

聚合函数：一列数据作为一个整体，纵向计算

常见聚合函数：

count 统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

聚合函数都是作用与表的某一列

1	SELECT 聚合函数(字段列表) FROM 表名；

null不参与聚合函数的计算

空值参与运算，结果一定为空

-- 查询总数据
SELECT COUNT(*) FROM employee;
-- 统计id字段的总数量
SELECT COUNT(id) FROM employee;
-- 统计企业员工的平均年龄
SELECT avg(age) FROM employee;
-- 统计企业员工的年龄和
SELECT SUM(age) FROM employee;

DQL-分组查询

关键字：GROUP BY

语句：

1	SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where与having区别

执行实际不同：where是分组前进行过滤，不满足where条件不参与分组；HAVING是分组后对结果过滤

判断条件不同：WHERE不能对聚合函数判断，HAVING可以

执行顺序 where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，其他字段无意义

1	SELECT name,gender,COUNT(*) FROM employee GROUP BY gender;

-- 根据性别分组统计男女员工数量
SELECT gender, COUNT(*) FROM employee GROUP BY gender;
-- 根据性别分组统计男女员工的平均年龄
SELECT employee.gender,AVG(employee.age) FROM itcast.employee GROUP BY gender;
-- 查询年龄小于45岁的员工，并根据WOKRNO分组
SELECT workno,COUNT(*) FROM employee WHERE age < 45 GROUP BY workno;
SELECT workno,COUNT(*) FROM employee WHERE age < 45 GROUP BY workno HAVING COUNT(*) >= 1;
SELECT workno,COUNT(*) worknum FROM employee WHERE age < 45 GROUP BY workno HAVING worknum >= 1;

DQL-排序查询

1	SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2...;

支持多字段排序

如果是多字段排序，当第一个字段值相同时候会按照第二个这样依次

排序方式

ASC 升序默认
DESC 降序

-- 排序查询
-- 根据年龄对公司的员工进行升序排序
SELECT * FROM employee ORDER BY age ASC;
-- 根据入职时间，对员工进行降序排序
SELECT name FROM employee ORDER BY employee.entrydate DESC;
-- 根据年龄对公司的员工进行升序排序，年龄相同，再根据入职时间排序
SELECT * FROM employee ORDER BY employee.entrydate DESC, entrydate ASC;

DQL-分页查询

1	SELECT 字段列表 FROM 表名 LIMIT 起始索引, 每页记录数;

注意：

起始索引从0开始，起始索引 = （查询页码-1）*每页显示记录数
分页查询是数据库的方言，不同的数据库之间不同，mysql中是LIMIT
如果查询的第一页数据，起始索引可以省略，直接简写

DQL-执行顺序

编写顺序

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

执行顺序

FROM 决定我要查询那一张表
WHERE 指定查询的条件
GROUP BY 和 HAVING 指定分组
SELECT 字段列表
ORDER BY 指定排序
LIMIT 指定分页

DCL-数据控制语言

主要用来管理数据库的用户，控制数据库的访问权限

DCL–管理用户

本质上是对user表的修改

主机名可以用%通配

查询用户

1 2	USE mysql； SELECT * FROM user;

主机地址和用户名才能定位一个用户，本质上是查询user表

创建用户

1	CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY '密码';

CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;

修改用户密码

1	ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

1	DROP USER ‘用户名’@‘主机名’;

DCL-权限控制

MySQL中定义了多种权限，常用的就这些

ALL, ALL PRIVILEGES 所有权限

SELECT 查询数据

INSERT 插入数据

UPDATE 修改数据

DELETE 删除数据

ALTER 修改表

DROP 删除数据库/表/视图

CREATE 创建数据库/表

查询权限

1	SHOW GRANTS FOR '用户名'@'主机名';

授予权限

1	GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

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

注意：

多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用*进行通配，代表所有

多表查询

多表关系

一对多（多对一）多的一方建立外键，指向一的一方的主键

多对多建立第三张表，中间表至少包含两个外键，分别关联两方主键

一对一拆分表在任意一方加入外键，管理另外一方的主键，并且设置为UNIQUE

内连接

查询表1，表2 交集部分数据

这种场景下得到的是满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

隐式内连接

1	SELECT 字段列表 FROM 表1，表2 WHERE 筛选条件;

显式内连接

1	SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 连接条件;

示例：

-- 题目 1：查询每个用户的角色ID列表
-- 实际场景：管理员需要查看每个用户关联的角色ID。
SELECT user.user_id, user_role.role_id FROM user
INNER JOIN user_role ON user.user_id = user_role.user_id;

-- 题目 2：查询每个角色的权限ID列表
-- 实际场景：系统需要列出每个角色关联的权限ID。
SELECT role.role_id, role_permission.permission_id FROM role
INNER JOIN role_permission ON role.role_id = role_permission.role_id;

外连接

左右外连接

左外连接

左外连接查询左表所有数据，以及两张表交集部分数据

左外连接相当于查询表A(左表)的所有数据和中间绿色的交集部分的数据。

表1的位置为左表，表2的位置为右表

SELECT 字段列表
FROM 表1
LEFT [OUTER] JOIN 表2
ON 条件;

右外连接

右外连接查询左表所有数据，以及两张表交集部分数据

右外连接相当于查询表B(右表)的所有数据和中间绿色的交集部分的数据。

表1的位置为左表，表2的位置为右表

SELECT 字段列表
FROM 表1
RIGHT [OUTER] JOIN 表2
ON 条件;

自连接

自连接是指将一张表与自身进行连接操作。通常用于处理表中存在层级关系或递归关系的数据。

在自连接中，我们需要为同一张表创建两个别名（如 p1 和 p2），然后通过某种条件将这两个别名关联起来。

自连接表一定要起别名

对于自连接查询，可以是内连接查询，也可以是外连接查询。

连接条件通常是父子关系字段

SELECT 字段列表
FROM 表A 别名A
JOIN 表B 别名B
ON 条件;

示例：

-- permission_id：权限的唯一标识。   parent_permission_id：当前权限的父权限 ID。
-- 找到每个权限的直接子权限
SELECT 
    p1.permission_id AS parent_permission_id, 
    p1.permission_name AS parent_permission_name, 
    p2.permission_id AS child_permission_id, 
    p2.permission_name AS child_permission_name
FROM 
    permission p1
LEFT JOIN   -- 确保即使某个权限没有子权限，父权限的信息仍然会被显示出来。
    permission p2 ON p1.permission_id = p2.parent_permission_id;

连接条件：对于 p1 中的每个权限（父权限），查找 p2 中所有 parent_permission_id 等于该权限 ID 的记录（子权限）

使用 INNER JOIN 时，只会返回有子权限的父权限。
使用 LEFT JOIN 时，会返回所有父权限，即使它们没有子权限。

-- 查询员工及其直接上级
SELECT e1.name AS employee, e2.name AS manager
FROM employee e1
LEFT JOIN employee e2 ON e1.manager_id = e2.emp_id;

-- 查询同一部门的员工对
SELECT a.name AS employee1, b.name AS employee2
FROM employee a
JOIN employee b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;

JOIN 和 ON

JOIN 是“连接”的意思，顾名思义，JOIN 子句用于将两个或者多个表联合起来进行查询。

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

1	SELECT c.name, o.name FROM customers c JOIN orders o ON c.cust_id == o.cust_id ORDER BY c.name;

当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名。

另外，如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON，改写上面的例子：

1	SELECT c.name, o.name FROM customers c JOIN orders o USING(cust_id) ORDER BY c.name;

ON 和 WHERE 的区别：

连接表时，SQL 会根据连接条件生成一张新的临时表。ON 就是连接条件，它决定临时表的生成。
WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

所以总结来说就是：SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选。

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，就是上面提到的多表查询

对了，如果不加任何修饰词，只写 JOIN，那么默认为 INNER JOIN，返回两张表字段共有的列

联合查询

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

1
2
3

SELECT 字段列表 FROM 表A
UNION [ALL]
SELECT 字段列表 FROM 表B

union all 会将全部的数据合并，union会对合并后的数据去重，对NULL也会处理

UNION 基本规则：

所有查询的列数和列顺序必须相同。
每个查询中涉及表的列的数据类型必须相同或兼容。
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

JOIN vs UNION：

JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积

子查询

子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

子查询是指嵌套在另一个 SQL 语句中的 SELECT 查询。

查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

而且它也可以出现在 WHERE、FROM、SELECT 等子句中，用于提供额外的数据过滤或计算条件。

子查询必须用括号 () 包裹

NULL 值处理：子查询返回 NULL 可能导致意外结果，需用 IS NULL 或 COALESCE 处理

1	SELECT * FROM 表1 WHERE 字段列表1 = （SELECT 字段列表 FROM 表2）

子查询外部的语句可以是INSERT UPDATE DELETE SELECT中的任意一个
标量子查询：子查询结果为单个值
列字查询：子查询结果为一列
行子查询：子查询结果为一行
表子查询：子查询结果为多行多列

根据子查询位置，分为WHRER之后，FROM之后，SELECT之后

当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。
当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，而且需要使用 AS 关键字为该临时表起一个名字，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

WHERE

WHERE 子句用于过滤记录，即缩小访问数据的范围。而且WHERE 可以与 SELECT，UPDATE 和 DELETE 一起使用。

WHERE 后跟一个返回 true 或 false 的条件。

可以在 WHERE 子句中使用的操作符。

IN 和 BETWEEN

IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。
1
SELECT u.name FROM users u WHERE u.id IN (2323434,2384888);
BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。
1
SELECT u.name FROM users u WHERE u.id BETWEEN 3 AND 5;

AND、OR、NOT

AND、OR、NOT 是用于对过滤条件的逻辑处理指令。
AND 优先级高于 OR，为了明确处理顺序，可以使用 ()。
AND 操作符表示左右条件都要满足。
OR 操作符表示左右条件满足任意一个即可。
NOT 操作符用于否定一个条件。

-- AND
SELECT p.id, p.price FROM products p WHERE p.name = 'sppl' AND p.is_delete = 1;

-- OR
SELECT p.id, p.name, p.price FROM products p WHERE p.vend_id = 'DLL01' OR p.vend_id = 'BRS01';

-- NOT
SELECT * FROM products WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE

LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。
只有字段是文本值时才使用 LIKE。
LIKE 支持两个通配符匹配选项：% 和 _。而且不要滥用通配符，通配符位于开头处匹配会非常慢，因为不会使用索引
% 表示任何字符出现任意次数。_ 表示任何字符出现一次。

-- %
SELECT p.id, p.name, p.price FROM products p WHERE p.name LIKE '%bean bag%';

-- _
SELECT p.id, p.name, p.price FROM products p WHERE p.name LIKE '__ inch teddy bear';

标量子查询

特点：返回单个值（一行一列）
常见场景：作为条件判断、赋值或计算的一部分

示例：查询工资高于平均工资的员工

1
2
3

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

子查询 (SELECT AVG(salary) FROM employees) 返回一个标量值（平均工资）
主查询通过 > 比较每个员工的工资与平均值

列子查询（Column Subquery）

特点：返回一列多行的数据
常用操作符：IN, NOT IN, ANY, ALL

示例：查询所有部门经理的员工信息

1
2
3

SELECT * 
FROM employees
WHERE emp_id IN (SELECT manager_id FROM departments);

行子查询（Row Subquery）

特点：返回一行多列的数据
常用操作符：=, <>, ALL

示例：查询与某个员工（ID=1001）部门和职位都相同的其他员工

SELECT * 
FROM employees
WHERE (department_id, position) = 
      (SELECT department_id, position FROM employees WHERE emp_id = 1001)
  AND emp_id != 1001;

子查询返回 emp_id=1001 的员工的部门和职位信息
主查询使用行比较 (col1, col2) = (val1, val2) 匹配条件

表子查询（Table Subquery）

特点：返回多行多列的数据（类似临时表）
常用位置：FROM 子句中，需使用别名

示例：查询每个部门的平均工资，并按降序排列

SELECT dept_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS t
JOIN departments d ON t.department_id = d.department_id
ORDER BY avg_salary DESC;

子查询计算每个部门的平均工资
主查询将子查询结果（临时表 t）与部门表连接，获取部门名称

子查询中 EXISTS

EXISTS：子查询是否返回任何行。如果子查询返回至少一行，则EXISTS条件为真，外层查询将根据这个结果进行相应的操作。

EXISTS只关心子查询是否有结果返回，而不关心返回的具体内容

1
2
3

SELECT columns
FROM table_name
WHERE EXISTS (subquery);

-- 查询有下属的员工
SELECT name FROM employee e1
WHERE EXISTS (SELECT 1 FROM employee e2 WHERE e2.manager_id = e1.emp_id);

-- 查询没有订单的客户
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

子查询与连接（JOIN）的对比

子查询和连接都可以实现多表数据关联，但适用场景不同：

子查询	连接（JOIN）
适合单值条件过滤	适合多表数据关联展示
逻辑上更直观（分步处理）	性能通常更高（优化器优化）
可能导致多次执行子查询	通常一次性扫描所有表

示例对比：查询每个部门的经理姓名（子查询 vs JOIN）

-- 子查询
SELECT 
    dept_name,
    (SELECT emp_name FROM employees WHERE emp_id = departments.manager_id) AS manager
FROM departments;

-- JOIN 方式
SELECT 
    d.dept_name,
    e.emp_name AS manager
FROM departments d
JOIN employees e ON d.manager_id = e.emp_id;

函数

不同数据库的函数往往各不相同，因此不可移植。本节主要以 MySQL 的函数为例

文本处理

`LEFT()`、`RIGHT()`	左边或者右边的字符
`LOWER()`、`UPPER()`	转换为小写或者大写
`LTRIM()`、`RTRIM()`	去除左边或者右边的空格
`LENGTH()`	长度，以字节为单位

日期和时间处理

数值处理

统计

AVG() 会忽略 NULL 行。

使用 DISTINCT，因为DISTINCT是去重，可以让汇总函数值汇总不同的值。

1	SELECT AVG(DISTINCT col1) AS avg_col FROM mytable

专用窗口函数

MySQL 8.0 版本引入了窗口函数的支持，下面是 MySQL 中常见的窗口函数及其用法

ROW_NUMBER(): 为查询结果集中的每一行分配一个唯一的整数值。

1 2	SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1) AS row_num FROM table;

RANK(): 计算每一行在排序结果中的排名。

1 2	SELECT col1, col2, RANK() OVER (ORDER BY col1 DESC) AS ranking FROM table;

DENSE_RANK(): 计算每一行在排序结果中的排名，保留相同的排名。

1 2	SELECT col1, col2, DENSE_RANK() OVER (ORDER BY col1 DESC) AS ranking FROM table;

NTILE(n): 将结果分成 n 个基本均匀的桶，并为每个桶分配一个标识号。
1
2
SELECT col1, col2, NTILE(4) OVER (ORDER BY col1) AS bucket
FROM table;

LEAD() 和 LAG(): LEAD 函数用于获取当前行之后的某个偏移量的行的值，而 LAG 函数用于获取当前行之前的某个偏移量的行的值。

1
2
3

SELECT col1, col2, LEAD(col1, 1) OVER (ORDER BY col1) AS next_col1,
                 LAG(col1, 1) OVER (ORDER BY col1) AS prev_col1
FROM table;

FIRST_VALUE() 和 LAST_VALUE(): FIRST_VALUE 函数用于获取窗口内指定列的第一个值，LAST_VALUE 函数用于获取窗口内指定列的最后一个值。

1
2
3

SELECT col1, col2, FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2) AS first_val,
                 LAST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2) AS last_val
FROM table;

窗口函数通常需要配合 OVER 子句一起使用，用于定义窗口的大小、排序规则和分组方式。

视图（VIEW）

理解视图

视图是基于 SQL 语句的结果集的可视化的表。

视图是由数据库中的一个表或多个表导出的虚拟表，其作用是方便用户对数据的操作。

因为视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。但是对视图的操作和对普通表的操作一样。

同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

所以，视图是虚拟的表，本身不包含数据，无法添加索引等操作，但是对视图的操作和对普通表的操作一样。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样对视图的更新，会影响到原来表的数据。

视图的作用主要是

安全原因：视图可以隐藏一些数据
可使复杂的查询易于理解和使用：这个视图就像一个“窗口”，从中只能看到你想看的数据列。

创建视图

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

删除视图

删除视图时，只能删除视图的定义，不会删除数据。

1	DROP VIEW top_10_user_view;

查询视图

可以使用SELECT语句来查询这些权限信息。查询语法别无二致

1	SELECT col1, col2 FROM 视图名 WHERE col1='ErgouTree';

修改视图也类似，略了

索引

这里只讲索引相关的 SQL 语法

创建索引

如果是建表时顺便创建索引

CREATE TABLE 表名 (
  字段1 类型 [约束],
  字段2 类型 [约束],
  -- 索引定义（放在字段后）
  [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名] (字段1[,字段2...]) [USING 存储引擎]
);

例如主键索引

CREATE TABLE user (
  id INT NOT NULL,
  name VARCHAR(50),
  PRIMARY KEY (id)  -- 单列主键
  -- 复合主键：PRIMARY KEY (id, name)
);

如果是建表后添加索引，就这样

1	CREATE INDEX user_index ON user(id);

ALTER TABLE 方式：意义倾向于为字段添加索引

1 2	ALTER TABLE 表名 ADD [UNIQUE\|FULLTEXT\|SPATIAL] INDEX [索引名] (字段1[,字段2...]) [USING 存储引擎];

CREATE INDEX 方式：意义倾向于为字段创建索引

1 2	CREATE [UNIQUE\|FULLTEXT\|SPATIAL] INDEX 索引名 ON 表名 (字段1[,字段2...]) [USING 存储引擎];

例如创建一个唯一索引，就可以这样

1	CREATE UNIQUE INDEX ON users (id);

前缀索引创建比较特殊

1 2	-- 建表后创建（截取name前10个字符） ALTER TABLE product ADD INDEX idx_product_name_prefix (name(10));

索引是有命名规范的：

主键索引：默认 PRIMARY，无需手动命名；
唯一索引：uk_表名_字段名；
普通索引：idx_表名_字段名；
复合索引：idx_表名_字段1_字段2；
全文索引：ft_表名_字段名。

删除索引

-- 方式1：ALTER TABLE（推荐，通用）
ALTER TABLE 表名 DROP INDEX 索引名;

-- 方式2：DROP INDEX
DROP INDEX 索引名 ON 表名;

查看索引

-- 查看单表所有索引
SHOW INDEX FROM 表名;
-- 简写
SHOW INDEXES ON 表名;

约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束类型

主键约束 PRIMARY KEY

唯一标识表中的每一行，确保数据的唯一性。

NOT NULL 和 UNIQUE 的结合。确保某列有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键列的值必须唯一且不能为 NULL。
一个表只能有一个主键，但主键可以由多个列组成（复合主键）。

递增约束 AUTO_INCREMENT

自动为列生成唯一的递增值，通常用于主键列。

只能用于整数类型的列。
每个表只能有一个 AUTO_INCREMENT 列。

唯一约束 UNIQUE

确保列中的值唯一，但允许 NULL 值。

一个表可以有多个唯一约束。
唯一约束可以作用于单列或多列（复合唯一约束）。

非空约束 NOT NULL

确保列中的值不能为 NULL。

非空约束只能作用于单列。

默认约束（DEFAULT）

为列设置默认值，当插入数据时未指定该列的值时，使用默认值。

默认值可以是常量、表达式或函数。

外键约束 FOREIGN KEY

确保表之间的引用完整性，用于关联两个表。

保证一个表中的数据匹配另一个表中的值的参照完整性。

外键列的值必须存在于被引用表的主键或唯一键中。
外键可以为 NULL。

检查约束 CHECK

确保列中的值满足指定条件。

MySQL 8.0.16 及以上版本支持 CHECK 约束。
条件可以是逻辑表达式。

复合约束

将多个列组合在一起作为约束条件。

复合主键：多个列共同作为主键。
复合唯一约束：多个列共同确保唯一性。

约束的添加

1	ALTER TABLE 表名 ADD CONSTRAINT 约束名约束类型 (列名);

约束的删除

1	ALTER TABLE 表名 DROP CONSTRAINT 约束名;

约束的命名

CREATE TABLE 表名 (
    列名 数据类型,
    CONSTRAINT 约束名 约束类型 (列名)
);

约束演示

-- 约束建表
CREATE TABLE Bin_user(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',   -- 多个约束空格分开
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK ( age >= 0 AND age <= 120 ) COMMENT '年龄',
    status CHAR(1) DEFAULT '0' COMMENT '状态',
    gender CHAR(1)  COMMENT '性别'
)COMMENT '用户表';

-- 主键约束的自动增长
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', 18, '1', 'M'), ('SJD', 23, '1', 'W');

-- name的非空约束
INSERT INTO Bin_user (name, age, status, gender) VALUES (NULL, 18, '1', 'M');
-- name的唯一约束
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', 18, '1', 'M');
-- age的检查约束
INSERT INTO Bin_user (name, age, status, gender) VALUES ('Tom1', -18, '1', 'M');
-- status的默认约束
INSERT INTO Bin_user (name, age, gender) VALUES ('Tom1', 18, 'M');

外键约束的级联操作

可以通过 ON DELETE 和 ON UPDATE 子句定义外键的级联行为。例如：

ON DELETE：当主表中的记录被删除时，自动删除从表中的相关记录。
ON UPDATE：当主表中的记录被更新时，自动更新从表中的相关记录。

而外键约束的删除和更新行为如下

NO ACTION：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则不允许删除/更新，同·RESTRICT
RESTRICT：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则不允许删除/更新
CASCADE：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则也删除/更新外键在子表中的记录
SET BULL：父表中删除/更新对应记录时，先检查该记录是否有对应外键，有则设置子表中该外键值为 NULL（外键允许 NULL 的前提下）
SET DEFAULT：父表变更的时候，子表将外键设置成一个默认的值

NO ACTION和RESTRICT是默认的

1 2	-- 设置 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE；

事务

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个 MySQL 连接而不是针对服务器的。

指令：

START TRANSACTION - 指令用于标记事务的起始点。
SAVEPOINT - 指令用于创建保留点。
ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
COMMIT - 提交事务。

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

存储过程

理解存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。存储过程可以由触发器，其他存储过程以及 Java， Python，PHP 等应用程序调用。

对于预编译，首次执行后，执行计划缓存到数据库，后续调用无需重新解析；

在传统企业级应用中，存储过程具有一定的实用价值。当业务逻辑复杂时，需要执行大量SQL语句才能完成一个业务操作，此时可以将这些语句封装成存储过程，简化调用过程。由于存储过程在创建时就已经编译并存储在数据库中，执行时无需重新编译，因此相比动态SQL语句具有更好的执行性能。同时，一旦存储过程调试完成，其运行相对稳定可靠。

然而，在现代互联网架构中，存储过程的使用越来越少。它调试困难，修改业务逻辑直接涉及到数据库对象，移植性差，不同数据库系统的存储过程语法差异较大，无法协作，无法水平扩展，分库分表不兼容，云原生不友好

将业务逻辑放在应用层实现，保持数据库的简单和高效是我们的常用做法

创建存储过程

创建存储过程：

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值都需要用 select into 语句。
每次只能给一个变量赋值，不支持集合的操作。

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;       -- 设置一个存储过程结束的符号
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;

使用存储过程

1
2
3

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

游标

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

使用游标的几个明确步骤：

在使用游标前，必须声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
对于填有数据的游标，根据需要取出(检索)各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标(有赖于具

体的 DBMS)。

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    -- 创建接收游标数据的变量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 创建总数变量
    DECLARE sage INT;
    -- 创建结束标志变量
    DECLARE done INT DEFAULT false;
    -- 创建游标
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定游标循环结束时的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- 调用存储过程
call getTotal();

注意：在 MySQL 中，分号 ; 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delimiter。new_delimiter 可以设为 1 个或多个长度的符号，默认的是分号 ;，我们可以把它修改为其他符号，如 $ - DELIMITER $ 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 $，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来

触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

我们可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

使用触发器的优点：

SQL 触发器提供了另一种检查数据完整性的方法。
SQL 触发器可以捕获数据库层中业务逻辑中的错误。
SQL 触发器提供了另一种运行计划任务的方法。通过使用 SQL 触发器，您不必等待运行计划任务，因为在对表中的数据进行更改之前或之后会自动调用触发器。
SQL 触发器对于审计表中数据的更改非常有用。

Spring Data JPA 就是基于触发器实现的

使用触发器的缺点：

SQL 触发器只能提供扩展验证，并且不能替换所有验证。必须在应用程序层中完成一些简单的验证。例如，您可以使用 JavaScript 在客户端验证用户的输入，或者使用服务器端脚本语言（如 JSP，PHP，ASP.NET，Perl）在服务器端验证用户的输入。
从客户端应用程序调用和执行 SQL 触发器是不可见的，因此很难弄清楚数据库层中发生了什么。
SQL 触发器可能会增加数据库服务器的开销。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

在 MySQL 5.7.2 版之前，可以为每个表定义最多六个触发器。

BEFORE INSERT - 在将数据插入表格之前激活。
AFTER INSERT - 将数据插入表格后激活。
BEFORE UPDATE - 在更新表中的数据之前激活。
AFTER UPDATE - 更新表中的数据后激活。
BEFORE DELETE - 在从表中删除数据之前激活。
AFTER DELETE - 从表中删除数据后激活。

但是，从 MySQL 版本 5.7.2+开始，可以为同一触发事件和操作时间定义多个触发器。

NEW 和 OLD：

MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法：NEW.columnName （columnName 为相应数据表某一列名）

创建触发器

CREATE TRIGGER 指令用于创建触发器。

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

说明：

trigger_name：触发器名
trigger_time : 触发器的触发时机。取值为 BEFORE 或 AFTER。
trigger_event : 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。
table_name : 触发器的监听目标。指定在哪张表上建立触发器。
ON table_name：触发器建在哪张表上
FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。
BEGIN 和 END：触发器执行动作开始和结束定界符，当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。
trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;