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文字信息，最大４G

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

clob和blob许多操作是不能直接使用oracle的数据库命令来完成的，

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

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

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

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

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

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

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

raw 1-2000 字符。

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

long 和long raw 数据类型

long 可变大字符型数据，最大G，long 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.修改列的数据类型

--将jobid列VARCHAR2类型改为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

八、更多

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

SQL 基础--> 视图(CREATE VIEW)

Oracle 常用目录结构(10g)

Oracle 用户、对象权限、系统权限

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

Oracle 角色、配置文件