ORACLE ALTER TABLE，oraclealtertable
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;