D:\MySQL\8.0）。Root@1234）。bashyum install -y wget
bashwget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
bashrpm -ivh mysql80-community-release-el7-3.noarch.rpm yum install -y mysql-community-server
bashsystemctl start mysqld
systemctl enable mysqld
bash# 查看初始密码
grep 'temporary password' /var/log/mysqld.log
# 登录 MySQL 并修改密码
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@1234';
mysql -u 用户名 -p 登录（适合熟悉 SQL 命令的场景）。sql-- 方式1：CREATE SCHEMA（推荐，语义更清晰）
CREATE SCHEMA test_db
DEFAULT CHARACTER SET utf8mb4 -- 支持 emoji 表情，比 utf8 更全面
DEFAULT COLLATE utf8mb4_general_ci; -- 不区分大小写（utf8mb4_bin 区分大小写）
-- 方式2：CREATE DATABASE（与方式1等价）
CREATE DATABASE IF NOT EXISTS test_db -- IF NOT EXISTS 避免重复创建报错
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
utf8mb4 是 MySQL 推荐的字符集，支持所有 Unicode 字符（包括 emoji），替代旧版 utf8（仅支持部分字符）。sql-- 查看所有数据库
SHOW DATABASES;
-- 查看当前使用的数据库
SELECT DATABASE();
-- 查看数据库创建语句
SHOW CREATE DATABASE test_db;
sqlUSE test_db; -- 切换到 test_db 数据库（后续操作默认在此数据库下）
sqlDROP DATABASE IF EXISTS test_db; -- IF EXISTS 避免删除不存在的数据库报错
sql-- 语法：CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
CREATE USER 'test_user'@'%' -- 主机 % 表示允许所有远程主机访问
IDENTIFIED BY 'Test@123456'; -- 密码需满足：8位+大小写+数字+特殊字符
-- 仅允许本地访问（安全性更高）
CREATE USER 'local_user'@'localhost'
IDENTIFIED BY 'Local@4321';
-- 允许指定IP段访问（生产环境推荐）
CREATE USER 'dev_user'@'192.168.1.%' -- 仅允许 192.168.1 网段访问
IDENTIFIED BY 'Dev@5678';
%：匹配所有远程主机（需开放 3306 端口，谨慎使用）。localhost：仅允许数据库所在服务器本地访问。192.168.1.%：匹配指定网段（如 192.168.1.100、192.168.1.200）。192.168.1.10：仅允许指定 IP 访问。sql-- 查看所有用户（MySQL 8.0+）
SELECT user, host FROM mysql.user;
-- 查看用户权限
SHOW GRANTS FOR 'test_user'@'%';
sql-- 方式1：ALTER USER（推荐，MySQL 8.0+）
ALTER USER 'test_user'@'%' IDENTIFIED BY 'NewTest@6789';
-- 方式2：SET PASSWORD（兼容旧版本）
SET PASSWORD FOR 'test_user'@'%' = PASSWORD('NewTest@6789');
sql-- 语法：GRANT 权限 ON 数据库.表 TO '用户名'@'主机';
GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'test_user'@'%'; -- 增删改查权限
-- 授予创建/修改/删除表权限（谨慎使用）
GRANT CREATE, ALTER, DROP ON test_db.* TO 'test_user'@'%';
-- 授予所有权限（不推荐，仅测试环境）
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'%';
-- 授予所有数据库的查询权限（高危，避免使用）
GRANT SELECT ON *.* TO 'test_user'@'%';
-- 授权后必须刷新权限
FLUSH PRIVILEGES;
DROP 权限）。sql-- 回收所有权限
REVOKE ALL PRIVILEGES ON test_db.* FROM 'test_user'@'%';
-- 精准回收 DELETE 权限
REVOKE DELETE ON test_db.* FROM 'test_user'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
sql-- 方式1：DROP USER（推荐，自动匹配主机）
DROP USER 'test_user'@'%';
-- 方式2：DELETE 语句（需指定 host）
DELETE FROM mysql.user WHERE user='test_user' AND host='%';
-- 刷新权限
FLUSH PRIVILEGES;
host 一致，否则删除失败。创建表前需指定字段的数据类型，MySQL 常用数据类型如下：
| 类型分类 | 具体类型 | 说明 |
|---|---|---|
| 整数型 | TINYINT(1) | 微小整数（-128 |
| INT(11) | 普通整数（-2147483648~2147483647），适合存储ID、数量等 | |
| BIGINT(20) | 大整数（-9e18~9e18），适合存储超大ID（如订单号） | |
| 字符串型 | VARCHAR(255) | 可变长度字符串（节省空间），适合存储用户名、标题等（长度按需设置） |
| CHAR(10) | 固定长度字符串（查询快），适合存储手机号、身份证号等固定长度数据 | |
| TEXT | 长文本（最大 65535 字节），适合存储文章内容、备注等 | |
| 日期时间型 | DATETIME | 日期+时间（格式：YYYY-MM-DD HH:MM，范围 1000-01-01 ~ 9999-12-31） |
| DATE | 仅日期（YYYY-MM-DD），适合存储生日、注册日期等 | |
| TIMESTAMP | 时间戳（自动同步当前时间，范围 1970-01-01 ~ 2038-01-19） | |
| 浮点型 | DECIMAL(10,2) | 精确小数（10位数字，2位小数），适合存储金额、价格等（避免浮点误差） |
| FLOAT(8,2) | 单精度浮点型（存在精度误差，不推荐存储金额） |
sqlUSE test_db; -- 切换到目标数据库
-- 创建用户表（user）
CREATE TABLE IF NOT EXISTS `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT, -- 自增ID（主键）
`username` VARCHAR(50) NOT NULL COMMENT '用户名', -- 非空，备注说明
`password` VARCHAR(100) NOT NULL COMMENT '密码（加密存储）',
`phone` CHAR(11) DEFAULT NULL COMMENT '手机号', -- 允许为空，默认值 NULL
`gender` TINYINT(1) DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女',
`balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT '账户余额',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`), -- 主键约束（唯一标识记录）
UNIQUE KEY `uk_username` (`username`), -- 唯一约束（用户名不可重复）
KEY `idx_phone` (`phone`) -- 普通索引（优化手机号查询）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
AUTO_INCREMENT：自增（仅用于整数型主键，每次插入自动+1）。COMMENT：字段/表的备注说明（增强可读性）。DEFAULT：默认值（如性别默认 0，余额默认 0.00）。ENGINE=InnoDB：MySQL 8.0 默认存储引擎，支持事务、索引、外键等。sql-- 查看数据库中所有表
SHOW TABLES;
-- 查看表结构（字段、类型、约束等）
DESC `user`; -- 简写
-- 或
SHOW COLUMNS FROM `user`;
-- 查看表创建语句
SHOW CREATE TABLE `user`;
sql-- 给 user 表添加 email 字段
ALTER TABLE `user`
ADD COLUMN `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱' AFTER `phone`;
-- AFTER `phone`：指定字段位置在 phone 之后（不指定则默认在最后）
sql-- 修改 email 字段类型和默认值
ALTER TABLE `user`
MODIFY COLUMN `email` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '邮箱（非空）';
-- 修改字段名（同时修改类型）
ALTER TABLE `user`
CHANGE COLUMN `email` `user_email` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '用户邮箱';
MODIFY 仅修改字段类型/约束，CHANGE 可修改字段名+类型/约束。sql-- 删除 user_email 字段
ALTER TABLE `user` DROP COLUMN `user_email`;
sql-- 将 user 表改为 sys_user
ALTER TABLE `user` RENAME TO `sys_user`;
sql-- 删除表（不可逆，谨慎！）
DROP TABLE IF EXISTS `sys_user`;
-- 删除所有表数据（保留表结构，不可逆）
TRUNCATE TABLE `sys_user`;
DROP TABLE 删除表结构+数据，TRUNCATE TABLE 仅删除数据（速度比 DELETE FROM 表 快）。适合存储半结构化数据（如用户配置、日志、API响应）。
sql-- 创建含 JSON 字段的表
CREATE TABLE user_profile (
id INT PRIMARY KEY,
info JSON COMMENT '用户扩展信息'
);
-- 插入 JSON 数据
INSERT INTO user_profile VALUES
(1, '{"age": 25, "hobbies": ["reading", "coding"], "address": {"city": "Beijing"}}');
-- 查询 JSON 字段
SELECT
info->>'$.age' AS age, -- 提取字符串（自动去引号）
JSON_EXTRACT(info, '$.hobbies[0]') AS first_hobby,
info->'$.address.city' AS city -- 提取对象（带引号）
FROM user_profile;
-- 修改 JSON
UPDATE user_profile
SET info = JSON_SET(info, '$.age', 26, '$.married', true)
WHERE id = 1;
-- 创建虚拟列 + 索引（加速 JSON 查询）
ALTER TABLE user_profile
ADD COLUMN age_virtual INT GENERATED ALWAYS AS (info->>'$.age') STORED,
ADD INDEX idx_age (age_virtual);
注意：频繁查询的 JSON 字段建议提取为虚拟列并建索引。
sql-- 方式1：指定所有字段（顺序与表结构一致）
INSERT INTO `sys_user` (username, password, phone, gender, balance)
VALUES ('zhangsan', 'Zhang@1234', '13800138000', 1, 100.00);
-- 方式2：省略字段（需按表结构顺序插入所有值，不推荐）
INSERT INTO `sys_user`
VALUES (NULL, 'lisi', 'Li@4321', '13900139000', 2, 200.00, NOW(), NOW());
-- 主键自增可填 NULL，会自动生成
-- 方式3：指定部分字段（未指定的字段取默认值/NULL）
INSERT INTO `sys_user` (username, password, gender)
VALUES ('wangwu', 'Wang@5678', 0);
sqlINSERT INTO `sys_user` (username, password, phone, gender, balance)
VALUES
('zhaoliu', 'Zhao@6789', '13700137000', 1, 300.00),
('qianqi', 'Qian@9876', '13600136000', 2, 400.00);
sql-- 查询所有字段（不推荐，性能差，字段变更会影响结果）
SELECT * FROM `sys_user`;
-- 查询指定字段
SELECT username, phone, balance FROM `sys_user`;
-- 别名（简化字段名，增强可读性）
SELECT
username AS '用户名',
phone AS '手机号',
balance + 100 AS '余额+100' -- 计算表达式
FROM `sys_user`;
-- 去重查询（DISTINCT）
SELECT DISTINCT gender FROM `sys_user`; -- 查询所有不重复的性别
sql-- 等于（=）
SELECT * FROM `sys_user` WHERE gender = 1; -- 查询男性用户
-- 不等于（!= 或 <>）
SELECT * FROM `sys_user` WHERE balance != 100.00;
-- 范围查询（BETWEEN...AND...）
SELECT * FROM `sys_user` WHERE balance BETWEEN 200 AND 500; -- 余额 200~500
-- 模糊查询（LIKE）
SELECT * FROM `sys_user` WHERE username LIKE 'zhang%'; -- 用户名以 zhang 开头
SELECT * FROM `sys_user` WHERE phone LIKE '%138%'; -- 手机号包含 138
-- 空值查询（IS NULL / IS NOT NULL）
SELECT * FROM `sys_user` WHERE phone IS NULL; -- 查询未填手机号的用户
-- 多条件查询（AND / OR / NOT）
SELECT * FROM `sys_user` WHERE gender = 2 AND balance > 300; -- 女性且余额>300
sql-- 更新单条数据（必须加 WHERE，否则更新所有记录！）
UPDATE `sys_user`
SET balance = 250.00, update_time = NOW()
WHERE username = 'lisi';
-- 更新多条数据
UPDATE `sys_user`
SET balance = balance + 50 -- 余额增加 50
WHERE gender = 1; -- 所有男性用户
UPDATE 语句必须加 WHERE 条件，否则会更新表中所有记录，生产环境务必谨慎！sql-- 删除单条数据
DELETE FROM `sys_user` WHERE username = 'qianqi';
-- 删除多条数据
DELETE FROM `sys_user` WHERE balance < 100; -- 删除余额<100的用户
-- 删除所有数据（保留表结构，速度慢，推荐用 TRUNCATE）
DELETE FROM `sys_user`;
DELETE：逐条删除记录，支持 WHERE 条件，可回滚（事务中）。TRUNCATE：直接清空表数据，不记录日志，速度快，不可回滚。sql-- 升序排列（ASC，默认）
SELECT * FROM `sys_user` ORDER BY balance ASC; -- 余额从小到大
-- 降序排列（DESC）
SELECT * FROM `sys_user` ORDER BY create_time DESC; -- 按创建时间倒序（最新的在前）
-- 多字段排序
SELECT * FROM `sys_user` ORDER BY gender ASC, balance DESC; -- 先按性别升序，再按余额降序
sql-- 基础分页：LIMIT 起始索引, 每页条数（起始索引从 0 开始）
SELECT * FROM `sys_user` LIMIT 0, 2; -- 第 1 页，每页 2 条（索引 0~1）
SELECT * FROM `sys_user` LIMIT 2, 2; -- 第 2 页，每页 2 条（索引 2~3）
-- MySQL 8.0+ 简化分页（OFFSET 偏移量）
SELECT * FROM `sys_user` LIMIT 2 OFFSET 2; -- 等价于 LIMIT 2, 2
常用聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()（最大值）、MIN()（最小值）。
sql-- 统计男性用户数量
SELECT gender, COUNT(*) AS '男性用户数' FROM `sys_user` WHERE gender = 1 GROUP BY gender;
-- 按性别分组，统计每组用户数和平均余额
SELECT
gender,
COUNT(*) AS '用户数',
AVG(balance) AS '平均余额'
FROM `sys_user`
GROUP BY gender;
-- 过滤分组结果（HAVING，区别于 WHERE）
SELECT
gender,
SUM(balance) AS '总余额'
FROM `sys_user`
GROUP BY gender
HAVING SUM(balance) > 500; -- 只显示总余额>500的分组
WHERE 过滤行数据（分组前），HAVING 过滤分组结果（分组后），HAVING 可使用聚合函数。假设有两张表：
sys_user（用户表）：id、username、dept_id（部门ID）sys_dept（部门表）：id（部门ID）、dept_name（部门名称）sql-- 查询用户及其所属部门（仅显示有部门的用户）
SELECT
u.username,
d.dept_name
FROM `sys_user` u -- 表别名 u
INNER JOIN `sys_dept` d -- 表别名 d
ON u.dept_id = d.id; -- 连接条件（用户表的部门ID = 部门表的ID）
sql-- 查询所有用户及其所属部门（无部门的用户也显示，部门名称为 NULL）
SELECT
u.username,
d.dept_name
FROM `sys_user` u
LEFT JOIN `sys_dept` d
ON u.dept_id = d.id;
sql-- 查询所有部门及其下的用户（无用户的部门也显示，用户名为 NULL）
SELECT
u.username,
d.dept_name
FROM `sys_user` u
RIGHT JOIN `sys_dept` d
ON u.dept_id = d.id;
子查询是嵌套在主查询中的查询语句，分为：
sql-- 查询余额大于平均余额的用户
SELECT * FROM `sys_user`
WHERE balance > (SELECT AVG(balance) FROM `sys_user`);
sql-- 查询部门名称为「技术部」的所有用户
SELECT * FROM `sys_user`
WHERE dept_id IN (SELECT id FROM `sys_dept` WHERE dept_name = '技术部');
MySQL 8.0+ 引入了强大的窗口函数，用于解决排名、累计求和、移动平均等复杂分析需求。
sql-- 示例：用户余额排名（RANK vs DENSE_RANK vs ROW_NUMBER）
SELECT
username,
balance,
ROW_NUMBER() OVER (ORDER BY balance DESC) AS row_num, -- 行号（无并列）
RANK() OVER (ORDER BY balance DESC) AS rank_num, -- 跳跃排名（有并列）
DENSE_RANK() OVER (ORDER BY balance DESC) AS dense_rank -- 连续排名（有并列但不跳）
FROM sys_user;
-- 示例：按性别分组内的余额排名
SELECT
gender,
username,
balance,
RANK() OVER (PARTITION BY gender ORDER BY balance DESC) AS rank_in_gender
FROM sys_user;
-- 示例：累计余额（前缀和）
SELECT
username,
balance,
SUM(balance) OVER (ORDER BY create_time ROWS UNBOUNDED PRECEDING) AS running_total
FROM sys_user
ORDER BY create_time;
-- 示例：移动平均（最近3条记录的平均余额）
SELECT
username,
balance,
AVG(balance) OVER (
ORDER BY create_time
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM sys_user;
说明：
OVER()定义窗口范围。PARTITION BY类似GROUP BY，但不聚合行。ROWS/RANGE控制窗口滑动方式。- 常用函数：
ROW_NUMBER(),RANK(),DENSE_RANK(),LEAD(),LAG(),FIRST_VALUE(),SUM(),AVG()等均可作为窗口函数使用。
替代子查询，提升可读性，支持递归查询（如组织架构、树形菜单）。
sql-- 非递归 CTE：简化多层嵌套
WITH high_balance_users AS (
SELECT id, username, balance
FROM sys_user
WHERE balance > 200
)
SELECT * FROM high_balance_users
WHERE username LIKE 'z%';
-- 递归 CTE：查询部门层级（假设 dept 表有 parent_id）
WITH RECURSIVE dept_tree AS (
-- 锚点：顶级部门
SELECT id, dept_name, parent_id, 0 AS level
FROM sys_dept
WHERE parent_id IS NULL
UNION ALL
-- 递归：子部门
SELECT d.id, d.dept_name, d.parent_id, dt.level + 1
FROM sys_dept d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;
优势：逻辑清晰、可复用、支持递归（MySQL 8.0+）。
数据完整性约束用于保证数据的准确性和一致性，避免脏数据。
sqlCREATE TABLE `sys_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`) -- 主键约束
);
sqlCREATE TABLE `sys_user` (
`username` VARCHAR(50) NOT NULL,
UNIQUE KEY `uk_username` (`username`) -- 用户名唯一
);
sqlCREATE TABLE `sys_user` (
`password` VARCHAR(100) NOT NULL -- 密码非空
);
sqlCREATE TABLE `sys_user` (
`gender` TINYINT(1) DEFAULT 0 -- 性别默认 0（未知）
);
sql-- 部门表（主表）
CREATE TABLE `sys_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`dept_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
);
-- 用户表（从表），dept_id 关联部门表的 id
CREATE TABLE `sys_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`dept_id` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`dept_id`) REFERENCES `sys_dept` (`id`) -- 外键约束
ON DELETE SET NULL -- 主表记录删除时，从表外键字段设为 NULL
ON UPDATE CASCADE -- 主表主键更新时，从表外键字段同步更新
);
ON DELETE/ON UPDATE 指定从表行为（避免数据不一致）。| 索引类型 | 说明 | 适用场景 |
|---|---|---|
| 主键索引（PRIMARY KEY） | 默认自动创建，基于主键字段，非空唯一 | 按主键查询（如 WHERE id=1） |
| 唯一索引（UNIQUE） | 基于唯一约束字段，字段值唯一 | 按唯一字段查询（如 WHERE username='zhangsan'） |
| 普通索引（INDEX） | 最常用，无约束，可重复 | 频繁查询的非唯一字段（如 WHERE phone='13800138000'） |
| 联合索引（复合索引） | 基于多个字段的索引（如 INDEX idx_name_phone (username, phone)） | 多字段联合查询（如 WHERE username='zhangsan' AND phone='13800138000'） |
| 全文索引（FULLTEXT） | 用于文本字段的模糊查询（如 TEXT 类型） | 文章内容、备注等长文本的关键词搜索 |
sql-- 1. 创建表时创建索引（推荐）
CREATE TABLE `sys_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`phone` CHAR(11) DEFAULT NULL,
PRIMARY KEY (`id`), -- 主键索引
UNIQUE KEY `uk_username` (`username`), -- 唯一索引
INDEX `idx_phone` (`phone`), -- 普通索引
INDEX `idx_username_phone` (`username`, `phone`) -- 联合索引
);
-- 2. 给已有表创建索引
CREATE INDEX `idx_balance` ON `sys_user` (`balance`); -- 普通索引
CREATE UNIQUE INDEX `uk_email` ON `sys_user` (`email`); -- 唯一索引
CREATE FULLTEXT INDEX `ft_content` ON `article` (`content`); -- 全文索引
sql-- 查看表的所有索引
SHOW INDEX FROM `sys_user`;
-- 删除索引
DROP INDEX `idx_balance` ON `sys_user`;
-- 删除唯一索引（与普通索引删除语法一致）
DROP INDEX `uk_email` ON `sys_user`;
联合索引遵循「最左前缀原则」：
idx_username_phone (username, phone) 仅对以下查询生效：
WHERE username='zhangsan'（匹配最左前缀）WHERE username='zhangsan' AND phone='13800138000'（匹配所有前缀）WHERE phone='13800138000'（跳过最左前缀）WHERE username LIKE '%zhangsan'（左模糊匹配）避免在索引字段上做运算：
sql-- 索引失效（balance 是索引字段，做了 +10 运算）
SELECT * FROM `sys_user` WHERE balance + 10 > 200;
-- 优化后（索引生效）
SELECT * FROM `sys_user` WHERE balance > 190;
避免使用 OR 连接非索引字段：
sql-- 索引失效（phone 是索引，email 非索引）
SELECT * FROM `sys_user` WHERE phone='13800138000' OR email='test@xxx.com';
小表无需索引：数据量少（如 < 1000 条）时，全表扫描比索引查询更快。
SELECT *，只查询需要的字段（减少数据传输和内存占用）。WHERE id > 上一页最大ID 替代 LIMIT 偏移量, 条数（避免全表扫描）：
sql-- 优化前（偏移量越大，速度越慢）
SELECT * FROM `sys_user` LIMIT 10000, 20;
-- 优化后（索引生效，速度快）
SELECT * FROM `sys_user` WHERE id > 10000 LIMIT 20;
EXPLAIN 分析 SQL 执行计划：
sqlEXPLAIN SELECT * FROM `sys_user` WHERE username='zhangsan';
type 字段：ALL（全表扫描，需优化）、ref（索引匹配，良好）、eq_ref（主键/唯一索引匹配，最优）。MySQL 8.0.18+ 支持
EXPLAIN ANALYZE，显示实际执行时间、循环次数等。
sqlEXPLAIN ANALYZE
SELECT * FROM sys_user WHERE balance > 100;
输出包含：
用途：精准定位慢查询瓶颈。
适用于大表（千万级以上），按时间、ID 范围分区，提升查询和维护效率。
sql-- 按年份分区订单表
CREATE TABLE orders (
id BIGINT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
-- 查询时自动剪枝（只扫描 p2024 分区）
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
适用场景：日志表、订单表、历史数据归档。
事务是一组 SQL 操作的集合，要么全部执行成功，要么全部执行失败（原子性），用于保证数据一致性（如转账、订单创建等场景）。
MySQL 中，默认每条 SQL 都是一个独立事务（自动提交），可通过以下语句手动控制事务：
sql-- 1. 开启事务（关闭自动提交）
START TRANSACTION; -- 或 BEGIN;
-- 2. 执行 SQL 操作（如转账：A 给 B 转 50 元）
UPDATE `sys_user` SET balance = balance - 50 WHERE username = 'zhangsan';
UPDATE `sys_user` SET balance = balance + 50 WHERE username = 'lisi';
-- 3. 提交事务（所有操作生效，持久化）
COMMIT;
-- 3. 回滚事务（若执行失败，撤销所有操作，恢复到事务开始前状态）
-- ROLLBACK;
事务隔离级别用于控制并发事务之间的干扰，MySQL 支持 4 种隔离级别（默认 REPEATABLE READ）：
| 隔离级别 | 说明 | 解决的问题 | 可能的问题 |
|---|---|---|---|
| 读未提交（Read Uncommitted） | 允许读取未提交的事务数据 | 无 | 脏读、不可重复读、幻读 |
| 读已提交（Read Committed） | 仅允许读取已提交的事务数据 | 脏读 | 不可重复读、幻读 |
| 可重复读（Repeatable Read） | 事务中多次读取同一数据结果一致 | 脏读、不可重复读 | 幻读（MySQL 8.0 已解决） |
| 串行化（Serializable） | 事务串行执行（不并发） | 所有问题 | 性能极差，仅用于高一致性场景 |
sql-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 修改隔离级别（会话级，仅当前连接有效）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 修改隔离级别（全局级，重启后生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
展示死锁案例、如何通过
SHOW ENGINE INNODB STATUS分析死锁日志。
sql-- 模拟死锁（两个会话交叉更新）
-- Session A: UPDATE user SET balance=100 WHERE id=1;
-- Session B: UPDATE user SET balance=200 WHERE id=2;
-- Session A: UPDATE user SET balance=150 WHERE id=2; -- 等待
-- Session B: UPDATE user SET balance=250 WHERE id=1; -- 死锁！
建议：
- 固定更新顺序（如 always update by id ASC）
- 缩短事务时间
- 设置
innodb_lock_wait_timeout
视图是基于 SQL 查询结果的虚拟表，不存储实际数据，仅存储查询逻辑（相当于「查询模板」）。
sql-- 1. 创建视图（查询用户姓名、手机号、部门名称，隐藏密码等敏感字段）
CREATE VIEW `v_user_dept` AS
SELECT
u.username,
u.phone,
d.dept_name
FROM `sys_user` u
LEFT JOIN `sys_dept` d ON u.dept_id = d.id;
-- 2. 查询视图（与查询表语法一致）
SELECT * FROM `v_user_dept` WHERE dept_name = '技术部';
-- 3. 修改视图
CREATE OR REPLACE VIEW `v_user_dept` AS -- 不存在则创建，存在则修改
SELECT
u.username,
u.phone,
u.gender,
d.dept_name
FROM `sys_user` u
LEFT JOIN `sys_dept` d ON u.dept_id = d.id;
-- 4. 删除视图
DROP VIEW IF EXISTS `v_user_dept`;
存储过程是预编译的 SQL 语句集合，存储在数据库中，可通过调用名称执行（相当于数据库中的「函数」）。
sql-- 1. 创建存储过程（无参数，查询所有男性用户）
DELIMITER // -- 修改语句结束符为 //（默认 ; 会与存储过程内的 ; 冲突）
CREATE PROCEDURE `proc_get_male_user`()
BEGIN
SELECT * FROM `sys_user` WHERE gender = 1;
END //
DELIMITER ; -- 恢复语句结束符为 ;
-- 2. 调用存储过程
CALL `proc_get_male_user`();
-- 3. 创建带参数的存储过程（根据部门ID查询用户）
DELIMITER //
CREATE PROCEDURE `proc_get_user_by_dept`(IN dept_id INT) -- IN 表示输入参数
BEGIN
SELECT * FROM `sys_user` WHERE dept_id = dept_id;
END //
DELIMITER ;
-- 调用带参数的存储过程
CALL `proc_get_user_by_dept`(1);
-- 4. 创建带输出参数的存储过程（统计指定性别的用户数）
DELIMITER //
CREATE PROCEDURE `proc_count_user_by_gender`(
IN gender INT, -- 输入参数：性别
OUT user_count INT -- 输出参数：用户数
)
BEGIN
SELECT COUNT(*) INTO user_count FROM `sys_user` WHERE gender = gender;
END //
DELIMITER ;
-- 调用带输出参数的存储过程
SET @count = 0; -- 定义变量接收输出结果
CALL `proc_count_user_by_gender`(1, @count);
SELECT @count; -- 查看结果
-- 5. 删除存储过程
DROP PROCEDURE IF EXISTS `proc_get_male_user`;
Access denied for user 'test_user'@'192.168.1.100' (using password: YES)host 是否允许远程访问（如 'test_user'@'%'）。bind-address 是否为 0.0.0.0（允许所有IP访问），默认可能为 127.0.0.1（仅本地访问）。EXPLAIN 分析发现 type=ALL（全表扫描）。SHOW INDEX FROM 表名）。COMMIT 后，数据库崩溃，重启后数据丢失。COMMIT 无效）。innodb_flush_log_at_trx_commit 是否为 1（默认值，事务提交时立即刷盘，保证持久性）。SHOW VARIABLES LIKE 'character_set_database';（需为 utf8mb4）。SHOW CREATE TABLE 表名;（需为 utf8mb4）。SET NAMES utf8mb4;（临时生效，永久生效需在连接字符串中指定 characterEncoding=utf8mb4）。命名规范：
sys_user、user_name），避免大小写敏感问题。pk_字段名，唯一索引 uk_字段名，普通索引 idx_字段名。安全规范：
性能规范：
CHAR(11) 而非 VARCHAR(20)）。OPTIMIZE TABLE 表名;（整理碎片，适用于 InnoDB 表）。开发规范：
WHERE 条件（避免全表更新/删除）。INSERT INTO ... VALUES (...), (...) 替代多条 INSERT（减少网络交互）。备份与恢复：
mysqldump 命令备份：
bashmysqldump -u root -p test_db > test_db_backup_20251124.sql # 备份 test_db 数据库
bashmysql -u root -p test_db < test_db_backup_20251124.sql
版本兼容性提醒：
明确标注哪些特性仅在 MySQL 8.0+ 可用：
窗口函数 → 8.0+
CTE（含递归）→ 8.0+
EXPLAIN ANALYZE → 8.0.18+
utf8mb4 默认字符集 → 8.0 默认
移除 mysql.user 表密码字段（改用 authentication_string）
建议：开发环境尽量使用 MySQL 8.0 LTS（如 8.4），避免旧版本限制。
本教程覆盖了 MySQL 从入门到进阶的核心知识点，包括环境搭建、数据库/用户管理、SQL 操作、索引优化、事务、视图/存储过程等。实际开发中，需结合业务场景灵活运用，同时注重数据安全和性能优化。建议多动手实践（如搭建测试环境、编写 SQL 语句、分析执行计划），逐步积累经验。
如果需要深入学习，可参考 MySQL 官方文档（MySQL Documentation）或进阶书籍《高性能 MySQL》。
本文作者:夏至十二
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！