什么是多表查询
多表查询:当查询的数据并不是来源一个表时,需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据
多表链接方式
内连接(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