什么是多表查询

 

多表查询：当查询的数据并不是来源一个表时，需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据

多表链接方式

内连接（inner join）

连接两个表，通过相等或不等判断链接列，称为内连接。在内连接中典型的联接运算有=或<>之类的比较运算符，包括等值连接和自然连接

等值连接

非等值连接

自连接

SQL99：交叉连接（cross join）

SQL99：内连接（inner join）

SQL99：自然连接（natural join）

外连接（outer join）

在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接。返回内连接的结果，同时还返回左和右连接，称为全连接

右外连接

左外连接

全外连接

子查询

当一个查询是另一个查询的条件时，称之为子查询

多表连接语法

语法结构

使用一个连接从多个表中查询数据

select        table.column，table2.column        --投影

from          table1，table2                                --表的定位

where        table1.column1 = table2.column2 ；       --关联（连接）条件

方法一：select a.*, b.* from a,b where a.a = b.b;

方法二：select a.*,b.*  from a INNER JOIN b on a.a = b.b;

•  在where子句中写连接条件（不写where条件则会出现笛卡尔现象）

• 当多个表中有相同的列名时，将表名或表的别名作为列名的前缀

定义连接

当数据从多表中查询时，要使用连接（join）条件。一个表中的行按照存在于相应列中的值被连接到另一个表中的行

原则

• 在写一个连接表的select语句时，在列名前面用表名或者表别名可以使语义清楚，并且加快数据库访问
• 为了连接n个表在一起，最少需要n-1个连接条件。例如，为了连接4个表，最少需要3个连接条件

等值连接

什么是等值连接

等值连接也被称为简单连接（simple joins）或者内连接（inner join）。是通过等号来判断连接条件中的数据值是否相匹配

抉择矩阵（decision matrix）

是通过行与列来分析一个查询的方式

例如，如果想显示同一部门中所有名称为Taylor的雇员的名字和部门名称，可以写出下列决策矩阵

投影列	源表	条件
last_name	employees	last_name='Taylor'
department_nam	departments	employee.department_id= department.department_id

使用and操作符附加搜索条件

除了连接之外，还可以要求用where子句在连接中限制一个或多个表中的行

select * from employees;
select * from departments;
--查询所有雇员名称以及所在的部门名称            
--方法一 from x,x where x=x
select last_name,job_id,department_name 
from employees e,departments d 
where e.department_id=d.department_id

--查询所有雇员名称以及所在的部门名称            
--方法二 from x inner join x on x=x
select last_name,job_id,department_name 
from employees e inner join departments  d 
on e.department_id=d.department_id;


--查询Taylor名称以及所在的部门名称
select last_name,job_id,department_name 
from employees e,departments d 
where e.department_id=d.department_id
and last_name in 'Taylor'

多于两个表的连接

为了连接n个表，最少需要n-1个连接条件。例如，为了连接3个表最少需要两个连接

--查看雇员的last_name,departmentname 和 city 
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

--查询Taylor名称.的last_name,departmentname 和 city 
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
and last_name='Taylor'

 

 非等值连接

一个非等值连接是一种不使用相等（=）作为连接条件的查询。如！=、>、<、>=、<=、between and等都是非等值连接的条件判断

select x,x from x,x
where x（条件） between x（上限）  and x（下限）

创建案例表

 ps：原来这个不需要做主外键约束的

--查询所有雇员的薪水级别
select em.last_name,em.salary,gr.gra from employees em,job_grades gr
where em.salary between gr.lowest_sal  and gr.highest_sal

--and写法
select em.last_name,em.salary,gr.gra from employees em,job_grades gr
where em.salary>=gr.lowest_sal and em.salary<=gr.highest_sal

自连接

使用一个表连接它自身的操作

--查询每个雇员的名称以及对应经理的名称
select works.last_name,manages.last_name 
from employees works,employees manages
where works.manager_id=manages.employee_id

 外连接（outer join）

 外连接是指查询出符合连接条件的数据同时还包含孤儿数据。左外连接包含左表的孤儿数据，右外连接包含右表的孤儿数据，全外连接包两个表中的孤儿数据

孤儿数据（orphan data）：是指被连接的列的值为空的数据

外连接类型

左外连接（left outer join）：包含左表的孤儿数据

右外连接（right outer join）：包含右表的孤儿数据

全外连接（full outer join）：包含两个表中的孤儿数据

外连接语法格式

left outer join

right outer join

full outer join

定义连接类型，用on子句创建连接条件

左外连接（left outer join）

--分析 谁是左表，左表是数据都显示，满足条件的；右边就是孤儿数据的
----左外连接
--查询雇员名称以及他们所在部门的名称，包括没有部门的雇员
select e.last_name 雇员名称,d.department_name 部门名称 
from employees e left outer join departments d 
on e.department_id=d.department_id

 

右外连接（right outer join）

--以右边为基础，左表为孤儿表
--右外连接
--查询雇员名称以及他们所在的部门，包含哪些没有雇员的部门
select e.last_name,d.department_name 
from employees e right outer join departments d
on d.department_id=e.department_id

--用左外连接也可以查出来就是，谁是左表谁是右边的问题
select e.last_name 雇员名称,d.department_name 部门名称 
from departments d left outer join employees e 
on e.department_id=d.department_id

 

全外连接（full outer join）

--全外连接 full outer join
--孤儿数据左右都显示 两个表谁在左侧谁在右侧都没关系了
select e.last_name,d.department_name from employees e full outer join departments d
on e.department_id=d.department_id

Oracle扩展的外连接

在Oracle数据库中对外连接中的左外与右外连接做了扩展，可以简化外连接的语法。通过在连接条件的后侧使用（+）来表示是否显示孤儿数据，有（+）表示不显示孤儿数据而另一侧显示孤儿数据。（仅在Oracle中使用,有+号说明是孤儿数据）

--Oracle扩展的外连接
select e.last_name,d.department_name from employees e,departments d
where e.department_id(+)=d.department_id;

select e.last_name,d.department_name from employees e,departments d
where e.department_id=d.department_id(+);

内连接（inner join）

内连接通过inner join来建立两个表的连接。在内连接中使用inner join作为表的连接，用on子句给定连接条件。inner join语句在性能上和其他语句没有性能优势

--查询雇员id为202的雇员名称，部门名称 以及工作城市

--等值连接写法
select e.last_name 雇员名字,d.department_name 部门名称,l.city 工作城市 
from employees e,departments d,locations l
where e.department_id=d.department_id 
and d.location_id=l.location_id and e.employee_id in 202

--内连接写法 inner join  on
select e.last_name 雇员名字,d.department_name 部门名称,l.city 工作城市 
from employees e inner join departments d on e.department_id=d.department_id
inner join locations l on l.location_id=d.location_id
where e.employee_id=202