SQL练习题(持续更新中)

测试表创建

1. -- 1. 部门表（departments）
2. CREATE TABLE IF NOT EXISTS departments (
3.     dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID，主键自增',
4.     dept_name VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称，唯一不可重复',
5.     location VARCHAR(100) COMMENT '部门所在地',
6.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
7. ) COMMENT '公司部门信息表';
9. -- 2. 员工表（employees）
10. CREATE TABLE IF NOT EXISTS employees (
11.     id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键自增',
12.     name VARCHAR(50) NOT NULL COMMENT '员工姓名',
13.     gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '员工性别',
14.     department VARCHAR(50) COMMENT '所属部门（关联departments表的dept_name）',
15.     hire_date DATE NOT NULL COMMENT '入职日期',
16.     phone VARCHAR(20) UNIQUE COMMENT '手机号码，唯一',
17.     email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一',
18.     manager_id INT COMMENT '直属上级ID（自关联，关联本表的id）',
19.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
20.     FOREIGN KEY (department) REFERENCES departments(dept_name) ON UPDATE CASCADE,
21.     FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
22. ) COMMENT '公司员工信息表';
24. -- 3. 技能表（skills）
25. CREATE TABLE IF NOT EXISTS skills (
26.     skill_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '技能ID，主键自增',
27.     skill_name VARCHAR(50) NOT NULL UNIQUE COMMENT '技能名称，唯一',
28.     skill_type VARCHAR(30) COMMENT '技能类型（如：编程语言、工具等）',
29.     description VARCHAR(200) COMMENT '技能描述'
30. ) COMMENT '技能信息表';
32. -- 4. 员工-技能中间表（employee_skills）
33. CREATE TABLE IF NOT EXISTS employee_skills (
34.     id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键自增',
35.     employee_id INT NOT NULL COMMENT '员工ID，关联employees表',
36.     skill_id INT NOT NULL COMMENT '技能ID，关联skills表',
37.     proficiency INT CHECK (proficiency BETWEEN 1 AND 5) COMMENT '熟练程度（1-5，5为最高）',
38.     learned_date DATE COMMENT '掌握该技能的日期',
39.     UNIQUE KEY uk_employee_skill (employee_id, skill_id),
40.     FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
41.     FOREIGN KEY (skill_id) REFERENCES skills(skill_id) ON DELETE CASCADE
42. ) COMMENT '员工与技能的关联表（多对多）';
44. -- 5. 薪资记录表（salary_records）
45. CREATE TABLE IF NOT EXISTS salary_records (
46.     record_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键自增',
47.     employee_id INT NOT NULL COMMENT '员工ID，关联员工表',
48.     basic_salary DECIMAL(10, 2) NOT NULL CHECK (basic_salary >= 0) COMMENT '基本工资',
49.     bonus DECIMAL(10, 2) DEFAULT 0 CHECK (bonus >= 0) COMMENT '奖金',
50.     subsidy DECIMAL(10, 2) DEFAULT 0 CHECK (subsidy >= 0) COMMENT '补贴',
51.     total_salary DECIMAL(10, 2) GENERATED ALWAYS AS (basic_salary + bonus + subsidy) STORED COMMENT '总薪资（自动计算）',
52.     effective_date DATE NOT NULL COMMENT '生效日期',
53.     expire_date DATE COMMENT '失效日期（NULL表示当前生效）',
54.     reason VARCHAR(200) COMMENT '薪资调整原因',
55.     created_by VARCHAR(50) COMMENT '操作人',
56.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
57.     CONSTRAINT uk_employee_effective UNIQUE (employee_id, effective_date),
58.     FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
59. ) COMMENT '员工薪资变动记录表';

复制代码

插入数据

1. -- 先插入部门数据（因为员工表依赖部门表）
2. INSERT INTO departments (dept_name, location) VALUES
3. ('技术部', '北京'),
4. ('市场部', '上海'),
5. ('人事部', '广州'),
6. ('财务部', '深圳');
8. -- 插入员工数据
9. -- 插入部门数据（使用IGNORE避免重复）
10. INSERT IGNORE INTO departments (dept_name, location) VALUES
11. ('技术部', '北京海淀区'),
12. ('市场部', '上海浦东新区'),
13. ('人事部', '广州天河区'),
14. ('财务部', '深圳南山区'),
15. ('运营部', '杭州西湖区');
17. -- 插入员工数据（包含部门关联和上级关联）
18. INSERT IGNORE INTO employees (id, name, gender, department, hire_date, phone, email, manager_id) VALUES
19. (1, '张三', '男', '技术部', '2020-01-15', '13800138001', 'zhangsan@example.com', NULL),
20. (2, '李四', '女', '市场部', '2021-03-20', '13900139002', 'lisi@example.com', NULL),
21. (3, '王五', '男', '技术部', '2019-11-05', '13700137003', 'wangwu@example.com', 1),
22. (4, '赵六', '女', '人事部', '2022-05-10', '13600136004', 'zhaoliu@example.com', NULL),
23. (5, '钱七', '男', '技术部', '2021-09-30', '13500135005', 'qianqi@example.com', 1),
24. (6, '孙八', '女', '财务部', '2020-07-22', '13400134006', 'sunba@example.com', NULL),
25. (7, '周九', '男', '市场部', '2022-01-18', '13300133007', 'zhoujiu@example.com', 2),
26. (8, '吴十', '女', '运营部', '2021-06-05', '13200132008', 'wushi@example.com', NULL),
27. (9, '郑十一', '男', '财务部', '2023-02-10', '13100131009', 'zheng11@example.com', 6),
28. (10, '王十二', '女', '运营部', '2022-09-15', '13001300130', 'wang12@example.com', 8);
30. -- 插入技能数据
31. INSERT IGNORE INTO skills (skill_id, skill_name, skill_type, description) VALUES
32. (1, 'Java', '编程语言', '面向对象的编程语言'),
33. (2, 'Python', '编程语言', '简洁易学的脚本语言'),
34. (3, 'MySQL', '数据库', '关系型数据库管理系统'),
35. (4, 'JavaScript', '编程语言', '前端开发主要语言'),
36. (5, 'Excel', '办公软件', '数据处理与分析工具'),
37. (6, 'PPT', '办公软件', '演示文稿制作工具'),
38. (7, 'Vue', '前端框架', '渐进式JavaScript框架'),
39. (8, 'Spring Boot', '后端框架', 'Java开发框架'),
40. (9, '数据分析', '业务能力', '数据挖掘与分析能力'),
41. (10, '项目管理', '管理能力', '项目规划与执行能力');
43. -- 插入员工-技能关联数据
44. INSERT IGNORE INTO employee_skills (employee_id, skill_id, proficiency, learned_date) VALUES
45. (1, 1, 5, '2018-06-10'),  -- 张三：Java（熟练5）
46. (1, 3, 4, '2019-01-15'),  -- 张三：MySQL（熟练4）
47. (1, 8, 5, '2019-05-20'),  -- 张三：Spring Boot（熟练5）
48. (3, 1, 4, '2019-03-20'),  -- 王五：Java（熟练4）
49. (3, 2, 3, '2020-05-10'),  -- 王五：Python（熟练3）
50. (3, 3, 3, '2019-12-05'),  -- 王五：MySQL（熟练3）
51. (5, 1, 3, '2021-02-28'),  -- 钱七：Java（熟练3）
52. (5, 4, 2, '2022-01-15'),  -- 钱七：JavaScript（熟练2）
53. (5, 7, 2, '2022-03-10'),  -- 钱七：Vue（熟练2）
54. (2, 5, 4, '2020-11-05'),  -- 李四：Excel（熟练4）
55. (2, 6, 5, '2019-09-30'),  -- 李四：PPT（熟练5）
56. (2, 9, 4, '2021-01-20'),  -- 李四：数据分析（熟练4）
57. (4, 5, 5, '2021-07-20'),  -- 赵六：Excel（熟练5）
58. (4, 10, 3, '2022-08-15'), -- 赵六：项目管理（熟练3）
59. (6, 3, 4, '2019-05-15'),  -- 孙八：MySQL（熟练4）
60. (6, 5, 4, '2018-11-10'),  -- 孙八：Excel（熟练4）
61. (7, 6, 3, '2021-05-10'),  -- 周九：PPT（熟练3）
62. (7, 9, 2, '2022-03-20'),  -- 周九：数据分析（熟练2）
63. (8, 10, 4, '2020-08-05'), -- 吴十：项目管理（熟练4）
64. (10, 9, 3, '2022-11-10'); -- 王十二：数据分析（熟练3）
66. -- 插入薪资记录数据（包含初始薪资和调整记录）
67. INSERT IGNORE INTO salary_records (record_id, employee_id, basic_salary, bonus, subsidy, effective_date, expire_date, reason, created_by) VALUES
68. -- 张三的薪资记录
69. (1, 1, 7000, 500, 500, '2020-01-15', '2021-12-31', '入职初始薪资', 'admin'),
70. (2, 1, 8000, 800, 500, '2022-01-01', NULL, '年度调薪', 'admin'),
72. -- 李四的薪资记录
73. (3, 2, 6000, 300, 200, '2021-03-20', '2022-06-30', '入职初始薪资', 'admin'),
74. (4, 2, 6500, 400, 200, '2022-07-01', NULL, '半年度调薪', 'admin'),
76. -- 王五的薪资记录
77. (5, 3, 8500, 500, 200, '2019-11-05', '2021-05-31', '入职初始薪资', 'admin'),
78. (6, 3, 9200, 600, 400, '2021-06-01', NULL, '晋升调薪', 'admin'),
80. -- 赵六的薪资记录
81. (7, 4, 5500, 200, 100, '2022-05-10', NULL, '入职初始薪资', 'admin'),
83. -- 钱七的薪资记录
84. (8, 5, 7000, 300, 200, '2021-09-30', '2023-02-28', '入职初始薪资', 'admin'),
85. (9, 5, 7500, 400, 200, '2023-03-01', NULL, '年度调薪', 'admin'),
87. -- 孙八的薪资记录
88. (10, 6, 7200, 500, 300, '2020-07-22', '2022-12-31', '入职初始薪资', 'admin'),
89. (11, 6, 7800, 600, 300, '2023-01-01', NULL, '年度调薪', 'admin'),
91. -- 周九的薪资记录
92. (12, 7, 6200, 200, 100, '2022-01-18', '2023-06-30', '入职初始薪资', 'admin'),
93. (13, 7, 6800, 300, 100, '2023-07-01', NULL, '年度调薪', 'admin'),
95. -- 吴十的薪资记录
96. (14, 8, 6500, 400, 300, '2021-06-05', '2022-11-30', '入职初始薪资', 'admin'),
97. (15, 8, 7000, 500, 300, '2022-12-01', NULL, '年度调薪', 'admin'),
99. -- 郑十一的薪资记录
100. (16, 9, 5800, 200, 100, '2023-02-10', NULL, '入职初始薪资', 'admin'),
102. -- 王十二的薪资记录
103. (17, 10, 6000, 300, 200, '2022-09-15', '2023-08-31', '入职初始薪资', 'admin'),
104. (18, 10, 6300, 300, 200, '2023-09-01', NULL, '年度调薪', 'admin');   

复制代码

一、基础查询与条件筛选（单表操作）

• 题目：查询所有部门的名称和所在地，并按部门名称升序排序。
  1. SELECT dept_name, location
  2. FROM departments
  3. ORDER BY dept_name ASC;

  复制代码
• 题目：查询技术部入职时间在2021年及之后的员工，显示姓名、入职日期和手机号。
  1. SELECT name, hire_date, phone
  2. FROM employees
  3. WHERE department = '技术部'
  4.   AND hire_date >= '2021-01-01';

  复制代码
• 题目：查询薪资记录中总薪资（total_salary）在8000-10000之间的记录，显示员工ID、总薪资和生效日期。
  1. SELECT employee_id, total_salary, effective_date
  2. FROM salary_records
  3. WHERE total_salary BETWEEN 8000 AND 10000;

  复制代码

二、聚合函数与分组查询

• 题目：统计每个部门的员工人数，显示部门名称和人数，过滤出人数≥3的部门。
  1. SELECT department AS 部门名称,
  2.        COUNT(*) AS 员工人数
  3. FROM employees
  4. GROUP BY department
  5. HAVING COUNT(*) >= 3;

  复制代码
• 题目：计算各部门当前生效薪资的平均值（取expire_date IS NULL的记录），显示部门名称和平均薪资（保留2位小数）。
  1. SELECT e.department AS 部门名称,
  2.        ROUND(AVG(sr.total_salary), 2) AS 平均薪资
  3. FROM employees e
  4. JOIN salary_records sr ON e.id = sr.employee_id
  5. WHERE sr.expire_date IS NULL
  6. GROUP BY e.department;

  复制代码
• 题目：统计每种技能的掌握人数，按人数从多到少排序，包含无人掌握的技能（显示0）。
  1. SELECT
  2.   s.skill_name AS 技能名称,
  3.   COUNT(es.employee_id) AS 掌握人数
  4. FROM skills s
  5. LEFT JOIN employee_skills es ON s.skill_id = es.skill_id
  6. GROUP BY s.skill_name;

  复制代码

三、连接查询（多表关联）

• 题目：查询所有员工的姓名、所属部门名称及部门所在地，包括未分配部门的员工（若有）。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   d.dept_name AS 部门名称,
  4.   d.location AS 部门所在地
  5. FROM employees e
  6. LEFT JOIN departments d ON e.department = d.dept_name;

  复制代码
• 题目：查询掌握“Java”技能的员工姓名、部门及熟练程度，要求熟练程度≥4。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   e.department AS 部门,
  4.   es.proficiency AS 熟练程度
  5. FROM employees e
  6. JOIN employee_skills es ON e.id = es.employee_id
  7. JOIN skills s ON es.skill_id = s.skill_id
  8. WHERE s.skill_name = 'Java'
  9.   AND es.proficiency >= 4;

  复制代码
• 题目：查询2023年有薪资调整的员工姓名及调整前后的总薪资（需显示“调整前薪资”和“调整后薪资”）。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   prev.total_salary AS 调整前薪资,
  4.   curr.total_salary AS 调整后薪资,
  5.   curr.effective_date AS 调整日期
  6. FROM employees e
  7. JOIN salary_records curr ON e.id = curr.employee_id
  8. JOIN salary_records prev ON e.id = prev.employee_id
  9.   AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
  10. WHERE YEAR(curr.effective_date) = 2023;

  复制代码

四、子查询与嵌套查询

• 题目：查询薪资高于本部门平均薪资的员工，显示姓名、部门和当前总薪资。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   e.department AS 部门,
  4.   sr.total_salary AS 当前总薪资
  5. FROM employees e
  6. JOIN salary_records sr ON e.id = sr.employee_id
  7. WHERE sr.expire_date IS NULL
  8.   AND sr.total_salary > (
  9.     SELECT AVG(sr2.total_salary)
  10.     FROM employees e2
  11.     JOIN salary_records sr2 ON e2.id = sr2.employee_id
  12.     WHERE sr2.expire_date IS NULL
  13.       AND e2.department = e.department
  14.   );

  复制代码
• 题目：找出同时掌握“Java”和“MySQL”两种技能的员工姓名。
  1. SELECT e.name AS 员工姓名
  2. FROM employees e
  3. WHERE EXISTS (
  4.   SELECT 1
  5.   FROM employee_skills es
  6.   JOIN skills s ON es.skill_id = s.skill_id
  7.   WHERE es.employee_id = e.id AND s.skill_name = 'Java'
  8. )
  9. AND EXISTS (
  10.   SELECT 1
  11.   FROM employee_skills es
  12.   JOIN skills s ON es.skill_id = s.skill_id
  13.   WHERE es.employee_id = e.id AND s.skill_name = 'MySQL'
  14. );

  复制代码
• 题目：查询各部门中薪资最高的员工，显示部门名称、员工姓名和最高薪资。
  1. SELECT
  2.   dept_name AS 部门名称,
  3.   name AS 员工姓名,
  4.   max_salary AS 最高薪资
  5. FROM (
  6.   SELECT
  7.     e.department AS dept_name,
  8.     e.name,
  9.     sr.total_salary,
  10.     MAX(sr.total_salary) OVER (PARTITION BY e.department) AS max_salary
  11.   FROM employees e
  12.   JOIN salary_records sr ON e.id = sr.employee_id
  13.   WHERE sr.expire_date IS NULL
  14. ) AS sub
  15. WHERE total_salary = max_salary
  16. ORDER BY dept_name;

  复制代码

五、窗口函数与高级查询

• 题目：给每个部门的员工按当前薪资从高到低排名，显示姓名、部门、薪资和排名（同一部门内排名）。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   e.department AS 部门,
  4.   sr.total_salary AS 薪资,
  5.   RANK() OVER (PARTITION BY e.department ORDER BY sr.total_salary DESC) AS 排名
  6. FROM employees e
  7. JOIN salary_records sr ON e.id = sr.employee_id
  8. WHERE sr.expire_date IS NULL
  9. ORDER BY e.department, 排名;

  复制代码
• 题目：计算每个员工的薪资较上一次调整的涨幅比例，显示姓名、调整日期和涨幅（保留1位小数）。
  1. SELECT
  2.   e.name AS 员工姓名,
  3.   curr.effective_date AS 调整日期,
  4.   curr.total_salary AS 当前薪资,
  5.   prev.total_salary AS 上次薪资,
  6.   ROUND(
  7.     (curr.total_salary - prev.total_salary) / prev.total_salary * 100,
  8.     1
  9.   ) AS 涨幅百分比
  10. FROM employees e
  11. JOIN salary_records curr ON e.id = curr.employee_id
  12. JOIN salary_records prev ON e.id = prev.employee_id
  13.   AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
  14. ORDER BY e.name, 调整日期;

  复制代码
• 题目：统计各部门中不同技能类型的员工数量，例如“技术部”的“编程语言”技能有多少人掌握。
  1. SELECT
  2.   e.department AS 部门,
  3.   s.skill_type AS 技能类型,
  4.   COUNT(DISTINCT e.id) AS 员工数量
  5. FROM employees e
  6. LEFT JOIN employee_skills es ON e.id = es.employee_id
  7. LEFT JOIN skills s ON es.skill_id = s.skill_id
  8. GROUP BY e.department, s.skill_type
  9. ORDER BY e.department, s.skill_type;

  复制代码

六、综合场景查询

• 题目：查询“张三”的所有上级（包含多级上级），显示上级姓名和职位关系（如“直接上级”、“间接上级”）。
  1. WITH RECURSIVE manager_chain AS (
  2.   -- 起始节点：张三的直接上级
  3.   SELECT
  4.     m.id AS manager_id,
  5.     m.name AS manager_name,
  6.     1 AS level,
  7.     '直接上级' AS relation
  8.   FROM employees e
  9.   LEFT JOIN employees m ON e.manager_id = m.id
  10.   WHERE e.name = '张三'
  12.   UNION ALL
  14.   -- 递归查询：上级的上级
  15.   SELECT
  16.     m2.id AS manager_id,
  17.     m2.name AS manager_name,
  18.     mc.level + 1 AS level,
  19.     CONCAT('间接上级（', mc.level + 1, '级）') AS relation
  20.   FROM manager_chain mc
  21.   JOIN employees m2 ON mc.manager_id = m2.manager_id
  22.   WHERE m2.id IS NOT NULL
  23. )
  24. SELECT manager_name AS 上级姓名, relation AS 职位关系
  25. FROM manager_chain;

  复制代码
• 题目：分析技能掌握情况与薪资的关系：计算掌握3种及以上技能的员工平均薪资，与掌握不足3种技能的员工平均薪资对比。
  1. SELECT
  2.   CASE
  3.     WHEN skill_count >= 3 THEN '掌握3种及以上技能'
  4.     ELSE '掌握不足3种技能'
  5.   END AS 技能掌握情况,
  6.   ROUND(AVG(total_salary), 2) AS 平均薪资
  7. FROM (
  8.   -- 子查询：统计每个员工的技能数量
  9.   SELECT
  10.     e.id,
  11.     e.name,
  12.     COUNT(DISTINCT es.skill_id) AS skill_count,
  13.     sr.total_salary
  14.   FROM employees e
  15.   LEFT JOIN employee_skills es ON e.id = es.employee_id
  16.   JOIN salary_records sr ON e.id = sr.employee_id
  17.   WHERE sr.expire_date IS NULL
  18.   GROUP BY e.id, e.name, sr.total_salary
  19. ) AS skill_stats
  20. GROUP BY 技能掌握情况;

  复制代码
• 题目：查询各部门近2年（2022-2023）薪资调整总次数，并计算调整次数最多的部门。
  1. SELECT
  2.   部门,
  3.   调整次数
  4. FROM (
  5.   SELECT
  6.     e.department AS 部门,
  7.     COUNT(sr.record_id) AS 调整次数,
  8.     RANK() OVER (ORDER BY COUNT(sr.record_id) DESC) AS rnk
  9.   FROM employees e
  10.   JOIN salary_records sr ON e.id = sr.employee_id
  11.   WHERE YEAR(sr.effective_date) BETWEEN 2022 AND 2023
  12.   GROUP BY e.department
  13. ) AS dept_adjust
  14. WHERE rnk = 1;

  复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！