MySQL练习数据库及完整练习题

数据库创建脚本

-- 创建数据库（使用UTF8MB4字符集支持中文）
CREATE DATABASE IF NOT EXISTS employee_management 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE employee_management;

-- 1. 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. 职位表
CREATE TABLE positions (
    position_id INT PRIMARY KEY AUTO_INCREMENT,
    position_name VARCHAR(50) NOT NULL,
    base_salary DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    birth_date DATE,
    hire_date DATE NOT NULL,
    dept_id INT,
    position_id INT,
    manager_id INT,
    email VARCHAR(100),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (position_id) REFERENCES positions(position_id),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. 薪资记录表
CREATE TABLE salaries (
    salary_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    salary_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    bonus DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. 项目表
CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12,2),
    dept_id INT,
    leader_id INT,
    status VARCHAR(20) DEFAULT 'Planning',
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (leader_id) REFERENCES employees(emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

测试数据插入脚本

-- 插入部门数据
INSERT INTO departments (dept_name, location) VALUES
('研发部', '3楼东区'),
('市场部', '2楼南侧'),
('财务部', '1楼西侧'),
('人力资源部', '1楼东侧'),
('运维部', '3楼西区');

-- 插入职位数据
INSERT INTO positions (position_name, base_salary) VALUES
('总监', 25000.00),
('经理', 18000.00),
('高级工程师', 15000.00),
('工程师', 12000.00),
('助理', 8000.00),
('实习生', 5000.00);

-- 插入员工数据(先插入没有manager_id的记录)
INSERT INTO employees (emp_name, gender, birth_date, hire_date, dept_id, position_id, email) VALUES
('张伟', 'M', '1980-05-15', '2010-06-01', 1, 1, 'zhangwei@company.com'),
('李娜', 'F', '1985-08-22', '2012-03-15', 2, 1, 'lina@company.com');

-- 更新manager_id并插入其他员工
INSERT INTO employees (emp_name, gender, birth_date, hire_date, dept_id, position_id, manager_id, email) VALUES
('王强', 'M', '1990-11-10', '2015-07-01', 1, 2, 1, 'wangqiang@company.com'),
('赵敏', 'F', '1992-03-25', '2016-09-01', 1, 3, 3, 'zhaomin@company.com'),
('刘洋', 'M', '1995-07-18', '2018-04-01', 1, 4, 3, 'liuyang@company.com'),
('陈晨', 'F', '1993-12-05', '2017-11-01', 2, 2, 2, 'chenchen@company.com'),
('周杰', 'M', '1991-09-30', '2016-06-01', 3, 1, NULL, 'zhoujie@company.com'),
('吴芳', 'F', '1988-04-12', '2014-08-01', 4, 1, NULL, 'wufang@company.com');

-- 插入薪资数据
INSERT INTO salaries (emp_id, salary_date, amount, bonus) VALUES
(1, '2023-01-31', 25000.00, 5000.00),
(1, '2023-02-28', 25000.00, 3000.00),
(2, '2023-01-31', 25000.00, 4000.00),
(2, '2023-02-28', 25000.00, 3500.00),
(3, '2023-01-31', 18000.00, 2000.00),
(3, '2023-02-28', 18000.00, 1500.00),
(4, '2023-01-31', 15000.00, 1000.00),
(4, '2023-02-28', 15000.00, 800.00),
(5, '2023-01-31', 12000.00, 500.00),
(5, '2023-02-28', 12000.00, 300.00);

-- 插入项目数据
INSERT INTO projects (project_name, start_date, end_date, budget, dept_id, leader_id, status) VALUES
('OA系统升级', '2023-01-01', '2023-06-30', 500000.00, 1, 3, 'In Progress'),
('市场推广活动', '2023-02-15', '2023-05-15', 300000.00, 2, 2, 'Planning'),
('财务软件迁移', '2023-03-01', '2023-09-30', 200000.00, 3, 7, 'Not Started');

完整练习题及参考答案

基础查询

1. 查询所有员工的基本信息（员工ID、姓名、性别、部门名称、职位名称）

SELECT e.emp_id, e.emp_name, e.gender, d.dept_name, p.position_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN positions p ON e.position_id = p.position_id;

多表关联

4. 查询每个部门的名称及其员工人数

SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

聚合函数

7. 查询每个部门的平均薪资

SELECT d.dept_name, AVG(s.amount + s.bonus) AS avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
WHERE s.salary_date = '2023-02-28'
GROUP BY d.dept_id, d.dept_name;

子查询

10. 查询薪资高于公司平均薪资的员工

SELECT e.emp_name, (s.amount + s.bonus) AS total_salary
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
WHERE s.salary_date = '2023-02-28'
AND (s.amount + s.bonus) > (
    SELECT AVG(amount + bonus) 
    FROM salaries 
    WHERE salary_date = '2023-02-28'
);

复杂查询

13. 查询每个部门薪资最高的员工信息

SELECT d.dept_name, e.emp_name, MAX(s.amount + s.bonus) AS max_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
WHERE s.salary_date = '2023-02-28'
GROUP BY d.dept_id, d.dept_name, e.emp_id, e.emp_name;

16. 查询薪资连续两个月增长的员工（修正版）

SELECT e.emp_name
FROM employees e
JOIN salaries s1 ON e.emp_id = s1.emp_id AND s1.salary_date = '2023-01-31'
JOIN salaries s2 ON e.emp_id = s2.emp_id AND s2.salary_date = '2023-02-28'
WHERE (s2.amount + s2.bonus) > (s1.amount + s1.bonus);

高级查询

18. 使用CASE WHEN统计各部门不同性别员工人数

SELECT 
    d.dept_name,
    COUNT(CASE WHEN e.gender = 'M' THEN 1 END) AS male_count,
    COUNT(CASE WHEN e.gender = 'F' THEN 1 END) AS female_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

实用练习题

窗口函数

19. 查询每个部门薪资排名前三的员工

MySQL 8.0+

SELECT * FROM (
    SELECT 
        e.emp_name,
        d.dept_name,
        s.amount + s.bonus AS total_salary,
        RANK() OVER (PARTITION BY d.dept_id ORDER BY s.amount + s.bonus DESC) AS salary_rank
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    JOIN salaries s ON e.emp_id = s.emp_id
    WHERE s.salary_date = '2023-02-28'
) ranked
WHERE salary_rank <= 3;

MySQL 5.4

在MySQL 5.4版本中，由于不支持窗口函数(如RANK(), DENSE_RANK(), ROW_NUMBER())，我们需要使用传统方法来实现这个查询。以下是几种可行的解决方案：

方法一：使用用户变量和子查询

SELECT dept_name, emp_name, total_salary
FROM (
    SELECT 
        d.dept_name,
        e.emp_name,
        s.amount + s.bonus AS total_salary,
        @rank := IF(@current_dept = d.dept_id, 
                   IF(@current_salary = s.amount + s.bonus, @rank, @rank + 1), 
                   1) AS rank,
        @current_dept := d.dept_id,
        @current_salary := s.amount + s.bonus
    FROM 
        (SELECT @rank := 0, @current_dept := 0, @current_salary := 0) AS vars,
        departments d
        JOIN employees e ON d.dept_id = e.dept_id
        JOIN salaries s ON e.emp_id = s.emp_id
    WHERE 
        s.salary_date = '2023-02-28'
    ORDER BY 
        d.dept_id, 
        s.amount + s.bonus DESC
) AS ranked
WHERE rank <= 3;

方法二：使用自连接和COUNT方式

SELECT d.dept_name, e.emp_name, s.amount + s.bonus AS total_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
WHERE s.salary_date = '2023-02-28'
AND (
    SELECT COUNT(DISTINCT s2.amount + s2.bonus)
    FROM employees e2
    JOIN salaries s2 ON e2.emp_id = s2.emp_id
    WHERE 
        e2.dept_id = e.dept_id
        AND s2.salary_date = '2023-02-28'
        AND (s2.amount + s2.bonus) >= (s.amount + s.bonus)
) <= 3
ORDER BY d.dept_name, total_salary DESC;

方法三：使用临时表（更易理解）

-- 创建临时表存储每个员工的总薪资
CREATE TEMPORARY TABLE temp_employee_salaries
SELECT 
    d.dept_id,
    d.dept_name,
    e.emp_id,
    e.emp_name,
    s.amount + s.bonus AS total_salary
FROM 
    departments d
    JOIN employees e ON d.dept_id = e.dept_id
    JOIN salaries s ON e.emp_id = s.emp_id
WHERE 
    s.salary_date = '2023-02-28'
ORDER BY 
    d.dept_id, 
    total_salary DESC;

-- 查询每个部门薪资前三的员工
SELECT t1.dept_name, t1.emp_name, t1.total_salary
FROM temp_employee_salaries t1
WHERE (
    SELECT COUNT(*) 
    FROM temp_employee_salaries t2
    WHERE 
        t2.dept_id = t1.dept_id 
        AND t2.total_salary >= t1.total_salary
) <= 3
ORDER BY t1.dept_name, t1.total_salary DESC;

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_employee_salaries;

方法四：针对每个部门单独查询（简单但效率较低）

-- 研发部
SELECT d.dept_name, e.emp_name, s.amount + s.bonus AS total_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
WHERE d.dept_name = '研发部' AND s.salary_date = '2023-02-28'
ORDER BY total_salary DESC
LIMIT 3;

-- 市场部
SELECT d.dept_name, e.emp_name, s.amount + s.bonus AS total_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salaries s ON e.emp_id = s.emp_id
WHERE d.dept_name = '市场部' AND s.salary_date = '2023-02-28'
ORDER BY total_salary DESC
LIMIT 3;

-- 其他部门类似...

注意事项

1. 方法一使用了用户变量，在复杂查询中可能不够稳定
2. 方法二使用了相关子查询，性能可能较差，特别是数据量大时
3. 方法三使用了临时表，是最可靠的方式，但需要额外的存储空间
4. 方法四最简单，但需要为每个部门单独执行查询
5. 所有方法都假设每个员工在指定月份只有一条薪资记录

日期函数

20. 查询入职超过5年的员工

SELECT emp_name, hire_date
FROM employees
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

可以直接在MySQL 5.4及以上版本运行。