连接查询（多表查询）

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

分类：

为表起别名：

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

内连接

查询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  表1  
left 【outer】 join  表2  
on  条件...

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

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

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

• 例题：

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

select e.*, d.name  
from  emp as e
left outer join dept as d
on e.dept_id = d.id

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

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

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

将右外改为左外

select d.*, e.* 
from dept as d 
left outer join emp as e 
on e.dept_id = d.id;

自连接

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

• 语法：

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

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

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

• 例题： 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 
left join emp as b 
on a.managerid = b.id;

3. 当要查询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筛选条件的话，会显示笛卡尔乘机的效果，出现许多的无用项

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

2. 查询员工名和对应的部门名，员工名在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实现连接条件和筛选条件的分离，可读性较高