隐藏在伊斯坦布尔的猫免安装绿色版
680M · 2025-09-18
测试表创建
-- 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', '[email protected]', NULL),(2, '李四', '女', '市场部', '2021-03-20', '13900139002', '[email protected]', NULL),(3, '王五', '男', '技术部', '2019-11-05', '13700137003', '[email protected]', 1),(4, '赵六', '女', '人事部', '2022-05-10', '13600136004', '[email protected]', NULL),(5, '钱七', '男', '技术部', '2021-09-30', '13500135005', '[email protected]', 1),(6, '孙八', '女', '财务部', '2020-07-22', '13400134006', '[email protected]', NULL),(7, '周九', '男', '市场部', '2022-01-18', '13300133007', '[email protected]', 2),(8, '吴十', '女', '运营部', '2021-06-05', '13200132008', '[email protected]', NULL),(9, '郑十一', '男', '财务部', '2023-02-10', '13100131009', '[email protected]', 6),(10, '王十二', '女', '运营部', '2022-09-15', '13001300130', '[email protected]', 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;SELECT name, hire_date, phone FROM employees WHERE department = '技术部' AND hire_date >= '2021-01-01';SELECT employee_id, total_salary, effective_date FROM salary_records WHERE total_salary BETWEEN 8000 AND 10000;题目：统计每个部门的员工人数，显示部门名称和人数，过滤出人数≥3的部门。
SELECT department AS 部门名称, COUNT(*) AS 员工人数FROM employeesGROUP BY departmentHAVING COUNT(*) >= 3;题目：计算各部门当前生效薪资的平均值（取expire_date IS NULL的记录），显示部门名称和平均薪资（保留2位小数）。
SELECT e.department AS 部门名称, ROUND(AVG(sr.total_salary), 2) AS 平均薪资FROM employees eJOIN salary_records sr ON e.id = sr.employee_idWHERE sr.expire_date IS NULLGROUP BY e.department;题目：统计每种技能的掌握人数，按人数从多到少排序，包含无人掌握的技能（显示0）。
SELECT s.skill_name AS 技能名称, COUNT(es.employee_id) AS 掌握人数FROM skills sLEFT JOIN employee_skills es ON s.skill_id = es.skill_idGROUP BY s.skill_name;题目：查询所有员工的姓名、所属部门名称及部门所在地，包括未分配部门的员工（若有）。
SELECT e.name AS 员工姓名, d.dept_name AS 部门名称, d.location AS 部门所在地FROM employees eLEFT JOIN departments d ON e.department = d.dept_name;题目：查询掌握“Java”技能的员工姓名、部门及熟练程度，要求熟练程度≥4。
SELECT e.name AS 员工姓名, e.department AS 部门, es.proficiency AS 熟练程度FROM employees eJOIN employee_skills es ON e.id = es.employee_idJOIN skills s ON es.skill_id = s.skill_idWHERE 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 eJOIN salary_records curr ON e.id = curr.employee_idJOIN salary_records prev ON e.id = prev.employee_id AND prev.expire_date = curr.effective_date - INTERVAL 1 DAYWHERE YEAR(curr.effective_date) = 2023;题目：查询薪资高于本部门平均薪资的员工，显示姓名、部门和当前总薪资。
SELECT e.name AS 员工姓名, e.department AS 部门, sr.total_salary AS 当前总薪资FROM employees eJOIN salary_records sr ON e.id = sr.employee_idWHERE 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 eWHERE 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 subWHERE total_salary = max_salaryORDER 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 eJOIN salary_records sr ON e.id = sr.employee_idWHERE sr.expire_date IS NULLORDER 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 eJOIN salary_records curr ON e.id = curr.employee_idJOIN salary_records prev ON e.id = prev.employee_id AND prev.expire_date = curr.effective_date - INTERVAL 1 DAYORDER BY e.name, 调整日期;题目：统计各部门中不同技能类型的员工数量，例如“技术部”的“编程语言”技能有多少人掌握。
SELECT e.department AS 部门, s.skill_type AS 技能类型, COUNT(DISTINCT e.id) AS 员工数量FROM employees eLEFT JOIN employee_skills es ON e.id = es.employee_idLEFT JOIN skills s ON es.skill_id = s.skill_idGROUP BY e.department, s.skill_typeORDER 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_statsGROUP 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_adjustWHERE rnk = 1;
