数据表

　　数据表（通常简称表）是Oracle数据库中主要的数据存储容器，表中的数据被组织成行和列。表中的每个列均有一个名称，并且每个列都具有一个指定的数据类型和大小，比如，VARCHAR(30)，TIMESTAMP(6)或NUMBER(12)。

　　在关系型数据库中，表可以对应于现实世界中的实体（如，雇员、岗位等）或联系（如，雇员工资）。在进行数据库设计时，需要首先设计E-R图（实体联系图），然后再将E-R图转变为数据库中的表。

　　从用户的角度来看，数据表的逻辑结构是一张由行列组成的二维表。表通过行和列来组织数据。通常称表中的一行为一条记录，表中的一列为属性列。一条记录描述一个实体，一个属性列描述实体的一个属性，如，雇员有雇员编号、雇员姓名、雇员岗位等属性；学生有学生编号、姓名、所在学校等属性。每个列都具有列名、列数据类型、列长度，可能还有约束条件、默认值等，这些内容在创建表时即被确定。

　　在Oracle中，有多种类型的表。不同类型的表各有一些特殊的属性。适应于保存某种特殊的数据、进行某些特殊的操作，即在某些方面可能比其他类型的表的性能更好，如处理速度更快、占用磁盘空间更少。

　　表一般指的是一个关系表，也可以生成对象表及临时表。其中，对象表是通过用户定义的数据类型生成的，临时表用于存储专用于某个事务或者会话的临时数据。

　　system给peppa用户授权

grant connect,resource to peppa;
conn peppa;

创建数据表

　　实际上通过CREATE TABLE语句创建表并不是很难的事，而难点是要确定被创建的表应该包含哪些列以及这些列的数据类型，而这些内容最好在对用户需求分析的基础上进行确定。Oracle提供了多种内置的列的数据类型，常用到的包括数值类型、字符类型、日期类型、LOB类型与ROW ID类型。 

字符类型

　　char: 固定长度字符串，可1-2000个字节。如：char(12)

　　varchar2：可变长度，1-4000个字节。如：varchar2(20)

　　long：可变长度，最多2G，一个遗留下来的而且在将来不会被支持的数据类型。它将被LOB(Large Object)数据类型所代替。

数值类型

　　可以存储整数、浮点数和实数

　　最高精度为 38 位

　　格式： NUMBER [( p[, s])]

日期时间类型

　　Date：存储日期和时间部分，精确到整个的秒

　　Timestamp：存储日期、时间和时区信息，秒值精确到小数点后6位

LOB类型

　　BLOB: 二进制数据

　　CLOB: 字符型数据
　　BFILE: 二进制文件

ROWID类型

　　ROWID 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。

-- 创建学生表
CREATE TABLE student(
  stu_id int(11) NOT NULL,
  stu_no char(11) NOT NULL,
  stu_name varchar2(20) NOT NULL,
  stu_birth date DEFAULT NULL,
  stu_gender char(6) DEFAULT 'MALE',
  stu_hobby varchar2(20) DEFAULT NULL
)

-- 创建课程表
CREATE TABLE course(
  cou_id int(11) ,
  cou_name varchar2(20) NOT NULL,
  cou_date date DEFAULT NULL,
  cou_credit int(2) DEFAULT NULL
)

-- 创建学生选课记录表
CREATE TABLE score (
  sc_id int(11),
  stu_id int(11) DEFAULT NULL,
  cou_id int(11) DEFAULT NULL,
  cou_date date DEFAULT NULL,
  score tinyint(4) DEFAULT NULL
) 

数据表的特性

　　1．存储参数 可用于：表空间、回滚段、表、索引、分区、快照、快照日志

　　当用户在Oracle中创建表时，Oracle允许用户指定该表如何使用磁盘上的存储空间。如果仅为表指定了表空间，而没有设置存储参数，则该表将采用其所属表空间的默认存储参数。

参数名称	缺省值	最小值	最大值	说明
INITIAL	5（数据块）	2（数据块）	操作系统限定	分配给Segment的第一个Extent的大小，以字节为单位，这个参数不能在alter语句中改变，如果指定的值小于最小值，则按最小值创建。
NEXT	5（数据块）	1（数据块）	操作系统限定	第二个Extent的大小等于NEXT的初值，以后的NEXT值=前一NEXT大小乘以（1+PCTINCREASE/100），如果指定的值小于最小值，则按最小值创建。 如果在alter语句中改变NEXT的值，则下一个分配的Extent将具有指定的大小，而不管上一次分配的Extent大小和PCTINCREASE参数值。
MINEXTENTS	1（Extent） 回滚段为2个Extent	1（Extent） 回滚段为2个Extent	操作系统限定	Segment第一次创建时分配的Extent数量
MAXEXTENTS	根据数据块大小而定	1（Extent） 回滚段为2个Extent	无限制	随着Segment中数据量的增长，最多可分配的Extent数量
PCTINCREASE	50% （Oracle8 16中为0%）	0%	操作系统限定	指定第三个及其后的Extent相对于上一个Extent所增加的百分比， 如果PCTINCREASE为0，则Segment中所有新增加的Extent的大小都相同，等于NEXT的值， 如果PCTINCREASE大于0，则每次计算NEXT的值（用上面的公式）， PCTINCREASE不能为负数。 创建回滚段时，不可指定此参数，回滚段中此参数固定为0。
OPTIMAL	----	不能小于回滚段初始分配空间	操作系统限定	仅与回滚段有关，当回滚段因为增长、扩展而超过此参数的设定范围时，Oracle系统会根据情况动态地重新分配Extents，试图收回多分配的Extent。
FREELISTS	1	1	数据块大小限制	只能在CREATE TABLE、CLUSTER、INDEX中指定FREELISTS和FREELIST GROUPS参数。 模式对象中每一个自由列表组中自由列表的数量
FREELIST GROUPS	1	1	取决于Oracle并行实例的数量	用户创建的数据库对象的自由列表组的数量，只有用OPS并行服务器选项时才使用这一参数，一个实例对应一个组。
BUFFER_POOL	----	----	----	给模式对象定义缺省缓冲池（高速缓存），该对象的所有块都存储在指定的高速缓存中，对于表空间或回滚段无效。

oracle存储模式：

数据块(block)：也叫逻辑块或ORACLE块,它是数据库进行I/O的最小单位。一个数据库块对应硬盘上的一个或多个物理块,它决定于建库时所使用的初始化参数DB_BLOCK_SIZE。数据库块的大小也决定了SGA中每一个数据库缓冲区的大小。

范围(extent)：范围是数据库存储空间分配的逻辑单位，它由一组连续的数据块所组成。当数据库的对象（如表、列等）增长时，空间就被分配给范围。

段(segment)：  是由一个或多个范围组成的范围集，它包含一个表空间内特定逻辑存储结构的所有数据。一个段是一个能被建立的逻辑结构，它占有存储，并且能增长，当一个段中的所有空间被用完时，ORACLE就分配它一个新的范围，但是一个段不能跨越表空间。

表空间(tablespace)：一个数据库从逻辑上来说是由一个或多个表空间所组成，表空间是数据库中物理编组的数据仓库。每一个表空间是由段所组成，一个表空间存放一个或多个数据库的物理文件。一个数据库中的数据被逻辑地存储在表空间上

　　2．数据块管理参数

　　对于一般不带有LOB类型的数据表而言，一个数据块可以存放表的多行记录，用户可以设置的数据块管理参数主要分为以下两类。

    　　（1）PCTFREE和PCTUSED

    　　（2）INITRANS参数：指定一个数据块所允许的并发事务数目。

　　3．重做日志参数

　　重做日志记录了数据库中数据的改变情况，这样，如果发生故障导致数据不能从内存中写入到数据文件中时，就可以从重做日志中获取被操作的数据。这样就可以防止数据丢失，从而提高表中数据的可靠性。 

　　4．缓存参数

　　当在Oracle中执行全表搜索时，读入缓存中的数据块将会存储在LRU列表的最近最少使用的一端。这意味着如果进行查询操作，并且必须向缓存中存储数据时，就会将刚读入的数据块换出缓存。 

维护数据表

　　在创建表后，如果发现对表的定义有不满意的地方，可以对表进行修改。普通用户只能对自己模式中的表进行修改，如果要对任何模式中的表进行修改操作，用户必须具有ALTER ANY TABLE系统权限。

• 新增字段

ALTER TABLE tab_name ADD column_name property;
-- 在学生表中添加省份province字段
ALTER TABLE student ADD province VARCHAR2(10);

• 删除字段

ALTER TABLE tab_name DROP COLUMN col_name;
--删除学生表中province字段
ALTER TABLE student DROP COLUMN province;

• 修改字段

　　除了在表中增加和删除字段外，还可以根据实际情况修改字段的有关属性，包括修改字段的数据类型的长度、数字列的精度、列的数据类型和列的默认值等。修改字段通常使用ALTER TABLE…MODIFY语句，其语法格式如下：

ALTER TABLE table_name MODIFY column_name column_property

• 重命名表

ALTER TABLE table_old_name RENAME TO table_new_name

• 修改表空间

　　　　若要将一个“非分区”表移动到一个新的表空间，则可以使用ALTER TABLE…MOVE TABLESPACE语句。

• 修改存储参数

　　　　修改存储参数，主要是指修改数据块参数PCTFREE和PCTUSED，若改变了这两个参数值，则表中所有的数据块都将受到影响，而不论数据块是否已经使用

• 删除表

DROP TABLE table_name [CASCADE CONSTRAINTS];

　　CASCADE CONSTRAINTS 表示所有引用这个表的视图、约束或触发器等也都被删除。

　　一般情况下，当某个表删除之后，实际上它并没有被彻底删除（仅仅时在数据字典中被除名），而是把该表放到了回收站中，这样当用户需要还原该表时，就可以使用FLASHBACK TABLE（闪回）进行还原。

--1、查看被删除的数据表

--2、通过数据字典视图RECYCLEBIN来了解该表是否在回收站中‘
SELECT object_name,original_name FROM RECYCLEBIN;

--3、使用闪回技术恢复被删除的表
FLASHBACK TABLE ..

--4、再次查看

• 修改表状态

　　　用户可以将表置于READ ONLY（只读）状态。处于该状态的表不能执行DML和某些DDL操作。

ALTER TABLE tab_name READ ONLY;
ALTER TABLE tab_name READ WRITE;

数据完整性和约束性

 　　数据库不仅仅是存储数据，它还必须保证所有存储数据的正确性，因为只有正确的数据才能提供有价值的信息。如果数据不准确或不一致，那么改数据的完整性就可能受到破坏，从而对数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。约束可以限制列的取值范围，强制列的取值来自合理的范围。

1. 非空约束

　　非空约约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。

-- 建表时指定某些列非空约束

--使用ALTER语句添加及删除非空约束

　　2.主键约束

　　主键约束用于唯一地标识表中的每一行记录。在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成（这种称为联合主键）。对于表中的每一行数据，主键约束列都是不同的，主键约束同时也具有非空约束的特性。

　　如果主键约束由一列组成时，该主键约束被称为行级约束。如果主键约束由两个或两个以上的列组成时，则该主键约束被称为表级约束。若要设置某个或某些列为主键约束，通常使用CONSTRAINT…PRIMARY KEY语句来定义。

-- 建表时行级约束

-- 建表时表级约束

-- ALTER

　　3.唯一约束

　　唯一性（UNIQUE）约束强调所在的列不允许有相同的值。但是，它的定义要比主键约束弱，即它所在的列允许空值（但主键约束列是不允许为空值的）。唯一性约束的主要作用是在保证除主键列外，其它列值的唯一性。

　　在一个表中，根据实际情况可能有多个列的数据都不允许存在相同值。例如，各种“会员表”的QQ、Email等列的值是不允许重复的（但用户可能不提供，这样就必须允许为空值），但是由于在一个表中最多只能由一个主键约束存在，那么如何解决这种多个列都不允许重复数据存在的问题呢？这就是唯一性约束的作用。若要设置某个列为UNIQUE约束，通常使用CONSTRAINT…UNIQUE标记该列。

-- 建表时

-- ALTER

　　4.外键约束

　　外键约束比较复杂，一般的外键约束会使用两个表进行关联（当然也存在同一个表自连接的情况）。外键是指“当前表”（即外键表）引用“另外一个表”（即被引用表）的某个列或某几个列，而“另外一个表”中被引用的列必须具有主键约束或者唯一性约束。在“另外一个表”中，被引用列中不存在的数据不能出现在“当前表”对应的列中。一般情况下，当删除被引用表中的数据时，该数据也不能出现在外键表的外键列中。如果外键列存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败。
　　最典型的外键约束时HR模式中的EMPLOYEES和DEPARTMENT表，在该外键约束中，外键表EMPLOYEES中的外键列DEPARTMENT_ID将引用被引用表DEPARTMENTS中的DEMPARTMENT_ID列，而该列也是DEPARTMENTS表的主键。

-- 示例

-- 如果外键表的外键列与被引用列列名相同，则可以省略references关键字后面的列名称。

-- 

　　另外，在定义外键约束时，还可以通过关键字ON指定引用行为的类型（级联操作）。当尝试删除被引用表中的一条记录时，通过引用行为可以确定如何处理外键表中的外键列。引用行为的类型包含三种：

• NO ACTION（默认），违反外键约束时，该操作将被禁止
• SET NULL 违反外键约束时，外键表中外键列置为NULL，前提：外键字段允许为NULL
• CASCADE 级联删除，外键表中对应的数据也将删除

--

　　5.检查约束

　　检查约束一般约定列取值范围，用于业务逻辑检查，比如余额大于0，性别必须为男或女等

-- 示例

　　管理约束

　　约束创建后，如果没有经过特殊处理，就一直起作用，但也可以根据实际需要，临时禁用某个约束。当某个约束被禁用后，该约束就不再起作用了，但它还存在数据库中。

　　那么，为什么要禁用约束呢？这是因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。当执行一些特殊操作时（比如，使用SQL*Loader从外部数据源向表中导入大量数据，并且知道这些数据是满足约束条件的），为提高运行效率，就可以禁用这些约束。

　　1．在定义约束时禁用

　　在使用CREATE TABLE或ALTER TABLE语句定义约束时（默认情况下约束是激活的），如果在定义约束时使用关键字DISABLE，则约束是被禁用的。

age int CONSTRAINT CK_AGE CHECK(age > 0 AND age < 200) DISABLE

　　2．禁用已经存在的约束

　　对于已存在的约束，则可以使用ALTER TABL…DISABLE CONSTRAINT语句禁止该约束。

-- ALTER

　　禁用约束只是一种暂时现象，在特殊处理完毕之后，还应该及时激活约束。

ALTER TABLE tab_name ENABLE [NOVALIDATE] CONSTRAINT con_name;

-- NOVALIDATE表示在激活约束时不验证表中已经存在的数据是否满足约束

　　3.删除约束

　　如果不再需要某个约束时，则可以将其删除。可以使用带DROP CONSTRAINT子句的ALTER TABLE语句删除约束。删除约束与禁用约束不同，禁用的约束时可以激活的，但是删除的约束在表中就完全消失了。使用ALTER TABLE语句删除约束的语法格式如下：

ALTER TABLE table_name DROP CONSTRAINT con_name;