一、MySQL 环境准备与连接
1. 命令行连接 MySQL
基本连接语法（默认端口3306）
mysql -u 用户名 -p密码
安全提示：直接在命令行包含密码可能存在安全风险，特别是当命令被记录到历史文件时。更安全的做法是只使用
-p参数，然后在提示符下输入密码。
连接示例
本地连接示例
# 使用root用户连接本地MySQL
mysql -u root -p123456
# 注意：-p和密码之间无空格（若密码含特殊字符，建议不加密码，回车后输入）
# 更安全的连接方式（会提示输入密码）
mysql -u root -p
远程连接示例
# 连接远程MySQL（指定IP和端口）
mysql -h 192.168.1.100 -P 3307 -u test_user -p
# 连接远程MySQL（使用默认端口3306）
mysql -h mysql.example.com -u remote_user -p
参数说明：
-h：指定主机地址（默认为localhost）-P：指定端口号（默认为3306）-u：指定用户名-p：提示输入密码（或直接跟密码）
2. 查看 MySQL 版本
连接成功后，可通过以下语句确认 MySQL 版本，这对确保脚本兼容性和功能可用性非常重要：
SELECT VERSION();
-- 结果示例：8.0.32
-- 也可使用以下命令查看更详细的版本信息
SHOW VARIABLES LIKE "%version%";
典型输出：
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 8.0.32 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.11 |
+-------------------------+------------------------------+
3. 退出 MySQL 连接
当完成数据库操作后，可以使用以下命令退出MySQL命令行界面：
EXIT; -- 或使用 QUIT;
注意：
- 分号(;)是SQL语句的结束符，在MySQL命令行中通常是必需的
- 也可以使用快捷键
Ctrl+D快速退出 - 退出后，所有未提交的事务会自动回滚
二、数据库操作语句
1. 创建数据库（CREATE DATABASE）
完整语法：
CREATE DATABASE [IF NOT EXISTS] 数据库名
[CHARACTER SET 字符集名称]
[COLLATE 排序规则名称];
参数说明：
IF NOT EXISTS：可选参数，当数据库已存在时不会报错，而是跳过执行CHARACTER SET：指定数据库字符集（可简写为CHARSET）COLLATE：指定排序规则
常用字符集：
utf8：基本多语言平面字符，不支持emoji表情utf8mb4：完全兼容utf8，支持4字节字符（如emoji），MySQL 5.5.3+推荐使用
常用排序规则：
utf8mb4_general_ci：不区分大小写的通用排序规则，性能较好utf8mb4_unicode_ci：基于Unicode标准的排序规则，更精确但性能稍差utf8mb4_bin：二进制排序，区分大小写
实际应用示例：
-- 创建电商数据库
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 创建用户数据库（带注释）
CREATE DATABASE `user_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
2. 查询所有数据库（SHOW DATABASES）
基本用法：
SHOW DATABASES;
返回内容：
- 系统数据库：
information_schema（元数据）、mysql（用户权限）、performance_schema（性能监控）等 - 用户自定义数据库
高级用法：
-- 使用LIKE筛选数据库
SHOW DATABASES LIKE '%test%';
-- 查看数据库创建语句
SHOW CREATE DATABASE 数据库名;
3. 切换数据库（USE）
详细说明：
- 切换数据库后，后续SQL操作默认针对该数据库中的对象
- 连接会话中只能有一个当前数据库
- 可以省略后续SQL语句中的数据库名前缀
实际应用：
-- 切换到产品数据库
USE product_db;
-- 等价于（显式指定数据库）
SELECT * FROM product_db.customers;
4. 查看当前使用的数据库（SELECT DATABASE()）
使用场景：
- 验证是否已成功切换数据库
- 在脚本中判断当前操作环境
- 调试SQL语句时确认操作对象
示例输出：
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test_db |
+------------+
1 row in set (0.00 sec)
5. 删除数据库（DROP DATABASE）
风险提示：
- 操作不可逆，会永久删除数据库及其所有表和数据
- 生产环境务必先备份数据
- 建议使用
IF EXISTS防止误删不存在的数据库
安全操作流程：
1.确认数据库内容
SHOW TABLES FROM 待删除数据库;
2.备份重要数据
mysqldump -u root -p 待删除数据库 > backup.sql
3.执行删除
DROP DATABASE IF EXISTS 待删除数据库;
扩展语法：
-- 强制删除（某些MySQL变种支持）
DROP DATABASE 数据库名 WITH LOCK;
系统权限说明：
- 执行删除操作需要
DROP权限 - 可通过
SHOW GRANTS查看当前用户权限
三、数据表操作语句
1. 数据类型（基础常用）
创建表前需先确定字段的数据类型，MySQL 支持多种数据类型，以下是最常用的几种：
数值类型
| 数据类型 | 说明 | 范围 | 示例 |
|---|---|---|---|
| INT | 整数（4 字节） | -2,147,483,648 到 2,147,483,647 | 存储用户ID、年龄 |
| TINYINT | 小整数（1 字节） | -128 到 127 | 存储状态标记（0/1） |
| BIGINT | 大整数（8 字节） | -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 | 存储大额数字 |
| DECIMAL(m,d) | 定点小数 | m是总位数，d是小数位 | 商品价格（DECIMAL(10,2)） |
字符串类型
| 数据类型 | 说明 | 最大长度 | 示例 |
|---|---|---|---|
| VARCHAR(n) | 可变长度字符串 | 65,535字节 | 用户名、地址 |
| CHAR(n) | 固定长度字符串 | 255字节 | 身份证号、手机号 |
| TEXT | 长文本数据 | 65,535字节 | 文章内容 |
日期时间类型
| 数据类型 | 说明 | 格式 | 示例 |
|---|---|---|---|
| DATE | 日期 | YYYY-MM-DD | 生日、入职日期 |
| DATETIME | 日期时间 | YYYY-MM-DD HH:MM:SS | 订单创建时间 |
| TIMESTAMP | 时间戳 | YYYY-MM-DD HH:MM:SS | 记录最后修改时间 |
2. 创建表（CREATE TABLE）
完整语法
CREATE TABLE [IF NOT EXISTS] 表名 (
字段1 数据类型 [列约束] [COMMENT '字段说明'],
字段2 数据类型 [列约束] [COMMENT '字段说明'],
...
[表级约束]
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集] [COLLATE=排序规则] [COMMENT='表说明'];
常用约束说明
- PRIMARY KEY：主键约束，确保每行记录的唯一标识
- NOT NULL：非空约束，字段值不能为空
- UNIQUE：唯一约束，字段值不能重复
- DEFAULT：默认值约束，当插入数据未指定值时使用默认值
- AUTO_INCREMENT：自增约束，通常用于主键
- FOREIGN KEY：外键约束，保证数据完整性
实践示例
创建电商平台的用户表：
CREATE TABLE IF NOT EXISTS ecommerce_users (
user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户唯一ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录用户名',
password CHAR(60) NOT NULL COMMENT '加密后的密码',
real_name VARCHAR(50) COMMENT '真实姓名',
email VARCHAR(100) UNIQUE COMMENT '电子邮箱',
phone CHAR(11) UNIQUE COMMENT '手机号码',
gender ENUM('male','female','other') DEFAULT 'other' COMMENT '性别',
birth_date DATE COMMENT '出生日期',
register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
last_login_time DATETIME COMMENT '最后登录时间',
status TINYINT(1) DEFAULT 1 COMMENT '账号状态：1-正常，0-禁用'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='电商平台用户信息表';
3. 表查询操作
查看数据库中的所有表
SHOW TABLES [FROM 数据库名] [LIKE '模式'];
查看表结构
DESCRIBE 表名;
-- 或
DESC 表名;
-- 或
SHOW COLUMNS FROM 表名;
查看表创建语句
SHOW CREATE TABLE 表名;
4. 修改表结构（ALTER TABLE）
添加字段
ALTER TABLE 表名
ADD COLUMN 新字段名 数据类型 [约束] [FIRST|AFTER 现有字段];
修改字段
-- 修改字段名和类型
ALTER TABLE 表名
CHANGE COLUMN 原字段名 新字段名 新数据类型 [约束];
-- 仅修改字段类型
ALTER TABLE 表名
MODIFY COLUMN 字段名 新数据类型 [约束];
删除字段
ALTER TABLE 表名
DROP COLUMN 字段名;
重命名表
ALTER TABLE 原表名
RENAME TO 新表名;
-- 或
RENAME TABLE 原表名 TO 新表名;
添加索引
ALTER TABLE 表名
ADD INDEX 索引名 (字段1, 字段2...);
5. 删除表（DROP TABLE）
DROP TABLE [IF EXISTS] 表名1 [, 表名2...];
注意事项
- 删除表会永久删除表中所有数据
- 如果表有外键约束，需要先删除约束或相关表
- 生产环境执行前务必先备份数据
6. 表维护操作
清空表数据
TRUNCATE TABLE 表名;
-- 与DELETE的区别：TRUNCATE会重置自增值，且不记录日志
优化表
OPTIMIZE TABLE 表名;
-- 用于整理表碎片，提高性能
修复表
REPAIR TABLE 表名;
-- 仅MyISAM引擎支持
7. 存储引擎选择
MySQL支持多种存储引擎，最常用的是：
- InnoDB：支持事务、行级锁定、外键约束（默认引擎）
- MyISAM：不支持事务，但查询速度快
- MEMORY：数据存储在内存中，速度快但不持久
设置存储引擎：
CREATE TABLE 表名 (...) ENGINE=InnoDB;
-- 或
ALTER TABLE 表名 ENGINE=InnoDB;
8. 字符集与排序规则
常见设置：
-- 创建表时指定
CREATE TABLE 表名 (...) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 修改现有表
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
常用组合：
- utf8mb4 + utf8mb4_unicode_ci：支持完整Unicode（包括emoji）
- utf8mb4 + utf8mb4_general_ci：性能稍好但不精确排序
四、数据操作语句
1. 插入数据（INSERT INTO）
（1）插入单条数据（指定字段）
语法：INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);
详细说明：
- 字段顺序可与表结构不一致，但值的顺序必须与字段顺序严格对应
- 字符串值必须用单引号（'）包裹，例如：'张三'
- 日期/时间类型也需要用单引号包裹，格式为'YYYY-MM-DD'或'YYYY-MM-DD HH:MM:SS'
- 自增字段（如id）无需插入值，MySQL会自动生成，也可以显式传入NULL
- 如果字段有默认值，可以省略不写
示例：向user_info表插入一条用户数据：
INSERT INTO user_info (name, phone, age, sex, register_date)
VALUES ('张三', '13800138000', 25, '男', '2024-01-15');
（2）插入单条数据（不指定字段）
若插入所有字段的值，可省略字段列表，但值的顺序必须与表结构完全一致：
INSERT INTO user_info
VALUES (NULL, '李四', '13900139000', 30, '女', '2024-02-20');
-- id为自增字段，传NULL即可
注意事项：
- 必须提供表中所有列的值（除了自增字段）
- 值的顺序必须与表定义时的列顺序完全一致
- 不推荐在生产环境使用此方式，因为表结构变更可能导致SQL失效
（3）插入多条数据
语法：INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;
应用场景：
- 批量导入数据时效率更高
- 减少数据库连接次数，提高性能
- 适用于初始化数据或数据迁移
示例：一次性插入3条用户数据：
INSERT INTO user_info (name, phone, age, sex, register_date)
VALUES
('王五', '13700137000', 28, '男', '2024-03-10'),
('赵六', '13600136000', 22, '女', '2024-03-15'),
('孙七', '13500135000', 35, '男', '2024-04-05');
性能提示：
- 单条INSERT插入多条数据比多条INSERT语句效率高
- 但单次插入数据量不宜过大，建议控制在1000条以内
2. 查询数据（SELECT）
（1）查询所有字段（*）
语法：SELECT * FROM 表名;
示例：查询user_info表的所有数据：
SELECT * FROM user_info;
注意事项：
- 表示查询所有字段，适合快速查看数据
- 生产环境建议指定具体字段，避免不必要的数据传输
- 当表结构变更时，* 查询可能返回意料之外的字段
（2）查询指定字段
语法：SELECT 字段1, 字段2, ... FROM 表名;
示例：查询user_info表的name、phone、age字段：
SELECT name, phone, age FROM user_info;
最佳实践：
- 只查询需要的字段，减少数据传输量
- 字段名区分大小写（取决于数据库配置）
- 可以使用AS关键字为字段设置别名
（3）条件查询（WHERE）
语法：SELECT 字段 FROM 表名 WHERE 条件;
常用条件运算符：
比较运算符：
=等于!=或<>不等于>大于<小于>=大于等于<=小于等于
范围运算符：
BETWEEN ... AND ...在范围内IN (值1, 值2, ...)在集合中
模糊匹配：
LIKE模糊匹配%匹配任意长度字符（包括0个字符）_匹配单个字符
空值判断：
IS NULL为空IS NOT NULL不为空
逻辑运算符：
AND且OR或NOT非
示例1：查询年龄大于25的用户：
SELECT name, age FROM user_info WHERE age > 25;
示例2：查询性别为"女"且注册日期在2024年3月之后的用户：
SELECT name, sex, register_date
FROM user_info
WHERE sex = '女' AND register_date > '2024-03-01';
示例3：查询手机号以"138"开头的用户（模糊匹配）：
SELECT name, phone
FROM user_info
WHERE phone LIKE '138%'; -- %表示任意长度的字符（包括0个）
示例4：查询年龄在22~30之间的用户（范围匹配）：
SELECT name, age
FROM user_info
WHERE age BETWEEN 22 AND 30; -- 等价于 age >=22 AND age <=30
（4）排序查询（ORDER BY）
语法：SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段1 [ASC/DESC], 字段2 [ASC/DESC];
说明：
ASC：升序（默认，从小到大）DESC：降序（从大到小）- 可以按多个字段排序，先按字段1排序，字段1值相同时按字段2排序
示例：查询所有用户，按年龄降序排列，年龄相同时按注册日期升序排列：
SELECT name, age, register_date
FROM user_info
ORDER BY age DESC, register_date ASC;
（5）限制查询结果（LIMIT）
语法：SELECT 字段 FROM 表名 [WHERE 条件] [ORDER BY 字段] LIMIT 起始索引, 条数;
说明：
- 起始索引：从0开始（若省略，默认从0开始）
- 常用于分页查询（如第1页：LIMIT 0,10；第2页：LIMIT 10,10）
示例1：查询前2条用户数据：
SELECT * FROM user_info LIMIT 2; -- 等价于 LIMIT 0,2
示例2：查询第2页数据（每页2条，即索引2开始，取2条）：
SELECT * FROM user_info LIMIT 2,2;
（6）去重查询（DISTINCT）
语法：SELECT DISTINCT 字段 FROM 表名;
说明：
- 去除字段中重复的值，只保留唯一值
- 可以用于单个字段或多个字段的组合
示例：查询user_info表中所有不重复的性别：
SELECT DISTINCT sex FROM user_info; -- 结果可能为：男、女、未知
3. 更新数据（UPDATE）
语法：UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];
⚠️ 重要警告：
- 若省略WHERE条件，会更新表中所有数据
- 生产环境务必先确认WHERE条件是否正确
- 建议先使用SELECT语句测试WHERE条件
示例1：将"张三"的年龄更新为26：
UPDATE user_info
SET age = 26
WHERE name = '张三';
-- 注意：若有多个"张三"，会全部更新，建议用主键条件（如id=1）
示例2：将"李四"的性别改为"男"，注册日期改为"2024-02-25"：
UPDATE user_info
SET sex = '男', register_date = '2024-02-25'
WHERE id = 2; -- 用主键id定位，确保只更新一条数据
最佳实践：
- 更新前备份重要数据
- 使用事务确保数据一致性
- 一次更新多个字段时，用逗号分隔
4. 删除数据（DELETE）
语法：DELETE FROM 表名 [WHERE 条件];
⚠️ 重要警告：
- 若省略WHERE条件，会删除表中所有数据（表结构保留）
- 删除操作通常无法恢复，请务必谨慎
- 建议先使用SELECT语句测试WHERE条件
示例1：删除id=5的用户（用主键定位，精准删除）：
DELETE FROM user_info
WHERE id = 5;
示例2：删除年龄小于20且性别为"未知"的用户（条件删除）：
DELETE FROM user_info
WHERE age < 20 AND sex = '未知';
清空表的两种方式对比
DELETE FROM 表名;：- 删除表中所有数据
- 自增字段会保留上次的最大值（如上次自增到10，清空后下次插入从11开始）
- 支持事务回滚（可恢复）
- 会记录每条记录的删除操作，速度较慢
TRUNCATE TABLE 表名;：- 删除表中所有数据
- 重置自增字段为1
- 不支持事务回滚（不可恢复）
- 执行速度比DELETE快（适合清空大表）
- 不会记录单条删除操作，而是直接删除表数据文件
五、聚合函数与分组查询
1. 常用聚合函数概述
SQL 提供了多种聚合函数，用于对一组值执行计算并返回单一值：
| 函数名 | 说明 | 详细应用场景示例 |
|---|---|---|
| COUNT() | 统计记录数（非NULL值） | COUNT(*)统计总行数，COUNT(email)统计有邮箱的用户数（NULL值不计数） |
| SUM() | 计算数值字段的总和 | SUM(salary)计算员工薪资总额，SUM(quantity*price)计算订单总金额 |
| AVG() | 计算数值字段的平均值 | AVG(rating)计算产品平均评分，AVG(DATEDIFF(NOW(),hire_date))计算平均在职天数 |
| MAX() | 取字段的最大值 | MAX(temperature)获取最高温度，MAX(transaction_date)获取最近交易日期 |
| MIN() | 取字段的最小值 | MIN(price)查找最低价格，MIN(birth_date)找出最年长的员工 |
实际应用示例
-- 统计电商平台用户总数（确保统计准确，使用主键id）
SELECT COUNT(id) AS total_users FROM users;
-- 计算商品平均价格（保留2位小数）
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;
-- 查找最高和最低订单金额
SELECT MAX(amount) AS max_order, MIN(amount) AS min_order FROM orders;
-- 计算员工平均工龄（年）
SELECT AVG(YEAR(NOW())-YEAR(hire_date)) AS avg_service_years FROM employees;
2. 分组查询（GROUP BY）深度解析
语法规范
SELECT
分组字段,
聚合函数(字段)
FROM
表名
[WHERE 筛选条件]
GROUP BY
分组字段
[HAVING 分组后筛选条件];
关键规则
- SELECT 子句中只能包含：
- GROUP BY 中指定的分组字段
- 聚合函数
- 分组字段可以包含多个列，形成多级分组
- 通常与 ORDER BY 配合使用，使结果更有序
实际业务场景示例
示例1：销售分析（按产品类别分组）
SELECT
category,
COUNT(*) AS product_count,
SUM(units_sold) AS total_sales,
ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY total_sales DESC;
示例2：员工绩效分析（按部门和职位分组）
SELECT
department,
job_title,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MAX(performance_score) AS max_score
FROM employees
WHERE hire_date > '2020-01-01' -- 只统计2020年后入职的员工
GROUP BY department, job_title
HAVING employee_count >= 3; -- 只显示员工数≥3的组
分组查询结果示例（员工部门分析）
| department | job_title | employee_count | avg_salary | max_score |
|---|---|---|---|---|
| IT | Developer | 5 | 8500.00 | 95 |
| Sales | Manager | 3 | 9200.00 | 88 |
| HR | Specialist | 4 | 6500.00 | 90 |
3. 分组后筛选（HAVING）高级应用
WHERE vs HAVING 区别
| 特性 | WHERE | HAVING |
|---|---|---|
| 执行时机 | 在分组前过滤数据 | 在分组后过滤结果 |
| 可使用字段 | 所有表字段 | 只能是分组字段或聚合函数结果 |
| 性能影响 | 先过滤可以减少分组处理的数据量 | 对已分组数据进行二次过滤 |
| 聚合函数 | 不能使用聚合函数 | 必须使用聚合函数或分组字段 |
复杂业务场景示例
场景1：找出平均订单金额超过1000的客户
SELECT
customer_id,
COUNT(*) AS order_count,
AVG(amount) AS avg_amount
FROM orders
GROUP BY customer_id
HAVING AVG(amount) > 1000
ORDER BY avg_amount DESC;
场景2：筛选出销售额前10%的产品类别
SELECT
category,
SUM(quantity*price) AS category_sales
FROM products
JOIN order_details ON products.id = order_details.product_id
GROUP BY category
HAVING SUM(quantity*price) > (
SELECT 0.1 * SUM(quantity*price)
FROM order_details
)
ORDER BY category_sales DESC;
场景3：找出月活跃用户数超过10000且留存率大于30%的渠道
SELECT
channel,
COUNT(DISTINCT user_id) AS mau,
ROUND(COUNT(DISTINCT CASE WHEN last_active >= DATE_SUB(NOW(), INTERVAL 30 DAY)
THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS retention_rate
FROM user_activity
GROUP BY channel
HAVING mau > 10000 AND retention_rate > 30
ORDER BY retention_rate DESC;
4. 高级分组技巧
1. 多级分组分析
-- 按年和月分组统计销售额
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
SUM(amount) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;
2. 使用GROUPING SETS进行多维分析
-- 同时按多个维度分组统计
SELECT
COALESCE(category, 'All') AS category,
COALESCE(region, 'All') AS region,
SUM(sales) AS total_sales
FROM sales_data
GROUP BY GROUPING SETS (
(category, region),
(category),
(region),
()
);
3. ROLLUP生成小计和总计
-- 生成包含小计和总计的报告
SELECT
IFNULL(department, 'All Departments') AS department,
IFNULL(job_title, 'All Positions') AS job_title,
COUNT(*) AS employee_count,
SUM(salary) AS department_salary
FROM employees
GROUP BY department, job_title WITH ROLLUP;
六、关联查询（多表查询）
1. 准备关联表
1.1 用户表(user_info)结构设计
用户表是存储用户基本信息的核心表，包含以下字段：
CREATE TABLE IF NOT EXISTS user_info (
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户ID，主键自增
name VARCHAR(50) NOT NULL, -- 用户姓名，非空
phone CHAR(11) UNIQUE NOT NULL, -- 手机号，唯一且非空
age INT DEFAULT 0, -- 年龄，默认0
sex VARCHAR(10) DEFAULT '未知', -- 性别，默认'未知'
register_date DATE NOT NULL -- 注册日期，非空
);
1.2 订单表(order_info)结构设计
订单表与用户表通过外键关联，记录用户的订单信息：
CREATE TABLE IF NOT EXISTS order_info (
order_id INT PRIMARY KEY AUTO_INCREMENT, -- 订单ID，主键自增
user_id INT NOT NULL, -- 关联用户表的id
order_name VARCHAR(100) NOT NULL, -- 订单名称
order_price DECIMAL(10,2) NOT NULL, -- 订单金额(10位整数，2位小数)
create_time DATETIME NOT NULL, -- 下单时间
-- 外键约束：确保user_id的值必须在user_info表的id中存在
FOREIGN KEY (user_id) REFERENCES user_info(id)
ON DELETE CASCADE -- 可选：用户删除时级联删除其订单
ON UPDATE CASCADE -- 可选：用户id更新时同步更新订单表
);
1.3 示例数据插入
为演示关联查询，我们先插入一些测试数据：
-- 用户表数据
INSERT INTO user_info (name, phone, age, sex, register_date) VALUES
('张三', '13800138001', 28, '男', '2023-05-10'),
('李四', '13800138002', 32, '女', '2023-06-15'),
('王五', '13800138003', 25, '男', '2023-07-20'),
('赵六', '13800138004', 40, '女', '2023-08-25');
-- 订单表数据
INSERT INTO order_info (user_id, order_name, order_price, create_time) VALUES
(1, 'iPhone 15', 5999.00, '2024-01-20 14:30:00'),
(1, 'AirPods Pro', 1799.00, '2024-02-05 10:15:00'),
(2, '华为Mate 60', 4999.00, '2024-02-28 09:45:00'),
(3, '小米手环8', 299.00, '2024-03-12 16:20:00');
2. 常用关联查询方式
2.1 内连接(INNER JOIN)
内连接只返回两个表中匹配的行，是最常用的连接方式。
应用场景：
- 查询有订单的用户及其订单信息
- 统计已下单用户的消费情况
语法示例：
SELECT
u.id AS user_id,
u.name AS user_name,
o.order_id,
o.order_name,
o.order_price
FROM user_info u
INNER JOIN order_info o ON u.id = o.user_id;
执行结果：
| user_id | user_name | order_id | order_name | order_price |
|---|---|---|---|---|
| 1 | 张三 | 1 | iPhone 15 | 5999.00 |
| 1 | 张三 | 2 | AirPods Pro | 1799.00 |
| 2 | 李四 | 3 | 华为Mate 60 | 4999.00 |
| 3 | 王五 | 4 | 小米手环8 | 299.00 |
2.2 左连接(LEFT JOIN)
左连接返回左表所有记录，即使右表没有匹配记录。
应用场景：
- 查询所有用户及其订单信息(包括没有订单的用户)
- 分析用户下单率
语法示例：
SELECT
u.id AS user_id,
u.name AS user_name,
o.order_id,
o.order_name
FROM user_info u
LEFT JOIN order_info o ON u.id = o.user_id;
执行结果：
| user_id | user_name | order_id | order_name |
|---|---|---|---|
| 1 | 张三 | 1 | iPhone 15 |
| 1 | 张三 | 2 | AirPods Pro |
| 2 | 李四 | 3 | 华为Mate 60 |
| 3 | 王五 | 4 | 小米手环8 |
| 4 | 赵六 | NULL | NULL |
2.3 右连接(RIGHT JOIN)
右连接返回右表所有记录，即使左表没有匹配记录。
应用场景：
- 查询所有订单及其用户信息(包括用户已被删除的订单)
- 分析订单归属情况
语法示例：
SELECT
u.name AS user_name,
o.order_id,
o.order_name
FROM user_info u
RIGHT JOIN order_info o ON u.id = o.user_id;
执行结果：
| user_name | order_id | order_name |
|---|---|---|
| 张三 | 1 | iPhone 15 |
| 张三 | 2 | AirPods Pro |
| 李四 | 3 | 华为Mate 60 |
| 王五 | 4 | 小米手环8 |
2.4 其他实用技巧
2.4.1 使用WHERE过滤连接结果
可以结合WHERE子句对连接结果进行筛选：
-- 查询30岁以上用户的订单
SELECT u.name, o.order_name, o.order_price
FROM user_info u
INNER JOIN order_info o ON u.id = o.user_id
WHERE u.age > 30;
2.4.2 多表连接
可以同时连接多个表：
-- 假设有第三个表product_info
SELECT u.name, o.order_name, p.category
FROM user_info u
INNER JOIN order_info o ON u.id = o.user_id
INNER JOIN product_info p ON o.product_id = p.id;
2.4.3 使用聚合函数
结合GROUP BY进行统计分析：
-- 统计每个用户的订单总金额
SELECT
u.id,
u.name,
SUM(o.order_price) AS total_amount
FROM user_info u
LEFT JOIN order_info o ON u.id = o.user_id
GROUP BY u.id, u.name;
七、索引操作（提升查询效率）
1. 索引的作用与使用场景
优点详解
索引能够大幅提升SELECT查询速度，特别是对于以下情况：
- 数据量超过10万行的大表（如电商平台的用户表）
- 需要频繁条件查询的字段（如WHERE phone='13800138000'）
- 需要排序的字段（如ORDER BY create_time DESC）
- 需要分组统计的字段（如GROUP BY department_id）
缺点详解
索引会增加DML操作的开销，主要体现在：
- INSERT操作：需要额外时间更新索引结构（B+树）
- UPDATE操作：若修改了索引字段值，需同步更新索引
- DELETE操作：需要从索引结构中删除对应记录
- 存储空间：每个索引需要额外占用磁盘空间（约为原表大小的10-30%）
适用场景分析
- 高频查询字段：用户表的phone/email字段（登录验证场景）
- 时间范围查询：订单表的create_time字段（查询最近3个月订单）
- 多条件组合查询：商品表的(category_id, price)字段（品类价格筛选）
- 外键字段：订单表的user_id字段（关联用户信息）
2. 创建索引（CREATE INDEX）
语法详解
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段1 [ASC|DESC], 字段2,...)
[USING BTREE|HASH]
[COMMENT '索引说明']
索引类型详解
UNIQUE索引（唯一索引）
- 应用场景：用户手机号、身份证号等需要唯一性约束的字段
- 示例：
CREATE UNIQUE INDEX idx_user_idcard ON users(id_card)
单字段索引
- 应用场景：单个字段的等值查询或范围查询
- 示例：
CREATE INDEX idx_product_price ON products(price)
联合索引（复合索引）
- 最左前缀原则：查询条件必须包含联合索引的最左列
- 示例：索引(a,b,c)可优化：
- WHERE a=1 AND b=2
- WHERE a=1
- 但不能优化 WHERE b=2
创建示例扩展
1.创建带排序的索引：
CREATE INDEX idx_products_price_desc ON products(price DESC);
2.创建函数索引（MySQL 8.0+）：
CREATE INDEX idx_user_name_lower ON users((LOWER(username)));
3.创建前缀索引（适用于长字符串）：
CREATE INDEX idx_product_name ON products(name(20));
3. 查询索引（SHOW INDEX）
输出结果详解
执行SHOW INDEX FROM user_info会返回包含以下重要信息的表格：
- Table：索引所属表名
- Non_unique：是否唯一索引（0表示唯一）
- Key_name：索引名称
- Seq_in_index：字段在索引中的顺序
- Column_name：索引字段名
- Collation：排序方式（A升序，D降序）
- Cardinality：索引基数（估算值）
- Index_type：索引类型（BTREE/HASH）
实用技巧
1.查看索引使用情况：
SELECT * FROM sys.schema_index_statistics
WHERE table_schema='数据库名' AND table_name='表名';
2.查看未使用的索引：
SELECT * FROM sys.schema_unused_indexes;
4. 删除索引（DROP INDEX）
注意事项
- 删除索引是立即生效的DML操作
- 大表删除索引可能导致短暂锁表
- 建议在业务低峰期执行
- 删除前确认索引未被重要查询使用
高级操作
1.批量删除索引：
-- 生成删除语句
SELECT CONCAT('DROP INDEX ', index_name, ' ON ', table_name, ';')
FROM information_schema.statistics
WHERE table_schema = '数据库名' AND table_name = '表名';
-- 执行生成的语句
2.重建索引（先删后建）：
-- 优化索引碎片
DROP INDEX idx_name ON table_name;
CREATE INDEX idx_name ON table_name(column_name);
3.在线修改索引（MySQL 8.0+）：
ALTER TABLE table_name
DROP INDEX old_index_name,
ADD INDEX new_index_name(column_name) ALGORITHM=INPLACE, LOCK=NONE;
八、视图操作（简化复杂查询）
1. 创建视图（CREATE VIEW）
视图是基于SQL查询结果集的虚拟表，它不存储实际数据，而是保存查询定义。创建视图的语法如下：
CREATE VIEW 视图名 AS SELECT 语句;
详细示例：创建"用户订单视图"
以下示例创建一个名为view_user_order的视图，该视图关联用户表和订单表，展示用户姓名、订单名称和订单金额：
CREATE VIEW view_user_order AS
SELECT
u.name AS user_name, -- 用户姓名，使用别名user_name
o.order_name, -- 订单名称
o.order_price -- 订单金额
FROM
user_info u -- 用户信息表，别名为u
INNER JOIN
order_info o -- 订单信息表，别名为o
ON
u.id = o.user_id; -- 关联条件：用户ID等于订单中的用户ID
应用场景：当需要频繁查询用户订单信息时，可以创建此视图，简化复杂查询操作。
2. 查询视图
视图创建后可以像普通表一样查询：
-- 查询视图全部数据
SELECT * FROM view_user_order;
-- 带条件查询视图
SELECT user_name, order_name
FROM view_user_order
WHERE order_price > 1000;
注意事项：视图查询会实时执行底层SELECT语句，每次查询视图都会重新计算结果。
3. 修改视图（ALTER VIEW）
当需要更新视图定义时，可以使用ALTER VIEW语句：
ALTER VIEW 视图名 AS 新的SELECT语句;
详细示例：修改用户订单视图
以下示例向view_user_order视图添加订单创建时间字段：
ALTER VIEW view_user_order AS
SELECT
u.name AS user_name,
o.order_name,
o.order_price,
o.create_time -- 新增的订单创建时间字段
FROM
user_info u
INNER JOIN
order_info o
ON
u.id = o.user_id;
修改影响：视图修改后，所有基于该视图的查询将立即使用新的定义，但不会影响已存储的数据。
4. 删除视图（DROP VIEW）
当不再需要某个视图时，可以将其删除：
DROP VIEW [IF EXISTS] 视图名;
详细示例：删除用户订单视图
-- 安全删除方式，如果视图存在则删除
DROP VIEW IF EXISTS view_user_order;
-- 直接删除方式，如果视图不存在会报错
DROP VIEW view_user_order;
注意事项：
- 删除视图不会影响基表中的数据
- 使用IF EXISTS可避免因视图不存在而导致的错误
- 删除视图后，依赖该视图的其他对象可能会失效
九、常见问题与注意事项
SQL 语句大小写规范
MySQL 关键字（如 SELECT、CREATE、WHERE）在语法上不区分大小写，但为了代码的可读性和一致性，建议采用以下规范：
所有 SQL 关键字使用大写字母
- 示例：
SELECT * FROM users WHERE id = 1; - 例外：函数名（如 count(), sum()）通常使用小写
- 示例：
表名和字段名的大小写敏感性：
- 在 Windows 系统上不区分大小写
- 在 Linux/Unix 系统上区分大小写
- 建议统一采用小写字母，并使用下划线连接单词
- 推荐：
user_profile,order_details - 不推荐：
UserProfile,OrderDetails
- 推荐：
字段名与关键字冲突处理
MySQL 中有一些保留关键字（如 order、user、group 等），当这些词被用作表名或字段名时，需要使用反引号(`)包裹：
常见需要引用的关键字：
order（排序）user（用户）group（分组）desc（降序）
正确用法示例：
SELECT * FROM `order` WHERE `user` = 1; INSERT INTO `group` (name, `desc`) VALUES ('Admin', 'Administrator group');最佳实践：尽量避免使用关键字作为标识符，可考虑以下替代方案：
order→ordersuser→usersgroup→user_groups
数据备份与恢复操作指南
备份操作
完整数据库备份：
mysqldump -u username -p database_name > backup_file.sql- 执行后会提示输入密码
- 备份文件包含数据库结构和数据
备份特定表：
mysqldump -u username -p database_name table1 table2 > partial_backup.sql高级备份选项：
- 添加
--single-transaction参数（InnoDB 表，不锁表） - 添加
--routines参数（包含存储过程和函数） - 添加
--events参数（包含事件）
- 添加
恢复操作
完整数据库恢复：
mysql -u username -p database_name < backup_file.sql恢复前注意事项：
- 确保目标数据库已存在
- 大型数据库恢复可添加
--max_allowed_packet参数 - 恢复期间避免中断操作
查询优化建议
避免使用 SELECT *
问题分析：
- 返回不必要的数据会增加网络传输负担
- 应用程序可能无法处理后续新增的字段
- 查询性能可能受到影响（特别是包含 BLOB/TEXT 字段时）
优化方案：
-- 不推荐 SELECT * FROM products; -- 推荐 SELECT id, name, price FROM products;例外情况：
- 调试查询时可临时使用
- 确实需要所有字段的极少数场景
事务管理与提交
事务基础操作
显式事务控制：
START TRANSACTION; INSERT INTO orders (user_id, amount) VALUES (1, 100); UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; COMMIT; -- 或 ROLLBACK;自动提交设置：
-- 查看当前设置 SELECT @@autocommit; -- 关闭自动提交（需手动COMMIT） SET autocommit = 0; -- 开启自动提交（每条语句自动提交） SET autocommit = 1;
常见问题排查
数据未生效的可能原因：
- 忘记执行 COMMIT
- 事务被回滚（ROLLBACK）
- 连接断开导致事务自动回滚
- 其他会话持有锁导致阻塞
事务最佳实践：
- 保持事务简短
- 避免在事务中进行耗时操作
- 考虑使用适当的隔离级别
- 处理异常时确保正确回滚