oracle实训笔记整理（three Day)

oracle新增子查询关键字

• =any：功能和in一致

• (>any)：查询比子查询中最小值大的所有结果

• <any：查询比子查询中最大值小的所有结果

• =all：不存在

• <>all：查询除了子查询结果中得所有数据

• (>all)：查询比子查询中最大值大的所有数据

• <all：查询比子查询中最小值小的所有数据

查询不等于所有部门经理最低工资的所有雇员信息（其他用法类似）

select * from emp where sal <> all(
       select min(sal) from emp where job = 'MANAGER' group by deptno)

空数据判断

exist用于判断子查询中是否有数据返回，有的话就返回true，没有就返回false。
not exists正好相反，如果不存在就返回真，否则返回假。

select * from emp where exists
       (select * from emp where empno =8818)

having中的子查询

select deptno 部门编号,count(*) 人数,round(avg(sal),2) 平均工资 from emp 
       group by deptno having avg(sal) > 
             (select avg(sal) from emp)

查询平均工资最高的部门名称和平均工资

select d.dname 部门名称, round(avg(e.sal), 2) 平均工资
  from emp e join dept d using(deptno)
 group by d.dname
having avg(sal) = (select max(avg(sal)) from emp group by deptno)

将查询结果作为一张新表，并起个别名使用

查询‘SALES’部门的最高工资、最低工资和员工信息

select empno, ename, sal, comm, job, hiredate, MaxSal 最高工资, MinSal 最低工资
  from emp,
       (select max(sal) MaxSal, min(sal) MinSal,emp.deptno dno
          from emp, dept d
         where emp.deptno = d.deptno
           and d.dname = 'SALES'
         group by emp.deptno) newSal where newSal.dno = deptno

查询工资多于ALLEN或者CLARK的员工的编号、姓名、基本工资、部门名称、领导姓名和部门人数

select empno          编号,
       ename          姓名,
       sal            基本工资,
       bumen.deptname 部门名称,
       leader.lname   领导姓名,
       counts         部门人数
  from emp e,
       (select d.dname deptname, deptno dno, count(*) counts
          from emp e
          join dept d
         using (deptno)
         group by dname, deptno) bumen,
       (select m.ename lname, e.empno eno
          from emp e, emp m
         where e.mgr = m.empno) leader
 where e.deptno = bumen.dno
   and leader.eno = e.empno
   and e.sal > any (select sal
          from emp
         where ename = 'ALLEN'
            or ename = 'CLARK')

两种子查询

嵌套子查询：子查询可以单独运行，并把查询结果作为外表的条件
相关子查询：子查询的条件是需要用到外表的列，不能单独运行

select * from emp e where sal =
       (select max(sal) from emp where emp.deptno = e.deptno group by deptno)

with as 的用法（创建临时表）

这里创建了两个临时表，分别查询部门信息和部门的平均工资以及部门人数，最后把两个临时表连接

with d as (select deptno ,dname 名称,loc 位置 from dept),
     e as (select deptno,avg(sal) 平均工资,count(*) 部门人数 from emp group by deptno)
select * from d,e where d.deptno = e.deptno

OVER(PARTITION BY)分析函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行

over后的写法over：（partition by deptno）按照部门分区

select empno, ename,job,deptno,
       round(avg(sal) over(partition by job), 2) 平均工资,
       count(deptno) over(partition by deptno) 部门人数 from emp;

rank()over()：统计排名

select empno, ename,job,deptno,
       rank()over(partition by job order by sal desc) 工资排名,
       count(deptno) over(partition by deptno) 部门人数 from emp;

row_number()over(partition by column1 order by column2)
表示根据column1分组，在分组内部根据column2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

select ename,deptno,job,
      row_number() over(partition by deptno order by job) id from emp

行列转换

Select * From 表名-- 这个表应该是下面聚合之后的表，而不是原理的行表
PIVOT(
SUM(‘要合并的列1’),MAX(‘要合并的列2’),… --操作的列FOR 将值转换成列的列名 IN
(列值1 as 别名1，
列值2 as 别名2,…）
)

查询每个部门的各个职位的工资总和

select * from 
    (select deptno, job, sal from emp) --这个查询结果作为一个表
pivot(
        sum(sal) for job in
               ('CLERK' as cle_sal,
              'SALESMAN' as sale_sal,
              'MANAGER' as man_sal,
              'ANALYST' as ana_sal,
              'PRESIDENT' as pre_sal))

查询结果：

批量插入数据

根据查询的结构作为新表的结构创建，并把查询结果批量插入到新表中。

语法：create table 新表名 as select 列 from 表名

只取表结构，不要数据
create table aa as select empno,ename,job,sal,deptno from emp where 1 = 2;

表结构已存在，需备份指定表中的数据

语法：insert into 表名（列）select 列 from 表名 where 条件

[外链图片转存失败(img-6s0INbB3-1562230342179)(en-resource://database/404:1)]

事务

oracle默认开启事务，关闭了自动提交
当插入或更新数据时，需要手动commit才能完成。
也可以使用rollback回滚所有或回滚到某个保存点。

insert into aa values(11,'哈哈','las',112,10);
savepoint a1;
insert into aa values(22,'嘿嘿','1ed',121,20);

commit
rollback to a1

当两个人同时操作一个数据时，当前的操作如果没有提交，另一个人是不能看到操作的。