1. Create Statement
(1) Create table:
Format:
CREATE TABLE table(field1 type[(size)][index1],field2 type [(size)][index2],...,nultifieldindex[,...])
Notable:
①创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面。
②创建表时可以用中文的字段名,但最好还是用英文的字段名。
③创建表时可以给字段加上默认值,例如DEFAULT SYSDATE。这样每次插入和修改 时,不用程序操作这个字段都能得到动作的时间。
④创建表时可以给字段加上约束条件。例如不允许重复UNIQUE,关键字PRIMARY KEY。
(2)Create Index
Format:
CREATE[UNIQUE]INDEX index ON table(field1[ASC|DESC],
field2[ASC|DESC],...)[WITH {PRIMARY|DISALLOWNULL|IGNORENULL}]
(3)Create View
Format:
CREATE VIEW view AS SELECT table1.field1,…,table2.field1…
FROM table1,table2….;
View is a logical form, it allows operators from other table or view access data, view itself does not contain data. That view is based on the table referred to as the base table.
Introduction:
1> Provide additional security table, the yankees limit access tables and columns or rows.
2> Hidden data complexity.
3>For data provide another opinion.
4?Oracle prompted some operating in the database contains views on execution, and not another database on execution.
(4)Create SYNONYM
Format:
CREATE SYNONYM symnon_name FOR [username.]tablename;
同义词为表、视图、序列、存储函数、包、快照或其它同义词的另一个名字。使用同义词为了安全和方便。对某一对象建立同义词可有下列好处:
引用对象不需指出对象的持有者。
引用对象不需指出它所位于的数据库。
为对象提供另一个名字。
(5)Create User
CREATE USER username IDENTIFIED BY password;
2. Alter Statement
ALTER TABLE table
{ADD {COLUMN field type[(size)][CONSTRAINT index]
|CONSTRAINT multifiedindex}
|DROP {COLUMN field|CONSTRAINT indexname}}
(1)改变表的名称,语句格式为:
ALTER TABLE table1 TO table 2;
(2)在表的后面增加一个字段,语句格式为:
ALTER TABLE table ADD FIELD DESCRIPTION;
(3)修改表里字段的定义描述,语句格式为:
ALTER TABLE table MODIFY FIELD DESCRIPTION;
(4)给表里的字段加上约束条件,语句格式为:
ALTER TABLE table ADD CONSTRAINT cons_name PRIMARYKEY
(field);
ALTER TABLE table ADD CONSTRAINTcons_name UNIQUE(field);
(5)把表放在或取出数据库的内存区,语句格式为:
ALTER TABLE table CACHE;
ALTER TABLE table NOCACHE;
3. Drop Statement
DROP {TABLE table|INDEX index ON table}
4. Truncate Statement
TRUNCATE table;
(1) Create table:
Format:
CREATE TABLE table(field1 type[(size)][index1],field2 type [(size)][index2],...,nultifieldindex[,...])
Notable:
①创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面。
②创建表时可以用中文的字段名,但最好还是用英文的字段名。
③创建表时可以给字段加上默认值,例如DEFAULT SYSDATE。这样每次插入和修改 时,不用程序操作这个字段都能得到动作的时间。
④创建表时可以给字段加上约束条件。例如不允许重复UNIQUE,关键字PRIMARY KEY。
(2)Create Index
Format:
CREATE[UNIQUE]INDEX index ON table(field1[ASC|DESC],
field2[ASC|DESC],...)[WITH {PRIMARY|DISALLOWNULL|IGNORENULL}]
(3)Create View
Format:
CREATE VIEW view AS SELECT table1.field1,…,table2.field1…
FROM table1,table2….;
View is a logical form, it allows operators from other table or view access data, view itself does not contain data. That view is based on the table referred to as the base table.
Introduction:
1> Provide additional security table, the yankees limit access tables and columns or rows.
2> Hidden data complexity.
3>For data provide another opinion.
4?Oracle prompted some operating in the database contains views on execution, and not another database on execution.
(4)Create SYNONYM
Format:
CREATE SYNONYM symnon_name FOR [username.]tablename;
同义词为表、视图、序列、存储函数、包、快照或其它同义词的另一个名字。使用同义词为了安全和方便。对某一对象建立同义词可有下列好处:
引用对象不需指出对象的持有者。
引用对象不需指出它所位于的数据库。
为对象提供另一个名字。
(5)Create User
CREATE USER username IDENTIFIED BY password;
2. Alter Statement
ALTER TABLE table
{ADD {COLUMN field type[(size)][CONSTRAINT index]
|CONSTRAINT multifiedindex}
|DROP {COLUMN field|CONSTRAINT indexname}}
(1)改变表的名称,语句格式为:
ALTER TABLE table1 TO table 2;
(2)在表的后面增加一个字段,语句格式为:
ALTER TABLE table ADD FIELD DESCRIPTION;
(3)修改表里字段的定义描述,语句格式为:
ALTER TABLE table MODIFY FIELD DESCRIPTION;
(4)给表里的字段加上约束条件,语句格式为:
ALTER TABLE table ADD CONSTRAINT cons_name PRIMARYKEY
(field);
ALTER TABLE table ADD CONSTRAINTcons_name UNIQUE(field);
(5)把表放在或取出数据库的内存区,语句格式为:
ALTER TABLE table CACHE;
ALTER TABLE table NOCACHE;
3. Drop Statement
DROP {TABLE table|INDEX index ON table}
4. Truncate Statement
TRUNCATE table;