MySQL-连接查询

连接查询

又称多表查询，当查询的字段来自于多个表时，会用到连接查询

笛卡尔乘积

原因：没有有效的连接条件
避免：添加有效的连接条件

分类

按年代分类

• sql92标准：仅仅支持内连接
• sql99标准：支持内连接+外连接（左外和右外）+交叉连接

sql92内连接

等值连接

• 多表等值连接的结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名
• 可以搭配所有子句使用，比如排序、分组、筛选。。
  查询女神名和对应的男神名

select name,boyName
from boys,beauty
where beauty.boyfrienf_id=boys.id;

非等值连接

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

select salary,grade_level
from employees e,job_grade g
where salary between g.lowest_sal and g.highest_sal;

自连接

查询员工名和上级的名称

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

sql99

语法

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

内连接 inner

语法

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

等值连接

查询员工名、部门名

select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id;

查询部门个数大于3的城市名和部门个数

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

查询员工名、部门名、工种名、并按部门名降序

select last_name,department_name,job.title
from employees e
inner join departments d on e.department_id=d.department_id
inner join jobs j on e.job_id=j.job_id
order by department_name desc;

非等值连接

查询员工的工资级别

select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

自连接

查询员工的名字、领导的名字

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

外连接

应用场景：查询一个表中有，另一个表中没有的数据
特点：

• 外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它匹配的，则显示null
  外连接查询结果=内连接结果+主表中有而从表中没有的记录
• 左外连接，left join 左边的是主表
• 右外连接，right join 右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果

左外 left 【outer】

查询男朋友不在男神表的女神名

select b.name
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where bo.id is null;

右外 right 【outer】

select b.name
from boys bo 
right outer join beauty b
on b.boyfriend_id=bo.id
where bo.id is null;

全外 full 【outer】

全外连接=左连接与右连接的并集

select b.*,bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id=bo.id;

交叉连接 cross

就是笛卡尔乘积的结果

select b.*,bo.*
from beauty b
cross join boys bo;