SQL 连接查询

概述

连接查询又称多表查询，查询的字段来自于多个表，推荐使用 SQL99语法

内连接（SQL92语法 不推荐使用）

就是 1992 年推出的语法

笛卡尔乘积

在连接查询不加以限制时，就会产生笛卡尔乘积现象。

• 笛卡尔乘积：A表字段3个值，B表字段4个值，最终结果 3 X 4 = 12 个值。

select b.name,b2.boyName 
from beauty b ,boys b2 

#----------------------#
name      |boyName|
----------|-------|
柳岩        |张无忌    |
柳岩        |鹿晗     |
柳岩        |黄晓明    |
柳岩        |段誉     |
Angelababy|张无忌    
Angelababy|鹿晗     |
Angelababy|黄晓明    
Angelababy|段誉     |
热巴        |张无忌    |
热巴        |鹿晗     |
热巴        |黄晓明    |
热巴        |段誉     |

等值连接

对字段进行一定的限制

select b.name,b2.boyName 
from beauty b ,boys b2 
where b.boyfriend_id = b2.id 

#--------------------------#
name      |boyName|
----------|-------|
热巴      |鹿晗   |
Angelababy|黄晓明 |

非等值连接

# 统计不同工资的等级

select e.salary as 工资,jg.grade_level as 等级
from myemployees.employees e ,myemployees.job_grades jg 
where e.salary between jg.lowest_sal and jg.highest_sal 
order by 等级,工资

#------------------------------------------------------#
工资 |等级|
-----|--|
 2900|A |
 2900|A |
 3000|B |
 3000|B |
 3100|B |
 4800|B |
 4800|B |
 5800|B |
 6000|C |
 6000|C |

自连接

两个需要作连接条件的字段在一张表上。

就是把同一张表重名为 不同的名字，就可以当做两张表查询了

select e.employee_id ,e.first_name ,m.first_name ,m.manager_id 
from myemployees.employees e ,myemployees.employees m
where e.employee_id = m.manager_id

连接查询 (SQL99) 语法

格式

select 字段
from 表1
[连接格式] join 表2  on 连接条件
[连接格式] join 表3  on 连接条件
[where 限定条件]
[group by 分组]
[having 查询后限定条件]
[order by 排序]

内连接

内连接：查询的是两表的交集

等值连接

on 后的连接条件是 相等

# 每个职位的员工个数
select j.job_title 职位名称 ,count(*) 数量
from myemployees.jobs j
join myemployees.employees e
on j.job_id = e.job_id   # 相等
group by 职位名称

非等值连接

on 后的连接条件是 不等的

# 职工的工资等级
select e.first_name 名称,e.salary 工资,j.grade_level 等级
from myemployees.employees e
join myemployees.job_grades j 
on e.salary between j.lowest_sal and j.highest_sal 

============================
名称         |工资   |等级|
-----------|-----|--|
Steven     |24000|E |
Neena      |17000|E |
Lex        |17000|E |
Alexander  | 9000|C |
Bruce      | 6000|C |
David      | 4800|B |
Valli      | 4800|B |

自连接

连接的两张表数据来自于同一张，只要别名不同就好

select e.employee_id 领导编号,e.first_name 领导名称 ,e1.first_name 职工名称,e1.manager_id 职工编号 
from myemployees.employees e
join myemployees.employees e1
on e.employee_id = e1.manager_id;

=================================
领导编号|领导名称     |职工名称       |职工编号|
----|---------|-----------|----|
 100|Steven   |Neena      | 100|
 100|Steven   |Lex        | 100|
 102|Lex      |Alexander  | 102|
 103|Alexander|Bruce      | 103|
 103|Alexander|David      | 103|
 103|Alexander|Valli      | 103|

外连接

外连接其实是：内连接的结果 + 主表中有而从表里没有的

可以使用where、having添加条件去掉 null 值

左外连接

左外连接：from 后的主表

# 查询 女生里没有男朋友的

select gby.name ,gbs.boyName 
from girls.beauty gby
left join girls.boys gbs
on gby.boyfriend_id = gbs.id 

============================
name      |boyName|
----------|-------|
周芷若    |张无忌  |
小昭      |张无忌  |
赵敏      |张无忌  |
热巴      |鹿晗    |
Angelababy|黄晓明  |
王语嫣    |段誉    |
柳岩      |        |
苍老师    |        |
周冬雨    |        |
岳灵珊    |        |
双儿      |        |
夏雪      |        |

右外连接

结果与上面一样，但是换成了 右外连接，主从表的位置也换了，现在就是 join 后是主表

# 查询 女生里没有男朋友的
select gby.name ,gbs.boyName 
from girls.boys gbs
right join girls.beauty gby
on gby.boyfriend_id = gbs.id 

============================
name      |boyName|
----------|-------|
周芷若    |张无忌  |
小昭      |张无忌  |
赵敏      |张无忌  |
热巴      |鹿晗    |
Angelababy|黄晓明  |
王语嫣    |段誉    |
柳岩      |        |
苍老师    |        |
周冬雨    |        |
岳灵珊    |        |
双儿      |        |
夏雪      |        |

交叉连接(笛卡尔乘积)

注意没有 on 关键字

select gby.*,gbs.*
from girls.beauty gby
cross join girls.boys gbs

=========================
id|name      |sex|borndate             |phone      |photo|boyfriend_id|id|boyName|userCP|
--|----------|---|---------------------|-----------|-----|------------|--|-------|------|
3|Angelababy|女  |1989-02-03 00:00:00.0|18209876567|     |           3| 1|张无忌    |   100|
3|Angelababy|女  |1989-02-03 00:00:00.0|18209876567|     |           3| 2|鹿晗     |   800|
3|Angelababy|女  |1989-02-03 00:00:00.0|18209876567|     |           3| 3|黄晓明    |    50|
3|Angelababy|女  |1989-02-03 00:00:00.0|18209876567|     |           3| 4|段誉     |   300|
4|热巴        |女  |1993-02-03 00:00:00.0|18209876579|     |           2| 1|张无忌    |   100|
4|热巴        |女  |1993-02-03 00:00:00.0|18209876579|     |           2| 2|鹿晗     |   800|
4|热巴        |女  |1993-02-03 00:00:00.0|18209876579|     |           2| 3|黄晓明    |    50|
4|热巴        |女  |1993-02-03 00:00:00.0|18209876579|     |           2| 4|段誉     |   300|