Oracle多表联合查询

—多表联合查询：
–当需要获取的数据分布在多张中，考虑使用联合查询
–SQL92方式
–SQL99方式

–SQL92方式
–笛卡尔积:将多个表的数据进行一一对应，所得到结果为多表的笛卡尔积。
–结果的数量为所有表的数量的乘积。
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.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
--外连接
–P.S.修改表数据
select t.*, t.rowid from emp t;
select * from emp for update;
–左外连接:加在右边，显示左边对应字段没有值的数据
–查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+)
--右外连接:加在做边，显示右边边对应字段没有值的数据
–查询员工姓名，工作，薪资，部门名称及没有员工信息的部门
select * from emp e,dept d where e.deptno(+)=d.deptno

—多表联合查询：
–当需要获取的数据分布在多张中，考虑使用联合查询
–SQL92方式
–SQL99方式

–SQL99多表查询
–注意1：依然可以给表添加别名
–注意2：如果使用on或者usering关键对结果进行筛选，必须使用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 outer join dept d on e.deptno=d.deptno
--右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
–查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
select * from emp e right outer 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(2,‘邯郸’,‘历史闻名古都’);
insert into city values(3,‘洛阳’,‘历史闻名古都’);
insert into city values(4,‘开封’,‘历史闻名古都’);
--将部门表中的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