Oracle基础学习笔记(一)(Create Table, ALTER, RENAME, SEQUENCE)

原创 2012年03月29日 18:23:05

1)

CREATE [GLOBAL TEMPORARY] TABLE name (

 

)

[ON COMMIT {DELETE|PRESERVE} ROWS]

TABLESPACE name;

 

ON COMMIT -- to set the duration of the rows persist in the temporary table

DELETE(by default) -- end of transaction

PRESERVE -- end of user session

 

2)

ALTER TABLE tab_name ADD col_name TYPE CONSTRAINT;

ALTER TABLE tab_name ADD (col_name AS (low_salary+high_salary)); (add a virtual column, new in Oracle 11g, the virtual column refers only to existing columns)

ALTER TABLE tab_name MODIFY col_name TYPE CONSTRAINT;

ALTER TABLE tab_name DROP COLUMN col_name;

ALTER TABLE tab_name ADD CONSTRAINT cons_name CONSTRAINT;

ALTER TABLE tab_name ADD CONSTRAINT cons_name_FK col_name REFERENCES tab_name(col_name) ON DELETE CASCADE;

ALTER TABLE tab_name ADD CONSTRAINT cons_name_FK col_name REFERENCES tab_name(col_name) ON DELETE SET NULL;

ALTER TABLE tab_name MODIFY col_name CONSTRAINT cons_name CONSTRAINT; (add constraint by MODIFY clause);

ALTER TABLE tab_name DROP CONSTRAINT cons_name;

 

Disabling a Constraint

ALTER TABLE tab_name ADD CONSTRAINT cons_name UNIQUE(col_name) DISABLE;

ALTER TABLE tab_name DISABLE CONSTRAINT cons_name; (disable existing constraint)

 

Enable a Constraint

ALTER TABLE tab_name ENABLE CONSTRAINT cons_name; (all rows should satisfy the constraint)

ALTER TABLE tab_name ENABLE NOVALIDATE CONSTRAINT cons_name; (only apply for new rows, by default it is ENABLE VALIDATE)

 

Deferred Constraints

(the constraint is enforces only when a transaction is committed. INITIALLY IMMEDIATE(by default) or INITIALLY DEFERRED)

ALTER TABLE tab_name ADD CONSTRAINT cons_name UNIQUE(col_name) DEFERRABLE INITIALLY DEFERRED;

 

3)

RENAME tab_name TO new_tab_name;

ALTER TABLE tab_name RENAME col_name TO new_col_name;

 

4)

CREATE SEQUENCE seq_name

[START WITH start_num]

[INCREMENT BY increment_num]

[{MAXVALUE maximum_num | NOMAXVALUE}]

[{MINVALUE minimum_num | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE cache_num | NOCACHE}]

[{ORDER | NOORDER}] -- guarantee the integers are generated in the order by the request,used in the real application clusters.

(Real Application Clusters are multiple database servers that share the same memory)

 

ALTER SEQUENCE seq_name INCREMENT BY 2;

seq_name.nextval

seq_name.currval

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

day19 Oracle ------ DDL(CREATE ,ALTER, constraints, PK, FK,UK,NOT NULL,CHECK) rename , truncate

union 联合查询case    when   .... then ....  end decode(dept_id, 41, salary ,1.2, 42, salary *1.4) 函数DEC...
  • inaoen
  • inaoen
  • 2011-01-07 19:13
  • 1083

oracle create table CREATE SEQUENCE学习

转自http://blog.sina.com.cn/s/blog_630d50dc0100hlph.html 首先,你要有一张表! CREATE TABLE example( ID Number(...

sql: Oracle 11g create table, function,trigger, sequence

----自动增长ID --序列创建 drop SEQUENCE BookPlaceList_SEQ; CREATE SEQUENCE BookPlaceList_SEQ INCREMENT BY...

sql: Oracle 11g create table, function,trigger, sequence

----自动增长ID --序列创建 drop SEQUENCE BookPlaceList_SEQ; CREATE SEQUENCE BookPlaceList_SEQ INCREMENT BY...

A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

The formulas presented here assume sp_configure max degree of parallelism=0 and no other resource go...

oracle 学习笔记(十一) 数据库常用对象, table ,constraint ,index ,view和数据字典

表:基本的数据存储对象,以行和列的形式存在,列是字段,行是记录。 数据字典:就是系统表,存放数据库相关信息的表。 约束条件:执行数据校验,保证了数据完整性的对象。 视图:一个或者多个表数据的逻辑显示。...

ORACLE Rename Table的语法

  • 2010-10-25 20:29
  • 24KB
  • 下载

oracle学习笔记-入门基础

  • 2010-05-10 17:02
  • 140KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)