Oracle基础——第五章表的实现

女爷i 2022-03-19 11:57 211阅读 0赞

### 本章目标 ###

数据完整性

创建表

修改表

添加、修改、删除列

添加、删除约束

启用、禁用约束

删除表

### 数据完整性 ###

1.        数据完整性

a)        存储在数据库中的所有数据值均正确的状态。

如果数据库中存储有不正确的数据值，则该数据库称为已失去数据完整性。

b)        数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。

它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

2.        数据完整性\-1

a)        数据类型

年龄使用什么类型？

b)        格式是否正确

身份证号，可以是15位也可以是18位

c)        范围

性别可选择“男”、“女”、“未知”

d)        是否允许重复

银行卡号不允许重复

e)        特定业务要求

银行贷款

3.        数据完整性\-2

a)        实体完整性

b)        域完整性

c)        引用完整性

d)        自定义完整性

4.        实体完整性

a)        实体完整性

实体完整性要求每一个表中的主键字段都不能为空或者重复的值。

b)        实现方法

i.              唯一约束

ii.              主键约束

5.        域完整性

a)        域完整性

域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等

b)        实现方法

i.              限制数据类型

ii.              外键约束

iii.              默认值

iv.              非空约束

6.        引用完整性

a)        引用完整性

i.              也称之为参照完整性，当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。

ii.              引用完整性要求关系中不允许引用不存在的实体。

iii.              引用完整性与实体完整性是关系模型必须满足的完整性约束条件。

b)        实现方法

外键约束

7.        自定义完整性

a)        自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

b)        实现方法

i.              存储过程

ii.              触发器

8.        选择主键的原则

a)        最少性

尽量选择单个键作为主键

b)        稳定性

尽量选择数值更新少的列作为主键

9.        主外键关系

a)        注意事项

i.              当主表中没有对应的记录时，不能将记录添加到子表

ii.              不能更改主表中的值而导致子表中的记录孤立

iii.              子表存在与主表对应的记录，不能从主表中删除该记录

iv.              删除主表前，先删子表

10.    表约束

a)        表约束的目的：确保表中数据的完整性

b)        常用的约束类型：

i.              主键约束(PRIMARY KEY)：要求主键列数据唯一，并且不允许为空

ii.              非空约束(NOT NULL)：指定的列的值不允许为空

iii.              唯一键约束(UNIQUE)：要求该列唯一，允许为空，但只能出现一个空值

iv.              检查约束(CHECK)：指定表中一列或多列可以接受的数据值格式

v.              默认约束(DEFAULT)：指定某列的默认值

vi.              外键约束(FOREIGN KEY)：用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行

### 创建表 ###

1.        语法及说明

a)        语法

CREATE  \[GLOBAL TEMPORARY\] TABLE table\_name (

column\_name type \[CONSTRAINT constraint\_def DEFAULT default\_exp\]

\[, column\_name type \[CONSTRAINT constraint\_def DEFAULT default\_exp\] ...\]

)

\[ON COMMIT \{DELETE | PRESERVE\} ROWS\]

TABLESPACE tab\_space;

b)        说明

i.              GLOBAL TEMPORARY：表示表中的数据行是临时的，表中的数据行保存到什么时候，需要参考OM COMMIT选项

ii.              table\_name：指定表名

iii.              column\_name：列名

iv.              type：列的类型

v.              constraint\_def：列的约束

vi.              default\_exp：指定列的默认值

vii.              ON COMMIT：控制临时表中数据行保存的时间。DELETE表示事务结束则表中的数据行被删除。PRESERVE表示表中的数据一直保存到用户会话结束。如果省略OM COMMIT后面的参数，则默认为DELETE

viii.              tab\_space：指定表存放的表空间。如果没有指定的话，则存放在当前用户的默认表空间

2.        案例

a)        创建表

CREATE TABLE order\_status2 (

id INTEGER CONSTRAINT order\_status2\_pk PRIMARY KEY,

status VARCHAR2(10),

last\_modified DATE DEFAULT SYSDATE

);

b)        例如

CREATE GLOBAL TEMPORARY TABLE order\_status\_temp (

id INTEGER,

status VARCHAR2(10),

last\_modified DATE DEFAULT SYSDATE

)

ON COMMIT PRESERVE ROWS;

i.              插入数据

INSERT INTO order\_status\_temp (id, status)

VALUES (1, 'New');

ii.              说明

1.        创建临时表order\_status\_temp，表中的数据将会保存到直到用户结束会话，表中的数据将会被清除

2.        插入数据成功之后，进行查询；断开会话，重新登录进行查询

3.        获取表的相关信息

a)        使用DESCRIBE命令查看某张表的信息

b)        查询USER\_TABLES视图获取表的相关信息

c)        案例

i.              例如

SELECT table\_name, tablespace\_name, temporary

FROM user\_tables;

ii.              注意：

1.        由于ORDER\_STATUS\_TEMP表是临时表，在最后一列使用Y表示

2.       table\_name：表示表名

3.       tablespace\_name：该表存储的表空间的名称。表空间是数据库用来存储对象的区域，如表。

4.       temporary：表示该表是否是临时的，Y表示是临时表，否则为N

4.        获取表中列的信息

a)        可以通过查看user\_tab\_columns视图，查看表的列相关信息，例如

SELECT table\_name,column\_name, data\_type, data\_length, data\_precision, data\_scale

FROM user\_tab\_columns;

![1364825518_1667.png][]

5.       user\_tab\_columns字段说明

a)       user\_tab\_columns视图中的某些列

i.              table\_name

表名

ii.              column\_name

列名

iii.              data\_type

列的数据类型

iv.              data\_length

数据长度

v.              data\_precision

数字列的精度

vi.              data\_scale

小数点后位数

### 修改表 ###

1.        增加列

a)        给order\_status2表增加一列

ALTER TABLE order\_status2

ADD modified\_by INTEGER;

b)        给order\_status2表增加一列，指定默认值

ALTER TABLE order\_status2

ADD initially\_created DATE DEFAULT SYSDATE NOT NULL;

c)        使用DESCRIBE命令验证添加结果

DESCRIBE order\_status2;

d)        显示效果如下图所示：

![1364825585_2310.png][]

2.        增加虚拟列

a)       Oracle 11g允许添加虚拟列，虚拟列可以引用表中已存在的列。

b)        例如，现有表salary\_grades，并有如下数据

CREATE TABLE salary\_grades (

salary\_grade\_id INTEGER CONSTRAINT salary\_grade\_pk PRIMARY KEY,

low\_salary NUMBER(6, 0),

high\_salary NUMBER(6, 0)

);

INSERT INTO salary\_grades VALUES (1, 1, 250000);

INSERT INTO salary\_grades VALUES (2, 250001, 500000);

INSERT INTO salary\_grades VALUES (3, 500001, 750000);

INSERT INTO salary\_grades VALUES (4, 750001, 999999);

c)        给表salary\_grades添加虚拟列

ALTER TABLE salary\_grades

ADD (average\_salary AS ((low\_salary + high\_salary)/2));

d)        查看表结构及表中数据

DESCCRIBE salary\_grades;

SELECT \* FROM salary\_grades;

![1364825602_3839.png][]

3.        修改列

a)        修改列的大小

ALTER TABLE order\_status2

MODIFY status VARCHAR2(15);

b)        修改数字列精度

ALTER TABLE order\_status2

MODIFY id NUMBER(5);

c)        修改数据类型

ALTER TABLE order\_status2

MODIFY status CHAR(15);

d)        修改默认值

ALTER TABLE order\_status2

MODIFY last\_modified DEFAULT SYSDATE - 1;

4.        给列重命名

a)        语法

ALTER TABLE <table\_name>

RENAME COLUMN <old\_col\_name> TO <new\_col\_name>

b)        例如

i.              修改order\_status2表的last\_modified列名为modified列

ALTER TABLE order\_status2

RENAME COLUMN last\_modified TO modified;

ii.              执行效果图如下表所示：

![1364825616_2684.png][]

5.        删除列

a)        使用ALTER TABLE进行列的删除，如下

ALTER TABLE order\_status2

DROP COLUMN initially\_created;

6.       CHECK约束

a)        添加CHECK约束

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_status\_ck

CHECK (status IN ('PLACED', 'PENDING', 'SHIPPED'));

b)        测试数据

i.              正确数据

INSERT INTO order\_status2(id, status, last\_modified, modified\_by)

VALUES (1,'PENDING','01-1月\-2005',1);

ii.              错误数据

INSERT INTO order\_status2(id, status, last\_modified, modified\_by)

VALUES(2, 'CLEARED','01-1月\-2005',2);

提示错误：ORA-02290:违反检查约束条件 (STORE.ORDER\_STATUS2\_STATUS\_CK)

7.        非空约束

a)        给status列添加非空约束

ALTER TABLE order\_status2

MODIFY status CONSTRAINT order\_status2\_status\_nn NOT NULL;

b)        给modified\_by列添加非空约束

ALTER TABLE order\_status2

MODIFY modified\_by CONSTRAINT order\_status2\_modified\_by\_nn NOT NULL;

c)        给last\_modified列添加非空约束，不指定约束名

ALTER TABLE order\_status2

MODIFY last\_modified NOT NULL;

8.        外键约束

a)        添加外键约束

b)        现有employees表

CREATE TABLE employees (

employee\_id INTEGER CONSTRAINT employees\_pk PRIMARY KEY,

manager\_id INTEGER,

first\_name VARCHAR2(10) NOT NULL,

last\_name VARCHAR2(10) NOT NULL,

title VARCHAR2(20),

salary NUMBER(6, 0)

);

c)        先删除order\_status2表中的modified\_by列

ALTER TABLE order\_status2

DROP COLUMN modified\_by;

d)        给order\_status2表添加FOREIGN KEY约束，引用employees表中的employee\_id列。

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_modified\_by\_fk

modified\_by REFERENCES employees(employee\_id);

e)        同样，在创建外键约束时可以添加ON DELETE CASCADE选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。例如

f)        \--重新删除modified\_by列

ALTER TABLE order\_status2

DROP COLUMN modified\_by;

g)       \--重新添加外键约束

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_modified\_by\_fk

modified\_by REFERENCES employees(employee\_id) ON DELETE CASCADE;

h)        在创建外键约束时可以添加ON DELETE SET NULL选项，那么当主表的数据被删除时，子表匹配的相关行的列会被设置为NULL值，而不是被删除

i)          例如

j)          删除modified\_by列

ALTER TABLE order\_status2

DROP COLUMN modified\_by;

k)        设置外键

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_modified\_by\_fk

modified\_by REFERENCES employees(employee\_id) ON DELETE SET NULL;

9.        唯一约束

a)        以下ALTER TABLE语句给order\_status2表的status列添加UNIQUE约束

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_status\_uq UNIQUE (status);

b)        在往order\_status2表中添加任意行时，行的status列的值必须要唯一。

10.    修改约束

a)        删除约束

b)        通过ALTER TABLE的DROP CONSTRAINT选项删除约束

ALTER TABLE order\_status2

DROP CONSTRAINT order\_status2\_status\_uq;

c)        停用约束

d)        约束在创建完之后就生效了，也可以在创建时在CONSTRAINT关键字末尾加DISABLE关键字，默认停用它。

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_status\_uq UNIQUE (status) DISABLE;

e)        也可以停用已经启用的约束，例如约束order\_status2\_status\_nn

ALTER TABLE order\_status2

DISABLE CONSTRAINT order\_status2\_status\_nn;

f)         启用约束

g)        可以使用ALTER TABLE的ENABLE CONSTRAINT选项将已存在的约束失效启用。例如

ALTER TABLE order\_status2

ENABLE CONSTRAINT order\_status2\_status\_uq;

h)        说明

在启用约束时，表中所有的数据行的数据必须要满足这个约束。

i)          也可以通过设置ENABLE NOVALIDATE，对新的数据行增加约束

ALTER TABLE order\_status2

ENABLE NOVALIDATE CONSTRAINT order\_status2\_status\_uq;

j)          说明

默认是ENABLE VALIDATE，对存在的数据行也要求符合约束

11.    延迟约束

a)        延迟约束(Deferred Constraint)就是在事务提交的时候才生效

i.              可以在约束中加DEFERRABLE选项

ii.              约束一旦创建之后就不能将其改为DEFERRABLE，必须删除重新创建

iii.              创建DEFERRABLE约束时，可以使用INITIALLY IMMEDIATE或INITIALLY DEFERRED

1.       INITIALLY IMMEDIATE：表示添加、更新或删除时都进行数据的合法性验证(默认情况)

2.       INITIALLY DEFERRED：表示只有事务提交时，才进行数据的合法性验证

b)        案例

i.              给order\_status2表的唯一约束设置成延迟的

ii.              先删除order\_status2表的唯一约束

ALTER TABLE order\_status2

DROP CONSTRAINT order\_status2\_status\_uq;

iii.              添加唯一约束

ALTER TABLE order\_status2

ADD CONSTRAINT order\_status2\_status\_uq UNIQUE (status)

DEFERRABLE INITIALLY DEFERRED;

12.    查看约束信息

a)        可以通过user\_constraints视图查看约束信息

b)        查看order\_status2表的约束信息

SELECT constraint\_name, constraint\_type, status, deferrable, deferred

FROM user\_constraints

WHERE table\_name = 'ORDER\_STATUS2';

c)        显示效果如下图所示

在下列约束中，有2个约束名是数据库自动分配的。

![1364825643_9833.png][]

13.    查看列约束信息

a)        通过查看user\_cons\_columns视图，了解列的约束信息

b)        视图相关列的说明

i.              owner：约束的拥有者

ii.              constraint\_name：约束名

iii.              table\_name：约束所在的表

iv.              column\_name：约束定义所在的列

c)       user\_cons\_columns视图

从视图中查询order\_status2表中列的约束信息

SELECT owner,constraint\_name,table\_name,column\_name

FROM user\_cons\_columns

WHERE table\_name='ORDER\_STATUS2';

查询效果如下图所示：

![1364825643_9833.png][]

16.    重新命名表名

a)        可以使用RENAME语句对表进行重命名

b)        例如

RENAME order\_status2 TO order\_state;

c)        运行效果图如下所示：

![1364825650_1610.png][]

d)        重新将表名恢复

RENAME order\_state TO order\_status2;

15.    给表添加备注

a)        给表或列添加备注，可以帮助用户理解表或列的作用。

i.              给表或列添加备注可以使用COMMENT语句

ii.              例如，给表添加备注

COMMENT ON TABLE order\_status2 IS

'存储订单状态信息';

b)        例如，给列条件备注

COMMENT ON COLUMN order\_status2.last\_modified IS

'存储订单最后修改时间';

16.    查看表备注

a)        查看表的备注

b)        可以从user\_tab\_comments视图中查看表的备注信息

SELECT \*

FROM user\_tab\_comments;

c)        查询效果如下图所示：

![1364825692_2107.png][]

17.    查看列备注

a)        查看列的备注

b)        可以从user\_col\_comments视图中查看列的备注信息

SELECT \*

FROM user\_col\_comments

WHERE table\_name = 'ORDER\_STATUS2';

c)        查询效果如下图所示：

![1364825767_4700.png][]

18.     TRUNCATE TABLE

a)        截断表使用TRUNCATE语句

b)        这个操作会从表中删除所有的行和重置表的存储区域。

c)        例如，截断order\_status2表

TRUNCATE TABLE order\_status2;

d)        操作效果如下图所示：

![1364825781_1654.png][]

e)        说明

i.              如果要删除表中所有的数据使用TRUNCATE比DELETE效果要好。因为TRUNCATE会为新的数据行重置存储区域，而且它删除时不需要占用数据库撤销空间，也不需要使用COMMIT进行提交。

19.    删除表

a)        如果要删除表的话，可以使用DROP TABLE语句

b)        例如，删除order\_status2表

DROP TABLE order\_status2;

c)        操作效果如下图所示：

![1364825796_8696.png][]

### 本章总结 ###

数据完整性

创建表

修改表

删除表

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[1364825518_1667.png]: https://img-my.csdn.net/uploads/201304/01/1364825518_1667.png
[1364825585_2310.png]: https://img-my.csdn.net/uploads/201304/01/1364825585_2310.png
[1364825602_3839.png]: https://img-my.csdn.net/uploads/201304/01/1364825602_3839.png
[1364825616_2684.png]: https://img-my.csdn.net/uploads/201304/01/1364825616_2684.png
[1364825643_9833.png]: https://img-my.csdn.net/uploads/201304/01/1364825643_9833.png
[1364825650_1610.png]: https://img-my.csdn.net/uploads/201304/01/1364825650_1610.png
[1364825692_2107.png]: https://img-my.csdn.net/uploads/201304/01/1364825692_2107.png
[1364825767_4700.png]: https://img-my.csdn.net/uploads/201304/01/1364825767_4700.png
[1364825781_1654.png]: https://img-my.csdn.net/uploads/201304/01/1364825781_1654.png
[1364825796_8696.png]: https://img-my.csdn.net/uploads/201304/01/1364825796_8696.png