2023年Oracle数据库期末复习知识点整理.docx

基础知识
表3.2 Oracle数据类型
数 据 类 型
格 式
描 述
char
CHAR[(size [BYTE | CHAR])]
固定长度字符域，size规定字符的最大长度，最大可为2 000个字节。BYTE和CHAR关键字表达长度单位是字节还是字符，默认为BYTE
nchar
NCHAR[(size)]
多字节字符集的固定长度字符域，长度随字符集而定，最多为2 000个字符或2 000个字节
varchar2
VARCHAR2(size [BYTE | CHAR])
可变长度字符域，最大长度可达4 000个字符
nvarchar2
NVARCHAR2[(size)]
多字节字符集的可变长度字符域，长度随字符集而定，最多为4 000个字符或4 000个字节
date
DATE
用于存储所有日期的固定长度(7个字节)字符域，时间作为日期的一部分存储其中。除非通过设立NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以DD-MON-RR格式表达，如13-APR-99表达1999年4月13日
timestamp
TIMESTAMP[(precision)]
用亚秒的粒度存储一个日期和时间。precision是亚秒粒度的位数，默认为6，范围为0～9
timestamp
TIMESTAMP[(precision)] WITH
TIME ZONE
通过此外存储一个时区偏差来扩展timestamp数据类型，这个时区偏差定义本地时区与UTC之间的差值
timestamp
TIMESTAMP[(precision)] WITH
LOCAL TIME ZONE
通过此外存储一个时区偏差来扩展timestamp数据类型，该类型不存储时区偏差，但存储时间作为数据库时区的标准形式，时间信息将从本地时区转换到数据库时区
interval year to
month
INTERVAL YEAR [(precision)] TO MONTH
用年和月的形式存储一段时间，precision指定的是年的位数，默认为2
interval day to
second
INTERVAL DAY [(precision)] TO
SECOND[(s_precision)]
用天、时、分和秒的形式存储一段时间，precision是这个时间的天部分所规定的最大位数，默认为2。s_precision是这个时间的秒部分所规定的小数点右边的位数，默认为6
number
NUMBER[(p[, s])]
可变长度数值列，允许值为0、正数和负数。p指总位数，默认为38，s指小数点右边的位数，默认为0
float
FLOAT[(p)]
浮点型数值列，p表达数值位数
long
LONG
可变长度字符域，最大长度可到2GB
raw
RAW(size)
表达二进制数据的可变长度字符域，size为最大长度，最长为2 000个字节
long raw
LONG RAW
表达二进制数据的可变长度字符域，最长为2 GB
blob
BLOB
二进制大对象，最大长度为4 GB
clob
CLOB
字符大对象，最大长度为4 GB
nclob
NCLOB
多字节字符集的CLOB数据类型，最大长度为4 GB
bfile
BFILE
外部二进制文献，大小由操作系统决定
rowid
ROWID
表达RowID的二进制数据，Oracle 8 RowID的数值为10个字节
urowid
UROWID[(size)]
用于数据寻址的二进制数据，size规定最大长度，默认为4 000个字节
binary_float
BINARY_FLOAT
表达浮点类型，比number效率更高，32位
binary_double
BINARY_DOUBLE
表达双精度数字类型，64位
表3.3 XSB的表结构
列 名
数 据 类 型
是 否 可 空
默 认 值
说 明
列 名 含 义
XH
char(6)
×
无
主键，前2位年级，中间2位班级号，后2位序号
学号
XM
char(8)
×
无

姓名
XB
char(2)
×
“男”

性别
CSSJ
date
×
无

出生时间
ZY
char(12)
√
无

专业
ZXF
number(2)
√
0
0 ≤总学分<160
总学分
BZ
varchar2(200)
√
无

备注
表3.4 KCB的表结构
列 名
数 据 类 型
是 否 可 空
默 认 值
说 明
列 名 含 义
KCH
char(3)
×
无
主键
课程号
KCM
char(16)
×
无

课程名
KKXQ
number(1)
√
1
只能为1～8
开课学期
XS
number(2)
√
0

学时
XF
number(1)
×
0

学分
表3.5 CJB的表结构
列 名
数 据 类 型
是 否 可 空
默 认 值
说 明
列 名 含 义
XH
char(6)
×
无
主键
学号
KCH
char(3)
×
无
主键
课程号
CJ
number(2)
√
无

成绩

操作表
创建表
CREATE TABLE [schema.] table_name
(
column_name datatype [DEFAULT expression] [column_constraint][,…n]
<table_constraint>[,…n]
)
[PCTFREE integer]
[PCTUSED integer]
[INITRANS integer]
[MAXTRANS integer]
[TABLESPACE tablespace_name]
[STORGE storage_clause]
[CLUSTER cluster_name(cluster_column,…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 [schema.] table_name
[ ADD(column_name datatype [DEFAULT expression][column_constraint],…n) ]
/*增长新列*/
[ MODIFY([ datatype ] [ DEFAULT expression ]
[column_constraint],…n) ] /*修改已有列的属性*/
[ STORAGE storage_clause ] *修改存储特性*/
[ DROP drop_clause ] /*删除列或约束条件*/
【例】使用ALTER TABLE语句修改XSCJ数据库中的表。
（1）在表XSB中增长两列：JXJ（奖学金等级）、DJSM（奖学金等级说明）。
ALTER TABLE XSB
ADD ( JXJ number(1),
DJSM varchar2(40) DEFAULT '奖金1000元');
（2）在表XSB中修改名为DJSM的列的默认值。
ALTER TABLE XSB
MODIFY ( DJSM DEFAULT '奖金800元' );
（3）在表XSB中删除名为JXJ和DJSM的列。
ALTER TABLE XSB
DROP COLUMN JXJ;
ALTER TABLE XSB
DROP COLUMN DJSM;
（4）修改KCB表的存储特性。
ALTER TABLE KCB
PCTFREE 20 MAXTRANS 200;
（5）为表XS_JSJ添加主键。
ALTER TABLE XS_JSJ
ADD (CONSTRAINT "PK_JSJ" PRIMARY KEY(XH) );
删除表
DROP TABLE [schema.] table_name 
操作表数据
插入记录
INSERT INTO table_name[(column_list)]
VALUES(constant1,constant2,…)
【例】向XSCJ数据库的表XSB中插入如下的一行：
101101 王林 计算机 男 19900210 50
可以使用如下的PL/SQL语句：
INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF)
VALUES('101101', '王林', '男',TO_DATE('19900210','YYYYMMDD'), '计算机', 50);

删除记录
DELETE FROM table_name∣view_name
[WHERE condition]
【例】将XSCJ数据库的XSB表中总学分小于50的行删除，使用如下的PL/SQL语句。
DELETE FROM XSB
WHERE ZXF<50;
修改记录
UPDATE table_name∣view_name
SET column_name=expression,[…n]
[WHERE condition]
【例】将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“101241”。
UPDATE XS
SET ZY='通信工程',
BZ='转专业学习',
XH='101241'
WHERE XM= '罗林琳';
查询
基本语法
SELECT select_list /*指定要选择的列及其限定*/
FROM table_source /*FROM子句，指定表或视图*/
[ WHERE search_condition ] /*WHERE子句，指定查询条件*/
[ GROUP BY group_by_expression ] /*GROUP BY子句，指定分组表达式*/
[ HAVING search_condition ] /*HAVING子句，指定分组记录条件*/
[ ORDER BY order_expression [ ASC | DESC ]] /*ORDER子句，指定排序表达式顺序*/
选择指定列
SELECT column_name [ , column_name [,…n] ]
FROM table_name
[WHERE search_condition]
【例】查询XSB表中ZXF大于50同学的XH、XM和ZXF。
SELECT XH, XM, ZXF
FROM XSB
WHERE ZXF>50;
修改查询结果中的列标题
【例】查询XSB表中计算机系同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。
SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分
FROM XSB
WHERE ZY= '计算机';
模式匹配
string_expression [ NOT ] LIKE string_expression [ ESCAPE escape_character ]
【例】查询CP表中产品名具有“冰箱”的产品情况。
SELECT *
FROM CP
WHERE CPMC
LIKE '%冰箱%';
【例】查询XSB表中姓“王”且单名的学生情况。
SELECT *
FROM XSB
WHERE XM LIKE '王_';
范围比较
expression [ NOT ] BETWEEN expression1 AND expression2
【例】① 查询CP表中价格在2023元与4000元之间的产品情况。
SELECT *
FROM CP
WHERE JG BETWEEN 2023 AND 4000;
执行结果如右图所示。
② 查询XSB表中不在1989年出生的学生情况。
SELECT *
FROM XSB
WHERE CSSJ NOT BETWEEN TO_DATE('19890101', 'YYYYMMDD')
AND TO_DATE('19891231', 'YYYYMMDD');
使用IN关键字可以指定一个值表，值表中列出所有也许的值，当表达式与值表中的任意一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：
expression IN ( expression [,…n])
【例】查询CP表中库存量为“200”“300”和“500”的情况。
SELECT *
FROM CP
WHERE KCL IN (200,300,500);
空值比较
【例】查询XSCJ数据库中总学分尚不定的学生情况。
SELECT *
FROM XSB
WHERE ZXF IS NULL;
子查询
IN子查询。IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：
expression [ NOT ] IN ( subquery )
【例】查找未选修离散数学的学生的情况。
SELECT XH, XM, ZY, ZXF
FROM XSB
WHERE XH NOT IN
( SELECT XH
FROM CJB
WHERE KCH IN
( SELECT KCH
FROM KCB
WHERE KCM = '离散数学'
)
);
比较子查询。
expression { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY } ( subquery )
【例】查找比所有计算机系学生年龄都大的学生。
SELECT *
FROM XSB
WHERE 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 KCB
WHERE NOT EXISTS
( SELECT *
FROM CJB
WHERE XH=XSB.XH AND KCH=KCB.KCH
)
);
查询对象
【例】查找与101102号同学所选修课程一致的同学的学号。
本例即要查找这样的学号y，对所有的课程号x，若101102号同学选修了该课，那么y也选修了该课。
SELECT DISTINCT XH
FROM CJB CJ1
WHERE NOT EXISTS
( SELECT *
FROM CJB CJ2
WHERE CJ2.XH ='101102' AND NOT EXISTS
( SELECT *
FROM CJB CJ3
WHERE CJ3.XH= CJ1.XH
AND CJ3.KCH = CJ2. KCH
)
);
【例】在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。
SELECT XM, ZY
FROM (SELECT * FROM XSB
WHERE CSSJ<TO_DATE('19900101', 'YYYYMMDD'));
自然连接
【例】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。
SELECT XSB.XH, XM, KCM, CJ
FROM XSB, KCB, CJB
WHERE XSB.XH = CJB.XH AND KCB.CH = CJB. KCH
AND KCM = '计算机基础' AND CJ >= 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
WHERE KCM = '计算机基础' AND CJ>=80;
外连接。外连接的结果表不仅包含满足连接条件的行，还涉及相应表中的所有行。外连接涉及以下三种。
l 左外连接（LEFT OUTER JOIN）：结果表中除了涉及满足连接条件的行外，还涉及左表的所有行；
l 右外连接（RIGHT OUTER JOIN）：结果表中除了涉及满足连接条件的行外，还涉及右表的所有行；
l 完全外连接（FULL OUTER JOIN）：结果表中除了涉及满足连接条件的行外，还涉及两个表的所有行。
【例】查找被选修了的课程的选修情况和所有开设的课程名。
SELECT CJB.* , KCM
FROM CJB RIGHT JOIN 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子句用于对表或视图中的数据按字段分组，语法格式为：
GROUP BY [ ALL ] group_by_expression [,…n]
【例】求XSCJ数据库中各专业的学生数。
SELECT ZY AS 专业,COUNT(*) AS 学生数
FROM XSB
GROUP BY ZY;
HAVING子句
HAVING子句的语法格式为：
[ HAVING <search_condition> ]
【例】其中，search_condition为查询条件，与WHERE子句的查询条件类似，但是不同的是HAVING子句可以使用记录函数，而WHERE子句不可以。
查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。
SELECT XH AS 学号,AVG(CJ) AS 平均成绩
FROM CJB
WHERE XH 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.KCH
AND KCM= '计算机基础' AND ZY= '计算机'
ORDER BY CJ DESC;
UNION子句
使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其语法格式为：
{ <query specification> | (<query expression> ) }
UNION [ A LL ] <query specification> | (<query expression> )
[ UNION [ A LL ] <query specification> | (<query expression> ) […n] ]
【例】查找学号为101101和学号为101210两位同学的信息。
SELECT *
FROM XSB
WHERE XH= '101101'
UNION ALL
SELECT *
FROM XSB
WHERE XH= '101210';
视图
创建视图
CREATE [ OR REPLACE ] [FORCE | NOFORCE] VIEW [schema.]view_name
[ ( column_name [ ,…n ] ) ]
AS
select_statement
[WITH CHECK OPTION[CONSTRAINT constraint_name]]
[WITH 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 CJB
GROUP BY XH;
再对XS_KC_AVG视图进行查询。
SELECT *
FROM XS_KC_AVG
WHERE score_avg>=80;
更新视图
【例】在XSCJ数据库中使用以下语句创建可更新视图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_KC
AS
SELECT XS.XH, XS.XM, XS_KC.KCH, KC.KCM, CJ
FROM XS, XS_KC, KC
WHERE XS.XH=XS_KC.XH AND XS_KC.KCH=KC.KCH
AND ZYM='通信工程'
WITH CHECK OPTION;
索引
创建索引
CREATE [UNIQUE∣BITMAP] INDEX /*索引类型*/
[schema.]index_name /*索引名称*/
ON [schema.]table_name(column_name | column_expression [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING] /*指定是否创建相应的日记记录*/
[COMPUTE STATISTICS] /*生成记录信息*/
[COMPAESS | NOCOMPRESS] /*对复合索引进行压缩*/
[TABLESPACE tablespace_name] /*索引所属表空间*/
[SORT | NOSORT] /*指定是否对表进行排序*/
[REVERSE]
【例】根据XSB表的姓名列和出生时间列创建复合索引。
CREATE INDEX XSB_ind
ON XSB(XM, CSSJ)
维护索引
ALTER INDEX [schema.]index_name
[LOGGING | NOLOGGING]
[TABLESPACE tablespace_name]
[SORT | NOSORT]
[REVERSE]
[RENAME TO new_index_name]
【例】重命名索引kc_name_idx。
ALTER INDEX kcb_name_idx
RENAME TO kcb_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 /*定义返回值类型*/
{IS | AS}
[声明变量]
BEGIN
function_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 char
AS
return_char char;
BEGIN
<函数语句序列>
RETURN(return_char);
END [explain_parameter];
函数语句序列及其也许出现的情况如下。
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参数可以在函数体中被赋值。
return_char:=in_out_pmt;
该语句对的，由于IN OUT 类型参数既能传递值，也可以复制。
【例】计算某门课程全体学生的平均成绩。
CREATE OR REPLACE FUNCTION average (cnum IN char)
RETURN number
AS
avger number; /*定义返回值变量*/
BEGIN
SELECT AVG(CJ) INTO avger
FROM CJB
WHERE KCH=cnum
GROUP BY KCH;
RETURN(avger);
END;
调用函数
variable_name:=function_name[(实参1,实参2,…)]
【例】用函数count_num登记表XSB中有多少男同学。
SET SERVEROUTPUT ON;
DECLARE
man_num number;
BEGIN
man_num:=count_num('男');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(man_num));
END;
输出结果为：14。
删除函数
语法格式：
DROP FUNCTION [schema.]function_name
存储过程
创建存储过程
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name /*定义过程名*/
[ (parameter parameter_mode date_type [ DEFAULT expr ] [, …n])] /*定义参数类型及属性*/
{ IS | AS }
[declare_section] /*变量声明部分*/
BEGIN
sql_statement /*PL/SQL过程体*/
END [procedure_name][;]
【例】计算某专业总学分大于40的人数，存储过程使用了一个输入参数和一个输出参数。
CREATE OR REPLACE PROCEDURE count_grade
( zy IN char, person_num OUT number )
AS
BEGIN
SELECT COUNT(XH)
INTO person_num
FROM XSB
WHERE ZY=zy AND ZXF>40;
END;
调用存储过程
[ { EXEC | EXECUTE } ] procedure_name
[ ( [parameter =>] value | @variable [,…n]) ] [;]
【例】调用例7.1中的存储过程proc。
SET SERVEROUT ON;
EXEC proc;
输出结果：
hello world
以下命令运营的结果与之相同：
BEGIN
proc;
END;
删除存储过程
DROP PROCEDURE [schema.] procedure_name;
触发器
DML触发器
CREATE [OR REPLACE] TRIGGER [schema.] trigger_name /*指定触发器名称*/
{ BEFORE∣AFTER∣INSTEAD OF }
{ DELETE | INSERT | UPDATE [ OF column,…n ]} /*定义触发器种类*/
[OR { DELETE | INSERT | UPDATE [ OF column,…n ]}]
ON [schema.] {table_name∣view_name} /*指定操作对象*/
[ FOR EACH ROW [ WHEN(condition) ] ]
sql_statement[…n] /*PL/SQL块*/
【例】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显示。
创建表table1：
CREATE TABLE table1(a number);
创建INSERT触发器table1_insert
CREATE OR REPLACE TRIGGER table1_insert
AFTER INSERT ON table1
DECLARE
str char(100) :='TRIGGER IS WORKING';
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END;
向table1中插入一行数据：
INSERT INTO table1 VALUES(10);
输出结果：
TRIGGER IS WORKING
替代触发器
【例】在XSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。
一方面创建视图：
CREATE VIEW stu_view
AS
SELECT XSB.XH, ZY, KCH, CJ
 FROM XSB, CJB
 WHERE XSB.XH=CJB.XH
创建INSTEAD OF触发器：
CREATE TRIGGER InsteadTrig
INSTEAD OF INSERT ON stu_view FOR EACH ROW
DECLARE
xm char(8);
xb char(2);
cssj date;
BEGIN
xm:='佚名';
xb:= '男';
cssj:= '01-1月-90';
INSERT INTO XSB(XH, XM, XB, CSSJ, ZY)
VALUES(:NEW.XH,xm, xb, cssj, :NEW.ZY);