ORACLE ALTER TABLE

create table T_CLASS_INFO(
 CLASSNO number(3) primary key, --班级序号
 CLASSNAME varchar2(10),
 CLASSPLACE varchar2(13)
);
create table T_STUDENT_INFO(
 STUNO number(3),--学号
 CLASSNO number(3),
 STUNAME varchar2(10),
 STUSEX char(1),
 STUCONTECT number(11), --联系方式
 STUADD varchar2(50),
 STUDETAILINFO varchar2(100)
);

--修改列名
ALTER table T_CLASS_INFO RENAME column CLASSPLACE to LOCATION;
--添加主键约束
ALTER table T_STUDENT_INFO ADD constraint PK_STU_NO primary key(STUNO);
--添加外键约束
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO) references T_CLASS_INFO(CLASSNO);
--添加check约束
ALTER table T_STUDENT_INFO ADD constraint CK_STU_INFO check (STUSEX in ('F','M'));
--添加not null约束
ALTER table T_STUDENT_INFO MODIFY STUCONTECT constraint NOT_NULL_INFO not null;
--添加unique约束
ALTER table T_STUDENT_INFO ADD constraint UQ_STU_CONTECT unique(STUCONTECT);
--添加default约束
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2) default'M';

--增加列
ALTER table T_STUDENT_INFO ADD STUID varchar2(18);
ALTER table T_STUDENT_INFO ADD STUAGE DATE default sysdate not null;
--删除列
ALTER table T_STUDENT_INFO DROP column STUDETAILINFO;

--修改列的长度
ALTER table T_CLASS_INFO MODIFY CLASSPLACE varchar2(50);
--修改列的精度
ALTER table T_STUDENT_INFO MODIFY STUNO number(2);
--修改列的数据类型
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2);
--修改列的默认值
ALTER table T_STUDENT_INFO MODIFY STUAGE default sysdate+1;

--禁用约束
ALTER table T_STUDENT_INFO disable FK_CLASSNO;
--启用约束
ALTER table T_STUDENT_INFO enable FK_CLASSNO;
--删除约束
ALTER table T_STUDENT_INFO DROP FK_CLASSNO;
--延迟约束
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO)
references T_CLASS_INFO(CLASSNO)
deferrable initially deferred;

--向表中添加注释
comment on table T_STUDENT_INFO is '学生表';
--向列添加注释
comment on column T_STUDENT_INFO.STUNAME is '学生姓名';
comment on column T_CLASS_INFO.CLASSNAME is '班级';
--清除表中所有数据
truncate table T_STUDENT_INFO;
--删除表
DROP table T_STUDENT_INFO;