数据基础练习
-- 创建部门表
CREATE TABLE Departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
-- 创建员工表
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
manager_id INT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
);
-- 创建项目表
CREATE TABLE Projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(50) NOT NULL,
budget DECIMAL(10, 2) NOT NULL
);
-- 创建员工项目关联表(多对多关系)
CREATE TABLE EmployeeProjects (
employee_id INT,
project_id INT,
start_date DATE,
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);
-- 插入部门数据
INSERT INTO Departments (department_id, department_name) VALUES
(1, '研发部'),
(2, '市场部'),
(3, '财务部');
-- 插入员工数据(包含自关联的经理关系)
INSERT INTO Employees (employee_id, employee_name, salary, manager_id, department_id) VALUES
(1, '张三', 10000, NULL, 1), -- 张三是研发部经理
(2, '李四', 8000, 1, 1), -- 李四向张三汇报
(3, '王五', 9000, 1, 1), -- 王五向张三汇报
(4, '赵六', 7500, NULL, 2), -- 赵六是市场部经理
(5, '钱七', 6000, 4, 2), -- 钱七向赵六汇报
(6, '孙八', 8500, NULL, 3); -- 孙八是财务部经理
-- 插入项目数据
INSERT INTO Projects (project_id, project_name, budget) VALUES
(101, '网站重构', 50000),
(102, '移动应用开发', 80000),
(103, '数据分析系统', 60000);
-- 插入员工项目关联数据
INSERT INTO EmployeeProjects (employee_id, project_id, start_date) VALUES
(1, 101, '2023-01-15'),
(2, 101, '2023-01-15'),
(2, 102, '2023-03-20'),
(3, 102, '2023-03-20'),
(4, 103, '2023-02-10'),
(5, 103, '2023-02-10');
练习题 1:内连接查询
问题:查询每个员工的姓名、所在部门名称及其项目名称。
答案:
sql
SELECT e.employee_name,d.department_name,p.project_name
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.department_id
INNER JOIN EmployeeProjects ep ON e.employee_id = ep.employee_id
INNER JOIN Projects p ON ep.project_id = p.project_id;
解析:
- 通过三次
INNER JOIN
连接四张表:Employees
→Departments
(通过department_id
)Employees
→EmployeeProjects
(通过employee_id
)EmployeeProjects
→Projects
(通过project_id
)
- 结果:返回所有参与项目的员工及其部门和项目信息。
练习题 2:左外连接查询
问题:查询所有员工的姓名、部门名称及工资,包括未分配部门的员工。
答案:
sql
SELECT e.employee_name,d.department_name,e.salary
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.department_id;
解析:
LEFT JOIN
确保所有员工记录被返回,即使其department_id
为NULL
。- 结果:若员工无部门,
department_name
显示为NULL
。
练习题 3:自连接查询
问题:查询每个员工的姓名及其经理的姓名(若存在)。
答案:
sql
SELECT e.employee_name AS "员工姓名",m.employee_name AS "经理姓名"
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.employee_id;
解析:
- 自连接技巧:将
Employees
表分别以e
(员工)和m
(经理)的别名引用,通过manager_id
关联。 - 结果:经理姓名为
NULL
的员工表示其无上级(如部门总监)。
练习题 4:子查询(条件过滤)
问题:查询工资高于研发部平均工资的员工姓名和工资。
答案:
sql
SELECT employee_name,salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees WHERE department_id = (SELECT department_id FROM Departments WHERE department_name = '研发部'));
解析:
- 嵌套子查询:
- 最内层子查询获取 “研发部” 的
department_id
。 - 中层子查询计算该部门的平均工资。
- 外层查询筛选出工资高于该平均值的员工。
- 最内层子查询获取 “研发部” 的
练习题 5:多表连接与聚合
问题:统计每个部门的员工数量和总预算(部门所有员工参与项目的预算总和)。
答案:
sql
SELECT d.department_name,COUNT(e.employee_id) AS employee_count,SUM(p.budget) AS total_budget
FROM Departments d
LEFT JOIN Employees e ON d.department_id = e.department_id
LEFT JOIN EmployeeProjects ep ON e.employee_id = ep.employee_id
LEFT JOIN Projects p ON ep.project_id = p.project_id
GROUP BY d.department_id, d.department_name;
解析:
- 多次左连接:确保空部门和未参与项目的员工也被统计。
- 聚合逻辑:
COUNT(e.employee_id)
:统计部门员工数(包括未参与项目的员工)。SUM(p.budget)
:累加员工参与项目的预算(未参与项目的员工贡献NULL
,SUM
会忽略NULL
)。
练习题 6:子查询(表关联)
问题:查询每个项目的名称及其负责人姓名(假设负责人是工资最高的员工)。
答案:
sql
SELECT p.project_name,e.employee_name AS project_leader
FROM Projects p
JOIN (SELECT ep.project_id,MAX(e.salary) AS max_salaryFROM EmployeeProjects epJOIN Employees e ON ep.employee_id = e.employee_idGROUP BY ep.project_id
) AS max_salaries
ON p.project_id = max_salaries.project_id
JOIN Employees e
ON max_salaries.max_salary = e.salary
AND p.project_id IN (SELECT project_id FROM EmployeeProjects WHERE employee_id = e.employee_id
);
解析:
- 子查询作为临时表:
- 先通过
max_salaries
子查询找出每个项目的最高工资。 - 再关联
Employees
表,匹配工资最高的员工,并确保该员工确实参与了项目。
- 先通过
练习题 7:EXISTS 谓词
问题:查询至少参与一个项目的员工姓名。
答案:
sql
SELECT employee_name
FROM Employees e
WHERE EXISTS (SELECT 1 FROM EmployeeProjects ep WHERE ep.employee_id = e.employee_id);
解析:
EXISTS
检查子查询是否返回任何行,若存在则外层查询返回该员工记录。- 性能优化:
EXISTS
比IN
更高效,尤其在处理大量数据时。
练习题 8:全连接查询
问题:查询所有员工和项目的组合,包括未分配项目的员工和无员工参与的项目。
答案:
sql
SELECT e.employee_name,p.project_name
FROM Employees e
LEFT JOIN EmployeeProjects ep ON e.employee_id = ep.employee_id
LEFT JOIN Projects p ON ep.project_id = p.project_id
UNION
SELECT e.employee_name,p.project_name
FROM Projects p
LEFT JOIN EmployeeProjects ep ON p.project_id = ep.project_id
LEFT JOIN Employees e ON ep.employee_id = e.employee_id;
解析:
- 全连接实现:
- 左半部分查询返回所有员工及其参与的项目(包括无项目的员工)。
- 右半部分查询返回所有项目及其参与员工(包括无员工的项目)。
UNION
合并结果并去重。
练习题 9:多条件连接
问题:查询 2023 年 1 月 1 日后开始的项目及其参与员工姓名。
答案:
sql
SELECT p.project_name,e.employee_name
FROM Projects p
JOIN EmployeeProjects ep ON p.project_id = ep.project_id AND ep.start_date > '2023-01-01'
JOIN Employees e ON ep.employee_id = e.employee_id;
解析:
- 连接条件优化:将日期过滤条件
ep.start_date > '2023-01-01'
放在ON
子句中,减少中间结果集。
练习题 10:ANY/ALL 谓词
问题:查询工资高于市场部任何员工的员工姓名和工资。
答案:
sql
SELECT employee_name,salary
FROM Employees
WHERE salary > ANY (SELECT salary FROM Employees WHERE department_id = (SELECT department_id FROM Departments WHERE department_name = '市场部'));
解析:
> ANY
表示大于子查询结果中的任意一个值,即只需大于市场部最低工资。- 若改为
> ALL
,则需大于市场部所有员工的工资。
总结
连接类型选择:
INNER JOIN
:获取匹配记录。LEFT JOIN
:保留左表所有记录。UNION
:合并结果集(去重)。
子查询技巧:
- 作为条件:
WHERE column IN (subquery)
。 - 作为临时表:
FROM (subquery) AS alias
。 - 存在性检查:
WHERE EXISTS (subquery)
。
- 作为条件:
性能建议:
- 优先使用
JOIN
而非子查询(如EXISTS
替代IN
)。 - 在
ON
子句中放置过滤条件,减少数据扫描量。
- 优先使用