oracle修改表结构和约束的修改及查询

一、修改表结构

1.为member添加两个字段

SQL> alter table member add(age number(3),birthday date dafault sysdate);

2. 将name字段的默认值定义为“hhhh”

SQL> alter table member modify (name varchar2(20) default 'hhhh');

3.修改name字段名为xingming

SQL> alter table member rename column name to xingming;

4.删除默认值

SQL> alter table member modify name default null;

 5.删除列

SQL> alter table member drop column name;

二、修改约束

1.增加主键约束

SQL> alter table member add constraint pk_mid primary key (mid);

2.删除主键约束 

SQL> alter table member drop constraint pk_mid;

三、查询约束

1.查询所有表的约束

SQL> select constraint_name,search_condition,table_name from from user_constraints;

2.查询部分约束（只能看到有约束，不显示具体的字段）

SQL> select owner,constraint_name,table_name from user_constraints;

3.查询哪个字段被约束（column_name即为约束字段的名称）

SQL> select * from user_cons_columns;