oracle10g经典实战第3章数据库的操作.ppt
第第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。如果列值指定为该列的默认值，则用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 INTO语句向学生表插入以下记录：学号学号 姓名姓名 专业名专业名 性别性别 出生时间出生时间 总学分总学分 备注备注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 40NULL061110张蔚 计算机 女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 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表中插入数据：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 张蔚 计算机 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使用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.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 ZXF=0 AND总学分=0 AND CJ=100”，单击“确定”按钮，完成CHECK约束的创建，系统返回到图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，其中包含所有的约束定义。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_name 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_name的约束。【例【例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为主键列创建唯一索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。PRIMARY KEY约束与UNIQUE约束的主要区别如下：(1)一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束；(2)PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL；PRIMARY KEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值；在创建PRIMARY KEY约束与UNIQUE约束时会自动产生索引。3.6.4实体完整性的实现实体完整性的实现 对于PRIMARY KEY约束与UNIQUE约束来说，都是由索引强制实现。在实现PRIMARY KEY约束与UNIQUE约束时，Oracle按照下面过程来实现：(1)如果禁止该约束，则不创建索引。(2)如果约束是允许的，且约束中的列是某个索引的一部分，则该索引用来强制约束。(3)如果约束是允许的，且约束中的列都不是某个索引的一部分，那么按照下面的规则创建索引：如果约束是可延迟的，则在这种约束的列上创建一个非唯一性索引。如果约束是非可延迟的，则创建一个唯一性索引。1.利用利用OEM创建和删除创建和删除PRIMAY KEY或或UNIQUE约束约束 利用OEM创建和删除PRIMAY 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|UNIQUE /*定义约束类型*/,n)/*n表示可定义多个字段*/在语法格式中，通过关键字PRIMARY KEY、UNIQUE说明所创建的约束类型。3.6.4实体完整性的实现实体完整性的实现 2.利用利用SQL语句创建和删除语句创建和删除PRIMAY KEY及及UNIQUE约束约束 【例【例3.30】对XSCJ数据库中XS表的学号字段创建PRIMARY KEY约束，对身份证号码字段定义UNIQUE约束。CREATE TABLE XS (XH char(6)NOT NULL CONSTRAINT PK_XH PRIMARY KEY,XM char(8)NOT NULL,INDENTITY char(20)CONSTRAINT UN_ID UNIQUE,ZYM char(10)NULL,XB char(2)NOT NULL,CSSJ ldate NOT NULL,ZXF number(2)NULL,BZ varchar2(100)NULL,RXSJ date );3.6.4实体完整性的实现实体完整性的实现2.利用利用SQL语句创建和删除语句创建和删除PRIMAY KEY及及UNIQUE约束约束(2)利用修改表创建PRIMAY KEY或UNIQUE约束 语法格式语法格式：ALTER TABLE table_name ADD(CONSTRAINT constraint_name PRIMARY KEY(column_name,n)说明：说明：这是创建PRIMAY KEY约束，ADD CONSTRAINT表示对table_name表增加一个约束，约束名由constraint_name指定，约束类型为PRIMARY KEY。索引字段由column_name参数指定，可包含一列或多列。语法格式：语法格式：ALTER TABLE table_name ADD(CONSTRAINT constraint_name UNIQUE(column_name,n)这是创建UNIQUE约束。3.6.4实体完整性的实现实体完整性的实现 【例【例3.31】先在XSCJ数据库中创建表XS，然后通过修改表，对学号字段创建PRIMARY KEY约束，对身份证号码字段定义UNIQUE约束。CREATE TABLE XS (XH char(6)NOT NULL,XM char(8)NOT NULL,INDENTITY char(20),ZYM char(10)NULL,XB char(2)NOT NULL,CSSJ date NOT NULL,ZXF number(2)NULL,BZ varchar2(100)NULL,RXSJ date );ALTER TABLE XS ADD(CONSTRAINT PK_XS PRIMARY KEY(XH);ALTER TABLE XS ADD(CONSTRAINT UN_XS UNIQUE(INDENTITY);3.6.4实体完整性的实现实体完整性的实现2.利用利用SQL语句创建和删除语句创建和删除PRIMAY KEY及及UNIQUE约束约束(3)删除PRIMARY KEY或UNIQUE约束语法格式：ALTER TABLE table_name DROP CONSTRAINT constraint_name,n;【例3.32】删除创建的UN_XS的UNIQUE约束。ALTER TABLE XS DROP CONSTRAINT UN_XS;3.6.5参照完整性的实现参照完整性的实现 对两个相关联的表(主表与从表)进行数据插入和删除时，通过参照完整性保证它们之间数据的一致性。利用FOREIGN KEY定义从表的外键，PRIMARY KEY约束定义主表中的主键(不允许为空)，可实现主表与从表之间的参照完整性。定义表间参照关系，可先定义主键，再对从表定义外键约束（根据查询的需要可先对从表的该列创建索引）。对于FOREIGN KEY约束来说，在创建时应该考虑以下因素：(1)在删除主表之前，必须删除FOREIGN KEY约束。(2)如果不删除或禁止FOREIGN KEY约束，则不能删除主表。(3)在删除包含主表的表空间之前，必须删除FOREIGN KEY约束。3.6.5参照完整性的实现参照完整性的实现 1.利用利用OEM定义表之间的参照关系定义表之间的参照关系 例如，要建立XS表和XS_KC表之间的参照完整性，操作步骤如下：(1)按照前面所介绍的方法定义主表的主键。在此，定义XS表中的学号字段为主键。(2)如图3.21所示，选择“FOREIGN”约束条件，单击“添加”按钮，进入“添加FOREIGN约束条件”界面，如图3.22所示。(3)在名称栏输入约束名称FK_XH。在表列项的“可用列”列表选择“XH”字段添加到所选列列表。在引用表列项的引用表选择所关联表；单击“开始”，引入关联表所有字段到可用列列表，选择“XH”字段添加至所选列。单击“确定”，系统会自动完成创建工作。图图3.21修改表界面修改表界面 图图3.22添加添加FOREIGN约束条件界面约束条件界面 3.6.5参照完整性的实现参照完整性的实现 2.利用利用OEM删除表间的参照关系删除表间的参照关系利用OEM删除表间的参照关系和删除CHECK约束的方法一样，请参照删除CHECK约束的方法。3.利用利用SQL命令定义表间的参照关系命令定义表间的参照关系 前面已介绍了创建主键(PRMARY KEY约束)的方法，在此将介绍通过SQL命令创建外键的方法。(1)创建表时同时定义外键约束语法格式：语法格式：CREATE TABLE table_name /*指定表名*/(column_name datatype FOREIGN KEYREFERENCES ref_table(ref_column),n /*n表示可定义多个字段*/3.6.5参照完整性的实现参照完整性的实现 【例【例3.33】在XSCJ数据库中创建主表XS，XS.XH为主键，然后定义从表XS_KC，XS_KC.XH为外键。首先创建主表：CREATE TABLE XS (XH char(6)NOT NULL CONSTRAINT PK_XH PRIMARY KEY,XM char(8)NOT NULL,ZYM char(10)NULL,XB char(2)NOT NULL,CSSJ date NOT NULL,ZXF number(1)NULL,BZ varchar2(100)NULL);3.6.5参照完整性的实现参照完整性的实现然后创建从表：CRAETE TABLE XS_KC(XH char(6)NOT NULL CONSTRAINT FK_XH FOREIGN KEY,REFENCES XS(XH),KCH char(3)NOT NULL,CJ number(2,1),XF number(1),);(2)通过修改表定义外键约束语法格式：语法格式：ALTER TABLE table_nameADD CONSTRAINT constraint_nameFOREIGN KEY(column,n)REFERENCES ref_table(ref_column,n)3.6.5参照完整性的实现参照完整性的实现 【例【例3.34】假设XSCJ数据库中KC表为主表，KC.KCH字段已定义为主键。XS_KC表为从表，如下示例用于将XS_KC.KCH字段定义为外键。ALTER TABLE XS_KC ADD(CONSTRAINT FK_KC FOREIGN KEY(KCH)REFERENCES KC(KCH);4.利用利用SQL语句删除表间的参照关系语句删除表间的参照关系删除表间的参照关系，实际上删除从表的外键约束即可。语法格式与前面其它约束删除的格式相同。【例【例3.35】删除上面对XS_KC.KCH字段定义的FK_kc外键约束。ALTER TABLE XS_KC DROP CONSTRAINT FK_KC;
