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

 

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 table tName 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. 删除的操作

       DELETE FROM tName; 删除表中的数据，表的结构还在，记录日志，可恢复，速度慢

       DROP TABLE tName; 删除表，包括数据和结构

       TRUNCATE TABLE tName 删除表中数据，表结构还在，不记录日志，不可恢复，速度快

7 ． 创建还原点 savepoint p1 ；

       在没有提交事务的情况下，也就是没有 commit ；的情况下，可以使用 rollback to p1 ；回滚事务。如果 commit ；了，那么还原点丢失

8. 显示时间

       set time on; 显示时间

       set timing on;  显示执行查询耗时

  关闭

       set time off;

       set timing off;

9. 查询指定字段的速度比查询所有列（ SELECT * ）的速度快

10. oracle 对于字段名称不区分大小写，但是存放于数据库中的值是区分大小写的

11. number 类型和 null 进行计算，得到的值总是 null ，处理是一般采用 nvl （ col ， 0 ），表示如果 col 查询到的是 null ，则用 0 代替，否则使用原值

12. oracle 分页查询

select * from (select x.*,rownum rn from (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 emp where sal > all (select sal from emp where deptno = 30);   效率比较低

    （2 ） select * from emp where sal > (select max(sal) from emp where deptno = 30);

    any 的用法和all 刚好想反，比如上例（1 ），如果使用any 相当于（2 ）使用min

15. 多列子查询

       （ 1 ） select * from emp where (deptno,job) = (select deptno,job from emp where ename= 'SMITH');

       （ 2 ） select * from emp where (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 ( select t1.rd,rownum rn from ( select rowid rd from emp order by sal ) t1 where rownum <=10) t2 where t2.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 …]