关系型数据库 SQL 核心操作与查询实践
一、 数据库结构构建
为了规范化管理企业内部员工、部门、项目及家属信息，我们需要定义如下关系模式：
- 人员表 (Staff)：包含 ID (主键)、姓名、出生日期、居住地址、性别、月薪、直属主管 ID、部门编码。
- 部门表 (Division)：包含 部门编号 (主键)、名称、经理 ID、任命日期。
- 部门地址表 (Div_Location)：包含 部门编号 (主键)、具体办公点。
- 项目表 (Task)：包含 项目编号 (主键)、项目名称、地点、所属部门编号。
- 工时记录表 (Activity_Log)：包含 员工 ID、项目编号 (组合主键)、投入时长。
- 受赡养人表 (Dependent_Info)：包含 员工 ID、姓名 (组合主键)、性别、出生日期、关系。
二、 数据定义与操作示例
通过 SQL 定义上述表结构并插入初始数据，以下是针对常见业务需求的查询与维护逻辑实现：
-- 示例：查询参与了特定项目且满足复杂条件的员工信息
-- 获取在"研发部"工作且薪资低于 3000 的员工
SELECT s.name, s.address
FROM Staff s
JOIN Division d ON s.dept_id = d.div_code
WHERE d.div_name = '研发部' AND s.salary < 3000;
-- 示例：子查询实现关联匹配
-- 查询未参与项目 'P01' 的员工名单
SELECT name FROM Staff
WHERE staff_id NOT IN (
SELECT staff_id FROM Activity_Log WHERE task_id = 'P01'
);
-- 示例：利用聚合函数与分组处理
-- 统计至少承担了 3 个项目的员工
SELECT staff_id
FROM Activity_Log
GROUP BY staff_id
HAVING COUNT(task_id) >= 3;
三、 进阶查询技巧
在处理复杂的多表关联与全量项目覆盖时，常采用以下 SQL 模式：
- 全量匹配：使用嵌套
NOT EXISTS结构判断是否完成所有任务列表。 - 多维度过滤：利用
GROUP BY和HAVING COUNT(DISTINCT ...)统计跨部门参与情况。 - 自关联查询：通过别名将表自身连接，用于查找汇报关系或进行比对分析。
四、 数据维护与更新
对已有关系进行模式变更及数据修正时，需确保一致性：
-- 修改数据：统一上调薪资水平
UPDATE Staff SET salary = 3000 WHERE salary < 3000;
-- 结构变更：动态添加字段以记录更多信息
ALTER TABLE Dependent_Info ADD (work_org VARCHAR(50), job_title VARCHAR(20));
-- 删除逻辑：清理部门相关联的员工数据
DELETE FROM Staff WHERE dept_id IN (
SELECT div_code FROM Division WHERE div_name = '研发部'
);
