11. 数据表的创建与管理（DDL）

之前我们使用的都是系统提供的数据表，实际环境中许哟啊自定义数据表，但是需要注意的是，数据表的定义属于SQL的DDL(数据定义语言)类，其不受到事务的控制，也就是说DML的操作才会受到事务的控制。
在Oracle中，如果现在的事务被提交，并发生了DDL操作，所有未提交的事务将自动提交。
实际开发中，数据表的创建要么是在数据库系统安装完成时做这样的操作，或者最简单的方式就是停机维护。
例如：
在会话1中对emp表执行update操作，会开启一个事务，在会话2中也对dept表执行update操作，对emp表执行instert操作，都会开启相应的事务在会话3中在对emp表执行delete操作，也会开启一个事务。
此时在会话1中执行commit，当前事务结束，并在次执行create table操作（此操作属于ddl中的定义语言，而ddl定义语言的特点是只要发生了ddl操作，所有未提交的事务都将自动提交），因此会导致会话2和会话3中的执行的操作（不管是进行中的操作还是等待的的操作）都会直接commit。

4.1常用数据类型

创建表其实就是创建表结构
数据表是由一堆数据所组成，每一个数据都要求由自己的数据类型。

4.1.1常用的数据类型

1.VARCHAR2(n)
一般要保存200个字以内的数据，都采用这种类型。(Oracle叫VARCHAR2,其他数据库VARCHAR),----》姓名，地址，新闻关键字
例如：Varchar（10）----》表示此字段只能存10个字符
2.NUMBER
直接编写NUMBER表示的就是数字，包括小数和整数
1）NUMBER(n)：表示最多N位整数
-----》number（2）----最多2位整数
2）NUMBER(n，m)：表示m小数位，n-m的整数位
------》number（7，2）----5位整数和2位小数
3.DATE
描述日期时间
1）Oracle中的DATE类型是包含有时间数据的
------》类似于 08-SEP-81+时分秒
2）其他数据库的DATE，只有日期
-----》类似于08-SEP-81
3）MYSQL中有DATE（只显示日期）和TIME（只显示时间），同功能为DATETIME（日期+时间）

4.CLOB
描述大文本数据（4G）
可以保存大的字符文件---->字符大对象（Character large object）
5.BLOB
描述二进制数据（4G）-----（binary large object）
如：电影，音乐，文字等，一般不考虑BLOB类型，不建议使用

4.2创建数据表

4.2.1创建数据表语法

定义列名称时尽可能见名知意，列名称的定义一般建议字母开头，中文也可以，但不能使用。
CREATE TABLE 表名称（
列名称 数据类型 【default 默认值】，
列名称 数据类型 【default 默认值】，
列名称 数据类型 【default 默认值】，
列名称 数据类型 【default 默认值】，
。。。。
列名称 数据类型 【default 默认值】----->最后一行定义的列名称不能写逗号
）;

4.2.2例如

1.数据表student的创建
SQL> CREATE TABLE student(
2 name VARCHAR2(5),
3 id NUMBER(4),
4 sex VARCHAR2(2)
5 ) ;

查看创建的表

查看创建的student表结构

2.数据表member的创建
SQL> CREATE TABLE member(
2 mid VARCHAR2(20),
3 name VARCHAR2(50) DEFAULT ‘no name’,-----没有名字默认是no name
4 age NUMBER(3),
5 salary NUMBER(10,2),
6 birthday DATE DEFAULT SYSDATE,
7 note CLOB
8 );

给数据表中添加数据yyn数据

在member表中查看数据

如果查看有问题，可以使用col 字段名 for a数字—修改列的大小

如果在创建的时候不写名字----默认就是no name

此时在创建数据表是采用的默认值只是数据本身的一种实现，在实际环境中作用不大。

4.3复制表

在之前的操作中可以自定义数据表，但是可以在数据库中支持一种简便的方法，就是复制表，可以根据已有的数据表或者查询语句来进行数据表的动态创建。
1.复制表的基本语法
CREATE TABLE + 新表名 AS 子查询
比如：CREATE TABLE yynemp AS SELECT * FROM emp；
新表名必须以字母开头
2.例如
1）创建一个名为yynemp的表
SQL> CREATE TABLE yynemp AS SELECT * FROM emp;

2）查询新创建的yynemp的表
SQL> SELECT * FROM tab;

查看yyntmp表中的数据

2）将部门20的雇员复制到emp1表中
SQL> CREATE TABLE emp1 as select * from emp where deptno=20;

以上的表操作都是根据已有的表进行了复制，甚至在做表创建的时候也可以操作复杂一些，也可以直接用自查询的结果创建。
4）将emp表结构复制到emp2中
此时只需要给定一个永远为假的条件就行，不管是什么条件只要是假的条件就可以。

只是将表结构复制过来，没有复制数据----》因此没有数据

5）创建一张数据表，将部门表数据统计结构保存在该表中

统计月信息的时候，可以采用此种模式，将长时间的操作结构保存在数据表中，方便浏览
（1）SQL> select deptno dno,count(empno) count
2 from emp
3 group by deptno;

（2）SQL> select d.deptno,d.dname,d.loc,temp.count
2 from dept d,(
3 select deptno dno,count(empno) count
4 from emp
5 group by deptno
6 )temp
7 where d.deptno=temp.dno(+);

3）SQL> create table yyntest as
2 select d.deptno,d.dname,d.loc,temp.count
3 from dept d,(
4 select deptno dno,count(empno) count
5 from emp
6 group by deptno
7 )temp
8 where d.deptno=temp.dno(+);

4）查看yyntest表信息

4.3修改表

在创建表之后，需要更改表结构，或更改删除列，可以使用alter table语句完成

4.3.1数据语法格式

使用 alter table 语句可以添加数据列，修改或删除列
1.添加数据列的语法
ALTER TABLE 表名 ADD （
新的字段 新的数据类型 【DEFAULT 默认值】，
新的字段 新的数据类型 【DEFAULT 默认值】，
新的字段 新的数据类型 【DEFAULT 默认值】，
新的字段 新的数据类型 【DEFAULT 默认值】
。。。。
）；
使用此方法添加的列为数据表里的最后一列

2.修改数据列的语法
ALTER TABLE 表名 MODIFY （
新的列名 新的数据类型 【DEFAULT 默认值】，
新的列名 新的数据类型 【DEFAULT 默认值】，
新的列名 新的数据类型 【DEFAULT 默认值】，
新的列名 新的数据类型 【DEFAULT 默认值】
。。。。
）；
3.删除数据列的语法
ALTER TABLE 表名 DROP （列名）；

使用DROP语句进行删除操作，那么数据表中所有数据和结构都会被删除，所有待定事务处理都会被提交。所有索引都会被删除。无法回退drop table语句

4.删除表的所有行的语法
Truncate TABLE 表名；
Truncate table 语句是另一个用于删除表的所有行并且释放该表使用的存储空间的ddl语句，如果使用truncate语句，无法回退行删除操作。
Delete table 语句也可以删除表里所有的行，但不会释放存储空间。
Truncate table 命令要快一些，删除表里的所有行不会激活表的删除触发器。

4.5重命名表

4.5.1重名名语句格式

RENAME 原来的表名 TO 新的表名
原来的表名可以是表，视图，序列，新的表名也可以是这些。

alter table 旧表名 rename to 新名
4.6面试三种删除语句的区别（delete/truncate/drop）
1.Delete ：删除----》属于DML语句----》会触发事务
保证事务具有回滚的特性，有回滚的特性
2.Truncate：截断----》属于DDL语句----》不会触发事务
不存在回滚
3.Drop：删表-----》属于DDL语句-----》不会触发事务
不存在回滚
4.DML语句和DDL语句的区别是是否被事务管理
保证事务具有回滚的特性，有回滚的特性
1．语言+事务区别
1）Delete属于DML操作，具有事务特性
2）Truncate和drop 属于DDL操作，不被事务管理
2.速度区别
1）Delete被事务管理，如果执行delete操作，会产生undo信息（还原数据），记录还原数据要 保存在undo表空间的数据文件中。
Undotbs2表空间----》包含undotbs2数据文件----》数据文件保存在硬盘中
Delete操作会涉及到大量的物理IO（记录undo落盘），执行的速度很慢
比如：要删除1万行数据，那么这对应的1万行还原数据要往硬盘里存放，执行的速度很慢
2）Truncate/drop这2个操作不被事务管理，删除数据不产生undo信息，删除速度很快
3.不同角度的区别
从事务角度来考虑数据安全问题：rollback
1）Delete更安全
2）Truncate和drop更危险
从操作角度来考虑：
1）truncate和drop性能更高
2）delete性能差
4.操作对象不同
1）delete针对行记录操作
Delete from 表名 where 条件
2）truncate和drop针对表操作
Truncate table 表名
Drop table 表名
5.保留结构+释放空间的区别
1）Delete 和truncate 会删除数据，但是保留表结构
Delete from test -----》test 所有行记录被删除—》保留表结构
----》delete只删除段中数据块中的行记录
----》如果一个段有1万个数据块，使用delete删除操作，段中还是这些数据块
Truncate table test ----》test所有行记录被删除—》保留表结构
----》truncate 会回收数据表的高水位线，truncate不仅删除段中数据块中的行记录，还会释放段中的数据块
----》如果1个段有1万个数据块，使用truncate删除操作，段中保留的就是和该表结构的块，其他数据块，都没有了。
2）drop会删除数据和表结构
Drop table test ----》test表被删除
6.举例
Test表—》sys用户下存在test表
1）delete操作实验

3）drop操作实验

A）回收站（recyclebin）（必须在普通用户下操作）
Sys用户和system用户默认不使用回收站功能，删除就是删除了，
新建普通用户yyn
Create user yyn identified by 123456；

给普通用户yyn权限
SQL> grant dba to yyn;

创建表，并使用drop删除，然后在查看

使用show recyclebin—》查看回收站中的内容
11g引入回收站机制，删除表时，并没有真的删除表，而是进行了重命名
BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0----》这个是回收站的表名

SQL> select count(*) from “BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0”;
通过查看回收站的表名，数据还是可以统计出来，回收站的表名需要用双引号引起来

查看原来的test表，就是该表不存在

B）还原drop表的机制（flashback）
使用flashback table “回收表名“ to before drop；
SQL> flashback table “BIN$s4cUYbLjrUfgU8aAqMBY2Q==$0” to before drop;

查看回收站SQL> show recyclebin

Test表又可以查询到

C）清空回收站（purge）
SQL> purge recyclebin;

4）truncate操作实验

7.危险级别
第一truncate 第二drop 第三 delete
安全性排序：
Truncate < drop < delete