学习目标熟悉常用 DML 操作（INSERT、UPDATE、DELETE）掌握事务与回滚机制学会批量插入、导入导出理解查询优化与索引使用掌握分组统计与聚合分析一、基本数据操作（DML）USE company;-- 插入多条记录INSERT INTO employee(name, dept, salary, hire_date)VALUES('Carol', 'dev', 11000, '2022-09-01'),('David', 'hr', 7000, '2023-03-01');-- 更新UPDATE employee SET salary = 12000 WHERE name='Carol';-- 删除DELETE FROM employee WHERE name='David';二、事务与回滚START TRANSACTION;UPDATE employee SET salary = salary + 500 WHERE dept='dev';-- 撤销更改ROLLBACK;-- 或提交永久更改COMMIT;扩展知识START TRANSACTION：开启事务ROLLBACK：撤销当前事务内所有未提交的更改COMMIT：提交事务，使更改永久生效查看当前事务状态：SHOW ENGINE INNODB STATUS\G实验在事务内更新多条记录后，不提交直接查询，观察结果。执行 ROLLBACK 后再查，验证数据回滚。三、批量数据生成与导入导出1. 批量插入存储过程DELIMITER $$DROP PROCEDURE IF EXISTS populate_employee$$CREATE PROCEDURE populate_employee(IN num INT)BEGIN DECLARE i INT DEFAULT 1; WHILE i <= num DO INSERT INTO employee(name, dept, salary, hire_date) VALUES (CONCAT('User', i), 'dev', FLOOR(RAND()*10000+5000), CURDATE()); SET i = i + 1; END WHILE;END$$DELIMITER ;-- 执行生成 5000 条数据CALL populate_employee(5000);-- 验证数据量SELECT COUNT(*) FROM employee;2. 导出与导入命令# 导出数据库mysqldump -uroot -p company > /tmp/company.sql# 清空表mysql -uroot -p -e "TRUNCATE TABLE company.employee;"# 导入数据mysql -uroot -p company < /tmp/company.sql### 也可以提前了解下Percona XtraBackup工具集实验修改存储过程，生成 10 万条数据并测试性能。使用 SHOW PROCESSLIST; 查看执行进度。四、查询优化与索引-- 查询部门为 dev 的员工SELECT * FROM employee WHERE dept='dev' ORDER BY salary DESC LIMIT 5;-- 查看执行计划EXPLAIN SELECT * FROM employee WHERE dept='dev'\G-- 创建复合索引优化查询CREATE INDEX idx_dept_salary ON employee(dept, salary);-- 再次查看执行计划EXPLAIN SELECT * FROM employee WHERE dept='dev'\G扩展知识type=ALL：全表扫描，性能最差type=ref：基于索引的访问方式索引能显著减少扫描行数，提高查询速度对高频过滤条件与排序字段建立复合索引实验SHOW PROFILE FOR QUERY n;

对比索引创建前后的查询耗时。

五、聚合与分组统计-- 部门薪资统计SELECT dept, COUNT(*) AS emp_count, AVG(salary) AS avg_salaryFROM employeeGROUP BY deptHAVING AVG(salary) > 8000ORDER BY avg_salary DESC;-- 汇总姓名SELECT dept, GROUP_CONCAT(name) FROM employee GROUP BY dept;-- 入职年份分布SELECT YEAR(hire_date) AS year, COUNT(*) FROM employee GROUP BY year;扩展知识GROUP BY：按字段分组HAVING：对聚合结果进行条件过滤GROUP_CONCAT()：将多行字符串合并为一行实验比较 WHERE 与 HAVING 的作用区别统计各部门平均薪资，筛选高薪团队