数据库table常用操作

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;