MySQL-SQL语法

coderCatIce
81 阅读20分钟

MySQL 语法

一、DDL（Data Definition Language，数据定义语言）

用于定义 / 修改数据库、表、视图、索引等数据库对象结构，操作后会自动提交事务（无法回滚）。

1. 数据库相关

（1）创建数据库
-- 基本创建（默认字符集）
CREATE DATABASE 数据库名;
-- 不存在时创建（避免重复创建报错）
CREATE DATABASE IF NOT EXISTS 数据库名;

-- 指定字符集和排序规则（推荐，避免中文乱码）
CREATE DATABASE 数据库名 
CHARACTER SET utf8mb4  -- 支持所有 Unicode 字符（含表情）
COLLATE utf8mb4_general_ci;  -- 不区分大小写（ci=case insensitive）
（2）查看数据库
-- 查看所有数据库
SHOW DATABASES;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 查看数据库创建语句（字符集、排序规则等）
SHOW CREATE DATABASE 数据库名;
（3）切换数据库
USE 数据库名;
（4）修改数据库
-- 修改字符集和排序规则
ALTER DATABASE 数据库名 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;
（5）删除数据库
-- 直接删除（危险！数据不可恢复）
DROP DATABASE 数据库名;

-- 存在时删除（避免报错）
DROP DATABASE IF EXISTS 数据库名;

2. 表相关

（1）创建表

  • 常用数据类型：

    • int：整数（例：age INT）

    • varchar(n)：可变长度字符串（例：name VARCHAR(20)，n 为最大长度）

    • char(n)：固定长度字符串（例：gender CHAR(1)）

    • datetime：日期时间（例：create_time DATETIME）

    • date：日期（例：birth_date DATE）

    • decimal(m,d)：小数（例：price DECIMAL(10,2)，m 总长度，d 小数位）

    • text：长文本（例：content TEXT）

  • 常用约束：

    • PRIMARY KEY：主键（唯一标识，非空）

    • NOT NULL：非空

    • UNIQUE：唯一值（可null）

    • DEFAULT：默认值

    • FOREIGN KEY：外键（关联其他表的主键）

CREATE TABLE IF NOT EXISTS 表名 (
    字段名1 数据类型 [约束条件],
    字段名2 数据类型 [约束条件],
    ...
    字段名n 数据类型 [约束条件]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  -- 存储引擎+字符集

-- 示例：创建用户表
CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键
    name VARCHAR(20) NOT NULL,
    gender CHAR(1) DEFAULT '男',
    age INT CHECK (age > 0 AND age < 150),  -- 检查约束（MySQL 8.0+支持）
    phone VARCHAR(11) UNIQUE,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
（2）查看表
-- 查看当前数据库所有表
SHOW TABLES;

-- 查看表结构（字段、类型、约束等）
DESC 表名;  -- 简写
-- 或
SHOW COLUMNS FROM 表名;

-- 查看表创建语句
SHOW CREATE TABLE 表名;
（3）修改表
-- 1. 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;

-- 2. 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件] [FIRST|AFTER 已有字段名];
-- 示例：在 name 后添加 email UNIQUE字段
ALTER TABLE user ADD email VARCHAR(50) UNIQUE AFTER name;

-- 3. 修改字段（类型/约束/位置）
-- 修改类型和约束
ALTER TABLE 表名 MODIFY 字段名 新数据类型 [新约束];
-- 示例：将 age 字段改为 INT(3)，允许为空
ALTER TABLE user MODIFY age INT(3) NULL;

-- 修改字段名（同时可改类型）
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [约束];
-- 示例：将 phone 改为 tel
ALTER TABLE user CHANGE phone tel VARCHAR(11) UNIQUE;

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

-- 5. 修改表的字符集
ALTER TABLE 表名 DEFAULT CHARSET utf8mb4;
（4）删除表
-- 直接删除（危险）
DROP TABLE 表名;

-- 存在时删除
DROP TABLE IF EXISTS 表名;

-- 清空表数据（保留表结构，自增主键重置）
TRUNCATE TABLE 表名;  -- 比 DELETE 快，无法回滚

3. 索引相关（DDL 范畴，优化查询）

-- 1. 创建索引
-- 普通索引
CREATE INDEX 索引名 ON 表名(字段名);
-- 唯一索引（字段值唯一，可null）
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
-- 联合索引（多字段组合）
CREATE INDEX 索引名 ON 表名(字段1, 字段2);

-- 2. 查看索引
SHOW INDEX FROM 表名;

-- 3. 删除索引
DROP INDEX 索引名 ON 表名;

二、DML（Data Manipulation Language，数据操纵语言）

用于操作表中的数据（增删改），操作后需手动提交（COMMIT）或回滚（ROLLBACK）（事务支持）。

1. 插入数据（INSERT）

（1）全字段插入（顺序需与表结构一致）
INSERT INTO 表名 VALUES (值1, 值2, ..., 值n);
-- 示例：插入用户数据（id 自增时，id 可省略）
INSERT INTO user VALUES (NULL, '张三', 'zhangsan@163.com', '男', 25, '13800138000', '2024-01-01 10:00:00');
（2）指定字段插入（推荐，顺序可自定义，未指定字段用默认值 / NULL）
INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...);
-- 示例：只插入姓名、手机号、性别
INSERT INTO user(name, tel, gender) VALUES ('李四', '13900139000', '女');
（3）批量插入（效率高于多次单条插入）
INSERT INTO 表名(字段1, 字段2) 
VALUES (值1, 值2), 
       (值3, 值4), 
       (值5, 值6);
（4）插入查询结果（从其他表导入数据）
INSERT INTO 目标表(字段1, 字段2)
SELECT 字段1, 字段2 FROM 源表 WHERE 条件;

2. 修改数据（UPDATE）

UPDATE 表名 
SET 字段1=新值1, 字段2=新值2, ... 
[WHERE 条件];  -- 记得加 WHERE，否则修改全表数据！

-- 示例：将 id=1 的用户年龄改为 26
UPDATE user SET age=26 WHERE id=1;

-- 示例：批量修改（将性别为女的用户默认邮箱改为 'female@default.com'）
UPDATE user SET email='female@default.com' WHERE gender='女';

3. 删除数据（DELETE）

  • DELETE vs TRUNCATE：

      1. DELETE 可加 WHERE，删除部分数据；TRUNCATE 清空全表（无 WHERE）
      1. DELETE 会记录日志，支持回滚；TRUNCATE 不记录日志，效率更高
      1. DELETE 后自增主键不重置；TRUNCATE 后自增主键重置
DELETE FROM 表名 [WHERE 条件];  -- 必须加 WHERE，否则删除全表数据！

-- 示例：删除 id=3 的用户
DELETE FROM user WHERE id=3;

-- 示例：删除年龄大于 100 的用户
DELETE FROM user WHERE age > 100;

4. 事务控制（配合 DML）

-- 开始事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行 DML 操作（INSERT/UPDATE/DELETE）
INSERT INTO user(name, tel) VALUES ('王五', '13700137000');
UPDATE user SET age=30 WHERE name='王五';

-- 提交事务（数据永久生效）
COMMIT;

-- 回滚事务（撤销所有未提交的操作，数据恢复到事务开始前）
ROLLBACK;

-- 保存点（可回滚到指定保存点，而非全量回滚）
SAVEPOINT 保存点名称;
ROLLBACK TO SAVEPOINT 保存点名称;

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

用于从表中查询数据（不会修改数据），核心是 SELECT 语句，配合各种子句实现复杂查询。

1. 基本查询（SELECT）

（1）查询所有字段（不推荐，效率低，字段顺序不确定）
SELECT * FROM 表名;
（2）查询指定字段
SELECT 字段1, 字段2, ... FROM 表名;

-- 示例：查询用户的姓名、年龄、手机号
SELECT name, age, tel FROM user;
（3）字段别名（AS 可省略）
SELECT 字段1 AS 别名1, 字段2 别名2 FROM 表名;
-- 示例：将 tel 显示为 手机号
SELECT name 姓名, age 年龄, tel 手机号 FROM user;
（4）去重查询（DISTINCT）
SELECT DISTINCT 字段 FROM 表名;
-- 示例：查询所有不重复的性别
SELECT DISTINCT gender FROM user;
（5）查询结果运算（针对数值型字段）
SELECT 字段1 + 字段2 AS 运算结果 FROM 表名;
-- 示例：查询用户年龄+5 后的结果
SELECT name, age, age+5 年龄加5 FROM user;

2. 条件查询（WHERE 子句）

（1）比较运算符
运算符说明示例
=等于WHERE age = 25
!=或<>不等于WHERE gender != ' 男'
>大于WHERE age > 30
<小于WHERE age < 20
>=大于等于WHERE age >= 25
<=小于等于WHERE age <= 40
BETWEEN...AND...介于两者之间（含边界）WHERE age BETWEEN 20 AND 30
IN(...)在指定集合中WHERE id IN (1,3,5)
NOT IN(...)不在指定集合中WHERE id NOT IN (2,4)
IS NULL为空（NULL 不能用 = 判断）WHERE email IS NULL
IS NOT NULL不为空WHERE email IS NOT NULL
-- 示例1：查询年龄在 20-30 之间的女性用户
SELECT * FROM user WHERE gender='女' AND age BETWEEN 20 AND 30;

-- 示例2：查询手机号为 13800138000 或 13900139000 的用户
SELECT * FROM user WHERE tel IN ('13800138000', '13900139000');

-- 示例3：查询邮箱不为空的用户
SELECT * FROM user WHERE email IS NOT NULL;
（2）逻辑运算符
  • AND：多个条件同时满足
  • OR：多个条件至少一个满足
  • NOT：否定条件
-- 示例：查询年龄大于 25 且性别为男，或手机号以 137 开头的用户
SELECT * FROM user WHERE (age>25 AND gender='男') OR tel LIKE '137%';
（3）模糊查询（LIKE）
  • %：匹配任意长度的字符（0 个、1 个、多个）
  • _：匹配单个字符
-- 示例1：查询姓名以 "张" 开头的用户
SELECT * FROM user WHERE name LIKE '张%';

-- 示例2：查询姓名第二个字是 "三" 的用户
SELECT * FROM user WHERE name LIKE '_三%';

-- 示例3：查询手机号包含 "000" 的用户
SELECT * FROM user WHERE tel LIKE '%000%';

3. 排序查询（ORDER BY 子句）

  • 说明：

    • ASC：升序（默认，可省略）

    • DESC：降序

    • 多字段排序：先按字段1排序，字段1相同再按字段2排序

SELECT 字段 FROM 表名 
ORDER BY 排序字段1 [ASC|DESC], 排序字段2 [ASC|DESC];

-- 示例1：按年龄升序查询（从小到大）
SELECT * FROM user ORDER BY age;

-- 示例2：按年龄降序，再按id升序
SELECT * FROM user ORDER BY age DESC, id ASC;

4. 分页查询（LIMIT 子句，MySQL 特有）

用于限制查询结果的条数（分页展示）：

-- 语法1：LIMIT 条数（查询前 n 条）
SELECT * FROM 表名 LIMIT n;

-- 语法2：LIMIT 偏移量, 条数（从第 m+1 条开始，查询 n 条）
-- 偏移量 = （页码-1）* 每页条数
SELECT * FROM 表名 LIMIT 偏移量, 条数;

-- 示例1：查询前 5 条用户数据
SELECT * FROM user LIMIT 5;

-- 示例2：分页查询（第 2 页，每页 10 条）
SELECT * FROM user LIMIT 10, 10;  -- 偏移量=10（10=2-1*10），条数=10

5. 聚合查询（GROUP BY + 聚合函数）

聚合函数用于对一组数据进行计算，返回单个结果：

聚合函数说明
COUNT()统计记录数
SUM()求和（数值型字段）
AVG()求平均值（数值型字段）
MAX()求最大值
MIN()求最小值
（1）基本聚合
-- 示例1：统计用户总数
SELECT COUNT(*) AS 用户总数 FROM user;
-- COUNT(*) 统计所有记录（含 NULL）；COUNT(字段) 统计非 NULL 记录

-- 示例2：统计女性用户数
SELECT COUNT(*) AS 女性用户数 FROM user WHERE gender='女';

-- 示例3：计算所有用户的平均年龄
SELECT AVG(age) AS 平均年龄 FROM user;

-- 示例4：查询最大年龄和最小年龄
SELECT MAX(age) 最大年龄, MIN(age) 最小年龄 FROM user;
（2）分组聚合（GROUP BY）

按指定字段分组，对每组数据进行聚合计算：

维度WHEREHAVING
过滤阶段分组（GROUP BY）之前分组（GROUP BY）之后
过滤对象原始表中的行数据（单条记录）分组后的聚合结果（一组记录的统计值）
可用条件表中字段、表达式（不能用聚合函数）分组字段、聚合函数（可结合表字段）
是否依赖 GROUP BY可独立使用（无分组时也能过滤）必须配合 GROUP BY 使用（无分组时无意义）
索引支持可使用字段索引（优化查询效率）无法使用索引（过滤的是聚合结果）
SELECT 分组字段, 聚合函数(字段) FROM 表名 
GROUP BY 分组字段 
[HAVING 聚合条件]; 

-- 注意：GROUP BY 后，SELECT 只能跟 分组字段 和 聚合函数

-- 示例1：按性别分组，统计每组的用户数和平均年龄
SELECT gender 性别, COUNT(*) 人数, AVG(age) 平均年龄 FROM user GROUP BY gender;

-- 示例2：按性别分组，筛选出人数大于 2 的组
SELECT gender 性别, COUNT(*) 人数 FROM user GROUP BY gender HAVING COUNT(*) > 2;

6. 多表查询（JOIN 子句）

当数据分布在多个表中时，需通过关联字段（通常是外键）查询：

（1）表关联类型
关联类型说明
INNER JOIN内连接（只显示两表中匹配的记录）
LEFT JOIN左连接（显示左表所有记录，右表匹配不到显示 NULL）
RIGHT JOIN右连接（显示右表所有记录，左表匹配不到显示 NULL）
FULL JOIN全连接（显示两表所有记录，匹配不到显示 NULL），MySQL 不直接支持，需用 UNION 实现
（2）示例准备

假设有两张表：

  • user 表（id, name, age, dept_id）
  • dept 表（id, dept_name）（dept_id 是 user 表的外键，关联 dept 表的 id）
（3）内连接（INNER JOIN）
-- 语法：表1 INNER JOIN 表2 ON 关联条件
SELECT u.name 用户名, u.age 年龄, d.dept_name 部门名称
FROM user u  -- 表别名（简化书写）
INNER JOIN dept d 
ON u.dept_id = d.id;  -- 关联条件（外键=主键）

-- 等价于（老式写法，不推荐）
SELECT u.name, d.dept_name FROM user u, dept d WHERE u.dept_id = d.id;
（4）左连接（LEFT JOIN）
-- 显示所有用户，即使没有关联的部门（部门名称显示 NULL）
SELECT u.name 用户名, d.dept_name 部门名称
FROM user u
LEFT JOIN dept d 
ON u.dept_id = d.id;
（5）右连接（RIGHT JOIN）
-- 显示所有部门，即使没有关联的用户（用户信息显示 NULL）
SELECT u.name 用户名, d.dept_name 部门名称
FROM user u
RIGHT JOIN dept d 
ON u.dept_id = d.id;

7. 子查询（嵌套查询）

将一个查询结果作为另一个查询的条件 / 数据源：

（1）WHERE 子句中的子查询
-- 示例：查询年龄大于平均年龄的用户
SELECT * FROM user WHERE age > (SELECT AVG(age) FROM user);

-- 示例：查询属于 "技术部" 的用户（先查技术部的 id，再查用户）
SELECT * FROM user WHERE dept_id = (SELECT id FROM dept WHERE dept_name='技术部');
（2）FROM 子句中的子查询（派生表）
-- 示例：查询每个部门的平均年龄，并筛选出平均年龄大于 30 的部门
SELECT dept_name, 平均年龄
FROM (
    -- 子查询作为派生表（必须加别名 t）
    SELECT d.dept_name, AVG(u.age) 平均年龄
    FROM user u
    JOIN dept d ON u.dept_id = d.id
    GROUP BY d.dept_name
) t
WHERE 平均年龄 > 30;
（3）EXISTS 子查询（判断是否存在满足条件的记录）
-- 示例：查询存在邮箱为 "zhangsan@163.com" 的用户所在的部门
SELECT * FROM dept d
WHERE EXISTS (
    SELECT 1 FROM user u WHERE u.dept_id = d.id AND u.email='zhangsan@163.com'
);

8. 常用函数

（1）字符串函数

  • CONCAT(str1, str2...)：拼接字符串

  • SUBSTRING(str, pos, len)：从指定位置（pos 从 1 开始）截取字符串，可选截取长度，用于提取固定位置字符（如手机号前缀）

  • LEFT(str, len)：快速截取字符串左侧 len 个字符（如手机号前 3 位、身份证前 6 位）

  • RIGHT(str, len)：快速截取字符串右侧 len 个字符（如手机号后 4 位、文件扩展名）

  • SUBSTRING_INDEX(str, delim, count)：按分隔符拆分字符串，count 正数取前 n 段、负数取后 n 段（如拆分邮箱、路径）

  • REPLACE(str, from, to)：替换字符串中所有指定字符，用于清理无效字符（如去除手机号分隔符、替换特殊符号）

  • TRIM(str)：去除字符串首尾空格，用于清理用户输入的无效空格

  • LENGTH(str)：获取字符串字节数（utf8mb4 中中文占 4 字节）

  • CHAR_LENGTH(str)：获取字符串字符数

  • UPPER(str)/LOWER(str)：转大小写

（2）日期函数

  • NOW()：获取当前日期时间（YYYY-MM-DD HH:MM:SS）

  • CURDATE()：获取当前日期（YYYY-MM-DD）

  • CURTIME()：获取当前时间（HH:MM:SS）

  • DATE_FORMAT(date, format)：日期格式化

    SELECT name, DATE_FORMAT(create_time, '%Y年%m月%d日') 创建日期 FROM user;
（3）数值函数

  • ROUND(num, n)：四舍五入（n 为小数位数）

  • FLOOR(num)：向下取整

  • CEIL(num)：向上取整

  • MOD(a, b)：取余（a % b）

9. DQL 完整语句

（1）示例

以下是包含 FROM/JOIN/WHERE/GROUP BY/HAVING/ORDER BY/LIMIT 的完整 DQL 语句，覆盖多表关联、过滤、分组、排序、分页全场景：

SELECT 
    d.dept_name AS 部门名称,  -- 3. 筛选展示字段（支持别名）
    u.gender AS 性别,
    COUNT(u.id) AS 人数,     -- 聚合函数统计
    AVG(u.age) AS 平均年龄
FROM 
    `user` u  -- 1. 主表（user 别名 u）
LEFT JOIN 
    `dept` d ON u.dept_id = d.id  -- 1. 关联表（dept 别名 d）
WHERE 
    u.age >= 20  -- 2. 过滤原始行数据（分组前）
GROUP BY 
    d.dept_name, u.gender  -- 4. 按部门+性别分组
HAVING 
    COUNT(u.id) > 1  -- 5. 过滤分组后的聚合结果
ORDER BY 
    平均年龄 DESC, 人数 ASC  -- 6. 按聚合字段/分组字段排序
LIMIT 
    0, 10;  -- 7. 分页（偏移量0，取前10条）

业务含义：查询所有部门中，年龄≥20 岁的用户按「部门 + 性别」分组后，组内人数 > 1 的分组，展示部门名称、性别、人数、平均年龄，并按平均年龄降序、人数升序排序，最终取前 10 条结果。

（2）DQL 执行顺序（从 FROM 开始）

MySQL 对 DQL 语句的执行顺序是先执行底层数据筛选 / 关联，再处理分组 / 聚合，最后排序 / 分页，核心顺序如下（编号对应上述示例注释）：

执行步骤关键字作用说明
1FROM/JOIN① 先加载 FROM 后的主表（user）；② 执行 JOIN 关联副表（dept），生成「笛卡尔积临时表」（关联条件 u.dept_id = d.id 过滤无效关联）。
2WHERE对第一步生成的临时表，过滤原始行数据（仅保留 u.age >= 20 的行），此时还未分组，无法使用聚合函数。
3SELECT筛选需要展示的字段（如 d.dept_name/u.gender），执行字段别名、聚合函数（COUNT/AVG），但此时仅「标记要展示的字段」，未最终计算。
4GROUP BY按指定字段（d.dept_name, u.gender）对第二步过滤后的行进行分组，同一组的行合并为一条，聚合函数开始计算（如 COUNT/AVG）。
5HAVING过滤分组后的结果（仅保留 COUNT(u.id) > 1 的分组），仅能使用「分组字段」或「聚合函数结果」作为条件。
6ORDER BY对第五步过滤后的结果集按指定字段排序（支持分组字段、聚合字段、别名），此时可使用 SELECT 中定义的别名（如「平均年龄」）。
7LIMIT对排序后的结果集进行分页，截取指定偏移量和条数的记录（最终返回给客户端的结果）。
（3）简化版执行顺序口诀（易记）

FROM 找表 → JOIN 关联 → WHERE 筛行 → SELECT 选字段 → GROUP BY 分组 → HAVING 筛组 → ORDER BY 排序 → LIMIT 分页。

（4）关键补充说明

  1. 执行顺序≠书写顺序：

    • 书写顺序：SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
    • 执行顺序：FROM → JOIN → WHERE → SELECT → GROUP BY → HAVING → ORDER BY → LIMIT（核心差异：SELECT 书写在最前，但执行在 WHERE 之后、GROUP BY 之前）。

  2. 别名的可用阶段：

    • WHERE/GROUP BY 中不能使用 SELECT 定义的别名（如 WHERE 平均年龄 > 30 报错），因为 WHERE 执行时 SELECT 还未完成字段别名映射；
    • HAVING/ORDER BY/LIMIT 中可以使用别名（如 ORDER BY 平均年龄 DESC 合法），因为这些步骤执行在 SELECT 之后。

  3. 性能优化逻辑：

    • WHERE 优先过滤（分组前减少数据量），比 HAVING 效率更高（如示例中 u.age >= 20 放在 WHERE 而非 HAVING）；
    • JOIN 时优先使用索引关联字段（如 u.dept_id/d.id 加索引），减少笛卡尔积计算量。

四、核心注意事项

  1. 关键字大小写：MySQL 关键字（SELECT、FROM、WHERE 等）不区分大小写，但建议大写（可读性更高）；表名、字段名是否区分大小写取决于操作系统（Linux 区分，Windows 不区分），建议统一小写 + 下划线命名。

  2. 字符集：创建数据库 / 表时优先指定 utf8mb4，避免中文 / 表情乱码。

  3. 事务安全：DML 操作（INSERT/UPDATE/DELETE）需注意事务控制，避免误操作导致数据丢失。

  4. 查询优化：

    • 避免 SELECT *，只查询需要的字段。

    • 大表查询加索引（WHERE 条件、JOIN 关联字段、ORDER BY 字段）。

    • 避免 LIMIT 大偏移量（如 LIMIT 100000, 10），可通过主键分页优化。

  5. 约束规范：

    • 主键必加（唯一标识记录）。

    • 唯一字段加 UNIQUE 约束（如手机号、邮箱）。

    • 外键关联确保数据一致性（需开启 InnoDB 存储引擎）。

DCL（Data Control Language，数据控制语言）（开发非重点）

DCL 用于管理 MySQL 数据库的权限和事务（核心是权限控制，事务控制也常归为 DCL 范畴），核心操作是「授权（GRANT）」和「撤权（REVOKE）」，以及设置用户密码、管理角色等。

一、核心概念

  • 用户：MySQL 中用户格式为 用户名@主机（如 root@localhost，localhost 表示仅本地访问，% 表示任意主机）；
  • 权限：分为全局权限（如 ALL PRIVILEGES）、数据库权限（如 SELECT ON db_name.*）、表权限（如 UPDATE ON db_name.table_name）、字段权限（如 SELECT (id, name) ON db_name.table_name）；
  • 角色：权限集合（MySQL 8.0+ 支持），可批量授予用户，简化权限管理。

二、常用 DCL 语句

1. 用户管理
（1）创建用户
-- 基础创建（指定密码，仅允许本地访问）
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

-- 允许任意主机访问（% 通配符，生产环境慎用）
CREATE USER 'user2'@'%' IDENTIFIED BY '123456';

-- MySQL 8.0+ 推荐加密方式（caching_sha2_password）
CREATE USER 'user3'@'192.168.%.%' IDENTIFIED WITH caching_sha2_password BY '123456';
（2）修改用户
-- 修改用户名（主机需一致）
RENAME USER 'user1'@'localhost' TO 'new_user'@'localhost';

-- 修改密码
ALTER USER 'user2'@'%' IDENTIFIED BY 'new_password';

-- 重置 root 密码（忘记密码时，跳过权限表启动 MySQL 后执行）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_password';
（3）删除用户
DROP USER IF EXISTS 'user2'@'%';

2. 权限管理

（1）授予权限（GRANT）

语法：GRANT 权限列表 ON 作用范围 TO '用户'@'主机';

-- 授予全局权限（所有数据库，仅 root 级用户使用）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

-- 授予指定数据库所有权限
GRANT ALL PRIVILEGES ON test_db.* TO 'user1'@'localhost';

-- 授予指定表的 SELECT/UPDATE 权限
GRANT SELECT, UPDATE ON test_db.user TO 'user1'@'localhost';

-- 授予指定字段的 SELECT 权限（精细化控制）
GRANT SELECT (id, name) ON test_db.user TO 'user1'@'localhost';

-- 授予权限并允许用户授权给其他用户（WITH GRANT OPTION）
GRANT SELECT ON test_db.* TO 'user2'@'%' WITH GRANT OPTION;
（2）撤销权限（REVOKE）

语法：REVOKE 权限列表 ON 作用范围 FROM '用户'@'主机';

-- 撤销指定表的 UPDATE 权限
REVOKE UPDATE ON test_db.user FROM 'user1'@'localhost';

-- 撤销所有数据库的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'user2'@'%';
（3）刷新权限（修改权限后需执行）
FLUSH PRIVILEGES;
（4）查看权限
-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'user1'@'localhost';

3. 角色管理（MySQL 8.0+）

-- 创建角色（定义权限集合）
CREATE ROLE 'dev_role'@'%', 'read_role'@'%';

-- 给角色授权
GRANT SELECT, UPDATE ON test_db.* TO 'dev_role'@'%';
GRANT SELECT ON test_db.* TO 'read_role'@'%';

-- 给用户分配角色
GRANT 'dev_role'@'%' TO 'user3'@'192.168.%.%';

-- 激活角色（用户登录后自动生效）
SET DEFAULT ROLE ALL TO 'user3'@'192.168.%.%';

-- 撤销用户的角色
REVOKE 'dev_role'@'%' FROM 'user3'@'192.168.%.%';

-- 删除角色
DROP ROLE 'read_role'@'%';

4. 事务控制（DCL 补充）

-- 设置事务自动提交（默认 ON，即执行 DML 后自动提交）
SET AUTOCOMMIT = OFF;  -- 关闭自动提交，需手动 COMMIT/ROLLBACK

-- 锁定表（MyISAM 常用，InnoDB 优先用行锁）
LOCK TABLES test_db.user READ;  -- 读锁（其他会话只能读，不能写）
LOCK TABLES test_db.user WRITE; -- 写锁（其他会话不能读/写）
UNLOCK TABLES;  -- 解锁

三、核心注意事项

  1. 权限最小化：生产环境避免授予 % 主机权限、ALL PRIVILEGES 全局权限，仅授予业务所需最小权限（如只读用户仅授 SELECT）；
  2. 密码安全：避免简单密码，MySQL 8.0+ 推荐使用 caching_sha2_password 加密方式，禁止明文存储密码；
  3. 权限生效：修改用户 / 权限后需执行 FLUSH PRIVILEGES（或重启 MySQL），否则可能不生效；
  4. 角色兼容性：MySQL 8.0 以下版本不支持角色，需直接给用户授权。

四、常用权限列表（精简版）

权限作用适用场景
ALL PRIVILEGES所有权限（除 GRANT OPTION）管理员账户
SELECT查询数据只读用户、报表查询
INSERT插入数据数据录入用户
UPDATE修改数据业务操作用户
DELETE删除数据谨慎授予，仅核心用户
CREATE创建数据库 / 表开发、测试用户
DROP删除数据库 / 表仅管理员
ALTER修改表结构开发、DBA
GRANT OPTION授予权限给其他用户仅管理员
avatar
文章
阅读
粉丝