一、关系数据库基础（20）
1.关系型数据库基本知识：
关系模型以及模型的层次结构：数据库系统、数据库管理系统的基本概念及其基本组成；E-R图；关系数据库设计
数据库系统的概念：数据库系统DBS（Data Base System，简称DBS）通常由软件、数据库和数据管理员组成。数据库系统是为了适应数据处理的需要而发展起来的一种较为理想的数据处理系统，也是一个为了实际可运行的存储、维护和应用系统提供数据的软件系统，是存储介质 、处理对象和管理系统的集合体。
数据库系统的组成： 数据库
数据库管理系统（及其开发工具） 应用系统 数据库管理员
数据与信息 数据是信息的载体 信息是数据的内涵
数据库管理系统的概念：
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。数据库管理系统对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
数据库管理系统的组成：
进行数据定义语言以及翻译的相关程序，在这个部分的帮助下，可以让数据库的用户自行进行选择，并且也能得到翻译由此形成一个内部形式；
进行数据运行控制的程序，因为这一程序的工作，让数据库中的资源可以充分得到管理，并且能实现关于数据的一种控制；
实例：
内存结构：
共享池
共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域
共享池由库缓存和数据字典缓存组成。
共享池的大小直接影响数据库的性能。
数据缓冲区
用于存储从磁盘数据文件中读入的数据，所有用户共享。
服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可以在内存中找到，不需要再从磁盘读取，提高了读取 速度。
数据缓冲区的大小对数据库的读取速度有直接的影响。
日志缓冲区
日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区。
当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写入日志文件中。
相对来说，日志缓冲区对数据库的性能影响较小。
后台进程：
PMON
进程监控进程：
清理出现故障的进程。
释放所有当前挂起的锁定。
释放故障进程使用的资源。
SMON
系统监控进程：
在实例失败之后，重新打开数据库时自动恢复实例。
整理数据文件的自由空间，将相邻区域结合起来。
释放不再使用的临时段。
DBWR
数据写入进程：
管理数据缓冲区，将最近使用过的块保留在内存中。
将修改后的缓冲区数据写入数据文件中。
LGWR
日志写入进程：
负责将日志缓冲区中的日志数据写入日志文件。
系统有多个日志文件，该进程以循环的方式将数据写入文件。
CKPT
校验点
数据库：
物理数据库
数据文件
控制文件
日志文件
逻辑结构
数据库
表空间
段
区
数据块
E-R图;
第一种:
第二种：
例：
关系数据库设计
概念:
关系数据库：在一个给定的应用领域中，所有实体及实体之间联系的集合构成一个关系数据库
关系模型结构:
1、单一的数据结构----关系（表文件）。关系数据库的表采用二维表格来存储数据，是一种按行与列排列的具有相关信息的逻辑组，它类似于Excel工作表。一个数据库可以包含任意多个数据表。
在用户看来，一个关系模型的逻辑结构是一张二维表，由行和列组成。这个二维表就叫关系，通俗地说，一个关系对应一张表。
2、元组（记录）。表中的一行即为一个元组，或称为一条记录。
3、属性（字段）。数据表中的每一列称为一个字段，表是由其包含的各种字段定义的，每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和其他属性。字段可以包含各种字符、数字、甚至图形。如错误！未找到引用源。
4、属性值。行和列的交叉位置表示某个属性值，如“数据库原理”就是课程名称的属性值
5、主码。主码（也称主键或主关键字），是表中用于唯一确定一个元组的数据。关键字用来确保表中记录的唯一性，可以是一个字段或多个字段，常用作一个表的索引字段。每条记录的关键字都是不同的，因而可以唯一地标识一个记录，关键字也称为主关键字，或简称主键。如错误！未找到引用源。
6、域。属性的取值范围。
7、关系模式。关系的描述称为关系模式。对关系的描述，一般表示为：关系名（属性1，属性2.....属性n）。例如上面的关系可描述为：课程(课程号、课程名称、学分、任课老师)。
约束
1.实体完整性，通常由关系系统自动支持
2.参照完整性，早期系统不支持，但大型系统能自动支持
3.用户定义的完整性，反映应用领域需要遵循的约束条件，体现了具体领域中的语义约束，用户定义后由系统支持
1.关系数据库的设计应遵从概念单一化“一事一地”的原则
2.避免在表之间出现重复字段
3.表中的字段必须是原始数据和基本数据元素
2.Oracle数据库体系结构
Oracle数据库和Oracle实例组成
Oracle主要组件
Oracle数据库用物理结构和逻辑结构
数据库的逻辑结构和物理储存结构：
逻辑结构：
物理结构：三种文件
1.数据文件
2.控制文件
3.日志文件
范式
二、数据库模式对象操作（20）
数据表，定义约束，创建视图，创建用户和角色，权限控制
数据表:
建表:
create table classinfo(
classid number(2) primary key,
classname varchar(10) not null
);
create table studentinfo(
studentid number(2) primary key,
studentname varchar(10) not null,
studentsex char(2) check(studentsex='男' or studentsex='女'),
studentage number(2) not null,
studenttel number(11) unique,
studentaddress varchar(50) default '上海',
classid number(2) references classinfo(classid)
);
删除表：
drop table student_infor;
drop table tb_date_test cascade constraints;
修改表：
Alter table member Add(age number(3),photo vachar2(100)) // 增加字段
Alter table member modify(name varchar2(30)); // 修改字段
Alter table member drop column age; //删除字段
......
创建视图
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
//查询
SELECT * FROM salvu50;
//删除
DROP VIEW empvu80;
序列：CREATE SEQUENCE
同义词：
CREATE SYNONYM d_sum FOR dept_sum_vu;
创建用户和角色
//创建用户
CREATE USER user_name
IDENTIFIED BY password
[ DEFAULT TABLESPACE default_tablespace_name ] //分配表空间
[
QUOTA quota [ K | M ] | UNLIMITED ON tablespace_name
[ ACCOUNT LOCK | UNLOCK ]
] ;
//修改用户
ALTER USER user_name IDENTIFIED BY new_password ;
//删除用户
DROP USER user_name [ CASCADE ] ;
//创建角色
create role to test_role;
//赋予权限
grant select on employees to test_role;
······
//将角色赋予用户
grant test_role to scott;
//删除角色
drop role test_role;
权限控制
grant all on employees to public;
grant select on employees to scott with grant option;--授予可传递权限
grant connect to scott;
grant create table to scott;
grant create procedure,create function to scott;
grant select on employees to scott;
grant update on employees to scott;
grant all on employees to scott;
revoke connect from scott;
revoke all on employees from scott;
三、SQL语句操作题（30）
Select查询语句（单表简单查询，单表分组聚合查询，两表连接查询，嵌套子查询）
单表简单查询
select * from emp;--查询emp表
select empno,ename,job from emp;--查询emp表中指定的列
select empno as员工编号,ename as员工姓名from emp;--第一种按指定的别名显示查询emp表中指定的列
select empn0 en0,ename en from emp;--第二种按指定的别名显示查询emp表中指定的列，比较常用
select distinct job from emp;--查询指定的列表并去除重复的数据
select distinct job,deptno from emp;--只有所有的列数据全重复才可以合并
select'员工编号是' || empno || '姓名是' || ename || '的工作是' || job from emp;--sql中字符串用单引号表示，连接符号是 ||
select ename,sal*12from emp;--查询中进行四则运算，必须是数值类型，在sql中并不推荐四则运算会影响性能
单表分组聚合查询
select empno,avg(sal) as avgsal
from scott.emp
group by empno
having avg(sal)>=1300
order by avgsal desc;
两表连接查询
内连接
select * from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;
select * from 表1,表2 where 表1.字段1=表2.字段1;
左外连接
select * from 表1 left outer join 表2 on 表1.字段1=表2.字段1;
select * from 表1 left outer join 表2 where 表1.字段1=表2.字段1;
右外连接
select * from 表1 right outer join 表2 on 表1.字段1=表2.字段1;
select * from 表1 right outer join 表2 where 表1.字段1=表2.字段1;
全外连接
select * from 表1 full outer join 表2 on 表1.字段1=表2.字段1;
嵌套子查询
//单行子查询
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = ‘Taylor’)
AND salary >
(SELECT salary
FROM employees
WHERE last_name = ‘Taylor’);
//嵌套子查询中的having语句
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
//多行子查询
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY //any、in、all都可用于多行子查询
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
四、程序设计题（20）
PL/SQL基本语法和程序结构，储存过程的定义和调用，函数的定义和调用
PL/SQL基本语法和程序结构
DECLARE
a number(2) := 10; //定义变量
BEGIN
a:= 10;
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
储存过程的定义和调用
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lnvuQGPc-1623244606497)(D:\233\database\1.png)]
declare
p_empno in emp.pemno%type
begin
p_empno := 110;
taxinfo(p_empno);
end;
函数同上
五、系统工具使用（10）
SQL * PLUS常用命令的使用；数据库启动与关闭，创建和修改表空间
SQL * PLUS常用命令的使用；数据库启动与关闭，创建和修改表空间
SET命令用来设置SQL*Plus的运行环境。语法格式为：SET system_option value
set linesize {80|n} //设置SQL*Plus在一行中能够显示的总字符数，默认值为80。可以的取值为任意正整数。
Set newpage {1|n|none} //设置每页打印标题前的空行数，默认值为1。
set pagesize {14|n} //设置每页打印的行数，该值包括NEWPAGE设置的空行数。
set pause{off|on|text} // 设置SQL*Plus输出结果时是否滚动显示。当取值为ON时表示输出结果的每一页都暂停，用户按下回车键后继 续显示；取为字符串时，每次暂停都将显示该字符串。
DESCRIBE命令可以返回数据库中所存储的对象的描述。对于表、视图等对象而言，DESCRIBE命令可以列出其各个列的名称以及各个列的属性。除此之外，DESCRIBE还会输出过程、函数和程序包的规范
使用PROMPT命令可以在屏幕上输出一行数据，这种输出方式非常有助于在存储的脚本文件中向用户传递相应的信息。PROMPT命令的语法形式如下：PROMPT prompt_text;
Prompt_text表示用于指定要在屏幕上显示的提示信息
数据库的启动
1.NOMOUNT模式：只打开了数据库实例，此时读取参数文件； 命令： startup nomount; 2.MOUNT：Oracle根据参数文件中控制文件的位置找到并打开控制文件，读取控制文件中的各种参数信息，但是此时并不打开数据文件。 命令：alter database mount; 3.open：数据库将打开数据库进行一系列的检查工作，这些检查工作用于数据恢复。 命令：(1):startup open或者startup(默认启动到open状态) (2)alter database open;
数据库的关闭
关闭数据库的操作正好和打开数据库的操作相反，先关闭各种数据文件，关闭打开的控制文件，然后关闭实例。数据库的关闭经历了close,dismount,shutdown三个阶段。
1.close：关闭数据库，日志文件等; 命令：alter database close; 2.dismount:关闭控制文件； 命令：alter database dismount； 3.shutdown：关闭实例 命令：shutdown shutdown可接参数normal：关闭数据库时，不允许有新的数据库连接，只有当前的所有连接都退出是才会关闭数据库，是一种安全的关闭数据库的方式； 接参数immeidate：中断当前事务，回滚未提交的事物，强制断开所有用户连接和执行检查点把脏数据写到数据文件中，该方式可以较快速且安全的关闭数据库。 接参数transactional：数据库当前连接继续执行，但不允许新的连接，一旦当前所有事物执行完毕，则关闭数据库。 接参数abort：该方式很不安全的关闭数据库，最好不要使用该方式来关闭数据库。
创建和修改表空间
创建表空间
CREATE TABLESPACE small1 DATAFILE '/home/oracle/app/oracle/oradata/CRM/small1.dbf' SIZE 1G;
修改表空间
ALTER TABLESPACE small1 ADD DATAFILE '/home/oracle/app/oracle/oradata/CRM/small2.dbf' SIZE 100M;
删除表空间
drop tablespace big1 including contents and datafiles;
查询表空间
SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES;