oracle学习备注

满外联接：Full Outer Join

UNION：并集，所有的内容都查询，重复的显示一次
UNION ALL：并集，所有的内容都显示，包括重复的
INTERSECT：交集：只显示重复的
MINUS：差集：只显示对方没有的（跟顺序是有关系的）

如果子查询未返回任何行，则主查询也不会返回任何结果，即使主查询使用的是不等查询。

TopN查询使用rownum

分页查询：
select * from (select rownum no,e.* from (select * from emp order by sal desc) e where rownum<=5 ) where no>=3;
select * from (select rownum no,e.* from (select * from emp order by sal desc) e) where no>=3 and no<=5;

对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，
所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 另外IN是不对NULL进行处理

随机返回5条记录：Select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5

处理空值排序：select * from emp order by comm desc nulls last(first);

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
over函数
over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：
over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno) 按照部门分区
over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录
over(order by salary rows between 50 perceding and 150 following)前50行，后150行
over（order by salary rows between unbounded preceding and unbounded following）所有行
over（order by salary range between unbounded preceding and unbounded following）所有行

查询跳过表中的偶数行 select ename from (select row_number() over (order by ename) rn,ename from emp) x where mod(rn,2)=1;

得到当前行上一行或者下一行的数据 select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp;得到上一行或者下一行后，变成了字段

查询某用户下所有表：select table_name from all_tables where owner='SCOTT';

查询EMP表中所有字段（列）：select * from all_tab_columns where table_name='EMP';

列出表的索引列 ：select * from sys.all_ind_columns where table_name='EMP';

列出表中约束 ：select * from all_constraints where table_name='EMP'

在oracle中描述数据字典视图 ：select table_name ,comments from dictionary where table_name like '%TABLE%';

Oracle主要数据类型：Char,nchar,varchar2,nvarchar2,number(),date,blob(binary二进制流的大对象),clob（文件大对象）

注意：
1、 由于char是以固定长度的，所以它的速度会比varchar2快得多!但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉
2、 Varchar2一般适用于英文和数字，Nvarchar2适用中文和其他字符，其中N表示Unicode常量，可以解决多语言字符集之间的转换问题
3、 Number(4,2) 指的是整数占2位，小数占2位(99.994可以。99.995不行，因为是四舍五入)
4、 Number默认为38位

约束:
age NUMBER CHECK(age BETWEEN 0 AND 150)
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY (pid) REFERENCES person(pid) ON DELETE CASCADE ;

删除约束：
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk ;

启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk ;
禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk ;

索引
select * from user_indexes 查询现有的索引
select * from user_ind_columns 可获知索引建立在那些字段上

建立索引的优点:
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
索引的缺点:
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

创建索引的原则
创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。
--在select操作占大部分的表上创建索引； --在where子句中出现最频繁的列上创建索引；
--在选择性高的列上创建索引（补充索引选择性，最高是1，eg：primary key）
--复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列……。
--小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

使用索引的原则
--查询结果是所有数据行的5%以下时，使用index查询效果最好；
--where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当sql 语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；
--索引利于select，但对经常insert，delte尤其update的表，会降低效率。
----where 子句中的这个字段，必须是复合索引的第一个字段；
---- where 子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
----应尽量熟悉各种操作符对 Oracle 是否使用索引的影响：以下这些操作会显式（explicitly）地阻止 Oracle 使用索引： is null ; is not null ; not in; !=; like ; numeric_col+0;date_col+0; char_col||' '; to_char; to_number，to_date 等。

索引的存储
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突。使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

管理索引
1）先插入数据后创建索引
向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行数据的时候都要更改索引。这样会大大降低插入数据的速度。
2）设置合理的索引列顺序
3）限制每个表索引的数量
4）删除不必要的索引
5）为每个索引指定表空间
6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎
片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期rebuild索引（rebuild期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，执行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销。

Oracle在系统设置中默认设置了输出不显示，如果要显示的话，输入以下命令： set serveroutput on

eno := &no ;接收输入的值

eno emp.empno%TYPE ;定义变量的时候经常使用的

dept dept %rowtype ; 该变量存放一行的数据

LOOP 循环的语句 ; EXIT WHEN 终止条件 ; 循环条件必须更改 ; END LOOP ;
循环输出1~10。 DECLARE cou NUMBER ; BEGIN -- 必须给一个初始值 cou := 1 ; LOOP DBMS_OUTPUT.put_line('cou = '||cou) ; EXIT WHEN cou>10 ; cou := cou + 1 ; END LOOP ; END ; / 此循环是先执行一次之后再进行判断

while(判断循环的条件) loop 循环的语句 ; 循环条件的改变 ; End loop ;
使用此语句修改上面的程序： DECLARE cou NUMBER ; BEGIN -- 必须给一个初始值 cou := 1 ; WHILE(cou<10) LOOP DBMS_OUTPUT.put_line('cou = '||cou) ; cou := cou + 1 ; END LOOP ; END ; / 此语句，是先判断，之后如果条件满足则执行，与while循环类似。

FOR 变量名称 in 变量的初始值..结束值 LOOP 循环语句 ; END LOOP ; DECLARE cou NUMBER ; BEGIN FOR cou IN 1..10 LOOP DBMS_OUTPUT.put_line('cou = '||cou) ;END LOOP ; END ; /

游标是一种PL/SQL控制结构；可以对SQL语句的处理进行显示控制，便于对表的行数据逐条进行处理。
游标并不是一个数据库对象，只是存留在内存中

操作步骤： • 声明游标 • 打开游标 • 取出结果，此时的结果取出的是一行数据 • 关闭游标

使用for循环操作游标（比较常用） DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp where empno=-1; empInfo emp%ROWTYPE ; cou NUMBER ; BEGIN  OPEN mycur  -- 游标操作使用循环，但是在操作之前必须先将游标打开FOR empInfo IN mycur LOOP cou := mycur%ROWCOUNT ; DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno) ; DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename) ; END LOOP ; END ; /

编写一个游标，输出全部的信息。 DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp ; -- List (EmpPo) empInfo emp%ROWTYPE ; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 OPEN mycur ; -- 使游标向下一行 FETCH mycur INTO empInfo ; -- 判断此行是否有数据被发现 WHILE (mycur%FOUND) LOOP DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ; DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ; -- 修改游标，继续向下 FETCH mycur INTO empInfo ; END LOOP ; END ; /

也可以使用另外一种方式循环游标：LOOP…END LOOP; DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp ; empInfo emp%ROWTYPE ; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 OPEN mycur ; LOOP -- 使游标向下一行 FETCH mycur INTO empInfo ; EXIT WHEN mycur%NOTFOUND ; DBMS_OUTPUT.put_line('雇员编号：'||empInfo.empno) ; DBMS_OUTPUT.put_line('雇员姓名：'||empInfo.ename) ; END LOOP ; END ; /

可以使用ROWCOUNT对游标所操作的行数进行记录。 DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp ; empInfo emp%ROWTYPE ; cou NUMBER ; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 IF mycur%ISOPEN THEN null ; ELSE OPEN mycur ; END IF ; LOOP -- 使游标向下一行 FETCH mycur INTO empInfo ; EXIT WHEN mycur%NOTFOUND ; cou := mycur%ROWCOUNT ; DBMS_OUTPUT.put_line(cou||'雇员编号：'||empInfo.empno) ; DBMS_OUTPUT.put_line(cou||'雇员姓名：'||empInfo.ename) ; END LOOP ;END ; /