引言 提出问题 前面我们学会了增删改查，但遇到一些问题： 如何防止插入重复的用户名？ 如何保证用户年龄不能为负数？ 如何保证订单必须关联到存在的用户？ 如何保证邮箱字段不能为空？ 这就需要数据库约束（Constraints）来保证数据完整性！ 数据完整性的三个层次 1. 实体完整性（Entity Integrity） 定义：每一行数据必须是唯一可识别的 实现方式：主键约束（PRIMARY KEY） CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，唯一标识 username VARCHAR(50) ); 2. 域完整性（Domain Integrity） 定义：列的值必须符合定义的类型和约束 实现方式： 数据类型（INT、VARCHAR等） NOT NULL约束 DEFAULT默认值 CHECK约束（MySQL 8.0.16+） CREATE TABLE users ( id INT PRIMARY KEY, age INT NOT NULL CHECK (age >= 0 AND age <= 150), -- 年龄范围 status TINYINT DEFAULT 1 -- 默认值 ); 3. 参照完整性（Referential Integrity） 定义：表之间的关联必须一致 实现方式：外键约束（FOREIGN KEY） CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id) -- 外键，关联users表 ); 主键约束（PRIMARY KEY） 特点 ✅ 唯一性：值不能重复 ✅ 非空性：值不能为NULL ✅ 每张表只能有一个主键 ✅ 主键可以是单列或多列（复合主键） 单列主键 -- 方式1：列级定义 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) ); -- 方式2：表级定义 CREATE TABLE users ( id INT AUTO_INCREMENT, username VARCHAR(50), PRIMARY KEY (id) ); 复合主键 -- 学生选课表（学生ID + 课程ID作为主键） CREATE TABLE student_courses ( student_id INT, course_id INT, score INT, PRIMARY KEY (student_id, course_id) -- 复合主键 ); -- 插入数据 INSERT INTO student_courses VALUES (1, 101, 90); -- 成功 INSERT INTO student_courses VALUES (1, 102, 85); -- 成功 INSERT INTO student_courses VALUES (1, 101, 95); -- 失败：主键重复 添加/删除主键 -- 添加主键 ALTER TABLE users ADD PRIMARY KEY (id); -- 删除主键 ALTER TABLE users DROP PRIMARY KEY; -- 修改主键（先删除再添加） ALTER TABLE users DROP PRIMARY KEY; ALTER TABLE users ADD PRIMARY KEY (new_id); 唯一约束（UNIQUE） 特点 ✅ 值不能重复 ✅ 可以为NULL（但只能有一个NULL） ✅ 一张表可以有多个唯一约束 基础用法 CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) UNIQUE, -- 用户名唯一 email VARCHAR(100) UNIQUE, -- 邮箱唯一 phone VARCHAR(20) ); -- 插入数据 INSERT INTO users VALUES (1, 'zhangsan', 'zhang@example.com', '13800138000'); INSERT INTO users VALUES (2, 'zhangsan', 'li@example.com', '13900139000'); -- ERROR 1062: Duplicate entry 'zhangsan' for key 'username' 复合唯一约束 -- IP访问日志（同一IP在同一天只能记录一次） CREATE TABLE access_logs ( id INT PRIMARY KEY AUTO_INCREMENT, ip VARCHAR(15), access_date DATE, UNIQUE KEY uk_ip_date (ip, access_date) -- 复合唯一约束 ); 添加/删除唯一约束 -- 添加唯一约束 ALTER TABLE users ADD UNIQUE (username); ALTER TABLE users ADD UNIQUE KEY uk_email (email); -- 指定约束名 -- 删除唯一约束 ALTER TABLE users DROP INDEX username; ALTER TABLE users DROP INDEX uk_email; 非空约束（NOT NULL） 特点 ✅ 字段不能为NULL ✅ 必须提供值 基础用法 CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) NOT NULL, -- 不能为空 email VARCHAR(100) NOT NULL, nickname VARCHAR(50) -- 可以为空 ); -- 插入数据 INSERT INTO users (id, username, email) VALUES (1, 'zhangsan', 'zhang@example.com'); -- 成功 INSERT INTO users (id, username) VALUES (2, 'lisi'); -- 失败：email不能为空 NULL vs 空字符串 -- NULL：未知、不存在 INSERT INTO users (id, username, email, nickname) VALUES (1, 'zhang', 'zhang@example.com', NULL); -- 空字符串：已知但为空 INSERT INTO users (id, username, email, nickname) VALUES (2, 'li', 'li@example.com', ''); -- 查询 SELECT * FROM users WHERE nickname IS NULL; -- 查到id=1 SELECT * FROM users WHERE nickname = ''; -- 查到id=2 重要区别： ...

引言 提出问题 使用MySQL时，经常遇到字符编码问题： 中文乱码：插入"你好"变成"???" emoji存储失败：插入😀报错 utf8和utf8mb4有什么区别？ 为什么查询"a"和"A"结果一样？ 这就是字符集（Character Set）和校对规则（Collation）的问题！ 字符集基础 什么是字符集？ 字符集：定义了字符与二进制编码的映射关系。 示例： ASCII：A → 0x41（65） UTF-8：你 → 0xE4BDA0 UTF-8：😀 → 0xF09F9880 MySQL常用字符集 字符集 字节数 支持字符 推荐使用 latin1 1字节 西欧字符 ❌ 不支持中文 gbk 1-2字节 简体中文 ⚠️ 老项目 utf8 1-3字节 多语言 ❌ 不支持emoji utf8mb4 1-4字节 多语言+emoji ✅ 推荐 utf8 vs utf8mb4 ⚠️ 重要：MySQL的utf8不是真正的UTF-8！ utf8：最多3字节，不支持emoji（😀需要4字节） utf8mb4：最多4字节，支持emoji和生僻字 -- ❌ 错误：utf8存储emoji CREATE TABLE test (name VARCHAR(50)) CHARACTER SET utf8; INSERT INTO test VALUES ('你好😀'); -- ERROR 1366: Incorrect string value -- ✅ 正确：utf8mb4存储emoji CREATE TABLE test (name VARCHAR(50)) CHARACTER SET utf8mb4; INSERT INTO test VALUES ('你好😀'); -- 成功 查看字符集 -- 查看数据库字符集 SHOW VARIABLES LIKE 'character_set%'; -- 输出示例： +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | +--------------------------+----------------------------+ -- 查看表的字符集 SHOW CREATE TABLE users; 校对规则（Collation） 什么是校对规则？ 校对规则：定义字符的比较和排序规则。 ...

引言 提出问题 在使用MySQL过程中，经常需要： 如何备份数据库？ 如何将Excel数据导入MySQL？ 如何将MySQL数据导出为Excel或CSV？ 如何迁移数据到另一台服务器？ 测试环境如何快速导入生产数据？ 这就是数据导入导出（Import/Export）的核心技能！ mysqldump：数据备份工具 基础语法 mysqldump [options] database [tables] > backup.sql 备份单个数据库 # 备份整个数据库 mysqldump -u root -p mydb > mydb_backup.sql # 执行后，输入密码 Enter password: **** # 查看备份文件 ls -lh mydb_backup.sql # -rw-r--r-- 1 user staff 1.2M Jan 15 10:30 mydb_backup.sql 备份单张表 # 备份指定表 mysqldump -u root -p mydb users > users_backup.sql # 备份多张表 mysqldump -u root -p mydb users orders > tables_backup.sql 备份多个数据库 # 备份多个数据库 mysqldump -u root -p --databases db1 db2 db3 > multi_db_backup.sql 备份所有数据库 # 备份所有数据库（包括系统库） mysqldump -u root -p --all-databases > all_databases_backup.sql 常用选项 # 完整备份命令（推荐） mysqldump -u root -p \ --single-transaction \ # 一致性快照（InnoDB） --routines \ # 包含存储过程和函数 --triggers \ # 包含触发器 --events \ # 包含事件 --default-character-set=utf8mb4 \ # 字符集 mydb > mydb_full_backup.sql 选项 作用 --single-transaction 不锁表，适合InnoDB --lock-tables 锁表备份，适合MyISAM --no-data 只备份结构，不备份数据 --no-create-info 只备份数据，不备份结构 --where='条件' 按条件备份数据 只备份表结构 # 只备份表结构（DDL） mysqldump -u root -p --no-data mydb > mydb_schema.sql 只备份数据 # 只备份数据（不含表结构） mysqldump -u root -p --no-create-info mydb > mydb_data.sql 按条件备份 # 备份最近30天的订单 mysqldump -u root -p mydb orders \ --where="created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" \ > orders_recent.sql 数据恢复（导入） 恢复数据库 # 方式1：命令行导入 mysql -u root -p mydb < mydb_backup.sql # 方式2：登录后导入 mysql -u root -p mysql> USE mydb; mysql> SOURCE /path/to/mydb_backup.sql; 创建数据库后恢复 # 如果数据库不存在，先创建 mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4;" # 再导入数据 mysql -u root -p mydb < mydb_backup.sql 恢复单张表 # 恢复单张表 mysql -u root -p mydb < users_backup.sql 进度显示 # 使用pv工具显示进度 pv mydb_backup.sql | mysql -u root -p mydb # 输出示例： # 1.2GB 0:05:30 [3.6MB/s] [=========> ] 45% ETA 0:06:45 CSV/TXT文件导入导出 导出为CSV -- 导出为CSV文件 SELECT * FROM users INTO OUTFILE '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; -- 执行结果： Query OK, 1000 rows affected (0.02 sec) 注意事项： ...

引言 回顾与展望 前9篇文章中，我们学习了： ✅ 数据库基础概念 ✅ MySQL安装配置 ✅ DDL（创建表） ✅ 数据类型选择 ✅ DML（增删改查） ✅ 约束与完整性 ✅ 字符集处理 ✅ 数据导入导出 现在，让我们综合运用这些知识，搭建一个完整的用户管理系统！ 系统需求 实现一个用户管理系统，支持： 用户注册（唯一用户名、邮箱验证） 用户登录（密码加密存储） 个人信息管理（修改资料、上传头像） 角色权限管理（管理员、普通用户） 登录日志记录 数据统计（用户数、登录次数） 需求分析 功能模块 用户管理系统 ├── 用户模块 │ ├── 注册 │ ├── 登录 │ ├── 修改资料 │ └── 注销账户 ├── 角色权限模块 │ ├── 角色管理 │ ├── 权限分配 │ └── 权限验证 └── 日志模块 ├── 登录日志 └── 操作日志 数据库设计 需要以下数据表： users：用户表（核心表） roles：角色表 permissions：权限表 role_permissions：角色权限关联表 user_roles：用户角色关联表 login_logs：登录日志表 数据库设计 创建数据库 -- 创建数据库 CREATE DATABASE IF NOT EXISTS user_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 使用数据库 USE user_management; 1. 用户表（users） CREATE TABLE users ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（唯一）', password VARCHAR(255) NOT NULL COMMENT '密码（SHA256加密）', email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱（唯一）', phone VARCHAR(20) COMMENT '手机号', real_name VARCHAR(50) COMMENT '真实姓名', nickname VARCHAR(50) COMMENT '昵称', avatar VARCHAR(255) COMMENT '头像URL', gender ENUM('male', 'female', 'other') DEFAULT 'other' COMMENT '性别', birthday DATE COMMENT '生日', bio TEXT COMMENT '个人简介', status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用 2待激活', login_count INT UNSIGNED DEFAULT 0 COMMENT '登录次数', last_login_at TIMESTAMP NULL COMMENT '最后登录时间', last_login_ip VARCHAR(45) COMMENT '最后登录IP', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted_at TIMESTAMP NULL COMMENT '删除时间（软删除）', INDEX idx_username (username), INDEX idx_email (email), INDEX idx_phone (phone), INDEX idx_status (status), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; 2. 角色表（roles） CREATE TABLE roles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称', display_name VARCHAR(100) COMMENT '显示名称', description VARCHAR(255) COMMENT '角色描述', level INT DEFAULT 0 COMMENT '角色级别（数字越大权限越高）', status TINYINT DEFAULT 1 COMMENT '状态：1启用 0禁用', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_level (level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表'; 3. 权限表（permissions） CREATE TABLE permissions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL UNIQUE COMMENT '权限名称（如user.create）', display_name VARCHAR(100) COMMENT '显示名称', description VARCHAR(255) COMMENT '权限描述', module VARCHAR(50) COMMENT '所属模块（user、order、product）', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表'; 4. 角色权限关联表（role_permissions） CREATE TABLE role_permissions ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, role_id INT UNSIGNED NOT NULL COMMENT '角色ID', permission_id INT UNSIGNED NOT NULL COMMENT '权限ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_role_permission (role_id, permission_id), INDEX idx_role_id (role_id), INDEX idx_permission_id (permission_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表'; 5. 用户角色关联表（user_roles） CREATE TABLE user_roles ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL COMMENT '用户ID', role_id INT UNSIGNED NOT NULL COMMENT '角色ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_user_role (user_id, role_id), INDEX idx_user_id (user_id), INDEX idx_role_id (role_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表'; 6. 登录日志表（login_logs） CREATE TABLE login_logs ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL COMMENT '用户ID', login_ip VARCHAR(45) NOT NULL COMMENT '登录IP', user_agent TEXT COMMENT '浏览器UA', status TINYINT DEFAULT 1 COMMENT '状态：1成功 0失败', failure_reason VARCHAR(255) COMMENT '失败原因', login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间', INDEX idx_user_id (user_id), INDEX idx_login_at (login_at), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表'; 初始化数据 插入默认角色 -- 插入角色 INSERT INTO roles (name, display_name, description, level) VALUES ('admin', '超级管理员', '拥有所有权限', 100), ('manager', '管理员', '拥有大部分管理权限', 50), ('user', '普通用户', '基础用户权限', 1); 插入权限 -- 插入权限 INSERT INTO permissions (name, display_name, description, module) VALUES -- 用户模块 ('user.create', '创建用户', '可以创建新用户', 'user'), ('user.read', '查看用户', '可以查看用户信息', 'user'), ('user.update', '修改用户', '可以修改用户信息', 'user'), ('user.delete', '删除用户', '可以删除用户', 'user'), -- 角色模块 ('role.create', '创建角色', '可以创建新角色', 'role'), ('role.read', '查看角色', '可以查看角色', 'role'), ('role.update', '修改角色', '可以修改角色', 'role'), ('role.delete', '删除角色', '可以删除角色', 'role'), -- 系统模块 ('system.setting', '系统设置', '可以修改系统设置', 'system'), ('system.log', '查看日志', '可以查看系统日志', 'system'); 分配权限给角色 -- 超级管理员拥有所有权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions; -- 管理员拥有用户模块权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE module = 'user'; -- 普通用户只有查看权限 INSERT INTO role_permissions (role_id, permission_id) SELECT 3, id FROM permissions WHERE name IN ('user.read'); 创建管理员账户 -- 创建超级管理员 INSERT INTO users (username, password, email, real_name, status) VALUES ( 'admin', SHA2('Admin@123456', 256), -- 密码加密 'admin@example.com', '系统管理员', 1 ); -- 分配超级管理员角色 INSERT INTO user_roles (user_id, role_id) VALUES (1, 1); -- user_id=1, role_id=1（超级管理员） 核心功能实现 功能1：用户注册 -- 注册新用户 INSERT INTO users (username, password, email, phone, real_name) VALUES ( 'zhangsan', SHA2('Password@123', 256), -- 密码加密 'zhangsan@example.com', '13800138000', '张三' ); -- 获取刚插入的用户ID SET @new_user_id = LAST_INSERT_ID(); -- 分配默认角色（普通用户） INSERT INTO user_roles (user_id, role_id) VALUES (@new_user_id, 3); -- role_id=3（普通用户） 功能2：用户登录 -- 验证用户名和密码 SELECT u.id, u.username, u.email, u.real_name, u.nickname, u.avatar, u.status, GROUP_CONCAT(r.name) AS roles FROM users u LEFT JOIN user_roles ur ON u.id = ur.user_id LEFT JOIN roles r ON ur.role_id = r.id WHERE u.username = 'zhangsan' AND u.password = SHA2('Password@123', 256) AND u.status = 1 GROUP BY u.id; -- 如果查到记录，登录成功 -- 更新登录信息 UPDATE users SET login_count = login_count + 1, last_login_at = CURRENT_TIMESTAMP, last_login_ip = '192.168.1.100' WHERE id = 2; -- 记录登录日志 INSERT INTO login_logs (user_id, login_ip, user_agent, status) VALUES (2, '192.168.1.100', 'Mozilla/5.0...', 1); 功能3：权限验证 -- 检查用户是否拥有指定权限 SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.id = 2 AND p.name = 'user.create'; -- 如果结果>0，说明有权限 功能4：修改个人信息 -- 修改昵称和头像 UPDATE users SET nickname = '张小三', avatar = 'https://example.com/avatars/zhangsan.jpg', updated_at = CURRENT_TIMESTAMP WHERE id = 2; 功能5：修改密码 -- 验证旧密码，修改新密码 UPDATE users SET password = SHA2('NewPassword@456', 256), updated_at = CURRENT_TIMESTAMP WHERE id = 2 AND password = SHA2('Password@123', 256); -- 验证旧密码 -- 检查affected rows -- 如果=1，说明旧密码正确，修改成功 -- 如果=0，说明旧密码错误 功能6：软删除用户 -- 软删除（推荐） UPDATE users SET status = 0, deleted_at = CURRENT_TIMESTAMP WHERE id = 2; -- 查询时排除已删除用户 SELECT * FROM users WHERE deleted_at IS NULL -- 未删除 AND status = 1; -- 正常状态 功能7：数据统计 -- 统计总用户数 SELECT COUNT(*) AS total_users FROM users WHERE deleted_at IS NULL; -- 统计各角色用户数 SELECT r.display_name AS role_name, COUNT(ur.user_id) AS user_count FROM roles r LEFT JOIN user_roles ur ON r.id = ur.role_id LEFT JOIN users u ON ur.user_id = u.id AND u.deleted_at IS NULL GROUP BY r.id, r.display_name ORDER BY user_count DESC; -- 统计最近7天注册用户 SELECT DATE(created_at) AS date, COUNT(*) AS register_count FROM users WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE(created_at) ORDER BY date; -- 统计最近7天登录日志 SELECT DATE(login_at) AS date, COUNT(*) AS login_count, COUNT(DISTINCT user_id) AS unique_users FROM login_logs WHERE login_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND status = 1 GROUP BY DATE(login_at) ORDER BY date; 数据完整性验证 测试1：唯一约束 -- 尝试注册重复用户名 INSERT INTO users (username, password, email) VALUES ('zhangsan', SHA2('test', 256), 'zhangsan2@example.com'); -- ERROR 1062: Duplicate entry 'zhangsan' for key 'username' -- 尝试注册重复邮箱 INSERT INTO users (username, password, email) VALUES ('zhangsan2', SHA2('test', 256), 'zhangsan@example.com'); -- ERROR 1062: Duplicate entry 'zhangsan@example.com' for key 'email' 测试2：非空约束 -- 尝试注册时不提供密码 INSERT INTO users (username, email) VALUES ('test', 'test@example.com'); -- ERROR 1364: Field 'password' doesn't have a default value 测试3：数据一致性 -- 查询用户的角色和权限 SELECT u.username, r.display_name AS role, GROUP_CONCAT(p.display_name) AS permissions FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id LEFT JOIN role_permissions rp ON r.id = rp.role_id LEFT JOIN permissions p ON rp.permission_id = p.id WHERE u.id = 2 GROUP BY u.id, u.username, r.display_name; 性能优化 索引优化 -- 查看表的索引 SHOW INDEX FROM users; -- 为常用查询字段添加索引 CREATE INDEX idx_last_login_at ON users(last_login_at); CREATE INDEX idx_login_count ON users(login_count); -- 复合索引（用户名+状态） CREATE INDEX idx_username_status ON users(username, status); 查询优化 -- ❌ 不推荐：SELECT * SELECT * FROM users WHERE username = 'zhangsan'; -- ✅ 推荐：只查询需要的列 SELECT id, username, email, real_name FROM users WHERE username = 'zhangsan'; -- ✅ 推荐：使用LIMIT限制结果 SELECT * FROM users ORDER BY created_at DESC LIMIT 10; 安全加固 1. 密码安全 -- ✅ 使用SHA256加密（基础） SHA2('password', 256) -- ✅ 更安全：加盐（Salt） CONCAT(SHA2(CONCAT('password', 'random_salt'), 256)) -- ✅ 最佳实践：使用bcrypt（应用层实现） 2. SQL注入防护 -- ❌ 危险：字符串拼接 SELECT * FROM users WHERE username = '{$username}'; -- 如果username = "admin' OR '1'='1"，会查出所有用户 -- ✅ 安全：使用预处理语句（Prepared Statement） -- 在应用层使用PDO或MySQLi的预处理 3. 权限最小化 -- 创建只读用户（用于查询） CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT ON user_management.* TO 'readonly'@'localhost'; -- 创建普通用户（不能删除） CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE ON user_management.* TO 'webapp'@'localhost'; 完整测试流程 1. 创建测试用户 -- 注册3个测试用户 INSERT INTO users (username, password, email, real_name) VALUES ('alice', SHA2('Alice@123', 256), 'alice@example.com', 'Alice'), ('bob', SHA2('Bob@123', 256), 'bob@example.com', 'Bob'), ('charlie', SHA2('Charlie@123', 256), 'charlie@example.com', 'Charlie'); -- 分配角色 INSERT INTO user_roles (user_id, role_id) VALUES (LAST_INSERT_ID()-2, 2), -- alice: manager (LAST_INSERT_ID()-1, 3), -- bob: user (LAST_INSERT_ID(), 3); -- charlie: user 2. 测试登录 -- Alice登录 INSERT INTO login_logs (user_id, login_ip, status) VALUES (2, '192.168.1.101', 1); -- Bob登录 INSERT INTO login_logs (user_id, login_ip, status) VALUES (3, '192.168.1.102', 1); -- 登录失败（密码错误） INSERT INTO login_logs (user_id, login_ip, status, failure_reason) VALUES (4, '192.168.1.103', 0, '密码错误'); 3. 测试权限 -- Alice（管理员）创建用户 SELECT '权限验证：Alice创建用户' AS action; SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.username = 'alice' AND p.name = 'user.create'; -- 结果：1（有权限） -- Bob（普通用户）创建用户 SELECT '权限验证：Bob创建用户' AS action; SELECT COUNT(*) AS has_permission FROM users u JOIN user_roles ur ON u.id = ur.user_id JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.username = 'bob' AND p.name = 'user.create'; -- 结果：0（无权限） 总结与展望 本案例涵盖的知识点 ✅ 数据库设计： ...

引言 在第一阶段我们学习了基础的 SELECT 查询，但在实际开发中，简单的 SELECT * FROM table 远远不够。我们需要根据各种复杂的业务条件来过滤数据，比如： 查询价格在100-500元之间的商品 查找姓"张"的所有员工 筛选订单金额大于1000且状态为"已支付"的订单 查询手机号为空或邮箱未验证的用户 这些都需要通过 WHERE 子句配合各种运算符来实现。 为什么WHERE查询如此重要？ 数据过滤的核心：90%的查询都需要条件过滤 性能的关键：合理的WHERE条件能利用索引，大幅提升查询速度 业务逻辑的体现：复杂的业务规则需要通过条件组合来实现 数据安全：通过WHERE条件控制数据访问范围 本文将系统讲解WHERE子句的各类运算符和使用技巧，让你能够编写出精准、高效的条件查询。 一、WHERE子句基础 1.1 WHERE的基本语法 SELECT column1, column2, ... FROM table_name WHERE condition; 执行顺序： FROM：确定要查询的表 WHERE：过滤出符合条件的行 SELECT：选择要返回的列 1.2 准备测试数据 让我们创建一个商品表来演示各种查询： -- 创建商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category VARCHAR(50), price DECIMAL(10, 2), stock INT, created_at DATE, description TEXT ); -- 插入测试数据 INSERT INTO products (name, category, price, stock, created_at, description) VALUES ('iPhone 15 Pro', '手机', 7999.00, 50, '2024-09-20', 'Apple最新旗舰手机'), ('华为Mate 60', '手机', 6999.00, 80, '2024-08-15', '华为高端旗舰'), ('小米14', '手机', 3999.00, 120, '2024-10-01', '小米年度旗舰'), ('MacBook Pro', '电脑', 14999.00, 30, '2024-07-10', 'Apple笔记本电脑'), ('联想ThinkPad', '电脑', 8999.00, 45, '2024-06-20', '商务笔记本'), ('AirPods Pro', '耳机', 1999.00, 200, '2024-09-01', '苹果降噪耳机'), ('索尼WH-1000XM5', '耳机', 2499.00, 60, '2024-05-15', '索尼降噪耳机'), ('iPad Air', '平板', 4799.00, 70, '2024-08-20', 'Apple平板电脑'), ('小米平板6', '平板', 1999.00, 90, '2024-07-25', '小米高性价比平板'), ('罗技MX Master', '鼠标', 699.00, 150, '2024-04-10', '罗技旗舰鼠标'); 二、比较运算符 比较运算符用于比较两个值的大小关系。 ...

引言 在实际开发中，我们经常需要对查询结果进行排序和分页： 商品列表按价格从低到高排序 文章列表按发布时间倒序显示 用户列表分页展示，每页20条 排行榜按得分从高到低排序 这些需求都需要通过 ORDER BY 和 LIMIT 来实现。 为什么排序和分页如此重要？ 用户体验：有序的数据更符合用户的阅读习惯 性能优化：分页可以减少数据传输量，提升响应速度 业务需求：排行榜、Top N查询等场景必不可少 数据管理：便于数据的浏览和检索 本文将深入讲解排序和分页的原理、语法、性能优化技巧，以及如何解决深分页问题。 一、ORDER BY 排序基础 1.1 基本语法 SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...; 执行顺序： FROM：确定要查询的表 WHERE：过滤出符合条件的行 ORDER BY：对结果进行排序 SELECT：选择要返回的列 排序方向： ASC：升序（Ascending），从小到大，默认值 DESC：降序（Descending），从大到小 1.2 准备测试数据 继续使用上一篇的商品表，并添加一些新数据： -- 补充更多测试数据 INSERT INTO products (name, category, price, stock, created_at, description) VALUES ('iPhone 14', '手机', 5999.00, 100, '2024-09-01', 'Apple上代旗舰'), ('小米13', '手机', 3299.00, 150, '2024-03-15', '小米上代旗舰'), ('华为P60', '手机', 4999.00, 80, '2024-04-20', '华为影像旗舰'), ('戴尔XPS', '电脑', 9999.00, 25, '2024-05-10', '戴尔高端笔记本'), ('Surface Pro', '平板', 6999.00, 40, '2024-06-15', '微软二合一平板'); 二、单字段排序 2.1 数值字段排序 -- 按价格升序排列（从低到高） SELECT name, price FROM products ORDER BY price ASC; -- 等价写法（ASC可省略） SELECT name, price FROM products ORDER BY price; 结果： ...

引言 在数据分析和报表统计中,我们经常需要对数据进行汇总计算： 统计商品总数、总销售额 计算平均价格、平均评分 找出最高价、最低价 统计用户数、订单数 这些需求都需要通过聚合函数（Aggregate Functions）来实现。 为什么聚合函数如此重要？ 数据分析的基础：90%的报表都需要聚合统计 业务指标计算：GMV、客单价、转化率等核心指标 性能优化关键：数据库层面的聚合比应用层效率高 决策支持：为业务决策提供数据依据 本文将系统讲解MySQL的五大聚合函数，以及它们在实际开发中的应用。 一、聚合函数基础 1.1 什么是聚合函数？ 聚合函数对一组值执行计算，返回单个值。 五大聚合函数： COUNT()：计数 SUM()：求和 AVG()：平均值 MAX()：最大值 MIN()：最小值 1.2 基本语法 SELECT aggregate_function(column_name) FROM table_name WHERE condition; 1.3 聚合函数的特点 输入多行，输出一行：对多条记录进行计算，返回一个结果 忽略NULL值：除了 COUNT(*) 外，其他聚合函数都忽略NULL 可与GROUP BY结合：对分组后的每组数据分别聚合 不能在WHERE中使用：WHERE是在聚合之前执行的 1.4 准备测试数据 -- 创建订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_name VARCHAR(100), price DECIMAL(10, 2), quantity INT, order_date DATE, status VARCHAR(20) ); -- 插入测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, 'iPhone 15 Pro', 7999.00, 1, '2024-11-01', 'completed'), (1, 'AirPods Pro', 1999.00, 1, '2024-11-02', 'completed'), (2, '华为Mate 60', 6999.00, 1, '2024-11-03', 'completed'), (2, '小米14', 3999.00, 2, '2024-11-04', 'completed'), (3, 'MacBook Pro', 14999.00, 1, '2024-11-05', 'pending'), (3, 'iPad Air', 4799.00, 1, '2024-11-06', 'completed'), (4, '小米13', 3299.00, 1, '2024-11-07', 'cancelled'), (5, '联想ThinkPad', NULL, 1, '2024-11-08', 'completed'), -- 价格为NULL (6, 'AirPods Pro', 1999.00, 2, '2024-11-09', 'completed'), (7, '索尼WH-1000XM5', 2499.00, 1, '2024-11-10', 'completed'); 二、COUNT() - 计数函数 2.1 COUNT(*) - 统计总行数 统计所有行数，包括NULL值的行。 ...

引言 在上一篇我们学习了聚合函数（COUNT、SUM、AVG、MAX、MIN），它们能对整个结果集进行统计。但在实际开发中，我们经常需要分组统计： 统计每个类别的商品数量和平均价格 分析每个用户的订单总额和订单数 计算每个月的销售额和订单量 按地区统计客户数量和消费金额 这些需求都需要通过 GROUP BY 来实现——先分组，再对每组数据进行聚合计算。 为什么GROUP BY如此重要？ 多维度分析：从不同角度分析数据（按时间、地区、类别等） 报表统计的核心：几乎所有报表都需要分组统计 业务洞察：发现不同群体的差异和规律 决策支持：为精细化运营提供数据依据 本文将系统讲解GROUP BY的原理、语法、使用技巧，以及HAVING子句的应用。 一、GROUP BY 基础 1.1 什么是分组查询？ 分组查询将数据按照某个或某些列的值进行分组，然后对每个组分别进行聚合计算。 执行流程： FROM：确定要查询的表 WHERE：过滤行（在分组之前） GROUP BY：将数据分组 HAVING：过滤分组（在分组之后） SELECT：选择要返回的列和聚合结果 ORDER BY：对结果排序 LIMIT：限制返回行数 1.2 基本语法 SELECT column1, aggregate_function(column2) FROM table_name WHERE condition GROUP BY column1 HAVING group_condition ORDER BY column1; 1.3 准备测试数据 继续使用上一篇的订单表，并补充一些数据： -- 补充更多测试数据 INSERT INTO orders (user_id, product_name, price, quantity, order_date, status) VALUES (1, '小米13', 3299.00, 1, '2024-11-11', 'completed'), (2, 'iPad Air', 4799.00, 1, '2024-11-12', 'completed'), (3, 'AirPods Pro', 1999.00, 1, '2024-11-13', 'completed'), (4, '华为Mate 60', 6999.00, 1, '2024-11-14', 'completed'), (5, '小米14', 3999.00, 1, '2024-11-15', 'pending'), (6, 'MacBook Pro', 14999.00, 1, '2024-11-16', 'completed'), (7, 'iPhone 15 Pro', 7999.00, 1, '2024-11-17', 'cancelled'); 二、单字段分组 2.1 基础分组统计 -- 统计每个用户的订单数量 SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id; 结果： ...

引言 实际开发中，数据通常分散在多个表中。要获取完整信息，需要将多个表关联起来查询。本文讲解MySQL的多表连接。 一、连接的本质：笛卡尔积 1.1 笛卡尔积 -- 创建测试表 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, '张三'), (2, '李四'), (3, '王五'); INSERT INTO orders VALUES (1, 1, 100), (2, 1, 200), (3, 2, 150); -- 笛卡尔积：3 * 3 = 9 条记录 SELECT * FROM users, orders; 结果：所有可能的组合（9条）。 1.2 加上连接条件 -- 只保留有意义的组合 SELECT * FROM users, orders WHERE users.id = orders.user_id; 这就是连接的本质：笛卡尔积 + 过滤条件。 二、INNER JOIN - 内连接 2.1 基本语法 SELECT columns FROM table1 INNER JOIN table2 ON table1.key = table2.key; 2.2 示例 -- 查询用户及其订单信息 SELECT u.name, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id; 结果： ...

引言 子查询（Subquery）是嵌套在其他查询中的SELECT语句，用于实现复杂的查询逻辑。 一、子查询分类 1.1 按返回结果分类 标量子查询：返回单个值（1行1列） 列子查询：返回一列多行 行子查询：返回一行多列 表子查询：返回多行多列 1.2 按执行方式分类 非相关子查询：独立执行，与外层查询无关 相关子查询：依赖外层查询，每行都执行一次 二、标量子查询 返回单个值，可用于比较运算。 -- 查询价格高于平均价格的商品 SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products); -- 查询销量最高的商品 SELECT name, sales FROM products WHERE sales = (SELECT MAX(sales) FROM products); 三、列子查询 返回一列多行，通常配合IN、ANY、ALL使用。 3.1 IN / NOT IN -- 查询有订单的用户 SELECT name FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders); -- 查询没有订单的用户 SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); ⚠️ NOT IN的陷阱：子查询包含NULL会返回空结果。 ...