SQL练习题(持续更新中)
测试表创建
-- 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');
一、基础查询与条件筛选（单表操作）
- 题目：查询所有部门的名称和所在地，并按部门名称升序排序。
SELECT dept_name, location FROM departments ORDER BY dept_name ASC; - 题目：查询技术部入职时间在2021年及之后的员工，显示姓名、入职日期和手机号。
SELECT name, hire_date, phone FROM employees WHERE department = '技术部' AND hire_date >= '2021-01-01'; - 题目：查询薪资记录中总薪资（total_salary）在8000-10000之间的记录，显示员工ID、总薪资和生效日期。
SELECT employee_id, total_salary, effective_date FROM salary_records WHERE total_salary BETWEEN 8000 AND 10000;
二、聚合函数与分组查询
题目：统计每个部门的员工人数，显示部门名称和人数，过滤出人数≥3的部门。
SELECT department AS 部门名称, COUNT(*) AS 员工人数 FROM employees GROUP BY department HAVING COUNT(*) >= 3;题目：计算各部门当前生效薪资的平均值（取expire_date IS NULL的记录），显示部门名称和平均薪资（保留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;
三、连接查询（多表关联）
题目：查询所有员工的姓名、所属部门名称及部门所在地，包括未分配部门的员工（若有）。
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.to