首页 > 代码库 > 建立简单的表,并对表进行简单ddl操作

建立简单的表,并对表进行简单ddl操作

1.常见的数据类型

  char(n)   定长

  varchar2(n) 变长,最大到4000

  number(p,s)

  long

  lob

  raw

2.创建一个简单的表

scott@TEST>create table t1(
  2  name char(8),
  3  salary number(5) default 0,
  4  content char (4 char),
  5  hiredate date );

Table created.
scott@TEST>desc t1;   //描述表结构
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                                       CHAR(8)
 SALARY                                                                     NUMBER(5)
 CONTENT                                                                    CHAR(4 CHAR)
 HIREDATE                                                                   DATE

  在现有的表基础上创建一个新的表。也就是复制现有的表结构和数据创建一个新的表空间

scott@TEST>create table t2 as
  2  select ename,job,sal from emp;

Table created.

scott@TEST>desc t2;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ENAME                                                                      VARCHAR2(10)
 JOB                                                                        VARCHAR2(9)
 SAL                                                                        NUMBER(7,2)

scott@TEST>select * from t2;

ENAME                JOB                       SAL
-------------------- ------------------ ----------
SMITH                CLERK                     800
ALLEN                SALESMAN                 1600
WARD                 SALESMAN                 1250
JONES                MANAGER                  2975
MARTIN               SALESMAN                 1250
BLAKE                MANAGER                  2850
CLARK                MANAGER                  2450
SCOTT                ANALYST                  3000
KING                 PRESIDENT                5000
TURNER               SALESMAN                 1500
ADAMS                CLERK                    1100
JAMES                CLERK                     950
FORD                 ANALYST                  3000
MILLER               CLERK                    1300

14 rows selected.

  如果不想要数据,只建立表结构,则在最后写一条不成立的where子句即可

scott@TEST>create table t3 as
  2  select ename,job,sal from emp where 1=2;

Table created.

scott@TEST>desc t3;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ENAME                                                                      VARCHAR2(10)
 JOB                                                                        VARCHAR2(9)
 SAL                                                                        NUMBER(7,2)

scott@TEST>select * from t3;

no rows selected

3.修改表结构

  1)如果列为null,可以随便修改列的类型和宽度

       如果有数据,修改会受到限制。但不会破坏数据

     如果不改变数据类型,只改变宽度的话加大是可以的

scott@TEST>alter table t1 modify(name char(4));

Table altered.

scott@TEST>desc t1;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                                       CHAR(4)
 SALARY                                                                     NUMBER(5)
 CONTENT                                                                    CHAR(4 CHAR)
 HIREDATE                                                                   DATE

  2)修改表的名称

scott@TEST>rename t1 to t_1;

Table renamed.

scott@TEST>select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
T2                                                           TABLE
T3                                                           TABLE
T_1                                                          TABLE

7 rows selected.

   3)修改列的名称(10g以上才可以)

scott@TEST>alter table t3 rename column ename to c1;

Table altered.

scott@TEST>desc t3;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         VARCHAR2(10)
 JOB                                                                        VARCHAR2(9)
 SAL                                                                        NUMBER(7,2)

  4)表注释

scott@TEST>comment on table emp is employee table;

Comment created.
scott@TEST>select comments from user_tab_comments where table_name=EMP;

COMMENTS
------------------------------------------------------------------------------------------------------------------------
employee table

  5)列注释

scott@TEST>comment on column emp.sal is money;

Comment created.
scott@TEST>select comments from user_col_comments 
  2  where table_name=EMP AND column_name=SAL;

COMMENTS
------------------------------------------------------------------------------------------------------------------------
money

   6)丢弃表

    被丢弃的表并没有被删除,只是改了个名称。

scott@TEST>select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
T2                                                           TABLE
T3                                                           TABLE
T_1                                                          TABLE

7 rows selected.

scott@TEST>
scott@TEST>
scott@TEST>drop table t2;

Table dropped.

scott@TEST>select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
BIN$TkFwIgLVUOTgU0MKqMBNZw==$0                               TABLE
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
T3                                                           TABLE
T_1                                                          TABLE

7 rows selected.

  显示回收站的信息

scott@TEST>show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T2               BIN$TkFwIgLVUOTgU0MKqMBNZw==$0 TABLE        2017-04-28:17:05:49
scott@TEST>select * from user_recyclebin;

OBJECT_NAME
------------------------------------------------------------
ORIGINAL_NAME                                                    OPERATION
---------------------------------------------------------------- ------------------
TYPE                                               TS_NAME
-------------------------------------------------- ------------------------------------------------------------
CREATETIME                             DROPTIME                                  DROPSCN
-------------------------------------- -------------------------------------- ----------
PARTITION_NAME                                                   CAN_UN CAN_PU    RELATED BASE_OBJECT PURGE_OBJECT
---------------------------------------------------------------- ------ ------ ---------- ----------- ------------
     SPACE
----------
BIN$TkFwIgLVUOTgU0MKqMBNZw==$0
T2                                                               DROP
TABLE                                              USERS
2017-04-28:15:16:19                    2017-04-28:17:05:49                       1331779
                                                                 YES    YES         88703       88703        88703
         8

  将回收站的表还原

scott@TEST>flashback table t2 to before drop;

Flashback complete.

scott@TEST>select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
T2                                                           TABLE
T3                                                           TABLE
T_1                                                          TABLE

7 rows selected.

  将回收站的表还原的同时更改名字

scott@TEST>flashback table t2 to before drop rename to tt2;

Flashback complete.

scott@TEST>select * from tab;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
T3                                                           TABLE
TT2                                                          TABLE
T_1                                                          TABLE

7 rows selected.

   清空回收站内指定的表

scott@TEST>create table t4 as select * from emp;

Table created.

scott@TEST>drop table t4;

Table dropped.

scott@TEST>purge table t4;

Table purged.

  清空当前用户的回收站,不会影响其它用户的回收站

scott@TEST>purge recyclebin;

Recyclebin purged.

  绕过回收站,直接彻底删除一张表

scott@TEST>drop table t4 purge;

Table dropped.

 

建立简单的表,并对表进行简单ddl操作