Oracle（大数据库应用）知识点总结
第三章 Oracle数据库的体系结构
3.1掌握Oracle数据库的物理存储结构
Oracle数据库指的是用户存储数据的一些物理文件，包括数据文件、重做日志文件、控制文件、参数文件，还包括密码文件、归档文件、备份文件、告警日志文件、跟踪文件等。这些物理文件统称为Oracle数据库的物理存储结构。
一般，Oracle数据库在物理上主要由三种类型的文件组成，分别是数据文件（*.dbf）、控制文件（*.ctl）和重做日志文件（*.log）。
3.2.掌握Oracle数据库的逻辑存储结构
3.2.1 逻辑存储结构
在实际对数据库进行管理时，操作这些物理文件显然是不方便的。Oracle通过表空间、段、区、块等逻辑存储结构来更加灵活方便的管理和操作数据库。Oracle数据库文件的逻辑存储结构是由“数据库内部”观看其组成的要素，包括：表空间、段、区、块以及数据库对象。
【例题1】
请详述Oracle的物理体系结构和逻辑体系结构
1. 逻辑结构
（1）表空间：为数据库提供使用存储空间的逻辑结构，一个表空间可以包含多个数据文件，
表空间是逻辑结构和物理结构沟通的桥梁；数据库必须创建的4个表空间为:
1SYSTEM：存储数据字典表，pl/sql 代码等
2SYSAUX：存储与数据库选项有关的数据
3TEMP ：用于大的排序操作
4UNDOTBS1:为读一致性和回复的目的，存储事务信息
（2）段： 位于表空间内；常见的段有 表段、 索引段、回退段等；这些段都是为了更好地
服务表段而存在的
（3）区：一个或多个区构成一个段，每个区由一些连续的数据块构成
（4）数据库块：数据库的最小存储单位，一个数据库块的大小为操作系统块的整数倍，方
便I/O;
2. 物理结构
（1）数据文件(data file)：
是用来存储数据的文件，一个或多个数据文件构成一个表空间,
/*可以用如下语句查询当前数据库下所有数据文件的编号和位置名称：
select file# , name from v$datafile;*/
（2）重做日志文件(Redo log files)：
用来记录数据库事务交易过程，便于恢复操作；分如下两类:
1联机日志：记录数据库的联机事务交易记录，一般有两组以上(一组活跃，一组非活跃)
2归档日志：将联机日志存储归档后的文件。
/*,可以用如下sql语句查询联机日志信息：select * from v$log;*/
（3）控制文件(control files)：相当于整个数据库的管家，存储有数据库的一些基本信息，
也有数据文件和联机日志文件的地址信息
3.3 熟练掌握Oracle实例的内存结构、进程结构
第4章 数据库的创建和管理
4.1 表空间
表空间（TABLESPACE）介绍
表空间是数据库的逻辑结构。
Oracle 数据库由一个或多个称为表空间的逻辑存储单元组成.表空间属性：
一个数据库可以包含多个表空间，一个表空间只能属于一个数据库一个表空间包含多个数据文件，一个数据文件只能属于一个表空间表这空间可以划分成更细的逻辑存储单元
创建一个数据库时默认创建以下表空间:
系统表空间不可以删除
4.1.1 查询表空间
-- 查询该数据库下的所有表空间
SELECT tablespace_name
FROM dba_tablespaces;
【例题2】
SELECT tablespace_name,file_name
FROM dba_data_files
WHERE tablespace_name=DEMO1;
用命令证明SYSTEM表空间和USERS表空间的作用。
（1）超级用户（SYSTEM）新建表默认存储到了SYSTEM表空间
（2）普通用户（例如zhangsan）新建默认存储到USERS表空间
-- 查询某个表的表空间
SELECT tablespace_name,table_name
FROM dba_tables
WHERE table_name=STUDENT;
4.1.2 增加表空间
【例题3】
请创建一个表空间，一个新用户，并请表空间付给新用户。
CREATE TABLESPACE DEMO2
DATAFILE D:Dataku ablespacesstudentDBDEMO2TEST01.dbf
SIZE 50M
REUSE AUTOEXTEND ON NEXT 1024k
MAXSIZE 100M;
create user wangwu identified by 123456 default tablespace DEMO2;
【例题4】
1、创建一个新的数据库
上机操作
2.登录数据库
conn zhangsan/123456@studentdb;
3.创建一个表空间(2个数据空间，允许自动扩充空间)
CREATE TABLESPACE DEMO3
DATAFILE D:Dataku ablespacesstudentDBDEMO3TEST01.dbf
SIZE 50M REUSE AUTOEXTEND ON NEXT 1024k MAXSIZE 100M,
D:Dataku ablespacesstudentDBDEMO3TEST02.dbf
SIZE 50M REUSE AUTOEXTEND ON NEXT 1024k MAXSIZE 100M;
4.创建一个用户使用刚才创建的表空间作为默认表空间
create user wangwu identified by 123456 default tablespace DEMO3
grant connect,resource,dba to wangwu;
5.创建一个表
CREATE TABLE test (
num CHAR(9) PRIMARY KEY,
name NVARCHAR2(20) NOT NULL
);
6.查看此表的表空间
SELECT table_name,tablespace_name
FROM dba_tables
WHERE table_name=TEST;
4.1.3 修改表空间
【课件例题1】
通过ALTER TABLESPACE命令把一个新的数据文件添加到DEMO3表空间，并指定了AUTOEXTEND ON和 MAXSIZE 50M
ALTER TABLESPACE DEMO3
ADD DATAFILE D:Dataku ablespacesstudentDBDEMO3TEST03.DBF SIZE 30M
REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 50M;
4.1.4 删除表空间
【课件例题2】
删除DEMO2表空间及其对应的数据文件
DROP TABLESPACE demo2
INCLUDING CONTENTS AND DATAFILES;
第五章 用户创建和管理&&数据表的创建和管理
5.1 用户创建和管理
【例题5】
请使用sqlplus 或者sqldeveloper创建一个新的用户，并修改该用户的密码,显示该用户登录的数据库。
create user zhangsan identified by 123456;
alter user zhangsan identified by 新密码;
grant connect,resource,unlimited tablespace to zhangsan;
conn zhangsan/新密码;
5.2 数据表的创建和管理
5.2.1 熟悉Oracle数据库的常用数据类型
Oracle支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。（1）数值数据类型：包括整数类型和小数类型。（2）日期/时间类型：包括DATE和TIMESTAMP。（3）字符串类型：包括CHAR、VARCHAR2/VARCHAR、NVARCHAR2、NCHAR和LONG 5种。
CHAR区分中英文，且区分编码，并且是固定长度（比如定义CHAR(9)，如果他的长度是小于9个字节，他也会按照9个字节来计算长度）。
VARCHAR区分中英文，且区分编码，长度是变长的。
NVARCHAR2不区分中英文，不区分编码，不论中文还是英文都是占2个字节。
编码：UTF-8英文占1个字节，中文占3个字节GBK英文占2个字节，中文也占两个字节
5.2.2掌握数据表的创建、修改和删除命令
和MySQL一致，除了类型有所区别
5.2.3 熟悉数据完整性约束的概念、类型
5.2.4 掌握数据完整性约束的定义和管理
与MySQL基本一致
【例外】sex CHAR(4) CHECK(sex in (男,女)
【外键】CONSTRAINT SC_FK1 FOREIGN KEY(Snum) REFERENCES student(Snum)
5.2.5掌握数据表中数据的插入、更新和删除命令
与MySQL基本一致
第六章 数据查询
6.1. 使用聚合函数统计查询
【课件例题3】
查找选修课程超过两门且成绩都在90分以上的学生的学号。
SELECT sno
FROM SC
WHERE score>=90
GROUP BY sno
HAVING COUNT(*)>=2;
【例题6】
请写一个例子，里面包括统计查询、模糊查询。
/*1.分组统计查询--查询男生和女生人数分别是多少*/
SELECT COUNT(*) AS 总人数
FROM STUDENT;
/*2.查询姓李的学生信息*/
SELECT *
FROM STUDENT
WHERE SNAME LIKE 张%;
第7章 视图
7.1 视图的创建和查看
【例题7】【课件例题4】
创建一个视图，然后查询数据
-- 创建SC_computer视图，包括计算机系各学生的学号、姓名、其选修的课程号及成绩。要保证对该视图的修改都要符合“系别名为计算机系”这个条件。
CREATE OR REPLACE VIEW sc_computer AS SELECT student.sno,sname, cno, scoreFROM student,scWHERE student.sno=sc.sno
AND “sdept”= 计算机系WITH CHECK OPTION;
7.2 视图的修改和更新
更新视图是指通过视图来插入、更新、删除表中的数据。由于视图中没有数据，通过视图的更新都要转到基本表上来实现。但并不是所有的视图都可以更新，只有对满足可更新条件的视图，才能进行更新。
通过视图更新基表数据，必须满足以下条件：
（1）没有使用连接函数、集合运算函数和组函数；
（2）创建视图的SELECT语句中没有聚合函数且没有GROUP BY、CONNECT BY、START WITH子句及DISTINCT关键字；
（3）创建视图的SELECT语句中不包含从基表列通过计算所得的列；
（4）创建视图没有包含只读属性。
【例题8】
修改视图中的数据并查询数据库中的数据
DELETE
FROM sc_computer
WHERE sno=200853105
-- 查询修改后的视图
SELECT * FROM sc_computer;
- PL/SQL编程
8.1 熟练掌握PL/SQL的基本结构和编程规范
PL/SQL程序的基本单位是块，一个基本的PL/SQL块由三部分组成：声明部分、执行部分和异常处理部分。格式如下：
[DECLARE] --声明开始关键字
/*这里是声明部分，包括PL/SQL中的变量、常量以及类型等的声明*/
BEGIN --执行部分开始关键字
/*这里是执行部分，是整个PL/SQL块的主体部分，该部分必须存在，可以是SQL语句或者程序流程控制语句*/
[EXCEPTION] --异常开始关键词
/*这里是异常处理部分，当出现异常时程序流程可以进入此部分*/
END; --执行结束标志
DBMS_OUTPUT.PUT_LINE()是系统提供的输出函数，用来输出字符串。如果看不到输出的语句，可以运行“SET SERVEROUTPUT ON;”命令，打开输出功能。
8.2 掌握PL/SQL的变量定义和数据类型
8.2.1 常量和变量
常量定义：
v_constant CONSTANT VARCHAR(20):=常量;
变量定义：
v_bl VARCHAR(9);
8.2.2 数据类型
1.标量数据类型
（1）基本数据类型
PL/SQL支持Oracle提供的基本数据类型，包括数值类型（NUMBER、PLS_INTEGER、BINARY_INTGER、SIMPLE_INTEGER等）、字符类型（CHAR、VARCHAR2、NCHAR、NVARCHAR2、LONG等）、布尔类型、日期类型（DATE、TIMESTAMP）。
（2）使用“%TYPE”属性
“%TYPE”属性利用已经存在的变量的数据类型来定义新变量的数据类型。
例如：v_sname student.sname%TYPE;
此例定义了一个变量v_name与student表的sname列具有相同的数据类型。
- 复合数据类型
- 采用“IS RECORD”关键词来定义记录类型。
例如：
TYPE stu_rec IS RECORD
(v_num student.Snum%TYPE,
V_name student.Sname%TYPE);
v_stu stu_rec;
- 采用数据表行属性“%ROWTYPE”来声明记录类型。
这种声明方式可以直接引用表中的行作为变量类型。它同“%TYPE”类似，可以避免因表中字段的数据类型而导致的PL/SQL块出错的问题。
例如：
v_stu student%ROWTYPE;
8.3 熟练掌握PL/SQL的程序结构
8.3.1 基本处理流程
在PL/SQL中，基本的处理流程包括三种结构，即顺序结构、选择结构和循环结构。
8.3.2 IF条件控制语句
【课件例题4】
求“数据库原理”课程的平均成绩，并判断输出其等级。
DECLARE
v_avg NUMBER(3);
BEGIN
SELECT AVG(score) INTO v_avg
FROM sc,course
WHERE sc.cno=course.cno
AND course.cname=数据库原理;
IF v_avg>=90 THEN
DBMS_OUTPUT.PUT_LINE(成绩优秀);
ELSIF v_avg>=80 THEN
DBMS_OUTPUT.PUT_LINE(成绩良好);
ELSIF v_avg>=70 THEN
DBMS_OUTPUT.PUT_LINE(成绩中等);
ELSIF v_avg>=60 THEN
DBMS_OUTPUT.PUT_LINE(成绩合格);
ELSE
DBMS_OUTPUT.PUT_LINE(成绩不合格);
END IF;
END;
【例题8】
PL/sql编程，请使用if elsif 语句和记录类型以及rowtype类型。
-- 根据输入的学号和课程名称查询成绩，根据将分数化为等级输出,并且输出该学生的姓名
DECLARE
vstu student%ROWTYPE;
vsc sc%ROWTYPE;
vcou course%ROWTYPE;
BEGIN
SELECT student.sname, sc.score,course.cname
INTO vstu.sname,vsc.score,vcou.cname
FROM student,course,sc
WHERE student.sno = sc.sno
AND course.cno = sc.cno
AND course.cname = &cname
AND student.sno = &sno;
IF vsc.score>=90 THEN
DBMS_OUTPUT.PUT_LINE (vstu.sname||同学的||vcou.cname||课程，成绩优秀！);
ELSIF vsc.score>=80 THEN
DBMS_OUTPUT.PUT_LINE (vstu.sname||同学的||vcou.cname||课程，成绩良好！);
ELSIF vsc.score>=70 THEN
DBMS_OUTPUT.PUT_LINE (vstu.sname||同学的||vcou.cname||课程，成绩中等！);
ELSIF vsc.score>=60 THEN
DBMS_OUTPUT.PUT_LINE (vstu.sname||同学的||vcou.cname||课程，成绩合格！);
ELSE
DBMS_OUTPUT.PUT_LINE (vstu.sname||同学的||vcou.cname||课程，成绩不及格！);
END IF;
END;
8.3.3 CASE条件控制语句
1.简单的CASE语句
简单的CASE语句给出一个表达式，并把表达式结果同提供的几个可预见的结果做比较，如果比较成功，则执行对应的语句。
【例题9】
请编写简单case语句
-- 用简单case语句，查询输入的课程号对应的课程名。
DECLARE
vcno CHAR(9);
vcname VARCHAR(40);
BEGIN
vcno:=&cno;
CASE vcno
WHEN 0001 THEN
vcname:=数据库原理;
WHEN 0002 THEN
vcname:=操作系统;
WHEN 0003 THEN
vcname:=计算机网络;
WHEN 0004 THEN
vcname:=数据结构;
ELSE
vcname:=没有该课程;
END CASE;
DBMS_OUTPUT.PUT_LINE (vcname);
END;
2.搜索式CASE语句
搜索式CASE语句会依次检索WHEN条件表达式的布尔值是否为TRUE，一旦为TRUE，那么它所在的WHEN子句会被执行，后面的布尔表达式将不再考虑。如果所有的布尔表达式都不为TRUE，则程序会转到ELSE子句。如果没有ELSE子句，系统会给出异常。
【课件例题5】
计算“200853101”同学所有课程的平均成绩，并进行等级判断。
DECLARE
v_grade number;
BEGIN
SELECT AVG(score) INTO v_grade
FROM SC
WHERE sno=200853101;
CASE
WHEN v_grade>=90 AND v_grade<=100 THEN
DBMS_OUTPUT.PUT_LINE(平均成绩优秀！);
WHEN v_grade>=80 THEN
DBMS_OUTPUT.PUT_LINE(平均成绩良好！);
WHEN v_grade>=70 THEN
DBMS_OUTPUT.PUT_LINE(平均成绩中等！);
WHEN v_grade>=60 THEN
DBMS_OUTPUT.PUT_LINE(平均成绩及格！);
WHEN v_grade>=0 THEN
DBMS_OUTPUT.PUT_LINE(平均成绩不及格！);
ELSE
DBMS_OUTPUT.PUT_LINE(平均成绩计算错误！);
END CASE;
END;
8.3.4 LOOP循环控制语句
【课件例题6】
求10的阶乘。-- 用基本LOOP
DECLARE
v_count NUMBER(3);
v_sum NUMBER(10);
BEGIN
v_count:=2;
v_sum:=1;
LOOP
v_sum:=v_count*v_sum;
v_count:=v_count+1;
/*IF v_count>10 THEN
EXIT;
END IF;*/
EXIT WHEN v_count>10;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_sum);
END;
-- 求10的阶乘。用WHILE...LOOP
DECLARE
v_count NUMBER(3);
v_sum NUMBER(10);
BEGIN
v_count:=2;
v_sum:=1;
WHILE v_count<=10
LOOP
v_sum:=v_count*v_sum;
v_count:=v_count+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_sum);
END;
-- 求10的阶乘。用FOR...LOOP
DECLARE
v_count NUMBER(3);
v_sum NUMBER(10);
BEGIN
v_count:=2;
v_sum:=1;
FOR v_count IN 2..10
LOOP
v_sum:=v_count*v_sum;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_sum);
END;
8.3.5 熟练掌握PL/SQL的异常处理
1 用户自定义异常
用户可以通过自定义异常来处理错误的发生，调用异常处理需要使用RAISE语句。每个异常处理部分都由WHEN子句和相应的执行语句组成。其语法格式如下：
EXCEPTION
WHEN 异常名1 THEN
语句块1;
WHEN 异常名2 THEN
语句块2;
[WHEN OTHERS THEN
语句块3;]
【课件例题7】
自定义异常处理。 -- 当查询条数超过5条时抛出异常
DECLARE
e_overnumber EXCEPTION; /*定义异常处理变量*/
v_xs_number number(9);
v_max_xs_number number(9):=5;
BEGIN
SELECT COUNT(*) INTO v_xs_number
FROM student;
IF v_max_xs_number<v_xs_number THEN
RAISE e_overnumber;
END IF;
EXCEPTION
WHEN e_overnumber THEN
DBMS_OUTPUT.PUT_LINE(Current Xs Number is: || v_xs_number || max allowed is: || v_max_xs_number);
END;
- 游标、存储过程和触发器
9.1 游标
9.1.1 显示游标
【例题10】
使用游标，循环输出每个学生信息
DECLARE
CURSOR stu_cur3
IS
SELECT sno,sname
FROM student;
vsno student.sno%TYPE;
vsname student.sname%TYPE;
BEGIN
OPEN stu_cur3;
DBMS_OUTPUT.PUT_LINE( 学号 姓名);
FETCH stu_cur3 INTO vsno,vsname;
-- %FOUND代表的是FETCH指向的行是否有数据，而不是他的下一行是否有数据
WHILE stu_cur3%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(stu_cur3%ROWCOUNT||:||vsno|| ||vsname);
FETCH stu_cur3 INTO vsno,vsname;
END LOOP;
CLOSE stu_cur3;
END;
9.1.2 隐式游标
使用隐式游标时必须保证只有一条记录符合要求，因为SELECT INTO 语句只能返回一条记录。如果返回多条记录，会出现异常。