Oracle数据库期末复习知识点整理

1、基础知识表3.2 Oracle数据类型数 据 类 型格 式描 述charCHAR(size BYTE | CHAR)固定长度字符域，size规定字符的最大长度，最大可为2 000个字节。BYTE和CHAR关键字表示长度单位是字节还是字符，默认为BYTEncharNCHAR(size)多字节字符集的固定长度字符域，长度随字符集而定，最多为2 000个字符或2 000个字节varchar2VARCHAR2(size BYTE | CHAR)可变长度字符域，最大长度可达4 000个字符nvarchar2NVARCHAR2(size)多字节字符集的可变长度字符域，长度随字符集而定，最多为4 000个字

2、符或4 000个字节dateDATE用于存储全部日期的固定长度(7个字节)字符域，时间作为日期的一部分存储其中。除非通过设置NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以DD-MON-RR格式表示，如13-APR-99表示1999年4月13日timestampTIMESTAMP(precision)用亚秒的粒度存储一个日期和时间。precision是亚秒粒度的位数，默认为6，范围为09timestampTIMESTAMP(precision) WITH TIME ZONE通过另外存储一个时区偏差来扩展timestamp数据类型，这个时区偏差定义本地时区与UTC之间的差值t

3、imestampTIMESTAMP(precision) WITH LOCAL TIME ZONE通过另外存储一个时区偏差来扩展timestamp数据类型，该类型不存储时区偏差，但存储时间作为数据库时区的标准形式，时间信息将从本地时区转换到数据库时区interval year tomonthINTERVAL YEAR (precision) TO MONTH用年和月的形式存储一段时间，precision指定的是年的位数，默认为2interval day tosecondINTERVAL DAY (precision) TOSECOND(s_precision)用天、时、分和秒的形式存储一段时间

4、，precision是这个时间的天部分所要求的最大位数，默认为2。s_precision是这个时间的秒部分所要求的小数点右边的位数，默认为6numberNUMBER(p, s)可变长度数值列，允许值为0、正数和负数。p指总位数，默认为38，s指小数点右边的位数，默认为0 floatFLOAT(p)浮点型数值列，p表示数值位数longLONG可变长度字符域，最大长度可到2GBrawRAW(size)表示二进制数据的可变长度字符域，size为最大长度，最长为2 000个字节long rawLONG RAW表示二进制数据的可变长度字符域，最长为2 GBblobBLOB二进制大对象，最大长度为4 GB

5、clobCLOB字符大对象，最大长度为4 GBnclobNCLOB多字节字符集的CLOB数据类型，最大长度为4 GBbfileBFILE外部二进制文件，大小由操作系统决定rowidROWID表示RowID的二进制数据，Oracle 8 RowID的数值为10个字节urowidUROWID(size)用于数据寻址的二进制数据，size规定最大长度，默认为4 000个字节binary_floatBINARY_FLOAT表示浮点类型，比number效率更高，32位binary_doubleBINARY_DOUBLE表示双精度数字类型，64位表3.3 XSB的表结构列 名数 据 类 型是 否 可 空默

6、 认 值说 明列 名 含 义XHchar(6)无主键，前2位年级，中间2位班级号，后2位序号学号XMchar(8)无姓名XBchar(2)“男”性别CSSJdate无出生时间ZYchar(12)无专业ZXFnumber(2)00 总学分160总学分BZvarchar2(200)无备注表3.4 KCB的表结构列 名数 据 类 型是 否 可 空默 认 值说 明列 名 含 义KCHchar(3)无主键课程号KCMchar(16)无课程名KKXQnumber(1)1只能为18开课学期XSnumber(2)0学时XFnumber(1)0学分表3.5 CJB的表结构列 名数 据 类 型是 否 可 空默 认

7、 值说 明列 名 含 义XHchar(6)无主键学号KCHchar(3)无主键课程号CJnumber(2)无成绩操作表创建表CREATE TABLE schema. table_name(column_name datatype DEFAULT expression column_constraint,n,n)PCTFREE integerPCTUSED integerINITRANS integerMAXTRANS integerTABLESPACE tablespace_nameSTORGE storage_clauseCLUSTER cluster_name(cluster_column

8、,n)ENABLE | DISABLE AS subquery【例】使用CRETE TABLE命令为XSCJ数据库建立表XSB，表结构参照表3.3。打开SQL*Plus工具，以system方案连接数据库，输入以下语句：CREATE TABLE XSB(XH char(6) NOT NULL PRIMARY KEY,XM char(8) NOT NULL,XB char(2) DEFAULT 1 NOT NULL,CSSJ date NOT NULL,ZY char(12) NULL,ZXF number(2) NULL,BZ varchar2(200) NULL);修改表ALTER TABLE

9、 schema. table_name ADD(column_name datatype DEFAULT expressioncolumn_constraint,n) /*增加新列*/ MODIFY( datatype DEFAULT expression column_constraint,n) /*修改已有列的属性*/ STORAGE storage_clause *修改存储特征*/ DROP drop_clause /*删除列或约束条件*/【例】使用ALTER TABLE语句修改XSCJ数据库中的表。（1）在表XSB中增加两列：JXJ（奖学金等级）、DJSM（奖学金等级说明）。ALTER

10、 TABLE XSBADD ( JXJ number(1),DJSM varchar2(40) DEFAULT 奖金1000元);（2）在表XSB中修改名为DJSM的列的默认值。ALTER TABLE XSBMODIFY ( DJSM DEFAULT 奖金800元 );（3）在表XSB中删除名为JXJ和DJSM的列。ALTER TABLE XSBDROP COLUMN JXJ;ALTER TABLE XSBDROP COLUMN DJSM;（4）修改KCB表的存储特征。ALTER TABLE KCBPCTFREE 20 MAXTRANS 200;（5）为表XS_JSJ添加主键。ALTER TA

11、BLE XS_JSJADD (CONSTRAINT PK_JSJ PRIMARY KEY(XH) );删除表DROP TABLE schema. table_name操作表数据插入记录INSERT INTO table_name(column_list) VALUES(constant1,constant2,)【例】向XSCJ数据库的表XSB中插入如下的一行： 王林 计算机 男 50可以使用如下的PL/SQL语句：INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF) VALUES(, 王林, 男,TO_DATE(,YYYYMMDD), 计算机, 50);删除记录

12、DELETE FROM table_nameview_nameWHERE condition【例】将XSCJ数据库的XSB表中总学分小于50的行删除，使用如下的PL/SQL语句。DELETE FROM XSBWHERE ZXF50;修改查询结果中的列标题【例】查询XSB表中计算机系同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分FROM XSBWHERE ZY= 计算机;模式匹配string_expression NOT LIKE string_expression ESCAPE escape_cha

13、racter 【例】查询CP表中产品名含有“冰箱”的产品情况。SELECT * FROM CP WHERE CPMCLIKE %冰箱%;【例】查询XSB表中姓“王”且单名的学生情况。SELECT * FROM XSB WHERE XM LIKE 王_;范围比较expression NOT BETWEEN expression1 AND expression2【例】 查询CP表中价格在2000元与4000元之间的产品情况。SELECT * FROM CP WHERE JG BETWEEN 2000 AND 4000;执行结果如右图所示。 查询XSB表中不在1989年出生的学生情况。SELECT

14、* FROM XSB WHERE CSSJ NOT BETWEEN TO_DATE(, YYYYMMDD) AND TO_DATE(, YYYYMMDD);使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression IN ( expression ,n)【例】查询CP表中库存量为“200”“300”和“500”的情况。SELECT * FROM CP WHERE KCL IN (200,300,500);空值比较【例】查询XSCJ数据库中总学分尚不定的学生情况。SELECT

15、* FROM XSB WHERE ZXF IS NULL;子查询IN子查询。IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression NOT IN ( subquery )【例】查找未选修离散数学的学生的情况。SELECT XH, XM, ZY, ZXF FROM XSBWHERE XH NOT IN ( SELECT XHFROM CJBWHERE KCH IN ( SELECT KCH FROM KCB WHERE KCM = 离散数学 );比较子查询。expression | | = | != | ALL | SOME | ANY ( subquery )【例

16、】查找比所有计算机系学生年龄都大的学生。SELECT * FROM XSBWHERE CSSJ ALL( SELECT CSSJ FROM XSB WHERE ZY= 计算机);EXISTS子查询。EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为： NOT EXISTS ( subquery )【例】查找选修了全部课程的同学姓名。SELECT XM FROM XSB WHERE NOT EXISTS ( SELECT * FROM

17、 KCB WHERE NOT EXISTS ( SELECT * FROM CJB WHERE XH=XSB.XH AND KCH=KCB.KCH) );查询对象【例】查找与号同学所选修课程一致的同学的学号。本例即要查找这样的学号y，对所有的课程号x，若号同学选修了该课，那么y也选修了该课。SELECT DISTINCT XHFROM CJB CJ1WHERE NOT EXISTS( SELECT *FROM CJB CJ2WHERE CJ2.XH = AND NOT EXISTS ( SELECT *FROM CJB CJ3WHERE CJ3.XH= CJ1.XH AND CJ3.KCH =

18、 CJ2. KCH);【例】在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。SELECT XM, ZYFROM (SELECT * FROM XSBWHERE CSSJ= 80;JOIN关键字指定的连接内连接。内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。【例】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XSB.XH , XM , KCM , CJ FROM XSB JOIN CJB JOIN KCB ON CJB.KCH = KCB.KCH ON XSB.XH = CJB.XH

19、 WHERE KCM = 计算机基础 AND CJ=80;外连接。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下三种。l 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；l 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；l 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。【例】查找被选修了的课程的选修情况和所有开设的课程名。SELECT CJB.* , KCM FROM CJB RIGHT JOIN

20、KCB ON CJB.KCH= KCB.KCH;交叉连接交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第1个表的每一行与第2个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。【例】列出学生所有可能的选课情况。SELECT XH, XM, KCH, KCM FROM XSB CROSS JOIN KCB;统计函数SUM和AVG函数、MAX和MIN函数、COUNT函数【例】求选修了课程的学生总人数。SELECT COUNT(DISTINCT XH) AS 选修了课程的总人数 FROM CJB;GROUP BY分组统计GROUP BY子句用于对表或视图中的数据按字段分组，语法格式

21、为：GROUP BY ALL group_by_expression ,n【例】求XSCJ数据库中各专业的学生数。SELECT ZY AS 专业,COUNT(*) AS 学生数 FROM XSB GROUP BY ZY; HAVING子句HAVING子句的语法格式为： HAVING 【例】其中，search_condition为查询条件，与WHERE子句的查询条件类似，不过不同的是HAVING子句可以使用统计函数，而WHERE子句不可以。查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS 学号,AVG(CJ) AS 平均成绩 FROM CJB WHERE XH

22、 IN ( SELECT XH FROM XSB WHERE ZY= 通信工程) GROUP BY XH HAVING AVG(CJ) =85;ORDER BY子句ORDER BY子句的语法格式为： ORDER BY order_by_expression ASC | DESC ,n 【例】将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECT XM AS 姓名, KCM AS 课程名, CJ AS 成绩 FROM XSB, KCB, CJB WHERE XSB.XH=CJB.XH AND CJB.KCH= KCB.KCHAND KCM= 计算机基础 AND ZY= 计算机 ORDE

23、R BY CJ DESC;UNION子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其语法格式为： | ( ) UNION A LL | ( ) UNION A LL | ( ) n 【例】查找学号为和学号为两位同学的信息。SELECT *FROM XSBWHERE XH= UNION ALLSELECT *FROM XSBWHERE XH= ;视图创建视图CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statementWITH CHECK

24、OPTIONCONSTRAINT constraint_nameWITH READ ONLY【例】查找平均成绩在80分以上的学生的学号和平均成绩。本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CREATE OR REPLACE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT XH, AVG(CJ) FROM CJBGROUP BY XH;再对XS_KC_AVG视图进行查询。SELECT * FROM XS_KC_AVG WHERE score_avg=80;更新视图【例】在X

25、SCJ数据库中使用以下语句创建可更新视图CS_XS1。CREATE OR REPLACE VIEW CS_XS1 AS SELECT * FROM XSB WHERE ZY= 通信工程;插入数据、修改数据、删除数据与表相似删除视图DROP VIEW schema.view_name修改视图跟创建视图一样【例】修改视图CS_KC的定义，包括学号、姓名、选修的课程号、课程名和成绩。CREATE OR REPLACE FORCE VIEW CS_KCASSELECT XS.XH, XS.XM, XS_KC.KCH, KC.KCM, CJFROM XS, XS_KC, KCWHERE XS.XH=XS

26、_KC.XH AND XS_KC.KCH=KC.KCH AND ZYM=通信工程WITH CHECK OPTION;索引创建索引CREATE UNIQUEBITMAP INDEX /*索引类型*/schema.index_name /*索引名称*/ON schema.table_name(column_name | column_expression ASCDESC ,n)LOGGING | NOLOGGING/*指定是否创建相应的日志记录*/COMPUTE STATISTICS/*生成统计信息*/COMPAESS | NOCOMPRESS/*对复合索引进行压缩*/TABLESPACE tab

27、lespace_name /*索引所属表空间*/SORT | NOSORT/*指定是否对表进行排序*/REVERSE【例】根据XSB表的姓名列和出生时间列创建复合索引。CREATE INDEX XSB_indON XSB(XM, CSSJ)维护索引ALTER INDEX schema.index_nameLOGGING | NOLOGGINGTABLESPACE tablespace_nameSORT | NOSORTREVERSERENAME TO new_index_name【例】重命名索引kc_name_idx。ALTER INDEX kcb_name_idxRENAME TO kcb_

28、idx;删除索引DROP INDEX schema.index_name数据完整性（详见表格创建）域完整性（CHECK）实体完整性（PRIMARY KEY/UNIQUE）参照完整性（REFERNCES）用户自定义函数创建函数语法格式：CREATE OR REPLACE FUNCTION function_name /*函数名称*/(parameter_name1, mode1 datatype1, /*参数定义部分*/parameter_name2, mode2 datatype2,parameter_name3, mode3 datatype3,)RETURN return_datatype

29、 /*定义返回值类型*/IS | AS声明变量BEGINfunction_body; /*函数体部分*/RETURN scalar_expression; /*返回语句*/END function_name;下面给出一个函数，说明其3种参数的合法性。CREATE OR REPLACE FUNCTION explain_parameter(in_pmt IN char,out_pmt OUT char,in_out_pmt IN OUT char)RETURN charASreturn_char char;BEGINRETURN(return_char);END explain_paramete

30、r;函数语句序列及其可能出现的情况如下。in_pmt:= hello;该语句是错误的，因为IN类型的参数只能作为形参来传递值，不能在函数体中赋值。return_char:=in_pmt;该语句语法正确。因为IN类型参数本身就是用来传递值，而return_char是作为返回值变量。通过IN类型参数in_pmt赋值给return_char。out_pmt:= hello;该语句正确。因为out_pmt作为OUT类型参数，在函数体内被赋值是允许的。return_char:= out_pmt;该语句不正确。因为OUT类型参数不能传递值。in_out_pmt:= world;该语句正确。因为IN OUT

31、参数可以在函数体中被赋值。return_char:=in_out_pmt;该语句正确，因为IN OUT 类型参数既能传递值，也可以复制。【例】计算某门课程全体学生的平均成绩。CREATE OR REPLACE FUNCTION average (cnum IN char)RETURN numberASavger number;/*定义返回值变量*/BEGINSELECT AVG(CJ) INTO avgerFROM CJBWHERE KCH=cnumGROUP BY KCH;RETURN(avger);END;调用函数variable_name:=function_name(实参1,实参2,)

32、【例】用函数count_num统计表XSB中有多少男同学。SET SERVEROUTPUT ON;DECLARE man_num number;BEGINman_num:=count_num(男);DBMS_OUTPUT.PUT_LINE(TO_CHAR(man_num);END;输出结果为：14。删除函数语法格式：DROP FUNCTION schema.function_name存储过程创建存储过程CREATE OR REPLACE PROCEDURE cedure_name /*定义过程名*/ (parameter parameter_mode date_type D

33、EFAULT expr , n) /*定义参数类型及属性*/ IS | AS declare_section/*变量声明部分*/BEGINsql_statement /*PL/SQL过程体*/END procedure_name;【例】计算某专业总学分大于40的人数，存储过程使用了一个输入参数和一个输出参数。CREATE OR REPLACE PROCEDURE count_grade( zy IN char, person_num OUT number )ASBEGINSELECT COUNT(XH)INTO person_numFROM XSBWHERE ZY=zy AND ZXF40;E

34、ND;调用存储过程 EXEC | EXECUTE procedure_name ( parameter = value | variable ,n) ;【例】调用例7.1中的存储过程proc。SET SERVEROUT ON;EXEC proc;输出结果：hello world以下命令运行的结果与之相同：BEGINproc;END;删除存储过程DROP PROCEDURE schema. procedure_name;触发器DML触发器CREATE OR REPLACE TRIGGER schema. trigger_name /*指定触发器名称*/ BEFOREAFTERINSTEAD OF

35、 DELETE | INSERT | UPDATE OF column,n /*定义触发器种类*/OR DELETE | INSERT | UPDATE OF column,n ON schema. table_nameview_name /*指定操作对象*/ FOR EACH ROW WHEN(condition) sql_statementn/*PL/SQL块*/【例】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显示。创建表table1：CREATE TABLE table1(a number)

36、;创建INSERT触发器table1_insertCREATE OR REPLACE TRIGGER table1_insertAFTER INSERT ON table1DECLARE str char(100) :=TRIGGER IS WORKING;BEGINDBMS_OUTPUT.PUT_LINE(str);END;向table1中插入一行数据：INSERT INTO table1 VALUES(10);输出结果：TRIGGER IS WORKING替代触发器【例】在XSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视

37、图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。首先创建视图：CREATE VIEW stu_viewAS SELECT XSB.XH, ZY, KCH, CJFROM XSB, CJBWHERE XSB.XH=CJB.XH创建INSTEAD OF触发器：CREATE TRIGGER InsteadTrigINSTEAD OF INSERT ON stu_view FOR EACH ROWDECLARE xm char(8);xb char(2);cssj date;BEGINxm:=佚名;xb:= 男;cssj:= 01-1月-90;INSERT INTO XSB(XH, XM, XB, CSSJ, ZY) VALUES(:NEW.XH,xm, xb, cssj, :NEW.ZY);INSERT INTO CJB VALUES(: