当前位置: 首页 > news >正文

数据基础练习

-- 创建部门表
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 连接四张表:
    1. Employees → Departments(通过 department_id
    2. Employees → EmployeeProjects(通过 employee_id
    3. 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 = '研发部'));

解析

  • 嵌套子查询
    1. 最内层子查询获取 “研发部” 的 department_id
    2. 中层子查询计算该部门的平均工资。
    3. 外层查询筛选出工资高于该平均值的员工。

练习题 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):累加员工参与项目的预算(未参与项目的员工贡献 NULLSUM 会忽略 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
);

解析

  • 子查询作为临时表
    1. 先通过 max_salaries 子查询找出每个项目的最高工资。
    2. 再关联 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;

解析

  • 全连接实现
    1. 左半部分查询返回所有员工及其参与的项目(包括无项目的员工)。
    2. 右半部分查询返回所有项目及其参与员工(包括无员工的项目)。
    3. 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,则需大于市场部所有员工的工资。

总结

  1. 连接类型选择

    • INNER JOIN:获取匹配记录。
    • LEFT JOIN:保留左表所有记录。
    • UNION:合并结果集(去重)。
  2. 子查询技巧

    • 作为条件:WHERE column IN (subquery)
    • 作为临时表:FROM (subquery) AS alias
    • 存在性检查:WHERE EXISTS (subquery)
  3. 性能建议

    • 优先使用 JOIN 而非子查询(如 EXISTS 替代 IN)。
    • 在 ON 子句中放置过滤条件,减少数据扫描量。
http://www.lryc.cn/news/583089.html

相关文章:

  • 【Linux】权限的概念及理解
  • 进程于线程-3
  • 代码审计-springel表达式注入
  • JSP动态网页开发基础
  • 前后端集合如何传递
  • 主流大模型Agent框架 AutoGPT详解
  • thinkphp使用redis抢单实例
  • 如何将华为手机中的照片传输到电脑
  • 超越公有云:在裸金属服务器上构建低成本、高性能的静态资源服务
  • 【RK3568+PG2L50H开发板实验例程】FPGA部分 | Pango 的时钟资源——锁相环
  • 川翔云电脑:突破硬件极限,重构设计生产力范式
  • 使用DDR4控制器实现多通道数据读写(十九)
  • Amazon S3 对象存储服务深度解析:存储原理、应用场景与实战指南
  • 1.1 ARMv8/ARMv9安全扩展
  • ReactNative【实战】轮播图(含组件封装 ImageSlider)
  • 洛谷P1044 栈(学习向)
  • react16-react19都更新哪些内容?
  • clickhouse 各个引擎适用的场景
  • 【TCP/IP】2. 计算机网络与因特网体系结构
  • 手机文件夹隐藏工具,一键保护隐私
  • 数据库性能优化指南:解决ORDER BY导致的查询性能问题( SQL Server )
  • Dify 文本语意识别与自动补全工作流
  • MyBatisPlus-03-扩展功能
  • C#基础篇(11)泛型类与泛型方法详解
  • 1068.产品销售分析Ⅰ
  • huggingface 笔记: Trainer
  • 打造自己的组件库(二)CSS工程化方案
  • 跨服务sqlplus连接oracle数据库
  • 54页|PPT|新型数字政府综合解决方案:“一网 一云 一中台 N应用”平台体系 及“安全+运营”服务体系
  • 人工智能的基石:TensorFlow与PyTorch在图像识别和NLP中的应用