11表的创建与管理
<h1>表的创建与管理</h1>
<h2>表的基本概念</h2>
<ul>
<li>表是由行与列组成，表中的数据操作包括增加、删除、查询、修改、约束、索引等。</li>
<li>表的出现形式可以是对现象事物的一种抽象（汽车颜色与价钱的抽象），可以是对数据的统计（世界杯冠军球队的统计）。</li>
<li>表之间还可以有关联关系，在第12章学习</li>
</ul>
<h2>Oracle 常用的数据类型</h2>
<ul>
<li>CHAR:保存定长的字符串。</li>
<li>VARCHAR2(n):可以存放数字、字母、ACSII码、一般存放姓名、地址、E-mail等这样的信息。</li>
<li>NUMBER(m,n):m代表整数，n代表小数，可以只有整数，可以分别用INT、FLOAT代替。</li>
<li>DATE:存放日期，但不包含毫秒。</li>
<li>TIMESTAMP:存放日期，包含毫秒，时间戳。</li>
<li>CLOB:存放大量的文本，例如一部小说文本。</li>
<li>BLOB:存放二进制文件，例如图片、电影、音乐。</li>
</ul>
<p><strong>常用数据类型：</strong>VARCHAR2、NUMBER、DATE、CLOB。</p>
<h2>表的创建</h2>
<ul>
<li>
<p>数据表又称为数据对象，创建表属于DDL语句，对表的名称及列的名称有要求如下：
1、必须以字母开头。
2、长度为1~30个字符。
3、表名称可以是字母、数据、_、$、#组成，而且名称要有意义。
4、对同一个用户不能使用相同的表名称。
5、不能是Oracle 中的保留字，如CREATE、SELECT等都是保留字。</p>
</li>
<li>Oracle 数据库支持中文对象的创建，但是不能这样做。</li>
<li>创建一张表示例：
<pre><code>create table member(
mid NUMBER(5),
name VARCHAR2(50) default '无名氏',
age NUMBER(3),
birthday date default sysdate,
note CLOB
);</code></pre></li>
</ul>
<p><strong>注意：</strong>对DDL语句的操作，事务不起任何作用。<strong>在Oracle 中，执行任何DDL 语句，所有没有提交的事务都将自动提交。</strong></p>
<ul>
<li>执行一些操作，查看表的结构
<pre><code>select * from member;
select * from tab; --查看当前用户下所有的表
desc member ; --查看表的结构，在PL/SQL 中无法实现</code></pre></li>
</ul>
<p><strong>提示：</strong>通过create 创建的都是对象，表是其中一种，oracle 中还有其他的对象。</p>
<ul>
<li>
<p>向表中插入数据：</p>
<pre><code>insert into member (mid, name, age, birthday, note) values (1,'李兴华',30,to_date('1979-09-27','yyyy-mm-dd'),'总公司活动倡导者');
insert into member (mid, name, age, birthday, note) values (1,'董鸣楠',29,to_date('1980-08-13','yyyy-mm-dd'),'积极响应者');
insert into member (mid, age, note) values (3,35,'活动名单提供者');
commit;
select * from member;</code></pre>
</li>
<li>表的复制，子查询是正常的，都能复制。
<pre><code>create table myemp as select * from emp;
select * from myemp;
create table employee as select * from emp where 0=1 ;--只复制结构，不需要数据
select * from employee ;
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 emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno, d.dname, d.loc
order by d.deptno;</code></pre></li>
</ul>
<h2>数据表重命名</h2>
<ul>
<li>变更表名的操作</li>
</ul>
<pre><code>rename member to mldnuser;
select * from tab;
select * from mldnuser;</code></pre>
<p><strong>数据字典：</strong>用于记录数据库的操作信息，修改表名称就是修改数据字典。</p>
<ul>
<li>静态数据字典：由表和视图组成，分为三类。
user_*:存储所有当前用户的对象信息。
all_*:存储所有当前用户可以访问的对象信息（某些对象可能不属于此用户）。
dba_*:存储数据库中所有对象的信息（数据库管理员操作）。</li>
<li>动态数据字典：随着数据库的运行而不断更新的数据表，一般用来保存内容和磁盘状态，而这类数据字典都以<code>“V$”</code>开头。</li>
</ul>
<p><strong>数据字典的操作：</strong>都是通过特定人命令来操作的，如下两个例子：</p>
<pre><code>select * from tab;
desc 表名称；--desc是简写
rename member to mldnuser;</code></pre>
<h2>截断表（truncate）</h2>
<p><strong>截断表：</strong>意思是删除表中的数据及索引、约束等，此操作会很危险。占用资源立刻释放。</p>
<p><strong>truncate与delete的区别：</strong></p>
<ul>
<li>truncate 是oracle 特有的，不是SQL 标准语句。delete 是标准SQL 语句。开发中推荐使用。</li>
<li>truncate 与delete 达到的效果不一样，delete 只删除表中数据，truncate 会立刻释放资源（索引、约束等）。</li>
</ul>
<h3>操作示例：</h3>
<pre><code>create table member as select * from mldnuser;
truncate table mldnuser;
select * from mldnuser;</code></pre>
<h2>表的删除</h2>
<p><strong>表的删除：</strong>表的删除和创建属于DDL 操作，不受事务的处理，执行后无法通过回滚进行恢复，表的删除包括数据及表本身的结构。</p>
<ul>
<li>示例：
<pre><code>drop table myemp ;-- 删除表
select * from tab; --查看表名</code></pre></li>
</ul>
<h2>闪回技术(flashback)</h2>
<p><strong>闪回技术：</strong>使用drop 删除一张表后，表会存放在回收站(recyclebin)中，以便以恢复数据。这种恢复数据就叫闪回技术。就相当于windows 系统下的回收站。</p>
<h3>闪收技术的一些特性</h3>
<ul>
<li>Oracle 10g 之后才出现的，相当于回收站，表名是(recyclebin)。</li>
<li>recyclebin 表中有多个字段，记录着多种情况。</li>
<li>使用闪回技术恢复回收站的表，<code>flashback table DEPARTMENT to before drop ;</code>。</li>
<li>可以使用<code>drop table 表名 purge</code> 的方式跳过回收站，彻底删除一张表。</li>
<li>可以一次性<strong>清空回收站</strong><code>purge recyclebin;</code>，还是不要执行这个好。</li>
<li>可以通过指定表名<strong>删除</strong>回收站中的表<code>purge table 表名称</code>。</li>
</ul>
<h3>一些操作：</h3>
<pre><code>select * from recyclebin;--查看回收站中删除的表的记录
purge recyclebin; --一次性清空回收站所有的删除表记录
select * from tab; --再次查询删除记录也不见了
select * from myemp ; --看一眼myemp 表
drop table myemp ; --删除myemp 表
select * from tab; --可以在这里看到删除表的记录
select object_name,original_name,operation,type from recyclebin;--查看回收站的删除记录
flashback table DEPARTMENT to before drop ; --使用闪回技术恢复回收站的表
select * from myemp; --查看一下表确实已经恢复，回收站(recyclebin)中也没有记录了，表记录(tab)中也没有删除记录了。
drop table myemp purge ;--不通过回收站，直接彻底删除表，回收站(recyclebin)、表记录(tab)中都没有记录
drop table department ;--再删除一张表
purge table department ;--从回收站(recyclebin)中清除表department
purge recyclebin ;--清除回收站(recyclebin)中所有表</code></pre>
<h2>修改表结构</h2>
<h3>为表中增加数据字段</h3>
<ul>
<li>可以一列一列地加，可以多列一起加</li>
<li>语法是<code>alter table 表名 add(字段名 属性,字段名 属性)</code></li>
<li>设置的默认值default 也会同时添加到列中</li>
</ul>
<pre><code>alter table member add(age number(3),name varchar2(50));
alter table member add(sex varchar2(10) default '男',photo varchar2(100) default 'nophoto.jpg');
desc member;--查看表结构，要在sqlplus 中才可以执行
select * from member ;--设置为default 的值也会insert 到对应的列中</code></pre>
<p><strong>问题：</strong>为什么photo 列不设置为blob 类型？因为设计上这一列只存放图片的文件名就好。</p>
<h3>修改表中的字段</h3>
<ul>
<li>可以一个语句修改多个字段。</li>
<li>在工具上点列看不到效果，要编辑表才能看到效果。</li>
<li>语法1是<code>alter table 表名 modify (列名 属性,列名 属性)</code></li>
</ul>
<pre><code>alter table member modify (name varchar2(40));
alter table member modify (photo varchar2(50));
alter table member modify (sex varchar2(10) default '女',age number(3));
select * from member ;</code></pre>
<h3>删除表中的字段</h3>
<ul>
<li>删除到最后至少要保留一个以上的字段</li>
<li><del>只能一列一列地删除</del> 使用<code>alter table products drop (name,cost);</code> 命令可以删除多列。</li>
<li>同时表的列的数据也被删除</li>
<li>数据一多就会慢，所以还有一个功能是设置列为不可见UNUSED，可以多列一起设置。</li>
<li>还没找到设置列为USED 的方法。</li>
<li>还可以一次性删除所有已经设置为UNUSED 的列。</li>
<li>删除列的语法<code>alter table 表名 drop column 列名</code>。</li>
<li>设置列不可用语法<code>alter table 表名 set unused(列名，列名)</code>。</li>
<li>删除所有不可见列的语法<code>alter table 表名 drop unused columns</code>。</li>
</ul>
<pre><code>alter table member drop column photo;
alter table member drop column age;
--删除列会把数据也删除，如果数据量很大，会很慢，可以设置一列为UNUSED，可以多列一起设置
alter table member set unused (sex,name);--暂时没有恢复的方法
alter table member set unused column age;--另一种设置无用方法
alter table member drop unused columns;--删除所有已经设置为无用的列，再去重新创建</code></pre>
<h2>为表&列添加注释</h2>
<ul>
<li>记录表和列的注释的数据字典分别是<code>user_tab_comments</code>&<code>user_col_comments</code>。</li>
<li>添加表的注释语法<code>comment on table 表名 is '注释内容'；</code></li>
<li>添加表的列注释语法<code>comment on column 表名.列名 is '注释内容';</code>。</li>
</ul>
<pre><code>-- 重新创建表
drop table member ;
create table member (
mid number,
name varchar2(50) default '无名氏' ,
age number(3),
birthday date
);
select * from member;
select * from user_tab_comments where table_name='MEMBER';
comment on table member is '用于记录参加活动的成员信息' ;
select * from user_col_comments where table_name='MEMBER';
comment on column member.mid is '参加添加成员编号';</code></pre>
<h2>设置可见/不可见字段(Oracle 12c才有这个功能)</h2>
<ul>
<li>语法<code>alter table 表名 modify(列名 invisible)</code>，invisible 是不可见，visible 是可见。</li>
<li>记录表的字段的数据字典是<code>user_tab_column</code>。</li>
<li>可以在创建表的时候就设置为不可见，或者创建表后通过修改字段的方法进行设置列不可见。</li>
<li>列不可见，与前面学习的列不可用要区分开来。</li>
</ul>
<h3>执行的一些sql 语句</h3>
<pre><code>create table mytab (
mid number,
name varchar2(30),
constraint pk_mid primary key(mid)
);--创建一张表，这里设置主键是第一次学到
select * from mytab;--查看表有两个字段
alter table mytab modify (name invisible);--设置name列为不可见后
desc mytab; --这两种方法都看不到name 列了
select * from mytab;--看不到name 列
select column_name from user_tab_columns where table_name='MYTAB';--这样才可以看到name 列
alter table mytab modify (name visible);--设置name 列为可见
insert into mytab values(1); --现在执行这个会报错
alter table mytab modify (name invisible) ;
insert into mytab values (1); --现在执行就不再报错了</code></pre>
<h2>表空间</h2>
<ul>
<li>
<p>查看表空间的一个语句：
<code>select tablespace_name,extent_management,status,contents from dba_tablespaces;--查看表空间</code></p>
</li>
<li>运行失败，过后再学</li>
</ul>
<h2>外部表</h2>
<p><a href="https://blog.csdn.net/cpgl40148/article/details/100263829">利用ORACLE_DATAPUMP为驱动创建外部表</a></p>
<h2>小结</h2>
<ul>
<li>数据库中常用的数据类型NUMBER、VARCHAR2、DATE、CLOB。</li>
<li>表的创建由CREATE TABLE完成，可以由DEFAULT设置默认值。</li>
<li>表的删除用drop table名完成，有缓冲的。如要直接去掉缓存，可以使用PURGE配置。</li>
<li>通过FlashBack（闪回技术）恢复误删除的表或者表中的数据。</li>
<li>通过截断表（TRUNCATE）操作可以立即释放表中所占用的资源。创建表时默认是存放在USERS表空间，也可以最后通过TABLESPACE 配置指定数据表所存放的位置。</li>
</ul>