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 StartupmacOS平台brew install mysql brew services start mysql mysql_secure_installationLinux(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简洁界面原生体验、快速导航HeidiSQLWindows专属轻量级、查询构建器3. 基础查询专项训练3.1 SELECT语句核心要素基础语法结构SELECT [DISTINCT] 列名 FROM 表名 [WHERE 条件] [GROUP BY 分组列] [HAVING 分组条件] [ORDER BY 排序列 [ASC|DESC]] [LIMIT 行数];典型练习题示例查询员工表中薪资大于10000的员工姓名和部门IDSELECT 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 %abc5.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_modeSTRICT_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.sql7. 全套练习题分类清单7.1 基础查询20题单表条件查询使用BETWEEN筛选范围IN操作符应用NULL值处理列别名使用7.2 聚合函数15题COUNT统计应用多列GROUP BYHAVING筛选分组聚合结果排序嵌套聚合查询7.3 多表操作25题两表内连接三表级联查询自连接场景外连接差异实践使用USING简化连接7.4 子查询20题WHERE子句子查询FROM子句派生表EXISTS/NOT EXISTS应用相关子查询优化WITH子句(CTE)使用7.5 数据修改10题批量UPDATE模式基于查询的INSERTDELETE联表操作事务回滚场景乐观锁实现7.6 高级特性10题窗口函数应用JSON数据处理全文索引搜索地理空间查询生成列使用练习建议每天完成10-15题对错题建立笔记记录重点理解执行计划分析。实际工作中约80%的日常SQL操作都涵盖在这些基础题型中。