基础入门

引言 回顾与展望 前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（无权限） 总结与展望 本案例涵盖的知识点 ✅ 数据库设计： ...

引言 提出问题 在使用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) 注意事项： ...

引言 提出问题 使用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） 什么是校对规则？ 校对规则：定义字符的比较和排序规则。 ...

引言 提出问题 前面我们学会了增删改查，但遇到一些问题： 如何防止插入重复的用户名？ 如何保证用户年龄不能为负数？ 如何保证订单必须关联到存在的用户？ 如何保证邮箱字段不能为空？ 这就需要数据库约束（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 重要区别： ...

引言 提出问题 前面几篇我们学会了创建数据库和表，但表只是一个空壳，现在要学习： 如何往表里插入数据？（INSERT） 如何修改已有的数据？（UPDATE） 如何删除不需要的数据？（DELETE） 如何查询数据？（SELECT） 批量插入和批量更新如何操作？ 这就是CRUD操作（Create增、Read读、Update改、Delete删），是使用数据库的核心！ DML语句概述 DML（Data Manipulation Language，数据操作语言）：操作表中的数据 操作 语句 作用 对应CRUD 插入 INSERT 向表中添加新数据 Create 查询 SELECT 从表中读取数据 Read 更新 UPDATE 修改表中已有数据 Update 删除 DELETE 从表中删除数据 Delete INSERT：插入数据 基础语法 INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 实战准备：创建测试表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, age INT, status TINYINT DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 插入单行数据 -- 方式1：指定列名（推荐） INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zhangsan@example.com', 25); -- 方式2：不指定列名（必须提供所有列的值，按顺序） INSERT INTO users VALUES (NULL, 'lisi', 'lisi@example.com', 30, 1, CURRENT_TIMESTAMP); -- NULL：让AUTO_INCREMENT自动生成ID -- CURRENT_TIMESTAMP：当前时间 推荐使用方式1，因为： ...

引言 提出问题 上一篇我们学会了创建表，但在定义列时遇到了很多数据类型： 存储用户ID，用 INT 还是 BIGINT？ 存储金额，为什么用 DECIMAL 而不是 FLOAT？ 存储用户名，VARCHAR(50) 和 VARCHAR(255) 有什么区别？ 存储日期，用 DATE、DATETIME 还是 TIMESTAMP？ CHAR 和 VARCHAR 怎么选择？ 选择错误的数据类型会导致： 浪费存储空间（INT能搞定的用BIGINT） 性能下降（VARCHAR(255)比VARCHAR(50)慢） 精度丢失（FLOAT存金额会有误差） 查询出错（日期类型选错导致时区问题） 这篇文章将帮你理解MySQL数据类型的底层原理，学会正确选型！ 数据类型分类 MySQL数据类型分为五大类： 分类 常用类型 应用场景 数值类型 INT, BIGINT, DECIMAL, FLOAT 年龄、金额、计数器 字符串类型 CHAR, VARCHAR, TEXT 用户名、描述、文章内容 日期时间类型 DATE, TIME, DATETIME, TIMESTAMP 创建时间、生日 二进制类型 BLOB, BINARY 图片、文件（不推荐存数据库） JSON类型 JSON 配置项、扩展字段 数值类型 整数类型 五种整数类型 类型 存储空间 有符号范围 无符号范围 典型应用 TINYINT 1字节 -128 ~ 127 0 ~ 255 年龄、状态码 SMALLINT 2字节 -32,768 ~ 32,767 0 ~ 65,535 分类ID MEDIUMINT 3字节 -838万 ~ 838万 0 ~ 1677万 较少使用 INT 4字节 -21亿 ~ 21亿 0 ~ 42亿 用户ID、商品ID BIGINT 8字节 -922京 ~ 922京 0 ~ 1844京 订单ID、大数据 实战示例 CREATE TABLE user_profile ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID（无符号，0-42亿）', age TINYINT UNSIGNED COMMENT '年龄（0-255）', score SMALLINT DEFAULT 0 COMMENT '积分（-32768~32767）', view_count BIGINT UNSIGNED DEFAULT 0 COMMENT '浏览量（大数据）' ); 有符号 vs 无符号（UNSIGNED） -- 有符号（默认） CREATE TABLE test1 (num INT); -- 范围：-2147483648 ~ 2147483647 -- 无符号（推荐用于ID、计数器） CREATE TABLE test2 (num INT UNSIGNED); -- 范围：0 ~ 4294967295 选择原则： ...

引言 提出问题 安装好MySQL后，你可能迫不及待想要存储数据。但是： 如何创建一个数据库？ 如何创建表来存储用户信息、订单数据？ 表创建后发现字段设计不合理，如何修改？ 如何删除不需要的表和数据库？ DDL、DML、DQL这些术语是什么意思？ 这篇文章将带你掌握MySQL的"数据定义语言"，学会管理数据库对象的生命周期！ 为什么重要 DDL（Data Definition Language）是使用MySQL的第一步： ✅ 没有数据库和表，就无法存储数据 ✅ 合理的表结构设计，是性能优化的基础 ✅ 掌握ALTER语句，应对需求变更 ✅ 了解DROP的风险，避免误删数据 基础概念 SQL语言的分类 SQL（Structured Query Language，结构化查询语言）按功能分为四大类： 1. DDL（Data Definition Language，数据定义语言） 作用：定义和管理数据库对象（数据库、表、索引、视图） 核心语句： CREATE：创建数据库对象 ALTER：修改数据库对象 DROP：删除数据库对象 TRUNCATE：清空表数据 RENAME：重命名对象 本文重点讲解DDL！ 2. DML（Data Manipulation Language，数据操作语言） 作用：操作表中的数据 核心语句： INSERT：插入数据 UPDATE：更新数据 DELETE：删除数据 （下一篇详细讲解） 3. DQL（Data Query Language，数据查询语言） 作用：查询数据 核心语句： SELECT：查询数据 （SQL进阶篇详细讲解） 4. DCL（Data Control Language，数据控制语言） 作用：管理用户权限 核心语句： GRANT：授予权限 REVOKE：撤销权限 数据库操作 创建数据库（CREATE DATABASE） 基础语法 CREATE DATABASE database_name; 实战示例 -- 创建一个名为 mydb 的数据库 CREATE DATABASE mydb; -- 执行结果 Query OK, 1 row affected (0.01 sec) 指定字符集和校对规则 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 参数说明： ...

引言 提出问题 前两篇文章中，我们理解了数据库的重要性和MySQL的核心优势。但是： 如何在自己的电脑上安装MySQL？ Windows、Mac、Linux系统的安装方式有什么区别？ 如何快速搭建MySQL开发环境（Docker）？ 安装后如何连接和使用MySQL？ 有哪些好用的MySQL客户端工具？ 这篇文章将带你动手实战，从零开始搭建MySQL开发环境！ 为什么重要 “工欲善其事，必先利其器”。正确安装和配置MySQL是学习的第一步： ✅ 环境搭建好，后续学习才能顺畅 ✅ 掌握多平台安装，适应不同工作环境 ✅ 了解配置文件，为后续性能调优打基础 ✅ 熟悉客户端工具，提升开发效率 基础概念 MySQL的三种安装方式 方式1：官方安装包（推荐新手） 特点： ✅ 安装简单，图形化界面 ✅ 自动配置环境变量 ✅ 集成MySQL Workbench客户端 ❌ 版本更新需要重新下载安装包 适用场景： 开发环境 学习测试 Windows/macOS用户 方式2：包管理器（推荐Linux用户） 特点： ✅ 命令行一键安装 ✅ 版本更新方便（apt upgrade/yum update） ✅ 自动处理依赖关系 ❌ 版本可能不是最新 适用场景： Linux服务器 CI/CD自动化 开发环境 方式3：Docker容器（推荐进阶用户） 特点： ✅ 环境隔离，不污染主机 ✅ 秒级启动和销毁 ✅ 版本切换方便 ❌ 需要掌握Docker基础 适用场景： 快速测试 多版本切换 微服务开发 Windows系统安装 方式一：MSI安装包（推荐） 步骤1：下载MySQL安装包 访问MySQL官网：https://dev.mysql.com/downloads/mysql/ ...

引言 提出问题 在上一篇文章中，我们理解了为什么需要数据库。但是面对市场上众多的数据库产品，你可能会困惑： 为什么阿里巴巴、腾讯、字节跳动都选择MySQL？ MySQL和Oracle有什么区别？为什么Oracle那么贵还有人用？ PostgreSQL被称为"最先进的开源数据库"，为什么MySQL更流行？ 我应该学MySQL还是学其他数据库？ 这篇文章将帮你找到答案！ 为什么重要 选择正确的数据库，关系到： 技术栈：你的职业发展方向 成本：开源 vs 商业授权（百万级差异） 生态：是否有丰富的工具和社区支持 就业：市场对不同数据库技能的需求 数据说话： DB-Engines排名：MySQL长期稳居第2名（仅次于Oracle） TIOBE指数：MySQL是最受欢迎的开源数据库 招聘数据：MySQL相关岗位是Oracle的3倍 MySQL的诞生与发展 起源：一个瑞典小团队的创业故事（1995年） 1995年，三位瑞典工程师创建了MySQL： Michael “Monty” Widenius David Axmark Allan Larsson 创业初衷：他们需要一个快速、可靠、免费的数据库来支持自己的咨询业务，但当时的商业数据库（如Oracle）太贵，开源数据库（如PostgreSQL）太慢。 命名来源： “My”：Monty女儿的名字 “SQL”：结构化查询语言（Structured Query Language） 有趣的细节：MySQL的海豚Logo名叫"Sakila"，来自全球用户投票选出的非洲城市名。 发展历程：从小众到全球第二 阶段一：开源崛起（1995-2000） 1995年5月23日：MySQL 1.0发布 仅支持基本的SQL查询 没有事务支持 但速度快、体积小、免费 关键特性： 采用双授权模式：GPL开源 + 商业授权 专注于速度而非功能完整性 适合Web应用（LAMP架构：Linux + Apache + MySQL + PHP） 阶段二：互联网爆发（2000-2008） 2000年：MySQL 3.23发布 支持全文检索 支持事务（InnoDB存储引擎） 成为互联网公司首选 2003年：MySQL 4.0发布 支持子查询 支持Union 查询缓存 2005年：MySQL 5.0发布 支持存储过程 支持视图 支持触发器 这一时期： ...

引言 提出问题 假设你正在开发一个电商网站，需要存储用户信息、商品数据、订单记录。最简单的方式是什么？把数据写入文本文件！ users.txt: 1,张三,zhangsan@example.com,18888888888 2,李四,lisi@example.com,13999999999 products.txt: 1,iPhone 15,6999,100 2,MacBook Pro,12999,50 orders.txt: 1,1,1,2024-01-15 10:30:00,6999 2,2,2,2024-01-15 11:45:00,12999 但当你的网站有1000万用户时，你会遇到以下问题： 如何快速查找张三的订单记录？（需要遍历1000万行） 如何保证两个用户不会同时购买最后一件商品？（并发问题） 如果服务器突然断电，刚写入一半的订单数据怎么办？（数据一致性） 如何保证用户密码不会被其他程序随意读取？（安全性） 这就是为什么我们需要数据库！ 为什么重要 数据库是现代软件系统的基石： 💰 金融系统：每秒处理上万笔交易，不能有任何差错 🛒 电商平台：双十一期间，亿级用户并发访问 📱 社交媒体：存储数十亿用户的照片、视频、动态 🏥 医疗系统：保存患者病历，关乎生命安全 掌握数据库，你才能： 设计出高效、可靠的系统架构 解决生产环境的性能瓶颈 理解为什么"删库跑路"是工程师最大的噩梦 基础概念 第一性原理：什么是数据？ 在计算机科学中，**数据（Data）**是对现实世界信息的抽象表示。 简单类比： 现实世界：一个名叫"张三"的人，身高180cm，体重75kg 数据表示：{"name": "张三", "height": 180, "weight": 75} 数据的本质是结构化的信息，可以被计算机存储、处理和传输。 从文件到数据库的演进 阶段一：纯文本文件（1960年代） 最原始的数据存储方式： users.txt: 张三,男,25,北京 李四,女,30,上海 王五,男,28,深圳 优点： ✅ 简单直观，易于理解 ✅ 不需要额外的软件支持 缺点： ❌ 查询效率低：需要逐行扫描 ❌ 并发问题：多个程序同时写入会数据混乱 ❌ 数据冗余：同一个数据在多个文件中重复存储 ❌ 无法保证一致性：写入一半断电会导致数据损坏 阶段二：层次数据库（1960-1970年代） 数据以树形结构组织： 公司 ├── 研发部 │ ├── 张三（工号001） │ └── 李四（工号002） └── 销售部 ├── 王五（工号003） └── 赵六（工号004） 优点： ...