Oracle数据库-第11章-表的创建与管理

1、Oracle数据库第11章：表的创建与管理表是现实世界的抽象n数据表是一种“行与列”数据的组合，也是数据库之中最为基本的组成单元，所有的数据操作（增加、修改、删除、查询）以及约束、索引等概念都要依附于数据表而存在，而数据表也可以理解为对现实或者是业务的抽象结果，例如，如果说现在希望抽象出现实世界中汽车的抽象模型表也是一种数据统计n例如，现在希望做一张数据表，可以记录下所有获得世界杯冠军的球队。No.球队球队举办国家举办国家年份年份1乌拉圭国家足球队乌拉圭1930年2意大利国家足球队意大利1934年3意大利国家足球队法国1938年4乌拉圭国家足球队巴西1950年5联邦德国足球队瑞士1954年6巴

2、西国家足球队瑞典1958年Oracle常用数据类型No.类型类型长度长度描述描述1CHAR(n)n= 1 to 2000（字节）保存定长的字符串2VARCHAR2(n)n= 1 to 4000（字节）可以放数字、字母以及ASCII码字符集，Oracle 12C开始，其最大支持32767字节长度3NUMBER(m,n)m = 1 to 38n = -84 to 127表示数字，其中小数部分长度为m，整数部分长度为m-n位4DATE-用于存放日期时间型数据（不包含毫秒）5TIMESTAMP-用于存放日期时间型数据（包含毫秒）6CLOB4G用于存放海量文字，例如：保存一部红楼梦、三国演义7BLOB4

3、G用于保存二进制文件，例如：图片、电影、MP3等创建表语法n CREATE TABLE 用户名.表名称(字段名称字段类型DEFAULT默认值 ,字段名称字段类型DEFAULT默认值 ,.) ;n 对于表名称及列名称的定义要求如下：必须以字母开头；长度为130个字符；表名称由字母（A-Z、a-z）、数字（0-9）、_、$、#组成，而且名称要有意义；对同一个用户不能使用相同的表名称；不能是Oracle中的保留字，像CREATE、SELECT等都是作为保留字；创建数据表n 表结构n 创建语法：CREATE TABLE member (midNUMBER(5),nameVARCHAR2(50) DEF

4、AULT无名氏 ,ageNUMBER(3),birthdayDATEDEFAULTSYSDATE ,noteCLOB);membermembermidnameagebirthdaynote.NUMBER(5)VARCHAR2(50)NUMBER(3)DATECLOB表的复制n在Oracle中，除了可以使用DDL创建新的数据表之外，也支持了复制已有数据表的支持语法：CREATE TABLE 表名称 AS 子查询 ;表复制 范例n将emp表复制成myemp表CREATE TABLE myemp AS SELECT * FROM emp ;表复制 范例n要求按照emp的表结构建立一张employee

5、表，但是不需要emp表中的任何数据，但是要求表的结构与emp表一样，但是不能存在emp表中的记录 即：只复制表结构CREATE TABLE employee AS SELECT * FROM emp WHERE 1=2 ;表复制 范例n所有部门的统计信息单独保存到一张新的department表之中CREATE TABLE department AS SELECT d.deptno deptno,d.dname dname,d.loc loc,COUNT(e.empno) count, SUM(e.sal + NVL(m,0) sum,ROUND(AVG(e.sal + NVL(m,0),2)

6、avg, MAX(e.sal) max, MIN(e.sal) minFROM dept d,emp e WHERE d.deptno=e.deptno(+)GROUP BY d.deptno,d.dname,d.locORDER BY d.deptno ;为表重命名n在Oracle之中，为了方便用户对数据表进行管理，所以专门提供了修改表名称的操作RENAME 旧的表名称 TO 新的表名称 ;将member表修改为mldnuser表RENAME member TO mldnuser ;数据字典n 在Oracle中专门提供了一组数据专门用于纪录数据库对象信息、对象结构、管理信息、存储信息的数据表

7、，那么这种类型的表就称为数据字典，在Oracle中一共定义了两类数据字典：静态数据字典：这类数据字典由表及视图所组成，这些视图分三类：user_*：存储了所有当前用户的对象信息；all_*：存储所有当前用户可以访问的对象信息（某些对象可能不属于此用户）；dba_*：存储数据库之中所有对象的信息（数据库管理员操作）；动态数据字典：随着数据库运行而不断更新的数据表，一般用来保存内存和磁盘状态，而这类数据字典都以“v$”开头；截断表n 如果此时要清空一张表中的全部数据，肯定首先想到的是“DELETE FROM 表名称”这样的语法，然而这样的清空表数据的方法不仅需要的时间很长，而且一张表所占用的资源（

8、例如：索引、约束等，在随后的章节会讲解到）也不会立刻释放掉，为此在Oracle之中提供了专门的截断表的操作TRUNCATE TABLE 表名称 ;n 截断mldnuser表TRUNCATE TABLE mldnuser ;小结n常用数据类型：NUMBER、VARCHAR2、DATE、CLOB；n数据表的创建属于数据库对象的创建；n创建表语法：CREATE TABLE ；n表复制属于一种DDL操作；n通过表复制操作，也可以将一个子查询转化为数据表保存。表的删除n数据库之中的某些数据表不再使用的时候，则就可以通过如下的语法进行数据表的删除操作：DROP TABLE 表名称 ;n范例：删除myemp

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

10、on,type FROM recyclebin ;闪回技术 范例n恢复myemp表FLASHBACK TABLE myemp TO BEFORE DROP ;闪回技术 范例n直接删除myemp表DROP TABLE myemp PURGE ;闪回技术 范例n从回收站之中删除employee表PURGE TABLE employee ;闪回技术 范例n清空回收站PURGE recyclebin ;小结n闪回是在Oracle 10g新增的功能，是一种防止表被误删除的操作手段；操作准备n 为了方便演示数据表的修改操作，下面首先编写如下的数据库创建脚本，并通过此表进行操作。- 删除数据表删除数据表DR

11、OP TABLE member PURGE ;- 创建数据表创建数据表CREATE TABLE member (midNUMBER,nameVARCHAR2(50)DEFAULT无名氏 ) ;- 增加测试数据增加测试数据INSERT INTO member (mid,name) VALUES (1,李兴华) ;INSERT INTO member (mid,name) VALUES (2,董鸣楠) ;INSERT INTO member (mid,name) VALUES (3,王月清) ;- 提交事务COMMIT ;为表中增加数据字段为表中增加数据字段n 为已有数据表增加字段的时候也像定义数

12、据表一样，需要给出字段名称、类型、默认值，格式如下ALTER TABLE 表名称 ADD (字段名称 字段类型 DEFAULT 默认值,字段名称 字段类型 DEFAULT 默认值.) ;n 范例：向member表中增加三个字段ALTER TABLE member ADD (ageNUMBER(3) ;ALTER TABLE member ADD (sexVARCHAR2(10)DEFAULT 男) ;ALTER TABLE member ADD (photoVARCHAR2(100) DEFAULT nophoto.jpg) ;修改表中的字段修改表中的字段n 如果现在发现表中的某一列设计不合理

13、的时候，也可以对已有的列进行修改，通过如下的语法完成ALTER TABLE 表名称 MODIFIY(字段名称 字段类型 DEFAULT 默认值) ;n 范例：范例：将name字段的长度修改为30，将sex字段的默认值修改为女ALTER TABLE member MODIFY(name VARCHAR2(30) ;ALTER TABLE member MODIFY(sex VARCHAR2(3)DEFAULT 女) ;删除表中的字段删除表中的字段n如果现在要想删除表中的一个列，可以通过如下的语法完成。ALTER TABLE 表名称 DROP COLUMN 列名称 ;n范例：范例：删除member

14、表中的photo和age字段ALTER TABLE member DROP COLUMN photo ;ALTER TABLE member DROP COLUMN age ;无用字段设置n将表中字段设置成无用状态ALTER TABLE 表名称 SET UNUSED(列名称) ;ALTER TABLE 表名称 SET UNUSED COLUMN 列名称;n范例：范例：将sex列设置成无用状态ALTER TABLE member SET UNUSED(sex) ;n范例：范例：将name列设置成无用状态ALTER TABLE member SET UNUSED COLUMN name ;删除表中

15、的无用列n语法：ALTER TABLE 表名称 DROP UNUSED COLUMNS ;n范例：范例：删除member表中的无用（UNUSED）列ALTER TABLE member DROP UNUSED COLUMNS ;添加注释n 程序中使用注释可以帮助使用者更加清晰的了解代码的作用，而在Oracle数据库之中也可以为表或列设置注释COMMENT ON TABLE 表名称 | COLUMN 表名称.列名称 IS 注释内容;n 范例：范例：定义数据库创建脚本- 删除数据表删除数据表DROP TABLE member PURGE ;- 创建数据表创建数据表CREATE TABLE memb

16、er (midNUMBER,nameVARCHAR2(50)DEFAULT无名氏 ,ageNUMBER(3),birthday DATE) ;添加注释范例n范例：范例：为member表添加注释COMMENT ON TABLE member IS 用于记录参加活动的成员信息 ;n范例：范例：为member表的mid添加注释信息COMMENT ON COLUMN member.mid IS 参加活动的成员编号 ;设置可见/不可见字段n 如果某些数据列的内容不需要使用，那么直接为其设置null值数据即可，但是这样一来有可能会出现一个小问题，例如：在一张数据表设计的时候，考虑到日后需要增加若干个列，那

17、么这些列如果提前增加的话，那么就有可能造成开发人员的困扰（不知道这些列做什么），为此就希望将这些暂时不使用的列定义为不可见的状态，这样开发人员在浏览数据时，只需要浏览有用的部分即可。当需要这些列时，再恢复其可见状态。在Oracle 12C之前，这些特性是不被支持的，而从Oracle 12C开始为了方便用户进行表管理，提供了不可见列的使用，同时用户也可以将一个可见列修改为不可见的状态。设置可见与不可见操作n 修改字段的可见状态ALTER TABLE 表名称 MODIFY (字段 INVISIBLE | VISIBLE);n 范例：范例：将name字段设置为不可见状态ALTER TABLE myt

18、ab MODIFY (name INVISIBLE);n 范例：范例：将name字段变为可见ALTER TABLE mytab MODIFY (name VISIBLE);n 范例：范例：定义表是直接设置不可见字段DROP TABLE mytab PURGE ;CREATE TABLE mytab (midNUMBER ,nameVARCHAR2(30)INVISIBLE ,CONSTRAINT pk_mid PRIMARY KEY(mid) ;小结n表创建之后可以利用ALTER进行修改；n在表建立时可以设置注释信息；nOracle 12C增加了可用/不可用列的设置。表空间n 数据库的运行需要

19、依赖于操作系统，而数据库本身也保存在了操作系统的磁盘上，所以当用户向数据表中保存数据时，最终数据也还是保存在了磁盘上，只不过这些数据是按照固定的格式进行保存。Oracle数据保存在磁盘上的格式如图所示。n 在数据库数据和磁盘数据之间存在了两种结构：逻辑结构：逻辑结构：Oracle中所引入的结构，开发人员所操作的都只针对于Oracle的逻辑结构；物理结构：物理结构：操作系统所拥有的存储结构，而逻辑结构到物理结构的转换由Oracle数据库管理系统来完成。表空间n 表空间是Oracle数据库之中最大的一个逻辑结构，每一个Oracle数据库都会由若干个表空间所组成，而每一个表空间将由多个数据文件组成，

20、用户所创建的数据表也统一都被表空间所管理。表空间与磁盘上的数据文件对应，所以直接与物理存储结构关联。而用户在数据库之中所创建的数据表、索引、视图、子程序等都被表空间保存到了不同的区域内。n 在Oracle数据库之中一般有两类表空间：系统表空间：系统表空间：是在数据库创建时与数据库一起建立起来的，例如：用户用于撤消的事务处理，或者使用的数据字典就保存在了系统表空间之中，例如：System或Sysaux表空间；非系统表空间：非系统表空间：由具备指定管理员权限的数据库用户创建，主要用于保存用户数据、索引等数据库对象，例如：USERS、TEMP、UNDOTBS1等表空间。创建表空间n如果要进行非系统表

21、空间的创建，可以使用如下语法完成。CREATE TEMPORARY TABLESPACE 表空间名称DATAFILE | TEMPFILE 表空间文件保存路径 . SIZE 数字K | MAUTOEXTEND ON | OFF NEXT 数字 K|M LOGGING | NOLOGGING ;n本程序各个创建子句的相关说明如下所示：DATAFILE：保存表空间的磁盘路径，可以设置多个保存路径；TEMPFILE：保存临时表空间的磁盘路径；SIZE：开辟的空间大小，其单位有K（字节）和M（兆）；AUTOEXTEND：是否为自动扩展表空间，如果为ON表示可以自动扩展表空间大小，反之为OFF；NEXT：可以定义表空间的增长量；LOGGING | NOLOGGING：是否需要对DML进行日志记录，记录下的日志可以用于数据恢复。表空间 范例n创建一个mldn_data的数据表空间CREATE TABLESPACE mldn_dataDATAFILE d:mldndsmldn_data01.dbf SIZE 50M , e:mldndsmldn_data02.dbf SIZE 50MAUTOEXTEND on NEXT 2M LOGGING ;表空间 范例n创建一个