mysql8数据库手册

MySQL 8 的主要新特性

• 新的数据类型
  • JSON 数据类型：允许存储 JSON 文本，并且支持 JSON 格式的检索和操作。
  • GIS 功能增强：在地理空间数据的存储和查询方面有了更多的改进，支持更多的 GIS 函数。
• 性能优化
  • InnoDB 存储引擎的改进：提高了并发处理能力和整体性能，特别是在大数据集上。
  • 查询性能优化：引入了并行执行查询的能力，并且优化器得到了改进，可以生成更高效的查询执行计划。
• 安全性增强
  • 默认账户锁定策略：防止暴力破解攻击，对尝试多次失败登录的账户进行锁定。
  • 加密和认证改进：MySQL 8 默认使用更强的认证插件（如 caching_sha2_password），并且支持更广泛的 TLS 协议。
• 新的 SQL 功能
  • 窗口函数：允许在查询结果中使用窗口函数，如 ROW_NUMBER()、RANK() 等，可以在查询时对数据进行复杂的分析和计算。
  • 公共表表达式（CTE）：支持递归查询，简化了层次化数据的查询。
  • 函数支持扩展：MySQL 8 引入了许多新函数，比如 JSON_TABLE、GROUPING SETS 和 SKIP LOCKED 等。
• 其他改进
  • 临时表改进：临时表现在支持使用 InnoDB 存储引擎，允许更多的特性使用和更好的性能。
  • 角色支持：可以定义角色，并为用户分配角色来简化权限管理。
  • MySQL Shell：提供了增强的命令行工具，支持 SQL、JavaScript、Python 等多种语言脚本，极大提高了管理的灵活性。

安装使用

使用环境；Ubuntu2404，mysql8.0.36，重点讲解关于表的查询

apt-get install -y mysql-server

启动并开机自启

systemctl enable --now mysql

默认没有密码，可直接回车进入

root@huhy:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36-2ubuntu3 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

用户管理

创建新用户

• username：你想创建的用户名。
• host：用户可以从哪个主机连接到 MySQL。通常使用 ‘localhost’ 表示只允许从本地主机连接。如果希望允许从任何主机连接，可以使用 ‘%’。
• password：用户的密码。

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授予权限

在 MySQL 中，用户没有默认的数据库访问权限。你需要显式授予权限。使用 GRANT 命令可以授予特定权限或所有权限

授予特定数据库的所有权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

授予全局权限（对所有数据库的所有权限），WITH GRANT OPTION：允许用户将自己拥有的权限授予其他用户

GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;

授予特定权限；可以授予特定的权限，如 SELECT、INSERT、UPDATE 等

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';

查看用户权限

查看某个用户的权限可以使用 SHOW GRANTS 命令

SHOW GRANTS FOR 'username'@'host';

撤销权限

撤销某个用户的权限可以使用 REVOKE 命令

REVOKE privilege_type ON database_name.* FROM 'username'@'host';

删除用户

可以使用 DROP USER 命令

DROP USER 'username'@'host';

修改用户

修改用户密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

刷新权限

FLUSH PRIVILEGES;

检查当前用户

SELECT USER(), CURRENT_USER();

修改用户的主机名

RENAME USER 'username'@'old_host' TO 'username'@'new_host';

修改用户的认证插件；可以通过 ALTER USER 命令修改用户的认证插件。MySQL 8 默认使用 caching_sha2_password 认证插件，但你可以更改为其他插件，例如 mysql_native_password：

ALTER USER 'username'@'host' IDENTIFIED WITH 'plugin_name' BY 'password';

修改用户资源限制
许对用户的资源使用进行限制，例如最大查询数、最大更新数和最大连接数

ALTER USER 'username'@'host'
WITH MAX_QUERIES_PER_HOUR value
MAX_UPDATES_PER_HOUR value
MAX_CONNECTIONS_PER_HOUR value
MAX_USER_CONNECTIONS value;

强制用户重置密码

ALTER USER 'username'@'host' PASSWORD EXPIRE;

解锁用户；在 MySQL 8 中，如果一个用户因为多次登录失败而被锁定，可以使用以下命令解锁用户

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

锁定用户

ALTER USER 'username'@'host' ACCOUNT LOCK;

查看用户的当前状态；可以使用以下命令查看用户的当前状态，例如密码过期状态、账户是否被锁定

SELECT user, host, account_locked, password_expired FROM mysql.user WHERE user = 'username';

表管理

创建表

常用数据类型
整数类型

TINYINT：小整数，存储需求 1 字节。
有符号范围：-128 到 127。
无符号范围：0 到 255。

SMALLINT：大小适中的整数，存储需求 2 字节。
有符号范围：-32,768 到 32,767。
无符号范围：0 到 65,535。

MEDIUMINT：中等大小的整数，存储需求 3 字节。
有符号范围：-8,388,608 到 8,388,607。
无符号范围：0 到 16,777,215。

INT (INTEGER)：标准整数，存储需求 4 字节。
有符号范围：-2,147,483,648 到 2,147,483,647。
无符号范围：0 到 4,294,967,295。

BIGINT：大整数，存储需求 8 字节。
有符号范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
无符号范围：0 到 18,446,744,073,709,551,615。

浮点数类型

FLOAT(M, D)：单精度浮点数，存储需求 4 字节。
范围：-3.402823466E+38 到 3.402823466E+38。

DOUBLE(M, D)：双精度浮点数，存储需求 8 字节。
范围：-1.7976931348623157E+308 到 1.7976931348623157E+308。

DECIMAL(M, D)：定点数，精确表示的小数，存储需求由列的定义决定。
范围依赖于 M 和 D 的定义。

字符串类型

CHAR(M)：定长字符串，长度为 M，存储需求为 1 字节/字符。
支持的字符长度范围：0 到 255 字符。

VARCHAR(M)：变长字符串，最大长度为 M，存储需求为 1 字节/字符 + 1 字节长度前缀。
支持的字符长度范围：0 到 65,535 字符。

日期和时间类型

DATE：日期类型，存储需求 3 字节。
范围：'1000-01-01' 到 '9999-12-31'。

TIME：时间类型，存储需求 3 字节。
范围：'-838:59:59' 到 '838:59:59'。

DATETIME：日期和时间组合类型，存储需求 8 字节。
范围：'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

TIMESTAMP：时间戳类型，存储需求 4 字节。
范围：'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。

YEAR：年类型，存储需求 1 字节。
范围：'1901' 到 '2155'。

布尔类型

BOOLEAN：布尔类型，本质上是 TINYINT(1)，存储需求 1 字节。
范围：0 或 1。

其他类型

ENUM：枚举类型，存储一个值列表中的一个，存储需求 1 或 2 字节。
范围：可以存储 65535 个值。

SET：集合类型，存储一个值列表中的零个或多个，存储需求根据定义的元素数量而定。
范围：最多可以存储 64 个成员的组合。

约束条件；

PRIMARY KEY

• 描述：唯一标识表中的每一行，不能包含 NULL 值。
  作用：确保每行数据具有唯一性。

FOREIGN KEY

• 描述：确保列中的值必须在另一张表的某一列中存在，用于建立两个表之间的关联关系。
  作用：维护参照完整性。

UNIQUE

• 描述：确保列中的所有值都是唯一的，不允许重复值，可以包含 NULL。
  作用：限制列中的数据必须唯一。

NOT NULL

• 描述：确保列不能包含 NULL 值，必须有实际数据。
  作用：防止插入或更新操作将 NULL 值插入到该列中。

DEFAULT

• 描述：为列指定一个默认值，如果插入数据时该列没有提供值，则使用默认值。
  作用：自动填充列的默认值，避免 NULL。

CHECK

• 描述：确保列中的值满足特定的条件或表达式（MySQL 8.0 及以上版本支持）。
  作用：验证列中的数据是否符合条件。

AUTO_INCREMENT

• 描述：自动为整数列生成唯一的递增值，通常用于 PRIMARY KEY。
  作用：自动为新插入的记录生成唯一标识。

创建表示例

CREATE TABLE table_name (
    column1 datatype（数据类型） constraint（约束条件）,
    column2 datatype constraint,
    ...
);

查看表结构

DESCRIBE users;

或

SHOW COLUMNS FROM users;

修改表结构

• 添加列

ALTER TABLE table_name ADD column_name datatype constraint;

• 修改列

ALTER TABLE table_name MODIFY column_name datatype constraint;

• 删除列

ALTER TABLE table_name DROP COLUMN column_name;

• 重命名列

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype constraint;

创建表的副本；基于现有表创建新表，并复制数据

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

复制表，但是数据为空；这样，new_table 会具有与 old_table 相同的字段、索引和其他表属性，但数据内容为空

CREATE TABLE new_table LIKE old_table;

删除表

使用 DROP TABLE 语句删除表。删除表会删除所有数据，并且无法恢复，因此要小心使用

DROP TABLE table_name;

重命名表

RENAME TABLE old_table_name TO new_table_name;

删除数据；ELETE 语句用于删除表中的特定数据行，可以根据条件删除符合条件的记录，也可以删除所有记录

DELETE FROM table_name WHERE condition;

清空表数据；通常比 DELETE 语句更快，因为 TRUNCATE 是一种更高效的操作，它通过删除并重新创建表来清空数据

TRUNCATE TABLE table_name;

插入数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

表查询

基础查询

简单选择列进行查询

SELECT column1, column2, ...
FROM table_name;

查询所有列

SELECT * 
FROM table_name;

使用where条件过滤，过滤的参数如下

运算符类型	运算符	描述
比较运算符	=	等于
比较运算符	<> 或 !=	不等于
比较运算符	>	大于
比较运算符	<	小于
比较运算符	>=	大于等于
比较运算符	<=	小于等于
逻辑运算符	AND	逻辑与
逻辑运算符	OR	逻辑或
逻辑运算符	NOT	逻辑非
范围运算符	BETWEEN AND	在范围内
集合运算符	IN	在集合内
字符匹配运算符	LIKE	匹配字符串模式
NULL判断运算符	IS NULL	为空
NULL判断运算符	IS NOT NULL	不为空

• 说明
  • 比较运算符：用于比较两个值的关系。
  • 逻辑运算符：用于组合多个条件。
  • 范围运算符：用于检查一个值是否在某个范围内。
  • 集合运算符：用于检查一个值是否在指定的集合中。
  • 字符匹配运算符：用于匹配符合特定模式的字符串。
  • NULL判断运算符：用于检查一个值是否为空或不为空。

创建一个测试表

create database huhy;
use huhy;

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10, 2),
    status VARCHAR(10)
);

INSERT INTO test_table (id, name, age, salary, status) VALUES
(1, 'Alice', 25, 5000.00, 'active'),
(2, 'Bob', 30, 6000.00, 'inactive'),
(3, 'Charlie', 28, 7000.00, 'active'),
(4, 'David', 22, 5500.00, 'inactive'),
(5, 'Eve', 35, 7500.00, 'active');

比较运算符 =（等于）

mysql> SELECT * FROM test_table WHERE age = 30;
+----+------+------+---------+----------+
| id | name | age  | salary  | status   |
+----+------+------+---------+----------+
|  2 | Bob  |   30 | 6000.00 | inactive |
+----+------+------+---------+----------+
1 row in set (0.00 sec)

比较运算符 <> 或 !=（不等于）

mysql> SELECT * FROM test_table WHERE age <> 30;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  1 | Alice   |   25 | 5000.00 | active   |
|  3 | Charlie |   28 | 7000.00 | active   |
|  4 | David   |   22 | 5500.00 | inactive |
|  5 | Eve     |   35 | 7500.00 | active   |
+----+---------+------+---------+----------+
4 rows in set (0.00 sec)

比较运算符 >（大于）

mysql> SELECT * FROM test_table WHERE salary > 6000;
+----+---------+------+---------+--------+
| id | name    | age  | salary  | status |
+----+---------+------+---------+--------+
|  3 | Charlie |   28 | 7000.00 | active |
|  5 | Eve     |   35 | 7500.00 | active |
+----+---------+------+---------+--------+
2 rows in set (0.00 sec)

比较运算符 <（小于）

mysql> SELECT * FROM test_table WHERE age < 28;
+----+-------+------+---------+----------+
| id | name  | age  | salary  | status   |
+----+-------+------+---------+----------+
|  1 | Alice |   25 | 5000.00 | active   |
|  4 | David |   22 | 5500.00 | inactive |
+----+-------+------+---------+----------+
2 rows in set (0.00 sec)

比较运算符 >=（大于等于）

mysql> SELECT * FROM test_table WHERE salary >= 6000;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
|  5 | Eve     |   35 | 7500.00 | active   |
+----+---------+------+---------+----------+
3 rows in set (0.00 sec)

比较运算符 <=（小于等于）

mysql> SELECT * FROM test_table WHERE salary >= 6000;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
|  5 | Eve     |   35 | 7500.00 | active   |
+----+---------+------+---------+----------+
3 rows in set (0.00 sec)

逻辑运算符 AND（逻辑与）

mysql> SELECT * FROM test_table WHERE age > 25 AND salary < 7000;
+----+------+------+---------+----------+
| id | name | age  | salary  | status   |
+----+------+------+---------+----------+
|  2 | Bob  |   30 | 6000.00 | inactive |
+----+------+------+---------+----------+
1 row in set (0.00 sec)

mysql>

逻辑运算符 OR（逻辑或）

mysql> SELECT * FROM test_table WHERE age < 25 OR salary > 7000;
+----+-------+------+---------+----------+
| id | name  | age  | salary  | status   |
+----+-------+------+---------+----------+
|  4 | David |   22 | 5500.00 | inactive |
|  5 | Eve   |   35 | 7500.00 | active   |
+----+-------+------+---------+----------+
2 rows in set (0.00 sec)

逻辑运算符 NOT（逻辑非）

mysql> SELECT * FROM test_table WHERE NOT (status = 'active');
+----+-------+------+---------+----------+
| id | name  | age  | salary  | status   |
+----+-------+------+---------+----------+
|  2 | Bob   |   30 | 6000.00 | inactive |
|  4 | David |   22 | 5500.00 | inactive |
+----+-------+------+---------+----------+
2 rows in set (0.00 sec)

范围运算符 BETWEEN AND（在范围内）

mysql> SELECT * FROM test_table WHERE age BETWEEN 25 AND 30;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  1 | Alice   |   25 | 5000.00 | active   |
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
+----+---------+------+---------+----------+
3 rows in set (0.00 sec)

集合运算符 IN（在集合内）

mysql> SELECT * FROM test_table WHERE name IN ('Alice', 'Eve');
+----+-------+------+---------+--------+
| id | name  | age  | salary  | status |
+----+-------+------+---------+--------+
|  1 | Alice |   25 | 5000.00 | active |
|  5 | Eve   |   35 | 7500.00 | active |
+----+-------+------+---------+--------+
2 rows in set (0.00 sec)

字符匹配运算符 LIKE（匹配字符串模式）

mysql> SELECT * FROM test_table WHERE name LIKE 'A%';
+----+-------+------+---------+--------+
| id | name  | age  | salary  | status |
+----+-------+------+---------+--------+
|  1 | Alice |   25 | 5000.00 | active |
+----+-------+------+---------+--------+
1 row in set (0.00 sec)

NULL 判断运算符 IS NULL（为空）

mysql> SELECT * FROM test_table WHERE status IS NULL;
Empty set (0.00 sec)

NULL 判断运算符 IS NOT NULL（不为空）

mysql> SELECT * FROM test_table WHERE status IS NOT NULL;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  1 | Alice   |   25 | 5000.00 | active   |
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
|  4 | David   |   22 | 5500.00 | inactive |
|  5 | Eve     |   35 | 7500.00 | active   |
+----+---------+------+---------+----------+
5 rows in set (0.00 sec)

• 排序结果；升序排序（ASC），降序排序（DESC）

mysql> SELECT * FROM test_table order by age ASC;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  4 | David   |   22 | 5500.00 | inactive |
|  1 | Alice   |   25 | 5000.00 | active   |
|  3 | Charlie |   28 | 7000.00 | active   |
|  2 | Bob     |   30 | 6000.00 | inactive |
|  5 | Eve     |   35 | 7500.00 | active   |
+----+---------+------+---------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test_table order by age DESC;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  5 | Eve     |   35 | 7500.00 | active   |
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
|  1 | Alice   |   25 | 5000.00 | active   |
|  4 | David   |   22 | 5500.00 | inactive |
+----+---------+------+---------+----------+
5 rows in set (0.00 sec)

限制查询结果

• 查询前 N 条记录
  查询表中的前 3 条记录：

mysql> SELECT * FROM test_table LIMIT 3;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  1 | Alice   |   25 | 5000.00 | active   |
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
+----+---------+------+---------+----------+
3 rows in set (0.00 sec)

• 查询从某一行开始的 N 条记录
  查询 3 条记录（即从第 2 条到第 4 条记录）使用 OFFSET 来指定开始的行数

mysql> SELECT * FROM test_table LIMIT 3 OFFSET 1;
+----+---------+------+---------+----------+
| id | name    | age  | salary  | status   |
+----+---------+------+---------+----------+
|  2 | Bob     |   30 | 6000.00 | inactive |
|  3 | Charlie |   28 | 7000.00 | active   |
|  4 | David   |   22 | 5500.00 | inactive |
+----+---------+------+---------+----------+
3 rows in set (0.00 sec)

• 查询最后 N 条记录
  要查询最后 2 条记录，可以结合 ORDER BY 和 LIMIT，因为 id 是降序排序的，所以最后的记录会先返回

mysql> SELECT * FROM test_table ORDER BY id DESC LIMIT 2;
+----+-------+------+---------+----------+
| id | name  | age  | salary  | status   |
+----+-------+------+---------+----------+
|  5 | Eve   |   35 | 7500.00 | active   |
|  4 | David |   22 | 5500.00 | inactive |
+----+-------+------+---------+----------+
2 rows in set (0.00 sec)

中级查询

聚合函数

COUNT: 统计行数

SELECT COUNT(*)
FROM table_name;

SUM: 求和

SELECT SUM(column_name)
FROM table_name;

AVG: 平均值

SELECT AVG(column_name)
FROM table_name;

MAX: 最大值

SELECT MAX(column_name)
FROM table_name;

MIN: 最小值

SELECT MIN(column_name)
FROM table_name;

分组查询

分组查询用于将结果集中的行按照指定列进行分组，并对每个分组应用聚合函数。分组查询主要使用GROUP BY子句，并且通常与聚合函数一起使用

• column1: 用于分组的列。
• aggregate_function(column2): 聚合函数，通常用来计算统计数据，例如COUNT、SUM、AVG、MAX、MIN等。
• GROUP BY column1: 指定分组的列

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

根据 status 字段进行分组，并计算每个状态组中员工的平均工资

mysql> SELECT status, AVG(salary)
    -> FROM test_table
    -> GROUP BY status;
+----------+-------------+
| status   | AVG(salary) |
+----------+-------------+
| active   | 6500.000000 |
| inactive | 5750.000000 |
+----------+-------------+
2 rows in set (0.01 sec)

HAVING子句

HAVING 子句用于过滤分组结果，与 WHERE 子句类似，但 HAVING 是在 GROUP BY 之后应用的。它允许你对分组后的结果进行筛选

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

根据 status 字段分组，计算每个状态组的平均工资，并且只显示平均工资大于 6000 的组

mysql> SELECT status, AVG(salary)
    -> FROM test_table
    -> GROUP BY status
    -> HAVING AVG(salary) > 6000;
+--------+-------------+
| status | AVG(salary) |
+--------+-------------+
| active | 6500.000000 |
+--------+-------------+
1 row in set (0.00 sec)

高级查询

子查询
单行子查询；单行子查询返回一行数据和一列数据，或者是一个标量值。这种子查询通常用于在主查询中匹配一个值

SELECT column1, column2, ...
FROM table_name
WHERE column = (SELECT column FROM table_name WHERE condition);

示例：查询表中工资最高的员工的信息

mysql> SELECT id, name, age, salary, status
    -> FROM test_table
    -> WHERE salary = (SELECT MAX(salary) FROM test_table);
+----+------+------+---------+--------+
| id | name | age  | salary  | status |
+----+------+------+---------+--------+
|  5 | Eve  |   35 | 7500.00 | active |
+----+------+------+---------+--------+
1 row in set (0.00 sec)

多行子查询；多行子查询返回多行数据，通常用于匹配主查询中列的多个值

SELECT column1, column2, ...
FROM table_name
WHERE column IN (SELECT column FROM table_name WHERE condition);

示例：查询工资最高的员工属于的状态组中的所有员工

mysql> SELECT id, name, age, salary, status
    -> FROM test_table
    -> WHERE status = (SELECT status FROM test_table WHERE salary = (SELECT MAX(salary) FROM test_table));
+----+---------+------+---------+--------+
| id | name    | age  | salary  | status |
+----+---------+------+---------+--------+
|  1 | Alice   |   25 | 5000.00 | active |
|  3 | Charlie |   28 | 7000.00 | active |
|  5 | Eve     |   35 | 7500.00 | active |
+----+---------+------+---------+--------+
3 rows in set (0.00 sec)

连接查询

内连接（INNER JOIN）
内连接是最常用的连接类型，它返回两个表中匹配条件的记录。如果两个表中没有匹配的记录，则这些记录不会出现在结果集中

SELECT t1.column1, t2.column2, ...
FROM table1 t1
INNER JOIN table2 t2
ON t1.common_column = t2.common_column;

示例：创建一个包含部门信息的表，department_table

CREATE TABLE department_table (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO department_table (dept_id, dept_name) VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Engineering'),
(4, 'Marketing');

更新 test_table，为每个员工分配部门

ALTER TABLE test_table ADD COLUMN dept_id INT;

UPDATE test_table SET dept_id = 1 WHERE id = 1;
UPDATE test_table SET dept_id = 2 WHERE id = 2;
UPDATE test_table SET dept_id = 3 WHERE id = 3;
UPDATE test_table SET dept_id = 3 WHERE id = 4;
UPDATE test_table SET dept_id = 4 WHERE id = 5;

内连接；返回两个表中存在匹配关系的记录

mysql> SELECT t.id, t.name, t.salary, d.dept_name
    -> FROM test_table t
    -> INNER JOIN department_table d ON t.dept_id = d.dept_id;
+----+---------+---------+-------------+
| id | name    | salary  | dept_name   |
+----+---------+---------+-------------+
|  1 | Alice   | 5000.00 | HR          |
|  2 | Bob     | 6000.00 | Finance     |
|  3 | Charlie | 7000.00 | Engineering |
|  4 | David   | 5500.00 | Engineering |
|  5 | Eve     | 7500.00 | Marketing   |
+----+---------+---------+-------------+
5 rows in set (0.00 sec)

左连接（LEFT JOIN）
左连接返回左表中的所有记录以及右表中匹配的记录。如果右表中没有匹配的记录，结果中对应右表的列会显示为 NULL，table1为主表，table2为从表

SELECT t1.column1, t2.column2, ...
FROM table1 t1
LEFT JOIN table2 t2
ON t1.common_column = t2.common_column;

示例；

mysql> SELECT t.id, t.name, t.salary, d.dept_name
    -> FROM test_table t
    -> LEFT JOIN department_table d ON t.dept_id = d.dept_id;
+----+---------+---------+-------------+
| id | name    | salary  | dept_name   |
+----+---------+---------+-------------+
|  1 | Alice   | 5000.00 | HR          |
|  2 | Bob     | 6000.00 | Finance     |
|  3 | Charlie | 7000.00 | Engineering |
|  4 | David   | 5500.00 | Engineering |
|  5 | Eve     | 7500.00 | Marketing   |
+----+---------+---------+-------------+
5 rows in set (0.00 sec)

右连接（RIGHT JOIN）
右连接返回右表中的所有记录以及左表中匹配的记录。如果左表中没有匹配的记录，结果中对应左表的列会显示为 NULL

SELECT t1.column1, t2.column2, ...
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.common_column = t2.common_column;

示例；返回右表中的所有记录，即使左表中没有匹配

mysql> SELECT t.id, t.name, t.salary, d.dept_name
    -> FROM test_table t
    -> RIGHT JOIN department_table d ON t.dept_id = d.dept_id;
+------+---------+---------+-------------+
| id   | name    | salary  | dept_name   |
+------+---------+---------+-------------+
|    1 | Alice   | 5000.00 | HR          |
|    2 | Bob     | 6000.00 | Finance     |
|    4 | David   | 5500.00 | Engineering |
|    3 | Charlie | 7000.00 | Engineering |
|    5 | Eve     | 7500.00 | Marketing   |
+------+---------+---------+-------------+
5 rows in set (0.00 sec)

FULL OUTER JOIN（全外连接）

返回两个表中的所有记录，不论是否有匹配。注意 MySQL 本身不直接支持 FULL OUTER JOIN，我们可以通过 LEFT JOIN 和 RIGHT JOIN 的结合来实现

• UNION 操作符用于将两个或更多查询的结果集合并成一个结果集。UNION 默认去除重复的记录，只返回唯一的记录
• UNION ALL 操作符用于将两个或更多查询的结果集合并成一个结果集。UNION ALL 不会去除重复的记录，会返回所有记录，包括重复的记录

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

mysql> SELECT t.id, t.name, t.salary, d.dept_name
    -> FROM test_table t
    -> LEFT JOIN department_table d ON t.dept_id = d.dept_id
    -> UNION
    -> SELECT t.id, t.name, t.salary, d.dept_name
    -> FROM test_table t
    -> RIGHT JOIN department_table d ON t.dept_id = d.dept_id;
+------+---------+---------+-------------+
| id   | name    | salary  | dept_name   |
+------+---------+---------+-------------+
|    1 | Alice   | 5000.00 | HR          |
|    2 | Bob     | 6000.00 | Finance     |
|    3 | Charlie | 7000.00 | Engineering |
|    4 | David   | 5500.00 | Engineering |
|    5 | Eve     | 7500.00 | Marketing   |
+------+---------+---------+-------------+
5 rows in set (0.00 sec)

窗口函数

窗口函数（Window Functions）是 SQL 中的一种高级功能，允许在查询结果集中执行计算和分析，而不必对结果集进行聚合。这些函数在处理数据时会使用“窗口”或“帧”，来计算指定范围内的值，从而为每一行提供额外的上下文信息。窗口函数在数据分析、报告和复杂计算中非常有用

• ROW_NUMBER(): 为结果集中的每一行分配一个唯一的编号。
• RANK(): 为结果集中的每一行分配一个排名，具有相同值的行会得到相同的排名，并在排名中留有空缺。
• DENSE_RANK(): 类似于 RANK()，但不会在排名中留有空缺。

SELECT column1, column2,
       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num,
       RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank,
       DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2) AS dense_rank
FROM table_name;

示例；计算每个部门的平均工资和每个员工与部门平均工资的差异

mysql> SELECT
    ->     t.name,
    ->     d.dept_name,
    ->     t.salary,
    ->     AVG(t.salary) OVER (PARTITION BY t.dept_id) AS dept_avg_salary,
lary) OVER (PART    ->     t.salary - AVG(t.salary) OVER (PARTITION BY t.dept_id) AS salary_diff_from_avg
    -> FROM
    ->     test_table t
    -> JOIN
    ->     department_table d ON t.dept_id = d.dept_id;
+---------+-------------+---------+-----------------+----------------------+
| name    | dept_name   | salary  | dept_avg_salary | salary_diff_from_avg |
+---------+-------------+---------+-----------------+----------------------+
| Alice   | HR          | 5000.00 |     5000.000000 |             0.000000 |
| Bob     | Finance     | 6000.00 |     6000.000000 |             0.000000 |
| Charlie | Engineering | 7000.00 |     6250.000000 |           750.000000 |
| David   | Engineering | 5500.00 |     6250.000000 |          -750.000000 |
| Eve     | Marketing   | 7500.00 |     7500.000000 |             0.000000 |
+---------+-------------+---------+-----------------+----------------------+
5 rows in set (0.00 sec)

CASE语句

CASE 语句用于根据条件选择不同的值。它类似于其他编程语言中的 switch 语句。你可以在 SELECT 语句、UPDATE 语句等中使用 CASE 语句来根据条件动态生成结果

CASE 语句在 MySQL 中有两种主要的语法：简单 CASE 和搜索 CASE

简单case

• expression: 一个要比较的表达式。
• value1, value2, …: 表达式可能的值。
• result1, result2, …: 对应于每个值的结果。
• ELSE: 当表达式与任何 WHEN 值不匹配时的默认结果。如果省略 ELSE 部分，且没有匹配项，则返回 NULL。
• END: 结束 CASE 语句。

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

搜索 CASE

• condition1, condition2, …: 布尔条件。
• result1, result2, …: 对应于每个条件为 TRUE 时的结果。
• ELSE: 当所有条件都为 FALSE 时的默认结果。如果省略 ELSE 部分，且没有条件满足，则返回 NULL。
• END: 结束 CASE 语句。

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

示例；根据员工的薪水给他们分配等级

SELECT 
    t.name AS employee_name,
    t.salary,
    t.status,
    d.dept_name AS department_name,
    CASE
        WHEN t.salary < 5000 THEN 'Low'
        WHEN t.salary BETWEEN 5000 AND 7000 THEN 'Medium'
        WHEN t.salary > 7000 THEN 'High'
    END AS salary_grade
FROM test_table t
JOIN department_table d ON t.dept_id = d.dept_id;

mysql> SELECT
    ->     t.name AS employee_name,
    ->     t.salary,
    ->     t.status,
      WHEN t.sal    -> ary < 5000 THEN     d.dept_name AS department_name,
    ->     CASE
    ->         WHEN t.salary < 5000 THEN 'Low'
    ->         WHEN t.salary BETWEEN 5000 AND 7000 THEN 'Medium'
    ->         WHEN t.salary > 7000 THEN 'High'
 AS salary_grade
FROM test_table    ->     END AS salary_grade
    -> FROM test_table t
    -> JOIN department_table d ON t.dept_id = d.dept_id;
+---------------+---------+----------+-----------------+--------------+
| employee_name | salary  | status   | department_name | salary_grade |
+---------------+---------+----------+-----------------+--------------+
| Alice         | 5000.00 | active   | HR              | Medium       |
| Bob           | 6000.00 | inactive | Finance         | Medium       |
| Charlie       | 7000.00 | active   | Engineering     | Medium       |
| David         | 5500.00 | inactive | Engineering     | Medium       |
| Eve           | 7500.00 | active   | Marketing       | High         |
+---------------+---------+----------+-----------------+--------------+
5 rows in set (0.00 sec)

WITH子句

WITH 子句通常用于编写公用表表达式 (Common Table Expression，简称 CTE)。CTE 是一种临时的结果集，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用

语法

• WITH: 引入 CTE 的关键字。
• cte_name: CTE 的名称，可以在后续的查询中像表一样使用。
• SELECT … FROM table_name: CTE 的查询部分。

WITH cte_name AS (
    -- 这里是你的查询
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE another_condition;

示例；计算每个部门的平均薪水，然后查找那些薪水高于部门平均薪水的员工

WITH dept_avg_salaries AS (
    SELECT 
        t.dept_id, 
        AVG(t.salary) AS avg_salary
    FROM 
        test_table t
    GROUP BY 
        t.dept_id
)
SELECT 
    t.name AS employee_name,
    t.salary,
    d.dept_name AS department_name,
    da.avg_salary
FROM 
    test_table t
JOIN 
    dept_avg_salaries da ON t.dept_id = da.dept_id
JOIN 
    department_table d ON t.dept_id = d.dept_id
WHERE 
    t.salary > da.avg_salary;

mysql> WITH dept_avg_salaries AS (
    ->     SELECT
    ->         t.dept_id,
    ->         AVG(t.salary) AS avg_salary
    ->     FROM
    ->         test_table t
    ->     GROUP BY
    ->         t.dept_id
    -> )
    -> SELECT
    ->     t.name AS employee_name,
    ->     t.salary,
    ->     d.dept_name AS department_name,
    ->     da.avg_salary
    -> FROM
    ->     test_table t
    -> JOIN
    ->     dept_avg_salaries da ON t.dept_id = da.dept_id
    -> JOIN
    ->     department_table d ON t.dept_id = d.dept_id
WHERE
    t.salary > da.avg_sal    -> WHERE
    ->     t.salary > da.avg_salary;
+---------------+---------+-----------------+-------------+
| employee_name | salary  | department_name | avg_salary  |
+---------------+---------+-----------------+-------------+
| Charlie       | 7000.00 | Engineering     | 6250.000000 |
+---------------+---------+-----------------+-------------+
1 row in set (0.00 sec)