Oracle快速入门(多表查询大全)

Oracle快速入门(基本概念+查询大全)

Oracle快速入门(常用函数大全)

Oracle快速入门(多表查询大全)

一、笛卡尔集
1、笛卡尔积的概念

笛卡尔集:实际上是两张表的乘积
Oracle快速入门(多表查询大全)

其中RxS代表笛卡尔积

2、Oracle当中笛卡尔查询的格式

格式:select * from 表1,表2;

3、多表查询
(1)查询emp表

select * from emp;

Oracle快速入门(多表查询大全)

(2)查询dept表

select * from dept;

Oracle快速入门(多表查询大全)

(3)多表查询

select * from emp,dept;

产生14X4条数据56条数据
Oracle快速入门(多表查询大全)

4、对查询的笛卡尔集进行筛选(去除没有用的数据)

select * from emp,dept where emp.deptno = dept.deptno;

Oracle快速入门(多表查询大全)

二、内连接
1、隐式内连接
(1)等值内连接:

select * from emp,dept where emp.deptno = dept.deptno;

Oracle快速入门(多表查询大全)

(2)不等值内连接:

select * from emp,dept where emp.deptno <> dept.deptno;

Oracle快速入门(多表查询大全)

(3)自连接:自己连接自己

查询员工编号,员工姓名,经理的编号,经理的姓名

在emp表当中有这样的对应关系
Oracle快速入门(多表查询大全)

select e1.empno,e1.ename,e1.mgr,m1.ename from emp e1,emp m1 where e1.mgr = m1.empno;

Oracle快速入门(多表查询大全)

查询员工编号,员工姓名,员工部门名称,经理的编号,经理的姓名
Oracle快速入门(多表查询大全)

select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1,emp m1,dept d1 where e1.mgr = m1.empno and e1.deptno = d1.deptno;

Oracle快速入门(多表查询大全)

查询员工编号,员工姓名,员工部门名称,经理的编号,经理的姓名,经理的部门名称

Oracle快速入门(多表查询大全)

select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1,emp m1,dept d1,dept d2
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno

Oracle快速入门(多表查询大全)

查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称

工资等级表

select * from salgrade;

Oracle快速入门(多表查询大全)
Oracle快速入门(多表查询大全)

select e1.empno,e1.ename,d1.dname,e1.sal,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1,emp m1,dept d1,dept d2,salgrade s1
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal;

Oracle快速入门(多表查询大全)

查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级

select e1.empno,e1.ename,d1.dname,e1.sal,s1.grade,e1.mgr,m1.ename,d2.dname,m1.sal,s2.grade
from emp e1,emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal;

Oracle快速入门(多表查询大全)

查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级,将工资等级数字1,2,3,4,显示为中文的一级,二级,三级…
通用方式和Oracle特有方式

select e1.empno,
e1.ename,
d1.dname,
e1.sal,
case s1.grade
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
when 4 then '四级'
else
'五级'
end "等级",
e1.mgr,
m1.ename,
d2.dname,
m1.sal,
decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') "等级"
from emp e1,emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal;

Oracle快速入门(多表查询大全)

(4)相关练习

查询员工姓名和员工部门所处的位置

select e1.ename,d1.loc
from emp e1,dept d1
where e1.deptno = d1.deptno;

Oracle快速入门(多表查询大全)

2、显示内连接inner join

inner这个关键字可以省略

select * from 表1 inner join 表2 on 链接条件;

(1)查询员工姓名和员工部门所处的位置

select e1.ename,d1.loc from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

Oracle快速入门(多表查询大全)

三、外链接
1、左外连接:left outer join :左表当中所有的记录,如果右表没有对应记录,就显示空
(1)先给EMP表插入一条没有对应DEPT表的数据

insert into emp(empno,ename) values(9527,'HUAAN');

(2)通过左外链接查询

select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;

左表EMP表当中所有的记录都被查询出来了,HUAAN在dept表当中没有对应的部门信息显示为空
Oracle快速入门(多表查询大全)

2、右外连接:right outer join :右表当中的所有记录,如果左表没有对应记录,就显示空

outer关键字可以省略

select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;

dept表当中的数据全部显示出来,OPERATIONS没有对应员工的数据为空
Oracle快速入门(多表查询大全)

3、Oracle当中的特有的外连接(+)实际上是如果没有对应的记录就加上空值
(1)Orace左外连接

将emp和dept表当中所有的数据都显示出来,如果emp表在dept表当中没有对应的数据就将dept表对应的数据值为空

select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);

Oracle快速入门(多表查询大全)

(2)Orace右外连接

select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;

将dept和emp表当中所有的数据都显示出来,如果dept表在emp表当中没有对应的数据就将emp表对应的数据值为空
Oracle快速入门(多表查询大全)

四、子查询:查询语句当中嵌套查询语句;用来解决复杂的查询的语句
1、单行子查询:> >= = <= < <= <> != 单行查询的特点将问题的每一部分分别查询然后组合起来
(1)查询员工最高工资的员工信息

select * from emp where sal = (select max(sal) from emp);

Oracle快速入门(多表查询大全)

(2)查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息

select * from emp
where
sal > (select sal from emp where empno = 7654)
and
job = (select job from emp where empno = 7788);

Oracle快速入门(多表查询大全)

(3)查询每个部门最低工资的员工信息和他所在的部门信息

将该问题拆分开了
1)先要知道每个部门的最低工资:分组统计
先将上方加的emp表当中空值删除

delete from emp where empno = 9527;

分组统计dept表当中的最低工资

select deptno,min(sal) minsal from emp group by deptno;

Oracle快速入门(多表查询大全)
2)员工工资等于他所处部门的最低工资
Oracle快速入门(多表查询大全)

select * from
emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal = t1.minsal;

Oracle快速入门(多表查询大全)
3)查询部门相关信息(全部语句的实现)
Oracle快速入门(多表查询大全)

select * from
emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1,
dept d1
where e1.deptno = t1.deptno
and e1.sal = t1.minsal
and e1.deptno = d1.deptno;

Oracle快速入门(多表查询大全)

2、多行子查询:
(1)查询领导的信息

select * from emp where empno not in(select mgr from emp);

Oracle快速入门(多表查询大全)

(2)查询不是领导的信息

注意在上面查询领导信息MGR的时候有空值,空值不可以直接not in ,首先需要将空值过滤了

通常情况下,数据库当中不要出现null 最好的做法加上not null非空约束

null并不代表不占空间,char(100) null 100字符

select * from emp where empno not in(select mgr from emp where mgr is not null);

Oracle快速入门(多表查询大全)

五、exists(查询语句):存在的意思

exists当做布尔值来处理,

当查询与有结果的时候,就是返回true

否则返回的就是fasle

数据量比较大的时候是非常高效的

1、查询语句

select * from emp where exists(select * from emp where deptno = 1234567);

其中exists(select * from emp where deptno = 1234567)返回的是false,所以没有查询出任何结果
Oracle快速入门(多表查询大全)

select * from emp where exists(select * from emp where deptno = 20);

其中exists(select * from emp where deptno = 20)返回的结果是true,所以查询出来的是所有的数据
Oracle快速入门(多表查询大全)

2、查询有员工的部门的信息

select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);

以下是exists的原理图

Oracle快速入门(多表查询大全)
查询dept 表当中与e1表当中对应的deptno,的部门信息
Oracle快速入门(多表查询大全)

六、rownum:伪列

行号,每查询出记录之后,会会添加一个行号

rownum:伪列,系统自动生成的一列,用来表示行号
rownum:oracle当中特有的用来表示行号的,默认值或者起始值是1 ,在查询出结果之后,在添加1
1、相关查询
(1)查询大于2是所有记录

select rownum,e1.* from emp e1 where rownum > 2;//当查询第一条记录的时候rownum为1不满足rownum > 2循环终止没有查询到任何结果

没有记录

Oracle快速入门(多表查询大全)

(2)查询rownum大于等于1的所有记录

select rownum,e1.* from emp e1 where rownum >= 1;

执行过程:查询对应的emp表现查询出一条记录并显示等到对应的当前列的行号判断是否大于等于1,然后rownum自增1

Oracle快速入门(多表查询大全)

(3)查询rownum<6的所有记录

rownum一般情况下不做大余号判断,一般都是做小于号判断

select rownum,e1.* from emp e1 where rownum < 6;

Oracle快速入门(多表查询大全)

(4)rownum排序,安装工资排序

SQL执行顺序:rownum
from .. where .. group by .. having .. select ..rownum.. order by

select rownum,e1.* from emp e1 order by sal;

Oracle快速入门(多表查询大全)

Oracle快速入门(多表查询大全)

2、找到员工表当中工资最高的前三名(降序排序,只显示三条)

Oracle快速入门(多表查询大全)

通过子查询的方式先通过排序的方式倒叙查询出工资,然后在此表的基础上对其进行生成行数,限定条件查询出前三行

select rownum,t1.* from (select e1.* from emp e1 order by sal desc) t1 where rownum < 4;

Oracle快速入门(多表查询大全)

3、找到员工表中薪水大于部门平局薪水的员工
(1)分组统计所有部门平均薪水

select deptno,avg(sal) from emp group by deptno;

Oracle快速入门(多表查询大全)

Oracle快速入门(多表查询大全)

(2)员工工资 > 大于本部门平均工资(实现代码)

上面通过分组将每组员工的平局工资计算出来

emp表与分组后的表取笛卡尔集,通过where将对应符合条件的结果筛选出来

select * from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;

Oracle快速入门(多表查询大全)

(3)上面的算法也有以下方式的实现

关联子查询,非关联子查询,上面的都是非关联子查询,下面的这个是关联子查询

select * from emp e
where sal > (select avg(sal) from emp e2 group by deptno having e.deptno = e2.deptno);

Oracle快速入门(多表查询大全)

4、统计每年入职员工的个数
(1)分组统计不同年份的人

Oracle快速入门(多表查询大全)

(2)将1987竖起来

判断当前行是否是1987如果是1987那么,设置为cc,并且取别名为1987

select case yy when '1987' then cc end "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

Oracle快速入门(多表查询大全)

(3)去除行记录当中的空值

-- 去除行记录中的空值
select sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

(4)统计员工的总数

select sum(cc) "TOTAL"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

Oracle快速入门(多表查询大全)

(5)将1987和TOTAL和并在一起

select
sum(cc) "TOTAL",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

Oracle快速入门(多表查询大全)

(6)显示所有年份

-- 显示所有年份的结果
select
sum(cc) "TOTAL",
sum(case yy when '1980' then cc end) "1980",
sum(case yy when '1981' then cc end) "1981",
sum(case yy when '1982' then cc end) "1982",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

Oracle快速入门(多表查询大全)

5、rownum:分页查询
(1)查询第六条到第十条 记录(在oracle当中只能使用子查询来做分页查询)

子查询当中先通过rownum 统计出带行号的表,然后在此基础上查询筛选出6到10行的内容

select * from(select rownum hanghao,emp.* from emp) tt where tt.hanghao between 6 and 10;

Oracle快速入门(多表查询大全)

七、rowid:伪列(每行记录所存放的真实物理地址)

相关练习

1、去除表当中的重复记录
(1)创建表插入一些数据

insert into p values('小兰');

Oracle快速入门(多表查询大全)

(2)使用distinct 去除重复数据

select distinct * from p;

Oracle快速入门(多表查询大全)
上面这样只是在查询的时候显示出不重复的语句

(3)删除表表当中重复的数据

Oracle快速入门(多表查询大全)

delete from p p1
where p1.rowid > (select min(rowid) from p p2 where p1.name = p2.name);

Oracle快速入门(多表查询大全)

八、集合运算:union union all
1、并集:将两个查询结果进行合并

Oracle快速入门(多表查询大全)

所有的查询结果可能不是来自同一张表,
基本信息,详细信息
emp

(1)查询工资大于1500或者29号下部门的员工

select * from emp where sal > 1500 or deptno = 20;

(2)使用集合的方式查询

union:去除重复的并且排序

select * from emp where sal > 1500
union
select * from emp where deptno = 20;

Oracle快速入门(多表查询大全)

union all:不会去除重复的

select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

Oracle快速入门(多表查询大全)
Oracle快速入门(多表查询大全)

2、差集运算:两个结果相减

Oracle快速入门(多表查询大全)
查询1981年入职的普通员工(不包括总裁和经理)(差集)

--1981年入职的员工
select * from emp where to_char(hiredate,'yyyy') = '1981'
minus
-- 总裁和经理
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';

Oracle快速入门(多表查询大全)

3、集合运算当中的注意事项
(1)列的类型一致

这样查询是可以成功的因为两张表的列显示顺序相同

select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;

两张表列的显示顺序不同时就会报错

select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;

Oracle快速入门(多表查询大全)

(2)两种表列的数量不匹配也会报错

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;

结果类的数量不正确
Oracle快速入门(多表查询大全)

(3)两种表列的数量不匹配,如果不足,用空值填充,或者和上面表的列类型相同的数据

如果数据的确来自于两张表,数据数量不匹配,设置其中一个为null

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;

Oracle快速入门(多表查询大全)

用数值填充

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,10 from emp where deptno = 20;

Oracle快速入门(多表查询大全)

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

Oracle快速入门(多表查询大全)

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » Oracle快速入门(多表查询大全)
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏