oracle语句速查(1)

表的定义，修改，删除操作在ORACLE数据库系统中，表是数据库的基本对象，与桌面数据库中的文件类似，我们可以把所有的基本实体都看成为表，不管应用中的表有多复杂，都可以使用（拆成）一个或多个表来表示。用以存放实体的数据。下面针对建表所需要的知识作简单的介绍。

    1建立表结构

    建立表结构是每个应用系统都必须进行的工作。由于建立表结构是一项统一规划和统一设计的工作。应该是由总设计师根据用户的具体应用需要来定。表的设计是否合理关系到应用系统将来的成败与性能问题。因此，任何担当总设计师角色的人都不要轻视这项工作。

    1.1 建立表结构命令由于创建表的命令非常长，这里仅给出一些主要的部分，详细的请参考《ORACLE8i SQL REFERENCE》 。

    CREATE TABLE命令简要语法如下：

    CREATE TABLE [USER.] table_name

    ( { COLUMN1 DATATYPE

    [DEFAULT EXPN] [COLUMN_CONSTRAINT] |TABLE_CONSTRAINT }

    [, { COLUMN1 DATATYPE

    [DEFAULT EXPN] [COLUMN_CONSTRAINT] |

    TABLE_CONSTRAINT }] ... ) [CLUSTER CLUSTER (COLUMN1 [,COLUMN2] ...) ]

    [PCTFREE N]

    [PCTUSED N]

    [INITRANS N]

    [MAXTRANS N]

    [STORAGE N]

    [TABLESPACE TABLESPACE]

    [ ENABLE | DISABLE]

    [ AS QUERY]

    其中：

    schema包括基表的模式（缺省：当前用户的帐号）

    table_name表名

    column    列名（字段名），ORACLE7最多254列，ORACLE8可达1000个列。

    datatype列数据类型

    DEFAULT当前列的缺省值（常数）

    Column constraint列约束

    Table_constraint表约束

    PCTFREE用于更新（update）的空间百分比（1-99）

    0表示在插入时完全填满数据块，缺省为10 PCTUSED   为表的每个数据块保留的可用空间的最小百分比. 取值1-99，缺省为40。

    PCTFREE和PCTUSED的组合决定了将插入的数据放入已存在的数据块还是放入一个新的块中。

    INITRANS 指定一个在每一个数据块中分配的事务入口的初始数1-255，缺省为1，每一个更新块的事务都需要在块中有一个事务入口（大小依OS），一般不需要指此参。

    MAXTRANS   指定用于更新分配给表的数据块的并发事务的最大数，1- 255，用户一般不应改此参。

    TABLESPACE   表空间。如果缺省则表建在用户缺省的表空间（如果建立用户不指定表空间则该用户的缺省表空间为system）。

    STORAGE存储分配参数

    INITIAL integer初始大小

    NEXT integer下一次的大小

    MINEXTENTS integer最小分配次数

    MAXEXTENTS integer最大分配次数

    PCTINCREASE integer增长百分比（>=0）

    ENABLE激活完整性约束

    DISABLE取消完整性约束

    As subquery      建表中查出数据给新表，此语句如果使用，则表的数据类型不需指定，而是继承原表的类型。

    FREELIST   GROUP  在并行服务器中指定表或分类、索引组的列表数目。

    FREEUST     在并行服务器中指定表、簇、索引的列表数。

    提示1：

    一般情况下，如果表含有long字段，这样势必需大量的空间，系统会在每次插入新记录时，经常分配空间给表，不久就会出现：

    "ORA-01547:Failed to allocate extent of size xxxxx in

    tablespace 'xxxx' "

    此种情况如果表空间还剩较多的连续空间的话。则可能是该表分配的空间次数已达最大值。为了对该表能插入新数据，需对该表的存储参数作修改，比如：

    SQL>alter table xxx storage(MAXEXTENTS 999 )；

    提示2:建议不要对表结构或索引使用 pctincrease大于0的参数以避免将来在运行中产生空间超支问题.提示3:建立表结构最重要的部分是存储参数（STORAGE）的说明。设置者要特别重视存储参数的估计，设置合理的大小。详细见〈Oracle8i/9i 初级数据库管理〉1.2 建立表结构例子例1：在SCOTT模式下建立表emp,并指定表空间和存储参数：Create   table   scott.emp

    (

    Empno   number(5)   primary   key, Ename   varchar2(15)   not   null, Job     varchar2(10), Mgr     number(5), Hiredate   date   default   sysdate, Sal     number(7,2)   CHECK(sal>100), Comm    number(3)   default 0.0 ,

    Dept number constraint

    dept_fkey     References   scott.dept

    )

    Tablespace users

    PCTFREE 10

    PCTUSED 70

    STORAGE

    (

    INITIAL 50K

    NEXT 50k

    MAXEXTENTS 10

    )；

    例2：在建立表过程中对有限制的列使用NOT NULL:

    CREATE TABLE CHECKUP_HISTORY

    (CHECKUP_NO NUMBER(10,0) NOT NULL, ID_NO NUMBER(10,0), CHECKUP_TYPE VARCHAR2(30), CHECKUP_DATE DATE,

    DOCTOR_NAME VARCHAR2(50));

    本例除了要求CHECKUP_NO非空外，其它无任何限制.

    例3：在建立表时指定列CHECKUP_TYPE为外部列：

    CREATE TABLE SEAPARK.CHECKUP_HISTORY

    (

    CHECKUP_NO NUMBER(10) NOT NULL, ID_NO NUMBER(10,0), CHECKUP_TYPE VARCHAR2(30), CHECKUP_DATE DATE, DOCTOR_NAME VARCHAR2(50),

    FOREIGN KEY (CHECKUP_TYPE) REFERENCES

    SEAPARK.CHECKUP (CHECKUP_TYPE),

    PRIMARY KEY (CHECKUP_NO)

    )

    PCTFREE 20

    PCTUSED 60

    INITRANS 2

    MAXTRANS 255

    STORAGE ( INITIAL 1250K

    NEXT 2K

    MINEXTENTS 1

    MAXEXTENTS 121

    Pctincrease 0)

    TABLESPACE user_data;

例子指定了所有者，主键，外部键,表空间及存储参数等，主键和外部键在后面章节介绍。

    1.3 建立临时表结构Oracle现在可以使用 CREATE GLOBAL TEMPORARY TABLE命令来实现建立临时表结构。这样的表它的数据只在用户会话期间存在，当会话完成后就自动清除。看下面例子：

    SQL> create global temporary table myemp as select * from emp;

    表已创建。

    SQL> desc myemp

    名称                                      空?     类型

   ----------------------------------------- -------- --------------

    ENAME VARCHAR2(20)

    SAL                                                NUMBER(9,2)

    DEPTNO NUMBER(4)

    TEL VARCHAR2(20)

    SQL> select * from myemp;

    未选定行

    SQL> insert into myemp values('赵元杰',32456.99,10,'12');

    已创建 1 行。

    SQL> select * from myemp;

    ENAME SAL DEPTNO TEL

  -------------------- ---------- ---------- ------------------

    赵元杰                 32456.99         10 12

    SQL> connect sys/sys

    已连接。

    SQL> connect zhao/zhao

    已连接。

    SQL> l

    1* select * from myemp

    SQL> /

    未选定行

    从上面可看出当连接到SYS在连接回来后数据就不存在了。对于临时表，可以用DROP TABLE来删除其结构。如：

    SQL> drop table myemp;

    表已丢弃。