全网最全的MySQL 必会操作大汇总：从建库到查询，手把手带你上手

在数字化浪潮席卷全球的今天，数据已成为企业最核心的战略资产。从电商平台的交易记录、社交媒体的用户行为，到金融系统的资金流动、医疗系统的患者档案，数据库作为存储、管理和检索数据的基础工具，支撑着现代信息系统的正常运转。无论是小型应用还是大型企业级架构，选择合适的数据库技术、掌握规范的操作方法，都是确保系统稳定性、数据一致性和业务连续性的关键。

第一章：数据库基础与环境准备

1.1 数据库核心概念解析

在开始操作之前，我们需要明确几个核心概念，避免术语混淆：

术语	定义	类比示例
数据库（Database）	存储数据的仓库，按一定结构组织和存储数据的集合	相当于"文件夹"
数据表（Table）	数据库中用于存储具体数据的二维表格，由行（记录）和列（字段）组成	相当于"Excel表格"
字段（Column）	表中的一列，代表数据的某个属性（如"姓名"、"年龄"）	相当于"Excel列标题"
记录（Row）	表中的一行，代表一条完整的数据（如一条用户信息）	相当于"Excel行数据"
主键（Primary Key）	唯一标识表中每条记录的字段，确保数据唯一性	相当于"身份证号"
SQL（Structured Query Language）	操作数据库的标准语言，用于查询、插入、更新和删除数据	相当于"与数据库对话的语言"

关系型数据库 vs 非关系型数据库：本文聚焦关系型数据库（RDBMS），其核心特点是数据以表格形式组织，通过外键建立表之间的关联关系（如"订单表"通过"用户ID"关联"用户表"），支持事务（ACID特性）和复杂查询。常见的关系型数据库包括MySQL、PostgreSQL、Oracle、SQL Server等。非关系型数据库（NoSQL）如MongoDB、Redis则适用于非结构化数据存储，本文暂不展开。

1.2 MySQL环境搭建与验证

在开始数据库操作前，需先安装MySQL环境。以下是Windows/macOS/Linux通用的安装步骤：

1.2.1 安装MySQL（以MySQL 8.0为例）

# macOS（使用Homebrew）
brew install mysql@8.0
brew services start mysql@8.0

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql

# Windows
# 1. 访问https://dev.mysql.com/downloads/installer/下载安装包
# 2. 选择"Developer Default"安装类型，按向导完成安装
# 3. 记住设置的root密码（后续登录需要）

1.2.2 验证安装与登录

# 验证MySQL服务状态
sudo systemctl status mysql  # Linux
brew services list | grep mysql  # macOS

# 登录MySQL（本地）
mysql -u root -p
# 输入安装时设置的root密码，成功后显示MySQL欢迎信息：
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 8
# Server version: 8.0.36 MySQL Community Server - GPL

# 登录远程MySQL（需提前开启远程访问权限）
mysql -h 192.168.1.100 -u root -p -P 3306  # -h:主机IP，-P:端口（默认3306）

1.2.3 客户端工具推荐

命令行：轻量高效，适合脚本和服务器环境操作
MySQL Workbench：官方GUI工具，支持可视化设计、查询和管理
Navicat Premium：功能强大的商业工具，支持多数据库类型（MySQL、PostgreSQL、MongoDB等）
DBeaver：开源免费，跨平台，支持数据导入导出和ER图设计

最佳实践：开发环境建议使用GUI工具（如Navicat）提高效率，生产环境操作优先使用命令行（避免GUI工具的潜在风险）。

有Navicat Premium下载需求的，可移步至（全网最详细，图文并茂）Navicat Premium 17免费下载安装，以及如何进行简单操作教程（附安装包）-CSDN博客

第二章：数据库基本操作（DDL）

数据定义语言（DDL，Data Definition Language）用于创建、修改和删除数据库对象（如数据库、表、视图等）。本节将详细讲解数据库级别的DDL操作。

2.1 创建数据库：`CREATE DATABASE`

创建数据库是数据库操作的第一步，语法如下：

CREATE DATABASE [IF NOT EXISTS] 数据库名称 [CHARACTER SET 字符集] [COLLATE 排序规则];

参数说明：

IF NOT EXISTS：可选，避免重复创建已存在的数据库（不加此参数时，创建已存在的数据库会报错）
CHARACTER SET：指定数据库默认字符集（如utf8mb4支持所有Unicode字符，包括emoji）
COLLATE：指定字符集的排序规则（如utf8mb4_general_ci不区分大小写，utf8mb4_bin区分大小写）

示例1：创建基础数据库

-- 创建名为"company"的数据库
CREATE DATABASE company;

-- 尝试创建已存在的数据库（会报错）
CREATE DATABASE company;  # 错误：Can't create database 'company'; database exists

-- 使用IF NOT EXISTS避免报错
CREATE DATABASE IF NOT EXISTS company;  # 成功（无提示，因为数据库已存在）

示例2：指定字符集和排序规则（推荐）

-- 创建支持中文和emoji的数据库（生产环境推荐配置）
CREATE DATABASE IF NOT EXISTS ecommerce 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci;

-- 查看数据库创建信息（验证字符集和排序规则）
SHOW CREATE DATABASE ecommerce;
-- 输出：
-- ecommerce | CREATE DATABASE `ecommerce` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */

避坑指南：不要使用utf8字符集！MySQL中的utf8仅支持最多3字节的Unicode字符，而utf8mb4支持4字节字符（如emoji😀、某些生僻汉字）。从MySQL 5.5.3开始支持utf8mb4，建议所有新数据库都使用此字符集。

2.2 查看数据库：`SHOW DATABASES`

查看当前MySQL服务器中的所有数据库：

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

-- 模糊查询数据库（如查看以"e"开头的数据库）
SHOW DATABASES LIKE 'e%';  # 匹配"ecommerce"
SHOW DATABASES LIKE '%company%';  # 匹配包含"company"的数据库

系统数据库说明：

执行SHOW DATABASES后，会看到几个默认存在的系统数据库，请勿随意修改或删除：

information_schema：存储MySQL服务器所有数据库的元数据（如表结构、列信息）
mysql：存储MySQL服务器的权限信息、用户账户、存储过程等
performance_schema：存储数据库性能相关的统计信息，用于性能监控
sys：基于performance_schema的数据，提供更易读的性能诊断视图

2.3 选择数据库：`USE`

对数据库中的表进行操作前，需先选择目标数据库：

USE 数据库名称;

示例：

-- 选择"ecommerce"数据库
USE ecommerce;

-- 验证当前选择的数据库
SELECT DATABASE();  # 输出：ecommerce

注意：如果未选择数据库就执行表操作（如CREATE TABLE），会报错No database selected。

2.4 修改数据库：`ALTER DATABASE`

修改数据库的字符集、排序规则等属性：

ALTER DATABASE 数据库名称 [CHARACTER SET 新字符集] [COLLATE 新排序规则];

示例：

-- 将"company"数据库的字符集修改为utf8mb4
ALTER DATABASE company 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci;

注意：修改数据库字符集仅影响后续创建的表，已存在的表字符集不会自动改变（需单独修改表的字符集）。

2.5 删除数据库：`DROP DATABASE`

删除数据库会永久删除该数据库中的所有表和数据，操作需极其谨慎！

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

示例：

-- 删除"test_db"数据库（危险操作！）
DROP DATABASE test_db;

-- 使用IF EXISTS避免删除不存在的数据库时报错
DROP DATABASE IF EXISTS test_db;  # 即使数据库不存在也不会报错

安全操作建议：

生产环境禁止直接使用DROP DATABASE，如需删除数据库，建议先备份数据

可通过重命名数据库（RENAME DATABASE，MySQL 8.0已移除该命令）或移动数据目录实现"软删除"

对关键数据库设置权限控制，仅允许管理员执行删除操作

2.6 数据库操作最佳实践总结

操作场景	推荐命令	风险等级
创建新数据库	`CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`	低
查看数据库列表	`SHOW DATABASES;`	低
选择数据库	`USE db_name;`	低
修改数据库字符集	`ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`	中
删除数据库	`DROP DATABASE IF EXISTS db_name;` （仅测试环境）	高

企业级规范：数据库命名建议使用小写字母、数字和下划线，避免使用中文和特殊字符；生产数据库名称应包含项目标识（如ecommerce_prod、ecommerce_test区分生产和测试环境）。

第三章：数据表基本操作（DDL）

数据表是存储数据的基本单位，本节将详细讲解表级别的DDL操作，包括创建、查看、修改和删除表。

3.1 创建表：`CREATE TABLE`

创建表是数据库设计的核心环节，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名 (
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  ...
  [表级约束]
) [ENGINE 存储引擎] [CHARACTER SET 字符集] [COLLATE 排序规则];

关键要素：

数据类型：定义列存储的数据种类（如INT、VARCHAR、DATE等）
约束：确保数据的完整性和一致性（如主键、非空、唯一约束等）
存储引擎：MySQL的表存储方式（如InnoDB支持事务和外键，MyISAM不支持）

3.1.1 常用数据类型详解

选择合适的数据类型是表设计的关键，直接影响存储效率和查询性能。以下是MySQL常用数据类型分类及示例：

1. 数值类型

类型	字节数	范围（有符号）	应用场景示例
`TINYINT`	1	-128 ~ 127	状态标识（0-禁用，1-启用）
`SMALLINT`	2	-32768 ~ 32767	数量较少的分类ID
`INT`	4	-2147483648 ~ 2147483647	用户ID、订单ID
`BIGINT`	8	-9223372036854775808 ~ 9223372036854775807	大表的自增ID（如日志表）
`FLOAT`	4	单精度浮点数（约6-7位小数）	非精确数值（如温度）
`DOUBLE`	8	双精度浮点数（约15-17位小数）	非精确数值（如重量）
`DECIMAL(M,D)`	可变	精确小数（M为总位数，D为小数位数）	金额、价格（如DECIMAL(10,2)）

金额存储最佳实践：永远不要使用FLOAT或DOUBLE存储金额！由于浮点数的精度问题，可能导致计算误差（如0.1 + 0.2 = 0.30000000000000004）。应使用DECIMAL(M,D)（如DECIMAL(10,2)表示最大9999999.99）或整数（存储分，而非元）。

2. 字符串类型

类型	特点	最大长度	应用场景示例
`CHAR(M)`	固定长度，不足补空格	M字节（1 ≤ M ≤ 255）	固定长度内容（如手机号、身份证号）
`VARCHAR(M)`	可变长度，按实际内容存储	M字符（1 ≤ M ≤ 65535）	可变长度内容（如姓名、地址）
`TEXT`	长文本，存储超过65535字符的内容	65535字节	文章内容、备注
`LONGTEXT`	超长文本	4GB	日志、富文本内容

CHAR vs VARCHAR选择原则：长度固定且较短的字符串用CHAR（如手机号11位，CHAR(11)），长度不固定或较长的用VARCHAR（如姓名，VARCHAR(50)）。CHAR查询性能略高于VARCHAR，但可能浪费存储空间。

3. 日期时间类型

类型	字节数	格式	范围	应用场景示例
`DATE`	3	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	生日、订单日期
`TIME`	3	HH:MM:SS	-838:59:59 ~ 838:59:59	时长、打卡时间
`DATETIME`	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	订单创建时间、文章发布时间
`TIMESTAMP`	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07	记录最后修改时间
`YEAR`	1	YYYY	1901 ~ 2155	年份（如毕业年份）

DATETIME vs TIMESTAMP：DATETIME存储范围更大，不受时区影响；TIMESTAMP存储占用空间小，会根据MySQL服务器时区自动转换（推荐用于记录"最后更新时间"）。

3.1.2 创建表完整示例

以电商系统的"用户表"为例，创建包含常用数据类型和约束的表：

-- 创建用户表（users）
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID（主键，自增）',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（非空，唯一）',
  email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱（非空，唯一）',
  phone CHAR(11) NOT NULL UNIQUE COMMENT '手机号（非空，唯一）',
  password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希（非空）',
  gender TINYINT COMMENT '性别（0-未知，1-男，2-女）',
  birth_date DATE COMMENT '生日',
  register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间（默认当前时间）',
  last_login_time TIMESTAMP NULL COMMENT '最后登录时间',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '状态（0-禁用，1-正常，2-待验证）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息表';

代码解析：

id INT PRIMARY KEY AUTO_INCREMENT：主键约束+自增，确保每条记录的唯一标识
username VARCHAR(50) NOT NULL UNIQUE：非空约束（NOT NULL）+唯一约束（UNIQUE），确保用户名必填且不重复
DEFAULT CURRENT_TIMESTAMP：默认值为当前时间（register_time字段自动记录注册时间）
COMMENT：字段注释，提高代码可读性（建议所有字段都添加注释）
ENGINE=InnoDB：使用InnoDB存储引擎（支持事务、外键、行级锁，MySQL 5.5.5后默认存储引擎）

3.2 查看表结构：`DESCRIBE`/`DESC`

查看表结构是了解表设计的常用操作，语法如下：

DESCRIBE 表名;  # 或简写为 DESC 表名;

示例：查看用户表结构

DESC users;

输出结果解析：

Field	Type	Null	Key	Default	Extra	Comment
id	int	NO	PRI	NULL	auto_increment	用户ID（主键，自增）
username	varchar(50)	NO	UNI	NULL		用户名（非空，唯一）
email	varchar(100)	NO	UNI	NULL		邮箱（非空，唯一）
phone	char(11)	NO	UNI	NULL		手机号（非空，唯一）
password_hash	varchar(255)	NO		NULL		密码哈希（非空）
gender	tinyint	YES		NULL		性别（0-未知，1-男，2-女）
birth_date	date	YES		NULL		生日
register_time	datetime	NO		CURRENT_TIMESTAMP		注册时间（默认当前时间）
last_login_time	timestamp	YES		NULL		最后登录时间
status	tinyint	NO		1		状态（0-禁用，1-正常）

Field：字段名
Type：数据类型
Null：是否允许为NULL（YES-允许，NO-不允许）
Key：索引类型（PRI-主键，UNI-唯一索引，MUL-普通索引）
Default：默认值
Extra：额外信息（如auto_increment表示自增）

3.3 修改表：`ALTER TABLE`

业务需求变化时，需对表结构进行修改（如添加新字段、修改数据类型等），语法如下：

ALTER TABLE 表名 操作类型;

常用操作类型：

1. 添加列：`ADD`

-- 为用户表添加"昵称"字段
ALTER TABLE users 
  ADD nickname VARCHAR(50) COMMENT '用户昵称' 
  AFTER username;  # AFTER指定字段位置（不指定则默认加在最后）

-- 添加"头像URL"字段（允许NULL）
ALTER TABLE users 
  ADD avatar_url VARCHAR(255) COMMENT '头像URL';

2. 修改列：`MODIFY`/`CHANGE`

MODIFY：修改列的数据类型或约束（不修改列名）
CHANGE：修改列名+数据类型+约束

-- MODIFY示例：将gender字段的默认值设为0
ALTER TABLE users 
  MODIFY gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别（0-未知，1-男，2-女）';

-- CHANGE示例：将"nickname"列改名为"nick_name"
ALTER TABLE users 
  CHANGE nickname nick_name VARCHAR(50) COMMENT '用户昵称';

3. 删除列：`DROP`

-- 删除"avatar_url"字段（谨慎操作！数据会永久丢失）
ALTER TABLE users DROP avatar_url;

4. 重命名表：`RENAME TO`

-- 将"users"表重命名为"sys_users"（系统用户表）
ALTER TABLE users RENAME TO sys_users;

-- 验证重命名结果
SHOW TABLES LIKE 'sys_%';  # 输出：sys_users

注意：修改表结构可能导致锁表（尤其是大表），生产环境建议在低峰期执行，并提前备份数据。

3.4 删除表：`DROP TABLE`

删除表会永久删除表结构和所有数据，操作需极其谨慎！

-- 删除表（危险操作！）
DROP TABLE IF EXISTS 表名;

示例：

-- 删除临时表
DROP TABLE IF EXISTS temp_order_data;

安全建议：删除表前先备份（如CREATE TABLE backup_table AS SELECT * FROM original_table;），或使用RENAME TABLE将表重命名为"废表"（如old_table_20250809），观察一段时间后再删除。

3.5 查看表列表：`SHOW TABLES`

查看当前数据库中的所有表：

-- 查看所有表
SHOW TABLES;

-- 模糊查询表（如查看以"sys_"开头的表）
SHOW TABLES LIKE 'sys_%';

示例输出：

+---------------------+
| Tables_in_ecommerce |
+---------------------+
| sys_users           |
| orders              |
| products            |
+---------------------+

第四章：数据表数据操作（DML）

数据操纵语言（DML，Data Manipulation Language）用于对表中的数据进行增、删、改操作。本节将详细讲解DML的核心命令及使用场景。

4.1 插入数据：`INSERT`

插入数据是将新记录添加到表中的操作，支持单行插入、多行插入和查询结果插入。

4.1.1 单行插入：`INSERT INTO ... VALUES`

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

示例：插入用户数据

-- 插入完整字段（按表结构顺序）
INSERT INTO sys_users (username, email, phone, password_hash, gender, birth_date, register_time, status)
VALUES ('zhangsan', 'zhangsan@example.com', '13800138000', 'e10adc3949ba59abbe56e057f20f883e', 1, '1990-01-15', '2025-08-01 09:30:00', 1);

-- 插入部分字段（未指定的字段需有默认值或允许NULL）
INSERT INTO sys_users (username, email, phone, password_hash)
VALUES ('lisi', 'lisi@example.com', '13900139000', 'e10adc3949ba59abbe56e057f20f883e');

注意：字符串值需用单引号（'）包裹，日期时间类型也需用单引号（如'2025-08-01'）；数值类型直接写值（如1、3.14）。

4.1.2 多行插入：`INSERT INTO ... VALUES (...), (...), ...`

一次插入多条记录，比单行插入效率更高（减少网络交互次数）：

-- 插入3条用户数据
INSERT INTO sys_users (username, email, phone, password_hash, status)
VALUES 
  ('wangwu', 'wangwu@example.com', '13700137000', 'e10adc3949ba59abbe56e057f20f883e', 1),
  ('zhaoliu', 'zhaoliu@example.com', '13600136000', 'e10adc3949ba59abbe56e057f20f883e', 1),
  ('qianqi', 'qianqi@example.com', '13500135000', 'e10adc3949ba59abbe56e057f20f883e', 2);  -- 状态2-待验证

4.1.3 插入查询结果：`INSERT INTO ... SELECT`

将查询结果插入到目标表（目标表需先存在）：

-- 创建"活跃用户表"（active_users）
CREATE TABLE IF NOT EXISTS active_users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- 将状态为1（正常）的用户插入到活跃用户表
INSERT INTO active_users (id, username, email)
SELECT id, username, email FROM sys_users WHERE status = 1;

4.2 更新数据：`UPDATE`

更新表中已存在的记录，语法如下：

UPDATE 表名 SET 列1=值1, 列2=值2, ... [WHERE 条件];

重要：`WHERE`条件是可选的，但不写WHERE会更新表中所有记录（极其危险！）。

示例1：更新单条记录

-- 将用户ID=2的邮箱更新为新值
UPDATE sys_users 
SET email = 'new_lisi@example.com', last_login_time = NOW()
WHERE id = 2;

示例2：批量更新

-- 将所有"待验证"状态（status=2）的用户改为"禁用"（status=0）
UPDATE sys_users 
SET status = 0, update_time = NOW()
WHERE status = 2;

示例3：避免全表更新（添加安全检查）

-- 错误示例：无WHERE条件（更新所有记录！）
UPDATE sys_users SET status = 0;  -- 生产环境禁止执行！

-- 安全写法：强制要求WHERE条件（可在MySQL配置中开启sql_safe_updates=1）
SET sql_safe_updates = 1;  -- 开启安全更新模式
UPDATE sys_users SET status = 0;  -- 报错：You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

生产环境建议：所有MySQL实例开启sql_safe_updates=1（在my.cnf配置文件中设置），避免误操作导致全表更新/删除。

4.3 删除数据：`DELETE`/`TRUNCATE`

删除数据有两种方式：DELETE（逐行删除，支持条件）和TRUNCATE（清空全表，不支持条件）。

4.3.1 `DELETE`：删除指定记录

DELETE FROM 表名 [WHERE 条件];

示例：

-- 删除ID=5的用户（单行删除）
DELETE FROM sys_users WHERE id = 5;

-- 删除2020年以前注册的非活跃用户（批量删除）
DELETE FROM sys_users 
WHERE register_time < '2020-01-01' AND status = 0;

4.3.2 `TRUNCATE`：清空全表

TRUNCATE TABLE 表名;

示例：

-- 清空"临时订单表"（保留表结构，删除所有数据）
TRUNCATE TABLE temp_orders;

`DELETE` vs `TRUNCATE`对比：

特性	`DELETE`	`TRUNCATE`
条件支持	支持`WHERE`条件，可删除部分记录	不支持条件，只能清空全表
事务回滚	支持（删除后可通过事务回滚恢复数据）	不支持（DDL操作，隐式提交事务）
自增ID重置	不重置（下一条记录自增ID继续累加）	重置（自增ID从1开始）
性能	逐行删除，大表速度慢	直接删除表数据页，速度快（相当于重建表）
日志记录	记录每行删除日志（binlog）	只记录表结构重建日志

使用场景：需要删除部分记录或需回滚时用DELETE；确定要清空全表且不需要回滚时用TRUNCATE（性能更好）。

4.4 DML操作最佳实践

操作类型	安全规范	性能优化建议
`INSERT`	批量插入优先使用多行VALUES语法	大表插入建议分批（每批1000-5000行），避免锁表
`UPDATE`	必须加`WHERE`条件，生产环境开启`sql_safe_updates=1`	更新频繁字段建议添加索引，避免全表扫描
`DELETE`	禁止无`WHERE`删除，大批量删除建议用`LIMIT`分批删除（如`DELETE ... LIMIT 1000`）	大表删除前考虑备份，或使用"逻辑删除"（status标记）
`TRUNCATE`	仅用于确认无需保留数据的表（如临时表）	清空大表优先使用`TRUNCATE`而非`DELETE`

逻辑删除最佳实践：重要数据建议使用"逻辑删除"（添加is_deleted TINYINT DEFAULT 0字段，删除时设为1），避免物理删除导致数据丢失和恢复困难。

第五章：约束规则（确保数据完整性）

约束（Constraint）是强加在表上的规则，用于确保数据的准确性和一致性。MySQL支持多种约束类型，本节将详细讲解其定义和应用。

5.1 主键约束（PRIMARY KEY）

主键是表中唯一标识每条记录的字段，具有以下特点：

每个表只能有一个主键
主键字段的值唯一且非空
建议使用无业务含义的自增ID作为主键（避免业务字段变化导致主键变更）

定义主键的两种方式：

1. 列级约束（常用）

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID（主键，自增）',
  name VARCHAR(50) NOT NULL COMMENT '姓名'
);

2. 表级约束（多字段联合主键时使用）

-- 创建"学生课程关联表"（联合主键：学生ID+课程ID）
CREATE TABLE student_courses (
  student_id INT NOT NULL COMMENT '学生ID',
  course_id INT NOT NULL COMMENT '课程ID',
  score DECIMAL(5,2) COMMENT '成绩',
  -- 表级约束定义联合主键
  PRIMARY KEY (student_id, course_id)
);

主键操作：

-- 添加主键（表已存在时）
ALTER TABLE students ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE students DROP PRIMARY KEY;

5.2 自增约束（AUTO_INCREMENT）

自增约束通常与主键配合使用，自动生成唯一ID，语法如下：

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID（自增主键）',
  order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单编号'
);

自增规则：

默认从1开始，每次递增1
可通过AUTO_INCREMENT=起始值设置初始值
删除记录后，自增ID不会回滚（如删除ID=5的记录，下一条记录ID仍为6）

示例：

-- 创建表时设置自增起始值
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单编号'
) AUTO_INCREMENT = 10000;  -- 从10000开始自增

-- 插入数据（无需指定order_id，自动生成）
INSERT INTO orders (order_no) VALUES ('20250809001'), ('20250809002');
SELECT order_id, order_no FROM orders;
-- 输出：
-- order_id | order_no
-- 10000    | 20250809001
-- 10001    | 20250809002

5.3 非空约束（NOT NULL）

非空约束确保字段值不能为NULL（必填项），语法如下：

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT '商品名称（非空）',
  price DECIMAL(10,2) NOT NULL COMMENT '商品价格（非空）',
  description TEXT COMMENT '商品描述（允许NULL）'
);

插入违反非空约束的数据会报错：

-- 错误示例：name字段为NULL
INSERT INTO products (price) VALUES (99.99);  -- 报错：Field 'name' doesn't have a default value

5.4 唯一约束（UNIQUE）

唯一约束确保字段值在表中唯一（允许NULL，但NULL可以重复），语法如下：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（唯一）',
  email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱（唯一）',
  phone VARCHAR(20) UNIQUE COMMENT '手机号（唯一，允许NULL）'
);

唯一约束 vs 主键：

主键：唯一且非空，一个表只能有一个
唯一约束：唯一但允许NULL，一个表可以有多个

插入重复值会报错：

-- 插入重复用户名
INSERT INTO users (username, email) VALUES ('zhangsan', 'zs@example.com');
INSERT INTO users (username, email) VALUES ('zhangsan', 'zs2@example.com');  -- 报错：Duplicate entry 'zhangsan' for key 'username'

5.5 默认约束（DEFAULT）

默认约束为字段设置默认值，当插入数据时未指定该字段值，将使用默认值：

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态（0-待支付，1-已支付，2-已取消）',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（默认当前时间）'
);

示例：

-- 未指定status和create_time，使用默认值
INSERT INTO orders (order_no) VALUES ('20250809003');

-- 查询结果
SELECT * FROM orders WHERE order_no = '20250809003';
-- 输出：
-- id | order_no      | status | create_time
-- 1  | 20250809003   | 0      | 2025-08-09 15:30:00

5.6 外键约束（FOREIGN KEY）

外键约束用于建立表之间的关联关系，确保数据的参照完整性（如"订单表"的用户ID必须对应"用户表"中存在的用户）。

语法：

CREATE TABLE 子表 (
  子表列1 数据类型,
  ...,
  外键列 数据类型,
  FOREIGN KEY (外键列) REFERENCES 父表 (父表主键列) [ON DELETE 操作] [ON UPDATE 操作]
);

`ON DELETE`/`ON UPDATE`选项：

CASCADE：父表记录删除/更新时，子表关联记录也删除/更新
SET NULL：父表记录删除/更新时，子表关联字段设为NULL（需子表字段允许NULL）
RESTRICT：拒绝父表删除/更新（子表有关联记录时）
NO ACTION：同RESTRICT（MySQL中与RESTRICT等效）

示例：创建订单表（子表）关联用户表（父表）

-- 父表：用户表（已存在）
CREATE TABLE sys_users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE
);

-- 子表：订单表（外键关联用户表）
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  user_id INT NOT NULL COMMENT '下单用户ID',
  total_amount DECIMAL(10,2) NOT NULL,
  -- 外键约束：user_id关联sys_users表的id
  FOREIGN KEY (user_id) REFERENCES sys_users (id) ON DELETE RESTRICT ON UPDATE CASCADE
);

外键约束效果：

-- 插入合法订单（user_id存在）
INSERT INTO orders (order_no, user_id, total_amount) VALUES ('ORD001', 1, 99.99);  -- 成功

-- 插入非法订单（user_id不存在）
INSERT INTO orders (order_no, user_id, total_amount) VALUES ('ORD002', 999, 199.99);  -- 报错：Cannot add or update a child row: a foreign key constraint fails

-- 删除父表记录（有子表关联时）
DELETE FROM sys_users WHERE id = 1;  -- 报错：Cannot delete or update a parent row: a foreign key constraint fails (due to ON DELETE RESTRICT)

外键使用建议：中小规模应用推荐使用外键确保数据完整性；超大规模应用（如电商订单表）可考虑业务层实现关联检查（外键可能影响性能）。

第六章：数据查询语言（DQL）——单表查询

数据查询语言（DQL，Data Query Language）用于从表中检索数据，核心命令是SELECT。单表查询是基础，掌握后才能进行复杂的多表联合查询。

6.1 简单查询：`SELECT`基础语法

SELECT [DISTINCT] 列1 [AS 别名1], 列2 [AS 别名2], ... 
FROM 表名 
[WHERE 条件] 
[ORDER BY 列名 [ASC|DESC]] 
[LIMIT 数量];

6.1.1 查询所有列：`SELECT *`

-- 查询用户表所有记录
SELECT * FROM sys_users;

6.1.2 查询指定列：

-- 查询用户名、邮箱和注册时间
SELECT username, email, register_time FROM sys_users;

6.1.3 列别名：`AS`

-- 使用别名美化输出
SELECT 
  username AS '用户名', 
  email AS '邮箱地址', 
 register_time AS '注册时间' 
FROM sys_users;

6.1.4 去重查询：`DISTINCT`

-- 查询所有不同的用户状态（去除重复值）
SELECT DISTINCT status FROM sys_users;

6.2 条件查询：`WHERE`子句

WHERE子句用于过滤符合条件的记录，支持多种运算符：

6.2.1 比较运算符

运算符	说明	示例
`=`	等于	`status = 1`
`<>`/`!=`	不等于	`gender != 1`
`>`/`<`	大于/小于	`age > 18`
`>=`/`<=`	大于等于/小于等于	`score >= 60`
`BETWEEN...AND`	在范围内（闭区间）	`birth_date BETWEEN '1990-01-01' AND '2000-12-31'`
`IN`	在值列表中	`status IN (0, 1)`
`IS NULL`	为空	`phone IS NULL`
`IS NOT NULL`	不为空	`email IS NOT NULL`

示例：

-- 查询状态为1（正常）的用户
SELECT username, email FROM sys_users WHERE status = 1;

-- 查询1990-2000年出生的用户
SELECT username, birth_date FROM sys_users 
WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';

-- 查询状态为0或2的用户
SELECT username, status FROM sys_users 
WHERE status IN (0, 2);

-- 查询未填写手机号的用户
SELECT username FROM sys_users WHERE phone IS NULL;

6.2.2 逻辑运算符

运算符	说明	示例
`AND`	与（同时满足）	`status = 1 AND gender = 1`
`OR`	或（满足其一）	`status = 0 OR register_time < '2024-01-01'`
`NOT`	非（取反）	`NOT status = 1` 等效于 `status != 1`

示例：

-- 查询状态正常且性别为男的用户
SELECT username FROM sys_users WHERE status = 1 AND gender = 1;

-- 查询状态禁用或注册时间超过1年的用户
SELECT username, status, register_time FROM sys_users 
WHERE status = 0 OR register_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

6.2.3 模糊查询：`LIKE`/`REGEXP`

LIKE：简单模式匹配（%匹配任意字符，_匹配单个字符）
REGEXP：正则表达式匹配（更强大）

示例：

-- 查询用户名以"张"开头的用户
SELECT username FROM sys_users WHERE username LIKE '张%';

-- 查询用户名包含"三"的用户
SELECT username FROM sys_users WHERE username LIKE '%三%';

-- 查询邮箱为qq.com或163.com的用户（正则表达式）
SELECT username, email FROM sys_users 
WHERE email REGEXP '@(qq|163)\\.com$';  -- 注意转义符\

6.3 排序查询：`ORDER BY`

对查询结果按指定列排序，语法如下：

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

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

示例：

-- 按注册时间升序排列（默认，最早注册的在前）
SELECT username, register_time FROM sys_users ORDER BY register_time;

-- 按状态降序、注册时间升序排列（先按状态，状态相同再按注册时间）
SELECT username, status, register_time FROM sys_users 
ORDER BY status DESC, register_time ASC;

-- 查询年龄最大的3个用户
SELECT username, birth_date FROM sys_users 
ORDER BY birth_date ASC LIMIT 3;  -- ASC表示出生日期早（年龄大）

6.4 聚合查询：`COUNT`/`SUM`/`AVG`/`MAX`/`MIN`

聚合函数对一组值进行计算并返回单个值，常用于统计分析：

函数	说明	示例
`COUNT()`	统计记录数	`COUNT(*)` 统计所有记录数
`SUM()`	求和	`SUM(amount)` 求金额总和
`AVG()`	求平均值	`AVG(score)` 求平均成绩
`MAX()`	求最大值	`MAX(register_time)` 最新注册时间
`MIN()`	求最小值	`MIN(price)` 最低价格

示例：

-- 统计用户总数
SELECT COUNT(*) AS '总用户数' FROM sys_users;

-- 统计正常用户数（status=1）
SELECT COUNT(*) AS '正常用户数' FROM sys_users WHERE status = 1;

-- 求所有订单的总金额、平均金额、最大金额
SELECT 
  SUM(total_amount) AS '总销售额',
  AVG(total_amount) AS '平均订单金额',
  MAX(total_amount) AS '最大订单金额',
  MIN(total_amount) AS '最小订单金额'
FROM orders;

-- 统计每个状态的用户数量（配合GROUP BY）
SELECT status, COUNT(*) AS '用户数' FROM sys_users GROUP BY status;

6.5 分组查询：`GROUP BY`与`HAVING`

GROUP BY：将记录按指定列分组，相同值的记录分为一组
HAVING：对分组后的结果进行过滤（类似WHERE，但WHERE不能用于聚合函数）

示例1：基础分组

-- 按性别分组，统计每组人数和平均年龄
SELECT 
  gender AS '性别',
  COUNT(*) AS '人数',
  AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) AS '平均年龄'
FROM sys_users 
WHERE birth_date IS NOT NULL  -- WHERE过滤分组前的数据
GROUP BY gender;

示例2：`HAVING`过滤分组结果

-- 统计用户数超过10人的状态组
SELECT 
  status AS '状态',
  COUNT(*) AS '用户数'
FROM sys_users 
GROUP BY status
HAVING COUNT(*) > 10;  -- HAVING过滤分组后的数据（用户数>10）

WHERE vs HAVING：

WHERE：分组前过滤，不能使用聚合函数

HAVING：分组后过滤，可以使用聚合函数

6.6 分页查询：`LIMIT`

分页查询用于获取结果集的部分数据（如每页显示10条记录），语法如下：

SELECT 列名 FROM 表名 LIMIT [偏移量, ] 记录数;

偏移量：从第几条记录开始（默认0，可省略）
记录数：获取多少条记录

示例：

-- 获取前10条记录（第1页，每页10条）
SELECT id, username FROM sys_users LIMIT 10;

-- 获取第2页数据（偏移量=10，记录数=10）
SELECT id, username FROM sys_users LIMIT 10, 10;

-- 通用分页公式：LIMIT (页码-1)*每页条数, 每页条数
-- 第3页，每页10条：LIMIT (3-1)*10, 10 → LIMIT 20, 10
SELECT id, username FROM sys_users LIMIT 20, 10;

注意：偏移量越大，查询性能越低（如LIMIT 100000, 10）。优化方案：使用"延迟关联"或"书签"分页（如WHERE id > 100000 LIMIT 10）。

第七章：多表联合查询

实际业务中，数据通常分布在多个表中（如用户表、订单表、商品表），需要通过联合查询获取关联数据。

7.1 表连接类型

MySQL支持多种表连接方式，核心区别在于如何处理关联条件不匹配的记录：

连接类型	说明	语法示例
内连接（INNER JOIN）	只返回两表中匹配关联条件的记录	`A INNER JOIN B ON A.id = B.a_id`
左连接（LEFT JOIN）	返回左表所有记录，右表匹配不到则为NULL	`A LEFT JOIN B ON A.id = B.a_id`
右连接（RIGHT JOIN）	返回右表所有记录，左表匹配不到则为NULL	`A RIGHT JOIN B ON A.id = B.a_id`
交叉连接（CROSS JOIN）	两表笛卡尔积（极少使用，会产生大量记录）	`A CROSS JOIN B` 或 `SELECT * FROM A, B`

数据准备：

-- 用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(20) NOT NULL,
  user_id INT,
  amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入测试数据
INSERT INTO users (name) VALUES ('张三'), ('李四'), ('王五');
INSERT INTO orders (order_no, user_id, amount) VALUES 
  ('ORD001', 1, 99.99), 
  ('ORD002', 1, 199.99), 
  ('ORD003', 2, 299.99);

7.1.1 内连接（INNER JOIN）

-- 查询有订单的用户及其订单信息（只返回匹配的记录）
SELECT 
  u.id AS '用户ID',
  u.name AS '用户名',
  o.order_no AS '订单号',
  o.amount AS '订单金额'
FROM users u  -- u是users表的别名
INNER JOIN orders o  -- o是orders表的别名
  ON u.id = o.user_id;  -- 关联条件

-- 结果：
-- 用户ID | 用户名 | 订单号  | 订单金额
-- 1      | 张三   | ORD001  | 99.99
-- 1      | 张三   | ORD002  | 199.99
-- 2      | 李四   | ORD003  | 299.99
-- （王五没有订单，不显示）

7.1.2 左连接（LEFT JOIN）

-- 查询所有用户及其订单信息（没有订单的用户也显示）
SELECT 
  u.id AS '用户ID',
  u.name AS '用户名',
  o.order_no AS '订单号',
  o.amount AS '订单金额'
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 结果：
-- 用户ID | 用户名 | 订单号  | 订单金额
-- 1      | 张三   | ORD001  | 99.99
-- 1      | 张三   | ORD002  | 199.99
-- 2      | 李四   | ORD003  | 299.99
-- 3      | 王五   | NULL    | NULL  （王五没有订单，显示NULL）

7.1.3 右连接（RIGHT JOIN）

-- 查询所有订单及其用户信息（假设存在用户已删除但订单未删除的情况）
SELECT 
  u.id AS '用户ID',
  u.name AS '用户名',
  o.order_no AS '订单号',
  o.amount AS '订单金额'
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 结果：与内连接相同（因为所有订单都有对应的用户）

7.2 多表连接示例（三表连接）

以"用户-订单-商品"三表关联为例：

-- 商品表
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- 订单明细表（订单-商品关联表）
CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL COMMENT '数量',
  price DECIMAL(10,2) NOT NULL COMMENT '购买时单价',
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入测试数据
INSERT INTO products (name, price) VALUES 
  ('iPhone 15', 7999.00), 
  ('小米14', 4999.00), 
  ('华为Mate 60', 5999.00);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
  (1, 2, 1, 4999.00),  -- 订单1购买小米14
  (2, 1, 1, 7999.00),  -- 订单2购买iPhone 15
  (3, 3, 1, 5999.00);  -- 订单3购买华为Mate 60

-- 查询订单详细信息（用户、订单、商品）
SELECT 
  u.name AS '用户名',
  o.order_no AS '订单号',
  p.name AS '商品名称',
  oi.quantity AS '数量',
  oi.price AS '单价',
  (oi.quantity * oi.price) AS '小计'
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.order_no;

结果：

用户名 | 订单号  | 商品名称   | 数量 | 单价    | 小计
张三   | ORD001  | 小米14     | 1    | 4999.00 | 4999.00
张三   | ORD002  | iPhone 15  | 1    | 7999.00 | 7999.00
李四   | ORD003  | 华为Mate 60| 1    | 5999.00 | 5999.00

第八章：子查询

子查询是嵌套在其他SQL语句中的查询，可作为条件、数据源或计算字段使用。

8.1 子查询类型

根据返回结果不同，子查询可分为：

1. 标量子查询：返回单个值（1行1列）

-- 查询订单金额最高的订单信息
SELECT * FROM orders 
WHERE amount = (SELECT MAX(amount) FROM orders);

2. 列子查询：返回单列多行（1列N行）

-- 查询购买过商品ID=1的用户
SELECT name FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE id IN 
  (SELECT order_id FROM order_items WHERE product_id = 1));

3. 表子查询：返回多行多列（N行M列），可作为临时表

-- 查询每个用户的最新订单
SELECT u.name, o.order_no, o.create_time 
FROM users u
INNER JOIN (
  -- 子查询：获取每个用户的最新订单ID
  SELECT user_id, MAX(id) AS latest_order_id 
  FROM orders 
  GROUP BY user_id
) lo ON u.id = lo.user_id
INNER JOIN orders o ON lo.latest_order_id = o.id;

8.2 子查询关键字：`IN`/`EXISTS`/`ALL`/`ANY`

8.2.1 `IN`：匹配子查询返回的任意值

-- 查询状态为1或2的用户订单
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status IN (1, 2));

8.2.2 `EXISTS`：子查询返回结果集则为真

-- 查询有订单的用户（等效于内连接）
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

8.2.3 `ALL`/`ANY`：与子查询所有/任意值比较

-- 查询订单金额大于用户2所有订单金额的订单
SELECT * FROM orders 
WHERE amount > ALL (SELECT amount FROM orders WHERE user_id = 2);

-- 查询订单金额大于用户2任意一个订单金额的订单
SELECT * FROM orders 
WHERE amount > ANY (SELECT amount FROM orders WHERE user_id = 2);

第九章：MySQL函数大全

MySQL提供丰富的内置函数，用于数据处理和计算，分为聚合函数、数学函数、字符串函数、日期函数等。

9.1 聚合函数（已在6.4节详细讲解）

9.2 数学函数

函数	说明	示例	结果
`ABS(x)`	绝对值	`ABS(-10)`	10
`CEIL(x)`	向上取整	`CEIL(3.2)`	4
`FLOOR(x)`	向下取整	`FLOOR(3.8)`	3
`ROUND(x, d)`	四舍五入，保留d位小数	`ROUND(3.1415, 2)`	3.14
`RAND()`	随机数（0-1）	`RAND()`	0.783456...
`POW(x, y)`	x的y次方	`POW(2, 3)`	8
`MOD(x, y)`	取模（x%y）	`MOD(10, 3)`	1

示例：生成随机数并四舍五入

-- 生成1-100的随机整数
SELECT FLOOR(RAND() * 100) + 1 AS '随机数';

9.3 字符串函数

函数	说明	示例	结果
`CONCAT(s1, s2)`	连接字符串	`CONCAT('Hello', 'World')`	'HelloWorld'
`SUBSTRING(s, start, len)`	截取子串（start从1开始）	`SUBSTRING('abcdef', 2, 3)`	'bcd'
`LENGTH(s)`	字符串长度（字节数）	`LENGTH('中国')`	6（utf8mb4中一个中文3字节）
`CHAR_LENGTH(s)`	字符数	`CHAR_LENGTH('中国')`	2
`UPPER(s)`/`LOWER(s)`	转大写/小写	`UPPER('hello')`	'HELLO'
`TRIM(s)`	去除首尾空格	`TRIM(' test ')`	'test'
`REPLACE(s, o, n)`	替换字符串	`REPLACE('abc', 'a', 'x')`	'xbc'
`LEFT(s, len)`/`RIGHT(s, len)`	取左/右len个字符	`LEFT('abcdef', 3)`	'abc'

示例：格式化用户姓名

-- 将用户名首字母大写，其余小写
SELECT 
  username,
  CONCAT(UPPER(LEFT(username, 1)), LOWER(SUBSTRING(username, 2))) AS '格式化姓名'
FROM sys_users;

9.4 日期函数

函数	说明	示例	结果
`NOW()`	当前日期时间	`NOW()`	'2025-08-09 16:30:00'
`CURDATE()`/`CURTIME()`	当前日期/时间	`CURDATE()`	'2025-08-09'
`YEAR(d)`/`MONTH(d)`/`DAY(d)`	提取年/月/日	`YEAR('2025-08-09')`	2025
`DATE_ADD(d, INTERVAL expr unit)`	日期加法	`DATE_ADD(NOW(), INTERVAL 1 DAY)`	'2025-08-10 16:30:00'
`DATEDIFF(d1, d2)`	日期差（d1-d2，天数）	`DATEDIFF('2025-08-10', '2025-08-01')`	9
`DATE_FORMAT(d, fmt)`	日期格式化	`DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')`	'2025-08-09 16:30:00'

示例：计算用户年龄

-- 根据生日计算年龄
SELECT 
  username,
  birth_date,
  TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS '年龄'
FROM sys_users;

9.5 窗口函数（MySQL 8.0+）

窗口函数用于对一组行进行计算，同时保留原表的每一行数据（与聚合函数不同），语法如下：

函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列 [ASC|DESC])

常用窗口函数：

1. 排名函数：

ROW_NUMBER()：行号（不重复）
RANK()：排名（有并列，会跳号）
DENSE_RANK()：密集排名（有并列，不跳号）

示例：对订单金额排名

-- 查询每个用户的订单金额排名
SELECT 
  user_id,
  order_no,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS '行号',
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS '排名',
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS '密集排名'
FROM orders;

2. 聚合窗口函数：

-- 查询每个订单金额及用户的总订单金额
SELECT 
  order_no,
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id) AS '用户总金额'
FROM orders;

第十章：视图（VIEW）

视图是基于查询结果的虚拟表，本身不存储数据，只保存查询逻辑。视图可以简化复杂查询、隐藏敏感字段和限制数据访问。

10.1 创建视图：`CREATE VIEW`

CREATE OR REPLACE VIEW 视图名 AS 查询语句;

示例：创建用户订单统计视图

-- 创建视图：统计每个用户的订单数量和总金额
CREATE OR REPLACE VIEW user_order_stats AS
SELECT 
  u.id AS user_id,
  u.username,
  COUNT(o.id) AS order_count,
  SUM(o.amount) AS total_amount,
  MAX(o.create_time) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

10.2 查询视图：

-- 像查询表一样查询视图
SELECT * FROM user_order_stats WHERE order_count > 0;

10.3 更新视图：

视图中的数据实际上存储在基表中，更新视图会同步更新基表：

-- 更新视图中的用户名（同步更新users表）
UPDATE user_order_stats SET username = '张三三' WHERE user_id = 1;

注意：包含GROUP BY、DISTINCT、聚合函数的视图通常不可更新。

10.4 删除视图：

DROP VIEW IF EXISTS user_order_stats;

10.5 重命名视图：

-- 重命名视图
RENAME TABLE v_employee_dept2 TO v_employee_dept;

注意事项：

删除视图不会影响底层表数据
重命名视图使用RENAME TABLE语句，而非ALTER VIEW

第十一章：存储过程，存储函数，触发器

11.1 存储过程

存储过程是预编译的SQL集合，保存在数据库中，可通过名称直接调用，适合复杂业务逻辑封装。

创建存储过程：

DELIMITER //  -- 修改语句结束符为//（避免与SQL中的;冲突）
CREATE PROCEDURE 存储过程名(IN 参数名 类型, OUT 参数名 类型)
BEGIN
  -- SQL语句
END //
DELIMITER ;  -- 恢复语句结束符为;

示例：创建查询用户订单的存储过程

-- 存储过程：根据用户ID查询订单总数和总金额
DELIMITER //
CREATE PROCEDURE GetUserOrderStats(
  IN in_user_id INT,  -- 输入参数：用户ID
  OUT out_order_count INT,  -- 输出参数：订单总数
  OUT out_total_amount DECIMAL(10,2)  -- 输出参数：总金额
)
BEGIN
  SELECT 
    COUNT(id) INTO out_order_count,
    SUM(amount) INTO out_total_amount
  FROM orders 
  WHERE user_id = in_user_id;
END //
DELIMITER ;

调用存储过程：

-- 声明变量接收输出参数
SET @order_count = 0;
SET @total_amount = 0;

-- 调用存储过程
CALL GetUserOrderStats(1, @order_count, @total_amount);

-- 查看结果
SELECT @order_count AS '订单总数', @total_amount AS '总金额';

完整示例：

-- 创建不带参数的存储过程
DELIMITER // 
CREATE PROCEDURE get_employee_dept()
BEGIN  
  SELECT e.e_id, e.e_name, e.salary, d.d_name 
  FROM employees AS e
  JOIN departments AS d ON e.dept_id = d.d_id;
END //  
DELIMITER ; 

-- 调用存储过程
CALL get_employee_dept();

-- 创建带参数的存储过程
DELIMITER // 
CREATE PROCEDURE get_employee_dept2(IN dept_id INT)
BEGIN  
  SELECT e.e_id, e.e_name, e.salary, d.d_name 
  FROM employees AS e
  JOIN departments AS d ON e.dept_id = d.d_id 
  WHERE e.dept_id = dept_id;
END //  
DELIMITER ; 

-- 调用带参数存储过程
CALL get_employee_dept2(2);

存储过程参数类型：

IN：输入参数（默认）
OUT：输出参数
INOUT：输入输出参数

带输出参数的存储过程：

DELIMITER //
CREATE PROCEDURE GetDepartmentSalary(
  IN department_id INT,
  OUT max_salary DECIMAL(10, 2),
  OUT min_salary DECIMAL(10, 2)
)
BEGIN
  SELECT MAX(salary), MIN(salary) 
  INTO max_salary, min_salary 
  FROM employees 
  WHERE dept_id = department_id;
END //
DELIMITER ;

-- 调用带输出参数的存储过程
SET @max_salary = 0;
SET @min_salary = 0;
CALL GetDepartmentSalary(10, @max_salary, @min_salary);
SELECT @max_salary AS '最高工资', @min_salary AS '最低工资';

11.2 触发器

触发器是在表上自动执行的存储过程，当表发生INSERT / UPDATE / DELETE操作时触发。

创建触发器：

-- 创建日志表
CREATE TABLE employee_log (
  id INT PRIMARY KEY AUTO_INCREMENT,
  operation VARCHAR(20),  -- 操作类型
  employee_id INT,        -- 员工ID
  operation_time DATETIME -- 操作时间
);

-- 创建INSERT触发器
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW  -- 行级触发器
BEGIN
  INSERT INTO employee_log (operation, employee_id, operation_time)
  VALUES ('INSERT', NEW.e_id, NOW());  -- NEW表示新插入的行
END //
DELIMITER ;

-- 创建UPDATE触发器
DELIMITER //
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_log (operation, employee_id, operation_time)
  VALUES ('UPDATE', NEW.e_id, NOW());
END //
DELIMITER ;

-- 创建DELETE触发器
DELIMITER //
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_log (operation, employee_id, operation_time)
  VALUES ('DELETE', OLD.e_id, NOW());  -- OLD表示被删除的行
END //
DELIMITER ;

触发器事件类型：

INSERT：插入数据时触发
UPDATE：更新数据时触发
DELETE：删除数据时触发

触发时机：

BEFORE：事件发生前触发（可用于数据验证和转换）
AFTER：事件发生后触发（可用于日志记录、数据同步）

11.3 存储函数

存储函数是返回单个值的特殊存储过程

示例：

-- 创建存储函数（计算最高工资）
DELIMITER //
CREATE FUNCTION get_max_salary(dept_name VARCHAR(30))
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- 确定性函数（相同输入返回相同输出）
BEGIN
  DECLARE v_max DECIMAL(10,2);
  SELECT MAX(salary) INTO v_max
  FROM employees  e
  JOIN departments d ON e.dept_id = d.d_id
  WHERE d.d_name = dept_name;
  RETURN v_max;
END //
DELIMITER ;

-- 调用存储函数
SELECT get_max_salary('技术部');  -- 返回技术部最高工资

存储函数与存储过程的区别：