【Mysql数据库基础04】连接查询、内连接、外连接

0 该博客所要用的数据库表的属性

1 SQL92 内连接

1.1 等值连接

查询员工名和对应的部门名

select last_name,department_name
from employees,departments
where departments.department_id = employees.department_id;

1.1.1 两个表的顺序可以调换

为表起别名
查询员工名、工种号、工种名

select last_name,j.job_id,job_titel
from jobs j,employees e
where e.job_id = j.job_id;

如果为表起了别名，则查询的字段就不能使用原来的表名去限定

1.1.2 加筛选

查询城市名中第二个字符为o的部门名和城市名

select department_name,city
from departments d,locations l
where d.location_id = l.location_id 
and city like '_o%';

1.1.3 加分组

查询每个城市的部门个数

select count(department_id),city
from departments d,locations l
where d.location_id = l.location_id
group by city;

1.1.4 加排序

查询每个工种的工种名和员工的个数，并且按员工个数降序

select job_title,count(employee_id) as 员工个数
from jobs j,employees e
where j.job_id = e.job_id
group by j.job_id
order by 员工个数 desc;

1.1.5 三表连接

查询员工名、部门名和所在的城市

select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;

1.2 非等值连接

查询员工的工资和工资级别

select salary,grade_level
from employees e,job_grades g
where salary BETWEEN lowest_sal and highest_sal;

1.3 自连接

查询 员工名和上级的名称

select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id = m.employee_id;

2 SQL99 内连接

语法：

select
from 表1
inner join 表2
on 连接调节
where
group by
having
order by

2.1 等值连接

1.查询部门个数>3的城市名和部门个数

select city,count(department_id) as 部门个数
from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having 部门个数 > 3;

2.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

select department_name,count(employee_id) as 员工个数
from departments d
inner join employees e
on d.department_id = e.department_id
group by d.department_id
having 员工个数 > 3
order by 员工个数 desc;

2.2 非等值连接

查询每个工资级别的个数>20，并且按工资级别降序排序

select grade_level,count(grade_level) as 级别个数
from job_grades g
join employees e
on e.salary between g.lowest_sal and highest_sal
group by grade_level
having 级别个数 > 20
order by 级别个数 desc;

2.3 自连接

查询姓名中包含字符k的员工的名字、上级的名字

select e.last_name as employee_name,m.last_name as manager_name 
from employees e
inner join employees m 
on e.manager_id = m.employee_id
where e.last_name like '%k%';

3 外连接

外连接的结果为主表中的所有记录
外连接的结果=主表中有+从表中没有的记录

3.1 左外和右外连接

谁当主表就可以查询到该表所有的记录

例：查询哪个城市没有部门
主表是城市的那个表

select l.city
from locations l
left outer join departments d
on l.location_id = d.location_id
where department_id is null;

外连接学的头晕😥

4 其他连接

交叉连接：笛卡尔乘积
全连接：