SQL练习题(持续更新中)-博客园

SQL练习题(持续更新中)

2025-10-01 04:30:41 发布 44 浏览

页面报错/反馈

已收藏点赞

测试表创建

-- 1. 部门表（departments）
CREATE TABLE IF NOT EXISTS departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID，主键自增',
    dept_name VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称，唯一不可重复',
    location VARCHAR(100) COMMENT '部门所在地',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) COMMENT '公司部门信息表';

-- 2. 员工表（employees）
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID，主键自增',
    name VARCHAR(50) NOT NULL COMMENT '员工姓名',
    gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '员工性别',
    department VARCHAR(50) COMMENT '所属部门（关联departments表的dept_name）',
    hire_date DATE NOT NULL COMMENT '入职日期',
    phone VARCHAR(20) UNIQUE COMMENT '手机号码，唯一',
    email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一',
    manager_id INT COMMENT '直属上级ID（自关联，关联本表的id）',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    FOREIGN KEY (department) REFERENCES departments(dept_name) ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
) COMMENT '公司员工信息表';

-- 3. 技能表（skills）
CREATE TABLE IF NOT EXISTS skills (
    skill_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '技能ID，主键自增',
    skill_name VARCHAR(50) NOT NULL UNIQUE COMMENT '技能名称，唯一',
    skill_type VARCHAR(30) COMMENT '技能类型（如：编程语言、工具等）',
    description VARCHAR(200) COMMENT '技能描述'
) COMMENT '技能信息表';

-- 4. 员工-技能中间表（employee_skills）
CREATE TABLE IF NOT EXISTS employee_skills (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键自增',
    employee_id INT NOT NULL COMMENT '员工ID，关联employees表',
    skill_id INT NOT NULL COMMENT '技能ID，关联skills表',
    proficiency INT CHECK (proficiency BETWEEN 1 AND 5) COMMENT '熟练程度（1-5，5为最高）',
    learned_date DATE COMMENT '掌握该技能的日期',
    UNIQUE KEY uk_employee_skill (employee_id, skill_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES skills(skill_id) ON DELETE CASCADE
) COMMENT '员工与技能的关联表（多对多）';

-- 5. 薪资记录表（salary_records）
CREATE TABLE IF NOT EXISTS salary_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键自增',
    employee_id INT NOT NULL COMMENT '员工ID，关联员工表',
    basic_salary DECIMAL(10, 2) NOT NULL CHECK (basic_salary >= 0) COMMENT '基本工资',
    bonus DECIMAL(10, 2) DEFAULT 0 CHECK (bonus >= 0) COMMENT '奖金',
    subsidy DECIMAL(10, 2) DEFAULT 0 CHECK (subsidy >= 0) COMMENT '补贴',
    total_salary DECIMAL(10, 2) GENERATED ALWAYS AS (basic_salary + bonus + subsidy) STORED COMMENT '总薪资（自动计算）',
    effective_date DATE NOT NULL COMMENT '生效日期',
    expire_date DATE COMMENT '失效日期（NULL表示当前生效）',
    reason VARCHAR(200) COMMENT '薪资调整原因',
    created_by VARCHAR(50) COMMENT '操作人',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    CONSTRAINT uk_employee_effective UNIQUE (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) COMMENT '员工薪资变动记录表';

插入数据

-- 先插入部门数据（因为员工表依赖部门表）
INSERT INTO departments (dept_name, location) VALUES
('技术部', '北京'),
('市场部', '上海'),
('人事部', '广州'),
('财务部', '深圳');

-- 插入员工数据
-- 插入部门数据（使用IGNORE避免重复）
INSERT IGNORE INTO departments (dept_name, location) VALUES
('技术部', '北京海淀区'),
('市场部', '上海浦东新区'),
('人事部', '广州天河区'),
('财务部', '深圳南山区'),
('运营部', '杭州西湖区');

-- 插入员工数据（包含部门关联和上级关联）
INSERT IGNORE INTO employees (id, name, gender, department, hire_date, phone, email, manager_id) VALUES
(1, '张三', '男', '技术部', '2020-01-15', '13800138001', 'zhangsan@example.com', NULL),
(2, '李四', '女', '市场部', '2021-03-20', '13900139002', 'lisi@example.com', NULL),
(3, '王五', '男', '技术部', '2019-11-05', '13700137003', 'wangwu@example.com', 1),
(4, '赵六', '女', '人事部', '2022-05-10', '13600136004', 'zhaoliu@example.com', NULL),
(5, '钱七', '男', '技术部', '2021-09-30', '13500135005', 'qianqi@example.com', 1),
(6, '孙八', '女', '财务部', '2020-07-22', '13400134006', 'sunba@example.com', NULL),
(7, '周九', '男', '市场部', '2022-01-18', '13300133007', 'zhoujiu@example.com', 2),
(8, '吴十', '女', '运营部', '2021-06-05', '13200132008', 'wushi@example.com', NULL),
(9, '郑十一', '男', '财务部', '2023-02-10', '13100131009', 'zheng11@example.com', 6),
(10, '王十二', '女', '运营部', '2022-09-15', '13001300130', 'wang12@example.com', 8);

-- 插入技能数据
INSERT IGNORE INTO skills (skill_id, skill_name, skill_type, description) VALUES
(1, 'Java', '编程语言', '面向对象的编程语言'),
(2, 'Python', '编程语言', '简洁易学的脚本语言'),
(3, 'MySQL', '数据库', '关系型数据库管理系统'),
(4, 'JavaScript', '编程语言', '前端开发主要语言'),
(5, 'Excel', '办公软件', '数据处理与分析工具'),
(6, 'PPT', '办公软件', '演示文稿制作工具'),
(7, 'Vue', '前端框架', '渐进式JavaScript框架'),
(8, 'Spring Boot', '后端框架', 'Java开发框架'),
(9, '数据分析', '业务能力', '数据挖掘与分析能力'),
(10, '项目管理', '管理能力', '项目规划与执行能力');

-- 插入员工-技能关联数据
INSERT IGNORE INTO employee_skills (employee_id, skill_id, proficiency, learned_date) VALUES
(1, 1, 5, '2018-06-10'),  -- 张三：Java（熟练5）
(1, 3, 4, '2019-01-15'),  -- 张三：MySQL（熟练4）
(1, 8, 5, '2019-05-20'),  -- 张三：Spring Boot（熟练5）
(3, 1, 4, '2019-03-20'),  -- 王五：Java（熟练4）
(3, 2, 3, '2020-05-10'),  -- 王五：Python（熟练3）
(3, 3, 3, '2019-12-05'),  -- 王五：MySQL（熟练3）
(5, 1, 3, '2021-02-28'),  -- 钱七：Java（熟练3）
(5, 4, 2, '2022-01-15'),  -- 钱七：JavaScript（熟练2）
(5, 7, 2, '2022-03-10'),  -- 钱七：Vue（熟练2）
(2, 5, 4, '2020-11-05'),  -- 李四：Excel（熟练4）
(2, 6, 5, '2019-09-30'),  -- 李四：PPT（熟练5）
(2, 9, 4, '2021-01-20'),  -- 李四：数据分析（熟练4）
(4, 5, 5, '2021-07-20'),  -- 赵六：Excel（熟练5）
(4, 10, 3, '2022-08-15'), -- 赵六：项目管理（熟练3）
(6, 3, 4, '2019-05-15'),  -- 孙八：MySQL（熟练4）
(6, 5, 4, '2018-11-10'),  -- 孙八：Excel（熟练4）
(7, 6, 3, '2021-05-10'),  -- 周九：PPT（熟练3）
(7, 9, 2, '2022-03-20'),  -- 周九：数据分析（熟练2）
(8, 10, 4, '2020-08-05'), -- 吴十：项目管理（熟练4）
(10, 9, 3, '2022-11-10'); -- 王十二：数据分析（熟练3）

-- 插入薪资记录数据（包含初始薪资和调整记录）
INSERT IGNORE INTO salary_records (record_id, employee_id, basic_salary, bonus, subsidy, effective_date, expire_date, reason, created_by) VALUES
-- 张三的薪资记录
(1, 1, 7000, 500, 500, '2020-01-15', '2021-12-31', '入职初始薪资', 'admin'),
(2, 1, 8000, 800, 500, '2022-01-01', NULL, '年度调薪', 'admin'),

-- 李四的薪资记录
(3, 2, 6000, 300, 200, '2021-03-20', '2022-06-30', '入职初始薪资', 'admin'),
(4, 2, 6500, 400, 200, '2022-07-01', NULL, '半年度调薪', 'admin'),

-- 王五的薪资记录
(5, 3, 8500, 500, 200, '2019-11-05', '2021-05-31', '入职初始薪资', 'admin'),
(6, 3, 9200, 600, 400, '2021-06-01', NULL, '晋升调薪', 'admin'),

-- 赵六的薪资记录
(7, 4, 5500, 200, 100, '2022-05-10', NULL, '入职初始薪资', 'admin'),

-- 钱七的薪资记录
(8, 5, 7000, 300, 200, '2021-09-30', '2023-02-28', '入职初始薪资', 'admin'),
(9, 5, 7500, 400, 200, '2023-03-01', NULL, '年度调薪', 'admin'),

-- 孙八的薪资记录
(10, 6, 7200, 500, 300, '2020-07-22', '2022-12-31', '入职初始薪资', 'admin'),
(11, 6, 7800, 600, 300, '2023-01-01', NULL, '年度调薪', 'admin'),

-- 周九的薪资记录
(12, 7, 6200, 200, 100, '2022-01-18', '2023-06-30', '入职初始薪资', 'admin'),
(13, 7, 6800, 300, 100, '2023-07-01', NULL, '年度调薪', 'admin'),

-- 吴十的薪资记录
(14, 8, 6500, 400, 300, '2021-06-05', '2022-11-30', '入职初始薪资', 'admin'),
(15, 8, 7000, 500, 300, '2022-12-01', NULL, '年度调薪', 'admin'),

-- 郑十一的薪资记录
(16, 9, 5800, 200, 100, '2023-02-10', NULL, '入职初始薪资', 'admin'),

-- 王十二的薪资记录
(17, 10, 6000, 300, 200, '2022-09-15', '2023-08-31', '入职初始薪资', 'admin'),
(18, 10, 6300, 300, 200, '2023-09-01', NULL, '年度调薪', 'admin');

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

题目：查询所有部门(departments)的部门名称(dept_name)和所在地(location)，并按部门名称(dept_name)、升序(ASC)排序。
```
SELECT dept_name, location 
FROM departments 
ORDER BY dept_name ASC;
```
题目：查询员工表(employees)中部门类型(department)是技术部并且入职时间(hire_date)在2021年及之后的员工，显示姓名(name)、入职日期(hire_date)和手机号(phone)。
```
SELECT name, hire_date, phone 
FROM employees 
WHERE department = '技术部' 
  AND hire_date >= '2021-01-01';
```
题目：查询薪资记录(salary_records)中总薪资（total_salary）在8000-10000之间的记录，显示员工ID(employee_id)、总薪资(total_salary)和生效日期(effective_date),并按生效日期(effective_date)、降序(desc)排序。
```
SELECT employee_id,total_salary,effective_date
FROM salary_records
WHERE total_salary BETWEEN 8000 AND 10000
ORDER BY effective_date;
```
题目：查询技能表(skills)中技能类型(skill_type)为编程语言的记录，显示技能ID(skill_id)、技能名称(skill_name)和技能描述(description)。
```
SELECT skill_id,skill_name,description
FROM skills
WHERE skill_type='编程语言'；
```
题目：查询员工-技能中间表(employee_skills)中技能熟练度(proficiency)为5并且掌握技能日期(learned_date)在2020年的记录，显示员工ID(employee_id)、技能ID(skill_id)和掌握技能日期(learned_date)，并按掌握日期(learned_date)进行降序(ASC)进行排序。
```
SELECT employee_id,skill_id,learned_date
FROM employee_skills
WHERE proficiency =5
  AND learned_date >= '2019-01-01'
ORDER BY learned_date ASC;
```

二、聚合函数与分组查询

题目：统计每个部门(departments)的员工人数，显示部门名称和员工人数，过滤人大于等于3的部门。
```
SELECT department AS 部门名称,
       COUNT(*) AS 员工人数
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
```

题目：计算各部门(department)当前生效薪资(total_salary)的平均值（取expire_date IS NULL的记录），显示部门名称(department)和平均薪资（保留2位小数）。

SELECT e.department AS 部门名称,
       ROUND(AVG(sr.total_salary), 2) AS 平均薪资
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
GROUP BY e.department;

题目：统计每种技能的掌握人数，按人数从多到少进行排序，包含无人掌握的技能（显示0）。

SELECT 
      s.skill_name AS 技能名称,
      COUNT(es.employee_id) AS 掌握人数
FROM skills s
LEFT JOIN employee_skills es ON s.skill_id = es.skill_id
GROUP BY s.skill_name
ORDER BY 掌握人数 desc;

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

题目：查询所有员工的姓名、所属部门名称及部门所在地，包括未分配部门的员工（若有）。

SELECT 
  e.name AS 员工姓名,
  d.dept_name AS 部门名称,
  d.location AS 部门所在地
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name;

题目：查询掌握“Java”技能的员工姓名、部门及熟练程度，要求熟练程度≥4。

SELECT 
  e.name AS 员工姓名,
  e.department AS 部门,
  es.proficiency AS 熟练程度
FROM employees e
JOIN employee_skills es ON e.id = es.employee_id
JOIN skills s ON es.skill_id = s.skill_id
WHERE s.skill_name = 'Java' 
  AND es.proficiency >= 4;

题目：查询2023年有薪资调整的员工姓名及调整前后的总薪资（需显示“调整前薪资”和“调整后薪资”）。

SELECT 
  e.name AS 员工姓名,
  prev.total_salary AS 调整前薪资,
  curr.total_salary AS 调整后薪资,
  curr.effective_date AS 调整日期
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id 
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
WHERE YEAR(curr.effective_date) = 2023;

四、子查询与嵌套查询

题目：查询薪资高于本部门平均薪资的员工，显示姓名、部门和当前总薪资。

SELECT 
  e.name AS 员工姓名,
  e.department AS 部门,
  sr.total_salary AS 当前总薪资
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
  AND sr.total_salary > (
    SELECT AVG(sr2.total_salary)
    FROM employees e2
    JOIN salary_records sr2 ON e2.id = sr2.employee_id
    WHERE sr2.expire_date IS NULL
      AND e2.department = e.department
  );

题目：找出同时掌握“Java”和“MySQL”两种技能的员工姓名。

SELECT e.name AS 员工姓名
FROM employees e
WHERE EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'Java'
)
AND EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'MySQL'
);

题目：查询各部门中薪资最高的员工，显示部门名称、员工姓名和最高薪资。

SELECT 
  dept_name AS 部门名称,
  name AS 员工姓名,
  max_salary AS 最高薪资
FROM (
  SELECT 
    e.department AS dept_name,
    e.name,
    sr.total_salary,
    MAX(sr.total_salary) OVER (PARTITION BY e.department) AS max_salary
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
) AS sub
WHERE total_salary = max_salary
ORDER BY dept_name;

五、窗口函数与高级查询

题目：给每个部门的员工按当前薪资从高到低排名，显示姓名、部门、薪资和排名（同一部门内排名）。

SELECT 
  e.name AS 员工姓名,
  e.department AS 部门,
  sr.total_salary AS 薪资,
  RANK() OVER (PARTITION BY e.department ORDER BY sr.total_salary DESC) AS 排名
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
ORDER BY e.department, 排名;

题目：计算每个员工的薪资较上一次调整的涨幅比例，显示姓名、调整日期和涨幅（保留1位小数）。

SELECT 
  e.name AS 员工姓名,
  curr.effective_date AS 调整日期,
  curr.total_salary AS 当前薪资,
  prev.total_salary AS 上次薪资,
  ROUND(
    (curr.total_salary - prev.total_salary) / prev.total_salary * 100, 
    1
  ) AS 涨幅百分比
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
ORDER BY e.name, 调整日期;

题目：统计各部门中不同技能类型的员工数量，例如“技术部”的“编程语言”技能有多少人掌握。

SELECT 
  e.department AS 部门,
  s.skill_type AS 技能类型,
  COUNT(DISTINCT e.id) AS 员工数量
FROM employees e
LEFT JOIN employee_skills es ON e.id = es.employee_id
LEFT JOIN skills s ON es.skill_id = s.skill_id
GROUP BY e.department, s.skill_type
ORDER BY e.department, s.skill_type;

六、综合场景查询

题目：查询“张三”的所有上级（包含多级上级），显示上级姓名和职位关系（如“直接上级”、“间接上级”）。

WITH RECURSIVE manager_chain AS (
  -- 起始节点：张三的直接上级
  SELECT 
    m.id AS manager_id,
    m.name AS manager_name,
    1 AS level,
    '直接上级' AS relation
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
  WHERE e.name = '张三'

  UNION ALL

  -- 递归查询：上级的上级
  SELECT 
    m2.id AS manager_id,
    m2.name AS manager_name,
    mc.level + 1 AS level,
    CONCAT('间接上级（', mc.level + 1, '级）') AS relation
  FROM manager_chain mc
  JOIN employees m2 ON mc.manager_id = m2.manager_id
  WHERE m2.id IS NOT NULL
)
SELECT manager_name AS 上级姓名, relation AS 职位关系
FROM manager_chain;

题目：分析技能掌握情况与薪资的关系：计算掌握3种及以上技能的员工平均薪资，与掌握不足3种技能的员工平均薪资对比。

SELECT 
  CASE 
    WHEN skill_count >= 3 THEN '掌握3种及以上技能'
    ELSE '掌握不足3种技能'
  END AS 技能掌握情况,
  ROUND(AVG(total_salary), 2) AS 平均薪资
FROM (
  -- 子查询：统计每个员工的技能数量
  SELECT 
    e.id,
    e.name,
    COUNT(DISTINCT es.skill_id) AS skill_count,
    sr.total_salary
  FROM employees e
  LEFT JOIN employee_skills es ON e.id = es.employee_id
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
  GROUP BY e.id, e.name, sr.total_salary
) AS skill_stats
GROUP BY 技能掌握情况;

题目：查询各部门近2年（2022-2023）薪资调整总次数，并计算调整次数最多的部门。

SELECT 
  部门,
  调整次数
FROM (
  SELECT 
    e.department AS 部门,
    COUNT(sr.record_id) AS 调整次数,
    RANK() OVER (ORDER BY COUNT(sr.record_id) DESC) AS rnk
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE YEAR(sr.effective_date) BETWEEN 2022 AND 2023
  GROUP BY e.department
) AS dept_adjust
WHERE rnk = 1;

登录查看全部

参与评论