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

数据基础练习

news 2025/7/10 10:30:05

-- 创建部门表
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)：累加员工参与项目的预算（未参与项目的员工贡献 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
);

解析：

  • 子查询作为临时表：
    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中的应用