oracle 9i 入门（初级教程）-----3

糜3392

　　
Oracle表的管理
1. 规范
       表名称必须以字母开头，不能超过30个字符，不能使用oracle关键字，只能使用 a-z A-Z  $ # 这些字符
2. 数据类型
       char定长字符，最大2000字符，不足后补空格，效率比较高，速度比较快，浪费空间
       varchar2变长字符，最大4000字符，可以节省空间，但可能影响效率
       clob(character large object) 字符型大对象，最大4G
       number范围 10的-38次方到10的38次方，如number（5,2）范围 -999.99~999.99，number（5）范围 -99999~99999
       date包含年月日时分秒
       timestamp精确到毫秒级别
       blob二进制数据，存放图片、声音、视频等，最大4G（但实际中一般不在数据库中存放真实的文件，而是存放路径，如果出于安全等方面的考虑，可以放在数据库中）
3. 对表的操作
       添加字段 alter table tName add (newCol DataType);
       修改字段长度alter tabletName modify （col DataType）;
       修改字段类型/名称alter table tName modify（col DataType）;列中不能有数据
       删除字段alter table tName drop column col;
       修改表名称 rename oldName to newName
4. 向date类型的字段添加值时，默认情况下格式为’09-6月-99’ 表示1999年06月09日，很不方便，我们可以修改日期默认格式alter session set nls_date_format = 'yyyy-MM-dd';
也可以将插入的字段值修改为日期格式 to_date(‘1999-01-02’,’yyyy-MM-dd’)这样就可以正常插入了
5. 插入空值的时候，使用关键字 null,如果插入的是char、varchar2、date等插入时需要加单引号的字段，那么可以使用‘’，表示插入的值时null
6. 删除的操作
       DELETEFROM tName;删除表中的数据，表的结构还在，记录日志，可恢复，速度慢
       DROPTABLE tName; 删除表，包括数据和结构
       TRUNCATETABLE tName 删除表中数据，表结构还在，不记录日志，不可恢复，速度快
7．创建还原点 savepoint p1；
       在没有提交事务的情况下，也就是没有commit；的情况下，可以使用rollback to p1；回滚事务。如果commit；了，那么还原点丢失
8. 显示时间
       settime on; 显示时间
       settiming on;  显示执行查询耗时
  关闭
       settime off;
       settiming off;
9. 查询指定字段的速度比查询所有列（SELECT *）的速度快
10. oracle对于字段名称不区分大小写，但是存放于数据库中的值是区分大小写的
11. number类型和null 进行计算，得到的值总是null，处理是一般采用 nvl（col，0），表示如果col查询到的是null，则用0代替，否则使用原值
12. oracle分页查询
select * from (select x.*,rownum rnfrom (select * from emp e order by e.deptno,e.empno) x where rownum <=10)where rn >=5;
三层嵌套，如果想指定要查询的条件以及排序，请在最内层的查询中指定
13. oracle 优化：把精确查询条件放在最后？？？
       比如 where conditionA and conditionB 如果conditionA 能将查询结果从1W条筛选到1K条，conditionA 能将查询结果从1W条筛选到10条，那么conditionB就应该在最后
（有待验证）
14. 比30号部门所有员工工资都高的员工
       （1）select * from empwhere sal > all(selectsal from emp where deptno = 30);  效率比较低
    （2）select * from empwhere sal > (select max(sal) from emp where deptno = 30);
    any 的用法和all 刚好想反，比如上例（1），如果使用any 相当于（2）使用min
15. 多列子查询
       （1）select * from empwhere (deptno,job) = (select deptno,job from emp where ename= 'SMITH');
       （2）select * from empwhere (deptno,job) in (select deptno,job from emp where ename= 'SMITH');
16. 给列取别名的时候可以加 as ，但是 表，特别是 内嵌视图类型的表（from 关键字之后的自定义表）不可以使用 as （所有除非特别需要，不要使用 as 即可）
17．更加高效的分页查询（使用rowid），先看例子
    select e.ename from (select t2.rd from (selectt1.rd,rownum rn from (select rowid rd from emp order by sal) t1 where rownum<=10) t2 wheret2.rn > 5) t3,emp e where t3.rd = e.rowid;
四层嵌套，是不是很麻烦？这么多嵌套，是为了保证效率，那么这样写真的高效么？

---查询第一页普通写法要比rowid写法要快

---随着翻页次数的增多，普通写法需要回表的记录越来越多，性能下降很快。比如你要看200-250条的记录，这时候普通写法需要回表250条记录，而rowid写法只要回表200-250区间中的这50条记录

--第一层必须都在索引中扫描，不回表
--第二三层找到满足条件的rowid
--最后根据rowid去回表找到记录 
优势：因为取rowid不回表，只在索引中扫描，需要回表的代价很小，不管你翻多少页，需要回表的记录是恒定的。
分析：
（1）       rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行
（2）      rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来
（3）      利用rowid是访问表中一行的最快方式
（4）      一般来说，当表中的行确定后，rowid就不会发生变化， 但当如下情况发生时，rowid将发生改变
a)      对一个表做表空间的移动后
b)      对一个表进行了EXP/IMP后
18.   创建表的时候同时插入数据
Create table tTable （id,name） as select empno,ename from emp;
Id的数据类型和emp.empno一致，并且将emp表的所有记录（字段）插入新表中。可以指定where条件。如果不想插入任何数据，只是保证类型一致，where 1=0
19.      取两个结果集的交集 intersect
20.      数据导入
Insert into aTable （col1,col2,col3） select col11,coll2,coll3 from bTable [where …],要求数据类型是一致的
21.      根据子查询更新表
Update aTable set （col1，col2，col3） = （select coll1，coll2，coll3 from table where 。。。） [where …]