Oracle子查询，集合运算,数据处理,事务,RowNum实例讲解，rownum实例讲解
子查询
解释：子查询就是在在一条查询语句中嵌套另外一个查询语句，有主查询他的条件是子查询。
案例1：
查出比A员工的工资高的员工信息，需要先查A员工工资，再查比他高的员工信息。这里就是要分两步进行。
子查询就是为了可以一步解决。
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT');
注意的问题：
1. 合理的书写风格
2. 括号
3. 可以在主查询的where select having from后面使用子查询
Where:子查询当作条件
Select：一般是在函数里，只允许是单号子查询
Having:过滤条件（Having后面只允许跟着函数）
From:当作是另外一张表
4. 不可以在group by后面使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用即可
7. 一般不在子查询排序；但top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10. 子查询中的null
SQL优化：
1.查询语句要写字段。
2.where语句是从右往左运行得。
3. having先分组在过滤。where先过滤在分组。优先选择where
4.在多表查询和子查询中选，选择多表查询。子查询都会转换成多表查询
单行比较符
只返回一行使用单行比较操作符| 操 作 符 | 含 义 |
|---|---|
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> | Not equal to |
注意：单行函数的结果集，不允许多行，否则报错如下：
ERROR at line 4: ORA-01427:single - row subquery returns more than one row
多行子查询
返回多行使用多行比较操作符| 操作符 | 含义 |
| IN | 等于列表中的任何一个 |
| ANY | 和子查询返回的任意一个值比较 |
| ALL | 和子查询返回的所有值比较 |
IN列表中得任何一个
--in 在集合中 --查询部门名称是SALES和ACCOUNTING的员工 select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
ANY和任意一个值比大，那么就是比这个部门中的最小值
--any:和集合中任意一个值比较 --查询工资比30号部门任意一个员工高的员工信息 select * from emp where sal > any (select sal from emp where deptno=30);
select * from emp where sal > (select min(sal) from emp where deptno=30)
All相当于是取值最大
--all: 和集合中的所有值比较 --查询工资比30号部门所有员工高的员工信息 select * from emp where sal > all (select sal from emp where deptno=30);
select * from emp where sal > (select max(sal) from emp where deptno=30)
集合运算
Union和Union All的区别
Union：取值，在去重。
Union All：取值，不去重。
SQL优化
1.查询语句要写字段。
2.where语句是从右往左运行得。
3. having先分组在过滤。where先过滤在分组。优先选择where
4.:在多表查询和子查询中选，选择多表查询。子查询都会转换成多表查询
5.尽量使用union all
union = union all+ distinct
Group by语句增强
6. SQL原则尽量不要使用集合运算
关于集合运算
1. 参与运算的各个集合必须列数相同且类型一致
2. 采用第一个集合作为最后的表头
3. order by永远在最后
集合运算列数要相同，如果列数不相等，加个null。
select deptno,job,sum(sal) from emp group bydeptno,job union selectdeptno,to_char(null),sum(sal) from emp group by deptno union selectto_number(null),to_char(null),sum(sal) from emp;
Sql执行时间开关DOS命令
Set time on
set timing off
数据处理
SQL的类型
1. DML（data manipulation language 数据操作语言）:insert update delete select
有的时候查询语句单独划分为DQL
2. DDL(data definition language 数据定义语言):
create table,alter table,droptable,truncate table（清空表）
view（视图）,sequence（序列）,index（索引）,synonym(同义词)
3. DCL(data control language 数据控制语言）：
grant(授权) revoke（撤销权限）
添加例子：
地址符可以是添加，字段或表或条件。（提交才能看到数据）
Insert intoemp(empno,ename) values(&empno,&ename);
利用查询语句，一次添加多条数据。
Insert intoemp(empno,ename) Select empno,enamefrom emp;
创建表
默认也会添加数据，如果条件语句后面是false，那么数据就不会添加。
--一次插入多条数据 CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE 1=2;
添加表数据
可以单独添加指定列，但是记得添加得列等于查询得列
没有值得列为null。
海量插入数据
1. 数据泵（PLSQL程序：dbms_datapump）
2. SQL*Loader工具
3. 外部表
更新表数据
1.更新的数据要是单行结果集。
2.要考虑到结果集返回为null的情况。
删除语句
delete和truncate的区别：
1. delete逐条删除；truncate先摧毁表 再重建
2. (*)delete是DML（可以回滚）truncate是DDL（不可以回滚）
3. delete不会释放空间 truncate会
4. delete可以闪回（flashback） truncate不可以
5. delete会产生碎片 truncate不会
Drop,delete和truncate区别。
1.drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
drop table xx
2. truncate先摧毁表 再重建
truncate table xx
3. delete只会删除数据
delete table xx
碎片
我们的电脑在删除文件的时候，会留下系统碎片，他相当于一块空白的部分。在系统的附件里，可以找到碎片整理工具，就是来清楚碎片的。
那么在表中也有碎片，我们需要使用truncate删除重建表，然后在导入表数据，以此来清理碎片。
事务
Oracle中事务的标志
1. 起始标志:事务中第一条DML语句
2. 结束标志：提交显式 commit
隐式 正常退出exit，DDL，DCL
回滚 显式 rollback
隐式 非正常退出，掉电，宕机
在命令行中事务的命令：
savepoint a;--保存事务点a。
rollback to savepoint a;--回滚事务到事务点a。
commit；--提交
数据库的隔离级别
对于同时运行的多个事务，当这些事务访问的数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
脏读：一个事务读到了另一个事务未提交的数据
不可重复读：一个事务读到了另一个事务已经提交的update数据,导致多次查询结果不一致。
虚读：一个事务读到了另一个事务已经提交的insert数据,导致多次查询结构不一致。
数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会互相影响，避免各种并发问题。
一个事务与其他事务隔离的程度成为隔离级别。数据库规定了多种事务隔离级别，不同隔离界别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。
Oracle明确支持ANSI/ISO SQL92中定义的serializable和read committed两种事务隔离级别。同时，Oracle还提供了自己独有的事务隔离级别：read only。
所以，可以说Oracle共支持3种事务隔离级别：
1. serializable
2. read committed
3. read only
Oracle默认的隔离级别是read committed。
查看数据库隔离级别的方法：
SELECT * FROM dual FOR UPDATE;
SELECT s.sid, s.serial#,
CASE BITAND(t.flag, POWER(2, 28))
WHEN 0 THEN 'READ COMMITTED'
ELSE 'SERIALIZABLE'
END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
修改数据库隔离级别的方法：
SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
总结
通过学习，认识了如何使用DML语句改变数据和事务的控制，主要关键词如下：
| 语句 | 功能 |
|---|---|
| INSERT | 插入 |
| UPDATE | 修正 |
| DELETE | 删除 |
| COMMIT | 提交 |
| SAVEPOINT | 保存点 |
| ROLLBACK | 回滚 |
问题
第一题：
Rownum
关于rownum
1. rownum永远按照默认的顺序生成。
2. rownum只能使用< <=;不能使用> >=。
3.rownum永远从1开始。
Rownum只能使用小于，不能使用大于，因为他是二维表，在查询的时候，是在查询每一行数据的时候给添加rownum的行号，并且第一行为1，如果你是大于2，那么他会一只回去对比大于1的行号，找不到就删除，继续对比，但是这里删除了，行号就一只为1，进入死循环。
如何使用rownum做分页呢？
把有rownum的表作为子表，查询这张表。然后主表根据子表的rownum来对进行条件筛选，可以对rownum大于或者小于
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;
表分类
标准表，索引表，临时表
临时表：
1. 手动: create global temporary table *****
2. 自动：排序
特点：当事务或者会话结束的时候，表中自动删除
临时表：以下是基于会话的表，一旦断开链接，或关闭会话，数据就消失。
create global temporary table temptest1 (tid number,tname varchar2(20)) on commit preserve rows;
临时表：以下是基于事务的表，一旦提交事务那么，数据就会删除。
create global temporary table temptest1 (tidnumber,tname varchar2(20)) on commit delete rows;
临时表的应有场景，就是在统计报表的时候，有的时候，我们需要的，只是一张临时表，来计算数据，算到最后结果出来了，数据就可以删除了。我们要的只是结果，不是数据过程。
下面这个图左是标准的表，我们给他rownum一下。再通过 order by排序，上面有说过排序的表是临时表，那么右边的表是临时表。在给有rownum的表排序的时候，我们看到的是临时表也就是右边的表。他的顺序是打乱的。但是真实的表是左边的表，他的顺序是一直都在的。只是排序了之后到临时表，顺序变了而已。
第二题总结：
在使用子查询时，可以在where条件里，对主表的列，进行引用。
这里主要是涉及到一个查询语句的运行顺序，先运行from，在运行where，最后运行select。因此在一般的查询语句中，where读不到select的赋值。而这里from先运行，先赋值了，所以可以读到。
--相关子查询：将主查询中的值 作为参数传递给子查询 select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno);
第三题总结：
主要是在多组函数里，比如avg里使用条件语句，比如case when then else
行转列：
select wm_concat(name) name from test;
在......之间
BETWEEN value1 AND value2