连接查询（多表查询）—

连接查询（多表查询）

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

分类：

在这里插入图片描述

为表起别名：

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

内连接

查询A、B 交集部分数据

语法：

隐式内连接
select 字段列表 from 表1，表2 where 筛选条件 ;
显式内连接
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;

外连接

在这里插入图片描述

左右外连接

左外连接

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

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

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

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

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

查询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

查询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;

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

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

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

SQL92语法

等值连接

为表起别名

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

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

加筛选条件

查询有奖金的员工名、部门名
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
查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from department as d,location as l
where d.'location_id' = l.'location_id'
and city like '_o%'

加分组

查询每个城市的部门个数
select count(*) as 个数 , city
from department as d = location as l
group by city
查询有奖金的每个部门的部门名，和部门的领导编号和该部门的最低工资标准
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 ;

加排序

查询每个工种的工种名和员工的个数，并按员工个数降序
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

实现三表连接

查询员工名，部门名，和所用城市名
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'

非等值连接

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

查询员工的工资和工资级别
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的

自连接

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

查询员工名，和他对应的上级名，每一个有员工对应的上级编号
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语法中的等值连接效果是一样的，都是查询多表的交集。

查询员工名，部门名
select last_name,department_name
from department d
innner join demployees as e
on e.'department_id' = d.'department_id'
查询名字中包含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的城市名和部门个数（添加分组、筛选条件）
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
查询哪个部门的员工个数>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.非等值连接

查询员工的工资级别
select salary,grade_level
from employees as e
join job_grades as g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal' ;
查询每个工资级别的个数，并按照级别降序。
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

自连接

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

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

外连接

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

外连接查询结果为主表中的所有数据。
如果主表中有与之匹配的显示匹配值。
表中没有与之匹配的显示null
外连接查询结果=内连接结果+主表中有而从表中没有的数据
左外连接，left join左边的是主表
右外连接,right join右边的是主表。
左外和右外交换两个表的顺序可以实现同样的效果。
查询男朋友不在男神表的的女神名

左外连接
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.'boyfriend_id' = bo.'id'
WHERE b.'id' IS NULL;
查询哪个部门没有员工
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;