1. MySQL基础操作与SQL分类解析作为一名数据库开发工程师我经常需要处理各种SQL查询和数据库管理任务。MySQL作为最流行的开源关系型数据库之一掌握其基础操作是每个开发者的必备技能。让我们从最基础的连接和SQL分类开始。1.1 客户端连接与基本操作连接MySQL服务器是我们操作数据库的第一步。标准的连接命令如下mysql -uroot -h172.17.0.1 -P3306 -p123456这个命令中-u指定用户名这里是root-h指定主机地址这里是172.17.0.1-P指定端口号默认是3306-p后面跟着密码这里是123456注意在实际生产环境中直接在命令行输入密码存在安全风险。更安全的做法是只输入-p参数而不带密码系统会提示你单独输入密码。连接成功后我们可以执行一些基本操作-- 查看所有数据库 SHOW DATABASES; -- 查看当前使用的数据库 SELECT DATABASE(); -- 切换数据库 USE database_name;1.2 SQL语言分类详解SQL语言按照功能可以分为四大类每类都有其特定的用途和语法DDL数据定义语言用于定义和管理数据库对象如表、索引、视图等的结构。主要命令包括CREATE创建数据库或表ALTER修改表结构DROP删除数据库或表TRUNCATE清空表数据但保留表结构示例-- 创建数据库 CREATE DATABASE IF NOT EXISTS newdb DEFAULT CHARSET utf8mb4; -- 创建表 CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 编号, emp_id VARCHAR(10) COMMENT 员工工号, name VARCHAR(10) COMMENT 员工姓名 ) COMMENT 员工表;DML数据操作语言用于操作表中的数据。主要命令包括INSERT插入新记录UPDATE更新现有记录DELETE删除记录示例-- 插入数据 INSERT INTO employee(id, emp_id, name) VALUES (1,00001,张三丰); -- 更新数据 UPDATE employee SET name 小昭 WHERE id 1; -- 删除数据 DELETE FROM employee WHERE id 1;DQL数据查询语言用于查询数据库中的数据。主要命令是SELECT及其各种变体。DCL数据控制语言用于控制数据库访问权限。主要命令包括GRANT授予权限REVOKE撤销权限CREATE USER创建用户ALTER USER修改用户2. 数据库与表操作实战2.1 数据库的创建与管理在实际项目中我们通常需要创建多个数据库来隔离不同业务的数据。创建数据库时需要考虑字符集的选择-- 创建默认字符集的数据库 CREATE DATABASE IF NOT EXISTS itcast; -- 创建指定字符集的数据库推荐使用utf8mb4以支持完整的Unicode字符 CREATE DATABASE IF NOT EXISTS itheima DEFAULT CHARSET utf8mb4;经验分享MySQL 5.7.7及以上版本中utf8mb4是推荐的字符集因为它支持完整的Unicode字符包括emoji而传统的utf8只支持基本的多语言平面字符。删除数据库需要谨慎操作-- 删除数据库 DROP DATABASE itheima;2.2 表结构的创建与修改设计良好的表结构是数据库性能的基础。下面是一个完整的员工表创建示例CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 编号, emp_id VARCHAR(10) UNIQUE COMMENT 员工工号, name VARCHAR(10) NOT NULL COMMENT 员工姓名, gender CHAR(1) CHECK (gender IN (男,女)) COMMENT 员工性别, age TINYINT UNSIGNED COMMENT 员工年龄, card_id CHAR(18) COMMENT 身份证号码, entrydate DATE COMMENT 入职时间 ) COMMENT 员工表 ENGINEInnoDB DEFAULT CHARSETutf8mb4;表创建后可能需要修改结构。常见的ALTER TABLE操作包括-- 添加新字段 ALTER TABLE employee ADD nickname VARCHAR(20) COMMENT 昵称; -- 修改字段名和类型 ALTER TABLE employee CHANGE nickname username VARCHAR(30); -- 删除字段 ALTER TABLE employee DROP username; -- 重命名表 ALTER TABLE employee RENAME TO staff;注意事项在生产环境中修改大表结构可能会导致锁表影响业务。建议在低峰期操作或使用pt-online-schema-change等工具进行在线DDL操作。3. 数据操作与查询技巧3.1 数据的增删改操作插入数据时我们可以一次性插入多条记录以提高效率INSERT INTO employee(id, emp_id, name, gender, age, card_id, entrydate) VALUES (1,00001,张三丰,男,63,12345678987456321X,1987-10-10), (2,00002,张翠山,男,48,123256789874563214,2011-1-19);更新数据时WHERE条件非常重要否则会更新整张表-- 更新特定记录 UPDATE employee SET name itheima WHERE id 1; -- 更新多个字段 UPDATE employee SET name 小昭, gender 女 WHERE id 1; -- 批量更新谨慎使用 UPDATE employee SET entrydate 2008-01-01;删除数据也有多种方式-- 条件删除 DELETE FROM employee WHERE gender 女; -- 清空表可回滚 DELETE FROM employee; -- 清空表不可回滚重置自增ID TRUNCATE TABLE employee;3.2 基础查询与条件筛选SELECT语句是SQL中最常用的命令基本语法如下-- 查询特定字段 SELECT name, workaddress, age FROM employee; -- 查询所有字段生产环境慎用 SELECT * FROM employee; -- 使用别名 SELECT workaddress AS 工作地址 FROM employee; -- 去重查询 SELECT DISTINCT workaddress FROM employee;条件查询是SQL的核心功能之一-- 等值查询 SELECT * FROM employee WHERE age 30; -- 范围查询 SELECT * FROM employee WHERE age BETWEEN 15 AND 20; -- 空值检查 SELECT * FROM employee WHERE card_id IS NULL; -- 多条件组合 SELECT * FROM employee WHERE gender 女 AND age 25; -- IN操作符 SELECT * FROM employee WHERE age IN (18,20,40);3.3 模糊查询与通配符使用模糊查询在业务中非常常见主要使用LIKE操作符-- 查询姓名为两个字的员工 SELECT * FROM employee WHERE name LIKE __; -- 查询身份证号最后一位是X的员工 SELECT * FROM employee WHERE card_id LIKE %X; -- 查询姓张的员工 SELECT * FROM employee WHERE name LIKE 张%;技巧%匹配任意多个字符_匹配单个字符。模糊查询通常会导致全表扫描在大表上性能较差应谨慎使用。4. 高级查询与数据统计4.1 聚合函数与分组统计MySQL提供了多种聚合函数用于数据统计-- 计数 SELECT COUNT(*) FROM employee; -- 平均值 SELECT AVG(age) FROM employee; -- 最大值/最小值 SELECT MAX(age), MIN(age) FROM employee; -- 求和 SELECT SUM(age) FROM employee WHERE workaddress 西安;GROUP BY子句可以将数据分组后统计-- 按性别统计员工数量 SELECT gender, COUNT(*) FROM employee GROUP BY gender; -- 按性别统计平均年龄 SELECT gender, AVG(age) FROM employee GROUP BY gender; -- 多字段分组 SELECT workaddress, gender, COUNT(*) FROM employee GROUP BY workaddress, gender;HAVING子句用于对分组结果进行筛选-- 查询员工数量大于等于3的工作地址 SELECT workaddress FROM employee WHERE age 45 GROUP BY workaddress HAVING COUNT(workaddress) 3;4.2 排序与分页查询ORDER BY用于对结果排序-- 单字段排序 SELECT * FROM employee ORDER BY age; -- 多字段排序 SELECT * FROM employee ORDER BY age ASC, entrydate DESC;LIMIT用于分页查询是Web应用的常见需求-- 第一页每页10条 SELECT * FROM employee LIMIT 0, 10; -- 第二页 SELECT * FROM employee LIMIT 10, 10; -- 通用分页公式(页码-1)*每页记录数 SELECT * FROM employee LIMIT (page-1)*size, size;4.3 复杂条件查询示例结合前面学到的知识我们可以构建更复杂的查询-- 查询性别为男年龄20-40姓名为三个字的员工 SELECT * FROM employee WHERE gender 男 AND age BETWEEN 20 AND 40 AND name LIKE ___; -- 查询年龄35的员工按年龄升序、入职时间降序排序 SELECT name, age FROM employee WHERE age 35 ORDER BY age ASC, entrydate DESC; -- 查询性别为男年龄20-40的前5个员工按年龄和入职时间排序 SELECT * FROM employee WHERE gender 男 AND age BETWEEN 20 AND 40 ORDER BY age, entrydate LIMIT 5;5. 用户权限管理与高级功能5.1 用户创建与权限控制MySQL的权限系统非常精细可以控制用户对数据库的各种操作-- 创建本地用户 CREATE USER itcastlocalhost IDENTIFIED BY 123456; -- 创建可从任意主机访问的用户 CREATE USER heima% IDENTIFIED BY 123456; -- 修改密码 ALTER USER heima% IDENTIFIED BY 654321; -- 删除用户 DROP USER itcastlocalhost;权限授予与撤销-- 授予所有权限 GRANT ALL ON itcast.* TO heima%; -- 撤销权限 REVOKE ALL ON itcast.* FROM heima%; -- 查看权限 SHOW GRANTS FOR heima%;安全建议生产环境中应遵循最小权限原则只授予用户必要的权限。避免使用具有全局权限的账户进行日常操作。5.2 字符串与日期函数MySQL提供了丰富的字符串处理函数-- 大小写转换 SELECT LOWER(card_id), UPPER(name) FROM employee; -- 字符串拼接 SELECT CONCAT(name, (, age, )) FROM employee; -- 填充字符串 SELECT LPAD(emp_id, 8, 0) FROM employee; -- 截取子串 SELECT SUBSTRING(card_id, 1, 6) FROM employee;日期函数也非常实用-- 当前日期时间 SELECT CURDATE(), CURTIME(), NOW(); -- 日期部分提取 SELECT YEAR(entrydate), MONTH(entrydate), DAY(entrydate) FROM employee; -- 日期加减 SELECT DATE_ADD(entrydate, INTERVAL 1 YEAR) FROM employee; -- 日期差 SELECT DATEDIFF(NOW(), entrydate) AS days FROM employee; -- 计算入职天数并排序 SELECT name, DATEDIFF(NOW(), entrydate) AS days FROM employee ORDER BY days DESC;5.3 条件判断与流程控制CASE WHEN语句可以实现复杂的条件逻辑-- 简单条件判断 SELECT name, IF(age 30, 资深, 新手) AS level FROM employee; -- 多条件判断 SELECT name, workaddress, CASE WHEN workaddress IN (北京,上海,广州,深圳) THEN 一线城市 ELSE 二线城市 END AS city_level FROM employee; -- 工资等级分类 SELECT name, salary, CASE WHEN salary 5000 THEN 普通员工 WHEN salary BETWEEN 5000 AND 9000 THEN 中层 ELSE 核心骨干 END AS salary_level FROM employee;6. 表连接与子查询实战6.1 内连接与外连接表连接是关系型数据库的核心特性允许我们从多个表中组合数据-- 隐式内连接 SELECT e.name, d.dept_name FROM employee e, department d WHERE e.dept_id d.id; -- 显式内连接推荐 SELECT e.name, d.dept_name FROM employee e INNER JOIN department d ON e.dept_id d.id; -- 左外连接保留左表所有记录 SELECT e.*, d.dept_name FROM employee e LEFT JOIN department d ON e.dept_id d.id; -- 右外连接保留右表所有记录 SELECT d.*, e.* FROM department d RIGHT JOIN employee e ON d.id e.dept_id;6.2 自连接查询自连接是指表与自身连接常用于查询层级关系-- 查询员工及其领导信息 SELECT e.name AS employee, m.name AS manager FROM employee e LEFT JOIN employee m ON e.manager_id m.id;6.3 子查询应用子查询可以嵌套在SELECT、FROM、WHERE等子句中-- WHERE子句中的子查询 -- 查询比平均薪资高的员工 SELECT * FROM employee WHERE salary (SELECT AVG(salary) FROM employee); -- 查询特定部门的员工 SELECT * FROM employee WHERE dept_id (SELECT id FROM department WHERE dept_name 研发部); -- IN子查询 SELECT * FROM employee WHERE dept_id IN (SELECT id FROM department WHERE dept_name IN (市场部,行政部)); -- FROM子句中的子查询 SELECT dept_id, avg_salary FROM ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) AS dept_stats;6.4 高级子查询技巧-- 相关子查询查询低于本部门平均工资的员工 SELECT e.* FROM employee e WHERE e.salary ( SELECT AVG(salary) FROM employee WHERE dept_id e.dept_id ); -- EXISTS子查询查询有下属的员工 SELECT m.* FROM employee m WHERE EXISTS ( SELECT 1 FROM employee e WHERE e.manager_id m.id ); -- 派生表连接查询部门及员工数量 SELECT d.*, e_count.emp_count FROM department d LEFT JOIN ( SELECT dept_id, COUNT(*) AS emp_count FROM employee GROUP BY dept_id ) e_count ON d.id e_count.dept_id;7. 性能优化与最佳实践7.1 索引使用建议合理的索引可以大幅提高查询性能-- 创建索引 CREATE INDEX idx_employee_name ON employee(name); CREATE INDEX idx_employee_dept ON employee(dept_id); -- 多列索引 CREATE INDEX idx_employee_dept_age ON employee(dept_id, age); -- 查看索引 SHOW INDEX FROM employee;经验法则为WHERE子句中的常用列创建索引为JOIN条件的列创建索引考虑为ORDER BY和GROUP BY的列创建索引避免过度索引因为索引会降低写入性能7.2 EXPLAIN分析查询使用EXPLAIN可以分析查询执行计划EXPLAIN SELECT * FROM employee WHERE name 张三丰;重点关注type最好能达到ref或range级别避免ALL全表扫描possible_keys可能使用的索引key实际使用的索引rows预估需要检查的行数7.3 查询优化技巧**避免SELECT ***只查询需要的列合理使用JOIN小表驱动大表LIMIT分页优化对于大表分页避免使用大偏移量-- 低效写法偏移量大时性能差 SELECT * FROM employee LIMIT 10000, 20; -- 优化写法使用索引列过滤 SELECT * FROM employee WHERE id 10000 LIMIT 20;批量操作使用批量INSERT代替单条插入避免在索引列上使用函数这会导致索引失效-- 索引失效的写法 SELECT * FROM employee WHERE YEAR(entrydate) 2020; -- 优化写法 SELECT * FROM employee WHERE entrydate BETWEEN 2020-01-01 AND 2020-12-31;7.4 事务与锁定MySQL默认使用自动提交模式但有时需要显式使用事务-- 开始事务 START TRANSACTION; -- 执行一系列操作 UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; -- 提交或回滚 COMMIT; -- 或 ROLLBACK;注意事项事务应尽量短小避免长时间持有锁合理设置隔离级别默认REPEATABLE READ注意死锁问题多个事务以相同顺序访问资源8. 实际业务场景解决方案8.1 员工管理系统查询示例-- 查询各部门人数及平均工资 SELECT d.dept_name, COUNT(e.id) AS employee_count, IFNULL(AVG(e.salary), 0) AS avg_salary FROM department d LEFT JOIN employee e ON d.id e.dept_id GROUP BY d.id ORDER BY employee_count DESC; -- 查询各部门工资最高的员工 SELECT d.dept_name, e.name, e.salary FROM department d JOIN employee e ON e.id ( SELECT id FROM employee WHERE dept_id d.id ORDER BY salary DESC LIMIT 1 ); -- 查询工资高于部门平均工资的员工 SELECT e.* FROM employee e WHERE e.salary ( SELECT AVG(salary) FROM employee WHERE dept_id e.dept_id );8.2 层级数据查询处理组织结构等层级数据时可以使用递归查询MySQL 8.0-- 使用CTE递归查询组织层级 WITH RECURSIVE org_tree AS ( -- 基础查询顶级部门 SELECT id, dept_name, parent_id, 1 AS level FROM department WHERE parent_id IS NULL UNION ALL -- 递归查询子部门 SELECT d.id, d.dept_name, d.parent_id, ot.level 1 FROM department d JOIN org_tree ot ON d.parent_id ot.id ) SELECT * FROM org_tree ORDER BY level, id;8.3 数据统计与报表-- 月度入职统计 SELECT YEAR(entrydate) AS year, MONTH(entrydate) AS month, COUNT(*) AS new_employees, AVG(age) AS avg_age FROM employee GROUP BY YEAR(entrydate), MONTH(entrydate) ORDER BY year, month; -- 员工年龄分布 SELECT CASE WHEN age 20 THEN 20岁以下 WHEN age BETWEEN 20 AND 29 THEN 20-29岁 WHEN age BETWEEN 30 AND 39 THEN 30-39岁 ELSE 40岁及以上 END AS age_group, COUNT(*) AS count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employee), 2) AS percentage FROM employee GROUP BY age_group ORDER BY MIN(age);9. 常见问题与解决方案9.1 性能问题排查慢查询日志分析-- 查看慢查询配置 SHOW VARIABLES LIKE slow_query%; -- 临时开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的查询锁等待问题-- 查看当前锁等待 SHOW ENGINE INNODB STATUS; -- 查看正在运行的进程 SHOW PROCESSLIST;9.2 数据一致性问题使用事务保证一致性START TRANSACTION; -- 扣除A账户金额 UPDATE accounts SET balance balance - 100 WHERE id 1; -- 增加B账户金额 UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT;外键约束-- 添加外键约束 ALTER TABLE employee ADD CONSTRAINT fk_employee_department FOREIGN KEY (dept_id) REFERENCES department(id) ON DELETE RESTRICT ON UPDATE CASCADE;9.3 备份与恢复使用mysqldump备份mysqldump -u root -p database_name backup.sql恢复备份mysql -u root -p database_name backup.sql二进制日志恢复-- 查看二进制日志状态 SHOW BINARY LOGS; -- 从特定位置恢复 mysqlbinlog --start-position4 --stop-position106 /var/log/mysql/mysql-bin.000001 | mysql -u root -p10. 进阶学习建议掌握了这100个SQL练习题后建议进一步学习以下内容MySQL高级特性存储过程和函数触发器视图事件调度器性能优化索引优化策略查询执行计划分析服务器参数调优高可用架构主从复制读写分离集群部署替代方案MySQL 8.0新特性其他关系型数据库PostgreSQL等NoSQL数据库MongoDB、Redis等在实际工作中我发现很多性能问题都源于不合理的SQL写法。建议定期审查慢查询日志对高频查询进行优化。同时随着数据量增长要考虑分库分表策略避免单表过大导致的性能瓶颈。