MySQL 表结构

MySQL 表选项

• ENGINE：存储引擎(InnoDB, MyISAM等)
• AUTO_INCREMENT：自增起始值
• DEFAULT CHARSET：默认字符集
• COLLATE：默认排序规则
• COMMENT：表注释
• ROW_FORMAT：行格式

MySQL 数据类型

数值类型

• 整数类型：
  • TINYINT (-128~127)
  • SMALLINT (-32768~32767)
  • MEDIUMINT (-8388608~8388607)
  • INT/INTEGER (-2147483648~2147483647)
  • BIGINT (-2^63~2^63-1)
• 浮点类型：
  • FLOAT (单精度浮点数)
  • DOUBLE (双精度浮点数)
• 定点数类型：
  • DECIMAL(M,D) (精确小数，M是总位数，D是小数位数)

字符串类型

• CHAR(M) (定长字符串，0-255字符)
• VARCHAR(M) (变长字符串，0-65535字符)
• TEXT (长文本数据)
• BLOB (二进制数据)
• ENUM (枚举类型，单选)
• SET (集合类型，多选)

日期时间类型

• DATE (YYYY-MM-DD)
• TIME (HH:MM:SS)
• DATETIME (YYYY-MM-DD HH:MM:SS)
• TIMESTAMP (时间戳，1970-2038)
• YEAR (年份)

MySQL 列属性

• NOT NULL：列不允许为空
• DEFAULT：设置默认值
• AUTO_INCREMENT：自动递增(仅整数类型)
• COMMENT：列注释
• UNSIGNED：无符号数值(仅数值类型)
• ZEROFILL：用零填充(仅数值类型)
• CHARACTER SET：指定字符集
• COLLATE：指定排序规则

MySQL 约束

• 主键约束：PRIMARY KEY
• 外键约束：FOREIGN KEY
• 唯一约束：UNIQUE
• 检查约束（MySQL 8.0+）：CHECK
• 默认约束：DEFAULT

MySQL-操作

连接数据库

语法格式

mysql [options] [database]

选项

选项	描述	示例
-u user	指定用户名	mysql -u root
-p	提示输入密码	mysql -u root -p
-h host	连接的主机名	mysql -h 127.0.0.1
-P port	连接的端口号	mysql -P 3307
-D db_name	指定默认数据库	mysql -D testdb
-e "query"	执行SQL语句后退出	mysql -e "SHOW DATABASES;"
--skip-column-names	不显示列名	mysql --skip-column-names
-v	详细模式	mysql -v
--safe-updates	安全更新模式	mysql --safe-updates
-B	批处理模式	mysql -B
-S socket_path	指定Unix域套接字文件路径	mysql -S /var/run/mysqld/mysqld.sock
--socket=socket_path	同-S选项	mysql --socket=/tmp/mysql.sock

设置账号远程连接

grant all on *.* to root@'%' identified by '123456';

# 刷新授权表
flush privileges;

查看当前服务器登录状况

show processlist;

经典案例

# 连接到本地MySQL服务器
mysql -u root -p

# 执行SQL文件
mysql -u root -p db_name < script.sql

# 批量执行SQL语句
mysql -u root -p -e "SHOW DATABASES; USE testdb; SHOW TABLES;"

# 导出数据为CSV格式
mysql -u root -p -B -e "SELECT * FROM users" testdb | sed 's/\t/,/g' > users.csv

# 安全更新模式
mysql -u root -p --safe-updates

# 以守护进程连接
mysql -u root -S /tmp/mysql.sock

MySQL 专属管理命令

MySQL 提供了一系列专属管理命令，这些命令不属于标准 SQL，但对数据库运维至关重要。以下是主要分类和命令详解：

服务器控制命令

服务启停控制

SHUTDOWN;  -- 停止MySQL服务（需SUPER权限）

系统刷新命令

FLUSH PRIVILEGES;  -- 重新加载权限表
FLUSH TABLES WITH READ LOCK;  -- 全局读锁
FLUSH LOGS;  -- 滚动日志文件
FLUSH STATUS;  -- 重置状态计数器

复制管理命令集

主服务器命令

RESET MASTER;  -- 删除所有二进制日志
SHOW MASTER STATUS;  -- 显示主库binlog状态
PURGE BINARY LOGS TO 'mysql-bin.000010';  -- 选择性删除binlog

从服务器命令

START SLAVE [IO_THREAD|SQL_THREAD];
STOP SLAVE;
RESET SLAVE ALL;  -- 彻底重置从库复制状态
SHOW SLAVE STATUS\G  -- 详细复制状态
CHANGE MASTER TO MASTER_HOST='master_host';  -- 修改主库配置

MySQL 5.7及以下命令	MySQL 8.0+ 新命令	说明
SHOW MASTER STATUS	SHOW BINARY LOG STATUS	查看主库二进制日志状态
SHOW SLAVE STATUS	SHOW REPLICA STATUS	查看从库复制状态
START SLAVE	START REPLICA	启动复制
STOP SLAVE	STOP REPLICA	停止复制
RESET SLAVE	RESET REPLICA	重置复制配置

存储引擎管理

InnoDB 专用

SHOW ENGINE INNODB STATUS\G  -- 显示完整引擎状态
SET GLOBAL innodb_fast_shutdown=0;  -- 完全关闭模式
ALTER TABLE ... DISCARD TABLESPACE;  -- 表空间管理

MyISAM 维护

REPAIR TABLE tbl_name;  -- 修复损坏表
CHECK TABLE tbl_name FAST;  -- 快速检查
ANALYZE TABLE tbl_name;  -- 更新统计信息

性能诊断工具

性能模式控制

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' WHERE NAME LIKE '%wait%';

优化器控制

SET optimizer_switch='block_nested_loop=off';
SET GLOBAL max_seeks_for_key=1000;

会话与连接管理

连接控制

KILL CONNECTION thread_id;  -- 终止连接
KILL QUERY thread_id;  -- 仅终止查询
SHOW PROCESSLIST;  -- 查看所有连接

资源限制

SET GLOBAL max_connections=500;
SET SESSION sql_select_limit=1000;

备份与恢复命令

热备份控制

LOCK TABLES FOR BACKUP;  -- MySQL Enterprise Backup专用
UNLOCK INSTANCE;  -- 解除备份锁

克隆插件（MySQL 8.0+）

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/path/to/clone';

安全控制命令

密钥管理

ALTER INSTANCE ROTATE INNODB MASTER KEY;
CREATE ENCRYPTION KEY 'key1' IDENTIFIED BY 'password';

审计控制

SET GLOBAL audit_log_flush=ON;  -- Enterprise Audit插件

实用程序命令

1延迟执行

SELECT SLEEP(5);  -- 暂停5秒
DO SLEEP(10);  -- 无结果集暂停

变量操作

SET PERSIST max_connections=1000;  -- 持久化修改
RESET PERSIST max_connections;  -- 清除持久化设置

命令使用注意事项

1. 权限要求：

   • 90%的管理命令需要SUPER或SYSTEM_VARIABLES_ADMIN权限
   • 部分命令需要安装特定插件

2. 版本差异：

   -- MySQL 5.7 vs 8.0 命令变化示例
   /* 5.7 */ SET GLOBAL innodb_buffer_pool_dump_now=ON;
   /* 8.0 */ SET PERSIST innodb_buffer_pool_dump_now=ON;

3. 生产环境建议：

   • 使用前在测试环境验证
   • 配合SET LOG_ERROR_VERBOSITY=3;记录详细错误
   • 重要操作前执行FLUSH STATUS建立基准指标

如需了解特定命令的详细使用场景或安全注意事项，可以具体说明您的MySQL版本和使用场景。

DDL 数据定义语言

创建数据库

CREATE DATABASE database_name;
-- 带字符集和排序规则
CREATE DATABASE database_name 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
  
# 创建数据库 字符集为utf8mb4
CREATE DATABASE yun CHARACTER SET utf8mb4;

选择/使用数据库

USE database_name;

修改数据库

ALTER DATABASE database_name 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
  
# 修改数据库属性
ALTER DATABASE yun 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

删除数据库

DROP DATABASE database_name;

创建表

常见列属性

• PRIMARY KEY :主键约束,表中只能有一个,非空且唯.
• NOT NULL : 非空约束,不允许空值
• UNIQUE KEY : 唯一键约束,不允许重复值
• DEFAULT : 默认值和NOT NULL 一起使用.
• UNSIGNED : 无符号,一般是配合数字列,非负数
• COMMENT : 注释
• AUTO_INCREMENT : 自增长的列

CREATE TABLE [IF NOT EXISTS] table_name (  
field1 datatype1 [COMMENT '注释信息'],  
field2 datatype2 [COMMENT '注释信息'],  
field3 datatype3 [COMMENT '注释信息'],  
...  
) [CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name]; 

-- 用户表
CREATE TABLE user (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  age INT COMMENT '年龄',
  email VARCHAR(100) COMMENT '邮箱',
  bid int UNSIGNED  not null COMMENT '部门ID',
  xinzi int UNSIGNED  not null COMMENT '薪资',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENGINE=InnoDB;

-- 部门表
CREATE TABLE bumen (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  bname VARCHAR(50) NOT NULL COMMENT '部门名',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENGINE=InnoDB;

• CREATE TABLE： 这是创建表的关键字。

• [IF NOT EXISTS] ：这是一个可选项，用于防止在表已经存在的情况下执行创建表的操作时出现错误。如果表已经存在，并且使用了这个选项，SQL语句将不会执行，也不会报错。

• table_name： 这是你要创建的表的名称。表名必须符合数据库的命名规则，通常是以字母开头，可以包含字母、数字、下划线等字符。

• field1 datatype1 [COMMENT '注释信息']：

  • field1: 列名，即表中字段的名称。

  • datatype1: 列的数据类型，如 INT, VARCHAR(255), DATE 等。

  • [COMMENT '注释信息']：可选项，用于为字段添加注释，方便理解字段的用途。

• CHARSET=charset_name:

  • CHARSET: 指定表的字符集。字符集决定了表中存储的字符数据的编码方式。

  • charset_name: 字符集的名称，如 utf8, utf8mb4, latin1 等。

• COLLATE=collation_name：

  • COLLATE: 指定表的校验规则，即字符的比较规则。

  • collation_name: 校验规则的名称，如 utf8_general_ci, utf8mb4_general_ci等。

• ENGINE=engine_name

  • ENGINE: 指定表的存储引擎。存储引擎决定了表如何存储数据、如何支持事务等。

  • engine_name: 存储引擎的名称，如 InnoDB, MyISAM, MEMORY 等。

修改表结构

-- 添加列
ALTER TABLE table_name ADD COLUMN address VARCHAR(200);

-- 修改列
ALTER TABLE table_name MODIFY COLUMN name VARCHAR(100);

-- 重命名列
ALTER TABLE table_name CHANGE COLUMN old_name new_name VARCHAR(50);

-- 删除列
ALTER TABLE table_name DROP COLUMN age;

修改表名

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
-- 或
RENAME TABLE old_table_name TO new_table_name;

复制表

# 复制表结构
CREATE TABLE 新数据库名.表1 LIKE 原数据库名.表1;
# 复制数据（insert插入的数据是原表select查询的数据）
INSERT INTO 新数据库名.表1 SELECT * FROM 原数据库名.表1;

# 创建新表并复制内容
CREATE TABLE 新表名 [可选: 表结构定义] 
AS 
SELECT 列1, 列2, ... 
FROM 原表名 
[WHERE 条件];

删除表

DROP TABLE table_name;

索引操作

-- 创建普通索引
CREATE INDEX idx_name ON table_name(name);
-- 创建前缀索引
CREATE INDEX idx_name ON table_name(name(n));

-- 创建唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(email);

-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY(column_name);

-- 删除索引
DROP INDEX idx_name ON table_name;

外键操作

-- 添加外键
ALTER TABLE orders 
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_customer;

清空表数据

TRUNCATE TABLE table_name;

DML 数据操作语言

DML（Data Manipulation Language）是SQL中用于操作数据库中数据的语言，主要包括数据的插入（INSERT）、更新（UPDATE）、删除（DELETE）操作。

INSERT 语句（插入数据）

-- 基本语法
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
-- 插入多条数据
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...),(值1, 值2, ...);
-- 从其他表插入数据
INSERT INTO user_backup (name, age, email) SELECT name, age, email FROM user WHERE age > 30;

UPDATE 语句（更新数据）

-- 基本语法
UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... [WHERE 条件];

-- 更新单列
UPDATE user SET email = 'new_email@example.com' WHERE id = 1;

-- 更新多列
UPDATE user 
SET age = 29, xinzi = 16000 
WHERE name = '张三';

-- 使用表达式更新
UPDATE user 
SET xinzi = xinzi * 1.1 
WHERE bid = 3;

DELETE 语句（删除数据）

-- 基本语法
DELETE FROM 表名 [WHERE 条件];

-- 删除特定记录
DELETE FROM user WHERE id = 5;

-- 删除满足条件的记录
DELETE FROM user WHERE age < 25;

-- 删除所有记录（慎用！）
DELETE FROM user;

DQL 数据查询语言

查询数据库服务器信息

-- 查看主服务器状态
SHOW MASTER STATUS

-- 查看从服务器状态
SHOW SLAVE STATUS\G

-- 查看所有二进制日志文件
SHOW BINARY LOGS;

-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000003';

返回字段说明

字段名	说明
File	当前正在使用的二进制日志文件名
Position	当前二进制日志文件中的写入位置（偏移量）
Binlog_Do_DB	配置中指定要记录二进制日志的数据库
Binlog_Ignore_DB	配置中指定不记录二进制日志的数据库
Executed_Gtid_Set	已执行的GTID集合（如果启用了GTID）

查询系统变量

# 查看所有系统变量
SHOW VARIABLES;

# 查看系统变量 
SELECT @@变量名;

# 查看特定模式的变量
SHOW VARIABLES LIKE '%timeout%';

连接相关变量

• @@port- 服务器监听端口
• @@hostname- 服务器主机名
• @@socket- Unix socket 文件路径
• @@version- MySQL 版本
• @@version_comment- 版本注释
• @@version_compile_machine- 编译机器的架构
• @@version_compile_os- 编译操作系统

服务器配置变量

• @@datadir- 数据目录路径
• @@basedir- 基础安装目录
• @@tmpdir- 临时目录
• @@pid_file- PID 文件路径
• @@plugin_dir- 插件目录

性能相关变量

• @@max_connections- 最大连接数
• @@max_allowed_packet- 最大允许数据包大小
• @@innodb_buffer_pool_size- InnoDB 缓冲池大小
• @@query_cache_size- 查询缓存大小

字符集和排序规则

• @@character_set_server- 服务器默认字符集
• @@collation_server- 服务器默认排序规则
• @@character_set_client- 客户端字符集
• @@character_set_connection- 连接字符集
• @@character_set_database- 数据库字符集
• @@character_set_results- 结果集字符集

其他常用变量

• @@auto_increment_increment- 自增步长
• @@auto_increment_offset- 自增起始值
• @@sql_mode- SQL 模式设置
• @@time_zone- 时区设置
• @@system_time_zone- 系统时区
• @@tx_isolation- 事务隔离级别(MySQL 5.7)
• @@transaction_isolation- 事务隔离级别(MySQL 8.0+)
• @@wait_timeout- 非交互连接超时时间
• @@interactive_timeout- 交互连接超时时间

查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看特定数据库创建语句
SHOW CREATE DATABASE database_name;

-- 检查新数据库是否存在
SHOW DATABASES LIKE '数据库名';

查看表

-- 查看当前数据库所有表
SHOW TABLES;

-- 检查表是否存在
SHOW TABLES LIKE 'table_name';

-- 查看表结构
DESCRIBE table_name;
DESC table_name;

-- 查看建表语句，显示完整的建表语句，包括所有约束、索引和表选项
SHOW CREATE TABLE table_name;

-- 展示字段结构
SHOW COLUMNS FROM 表名;

-- 显示更详细的列信息，包括注释和权限。
SHOW FULL COLUMNS FROM 表名;

方法	显示信息详细程度	适合场景
DESCRIBE/DESC	基本列信息	快速查看表结构
SHOW CREATE TABLE	完整建表语句	需要复制表结构或查看完整定义
SHOW COLUMNS	基本列信息	类似DESCRIBE
INFORMATION_SCHEMA	可定制详细程度	程序化获取表结构信息
SHOW FULL COLUMNS	非常详细	需要查看列注释等额外信息
SHOW TABLE STATUS	表状态信息	查看表大小、引擎等信息

DESC 输出字段说明

1. Field - 字段名称（列名）
2. Type - 字段数据类型，包括：
   • 基本类型：INT, VARCHAR, CHAR, TEXT, DATETIME 等
   • 可能包含长度或精度：如 VARCHAR(255), DECIMAL(10,2)
3. Null - 是否允许 NULL 值：
   • YES 表示允许 NULL
   • NO 表示不允许 NULL
4. Key - 索引信息：
   • PRI 表示主键（PRIMARY KEY）
   • UNI 表示唯一键（UNIQUE KEY）
   • MUL 表示普通索引（可以重复）
   • 空表示没有索引
5. Default - 默认值：
   • 显示字段的默认值
   • NULL 表示默认为 NULL
   • 空表示没有默认值
6. Extra - 额外信息：
   • auto_increment 表示自增字段
   • on update CURRENT_TIMESTAMP 表示时间戳自动更新
   • 其他特殊属性

查看索引

-- 基本格式
SHOW INDEX FROM table_name;

SELECT 语句（查询数据）

-- 基本语法
SELECT 列1, 列2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[HAVING 分组条件]
[ORDER BY 排序列 [ASC|DESC]]
[LIMIT 数量];

-- 查询所有列
SELECT * FROM table_name;

-- 查询特定列
SELECT column1, column2 FROM table_name;

-- 使用别名
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

条件查询 (WHERE)

-- 基本条件
SELECT * FROM table_name WHERE column1 = 'value';

-- 比较运算符
SELECT * FROM table_name WHERE age > 18;
SELECT * FROM table_name WHERE salary <= 5000;

-- 逻辑运算符
SELECT * FROM table_name WHERE age > 18 AND gender = 'M';
SELECT * FROM table_name WHERE status = 'active' OR status = 'pending';
SELECT * FROM table_name WHERE NOT status = 'inactive';

-- BETWEEN (范围)
SELECT * FROM table_name WHERE age BETWEEN 20 AND 30;

-- IN (包涵)
SELECT * FROM table_name WHERE id IN (1, 3, 5);
SELECT * FROM table_name WHERE department IN ('IT', 'HR', 'Finance');

-- LIKE (模糊查询)
SELECT * FROM table_name WHERE name LIKE '张%';  -- 张开头
SELECT * FROM table_name WHERE name LIKE '%张%'; -- 包含张
SELECT * FROM table_name WHERE name LIKE '_张%'; -- 第二个字是张，_代表任意一个字符

排序 (ORDER BY)

-- 单列排序
SELECT * FROM table_name ORDER BY column1 ASC;  -- 升序(默认)
SELECT * FROM table_name ORDER BY column1 DESC; -- 降序

-- 多列排序
SELECT * FROM table_name ORDER BY column1 DESC, column2 ASC;

分组 (GROUP BY)

-- 基本分组
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- 分组后筛选 (HAVING)
SELECT department, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department 
HAVING avg_salary > 5000;

聚合函数

SELECT COUNT(*) FROM table_name;  -- 计数
SELECT SUM(salary) FROM employees; -- 求和
SELECT AVG(age) FROM customers;    -- 平均值
SELECT MAX(price) FROM products;   -- 最大值
SELECT MIN(price) FROM products;   -- 最小值

多表链接

SELECT a.*,b.* FROM table1 AS a,table2 as b WHERE a.id=b.id;

连接查询 (JOIN)

-- 内连接
SELECT a.*, b.* 
FROM table_a a 
INNER JOIN table_b b ON a.id = b.a_id;

-- 左连接
SELECT a.*, b.* 
FROM table_a a 
LEFT JOIN table_b b ON a.id = b.a_id;

-- 右连接
SELECT a.*, b.* 
FROM table_a a 
RIGHT JOIN table_b b ON a.id = b.a_id;

-- 全外连接 (MySQL不支持，可用UNION实现)
SELECT a.*, b.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id
UNION
SELECT a.*, b.* FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id;

子查询

-- WHERE子句中的子查询
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- FROM子句中的子查询
SELECT dept.name, emp_count.count 
FROM departments dept
JOIN (SELECT department_id, COUNT(*) as count FROM employees GROUP BY department_id) emp_count
ON dept.id = emp_count.department_id;

-- IN子查询
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE name LIKE '%电子%');

分页查询 (LIMIT)

-- 基本分页
SELECT * FROM table_name LIMIT 10;      -- 前10条
SELECT * FROM table_name LIMIT 5, 10;   -- 从第6条开始，取10条(第6-15条)

-- 现代分页写法(MySQL 8.0+)
SELECT * FROM table_name LIMIT 10 OFFSET 20; -- 跳过20条，取10条

联合查询 (UNION)

-- 合并两个查询结果(去重)
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- 合并两个查询结果(不去重)
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

窗口函数 (MySQL 8.0+)

-- 排名函数
SELECT 
  name, 
  salary,
  RANK() OVER (ORDER BY salary DESC) as salary_rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as salary_dense_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

-- 分区窗口函数
SELECT 
  department,
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

公用表表达式 (CTE, MySQL 8.0+)

WITH dept_stats AS (
  SELECT 
    department_id, 
    AVG(salary) as avg_salary,
    COUNT(*) as emp_count
  FROM employees
  GROUP BY department_id
)
SELECT 
  d.name as department_name,
  ds.avg_salary,
  ds.emp_count
FROM departments d
JOIN dept_stats ds ON d.id = ds.department_id;

条件判断

-- CASE WHEN
SELECT 
  name,
  salary,
  CASE 
    WHEN salary > 10000 THEN '高薪'
    WHEN salary > 5000 THEN '中薪'
    ELSE '低薪'
  END as salary_level
FROM employees;

-- IF函数
SELECT name, IF(salary > 5000, '高薪', '低薪') as salary_level FROM employees;

日期时间处理

-- 当前日期时间
SELECT NOW(), CURDATE(), CURTIME();

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT DATEDIFF('2023-12-31', '2023-01-01');

JSON处理 (MySQL 5.7+)

-- 创建JSON字段
CREATE TABLE products (
  id INT PRIMARY KEY,
  details JSON
);

-- 查询JSON字段
SELECT id, details->'$.price' as price FROM products;
SELECT id, JSON_EXTRACT(details, '$.price') as price FROM products;

-- 更新JSON字段
UPDATE products SET details = JSON_SET(details, '$.price', 99.99) WHERE id = 1;

DCL 数据控制语言

DCL（Data Control Language）是SQL中用于控制数据库访问权限和安全性的语言类别。

用户管理

-- 基本语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 示例：
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'Admin@123';
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'Remote@123';

-- 使用 mysql_native_password 插件
CREATE USER 'user1'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

-- 使用 caching_sha2_password 插件（MySQL 8.0+ 默认）
CREATE USER 'user2'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'password456';

-- 密码立即过期（首次登录需修改）
CREATE USER 'temp_user'@'localhost' 
IDENTIFIED BY 'TempPass123' PASSWORD EXPIRE;

-- 密码永不过期
CREATE USER 'longterm_user'@'localhost' 
IDENTIFIED BY 'LongTermPass456' PASSWORD EXPIRE NEVER;

注：grant直接授权创建仅限于MySQL8.0之前的版本，8.0之后的版本都需要先创建用户，在进行授权。

MySQL 通配符：

• _：任意单个字符
• %：任意长度的任意字符

查询用户

-- 查看用户列表
SELECT User, Host FROM mysql.user;

-- 查看用户权限
SHOW GRANTS FOR '用户名'@'主机名';

修改用户密码

-- MySQL 5.7.6+ 和 8.0+ 语法
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

-- 旧版本语法
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

修改用户主机名

-- 基本语法
RENAME USER '旧用户名'@'旧主机名' TO '新用户名'@'新主机名';
-- 将用户从localhost改为允许从192.168.1.%网段连接
RENAME USER 'app_user'@'localhost' TO 'app_user'@'192.168.1.%';

-- 直接更新mysql.user
UPDATE mysql.user SET Host='新主机名' WHERE User='用户名' AND Host='旧主机名';
FLUSH PRIVILEGES;

删除用户

DROP USER '用户名'@'主机名';

-- 同时删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';

权限管理

授予权限

GRANT privilege_type ON database_name.table_name TO 'username'@'host';
-- 示例：
GRANT SELECT, INSERT ON mydb.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

撤销权限

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';
-- 示例：
REVOKE DELETE ON mydb.users FROM 'user1'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'%';

刷新权限

FLUSH PRIVILEGES;
-- 在修改权限后执行，使更改立即生效

权限类型

权限	说明
ALL PRIVILEGES	所有权限
CREATE	创建数据库/表
DROP	删除数据库/表
ALTER	修改表结构
SELECT	查询数据
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据
INDEX	创建/删除索引
CREATE VIEW	创建视图
SHOW DATABASES	查看数据库列表
GRANT OPTION	授予权限的权限

权限查看语句

查看用户权限

SHOW GRANTS FOR 'username'@'host';
-- 示例：
SHOW GRANTS FOR 'admin'@'localhost';

查看当前用户权限

SHOW GRANTS;

角色管理（MySQL 8.0+）

创建角色

CREATE ROLE 'role_name';
-- 示例：
CREATE ROLE 'read_only';
CREATE ROLE 'data_operator';

授予角色权限

GRANT SELECT ON *.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'data_operator';

将角色授予用户

GRANT 'role_name' TO 'username'@'host';
-- 示例：
GRANT 'read_only' TO 'user1'@'localhost';

激活角色

SET DEFAULT ROLE 'role_name' TO 'username'@'host';
-- 或为当前会话激活：
SET ROLE 'role_name';