oracle alter table详解

• ALTER TABLE (表名) ADD (列名 数据类型);
• ALTER TABLE (表名) MODIFY (列名 数据类型);
• ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名);
• ALTER TABLE (表名) DROP COLUMN (列名);
• ALTER TABLE (当前表名) RENAME TO (新表名)
• create table dept(
• deptno number(3) primary key,
• dname varchar2(10),
• loc varchar2(13)
• );
• create table employee_info(
• empno number(3),
• deptno number(3),
• ename varchar2(10),
• sex char(1),
• phone number(11),
• address varchar2(50),
• introduce varchar2(100)
• );
• --
• //0.重命名
• //0.1 表:rename dept to dt;
• rename dt to dept;
• //0.2 列:alter table dept rename column loc to location;
• alter tabledept rename column location to loc;
• //1.添加约束
• //1.1 primary key
• alter table employee_info add constraint pk_emp_info primary key(empno);
• //1.2 foreign key
• alter table employee_info add constraint fk_emp_info foreign key(deptno)
• references dept(deptno);
• //1.3 check
• alter table employee_info add constraint ck_emp_info check
• (sex in ('F','M'));
• //1.4 not null
• alter table employee_info modify phone constraint not_null_emp_info not null;
• //1.5 unique
• alter table employee_info add constraint uq_emp_info unique(phone);
• //1.6 default
• alter table employee_info modify sex char(2) default'M';
• //2.添加列
• alter table employee_info add id varchar2(18);
• alter table employee_info add hiredatedate default sysdate not null;
• //3.删除列
• alter table employee_info drop columnintroduce;
• //3.修改列
• //3.1 修改列的长度
• alter table dept modify loc varchar2(50);
• //3.2 修改列的精度
• alter table employee_info modify empno number(2);
• //3.3 修改列的数据类型
• alter table employee_info modify sex char(2);
• //3.4 修改默认值
• alter table employee_info modify hiredate default sysdate+1;
• //4.禁用约束
• alter table employee_info disable constraintuq_emp_info;
• //5.启用约束
• alter table employee_info enable constraintuq_emp_info;
• //6.延迟约束
• alter table employee_info drop constraintfk_emp_info;
• alter table employee_info add constraint fk_emp_info foreign key(deptno)
• references dept(deptno)
• deferrable initially deferred;
• //7.向表中添加注释
• comment on table employee_info is'information of employees';
• //8.向列添加注释
• comment on column employee_info.ename is'the name of employees';
• comment on column dept.dname is'the name of department';
• //9.清除表中所有数据
• truncate table employee_info;
• //10.删除表
• drop table employee_info;
• --