0.重命名
0.1 表:rename dept to dt;
rename dt to dept;
0.2 列:alter table dept rename column loc to location;
alter table dept 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 hiredate date default sysdate not null;
3.删除列
alter table employee_info drop column introduce;
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 constraint uq_emp_info;
5.启用约束
alter table employee_info enable constraint uq_emp_info;
6.延迟约束
alter table employee_info drop constraint fk_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;
1、创建表的同时创建主键约束
(1)无命名 create table student ( studentid int primary key not null, studentname varchar(8), age int);
(2)有命名 create table students ( studentid int , studentname varchar(8), age int, constraint yy primary key(studentid));
2、删除表中已有的主键约束
(1)无命名可用 SELECT * from user_cons_columns; 查找表中主键名称得student表中的主键名为SYS_C002715
alter table student drop constraint SYS_C002715;
(2)有命名 alter table students drop constraint yy;
3、向表中添加主键约束 alter table student add constraint pk_student primary key(studentid);
4、向表中添加外键约束 ALTER TABLE table_A ADD CONSTRAINT FK_name FOREIGN KEY(id) REFERENCES table_B(id);
添加字段的语法:alter table tablename add (column datatype [default value][null/not null],….);
修改字段的语法:alter table tablename modify (column datatype [default value][null/not null],….);
删除字段的语法:alter table tablename drop (column);
重命名字段的语法:alter table tablename rename column colname to colnamenew;