ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL 基础-->创建和管理表

SQL 基础-->创建和管理表

原创 Linux操作系统 作者:beatony 时间:2011-07-29 14:48:48 0 删除 编辑
SQL 基础-->创建和管理表

--=================================

--SQL 基础-->创建和管理表

--=================================

 

一、创建表:create table

    1.语法:CREATE TABLE  [ 用户名. ] 表名

         (列名 数据类型 [ default 默认值] [ 约束条件]  [ , ......]  ) 

       TABLESPACE 表空间名

 

    2.表名和列名命名规则:

       必须以字母开头

       必须在1–个字符之间

       必须只能包含A–Z, a–z, 0–, _, $, #

       必须不能和用户定义的其他对象重名

       必须不能是Oracle 的保留字

 

    3.创建前必须具备的条件:

       CREATE TABLE权限

       存储空间

 

     4.必须指定:

       表名

       列名, 数据类型, 尺寸

 

    5.数据类型:

       varchar2(size)    变长字符型(最大字符)

       nvarchar2(size)   变长unicode字符型(最大字符)

       char(size)        字长字符型(最大字符)

       number(p,s)       数值型(p为长度最大,s为小数点后的位数-~)

       data              日期型

   

       LOB ( Large Object )

           clob              字符型,用于在数据库中存储单字节的大数据对象,最大G

           nclob             可存放大量unicode文字信息,最大4G

           blob              用于在数据库中存储二进制数据,如照片,最大G

              clobblob许多操作是不能直接使用oracle的数据库命令来完成的,

              因此,oracle 提供了一个叫DBMS_LOB PL/SQL 软件包来维护LOB数据类型的列。

 

       bfile      外部二进制文件,用于在数据库外的操作系统文件中存储大的二进制

              对象,如电影,最大Gbfile 数据类型是外部数据类型,因此定义为bfile数据

              类型的列是不能通过oracle 的数据库命令来操作的,

              这些列只能通过操作系统命令或第三方软件来维护。

 

       raw        裸二进制数据,此种类型的数据占用的存储空间小,操作效率也高,但在网络环境

              中不同的计算机上传输资料时,oracle服务器不进行任何字符集转换,

              raw 1-2000 字符。

                    

       long long raw   为和以前的oracle版本兼容,oracle 继续支持

                  long long raw 数据类型                   

       long       可变大字符型数据,最大Glong raw 裸二进制数据,最大2 G

              主要用在8i 以前的数据库中存储无结构的数据。

 

       rowid      行地址

 

       oracle 8 以后的版本,LOB 数据类型可以完全取代LONG 数据类型,

       而且oracle 服务器操作LOB 数据类型比操作LONG 数据类型效率更高。

       另外,在一个表中只能定义一个LONG数据类型的列,但可定义多个LOB数据

       类型的列。LONG数据类型的列最多可以存储GB数据,而LOB数据类型的列最多可以存储GB的数据。

 

 

    注意:

    number(p,s)

       p:1~38          精度位,precision,是总有效数据位数,默认是,可以用字符*表示。

       s:-84~127      小数位,scale,是小数点右边的位数,取值范围是-84~127

              默认值取决于p,如果没有指定p,那么s是最大范围,如果指定了p,那么s=0

 

       p>0,对s分种情况:

 

           1. s>0

              精确到小数点右边s位,并四舍五入。然后检验有效数位是否<=p;如果s>p

              小数点右边至少有s-p个填充。

 

           2. s<0

              精确到小数点左边s位,并四舍五入。然后检验有效数位是否<=p+|s|

       eg:

           123.2564 NUMBER 123.2564

 

           1234.9876 NUMBER(6,2) 1234.99

 

           12345.12345 NUMBER(6,2) Error

 

           1234.9876 NUMBER(6) 1235

 

           12345.345 NUMBER(5,-2) 12300

 

           1234567 NUMBER(5,-2) 1234600

 

 

    6.引用其他用户的表

       其他用户定义的表不在当前用户的方案中

       应该使用用户名作为前缀,引用其他用户定义的对象

 

    7.DEFAULT 选项

       插入时为一个列指定默认值

       字符串, 表达式, SQL 函数都是合法的

       其它列的列名和伪列是非法的

       默认值必须满足列的数据类型定义

 

    8.使用默认值:

       插入记录时,可省略那个字段,也可显示的加default

 

       --演示创建表

       SQL> CREATE TABLE orders

         2  (

         3      orderid int,

         4      orderstatus varchar2(20),

         5      orderdate date,

         6      ordernum number(10)

         7  );

 

       Table created.

 

       --确认表结构

       SQL> DESC orders;

        Name                                      Null?    Type

        ----------------------------------------- -------- ----------------------------

        ORDERID                                            NUMBER(38)

        ORDERSTATUS                                        VARCHAR2(20)

        ORDERDATE                                          DATE

        ORDERNUM                                           NUMBER(10)

 

       Oracle 数据库中的表

           用户定义的表:

              用户自己创建并维护的一组表

              包含了用户所需的信息

            数据字典:

              Oracle Server自动创建的一组表

              包含数据库信息

 

 

    9.查询数据字典

       查看用户定义的表,

           SELECT table_name FROM user_tables ;

       查看用户定义的各种数据库对象

           SELECT DISTINCT object_type  FROM user_objects ;

       查看用户定义的表, 视图, 同义词和序列

           SELECT *  FROM user_catalog ;

 

    10.利用子查询创建表:

       CREATE TABLE table

       [(column, column...)]

       AS subquery;

   

       --演示利用子查询创建表

           SQL> CREATE TABLE emp

             2  AS

             3  SELECT ename,job,hiredate,mgr,sal               

             4  FROM scott.emp;

 

           Table created.

 

   

    11.利用子查询创建一个空表(克隆表结构):

       create table xx as select * from yy where 1=0;  字段可以指定的

      

           SQL> CREATE TABLE emp2

             2  AS

             3  SELECT *                        

             4  FROM scott.emp

             5  WHERE 1=2;

 

           Table created.

 

           SQL> SELECT * FROM emp2;

 

           no rows selected    

 

    12.使用子查询创建表时候用

    AS subquery 选项,将创建表和插入数据结合起来

       指定的列和子查询中的列要一一对应

       通过列名和默认值定义列

 

    13.用户查看一个表占用的磁盘空间:

       user_extents,user_segments

   

    14.DBA如何查看一个表占用的磁盘空间:

       dba_extents,dba_segments

   

       --用户查看自身表的使用情况(user_extents)

       SQL> SELECT segment_name,segment_type,tablespace_name,bytes/1024/1024 "size",

         2  blocks from user_extents;

 

       SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                      size     BLOCKS

       -------------------- ------------------ ------------------------------ ---------- ----------

       ORDERS               TABLE              USERS                               .0625          8

       EMP                  TABLE              USERS                               .0625          8

       EMP2                 TABLE              USERS                               .0625          8  

 

      

       --用户查看自身表的使用情况(user_segments)

       SQL> SELECT segment_name,segment_type,tablespace_name,

         2  bytes/1024/1024 byt,buffer_pool

         3  FROM user_segments;

      

       SEGMENT_NAME                             SEGMENT_TYPE       TABLESPACE_NAME             BYT BUFFER_

       ---------------------------------------- ------------------ ------------------- ---------- -------

       EMP2                                     TABLE              USERS                    .0625 DEFAULT

       EMP                                      TABLE              USERS                    .0625 DEFAULT

       ORDERS                                   TABLE              USERS                    .0625 DEFAULT

       BIN$iZfhhmba19rgQKjAZQETvA==$0           TABLE              USERS                    .0625 DEFAULT

       BIN$iZfhhmbZ19rgQKjAZQETvA==$0           TABLE              USERS                    .0625 DEFAULT

       BIN$iZfhhmbY19rgQKjAZQETvA==$0           TABLE              USERS                    .0625 DEFAULT

 

       SQL> INSERT INTO emp2

         2  SELECT * FROM scott.emp;

 

       15 rows created.

 

       SQL> /

 

       --DBA查看占用的磁盘空间情况

       SQL> SELECT owner,segment_name,segment_type,tablespace_name,bytes/1024/1024

         2  FROM dba_extents WHERE owner='SCOTT';

 

       OWNER           SEGMENT_NAME                 SEGMENT_TYPE       TABLESPACE_NAME      BYTES/1024/1024

       --------------- ---------------------------- ------------------ -------------------- ---------------

       SCOTT           DEPT                         TABLE              USERS                          .0625

       SCOTT           EMP                          TABLE              USERS                          .0625

       SCOTT           BONUS                        TABLE              USERS                          .0625

       SCOTT           SALGRADE                     TABLE              USERS                          .0625

       SCOTT           TB                           TABLE              USERS                          .0625

       SCOTT           DIGITS                       TABLE              USERS                          .0625

       SCOTT           TB2                          TABLE              USERS                          .0625

       SCOTT           PK_DEPT                      INDEX              USERS                          .0625

       SCOTT           PK_EMP                       INDEX              USERS                          .0625

       SCOTT           CUST_ID_PK                   INDEX              USERS                          .0625

 

 

       SQL> SELECT SUM(BYTES)/1024/1024 FROM dba_extents WHERE owner='ROBINSON'

         2  AND segment_name = 'EMP2';

 

       SUM(BYTES)/1024/1024

       --------------------

                        5

 

       SQL> SELECT owner,segment_name,segment_type,tablespace_name,bytes/1024/1024

         2   FROM dba_segments WHERE owner='SCOTT';

 

       OWNER           SEGMENT_NAME                       SEGMENT_TYPE    TABLESPACE_NAME   BYTES/1024/1024

       --------------- ---------------------------------- --------------- ----------------- ---------------

       SCOTT           PK_DEPT                            INDEX           USERS                       .0625

       SCOTT           DEPT                               TABLE           USERS                       .0625

       SCOTT           EMP                                TABLE           USERS                       .0625

       SCOTT           PK_EMP                             INDEX           USERS                       .0625

       SCOTT           BONUS                              TABLE           USERS                       .0625

       SCOTT           SALGRADE                           TABLE           USERS                       .0625

       SCOTT           TB                                 TABLE           USERS                       .0625

       SCOTT           DIGITS                             TABLE           USERS                       .0625

       SCOTT           TB2                                TABLE           USERS                       .0625

       SCOTT           CUST_ID_PK                         INDEX           USERS                       .0625

       SCOTT           BIN$h5Qj5nIfmqrgQKjAZQESMA==$0     TABLE           TBS1                        .0625

 

 

    15.DBA如何查看一个表的行数:

       先分析表,更新系统数据

           exec dbms_stats.gather_table_stats('属主' , ' 表名')

       dba_tables数据字典中查询

           select table_name,num_rows from dba_tables;

      

       --使用dbms_stats.gather_table_stats包查看

       SQL> exec dbms_stats.gather_table_stats('robinson','emp2');

 

       PL/SQL procedure successfully completed.

 

       SQL> /

 

       TABLE_NAME                       NUM_ROWS

       ------------------------------ ----------

       EMP

       ORDERS

       EMP2                                93544

          

       --dba_tables查看

       SQL> SELECT table_name,num_rows FROM dba_tables

         2  WHERE table_name = 'EMP2' AND owner = 'ROBINSON';

 

       TABLE_NAME                       NUM_ROWS

       ------------------------------ ----------

       EMP2                                93544

   

    16.用户自身查看表及列的相关信息

        user_tables user_tab_columns

      

       --查看表的相关信息

       SQL> SELECT table_name,tablespace_name,temporary,num_rows FROM user_tables;

 

       TABLE_NAME                     TABLESPACE_NAME                T   NUM_ROWS

       ------------------------------ ------------------------------ - ----------

       EMP                            USERS                          N

       ORDERS                         USERS                          N

       EMP2                           USERS                          N      93544

 

       --查看列的相关信息

       SQL> SELECT table_name,column_name,data_type,data_length,data_precision

       2  FROM user_tab_columns

       3  WHERE table_name = 'EMP2';

 

       TABLE_NAME                     COLUMN_NAME            DATA_TYPE      DATA_LENGTH DATA_PRECISION

       ------------------------------ ---------------------- -------------- ----------- --------------

       EMP2                           EMPNO                  NUMBER                  22              4

       EMP2                           ENAME                  VARCHAR2                10

       EMP2                           JOB                    VARCHAR2                 9

       EMP2                           MGR                    NUMBER                  22              4

       EMP2                           HIREDATE               DATE                     7

       EMP2                           SAL                    NUMBER                  22              7

       EMP2                           DEPTNO                 NUMBER                  22              2

          

          

    17.创建临时表:

       临时表分为LOCAL(本地)临时表和GLOBAL (全局)临时表,两者的区别在于数据可见性。

       LOCAL临时表中的数据只在填充它的事务可见,GLOBAL临时表可以被会话中的任何程序或模块访问。

      

       临时表的数据在退出时自动清除,但临时表的定义是永久的。当创建临表的会话注销后,

       表依然会存在,只不过是空的罢了。

 

       CREATE GLOBAL TEMPRORARY TABLE TEMP

       ON COMMIT PRESERVE ROWS

       AS

       SELECT * FROM SCOTT.EMP WHERE SAL>2000

 

 

        ON COMMIT DELETE ROWS    数据行只有在事务中可见(默认值)。

       ON COMMIT PRESERVE ROWS 数据行在整个会话中可见。

 

 

       删除临时表:DROP TABLE

       有时删除时需要TRUNCATE TABLE才行。

 

二、修改表:

    1.添加新列( 添加的列在最后,没办法调整其位置)

       ALTER TABLE tablename   

       ADD (column datatype [DEFAULT expr] [ constraint ] [, column datatype]...);

      

       SQL> ALTER TABLE scott.emp

         2  ADD (jobid varchar2(20) DEFAULT 'Eng');     

   

    2.修改现有的列

       ALTER TABLE tablename   

       MODIFY (column datatype [DEFAULT expr] [ constraint ]

           [, column datatype]...);

      

       可以被修改的内容:

           列的长度

           数字列的精度

           列的数据类型

           修改列的默认值

      

       a.修改列的长度

           --修改列的长度,当修改的长度比不能容纳现有数据长度,提示错误,如下:      

           SQL> ALTER TABLE scott.emp MODIFY(ename varchar2(3));

           ALTER TABLE scott.emp MODIFY(ename varchar2(3))

                                    *

           ERROR at line 1:

           ORA-01441: cannot decrease column length because some value is too big

 

           --以下修改长度正确执行

           SQL> ALTER TABLE scott.emp MODIFY(ename varchar2(30));

 

           Table altered.

      

       b.修改列的精度

           --只有当表中还没有任何行或列值为空值才可以降低数字的精度,如下第一条语句修改出错    

           SQL> ALTER TABLE scott.emp MODIFY(sal number(6,2));

           ALTER TABLE scott.emp MODIFY(sal number(6,2))

                                    *

           ERROR at line 1:

           ORA-01440: column to be modified must be empty to decrease precision or scale

 

 

           SQL> ALTER TABLE scott.emp MODIFY(sal number(8,2));

 

           Table altered.   

          

       c.修改列的数据类型

           --jobidVARCHAR2类型改为CHAR类型      

           SQL> ALTER TABLE scott.emp

             2  MODIFY (jobid CHAR(20));

 

           Table altered.      

      

       d.修改列的默认值

           --jobid列的默认值Eng改为Engn

           SQL> ALTER TABLE scott.emp MODIFY (jobid varchar2(20) DEFAULT 'Engn');

 

           Table altered.

 

      

    3.删除列(一次只能删除一个列,无法删除属于SYS 的表中的列):

       ALTER TABLE tablename    DROP  COLUMN columnname;

           --删除jobid

           SQL> ALTER TABLE scott.emp DROP COLUMN jobid;

 

           Table altered.

   

    4.将一列设置成无用(UNUSED):

       ALTER TABLE tablename SET UNUSED (列名)或

       ALTER TABLE tablename SET UNUSED COLUMN 列名

           --robinson.emp表中deptno列设置为无用列

           SQL> ALTER TABLE robinson.emp SET UNUSED COLUMN deptno;

 

           Table altered.

      

    5.删除无用的列:

       ALTER TABLE tablename DROP UNUSED COLUMNS

 

       SQL> ALTER TABLE robinson.emp DROP UNUSED COLUMN;

 

       Table altered.

 

       SQL> ALTER TABLE robinson.emp DROP UNUSED COLUMNS;

 

       Table altered.      

 

三、对象改名:

 

    列改名:

       ALTER TABLE tablename RENAME COLUMN oldname TO newname

      

           --scott.emp的列sal改为salary

           SQL> ALTER TABLE scott.emp RENAME COLUMN sal TO salary;

 

           Table altered.      

 

    表改名:

       ALTER TABLE tablename RENAME TO newname

      

           SQL> CONN robinson/lion

           Connected.

           --将表名emp 改为employees

           SQL> ALTER TABLE emp RENAME TO employees;

 

           Table altered.

                 

           SQL> select table_name from user_tables;

 

           TABLE_NAME

           ------------------------------

           EMPLOYEES

           ORDERS

           EMP2

          

           --使用下面的方法也能够实现表的改名

           SQL> RENAME employees TO emp;

 

           Table renamed.

 

           SQL> select table_name from user_tables;

 

           TABLE_NAME

           ------------------------------

           EMP

           ORDERS

           EMP2  

          

    对于表、索引、视图、序列、同义词等对象可直接用RENAME

       格式:RENAME oldname TO newname

           如:RENAME test2 TO test;

 

 

四、移动表空间:

     ALTER TABLE tablename MOVE TABLESPACE tablespacename;

   

    查看表在哪个表空间:

       dba_tables

 

       --首先查看表位于哪个表空间      

       SQL> SELECT owner,table_name,tablespace_name FROM dba_tables 

         2  WHERE owner = 'ROBINSON';

 

       OWNER           TABLE_NAME                     TABLESPACE_NAME

       --------------- ------------------------------ ------------------------------

       ROBINSON        ORDERS                         USERS

       ROBINSON        EMP                            USERS

       ROBINSON        EMP2                           USERS

            

       --emp2表移动到tbs1表空间

       SQL> ALTER TABLE robinson.emp2 MOVE TABLESPACE TBS1;

 

       Table altered.

 

       --再次查看emp2已位于tbs1表空间

       SQL> SELECT owner,table_name,tablespace_name FROM dba_tables

         2  WHERE owner = 'ROBINSON';

 

       OWNER           TABLE_NAME                     TABLESPACE_NAME

       --------------- ------------------------------ ------------------------------

       ROBINSON        EMP2                           TBS1

       ROBINSON        ORDERS                         USERS

       ROBINSON        EMP                            USERS

 

五、表和列的注释

    使用COMMENT 语句给表或列添加注释

    表加注释:

       COMMENT ON TABLE tablename IS '.....'

 

       SQL> COMMENT ON TABLE emp IS 'Emp is table contain all employees.';

 

       Comment created.

 

    列加注释:

       COMMENT ON COLUMN tablename.columnname IS '......';

 

       SQL> COMMENT ON COLUMN emp.mgr IS 'MGR column is manager ID';

 

       Comment created.    

 

    可以通过下列数据字典视图查看所添加的注释:

       –ALL_COL_COMMENTS

       –USER_COL_COMMENTS

       –ALL_TAB_COMMENTS

       –USER_TAB_COMMENTS

      

           SQL> SELECT * FROM user_tab_comments;

 

           TABLE_NAME                     TABLE_TYPE  COMMENTS

           ------------------------------ ----------- -----------------------------------------

           EMP                            TABLE       Emp is table contain all employees.

           ORDERS                         TABLE

           EMP2                           TABLE

 

 

           SQL> SELECT * FROM user_col_comments WHERE table_name = 'EMP';

 

           TABLE_NAME                     COLUMN_NAME                    COMMENTS

           ------------------------------ ------------------------------ -------------------------

           EMP                            ENAME

           EMP                            JOB

           EMP                            HIREDATE

           EMP                            MGR                            MGR column is manager ID

 

六、截断(清空)表

    TRUNCATE TABLE tablename

 

    TRUNCATE TABLE 语句:

        删除表中所有的数据,但保留结构

        释放表的存储空间

        不触发表的删除触发器

        TRUNCATE语句不能回滚

         可以使用DELETE 语句删除数据

 

       SQL> SELECT COUNT(1) FROM emp;

 

         COUNT(1)

       ----------

              15

 

       SQL> TRUNCATE TABLE emp;

 

       Table truncated.

 

       SQL> SELECT COUNT(1) FROM emp;

 

         COUNT(1)

       ----------

               0

 

 

七、删除表:DROP TABLE tablename

    DROP TABLE dept80;

 

    数据和结构都被删除

    所有正在运行的相关事物被提交

    所有相关索引被删除

    DROP TABLE 语句不能回滚

    所有基于该表扣视图和别名依然保留但已无效

 

       SQL> DROP TABLE emp;

 

       Table dropped.

 

       SQL> SELECT * FROM emp;

       SELECT * FROM emp

                    *

       ERROR at line 1:

       ORA-00942: table or view does not exist

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22578826/viewspace-703552/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-05-18

  • 博文量
    192
  • 访问量
    455744