连接查询（多表查询）——MySQL

连接查询（多表查询）

又称多表查询，当查询的字段涉及多个表的时候，就要用到连接查询

分类：

为表起别名：

• 提高语句的简洁度
• 区分多个重名字段
• 注意：如果为表起了别名，则查询的字段就不能使用原来的别名去限定

内连接

查询A、B 交集部分数据

语法：

1. 隐式内连接select 字段列表 from 表1，表2 where 筛选条件 ;
2. 显式内连接select 字段列表 from 表1 【inner】 join 表2 on 连接条件 ... ;

• 例题：

查询每一个员工的姓名 , 及关联的部门的名称
表结构: emp , dept
连接条件: emp.dept_id = dept.id

• 隐式内连接实现

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

若果有员工没有部门，则不会显示

• 显示内连接实现

select e.name, d.name 
from emp as e 
join dept as d 
on e.dept_id = d.id;

外连接

左右外连接

1. 左外连接

左外连接相当于查询表A(左表)的所有数据和中间绿色的交集部分的数据。
表1的位置为左表，表2的位置为右表

select  字段列表  
from  表1left 【outer】 join  表2on  条件...

1. 右外连接 右外连接相当于查询表B(右表)的所有数据和中间绿色的交集部分的数据。 表1的位置为左表，表2的位置为右表

select  字段列表  
from  表1right 【outer】 join  表2on  条件...

• 想把右外连接改成左外连接，并且查询结果不改变，可以把right改为left，并且把表1和表2的位置调换
• 例题：

1. 查询emp表的所有数据, 和对应的部门信息 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，因为有一些中间数据查询不到，需要考虑使用外连接查询。 表结构: emp, dept 连接条件: emp.dept_id = dept.id （左外连接）

select e.*, d.name  
from  emp as e
leftouterjoin dept as d
on e.dept_id = d.id

1. 查询dept表的所有数据, 和对应的员工信息

表结构: emp, dept
连接条件: emp.dept_id = dept.id
(右外连接)

select d.*, e.*from emp e  # 左表rightouterjoin dept as d  # 右表 on e.dept_id = d.id;

将右外改为左外

select d.*, e.*from dept as d 
leftouterjoin emp as e 
on e.dept_id = d.id;

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

• 语法：

select  字段列表  
from  表1as 别名1join 表1as 别名2on 条件....

注意：自连接表一定要起别名

对于自连接查询，可以是内连接查询，也可以是外连接查询。

• 例题： 1. 查询员工 及其 所属领导的名字，

普通员工和领导其实都属于员工，都在员工表当中，每个员工又有一项manager_id记录这他的领导的id值
用内连接

select a.name , b.name 
from emp as a , emp as b 
where a.managerid = b.id;

• 例题2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 表结构: emp a , emp b 用外连接

select a.name as'员工', b.name as'领导'from emp as a 
leftjoin emp as b 
on a.managerid = b.id;

1. 当要查询girl表中的g_name和boy表中的b_name，girl表中的boyfriend_id与boy表中的id是对应的select g_name,b_name from boy,bearty``````where girl.boufriend_id = boy.id 如果不加where筛选条件的话，会显示笛卡尔乘机的效果，出现许多的无用项

会用第一个表中的每一行和第二个表中的每一行进行逐个匹配，然后进行筛选，如果匹配会筛选出来
可以自由调换表的顺序

1. 查询员工名和对应的部门名，员工名在employees表中，部门名在department表中select last_name,department_name``````from employees,department``````where employees.'department_id',department.'department_id'

SQL92语法

等值连接

为表起别名

当语句中经常出表名作用域的时候，每条语句就会很长，为精简语句

1. 查询员工名，工种号，工种名select e.last_name,e.job_id,j.job_title ``````from employees as e , job as j ``````where e.'job_id'= j.job_id

加筛选条件

1. 查询有奖金的员工名、部门名select last_name,department_name``````from employees as e , department as d``````where e.'department_id' = d.'department_id' ``````and e.'commission_pct'is not null 已经有一个where筛选了不能再用where，用and
2. 查询城市名中第二个字符为o的部门名和城市名select department_name,city``````from department as d,location as l``````where d.'location_id' = l.'location_id'``````and city like '_o%'

加分组

1. 查询每个城市的部门个数select count(*) as 个数 , city ``````from department as d = location as l``````group by city
2. 查询有奖金的每个部门的部门名，和部门的领导编号和该部门的最低工资标准select department_name , manager_id,min(salary) ``````from employees as e , department as d``````where e.'department_id' = d.'department_id' ``````and commission_pct is not null ``````group by department_name,d,manager_id ;

加排序

1. 查询每个工种的工种名和员工的个数，并按员工个数降序select job_title , count(*) ``````from employees as e , job as j ``````where e.'job_id'= j.job_id``````group by job_title``````order by count(*) desc

实现三表连接

1. 查询员工名，部门名，和所用城市名select last_name,department_name,city``````from employees as e , department as d , location as l``````where e.'department_id' = d.'department_id' ``````and d.'location_id' = l.'location_id'

非等值连接

也就是把上面的等于号换成了不等于（大于、小于、不等）

1. 查询员工的工资和工资级别select salary,grade_level``````from employees e,job_frades g``````where salary between g.'lowest_sal' and g.'higthest_sal' 追加其他条件。and g.'lowest_sal'='A';只查看等级为A的

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

1. 查询员工名，和他对应的上级名，每一个有员工对应的上级编号select e.employee_id,e.last_name,m.employee_id,m.last_name``````from employees.e,employees.me代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突where e.'manager_id' = m.'employees_id'

SQL99语法

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

内连接

1.等值连接

• 特点 1. 可以添加排序、分组、筛选2. inner可以省略3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读。4. inner join连接和SQL192语法中的等值连接效果是一样的，都是查询多表的交集。

1. 查询员工名，部门名select last_name,department_name``````from department d``````innner join demployees as e``````on e.'department_id' = d.'department_id'
2. 查询名字中包含e的员工名和工种名(添加了筛选条件)select last_name,job_id``````from employees as e``````inner join as j``````on e.'job_id' = j.'job_id'``````where e.'last_name' like '%e%' ;
3. 查询部门个数>3的城市名和部门个数（添加分组、筛选条件）select city,count(*) as 部门个数``````from departments as d``````inner hoin locations as l``````on d.'location_id' = l.'location_id'``````group by city``````having count(*)>3
4. 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序select count(*) , department_id``````from employees as e``````inner hoin departments as d``````on e.'department_id' = d.'department_id'``````group by department_name``````having count(*)>3``````order by count(*) desc

1.非等值连接

1. 查询员工的工资级别select salary,grade_level``````from employees as e``````join job_grades as g``````on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ;
2. 查询每个工资级别的个数，并按照级别降序。select count(*),grade_level``````from employees as e``````join job_grades as g``````on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ``````having count(*)>20``````order by grade_lecel desc

自连接

把原来这一张表当做多张表来使用，由表中的数据找到另一个数据，再由找到的数据回过头来找另一个数据，可以这样往复下去

1. 查询员工名，和他对应的上级名 (每一个有员工对应的上级编号)select m.employee_id,m.last_name``````from employees as e e代表员工表，m代表领导表。其实都在一张表里，重命名来避免冲突join employees as m``````on e.'manager_id' = m.'employees_id'

外连接

用于查询主表的时候，主表中没有，但是附表中，主表通过外连接附表来查询数据
特点：

1. 外连接查询结果为主表中的所有数据。 如果主表中有与之匹配的显示匹配值。 表中没有与之匹配的显示null 外连接查询结果=内连接结果+主表中有而从表中没有的数据
2. 左外连接，left join左边的是主表 右外连接,right join右边的是主表。
3. 左外和右外交换两个表的顺序可以实现同样的效果。
4. 查询男朋友 不在男神表的的女神名左外连接SELECT b.*,bo.*``````FROM boys bo``````LEFT OUTER JOIN beauty b``````ON b.'boyfriend_id' = bo.'id'``````WHERE b.'id' IS NULL;
5. 查询哪个部门没有员工1. 左外SELECT d.*,e.employee_id``````FROM departments d``````LEFT OUTER JOIN employees e``````ON d.'department_id' = e.'department_id'``````WHERE e.'employee_id' IS NULL;2. 右外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;

全外

USE girls;

SELECT b.*,bo.*

FROM beauty b

FULL OUTER JOIN boys bo

ON b.'boyfriend_id' = bo.id;

交叉连接

SELECT b.*,bo.*

FROM beauty b

CROSS JOIN boys bo;

sql92和 sql99 pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高