oracle10g经典实战第3章数据库的操作.ppt

《oracle10g经典实战第3章数据库的操作.ppt》由会员分享，可在线阅读，更多相关《oracle10g经典实战第3章数据库的操作.ppt（72页珍藏版）》请在三一办公上搜索。

1、第3章数据库的操作,3.1操作表数据 通过PL/SQL语句操作表数据比较方便、灵活。所有SQL命令将在SQL*Plus中编译完成。进入SQL*Plus，用户名和密码分别为：ADMIN和manager。3.1.1插入表记录 语法格式：INSERT INTO table_namecolumn_list VALUES(constant1,constant2,)该语句的功能是向由table_name指定的表中加入一行，由VALUES指定的各列值。(1)在插入时，列值表必须与列名表顺序和数据类型一致。(2)VALUES中描述的值可以是一个常量、变量或一个表达式。(3)如果列值为空，则值必须置为NULL。

2、如果列值指定为该列的默认值，则用DEFAULT。(4)在对表进行插入行时，若新插入的行中所有可取空值的列值均取空值，则就可以在INSERT语句中通过列表指出插入的行值中所包含非空的列，而在VALUES中只要给出这些列的值即可。,3.1.1插入表记录,【例3.1】向XSCJ数据库的表XS中插入如下的一行:061101 王林 计算机 男 19870201 50可以使用如下的PL/SQL语句:INSERT INTO XS(XH,XM,ZYM,XB,CSSJ,ZXF)VALUES(061101,王林,计算机,男,TO_DATE(19860210,YYYYMMDD),50);请读者使用INSERT IN

3、TO语句向学生表插入以下记录：学号 姓名 专业名 性别 出生时间 总学分 备注061102 王平 计算机 女 1986-09-02 38 NULL061103王燕 计算机 女1985-10-06 40NULL061104韦严平 计算机 男1986-08-26 40NULL061105吴庆红计算机男1985-12-3036有两门不及格061106李方方 计算机 男1986-11-20 40NULL061107李明 计算机 男1986-05-01 40NULL061108林一帆 计算机 男1985-08-05 42已提前修完一门课061109张强民 计算机 男1984-08-11 40NULL06

4、1110张蔚 计算机 女1987-07-22 40NULL061111赵琳 计算机 女1986-03-18 40NULL061112罗林琳 计算机 女1984-01-30 40NULL,3.1.1插入表记录,用SELECT语句进行查询，可以发现表中已经增加的记录，如图3.1所示。,图3.1插入新数据后查询的结果,3.1.1插入表记录,【例3.2】建立了表test。CREATE TABLE test(xm char(20)NOT NULL,zy varchar(30)DEFAULT(计算机),nj number NOT NULL);用INSERT向test表中插入一条记录：INSERT INTO

5、 test(xm,nj)VALUES(王林,3);则插入到test表中的记录为：王林 计算机 3 语法格式：INSERT INTO table_name derived_table derived_table是一个由SELECT语句查询所得到的结果集。利用该参数，可把一个表中的部分数据插入到表table_name中。,3.1.1插入表记录,【例3.3】用如下的CREATE语句建立表XS1：CREATE TABLE XS1(num char(6)NOT NULL,name char(8)NOT NULL,speiality char(10)NULL);用如下的INSERT语句向XS1表中插入数据

6、：INSERT INTO XS1 SELECT XH,XM,ZYM FROM XS WHERE ZYM=计算机;,3.1.1插入表记录,这条INSERT语句将XS表中专业名为计算机的各记录的学号、姓名和专业名列的值插入到XS1表的各行中。用SELECT语句可查看插入结果：num name speciality-061101 王林 计算机 061102 王平 计算机 061103 王燕 计算机 061104 韦严平 计算机 061105 吴庆红 计算机 061106 李方方 计算机 061107 李明 计算机 061108 林一帆 计算机 061109 张强民 计算机 061110 张蔚 计算机

7、 061111 赵琳 计算机 061112 罗林琳 计算机 从Oracle 10g开始新增了MERGE语句，使用这个语句可以实现对表的更新或插入。,3.1.1插入表记录,语法格式：MERGE INTO table_name USING table_name ON(join_condition)WHEN MATCHED THEN UPDATE SET WHEN NOT MATCHED THEN INSERT()VALUES()Joni_condition：连接条件。【例3.4】检查表XS_JSJ中的数据是否和表XS的数据相匹配，如果匹配则使用INSERT子句执行插入数据行。首先在表XS_JSJ使

8、用INSERT语句添加一行数据：INSERT INTO XS_JSJ(XH,XM,ZYM,XB,CSSJ,ZXF)VALUES(101112,霍甲,计算机,女,TO_DATE(19860130,YYYYMMDD),36);使用MERGE语句XS_JSJ表中新增的数据插入表XS中：MERGE INTO XS a USING XS_JSJ b ON(a.XH=b.XH)WHEN NOT MATCHED THEN INSERT Values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz);用SELECT语句进行查询，可以发现表中已经增加一条姓名为霍甲的学生记录。,3.

9、1.2删除表记录,1.使用 DELETE删除数据 在PL/SQL语言中，删除行可以使用DELETE语句。语法格式：DELETE FROM table_nameview_name WHERE condition 该语句的功能为从table_name指定的表或view_name指定的视图中删除满足condition 查询条件的行，若省略该条件，表示删除所有的行。【例3.5】将XSCJ数据库的XS表中总学分小于39的行删除，使用如下的PL/SQL语句。DELETE FROM XS WHERE ZXF39;,3.1.2删除表记录,1.使用 DELETE删除数据 用SELECT语句进行查询，可以发现表中

10、学号为“061102”和“061105”两行已被删除，结果为：XH XM ZYM-061101 王林 计算机 061103 王燕 计算机 061104 韦严平 计算机 061106 李方方 计算机 061107 李明 计算机 061108 林一帆 计算机 061109 张强民 计算机 061110 张蔚 计算机 061111 赵琳 计算机 061112 罗林琳 计算机,3.1.2删除表记录,2.使用TRUNCATE TABLE语句删除表数据 如果确实要删除一个大表里的全部记录,可以用TRUNCATE命令,它可以释放占用的数据块表空间。此操作不可回退。语法格式：TRUNCATE TABLE ta

11、ble_name 其中table_name为要删除数据的表名。由于TRUNCATE TABLE语句删除表中的所有数据，且不能恢复，所以使用时要谨慎。使用TRUNCATE TABLE删除了指定表中的所有行，但表的结构及其列、约束、索引等保持不变。TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同，二者均删除表中的全部行。但TRUNCATE TABLE执行速度比DELETE快。对于由外键(FOREIGN KEY)约束引用的表不能使用TRUNCATE TABLE删除数据，而应使用不带WHERE子句的DELETE语句。另外，TRUNCATE TABLE也不能用于索引视图的表

12、。,3.1.3修改表记录,在PL/SQL语言中，UPDATE语句可以用来修改表中的数据行。语法格式：UPDATE table_nameview_name SET column_name=expression,n WHERE condition该语句table_name指定的表或view_name指定的视图中满足condition 查询条件的记录中由SET指定的各列的列值设置为SET指定的新值。若不使用WHERE子句，则更新所有记录的指定列值。【例3.6】将XSCJ数据库的XS表中学号为“061110”的学生备注列值改为“三好学生”，使用如下PL/SQL语句：UPDATE XS SET BZ=三

13、好学生 WHERE XH=061110;,3.1.3修改表记录,用SELECT语句进行查询，可以发现表中学号为“061110”的行的备注字段值已被修改，如图3.2所示。,图3.2修改数据以后的表,3.1.3修改表记录,【例3.7】将XS表中的所有学生的总学分都增加10。UPDATE XS SET ZXF=ZXF+10;用SELECT语句进行查询，可以发现表中所有学生的总学分都增加了10分，如图3.3所示。,图3.3增加学分后的数据,3.1.3修改表记录,【例3.8】将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“061241”。UPDATE XS SET ZY

14、M=通信工程,BZ=转专业学习,XH=061241 WHERE XM=罗林琳;【例3.9】对XS表进行修改，将姓名为“李明”的学生的总学分加4，备注改为“提前修完数据结构，并获得学分”。UPDATE XS SET ZXF=ZXF+4,BZ=提前修完数据结构，并获得学分 WHERE XM=李明;,3.2默认值约束,对于某些字段，可在程序中定义默认值以方便用户。定义一个字段的默认值可以在定义表或修改表时，定义默认值约束。1.默认值约束的定义 在使用OEM或SQL命令定义表或修改表时，可定义一个字段的默认值约束。下面通过例子介绍利用SQL语句定义一个字段的默认值约束的方法，有兴趣的读者可以练习一下在

15、OEM中定义一个字段的默认值约束。默认值约束定义的一般格式为：CREATE TABLE table_name(column_name datatype NOT NULL|NULL DEFAULT constraint_expression/*默认值约束的表达式*/,n)table_name为创建的表名；column_name为列名；datatype为对应列的数据类型；DEFAULT关键字表示其后的constraint_expression表达式为缺省值约束表达式，此表达式只能是常量（如字符串）、系统函数或 NULL；参数n表示可定义多个数据字段。,3.2默认值约束,【例3.10】在定义表时定义

16、一个字段的默认值约束。CREATE TABLE XS(学号 char(6)NOT NULL,姓名 char(8)NOT NULL,专业名 char(10)NULL,性别 char(2)NOT NULL,出生时间 date NOT NULL,总学分 number DEFAULT 0,/*定义默认值约束*/备注 varchar2(200)NULL)【例3.11】在修改表时定义一个字段的默认值约束。ALTER TABLE XS ADD(Addcolumn number(2)DEFAULT 0);,3.3索引,为什么在字典中进行查找时能够很快地找到要查的内容呢？主要原因是字典中已按某种顺序进行了排序。

17、在Oracle 10g中，索引是一种供服务器在表中快速查找一个行的数据库结构。在数据库中建立索引主要有以下作用：(1)快速存取数据；(2)既可以改善数据库性能又可以保证列值的唯一性；(3)实现表与表之间的参照完整性；(4)在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,3.3索引,3.3.1索引的分类 索引按存储方法分类，可以分为2类：B*树索引和位图索引。(1)B*树索引。B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。(2)位图索引。位图索引储存主要用来节省空间，减少

18、ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。索引按功能和索引对象还有以下类型：(1)唯一索引。唯一索引意味着不会有两行记录相同的索引键值。(2)非唯一索引。不对索引列的值进行唯一性限制的所以称为非唯一索引。(3)分区索引。所谓分区索引是指索引可以分散地存在于多个不同的表空间中，其优点是可以提高数据查询的效率。(4)未排序索引。未排序索引也称为正向索引。Oracle 10g数据库中的行是按升序排序的，因此创建索引时不必指定对其排序而使用默认的顺序。(5)逆序索引。逆序索引也称为反向索引。该索引同样保持索引列按顺序排列，但是颠倒已索引的

19、每列的字节。(6)基于函数的索引。基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或者表达式计算索引列的值。,3.3.2使用索引的原则,在正确使用索引的前提下，索引可以提高检索相应的表的速度。当用户考虑在表中使用索引时，应遵循下列一些基本的原则：(1)在表中插入数据后创建索引(2)索引正确的表和列(3)合理安排索引列(4)限制表中索引的数量(5)指定索引数据块空间的使用(6)根据索引大小设置存储参数,3.3.3创建索引,创建索引有三种方法：随数据库表创建、使用Oracle 10g管理控制台工具单独创建和使用SQL命令创建索引。1.随数据库表一起创建索引在创建数据库表时，

20、如果表中包含有唯一关键字或主关键字，则Oracle 10g自动为这两种关键字所包含的列建立索引。如果不特别指定，系统将默认为该索引定义一个名字。表建立之后，实际上就是在表XS的列XH上建立了一个索引，如图3.4所示。这种方法创建的索引是非排序索引，既正向索引，以B*树形式存储。,图3.4随数据库表创建的索引,3.3.3创建索引,2.在OEM中创建索引【例3.11】为XS表的姓名列创建索引。在如图3.5所示的界面中，在方案中选择“索引”，鼠标单击左键，打开如图3.4所示的“索引搜索”界面。,图3.5 Oracle企业管理器,3.3.3创建索引,2.在OEM中创建索引 在如图3.4所示界面，单击“

21、创建”按钮，进入如图3.6所示的界面。“创建索引”窗口包含一般信息、分区、存储、选项和统计信息5个选项页面。,图3.6创建索引 一般信息选项界面,3.3.3创建索引,2.在OEM中创建索引(1)“一般信息”选项页面。它可以设置以下信息：名称：指定索引名。创建索引时指定一个有效的Oracle标识符。这里指定的索引名是XS_NAME_INDEX。方案：单击“手电筒”形状的按钮，打开如图3.7所示的“搜索和选择方案”界面。,图3.7搜索和选择方案界面,3.3.3创建索引,2.在OEM中创建索引 表空间：指定索引所属的表空间。表名：指定哪个表创建索引。索引类型：指定要创建的索引是标准索引还是位图索引。

22、表列：指定索引列和顺序。单击“置入列”按钮，表列电子表格列出表XS所有的列。电子表格包含列名、数据类型、排序次序和顺序，由“顺序”字段指定索引的列及其顺序。(2)“存储”选项选项页面。“存储”选项页面如图3.8所示。在该界面指定存储参数。事件记录：指示是否生成重做日志。区数：初始大小指定对象的第一个区的大小。空间空闲：区定义保留用于更新的空闲的百分比 事务处理数量：类别定义下列参数：初始值：对象的每个数据块内分配的事务处理条目的初始数量。范围为1255。最大值：可同时更新分配给对象的数据块的并行事务处理的最大值。范围为1255。缓冲池：区定义通过“缓冲池”下拉列表选择使用的缓冲池。可以选择的缓

23、冲池包括KEEP、RECYCLE和DEFAULT。,3.3.3创建索引,图3.8创建索引存储选项界面,3.3.3创建索引,2.在OEM中创建索引(3)“选项”选项页面。“选项”选项界面如图3.9所示。在该选项页面，可以指定如何执行并行查询、如何存储重做日志等信息。唯一：该复选框指定表中将索引的列或列组合的值必须唯一。逆序：该复选框指定创建逆序（从大到小）关键字索引。并行：该复选框可用于创建一个常规表或一个按索引组织的表，并以并行方式装载。该选项可指定并行执行某一操作。程度表示单个例程的操作并行度，并行度可以有两种设置：默认值：查询服务器数量根据CPU数和存储要并行扫描的表的“设备”数计算得出。

24、值：用户指定的查询服务器数量。压缩：是为了避免关键字列的值重复出现，可以大大减少存储空间。执行选项：有3个复选框可以勾选设置：联机：在创建或重建索引时，允许对表进行DML操作。计算统计信息：在创建或重建索引时，以非常小的代价收集统计信息。不排序：表明存储在数据库中的数据按升序排序，所以创建索引时不对其进行排序。,3.3.3创建索引,图3.9创建索引选项选项界面,3.3.3创建索引,2.在OEM中创建索引(4)“分区”选项页面。“分区”选项界面如图3.10所示。,图3.10创建索引分区选项界面,3.3.3创建索引,2.在OEM中创建索引 对索引进行分区有2种方法：全局范围：在使用范围分区的全局索

25、引时,每个索引分区都包含由分区边界定义的值。全局散列：在使用散列分区的全局索引时,每个索引分区都包含由 Oracle 的散列函数确定的值。(5)单击“统计信息”选项页面，出现如图3.11所示的界面。该界面显示索引使用情况说明。,3.3.3创建索引,图3.11统计信息选项界面,3.3.3创建索引,3.利用SQL命令建立索引 使用SQL命令可以灵活方便地创建索引。在使用SQL命令创建索引时，必须满足下列条件之一：(1)索引的表或簇必须在自己的模式中；(2)必须在要索引的表上具有INDEX权限；(3)必须具有CREATE ANY INDEX权限。语法格式：CREATE UNIQUEBITMAP IN

26、DEX/*索引类型*/schema.index_name/*索引名称*/ON schema.table_name(column_nameASCDESC,n,column_expression)/*索引建于表*/CLUSTER schema.cluster_name/*索引建于簇*/INITRANS integer MAXTRANS integer PCTFREE integer PCTUSED integer/*建立索引的物理和存储特征值*/TABLESPACE tablespace_name/*索引所属表空间*/STORAGE storage_clause/*为索引建立存储特征*/NOSOR

27、T REVERSE,3.3.3创建索引,3.利用SQL命令建立索引 其中：UNIQUE：指定索引所基于的列(或多列)值必须唯一。默认的索引是非唯一索引。BITMAP：指定建成位映射索引而不是B*索引。Schema：表示包含索引的方案。ON table_name：建立table_name表索引。column_expression：创建基于函数的索引。ON CLUSTER：创建cluster_name簇索引。NOSORT：数据库中的行以升序保存，在创建索引时不必对行排序。REVERSE：指定以反序索引块的字节，不包含行标识符。,3.3.3创建索引,【例3.12】为KC表的课程名列创建索引。CREA

28、TE INDEX kc_name_idx ON KC(KCM)TABLESPACE INDX;【例3.13】为XS表的姓名列创建索引，指定索引的物理和存储特征值，数据库中的行以升序保存。CREATE INDEX xs_xm_idx ON XS(XM)TABLESPACE INDX INITRANS 2 MAXTRANS 255 NOSORT;,3.3.4维护索引,1.在OEM中维护索引 在OEM中维护索引的操作，除了一些特殊的信息不能再修改，其他与创建索引的操作相同。在如图3.4所示的界面中，选择要维护的索引，单击“编辑”按钮，进入“编辑索引”界面，如图3.12所示。在各个选项页面上重新指定相

29、应的设置，确认无误后，单击“应用”按钮，完成修改。,图3.12维护索引,3.3.4维护索引,2.利用ALTER INDEX命令维护索引 语法格式：ALTER INDEX schema.index_name INITRANS integer MAXTRANS integer PCTFREE integer/*建立索引的物理和存储特征值*/STORAGE storage_clause/*为索引建立存储特征*/RENAME TO new_index_name【例3.14】修改例3.12中创建的索引kc_name_idx。ALTER INDEX admin.xs_name_index INITRANS

30、 2 MAXTRANS 128;【例3.15】重命名索引kc_name_idx。ALTER INDEX kc_name_idx RENAME TO kc_idx;,3.3.5删除索引,索引的删除既可以通过OEM删除，也可以通过执行SQL命令删除。1.利用OEM删除索引 在如图3.4所示的界面中，选中要删除的索引，单击“删除”，系统弹出确认界面，单击“是”按钮后，就成功删除该索引了。2.利用SQL命令删除索引 语法格式：DROP INDEX schema.index_name 其中，schema是包含索引的方案。index_name是要删除的索引名称。【例3.16】删除XSCJ数据库中表XS的一

31、个索引名为XS_NAME_IDX的索引。DROP INDEX XS_NAME_IDX;,3.4同义词,3.4.1创建同义词 1.利用OEM创建同义词【例3.17】为本地数据库XSCJ的表XS创建同义词XS。如图3.5所示，在OEM中，选择方案选项中的同义词，单击鼠标左键，进入“同义词搜索”界面，如图3.13所示。单击“创建”按钮，进入“创建同义词”界面，如图3.14所示。,图3.13同义词搜索界面,图3.14创建同义词界面,3.4.1创建同义词,2.利用CREATE SYNONYM命令创建同义词 语法格式：CREATE PUBLIC SYNONYM schema.synonym_name FO

32、R schema.object dblink【例3.18】创建同义词。(1)为XSCJ数据库的XS_KC表创建公用同义词XS_KC。CREATE PUBLIC SYNONYM XS_KC FOR ADMIN.XS_KC;(2)为XSCJ数据库XS表创建远程数据库同义词。CREATE PUBLIC SYNONYM XS FOR ADMIN.XSMY_LINK;(3)为XSCJ数据库的CS_XS视图创建公用同义词CS_XS。CREATE PUBLIC SYNONYM CS_XS FOR ADMIN.CS_XS;,3.4.2使用同义词,一旦创建同义词后，数据库的用户就可以直接通过同义词名称访问该同义

33、词所指的数据库对象，而不需要特别指出该对象的所属关系。【例3.19】SYSTEM用户查询XSCJ数据库XS表中所有学生的情况。SELECT*FROM XS;如果没有为XSCJ数据库XS表创建同义词XS，那么SYSTEM用户查询XS表则需指定XS表的所有者。SELECT*FROM ADMIN.XS;,3.4.3删除同义词,1.利用OEM删除同义词 在如图3.13所示的“同义词搜索”界面中，在搜索栏输入搜索条件，单击“开始”按钮查找要删除的同义词后，选中要删除的同义词，单击“删除”，在出现的“确认”界面，单击“是”按钮后，就能删除该同义词。2.利用DROP SYNONYM命令删除同义词 语法格式：

34、DROP PUBLIC SYNONYM schema.synonym_name 说明：PUBLIC表明删除一个公用同义词。Schema指定将要删除的同义词的用户方案。synonym_name为将要删除的同义词名称。【例3.20】删除公用同义词CS_XS。DROP PUBLIC SYNONYM CS_XS;,3.5数据库链接,3.5.1 创建数据库链接 1.利用OEM创建数据库链接【例3.21】利用OEM创建数据库链接MY_LINK。(1)如图3.15所示，在企业管理中选择单击“数据库链接”，进入如图3.16所示的“数据库链接搜索”界面。,图3.15 Oracle企业管理器,图3.16数据库链接

35、搜索界面,3.5.1 创建数据库链接,1.利用OEM创建数据库链接(2)单击“创建”按钮，进入“创建数据库链接”界面，如图3.17所示。,图3.17创建数据库链接界面,3.5.1 创建数据库链接,1.利用OEM创建数据库链接(3)单击“确定”按钮，创建成功后，系统返回到图3.16所示的界面，完成数据库链接操作。2.利用CREATE DATABASE LINK命令创建数据库链接 语法格式：CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO user IDENTIFIED BY password USING connect_string【例3.22

36、】为XSCJ数据库创建一个名为MY_PLINK的公用链接。CREATE PUBLIC DATABASE LINK MY_PLINK CONNECT TO ADMIN IDENTIFIED BY MANAGE USING XSCJ;,3.5.2使用数据库链接,创建了数据库链接，就可以使用远程数据库的对象了。【例3.23】查询远程数据库XSCJ表KC中的所有课程情况。SELECT*FROM ADMIN.KCMY_PLINK;上述查询将通过MY_PLINK数据库链接来访问KC表，也可以为该表创建一个同义词。【例3.24】为XSCJ远程数据库表KC创建一个同义词。CREATE PUBLIC SYNON

37、YM KC FOR ADMIN.KC MY_PLINK;这时数据库对象的全限定标志已被定义，其中包括通过服务名的主机和实例、通过数据库链接的拥有者(ADMIN)和表名(KC)。,3.5.3删除数据库链接,1.利用OEM删除数据库链接 在如图3.16所示的窗口中，选择要删除的数据库链接，单击“删除”按钮，在出现的“确认”界面，单击“是”按钮后，就能成功删除该数据库链接。2.利用DROP DATABASE LINK删除数据库链接 语法格式：DROP DATABASE LINK dblink_name dblink_name为要删除的数据库链接名称。【例3.25】删除公用数据库链接MY_PLINK。

38、DROP DATABASE LINK MY_PLINK;注意：公用数据库链接可由任何有相应权限的用户删除，而私有数据库链接只能由SYS系统用户删除。,3.6数据完整性,Oracle使用完整性约束防止不合法的数据进入到基表中。管理员和开发人员可以定义完整性规则，以增强商业规则，限制数据表中的数据。使用完整性约束有以下几个好处：(1)在数据库应用的代码中增强了商业规则。(2)使用存储过程，完整控制对数据的访问。(3)增强了触发存储数据库过程的商业规则。3.6.1数据完整性的分类 1.域完整性 域完整性又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许空值。,3.6.1数据完整性的分类,

39、【例3.26】定义表KC的同时定义学分的约束条件。CREATE TABLE KC(KCH char(6)NOT NULL,KCM char(8)NOT NULL,XF NUMBER(2)CHECK(总学分=0 AND总学分=10)NULL,/*通过CHECK子句定义约束条件*/BZ VARCHAR2 NULL);2.实体完整性 实体完整性也可以称为行完整性，要求表中的每一行有一个唯一的标识符，这个标识符就是主关键字。3.参照完整性 参照完整性又可以称为引用完整性。参照完整性保证主表中的数据与从表（被参照表）中数据的一致性。主键：在表中能唯一标识表的每个数据行的一个或多个表列。外键：如果一个表中

40、的一个字段或若干个字段的组合是另一个表的主键则称该字段或字段组合为该表的外键。,3.6.1数据完整性的分类,3.参照完整性 例如，对于XSCJ数据库中XS表的每一个学号，在 XS_KC表中都有相关的课程成绩记录，将XS作为主表，学号字段定义为主键，XS_KC作为从表，表中的学号字段定义为外键，从而建立主表和从表之间的联系实现参照完整性。XS和 XS_KC表的对应关系如表3.1、3.2所示。如果定义了两个表之间的参照完整性，则要求：(1)从表不能引用不存在的键值。(2)如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。(3)如果主表中没有关联的记录，则不能将记

41、录添加到从表。(4)如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。,3.参照完整性,3.6.1数据完整性的分类,3.参照完整性 完整性约束是通过限制列数据、行数据和表之间数据来保证数据完整性的有效的方法。约束是保证数据完整性的标准方法。每一种数据完整性类型，如域完整性、实体完整性和参照完整性，都可以由不同的约束类型来保障。约束确保有效的数据输入到列中和确保维护表与表之间的关系。表3.3描述了不同类型的完整性约束。,表3.3完整性约束描述,3.6.2约束的状态,在Oracle中，完整性约束有4种状态：(1)禁止的非校验状态。表示该约束是不起作用的，即使该约束定义依然存储在数据

42、字典。(2)禁止的校验状态。表示对约束列的任何修改都是禁止的。(3)允许的非校验状态或强制状态。该状态可以向表中添加数据，但是与约束有冲突的数据不能添加。(4)允许的校验状态。表示约束处于正常的状态。这时，表中所有的数据，无论是已有的还是新添加的，都必须满足约束条件。,3.6.3域完整性的实现,Oracle可以通过CHECK约束实现域完整性。CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件；若不满足，则数据无法正常输入。(1)通过Oracle企业管理创建与删除CHECK约束在XSCJ数据库的XS_KC表中，学生每门功课的成绩一般在0100的范围内

43、，如果对用户的输入数据要施加这一限制，可按如下操作进行。如图3.18所示，在搜索栏输入搜索条件，单击“开始”按钮进行查找。系统根据输入的条件，查找出相应的表XS_KC，单击“编辑”按钮，进入“编辑表”界面，如图3.19所示。,3.6.3域完整性的实现,图3.18表搜索界面,3.6.3域完整性的实现,图3.19编辑表界面,3.6.3域完整性的实现,在“约束条件”选项卡界面，在“添加”按钮左边的下拉框选择“CHECK”约束条件，单击“添加”，进入“添加CHECK约束条件”界面，如图3.20所示。名称栏输入约束名称CH_CJ，检查条件是“CJ=0 AND CJ=100”，单击“确定”按钮，完成CHE

44、CK约束的创建，系统返回到图3.19所示界面，单击“应用”保存上述操作。,图3.20添加CHECK约束条件界面,3.6.3域完整性的实现,(2)利用SQL语句在创建表时创建CHECK约束 语法格式：CREATE TABLE table_name/*指定表名*/(column_name datatype NOT NULL|NULL DEFAULT constraint_expression/*默认值*/CONSTRAINT check_name CHECK(check_expression),n)/*CHECK约束表达式*/【例3.27】在 XSCJ 数据库中创建表books，其中包含所有的约束

45、定义。CREATE TABLE books(book_id number(10),book_name varchar2(50)not null,book_desc varchar2(50)DEFAULT New book,max_lvl number(3,2)not null,trade_price number(4,1)not null,CONSTRAINT ch_cost CHECK(max_lvl=250);,3.6.3域完整性的实现,(3)利用SQL语句在修改表时创建CHECK约束 语法格式：ALTER TABLE table_name ADD(CONSTRAINT check_nam

46、e CHECK(check_expression)【例3.28】通过修改XSCJ数据库的books表，增加批发价字段trade_price的CHECK约束。ALTER TABLE books ADD(CONSTRAINT ch_price CHECK(trade_price=250);(4)利用SQL语句删除CHECK约束 CHECK约束的删除可在OEM中删除，有兴趣的读者可以自己试一试，在此介绍如何利用SQL命令删除。语法格式：ALTER TABLE table_name DROP CONSTRAINT check_name 功能：在table_name指定的表中，删除名为check_nam

47、e的约束。【例3.29】删除XSCJ数据库中books表批发价字段的CHECK约束。ALTER TABLE books DROP CONSTRAINT ch_price;,3.6.4实体完整性的实现,如前所述，表中应有一个列或列的组合，其值能唯一地标识表中的每一行，选择这样的一列或多列作为主键可实现表的实体完整性。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能取空值。由于 PRIMARY KEY 约束能确保数据的唯一，所以经常用来定义标识列。当为表定义PRIMARY KEY约束时，Oracle 10g为主键列创建唯一索引，实现数据的唯一性，在查询中

48、使用主键时，该索引可用来对数据进行快速访问。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。PRIMARY KEY约束与UNIQUE约束的主要区别如下：(1)一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；(2)PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；PRIMARY KEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值；在创建PRIMARY KEY约束与UNIQUE约束时会自动产

49、生索引。,3.6.4实体完整性的实现,对于PRIMARY KEY约束与UNIQUE约束来说，都是由索引强制实现。在实现PRIMARY KEY约束与UNIQUE约束时，Oracle按照下面过程来实现：(1)如果禁止该约束，则不创建索引。(2)如果约束是允许的，且约束中的列是某个索引的一部分，则该索引用来强制约束。(3)如果约束是允许的，且约束中的列都不是某个索引的一部分，那么按照下面的规则创建索引：如果约束是可延迟的，则在这种约束的列上创建一个非唯一性索引。如果约束是非可延迟的，则创建一个唯一性索引。1.利用OEM创建和删除PRIMAY KEY或UNIQUE约束 利用OEM创建和删除PRIMAY

50、 KEY或UNIQUE约束的操作过程同利用OEM创建和删除CHECK约束基本相同，唯一的区别是在选择约束类型时选择PRIMAY KEY或UNIQUE约束，详细操作请参照利用OEM创建和删除CHECK约束。,3.6.4实体完整性的实现,2.利用SQL语句创建和删除PRIMAY KEY及UNIQUE约束(1)创建表的同时创建PRIMAY KEY或UNIQUE约束语法格式：CREATE TABLE table_name/*指定表名*/(column_name datatype/*定义字段*/CONSTRAINT constraint_name/*定义约束名*/NOT NULLPRIMARY KEY|