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

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) 如果列值为空，则值

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

3、);请读者使使用INSERTINTO语句向向学生表表插入以以下记录录：学号姓姓名专专业名名性性别别出出生生时间总总学分备备注注061102王王平计计算机女女1986-09-0238NULL061103王王燕计计算机机女女1985-10-0640NULL061104韦韦严平平计计算机男男1986-08-2640NULL061105吴吴庆红红计算算机男男1985-12-3036有有两门门不及格格061106李李方方方计计算机男男1986-11-2040NULL061107李李明计计算机机男男1986-05-0140NULL061108林林一帆帆计计算机男男1985-08-0542已提提前修完完一门

4、课课061109张张强民民计计算机男男1984-08-1140NULL061110张张蔚计计算机机女女1987-07-2240NULL061111赵赵琳计计算机机女女1986-03-1840NULL061112罗罗林琳琳计计算机女女1984-01-3040NULL3.1.1插入入表记录录用SELECT语句进进行查询询，可以以发现表表中已经经增加的的记录，如图3.1所所示。图3.1插入新新数据后后查询的的结果3.1.1插入入表记录录【例3.2】建立了表表test。CREATETABLEtest(xmchar(20)NOT NULL,zyvarchar(30)DEFAULT(计计算机),njnum

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

6、(8) NOTNULL,speialitychar(10)NULL);用如下的的INSERT语句向向XS1表中插插入数据据：INSERTINTOXS1SELECTXH,XM,ZYMFROM XSWHEREZYM=计算算机;3.1.1插入入表记录录这条INSERT语句句将XS表中专专业名为为计算算机的的各记录录的学号号、姓名名和专业业名列的值插插入到XS1表表的各行行中。用用SELECT语句可可查看插插入结果果：numnamespeciality-061101王王林林计计算机机061102王王平平计计算机机061103王王燕燕计计算机机061104韦韦严严平计计算算机061105吴吴庆庆红计计算

7、算机061106李李方方方计计算算机061107李李明明计计算机机061108林林一一帆计计算算机061109张张强强民计计算机机061110张张蔚蔚计计算机机061111赵赵琳琳计计算机机061112罗罗林林琳计计算机机从Oracle 10g开始始新增了了MERGE语语句，使使用这个个语句可可以实现现对表的的更新或或插入。3.1.1插入入表记录录语法格式式：MERGEINTO table_nameUSINGtable_name ON (join_condition)WHEN MATCHEDTHENUPDATESETWHEN NOTMATCHED THENINSERT() VALUES()Jo

8、ni_condition：连连接条件件。【例3.4】 检查查表XS_JSJ中的的数据是是否和表表XS的的数据相相匹配，如果匹匹配则使使用INSERT子子句执行行插入数数据行。首先在表表XS_JSJ使用INSERT语语句添加加一行数数据：INSERTINTOXS_JSJ(XH,XM,ZYM,XB,CSSJ,ZXF)VALUES(101112,霍甲甲,计算机机,女,TO_DATE(19860130,YYYYMMDD),36);使用MERGE语句XS_JSJ表表中新增增的数据据插入表表XS中中：MERGEINTO XS aUSINGXS_JSJbON(a.XH=b.XH)WHEN NOTMATCHE

9、D THENINSERTValues(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz);用SELECT语句进进行查询询，可以以发现表表中已经经增加一一条姓名名为霍甲甲的学生生记录。3.1.2删除除表记录录1.使使用DELETE删除数数据在PL/SQL语言中中，删除除行可以以使用DELETE语语句。语法格式式：DELETEFROMtable_nameview_nameWHEREcondition该语句的的功能为为从table_name指指定的表表或view_name指定定的视图图中删除除满足condition查查询条条件的行行，若省省略该条条件，表表示删除除所有的的

10、行。【例3.5】将XSCJ数数据库的的XS表表中总学学分小于于39的的行删除除，使用用如下的的PL/SQL语句。DELETEFROMXSWHEREZXF=0AND总学分分=0ANDCJ=100”，单击“确定”按钮，完成CHECK约约束的创创建，系系统返回回到图3.19所示界界面，单单击“应应用”保保存上述述操作。图3.20添加加CHECK约约束条件件界面3.6.3域完完整性的的实现(2)利利用用SQL语句在在创建表表时创建建CHECK约约束语法格式式：CREATETABLEtable_name/*指定定表名*/(column_namedatatypeNOTNULL |NULLDEFAULTco

11、nstraint_expression/*默认值值*/CONSTRAINT check_nameCHECK(check_expression),n)/*CHECK约束表表达式*/【例3.27】在 XSCJ数数据库库中创建建表books，其中中包含所所有的约约束定义义。CREATETABLEbooks(book_id number(10),book_namevarchar2(50) notnull,book_descvarchar2(50) DEFAULTNewbook,max_lvl number(3,2) notnull,trade_pricenumber(4,1)notnull,CONST

12、RAINT ch_costCHECK(max_lvl=250);3.6.3域完完整性的的实现(3)利利用用SQL语句在在修改表表时创建建CHECK约约束语法格式式：ALTERTABLEtable_nameADD( CONSTRAINTcheck_nameCHECK(check_expression)【例3.28】通过修改改XSCJ数据据库的books表，增加批批发价字字段trade_price的CHECK约束束。ALTERTABLEbooksADD( CONSTRAINTch_priceCHECK(trade_price=250);(4)利利用用SQL语句删删除CHECK约束CHECK约束束的

13、删除除可在OEM中中删除，有兴趣趣的读者者可以自自己试一一试，在在此介绍如何利利用SQL命令令删除。语法格式式：ALTERTABLEtable_nameDROP CONSTRAINTcheck_name功能：在在table_name指定定的表中中，删除除名为check_name的约束束。【例3.29】删除XSCJ数数据库中中books表表批发价价字段的的CHECK约约束。ALTERTABLEbooksDROP CONSTRAINTch_price;3.6.4实体体完整性性的实现现如前所述述，表中中应有一一个列或或列的组组合，其其值能唯唯一地标标识表中中的每一一行，选选择这样的的一列或或多列作作

14、为主键键可实现现表的实实体完整整性。一个表只只能有一一个PRIMARY KEY约约束，而而且PRIMARY KEY约约束中的的列不能取空值值。由于于 PRIMARYKEY 约束束能确保保数据的的唯一，所以经经常用来来定义标标识列。当当为表定定义PRIMARYKEY约束时时，Oracle10g为为主键列列创建唯唯一索引引，实现数据据的唯一一性，在在查询中中使用主主键时，该索引引可用来来对数据据进行快快速访问问。如果PRIMARY KEY约约束是由由多列组组合定义义的，则则某一列列的值可可以重复复，但PRIMARY KEY约约束定义义中所有有列的组组合值必必须唯一一。PRIMARY KEY约束束与

15、UNIQUE约束束的主要要区别如如下：(1)一一个个数据表表只能创创建一个个PRIMARYKEY约约束，但但一个表表中可根根据需要要对不同的列创创建若干干个UNIQUE约束束；(2)PRIMARYKEY字段的的值不允允许为NULL，而UNIQUE字字段的值值可取NULL；PRIMARY KEY约束束与UNIQUE约束束的相同同点在于于：二者者均不允允许表中中对应字字段存在重复值值；在创创建PRIMARYKEY约束与与UNIQUE约束时时会自动动产生索索引。3.6.4实体体完整性性的实现现对于PRIMARYKEY约束与与UNIQUE约束来来说，都都是由索索引强制制实现。在实现现PRIMARY K

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

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

18、NULLPRIMARY KEY| UNIQUE/*定义约约束类型型*/,n)/*n表示示可定义义多个字字段*/在语法格格式中，通过关关键字PRIMARY KEY、UNIQUE说说明所创创建的约约束类型型。3.6.4实体体完整性性的实现现2.利利用SQL语语句创建建和删除除PRIMAY KEY及UNIQUE约约束【例3.30】对XSCJ数据据库中XS表的的学号字字段创建建PRIMARYKEY约约束，对对身份证号码码字段定定义UNIQUE约束束。CREATETABLEXS(XHchar(6) NOTNULLCONSTRAINT PK_XH PRIMARYKEY,XMchar(8) NOTNULL,

19、INDENTITYchar(20)CONSTRAINT UN_ID UNIQUE,ZYMchar(10)NULL,XBchar(2) NOTNULL,CSSJ ldate NOTNULL,ZXFnumber(2) NULL,BZvarchar2(100) NULL,RXSJ date);3.6.4实体体完整性性的实现现2.利利用SQL语语句创建建和删除除PRIMAY KEY及UNIQUE约约束(2)利利用用修改表表创建PRIMAYKEY或UNIQUE约束束语法格式式：ALTERTABLEtable_nameADD( CONSTRAINTconstraint_name PRIMARYKEY(co

20、lumn_name,n)说明：这是创建建PRIMAY KEY约束束，ADDCONSTRAINT表示对对table_name表增增加一个个约束，约束名由constraint_name指指定，约约束类型型为PRIMARYKEY。索引引字段由由column_name参参数指定，可包含含一列或或多列。语法格式式：ALTERTABLEtable_nameADD(CONSTRAINTconstraint_nameUNIQUE(column_name,n)这是创建建UNIQUE约束。3.6.4实体体完整性性的实现现【例3.31】先在XSCJ数数据库中中创建表表XS，然后通通过修改改表，对对学号字字段创建建P

21、RIMARY KEY约束束，对身身份证号号码字段段定义UNIQUE约约束。CREATETABLEXS(XHchar(6) NOTNULL,XMchar(8) NOTNULL,INDENTITYchar(20),ZYMchar(10)NULL,XBchar(2) NOTNULL,CSSJ dateNOT NULL,ZXFnumber(2) NULL,BZvarchar2(100) NULL,RXSJ date);ALTERTABLEXSADD(CONSTRAINTPK_XSPRIMARY KEY(XH);ALTERTABLEXSADD(CONSTRAINTUN_XSUNIQUE(INDENTIT

22、Y);3.6.4实体体完整性性的实现现2.利利用SQL语语句创建建和删除除PRIMAY KEY及UNIQUE约约束(3)删删除PRIMARY KEY或UNIQUE约约束语法格式式：ALTERTABLEtable_nameDROP CONSTRAINTconstraint_name,n;【例3.32】删除创创建的UN_XS的UNIQUE约约束。ALTERTABLEXSDROP CONSTRAINTUN_XS;3.6.5参照照完整性性的实现现对两个相相关联的的表(主主表与从从表)进进行数据据插入和和删除时时，通过过参照完完整性保保证它们之之间数据据的一致致性。利利用FOREIGNKEY定义从从表的

23、外外键，PRIMARYKEY约约束定义义主表中中的主键键(不允允许为空空)，可可实现主主表与从从表之间间的参照照完整性性。定义表间间参照关关系，可可先定义义主键，再对从从表定义义外键约约束（根根据查询询的需要要可先对从从表的该该列创建建索引）。对于FOREIGNKEY约束来来说，在在创建时时应该考考虑以下下因素：(1)在在删删除主表表之前，必须删删除FOREIGNKEY约束。(2)如如果果不删除除或禁止止FOREIGNKEY约约束，则则不能删删除主表表。(3)在在删删除包含含主表的的表空间间之前，必须删删除FOREIGNKEY约束。3.6.5参照照完整性性的实现现1.利利用OEM定定义表之之间

24、的参参照关系系例如，要要建立XS表和和XS_KC表表之间的的参照完完整性，操作步步骤如下下：(1)按按照照前面所所介绍的的方法定定义主表表的主键键。在此此，定义义XS表表中的学学号字段段为主键。(2)如如图图3.21所示示，选择择“FOREIGN”约束条条件，单单击“添添加”按按钮，进进入“添添加FOREIGN约束条条件”界界面，如如图3.22所所示。(3)在在名名称栏输输入约束束名称FK_XH。在在表列项项的“可可用列”列表选选择“XH”字字段添加到所所选列列列表。在在引用表表列项的的引用表表选择所所关联表表；单击击“开始始”，引引入关联表所有有字段到到可用列列列表，选择“XH”字段添添加至

25、所所选列。单击“确定”，系统统会自动完成成创建工工作。图3.21修改改表界面面图3.22添加加FOREIGN约束束条件界界面3.6.5参照照完整性性的实现现2.利利用OEM删删除表间间的参照照关系利用OEM删除除表间的的参照关关系和删删除CHECK约束的的方法一一样，请请参照删删除CHECK约束束的方法法。3.利利用SQL命命令定义义表间的的参照关关系前面已介介绍了创创建主键键(PRMARYKEY约约束)的的方法，在此将将介绍通通过SQL命令创建建外键的的方法。(1)创创建建表时同同时定义义外键约约束语法格式式：CREATETABLEtable_name/*指指定表名名*/(column_namedatatypeFOREIGNKEYREFERENCES ref_table(ref_column),n/*n表表示可定定义多个个字段*/3.6.5参照照完整性性的