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

本文介绍了Oracle数据库的基础操作,包括CREATE TABLE语法,详细讲解了临时表的两种行为(默认删除和保留行)。接着讨论了ALTER TABLE的多种用法,如添加、修改和删除列,以及添加、禁用和启用约束,包括外键约束。还提到了RENAME用于重命名表和列。最后,介绍了如何创建和调整SEQUENCE,以及在实际应用集群中使用序列的注意事项。

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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值