MySQL查询语句之连接查询（五）

含义：
又称多表查询,当查询字段来自于多个表时,就会用到连接查询

笛卡尔乘积现象:表1,m行,表2有n行,结果等于m×n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

分类:
按年代分类:
        sql 92 标准:仅仅支持内连接
        sql 99 标准[推荐]:内连接+外连接(左外加右外)+交叉连接
按功能分类:
        内连接:
                等值连接,非等值连接,自连接
        外连接:
                左外连接,右外连接,全外连接
        交叉连接

show databases;
use girls;
select * from beauty;
select * from boys;
select name,boyName from boys,beauty;  # 笛卡尔乘积现象
select name,boyName from boys,beauty
where beauty.boyfriend_id=boys.id;

一.sql92标准

1.等值连接

• 多表等值连接的结果为多表的交集部分
• n表连接,至少有n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表去表名
• 可以搭配前面介绍的所有子句,比如排序,分组,筛选

案例1:查询女神名和对应的男神名

select name,boyName 
from boys,beauty # 等值连接，beauty表boyfriend_id与boys表中id等值
where beauty.boyfriend_id=boys.id;

案例2:查询员工名和对应的部门名

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

拓展：为表起别名

• 提高语句简洁度,
• 区分多个重名的字段

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

起别名范例： 查询员工名，工种号，工种名

select last_name,employees.job_id,job_title
from employees,jobs
where employees.job_id=jobs.job_id;

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

1.1 两个表的顺序可否调换,可以的

select last_name,e.job_id,job_title
from jobs j,employees e
where e.job_id=j.job_id; # 顺序调换

1.2 可以加筛选?可以的

案例1: 查询有奖金的员工名和部门名

select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id 
and e.commission_pct is not null;  # 加入筛选

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

select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and city like '_o%'; # 加入筛选

1.3可以加分组?可以的

案例1:查询每个城市的部门个数

select count(*) 个数,city
from departments d,locations l
where d.location_id=l.location_id
group by city; # group by加入分组

案例2:查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select department_name,d.manager_id,min(salary)
from employees e,departments d
where d.department_id=e.department_id
and commission_pct is not null
group by department_name,d.manager_id; # 多字段分组

1.4 可以加排序?可以的

案例:查询每个工种的工种名和员工个数,并且按员工个数降序

select job_title,count(*)
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
order by count(*) desc; # 加排序

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; # where三表连接

2.非等值连接

案例:查询员工的工资级别和工资等级

select salary,grade_level 
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal # 非等值连接
and g.grade_level='A';

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;  # 在同一张表自己连接

二.sql99标准

语法：
    select 查询列表
    from 表1 别名【连接类型】
    join 表2 别名
    on 连接条件
  【where 筛选条件】
  【group by 分组】
  【having 筛选条件】
  【order by 排序列表】
分类：
    内连接(★)：inner
    外连接
          左外(★)：left 【outer】
          右外(★)：right 【outer】
          全外：full 【outer】
    交叉连接：cross

一）内连接A∩B

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

分类:
      等值,非等值,自连接

特点:
    ①添加排序,分组,筛选
    ②inner 可省略(直接是join)
    ③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
    ④inner join连接与sql92语法中等值连接最终实现的效果是一样的,都是查询多表的交集

2.1.1 等值连接

案例1:查询员工名,部门名

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

案例2:查询名中含有e的员工名和工种名(添加筛选)

select last_name,job_title
from employees e
inner join jobs j
on e.job_id=j.job_id
where e.last_name like '%e%'; # 添加where筛选，放在on后面

案例3:查询部门个数大于3的城市名和部门个数(添加分组和筛选)
step1.查询每个城市的部门个数
step2.筛选满足条件的

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

案例4:查询哪个部门员工个数大于3的部门名和员工个数,并按个数降序(添加排序)

select count(*) 个数,department_name
from employees e
inner join departments d
on e.department_id=d.department_id
group by department_name
having count(*)>3
order by count(*) desc; # 添加排序

案例5:查询员工名,部门名,工种名,并按部门名降序(添加三表连接)

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;

2.1.2 非等值连接

案例1： 查询员工工资级别

select salary,grade_level
from employees e
join job_grades g  # 内连接可省略inner
on e.salary between g.lowest_sal and g.highest_sal; # 非等值连接

案例2： 查询每个工资级别个数>20的个数,降序

select count(*),grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc; # desc：降序，默认是升序

2.1.3 自连接

案例： 查询员工名字包含字符k,上级名字

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

二）外连接

应用场景:
        用于查询一个表有,另一个表没有的记录
特点:
        1.外连接的查询结果为主表中的所有记录
                如果从表中有和它匹配的,则显示匹配的值
                如果从表中没有和他匹配的,则显示null值
                外连接查询结果=内连接结果+主表有而从表没有的记录
        2.左外连接,left join左边是主表,右外连接right join右边的是主表
        3.左外和右外交换两个表的顺序,可以实现同样的效果
        4.全外连接=内连接结果+表1中有的但表2没有+表2中有而表1中没有
引入: 查询男朋友不在boys表的女神名

use girls;
select * from beauty;
select * from boys;

左外连接 A∪(A∩B)

含义： 主表A，和从表B与主表A的匹配值

select b.name
from beauty b
left outer join boys bo # 主表beauty，从表boys
on b.boyfriend_id=bo.id # 连接条件
where bo.id is null;

右外连接 （A∩B）∪B

含义： 主表B，和从表A与主表B的匹配值

select b.name,bo.*
from boys bo
right outer join beauty b # 主表beauty，从表boys
on b.boyfriend_id=bo.id
where bo.id is null;

全外 A∪B

备注： mysql不支持，oracle支持

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

案例1: 查询哪个部门没有员工
左外：

use myemployees;
select d.*,e.employee_id
from departments d
left outer join employees e # 主表departments，从表employees
on d.department_id=e.department_id
where e.employee_id is null;

右外：

select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id=e.department_id
where e.employee_id is null;

三）交叉连接

select b.*,bo.*
from beauty b
cross join boys bo; # 笛卡尔乘积

sql92 PK sql99
功能: sql99支持的较多
可读性: sql99实现连接条件和筛选条件的分离,可读性较高