在这里整理一下Oracle中对表的基本操作。
1. 创建表
1.1 直接创建
create table 表名
(
field1 type[(size)] [index1],
field2 type[(size)] [index2],
......,
[[multifieldindex],...]
)
示例:
CREATE TABLE T_YGY_DEMO_BOOK
(
ID NUMBER(11),
NAME VARCHAR2(100)
);
1.2 从其他表创建
create table 表名 as select语句
CREATE TABLE T_YGY_DEMO_BOOK2 AS SELECT *FROM T_YGY_DEMO_BOOK;
2. 修改表
2.1 添加列
alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);
示例:
ALTER TABLE T_YGY_DEMO_BOOK ADD PRICE NUMBER(5,2)
alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;
ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);
2.2 更改列
alter table
table_name
modify
column_name datatype;
alter table
table_name
modify
(
column1_name column1_datatype,
column2_name column2_datatype,
column3_name column3_datatype,
column4_name column4_datatype
);
示例:
ALTER TABLE
customer
MODIFY
(
cust_name varchar2(100) not null,
cust_hair_color varchar2(20)
)
;
2.3 删除列
alter table
table_name
drop column
col_name1; -- drop ONE column
alter table
table_name
drop
(col_name1, col_name2); -- drop MANY columns
示例:
alter table
cust_table
drop column
cust_sex;
2.4 设置未用列
alter table
table_name
set unused column
column_name;
alter table
table_name
set unused
(
col_name1,col_name2
);
清除字典信息(撤消存储空间),不可恢复。
可以使用 SET UNUSED 选项标记一列或者多列不可用。
使用DROP UNUSED 选项删除被被标记为不可用的列。
示例:
alter table
T_YGY_DEMO_BOOK
set unused
(PRICE,PUBLISH_DATE);
alter table
T_YGY_DEMO_BOOK
drop unused columns;
2.5 修改表名
alter table
table_name
rename to
new_table_name;
示例:
alter table
T_YGY_DEMO_BOOK
rename to
T_YGY_DEMO_BOOK009;
2.6 修改列名
alter table
table_name
rename column
old_column_name
TO
new_column_name;
示例:
alter table
T_YGY_DEMO_BOOK009
rename column
name
to
nickname;
3. 删除表
DROP TABLE table_name;