oracle create table(转)

时间:2021-01-10 05:31:02
  • //建测试表
  • 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