–多表联合查询
–当需要获取的数据分布在多张表中,需要使用多表联合查询
–SQL92方式
–笛卡尔积:将多个表中的数据进行一一对应,所得到的结果为多表笛卡尔积
–结果的数量为所有表的数量的乘积
```sql
select *from emp,dept,salgrade
select *from emp,dept where emp.deptno=dept.deptno
等值连接筛选
概念:先做表的笛卡尔积,然后筛选,筛选条件为等值筛选。
注意:条件为字段的值相同来进行筛选,字段的名字可以不同
--查询员工姓名,工作,薪资,部门名称
select *from emp,dept where emp.deptno=dept.deptno
可以直接在select子句中使用字段直接获取数据,但是效率比较低。建议字段前加上表名
注意:如果是公共字段,则必须声明表名
select ename,job,sal,dname,from emp,dept where emp.deptno=dept.deptno --等值连接筛选
select emp.ename,emp.job,emp.sal,dept.dname, emp.deptno from emp,dept where emp.deptno=dept.deptno
select e.ename,e.job,e.sal,d.dname,from emp e,dept d where e.deptno=d.deptno and sal>2000 --给表使用别名
不等值连接
--查询员工姓名,工作,工资,工资等级
select *from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal
`
``
自连接:
--查询员工姓名,工作,薪资,以及上级领导姓名
select e1.name,e1.job,e1.sal,e2.ename from emp e1, emp e2 where e1.mgr=e2.empno
外连接
左外连接:加在右边,显示左边对应字段没有值的数据
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+)
右外连接,加在左边,显示右边对应字段没有值的数据
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
select * from emp e,dept d where e.deptno(+)=d.deptno
SQL99方式
–注意1:依然可以给表添加别名
–注意2:如果使用on或者using 关键字对结果进行筛选,必须使用inner join作用表与表的连接,其中inner可以省略
–注意3:外连接的outer关键字可以省略不写
–注意4:依然可以继续使用分组,having,排序等
–笛卡尔积:使用cross join 关键字
–select 内容 from 表名 cross join
select *from emp cross join dept
--筛选
--查询员工姓名,工作,薪资,部门名称
--自然连接:使用关键字natural join
--使用:select 内容 from 表名 natural join 表名
--特点1:底层先笛卡尔积,然后按照同名同值字段字段自动进行等值筛选
--问题1:如果想按照字段名不同,但是值相同进行等值筛选怎么办?
--问题2:如果只想按照部分字段结果筛选怎么办?
select * from emp natural join dept
--解决1:使用using关键字
--作用1:指明使用指定的字段对联合查询的结果进行等值筛选
--注意:指明的字段必须是两表的同名同值字段
--使用:select 内容 from 表名 inner join 表名 using(字段名,字段名,......)
select * from emp inner join dept using(deptno)
--解决2:使用on关键字进行自定义连接条件筛选(等值筛选,不等值筛选)
--注意:普通筛选条件使用where进行筛选,不要使用on进行。好处SQL语句的阅读性变强
--使用:select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
--外连接:
--左外连接:select 内容 from 表名 left outer join 表名 on 连接条件
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
select * from emp e left join dept d on e.deptno=d.deptno
--右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
--查询员工姓名,工作,薪资,部门名称及没有员工的部门信息
select * from emp e right join dept d on e.deptno=d.deptno
--全外连接:select 内容 from 表名 full outer join 表名 on 连接条件
select * from emp e full outer join dept d on e.deptno=d.deptno
--自连接:
--查询员工及其上级领导姓名
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
–SQL92&SQL99实现三表联合查询
--创建city表,使用图形操作即可
--给city表添加测试数据
insert into city values(1,'北京','首都');
insert into city values(1,'西安','古城');
insert into city values(1,'上海','国际化都市');
insert into city values(1,'杭州','新一线');
--将部门表中的loc字段设置为城市表的城市编号
update dept set loc='1' where deptno=50;
update dept set loc='2' where deptno=40;
update dept set loc='3' where deptno=30;
update dept set loc='4' where deptno=20;
update dept set loc='4' where deptno=10;
--完成三表联合查询
--SQL92实现:查询员工信息及部门信息及所在城市名称并且员工的工资大于2000或者有奖金
--特点:易于书写,难于阅读
--缺点:92的SQL语句结构不清晰
--用法:
--select 内容(别名,连接符,去除重复,oracle函数,逻辑运算)
--from 表名1, 表名2, 表名3...
--where 条件(连接条件,普通筛选条件,where子句关键字)
--group by 分组字段
--having 多行函数筛选
--order by 排序字段
select e.*,d.dname,c.cname
from emp e,dept d,city c
where (e.deptno=d.deptno and d.loc=c.cid and sal>2000)or (e.deptno=d.deptno and d.loc=c.cid and comm is not null)
order by e.sal
--SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
-- 特点:难于书写,易于阅读
--使用:
--select 内容 from 表名1
--inner join 表名2
--on 连接条件
--inner join 表名3
--on 连接条件
--where 普通筛选条件
--group by 分组
--having 多行函数筛选
--order by 排序
select * from emp e
inner join dept d
on e.deptno=d.deptno
inner join city c
on d.loc=c.cid
where e.sal>2000 or e.comm is not null
order by e.sal