2023年Oracle数据库期末复习知识点

基础知识表3.2Oracle数据类型数据类型格式描述charCHAR[(size[BYTE|CHAR])]固定长度字符域，size规定字符的最大长度，最大可为2000个字节。BYTE和CHAR关键字表达长度单位是字节还是字符，默认为BYTEncharNCHAR[(size)]多字节字符集的固定长度字符域，长度随字符集而定,最多为2000个字符或2000个字节varchar2VARCHAR2(size[BYTE|CHAR])可变长度字符域，最大长度可达4000个字符nvarchar2NVARCHAR2[(size)]多字节字符集的可变长度字符域，长度随字符集而定，最多为4。00个字符或4OOO个字节dateDATE用于存储所有日期的固定长度（7个字节）字符域，时间作为日期的一部分存储其中。除非通过设立NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以DD-M0N一RR格式表达,如13—APR-99表达1999年4月13日timestampTIMESTAMP[(precision)]用亚秒的粒度存储一个日期和时间。precision是亚秒粒度的位数,默认为6,范围为0〜9timestampTIMESTAMP[(precision)]WITHTIMEZONE通过此外存储一个时区偏差来扩展timestamp数据类型，这个时区偏差定义本地时区与UTC之间的差值timestampTIMESTAMP[(precision)]WITHLOCALTIMEZONE通过此外存储一个时区偏差来扩展timestamp数据类型，该类型不存储时区偏差，但存储时间作为数据库时区的标准形式，时间信息将从本地时区转换到数据库时区intervalyeartomonthINTERVALYEAR[(precision)]TOM0NTH用年和月的形式存储一段时间，precision指定的是年的位数，默认为2interva1daytosecondINTERVALDAY[(Precision)]TOSECOND[(s_precision)]用天、时、分和秒的形式存储一段时间，Precision是这个时间的天部分所规定的最大位数，默认为2。s_precision是这个时间的秒部分所规定的小数点右边的位数，默认为6numberNUMBERL(p[,s])]可变长度数值列，允许值为0、正数和负数。p指总位数，默认为38,s指小数点右边的位数，默认为0f1oatFLOAT[(p)]浮点型数值列，p表达数值位数iongLONG可变长度字符域，最大长度可至ij2GBrawRAW(size)表达二进制数据的可变长度字符域,size为最大长度，最长为2000个字节longrawLONGRAW表达二进制数据的可变长度字符域，最长为2GB8WHEREKCM=，离散数学，bbb))；比较子查询。expression{<|<=|=|>I>=I!=I<>}{ALLISOME|ANY}(subquery)【例】查找比所有计算机系学生年龄都大的学生。SELECT*FROMXSBoWHERECSSJ<ALL(SELECTCSSJ。oFROMXSBWHEREZY廿计算机'EXISTS子查询。EXISTS谓词用于测试子查询的结果是否为空表,若子查询的结果集不为空，则EXISTS返回TRUE,否则返回FALSE。EXISTS还可与NOT结合使用，即NOTEXISTS淇返回值与EXIST刚好相反。格式为：[NOT]EXISTS(subquery)【例】查找选修了所有课程的同学姓名。SELECTXMFROMXSBoWHERENOTEXISTSSELECT*8。FROMKCB。WHERENOTEXISTS(SELECT*。。FROMCJB。gWHEREXH=XSB.XHANDKCH=KCB.KCH000)0);查询对象【例】查找与101102号同学所选修课程一致的同学的学号。本例即要查找这样的学号y,对所有的课程号x,若101102号同学选修了该课，那么y也选修了该课。SELECTDISTINCTXHFROMCJBCJ1WHERENOTEXISTS(SELECT*。FROMCJBCJ2b»WHERECJ2.XH='101102,ANDNOTEXISTS(SELECT*FROMCJBCJ3。oWHERECJ3.XH=CJ1.XH…ANDCJ3.KCH=CJ2.KCH6)6。)；【例】在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。SELECTXM,ZYFROM(SELECT*FROMXSBWHERECSSJ<TO_DATE(,19900101，JYYYYMMDD'));自然连接【例】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECTXSB.XH,XM,KCM,CJFROMXSB,KCB,CJBWHEREXSB.XH=CJB.XHANDKCB.CH=CJB.KCH-ANDKCM二计算机基础，ANDCJ>=80;JOIN关键字指定的连接内连接。内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。【例】用FROM的JOIN关键字表达下列查询:查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECTXSB.XH,XM,KCM,CJFROMXSBoJOINCJBJOINKCBONCJB.KCH=KCB.KCH。ONXSB.XH=CJB.XH-WHEREKCM廿计算机基础'ANDCJ>=80；外连接。外连接的结果表不仅包含满足连接条件的行，还涉及相应表中的所有行。外连接涉及以下三种。•左外连接(LEFTOUTERJOIN)：结果表中除了涉及满足连接条件的行外,还涉及左表的所有行；•右外连接(RIGHTOUTERJOIN):结果表中除了涉及满足连接条件的行外,还涉及右表的所有行；•完全外连接(FULLOUTERJOIN)：结果表中除了涉及满足连接条件的行外,还涉及两个表的所有行。

【例】查找被选修了的课程的选修情况和所有开设的课程名。SELECTCJB.*,KCMFROMCJBRIGHTJOINKCBONCJB.KCH=KCB.KCH；交叉连接交叉连接事实上是将两个表进行笛卡尔积运算，结果表是由第1个表的每一行与第2个表的每一行拼接后形成的表,因此结果表的行数等于两个表行数之积。【例】列出学生所有也许的选课情况。SELECTXH,XM,KCH,KCMFROMXSBCROSSJOINKCB;记录函数SUM和AVGSUM和AVG函数、MAX和MIN函数SUM和SUM和AVG函数、MAX和MIN函数、COUNT函数【例】求选修了课程的学生总人数。SELECTCOUNT(DISTINCTXH)AS选修了课程的总人数FROMCJB;GROUPBY分组记录GROUPBY子句用于对表或视图中的数据按字段分组,语法格式为:GROUPBY[ALL]group_by_expression【例】求XSCJ数据库中各专业的学生数。SELECTZYAS专业,COUNT(*)AS学生数oFR0MXSBGROUPBYZY;HAVING子句HAVING子句的语法格式为：[HAVING<search_condition>]【例】其中，search_condition为查询条件，与WHERE子句的查询条件类似，但是不同的是HAVING子句可以使用记录函数，而WHERE子句不可以。查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECTXHAS学号,AVG(CJ)AS平均成绩FROMCJBooWHEREXHIN^(SELECTXHFROMXSB。WHEREZY=，通信工程，)“GROUPBYXHoHAVINGAVG(CJ)>=85;ORDERBY子句ORDERBY子句的语法格式为：[ORDERBY{order_by_expression[ASC|DESC]}[,...n]【例】将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECTXMAS姓名，KCMAS课程名,CJAS成绩-FROMXSB,KCB,CJBoWHEREXSB.XH=CJB.XHANDCJB.KCH=KCB.KCHoANDKCM廿计算机基础，ANDZY廿计算机，ORDERBYCJDESC;UNION子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其语法格式为：{<queryspecification>|(<queryexpression>)}UNION[ALL]<queryspecification>|(<queryexpression>)[UNION[ALL]<queryspecification>|(<queryexpression>)[…n]]【例】查找学号为101101和学号为101210两位同学的信息。SELECT*FROMXSBWHEREXH='101101'UNIONALLSELECT*FROMXSBoWHEREXH廿101210，；视图创建视图CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[schema.]view_name[(column_name[,...n])]ASselect_statement“WITHCHECKOPTION[CONSTRAINTconstraint_name]]o[WITHREADONLY]【例】查找平均成绩在80分以上的学生的学号和平均成绩。本例一方面创建学生平均成绩视图XS_KC_AVG,涉及学号(在视图中列名为num)和平均成绩(在视图中列名为score_avg)oCREATE0RREPLACEVIEWXS_KC_AVG(num,score_avg)ASSELECTXH,AVG(CJ)oFROMCJB»GROUPBYXH;再对XS_KC_AVG视图进行查询。SELECT*oFROMXS_KC_AVGeWHEREscore_avg>=80;更新视图【例】在XSCJ数据库中使用以下语句创建可更新视图cS_XSloCREATEORREPLACEVIEWCS_XS1-ASSELECT*。FROMXSB®WHEREZY='通信工程，；插入数据、修改数据、删除数据与表相似删除视图DR0PVIEW[schema.]view_name修改视图跟创建视图同样【例】修改视图CSJKC的定义，涉及学号、姓名、选修的课程号、课程名和成绩。CREATEORREPLACEFORCEVIEWCS_KCoASSELECTXS.XH,XS.XM,XS_KC.KCH,KC.KCM,CJ。FROMXS,XS_KC,KCoWHEREXS.XH=XS_KC.XHANDXS_KC.KCH=KC.KCH。ANDZYM±通信工程，WITHCHECKOPTION;索引创建索引CREATE[UNIQUE|BITMAP]INDEX/*索引类型*/“schema.]index_name/*索引名称*/ON[schema.]table_name(column_name|column_expression[ASCIDESC][，…n])[LOGGINGINOLOGGING]-。/*指定是否创建相应的日记记录*/[COMPUTESTATISTICS>。。。/*生成记录信息*/[COMPAESS|NOCOMPRESS]。。。/*对复合索引进行压缩*/[TABLESPACEtablespace_name]/*索引所属表空间*/[SORT|NOSORT]/*指定是否对表进行排序*/[REVERSE]【例】根据XSB表的姓名列和出生时间列创建复合索引oCREATEINDEXXSB_ind9NXSB(XM,CSSJ)维护索引ALTERINDEX[schema.]index_name[LOGGING|NOLOGGING][TABLESPACEtablespace_name][SORT|NOSORT][REVERSE][RENAMETOnew_index_name]【例】重命名索引kc_name_idxoALTERINDEXkcb_name_idxRENAMETOkcb_idx;删除索引DROPINDEX[schema.]index_name数据完整性(详见表格创建)域完整性(CHECK)实体完整性(PRIMARYKEY/UNIQUE)参照完整性(REFERNCES)用户自定义函数创建函数语法格式：CREATE[ORREPLACE]FUNCTIONfunction_name/*函数名称*/(叩arameter_namel,modeldatatype1,。。/*参数定义部分*/oparameter_name2,mode2datatype2,叩arameter_name3,mode3datatype3,…)RETURNreturn_datatype。/*定义返回值类型*/“IS|AS}[声明变量]BEGINgfunction_body;/*函数体部分*/[RETURNsca1ar_expression；]/*返回语句*/oEND[function_name];下面给出一个函数，说明其3种参数的合法性。CREATEORREPLACEFUNCTIONexp1ain_parameter

表3.3XSB的表结构blobBLOB二进制大对象,最大长度为4GBc1obCLOB字符大对象,最大长度为4GBnc1obNCLOB多字节字符集的CLOB数据类型，最大长度为4GBbfi1eBFILE外部一进制文献,大小由操作系统决定rowidROWID表达RowID的二进制数据，Oracle8RowID的数值为10个字节urowidUROWID[(size)]用于数据寻址的二进制数据，size规定最大长度,默认为40。0个字吊binary_floatBINARY_FLOAT表达浮点类型，比number效率更高，32位binary_doubleBINARY_DOUBLE表达双精度数字类型，64位表3.4KCB的表结构列名数据类型是否可空默认值说明列名含义XHchar(6)X无主键，前2位年级，中间2位班级号，后2位序号学号XMchar(8)X无姓名XBchar(2)X“男”性别CSSJdateX无出生时间ZYchar(12)q无专业ZXFnumber(2)0OS总学分＜160总学分BZvarchar2(200)无备注表3.5CJB的表结构列名数据类型是否可空默认值说明列名含义KCHchar(3)X无主键课程号KCMchar(16)X无课程名KKXQnumber(l)q1只能为1〜8开课学期XSnumber(2)70学时XFnumber(l)X0学分列名数据类型是否可空默认值说明列名含义XHchar(6)X无主键学号in_pmtINchar,out_pmtOUTchar,in_out_pmtINOUTchar)RETURNcharAS®return_charchar;oBEGIN。〈函数语句序列,RETURN(return_char);END[explain_parameter]；函数语句序列及其也许出现的情况如下。in_pmt:=’hello7;该语句是错误的，由于IN类型的参数只能作为形参来传递值，不能在函数体中赋值。return_char:二in_pmt;该语句语法对的。由于IN类型参数自身就是用来传递值，而return_char是作为返回值变量。通过IN类型参数in_pmt赋值给return_char。out_pmt:=fhe11o该语句对的。由于out_pmt作为OUT类型参数，在函数体内被赋值是允许的。return_char:=out_pmt;该语句不对的。由于OUT类型参数不能传递值。in_out_pmt:=’worid';该语句对的。由于INOUT参数可以在函数体中被赋值°return_char:=in_out_pmt；该语句对的，由于INOUT类型参数既能传递值，也可以复制。【例】计算某门课程全体学生的平均成绩。CREATEORREPLACEFUNCTIONaverage(cnumINchar)oRETURNnumberAS阳vgernumber;。。/*定义返回值变量*/BEGINoSELECTAVG(CJ)INT0avgerFROMCJB。WHEREKCH=cnumooGR0UPBYKCH;^RETURN(avger);END;调用函数variable_name~function_name[(实参1,实参2,・..)]【例】用函数count_num登记表XSB中有多少男同学。SETSERVEROUTPUTON;DECLAREmannumnumber;BEGINman_num：=count_num('男f);DBMS_OUTPUT.PUT_LINE(T0_CHAR(man_num))；END；输出结果为:14。删除函数语法格式:DROPFUNCTION[schema.]function_name存储过程创建存储过程CREATE[ORREPLACE]PROCEDURE[schema.]procedure_name/*定义过程名*/”(parameterparameter_modedate_type[DEFAULTexpr][,...n])]。/*定义参数类型及属性*/{IS|AS)寸declare_section0。/*变量声明部分*/BEGINsql_statement6。/*PL/SQL过程体*/oEND[procedure_name][;]【例】计算某专业总学分大于40的人数，存储过程使用了一个输入参数和一个输出参数。CREATEORREPLACEPROCEDUREcount_grade(zyINchar,person_numOUTnumber)ASBEGINSELECTCOUNT(XH)TOperson_numaFR0MXSBWHEREZY=zyANDZXF>40;END;调用存储过程[{EXEC|EXECUTE}]procedure_name[([parameter=>]value|@variab1e][;]【例】调用例7.1中的存储过程proc。SETSERVEROUTON；EXECproc;输出结果：he11owor1d以下命令运营的结果与之相同：BEGIN叩roc;END;删除存储过程DROPPROCEDURE[schema.]procedure_name;触发器DML触发器CREATE[ORREPLACE]TRIGGER[schema.]trigger_name/*指定触发器名称*/。{BEFOREIAFTERIINSTEADOF}“DELETE|INSERT|UPDATE[OFco1umn,.・.n]}/*定义触发器种类*/0[OR{DELETE|INSERT|UPDATE[OFcolu]}]ON[schema.]{tabie_name|view_name}。/*指定操作对象*/。[FOREACHROW[WHEN(condition)]]sql_statement[...n]。。/*PL/SQL块*/【例】创建一个表tabie1,其中只有一列ao在表上创建一个触发器，每次插入操作时，将变量str的值设为uTRIGGERISWORKING”并显示。创建表table1：CREATETABLEtable1(anumber)；创建INSERT触发器table1JnsertCREATEORREPLACETRIGGERtable1JnsertAFTERINSERT0Ntab1elDECLARE双rchar(l00)-TRIGGERISWORKING';BEGINoDBMS_0UTPUT.PUT_LINE(str)；END；向tablei中插入一行数据：INSERTINTOtable1VALUES(10);输出结果：TRIGGERISWORKING替代触发器【例】在XSCJ数据库中创建视图stu_view,包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB,是不可更新视图。可以在视图上创建INSTEADOF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。一方面创建视图：CREATEVIEWstuviewASSELECTXSB.XH,ZY,KCH,CJ。FROMXSB,CJB。WHEREXSB.XH=CJB.XH仓ij建INSTEAD0F触发器：CREATETRIGGERInsteadTrig-INSTEAD0FINSERT0Nstu_viewFOREACHROWDECLAREoxmchar(8);xbchar(2);cssjdate；BEGINxm：='佚名:xb：='男'；ocssj:='O1-1月-90'；4NSERTINTOXSB(XH,XM,XB,CSSJ,ZY)“VALUES(:NEW.XH,xm,xb,cssj,:NEW.ZY);“NSERTINTOCJBVALUES(:NEW.XH,:NEW.KCH,：NEW.CJ);END;向视图插入一行数据：INSERTINTOstu_viewVALUESC091102',计算机」10「，85);查看数据是否插入：SELECT*FROMstu_viewWHEREXH=7091102,;执行结果如图所示。查看与视图关联的XSB表的情况：SELECT*FROMXSBWHEREXH='091102';系统触发器从Oracle8i开始Qracle提供的系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据库系统事件涉及数据库服务器的启动(STARTUP)或关闭(SHUTDOWN),数据库服务器犯错(SERVERERROR)等。语法格式：CREATEORREPLACETRIGGER[scache.]trigger_name{BEFORE|AFTER}。{ddl_event_1istIdatabse_event_list}ON{DATABASE|[schema.]SCHEMA}。[when_c1ause]“igger_body【例】创建一个用户事件触发器，记录用户SYSTEM所删除的所有对象。一方面以用户SYSTEM身份连接数据库，创建一个存储用户信息的表：CREATETABLEdropped_objects(^>object_namevarchar2(30),object_typevarchar(20),dropped_datedate)；创建BEFOREDROP触发器，在用户删除对象之前记录到信息表dropped_objects中。CREATEORREPLACETRIGGERdropped_ob'triggeroBEFOREDROPONSYSTEM.SCHEMABEGININSERTINTOdropped_objects^VALUES(ora_diet_obj_name,ora_dict_obj_type,SYSDATE)；END;现在删除SYSTEM模式下的一些对象，并查询表dropped_objects:DROPTABLEtablet；DROPTABLEtable2;SELECT*FROMdropped_objects；触发器删除删除触发器使用DROPTRIGGER语句，语法格式：DROPTRIGGER[schema.]trigger_name启用和禁用触发器ALTERTRIGGER[schema.]trigger_nameDISABLE|ENABLE;其中,DISABLE表达禁用触发器,ENABLE表达启用触发器。例如要禁用触发器delxs,使用如下语句：ALTERTRIGGERdel_xsDISABLE；假如要启用或禁用一个表中的所有触发器，还可以使用如下的语法：ALTERTABLEtable_name{DISABLE|ENABLE}-ALLTRIGGERS；KCHchar(3)X无主键课程号CJnumber(2)q无成绩操作表创建表CREATETABLE[schema.]table_name(。column_namedatatype[DEFAULTexpression][column_constraint][9...n]<table_constraint>[,...n])[PCTFREEinteger][PCTUSEDinteger]o[INITRANSinteger]“MAXTRANSinteger]“TABLESPACEtablespace_name][STORGEstorage_c1ause][CLUSTERc1uster_name(cluster_column,...n)][ENABLEIDISABLE]MASsubquery]【例】使用CRETETABLE命令为XSCJ数据库建立表XSB,表结构参照表3.3。打开SQL*Plus工具，以system方案连接数据库，输入以下语句：CREATETABLEXSBXHchar(6)相0TNULLPRIMARYKEY,XMchar(8)-NOTNULL,XBchar(2)DEFAULT711NOTNULL,oCSSJdateNOTNULL,亿丫char(12)NULL,ZXFnumber(2)^NULL,BZvarchar2(200)NULL)；修改表ALTERTABLE[schema.]table_name[ADD(column_namedatatype[DEFAULTexpression][column_constraint]9...n)]eooo8。/*增长新歹ij*/[MODIFY([datatype][DEFAULTexpression]g®[column_constraint],...n)]/*修改已有列的属性*/STORAGEstorage_clause]超。。“*修改存储特性*/[DROPdrop_clause卜。/*删除列或约束条件*/【例】使用ALTERTABLE语句修改XSCJ数据库中的表。⑴在表XSB中增长两列：JXJ(奖学金等级)、DJSM(奖学金等级说明)。ALTERTABLEXSBADD(JXJnumber(l),。DJSMvarchar2(40)DEFAULT奖金1000元’);(2)在表XSB中修改名为DJSM的列的默认值。ALTERTABLEXSBMODIFY(DJSMDEFAULT，奖金800元，);(3)在表XSB中删除名为JXJ和DJSM的列。ALTERTABLEXSBDROPCOLUMNJXJ;ALTERTABLEXSBDROPCOLUMNDJSM;(4)修改KCB表的存储特性。ALTERTABLEKCBPCTFREE20MAXTRANS200;(5)为表XS_JSJ添加主键。ALTERTABLEXS_JSJ-ADD(CONSTRAINTHPK_JSJnPRIMARYKEY(XH))；删除表DROPTABLE[schema.]table_name操作表数据插入记录INSERTINTOtable_name[(co1umn_list)]VALUES(constant1,constan【例】向XSCJ数据库的表XSB中插入如下的一行：o101101王林计算机男1990021050可以使用如下的PL/SQL语句：INSERTINT0XSB(XH,XM,XB,CSSJ,ZY,ZXF)VALUESC101101'王林「男',TO_DATE('1990021刀'YYYYMMDD'),

计算机;50);删除记录DELETEFROMtable_nameIview_name“WHEREcondition]【例】将XSCJ数据库的XSB表中总学分小于50的行删除，使用如下的PL/SQL语句。DELETEFROMXSBWHEREZXF<50；修改记录UPDATEtable_nameIview_name。SETcolumn_name=expression“WHEREcondition]【例】将姓名为“罗林琳”的同学的专业改为“通信工程''，备注改为“转专业学习“，学号改为“101241”。UPDATEXSoSETZY=，通信工程,，BZ=，转专业学习:XH+10124roWHEREXM廿罗林琳，；查关基本语法SELECTselectjistSELECTselectjistSELECTselectjist/*指定要选择的列及其限定*/

SELECTselectjist/*指定要选择的列及其限定*/oFROMtablesourceoFROMtablesource°/*FROM子句，指定表或视图*//*WH