Oracle学习三

最新推荐文章于 2023-11-07 19:00:36 发布

原创最新推荐文章于 2023-11-07 19:00:36 发布 · 338 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

5 篇文章

订阅专栏

该博客围绕Oracle学习展开，介绍了多表查询、外连接、子查询、分页查询和集合运算等内容。使用Oracle下scott用户的四张表进行操作，包含多个查询示例，如查询员工与领导信息、各部门最低工资等，还涉及练习及分页公式总结。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle学习三

有关连接查询和最查询的解释和练习请参考：mysql的连接查询

使用的是oracle下的scott用户下的四张表

select * from dept;
select * from emp;
select * from salgrade;
select * from bouns;

多表查询

1、查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

主要注意的地方：员工和领导不一定在一个部门即（员工表.deptno 不一定等于领导表.deptno ）
在这里插入图片描述
通过上表分析得知：

select e1.empno,e1.ename,d1.deptno,d1.dname,d1.loc,s1.grade,e1.mgr,e2.ename,e2.deptno,d2.dname,s2.grade
from dept d1,emp e1,salgrade s1,emp e2,dept d2,salgrade s2  
where d1.deptno = e1.deptno and e1.sal between s1.losal and s1.hisal and e1.mgr = e2.empno and 
      e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal
-- ===========================六表联查===========================================
      select e1.empno,e1.ename,d1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "slaLevel",e1.mgr,e2.ename,e2.deptno,d2.dname,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "slaLevel"
from dept d1,emp e1,salgrade s1,emp e2,dept d2,salgrade s2  
where d1.deptno = e1.deptno and e1.sal between s1.losal and s1.hisal and e1.mgr = e2.empno and 
      e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal

2、查询员工编号,员工姓名,领导编号,领导姓名

-- 连接查询
select e1.empno,e1.ename,e1.mgr,e2.empno
from emp e1,emp e2
where e1.mgr = e2.empno;

-- 内连接查询
select e1.empno,e1.ename,e1.mgr,e2.empno
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

外连接

1、查询员工编号，姓名，领导编号，领导姓名，包括没领导的

-- left join on方式（主要查询的主表是员工表）左外连接主表放到from后面
select e1.empno,e1.ename,e1.mgr,e2.empno
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

----Oracle的(+)方式(添加+号的一方以不添加+的一方为准)（即不添加+的一方为主表）
select e1.empno,e1.ename,e1.mgr,e2.empno
from emp e1,emp e2
where e1.mgr=e2.empno(+)

2、查询出所有部门信息(包括没员工的部门)及部门下的员工信息

--left join on方式一
select d.*,e.*
from dept d
left join emp e
on d.deptno=e.deptno
--Oracle的(+)方式二
select d.*,e.*
from dept d,emp e 
where d.deptno=e.deptno(+)

子查询

1、查询比雇员7654工资高，同时从事和7788的工作一样的员工

--(1)首先查询雇员7654的工资
    select  e1.sal 
    from emp e1
    where e1.empno = 7654

--(2)查询7788从事的工作
    select  e2.job
    from emp e2
    where e2.empno = 7788

--(3)sal>(1)且job=(2)
    select *
    from emp
    where sal>(
          select  e1.sal 
          from emp e1
          where e1.empno = 7654
    ) and job=(
            select  e2.job
            from emp e2
            where e2.empno = 7788
    );

2、查询每个部门最低工资及最低工资的部门名称和雇员名称

(1)根据部门分组查询最低工资

(2)查询(1)中的、员工表和部门表三个表，要满足三个条件：

1.（1）表中的deptno和员工表的deptno相等

2.（1）表中的最低工资和员工表中的最低工资相等

3.员工表中的deptno和部门中的deptno相等

-- (1)查询每个部门的最低工资
select deptno,min(sal)
from emp
group by deptno

-- (2)最低工资的部门名称和雇员名称(重点)
select e1.minsal,d.dname,e.ename
from (
  select deptno,min(sal) minsal
  from emp
  group by deptno
) e1,emp e,dept d
where e1.deptno = e.deptno and e1.deptno = d.deptno and e1.minsal = e.sal;

练习

1、找到员工表中工资最高的前三名

每一张表中都会有一个隐藏的字段rownum

但是rownum只支持小于号的比较

select 列名1 ...列名n from
(
select 列名1 ...列名n 
   from 表名 order by 列名1
)
where rownum <=N(抽出记录数)
order by rownum asc
-- ========================
select * from (
  select sal
  from emp
  order by sal desc
)where rownum < 4

2、找到员工表中薪水大于本部门平均工资的所有员工

--(1)查询本部门的平均工资（按照部门进行分组）
select deptno,avg(sal) avgsal
from emp
group by deptno

--(2)总查询
select e.*
from (
  select deptno,avg(sal) avgsal
  from emp
  group by deptno
) temp,emp e
--条件就是本部门员工部门和(1)中的部门是否相等（e.deptno = temp.deptno）&& 薪水>平均薪水
where e.sal>temp.avgsal and e.deptno = temp.deptno

3、统计每年入职的员工个数(考察的是行列转换)

通过to_char将日期中的年份提出

decode的使用

-- 每年的总数
  select count(*) "hcount",to_char(hiredate,'yyyy') "hdate"
  from emp
  group by to_char(hiredate,'yyyy') 
  
-- 总共入职的总数
select decode(hdate,'1980',hcount),decode(hdate,'1981',hcount),
       decode(hdate,'1982',hcount),decode(hdate,'1987',hcount)
from (
  select count(*) hcount,to_char(hiredate,'yyyy') hdate
  from emp
  group by to_char(hiredate,'yyyy') 
) temp;

-- 再将每一列进行聚合（聚合函数sum、min、max、avg）
select sum(hcount) "total" ,sum(decode(hdate,'1980',hcount)) "1980",min(decode(hdate,'1981',hcount)) "1981",
       max(decode(hdate,'1982',hcount)) "1982",avg(decode(hdate,'1987',hcount)) "1987"
from (
  select count(*) hcount,to_char(hiredate,'yyyy') hdate
  from emp
  group by to_char(hiredate,'yyyy') 
) temp;

分页查询

1、查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录

--1.查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录
select rownum,temp.*
from (
  select *
  from emp 
  order by sal desc
) temp
where rownum <=3
--2.rownum不能做大于查询(上面的语句还没有出现rownum)
select * 
from (
  select rownum r,temp.*
  from (
    select *
    from emp 
    order by sal desc
  ) temp
) page
where page.r between 4 and 6

总结得出Oracel的分页公式：

-- oracle分页公式：
-- pageNo = 第几页
-- pageSize = 每页多少条

select * 
from (
  select rownum r,temp.*
  from (
    select *
    from 表名 
    order by 列名 desc
  ) temp
) page
where page.r between (pageNo-1)*pageSize+1 and pageNo*pageSize

集合运算

1、查询工资大于1200并且job是SALESMAN(intersect) and（交集）

select * from emp where sal > 1200 
intersect
select * from emp where job = 'SALESMAN';

2、查询工资大于1200或者job是SALESMAN(union) or （并集）

--union all(加上all不去重)

select * from emp where sal > 1200 
union 
select * from emp where job = 'SALESMAN';

3、求工资大约1200和job是SALESMAN的差集(minus)有主从表之分（差集）
```
select * from emp where sal > 1200 
minus
select * from emp where job = 'SALESMAN';
```
exists / not exists

4、查询出有员工的部门

/*
select ... where exists(查询语句)
exists：当查询结果不为null，返回true
	当查询结果为null，返回false

*/
-- 查询部门
select d1.deptno
from  dept d1
where exists(
--查询员工
  select e.*
  from emp e 
  where e.deptno = d1.deptno
)

5、查询出没有员工的部门

-- 查询部门
select d1.deptno
from  dept d1
where not exists(
--查询员工
  select e.*
  from emp e 
  where e.deptno = d1.deptno
)

### 全部教程是本人自己在学习过程中的总结和练习，都很基础写下来的目的主要是方便自己和他人的复习理解，也消除了笔记写下找不到的缺点。如有需要什么笔记或者文档可以留言。