MySQL SQL基础练习100题：从入门到实战-三亩地-编程学习日记

1. MySQL SQL基础练习的价值与定位

作为关系型数据库的标杆产品，MySQL在全球开发者中保持着压倒性的使用率。根据2023年Stack Overflow开发者调查报告，MySQL在专业开发者中的使用率达到45.6%，远超第二名PostgreSQL的26.5%。这种广泛的应用场景使得MySQL技能成为后端开发、数据分析等岗位的必备能力。

我整理这100道基础练习题的初衷，源于多年面试官经历中观察到的现象：约70%的初级候选人在基础SQL操作上存在明显短板。常见问题包括：

对JOIN操作的类型和区别理解模糊
聚合函数与GROUP BY的配合使用不熟练
子查询和临时表的应用场景混淆
事务隔离级别的实际影响认知不足

这套练习题特别适合以下人群：

准备校招/实习的技术类专业学生
计划转行数据分析的传统行业从业者
需要巩固数据库基础的初级开发人员
准备MySQL相关认证考试的备考者

2. 练习环境搭建指南

2.1 MySQL安装配置

推荐使用MySQL 8.0社区版作为练习环境，其安装过程在不同平台有所差异：

Windows平台：

从官网下载MySQL Installer
选择"Developer Default"安装类型
设置root密码时建议启用"Strong Password Encryption"
配置Windows服务时勾选"Start the MySQL Server at System Startup"

macOS平台：

brew install mysql brew services start mysql mysql_secure_installation

Linux(Ubuntu)平台：

sudo apt update sudo apt install mysql-server sudo mysql_secure_installation

重要提示：练习时建议创建专用测试数据库，避免误操作生产数据：
CREATE DATABASE practice_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2.2 图形化工具选型

对于初学者，推荐以下工具辅助练习：

工具名称	适用场景	特色功能
MySQL Workbench	综合管理	可视化ER图、SQL调试
DBeaver	多数据库支持	跨平台、数据导出
TablePlus	简洁界面	原生体验、快速导航
HeidiSQL	Windows专属	轻量级、查询构建器

3. 基础查询专项训练

3.1 SELECT语句核心要素

基础语法结构：

SELECT [DISTINCT] 列名 FROM 表名 [WHERE 条件] [GROUP BY 分组列] [HAVING 分组条件] [ORDER BY 排序列 [ASC|DESC]] [LIMIT 行数];

典型练习题示例：

查询员工表中薪资大于10000的员工姓名和部门ID

SELECT employee_name, department_id FROM employees WHERE salary > 10000;

统计各部门员工数量，按人数降序排列

SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id ORDER BY emp_count DESC;

3.2 多表连接实战

连接类型对比表：

连接类型	关键字	结果特征	性能影响
内连接	INNER JOIN	只返回匹配行	最优
左外连接	LEFT JOIN	左表全保留	中等
右外连接	RIGHT JOIN	右表全保留	中等
全外连接	FULL JOIN	两表全保留	最差
交叉连接	CROSS JOIN	笛卡尔积	慎用

连接练习案例：查询每个部门的名称及其经理姓名：

SELECT d.department_name, e.employee_name as manager_name FROM departments d LEFT JOIN employees e ON d.manager_id = e.employee_id;

4. 数据操作进阶训练

4.1 事务处理机制

ACID特性实现：

START TRANSACTION; -- 操作1：转账出账 UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A001'; -- 操作2：转账入账 UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B002'; -- 根据业务逻辑决定提交或回滚 COMMIT; -- 或 ROLLBACK;

隔离级别对比：

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	可能	可能	可能	最高
READ COMMITTED	避免	可能	可能	高
REPEATABLE READ	避免	避免	可能	中
SERIALIZABLE	避免	避免	避免	最低

4.2 存储过程开发

创建带参数的存储过程示例：

DELIMITER // CREATE PROCEDURE update_salary( IN emp_id INT, IN increase_percent DECIMAL(5,2), OUT new_salary DECIMAL(10,2) ) BEGIN DECLARE current_sal DECIMAL(10,2); SELECT salary INTO current_sal FROM employees WHERE employee_id = emp_id; SET new_salary = current_sal * (1 + increase_percent/100); UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; END // DELIMITER ; -- 调用示例 CALL update_salary(101, 10.5, @result); SELECT @result;

5. 性能优化关键策略

5.1 索引设计原则

索引创建最佳实践：

-- 单列索引 CREATE INDEX idx_employee_name ON employees(employee_name); -- 复合索引（注意列顺序） CREATE INDEX idx_dept_salary ON employees(department_id, salary); -- 覆盖索引优化 CREATE INDEX idx_covering ON orders(customer_id, order_date, total_amount);

索引失效的常见场景：

使用!=或<>操作符
对索引列使用函数操作（如UPPER(name)）
隐式类型转换（如字符串列与数字比较）
使用OR条件连接不同索引列
模糊查询以通配符开头（如LIKE '%abc'）

5.2 执行计划解析

EXPLAIN输出关键字段：

字段	说明	优化关注点
type	访问类型	至少达到range级别
key	实际使用的索引	检查是否使用预期索引
rows	预估扫描行数	数值过大需优化
Extra	附加信息	避免出现"Using filesort"

优化案例：

-- 优化前（全表扫描） EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 优化后（索引范围扫描） EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

6. 安全防护实践

6.1 SQL注入防御

不安全写法：

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

参数化查询示例（PHP）：

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?"); $stmt->bind_param("ss", $username, $password); $stmt->execute();

MySQL防御措施：

使用PREPARE语句
设置最小权限原则
启用sql_mode=STRICT_ALL_TABLES
对输入进行白名单验证

6.2 数据备份策略

mysqldump常用命令：

# 完整备份 mysqldump -u root -p --databases practice_db > backup.sql # 增量备份（需启用binlog） mysqlbinlog /var/lib/mysql/mysql-bin.000123 > incremental.sql # 恢复流程 mysql -u root -p practice_db < backup.sql mysql -u root -p < incremental.sql

7. 全套练习题分类清单

7.1 基础查询（20题）

单表条件查询
使用BETWEEN筛选范围
IN操作符应用
NULL值处理
列别名使用

7.2 聚合函数（15题）

COUNT统计应用
多列GROUP BY
HAVING筛选分组
聚合结果排序
嵌套聚合查询

7.3 多表操作（25题）

两表内连接
三表级联查询
自连接场景
外连接差异实践
使用USING简化连接

7.4 子查询（20题）

WHERE子句子查询
FROM子句派生表
EXISTS/NOT EXISTS应用
相关子查询优化
WITH子句(CTE)使用

7.5 数据修改（10题）

批量UPDATE模式
基于查询的INSERT
DELETE联表操作
事务回滚场景
乐观锁实现

7.6 高级特性（10题）

窗口函数应用
JSON数据处理
全文索引搜索
地理空间查询
生成列使用

练习建议：每天完成10-15题，对错题建立笔记记录，重点理解执行计划分析。实际工作中，约80%的日常SQL操作都涵盖在这些基础题型中。

日记详情

MySQL SQL基础练习100题：从入门到实战