SQL 学习笔记 - 多表关系与多表查询

一、多表关系

在数据库中，表与表之间存在关联关系，主要有以下几种：

1. 一对多（常见，如：部门 - 员工）
   • 一个部门有多个员工
   • 员工表 emp 的 dept_id 外键关联部门表 dept 的 id
2. 多对多
   • 通常通过中间表实现（例如：学生 - 课程关系）
3. 一对一
   • 一个实体拆分到多个表时可能出现

二、多表查询方式

1. 隐式内连接

select emp.id, emp.name, dept.name 
from emp, dept 
where emp.dept_id = dept.id;

2. 显式内连接

select emp.id, emp.name, dept.name 
from emp inner join dept 
on emp.dept_id = dept.id;

3. 使用别名的多表查询

select e.id, e.name, d.name 
from emp as e, dept as d 
where e.dept_id = d.id and e.gender = 1 and e.salary > 8000;

4. 外连接

• 左外连接：查询左表的所有数据，即使右表没有匹配数据

  select emp.name, dept.name 
  from emp left join dept 
  on emp.dept_id = dept.id;
  

带条件的左连接：

select emp.name, dept.name 
from emp left join dept 
on emp.dept_id = dept.id 
where salary > 10000;

三、子查询

1. 标量子查询（返回单个值）

-- 查询最早入职日期
select min(entry_date) from emp;

-- 查询最早入职的员工信息
select * from emp where entry_date = (select min(entry_date) from emp);

-- 查询比阮小五入职晚的员工信息
select * from emp 
where entry_date > (select entry_date from emp where name = '阮小五');

2. 列子查询（返回一列）

-- 查询教研部和咨询部的员工
select * from emp 
where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

3. 行子查询（返回多列）

-- 查询与李忠薪资和职位相同的员工
select * from emp 
where (salary, job) = (select salary, job from emp where name = '李忠');

等价写法：

select * from emp 
where salary = (select salary from emp where name = '李忠') 
  and job = (select job from emp where name = '李忠');

4. 表子查询（结果当作一张临时表）

-- 查询 2006-01-01 之后入职的员工及部门信息
select e.*, d.name 
from (select * from emp where entry_date > '2006-01-01') as e, dept as d 
where e.dept_id = d.id;

四、典型案例

1. 查询教研部男性且 2011-05-01 之后入职的员工

select e.* 
from emp as e, dept as d 
where e.dept_id = d.id and d.name = '教研部' 
  and e.gender = 1 and entry_date > '2011-05-01';

2. 查询工资低于公司平均工资的男性员工

select e.* 
from emp as e, dept as d 
where e.dept_id = d.id 
  and e.salary < (select avg(salary) from emp) 
  and e.gender = 1;

3. 查询工资低于本部门平均工资的员工

-- 示例：1号部门平均工资
select avg(salary) from emp where dept_id = 1;

4. 查询部门人数超过 10 人的部门名称

select d.name, count(*) 
from emp as e, dept as d 
where e.dept_id = d.id 
group by d.name 
having count(*) > 10;

五、总结

• 内连接：取交集，匹配成功的数据
• 外连接：保留一边的全部数据（左/右）
• 子查询：分为标量、列、行、表子查询
• 多表查询核心：通过外键建立关联，使用 join 或 where 进行条件匹配