SQL Fundamentals: 表的创建和管理（表的基本操作，闪回技术flashback，表结构修改）
1、表的基本操作
（CREATE TABLE, DROP TABLE,TRUNCATE TABLE, RENAME tablename1 TO tablename2）
2、闪回技术(FLASHBACK TABLE,PURGE,recyclebin)
3、修改表结构（最好不要使用）
一、表的基本操作
表是现实世界的抽象
数据表是一种“行与列”数据的组合，也是数据库之中最为基本的组成单元，所有的数据操作（增加、修改、删除、查询）以及约束、索引等概念都要依附于数据表而存在，而数据表也可以理解为对现实或者是业务的抽象结果
表也是一种数据统计
Oracle常用数据类型（重点）
表中的最基本组成单元是字段，而每一个字段都有其类型.
oracle使用VARCHAR2, MYSQL使用的是VARCHAR
| 类型 | 长度 | 描述 |
| CHAR(n) | n= 1 to 2000（字节） | 保存定长的字符串 |
| VARCHAR2(n) | n= 1 to 4000（字节） | 可以放数字、字母以及ASCII码字符集，Oracle 12C开始，其最大支持32767字节长度 |
| NUMBER(n) |
| 表示证书，可以使用INT替代. |
| NUMBER(m,n) | m = 1 to 38 n = -84 to 127 | 表示数字，其中小数部分长度为m，整数部分长度为m-n位 可以用FLOAT替代 |
| DATE | - | 用于存放日期时间型数据（不包含毫秒） |
| TIMESTAMP | - | DATE包含日期，不包含时间； TIME包含时间，不包含日期； TIMESTAMP包含日期时间、毫秒 用于存放日期时间型数据（包含毫秒） |
| CLOB | 4G | 用于存放海量文字，例如：保存一部《红楼梦》、《三国演义》 表示大文本的数据. |
| BLOB | 4G | 用于保存二进制文件，例如：图片、电影、MP3等 保存二进制文件. |
创建表（重点）
表的创建属于DDL(数据定义语言)范畴，每创建数据表或者是表中的列都属于数据库之中的对象.
数据表的创建属于数据库对象的创建；
表的创建不受事务控制.
语法
| CREATE TABLE 用户名.表名称( 字段名称 字段类型 [DEFAULT 默认值] , 字段名称 字段类型 [DEFAULT 默认值] , ..... ) ; |
- 对于表名称及列名称的定义要求如下：
- 必须以字母开头；
- 长度为1~30个字符；
- 表名称由字母（A-Z、a-z）、数字（0-9）、_、$、#组成，而且名称要有意义；
- 对同一个用户不能使用相同的表名称；
- 不能是Oracle中的保留字，像CREATE、SELECT等都是作为保留字；
创建数据表
表结构：
创建语法：
| CREATE TABLE member ( mid NUMBER(5) , name VARCHAR2(50) DEFAULT '无名氏' , age NUMBER(3) , birthday DATE DEFAULT SYSDATE , note CLOB ); |
创建数据表后：
| 查看表是否存在： | SQL> SELECT * FROM tab; |
| 查看表的结构 | DESC member |
| 向表中插入数据 | INSERT INTO member(mid,name,age,birthday,note) VALUES(1,'WENDY',26,TO_DATE('1991-08-10','yyyy-mm-dd'),'borrow'); |
表的复制
表的复制操作只要是行与列的查询结果，就可以将其定义为数据表
- 在Oracle中，除了可以使用DDL创建新的数据表之外，也支持了复制已有数据表的支持
- 语法：CREATE TABLE 表名称 AS 子查询 ;
| 复制全表：将emp表复制成myemp表 |
| CREATE TABLE myemp1 AS SELECT * FROM emp ; |
| 复制部分表：复制部门为10的雇员信息 |
| CREATE TABLE myemp10 AS SELECT * FROM emp WHERE deptno=10 ; |
| 复制表结构(不复制数据)：只能编写一个永远不可能满足的条件 |
| CREATE TABLE employee1 AS SELECT * FROM emp WHERE 1=2 ; |
| 利用查询结果创建表：所有部门的统计信息单独保存到一张新的department表之中 |
| CREATE TABLE department AS SELECT d.deptno deptno,d.dname dname,d.loc loc, COUNT(e.empno) count, SUM(e.sal + NVL(e.comm,0)) sum, ROUND(AVG(e.sal + NVL(e.comm,0)),2) avg, MAX(e.sal) max, MIN(e.sal) min FROM dept d,emp e WHERE d.deptno=e.deptno(+) GROUP BY d.deptno,d.dname,d.loc ORDER BY d.deptno ; |
数据字典
- 所有的数据表都属于数据库对象，每当创建一张数据表的时候，会自动在指定的数据字典表执行一个增加语句（这个增加语言我们是不知道的），数据字典的数据操作只能通过命令完成，不能直接使用SQL完成.
- 在Oracle中专门提供了一组数据专门用于纪录数据库对象信息、对象结构、管理信息、存储信息的数据表，那么这种类型的表就称为数据字典，
- 在Oracle中一共定义了两类数据字典：
| 静态数据字典： | 这类数据字典由表及视图所组成，这些视图分三类：
| ||||||
| 动态数据字典： | 随着数据库运行而不断更新的数据表，一般用来保存内存和磁盘状态，而这类数据字典都以“v$”开头； |
为表重命名
- 在Oralcle之中，为了方便用户对数据表进行管理，所以专门提供了修改表名称的操作.
- 之所以可以修改名字，是由oracle存储结构决定.
- 修改表名称相当于修改了数据字典中的表的名称.
RENAME 旧的表名称 TO 新的表名称 ;
| 将member表修改为member1表 |
| RENAME member TO member1 ; |
当发生了任何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;命令查看
| 查看回收站中的数据 |
| SELECT object_name,original_name,operation,type FROM recyclebin ; |
| 恢复myemp表 |
| FLASHBACK TABLE myemp TO BEFORE DROP ; |
| 直接删除myemp表 |
| DROP TABLE myemp PURGE ; |
| 从回收站之中删除employee表 |
| PURGE TABLE employee ; |
| 清空回收站 |
| PURGE recyclebin ; |
三、修改表结构
- 表创建之后可以利用ALTER进行修改；
- 在表建立时可以设置注释信息；
- Oracle 12C增加了可用/不可用列的设置。
数据表属于oracle数据库对象，针对数据库的对象，操作语法只有三种：
- 创建：CREATE 对象类型 名称；
- 删除：DROP 对象类型 名称；
- 修改：ALTER 对象类型 名称.
ALTER指令尽量不用，在开发中修改表结构，把表删了重新建立比较好.
- 操作准备
| -- 删除数据表 DROP TABLE member PURGE ; -- 创建数据表 CREATE TABLE member ( mid NUMBER , name VARCHAR2(50) DEFAULT '无名氏' ) ; -- 增加测试数据 INSERT INTO member (mid,name) VALUES (1,'wendy') ; INSERT INTO member (mid,name) VALUES (2,'gaga') ; INSERT INTO member (mid,name) VALUES (3,'nacy') ; -- 提交事务 COMMIT ; |
- 为表中增加数据字段
- 为已有数据表增加字段的时候也像定义数据表一样，需要给出字段名称、类型、默认值，格式如下
- ALTER TABLE 表名称 ADD (字段名称 字段类型 DEFAULT 默认值,字段名称 字段类型 DEFAULT 默认值..) ;
- 范例：向member表中增加三个字段
- ALTER TABLE member ADD (age NUMBER(3)) ;
- ALTER TABLE member ADD (sex VARCHAR2(10) DEFAULT '男') ;
- ALTER TABLE member ADD (photo VARCHAR2(100) DEFAULT 'nophoto.jpg') ;
- 修改表中的字段
- 如果现在发现表中的某一列设计不合理的时候，也可以对已有的列进行修改，通过如下的语法完成
- ALTER TABLE 表名称 MODIFIY(字段名称 字段类型 DEFAULT 默认值) ;
- 范例：将name字段的长度修改为30，将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)
) ;
