MySQL 基础语句大全-CSDN博客

一、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 → orders
- user → users
- group → 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）
- 连接断开导致事务自动回滚
- 其他会话持有锁导致阻塞
事务最佳实践：
- 保持事务简短
- 避免在事务中进行耗时操作
- 考虑使用适当的隔离级别
- 处理异常时确保正确回滚