首页 > 代码库 > oracle create table(转)

oracle create table(转)

  • //建测试表  
  • create table dept(  
  •        deptno number(3) primary key,  
  •        dname varchar2(10),  
  •        loc varchar2(13)   
  •        );  
  • create table employee_info(  
  •        empno number(3),  
  •        deptno number(3),  
  •        ename varchar2(10),  
  •        sex char(1),  
  •        phone number(11),  
  •        address varchar2(50),  
  •        introduce varchar2(100)  
  •        );  
  • --   
  • //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;  
  • --   
  • //下面来看看刚刚才我们对表dept和表employee_info所做的更改  
  • //user_constraints视图里面包含了刚刚才我们创建的所有约束,以及其他信息,  
  • //你可以用desc user_constraints命令查看其详细说明  
  • select constraint_name,constraint_type,status,deferrable,deferred  
  • from user_constraints  
  • where table_name=‘EMPLOYEE_INFO‘;  
  • --   
  • CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED  
  • ------------------------------ --------------- -------- -------------- ---------   
  • PK_EMP_INFO                    P               ENABLED  NOT DEFERRABLE IMMEDIATE  
  • FK_EMP_INFO                    R               ENABLED  DEFERRABLE     DEFERRED  
  • NOT_NULL_EMP_INFO              C               ENABLED  NOT DEFERRABLE IMMEDIATE  
  • SYS_C005373                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
  • UQ_EMP_INFO                    U               ENABLED  NOT DEFERRABLE IMMEDIATE  
  • CK_EMP_INFO                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
  • //我们可以通过user_cons_columns视图查看有关列的约束信息;  
  • select owner,constraint_name,table_name,column_name  
  • from user_cons_columns  
  • where table_name=‘EMPLOYEE_INFO‘;  
  • --   
  • OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME  
  • ------------------------------ ------------------------------ ------------------------------ ---------------   
  • YEEXUN                         PK_EMP_INFO                    EMPLOYEE_INFO                  EMPNO  
  • YEEXUN                         CK_EMP_INFO                    EMPLOYEE_INFO                  SEX  
  • YEEXUN                         NOT_NULL_EMP_INFO              EMPLOYEE_INFO                  PHONE  
  • YEEXUN                         SYS_C005373                    EMPLOYEE_INFO                  HIREDATE  
  • YEEXUN                         UQ_EMP_INFO                    EMPLOYEE_INFO                  PHONE  
  • YEEXUN                         FK_EMP_INFO                    EMPLOYEE_INFO                  DEPTNO  
  • //我们将user_constraints视图与user_cons_columns视图连接起来  
  • //查看约束都指向哪些列  
  • column column_name format a15;  
  • select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status  
  • from user_constraints uc,user_cons_columns ucc  
  • where uc.table_name=ucc.table_name and  
  •       uc.constraint_name=ucc.constraint_name and  
  •       ucc.table_name=‘EMPLOYEE_INFO‘;  
  • --   
  • COLUMN_NAME     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS  
  • --------------- ------------------------------ --------------- --------   
  • EMPNO           PK_EMP_INFO                    P               ENABLED  
  • DEPTNO          FK_EMP_INFO                    R               ENABLED  
  • PHONE           NOT_NULL_EMP_INFO              C               ENABLED  
  • HIREDATE        SYS_C005373                    C               ENABLED  
  • PHONE           UQ_EMP_INFO                    U               ENABLED  
  • SEX             CK_EMP_INFO                    C               ENABLED  
  • --   
  • //这里有个constraint_type,他具体指下面几种类型:  
  • //C:check,not null  
  • //P:primary key  
  • //R:foreign key  
  • //U:unique  
  • //V:check option  
  • //O:read only  
  • --   
  • //我们可以通过user_tab_comments视图获得对表的注释  
  • select * from user_tab_comments  
  • where table_name=‘EMPLOYEE_INFO‘;  
  • TABLE_NAME                     TABLE_TYPE  COMMENTS  
  • ------------------------------ ----------- --------------------------   
  • EMPLOYEE_INFO                  TABLE       information of employees  
  • --   
  • //我们还可以通过user_col_comments视图获得对表列的注释:  
  • select * from  user_col_comments  
  • where table_name=‘EMPLOYEE_INFO‘;  
  • --   
  • TABLE_NAME                     COLUMN_NAME                    COMMENTS  
  • ------------------------------ ------------------------------ ---------------------------   
  • EMPLOYEE_INFO                  EMPNO                            
  • EMPLOYEE_INFO                  DEPTNO                           
  • EMPLOYEE_INFO                  ENAME                          the name of employees  
  • EMPLOYEE_INFO                  SEX                              
  • EMPLOYEE_INFO                  PHONE                            
  • EMPLOYEE_INFO                  ADDRESS                          
  • EMPLOYEE_INFO                  ID                               
  • EMPLOYEE_INFO                  HIREDATE   
  • --   
  • select * from user_col_comments  
  • where table_name=‘EMPLOYEE_INFO‘ and  
  •       comments is not null;  
  • --   
  • TABLE_NAME                     COLUMN_NAME                    COMMENTS  
  • ------------------------------ ------------------------------ ------------------------   
  • EMPLOYEE_INFO                  ENAME                          the name of employees  
  • --   
  • //最后我们来查看一下修改后的表:  
  • desc employee_info;  
  • Name     Type         Nullable Default   Comments                
  • -------- ------------ -------- --------- ---------------------    
  • EMPNO    NUMBER(2)                                               
  • DEPTNO   NUMBER(3)    Y                                          
  • ENAME    VARCHAR2(10) Y                  the name of employees   
  • SEX      CHAR(2)      Y        ‘M‘                               
  • PHONE    NUMBER(11)                                              
  • ADDRESS  VARCHAR2(50) Y                                          
  • ID       VARCHAR2(18) Y                                          
  • HIREDATE DATE                  sysdate+1  
  • --   
  • desc dept;  
  • Name   Type         Nullable Default Comments                 
  • ------ ------------ -------- ------- ----------------------    
  • DEPTNO NUMBER(3)                                              
  • DNAME  VARCHAR2(10) Y                the name of department   
  • LOC    VARCHAR2(50) Y