--=================================
--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 基础--> ROLLUP 与CUBE 运算符实现数据汇总
