SQL Fundamentals: 表的创建和管理（表的基本操作，闪回技术flashback，表结构修改）

SQL Fundamentals || Oracle SQL语言

1、表的基本操作

（CREATE TABLE, DROP TABLE,TRUNCATE TABLE, RENAME tablename1 TO tablename2）

2、闪回技术(FLASHBACK TABLE,PURGE,recyclebin)

Oracle 的闪回技术 --flashback

3、修改表结构（最好不要使用）

一、表的基本操作

表是现实世界的抽象

字节长度

NUMBER(n)

表示证书，可以使用INT替代.

NUMBER(m,n)

m = 1 to 38

n = -84 to 127

表示数字，其中小数部分长度为m，整数部分长度为m-n位

可以用FLOAT替代

DATE

用于存放日期时间型数据（不包含毫秒）

TIMESTAMP

DATE包含日期，不包含时间；

TIME包含时间，不包含日期；

TIMESTAMP包含日期时间、毫秒

用于存放日期时间型数据（包含毫秒）

CLOB

用于存放海量文字，例如：保存一部《红楼梦》、《三国演义》

表示大文本的数据.

BLOB

用于保存二进制文件，例如：图片、电影、MP3等

保存二进制文件.

创建表（重点）

表的创建属于DDL(数据定义语言)范畴，每创建数据表或者是表中的列都属于数据库之中的对象.

数据表的创建属于数据库对象的创建；

表的创建不受事务控制.

语法

CREATE TABLE 用户名.表名称(

字段名称字段类型 [DEFAULT 默认值] ,

.....

) ;

对于表名称及列名称的定义要求如下：

必须以字母开头；
长度为1~30个字符；
表名称由字母（A-Z、a-z）、数字（0-9）、_、$、#组成，而且名称要有意义；
对同一个用户不能使用相同的表名称；
不能是Oracle中的保留字，像CREATE、SELECT等都是作为保留字；

创建数据表

表结构：

SQL Fundamentals: 表的创建和管理（表的基本操作，闪回技术flashback，表结构修改）

创建语法：

CREATE TABLE member (

mid NUMBER(5) ,

的雇员信息

CREATE TABLE myemp10

SELECT * FROM emp WHERE deptno=10 ;

复制表结构(不复制数据)：只能编写一个永远不可能满足的条件

CREATE TABLE employee1

SELECT * FROM emp WHERE 1=2 ;

;

当发生了任何DDL操作的时候，对于事务都会自动提交，ROLLBACK不能回滚.

所有DDL操作不受事务的控制.

表复制属于一种DDL操作；
通过表复制操作，也可以将一个子查询转化为数据表保存。

截断表

使用DELETE FROM 表名;删除表后，可以使用rollback回滚，删除的同时所占用的资源（表空间资源，约束，索引等）都不会立刻被释放，如果希望立刻被释放资源，就需要截断表.
如果此时要清空一张表中的全部数据，肯定首先想到的是“DELETE FROM 表名称”这样的语法，然而这样的清空表数据的方法不仅需要的时间很长，而且一张表所占用的资源（例如：索引、约束等）也不会立刻释放掉，为此在Oracle之中提供了专门的截断表的操作

TABLE 表名称 ;

截断mldnuser表

TRUNCATE TABLE member2 ;

删除表（重点）

数据库之中的某些数据表不再使用的时候，则就可以通过如下的语法进行数据表的删除操作：

DROP TABLE 表名称 ;

范例：删除myemp表

DROP TABLE myemp ;

删除表后，查询表时会发现如下表名称.

SQL> SELECT * FROM tab;

TNAME TABTYPE CLUSTERID

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

BIN$Va2mEtdNdeDgU3ABqMCFcQ==$0 TABLE

二、闪回技术（Flashback）

闪回（Flashback）技术是Oracle 10g之后所提供的一种新的数据保障措施，在Oracle 10g之前，如果用户不幸将表误删了则数据表也就再也找不回来了，但是到了Oracle 10g之后，为了解决这种误删除所带来的数据丢失问题，专门提供了一个与windows操作系统类似的回收站功能，即：所有的数据表删除的话，会默认先将其保存在回收站之中，此时，如果用户发现删除有错误，则可以直接通过回收站进行表的恢复。
闪回是在Oracle 10g新增的功能，是一种防止表被误删除的操作手段；
ORACLE 10g可以通过SHOW RECYCLEBIN;命令查看

查看回收站中的数据

，将sex字段的默认值修改为女

ALTER TABLE member MODIFY(name VARCHAR2(30)) ;
ALTER TABLE member MODIFY(sex VARCHAR2(3) DEFAULT '女') ;

删除表中的字段

如果现在要想删除表中的一个列，可以通过如下的语法完成。

ALTER TABLE 表名称 DROP COLUMN 列名称 ;

范例：删除member表中的photo和age字段

ALTER TABLE member DROP COLUMN photo ;
ALTER TABLE member DROP COLUMN age ;

无用字段设置

如果数据表数据量很大，执行删除操作，性能损耗很大，为了保证表在大数据量的情况下删除操作可以使用，又不影响表的正常使用，可以将表中设置为无用的列.

将表中字段设置成无用状态

ALTER TABLE 表名称 SET UNUSED(列名称) ;
ALTER TABLE 表名称 SET UNUSED COLUMN 列名称;

范例：将sex列设置成无用状态

ALTER TABLE member SET UNUSED(sex) ;

范例：将name列设置成无用状态

ALTER TABLE member SET UNUSED COLUMN name ;

删除表中的无用列

语法：ALTER TABLE 表名称 DROP UNUSED COLUMNS ;
范例：删除member表中的无用（UNUSED）列

ALTER TABLE member DROP UNUSED COLUMNS ;

添加注释

oracle提供了一个user_tab_comments的数据字典，将所有的表的注释信息列出来.
oracle提供了一个user_col_comments的数据字典，将所有的列的注释信息列出来.

程序中使用注释可以帮助使用者更加清晰的了解代码的作用，而在Oracle数据库之中也可以为表或列设置注释

COMMENT ON TABLE 表名称 | COLUMN 表名称.列名称 IS '注释内容';

范例：定义数据库创建脚本

-- 删除数据表

DROP TABLE member PURGE ;

-- 创建数据表

CREATE TABLE member (

mid NUMBER ,

name VARCHAR2(50) DEFAULT '无名氏' ,

age NUMBER(3) ,

birthday DATE

) ;

范例：为member表添加注释

COMMENT ON TABLE member IS '用于记录参加活动的成员信息' ;

范例：为member表的mid添加注释信息

COMMENT ON COLUMN member.mid IS '参加活动的成员编号' ;

设置可见/不可见字段

如果某些数据列的内容不需要使用，那么直接为其设置null值数据即可，但是这样一来有可能会出现一个小问题，例如：在一张数据表设计的时候，考虑到日后需要增加若干个列，那么这些列如果提前增加的话，那么就有可能造成开发人员的困扰（不知道这些列做什么），为此就希望将这些暂时不使用的列定义为不可见的状态，这样开发人员在浏览数据时，只需要浏览有用的部分即可。当需要这些列时，再恢复其可见状态。在Oracle 12C之前，这些特性是不被支持的，而从Oracle 12C开始为了方便用户进行表管理，提供了不可见列的使用，同时用户也可以将一个可见列修改为不可见的状态。

设置可见与不可见操作

修改字段的可见状态

ALTER TABLE 表名称 MODIFY (字段 [INVISIBLE | VISIBLE]);

范例：将name字段设置为不可见状态

ALTER TABLE mytab MODIFY (name INVISIBLE);

范例：将name字段变为可见

ALTER TABLE mytab MODIFY (name VISIBLE);

范例：定义表是直接设置不可见字段

DROP TABLE mytab PURGE ;

CREATE TABLE mytab (

mid NUMBER ,

name VARCHAR2(30) INVISIBLE ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;