MySQL数据库表创建与多表查询实战指南 一、MySQL基础认知 1. 数据库管理系统核心功能 2. 表结构设计原则 3. SQL语言特性解析 二、数据库表创建全流程 1. 创建数据库基础语法 CREATE DATABA […]
- MySQL数据库表创建与多表查询实战指南
- 一、MySQL基础认知
- 1. 数据库管理系统核心功能
- 2. 表结构设计原则
- 3. SQL语言特性解析
- 二、数据库表创建全流程
- 1. 创建数据库基础语法
- CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 2. 表结构设计规范
- - 字段命名采用小驼峰
- - 主键使用INT UNSIGNED AUTO_INCREMENT
- - 时间字段统一TIMESTAMP(6)
- 3. 基础建表语法
- CREATE TABLE users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录账号', password CHAR(64) NOT NULL COMMENT '密码哈希值', create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
- 三、进阶约束设置
- 1. 主键约束
- PRIMARY KEY (id)
- 2. 外键约束
- FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
- 3. 唯一约束
- UNIQUE (email)
- 四、索引优化策略
- 1. 普通索引
- CREATE INDEX idx_username ON users(username);
- 2. 联合索引
- CREATE INDEX idx_search ON orders(user_id, order_status);
- 3. 全文索引
- CREATE FULLTEXT INDEX idx_content ON articles(content);
- 五、多表查询核心技术
- 1. 内连接查询
- SELECT u.username, o.order_no FROM users u INNER JOIN orders o ON u.user_id = o.user_id;
- 2. 左外连接
- SELECT u.username, o.order_no FROM users u LEFT JOIN orders o ON u.user_id = o.user_id;
- 3. 子查询应用
- SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
- 六、事务处理机制
- 1. 显式事务控制
- BEGIN;UPDATE accounts SET balance = balance - 100 WHERE account_id=1;UPDATE accounts SET balance = balance + 100 WHERE account_id=2;COMMIT;
- 2. 锁定机制
- SELECT * FROM products WHERE product_id=1 FOR UPDATE;
- 七、性能优化技巧
- 1. 查询缓存
- SELECT SQL_CACHE * FROM products WHERE category='electronics';
- 2. 分页优化
- SELECT * FROM logs ORDER BY log_time DESC LIMIT 100000, 20;
- 3. 索引优化
- EXPLAIN SELECT * FROM orders WHERE user_id=123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';
- 八、实战案例解析
- 1. 用户订单系统设计
- CREATE TABLE orders ( order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, order_amount DECIMAL(10,2) NOT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (product_id) REFERENCES products(product_id));
- 2. 复杂查询示例
- SELECT u.username, p.product_name, o.order_amount FROM orders o JOIN users u USING(user_id) JOIN products p ON o.product_id = p.product_id WHERE o.create_time BETWEEN '2023-01-01' AND '2023-12-31';
- 九、常见问题解决方案
- 1. 死锁处理
- SHOW ENGINE INNODB STATUS;
- 2. 性能瓶颈排查
- SHOW PROCESSLIST;
- 3. 数据迁移方案
- mysqldump --single-transaction dbname > backup.sql
- 十、最佳实践总结
- 1. 规范命名约定
- 2. 定期维护优化
- 3. 权限最小化原则
- 十一、未来趋势展望
- 1. HTAP架构演进
- 2. 新型存储引擎
- 3. 自动化运维工具
