Oracle数据库快速入门笔记

网页左边，向下滑有目录索引，可以根据标题跳转到你想看的内容

一、安装与基本概念介绍

由于安装过程篇幅较大，我将其放在另一篇文章中：https://blog.youkuaiyun.com/grd_java/article/details/114733011
这篇文章介绍了如何安装Oracle，如何创建数据库，以及如何使用数据库连接软件连接数据库

1.Oracle服务

Oracle的服务，需要牢记两个最重要的服务，其它服务的作用需要了解
OracleServiceORCL：数据库实例，Oracle核心服务，没有这个服务，Oracle是没有办法启动的，并且此服务是操作Oracle的基础，启动此服务才能进行增删改查操作
OracleOraDB1g_home1TNSListener：监听器，如果想要使用数据库管理软件远程连接数据库，必须开启此服务，用来监听客户端请求并分配Server Process（服务进程，有数量限制，如果同时有过多用户远程连接数据库时，会出现session（会话）不够用情况）

2. Oracle体系结构

1. Oracle数据库：是实际存储数据的地方，相当于仓库，用于持久化存储数据，属于文件的集合，由数据文件、控制文件、日志、临时文件构成
2. 实例：相当于预约挂号，临时在内存中建立一个会话，根据规定申请操作数据库，进行增删改查等操作，由操作系统进程、进程使用的内存（SGA）组成

查看数据库和实例信息(了解即可，无需操作)

1. 查看数据库基本信息

select file#,status,enabled,name from v$datafile

2. cakan查看当前数据库实例信息

select instance_name,host_name,version,startup_time from v$instance

oracle 与其它数据库的不同之处

不同点	Oracle	其它数据库	说明
----------------------------------------------------------------结构-------------------------------------	实例->用户->数据表	实例->数据库->数据表	oracle数据库，采用实例连接用户，然后每个用户有自己的表这种模式。其它数据库一般是，建立实例，连接数据库，然后操作数据库
实例->数据库	一个实例只能对应一个数据库，数据库系统基本信息也保存在这个数据库	一个实例管理多个数据库，每个数据库有自己的表，系统基本信息保存到单独的master数据库中	Oracle多个用户操作一个数据库，每个用户管理自己的表，其它数据库，使用账号建立数据库实例，即可管理所有数据库
表	表建立在用户下，每个用户管理自己的表，这些表统一在一个数据库中，但是由用户管理	表建立在数据库中，不同数据库具有自己的表，这些表属于数据库，不属于某个用户	Oracle的数据库，一般不建立太多，多个用户连接同一个数据库，然后在自己的账号下建立表，其它数据库每个项目都单独建立一个数据库，然后将相关的表放在数据库中，所有用户登录到数据库都可以操作

3. 数据库的基本结构

Oracle体系结构分为数据库，数据库实例和数据库服务器，这里介绍数据库（基本结构由内部结构和外部结构组成）

1. 数据库的逻辑结构（内部结构）

1. 数据库内部结构描述的是存储结构
2. 数据库内部的存储结构主要包括表空间、表、分区、用户、索引、视图、权限、角色、段、盘区、块等。
3. 表空间（Oracle特有的概念）
   

1. 一个表空间对应一个数据库，一个数据库可以对应多个表空间，一个表空间对应一个或多个数据文件（物理结构）
2. 表空间由表，索引，视图构成。
3. 对于表，是存储数据的基础，由字段名和属性构成，当一个表过大时，可以分区，将大表分为多个小表，分布式管理

2. 数据库的物理结构（外部结构）

1. 数据文件：存储数据和脚本文件，一个表空间对应多个数据文件，各表空间的数据文件可以存放在不同磁盘进行物理划分
   
2. 重做日志文件：用来记录数据变化，保存所有数据库事务日志。这些事务记录在联机重做日志文件中，数据库数据丢失或损坏，可用这些日志恢复数据库
3. 控制文件：记录与描述数据库外部结构

1. Oracle数据库名称与建立时间
2. 记录数据文件、重做日志文件名称、及它们的所在位置
3. 日志记录序列码

4. 数据库实例

介绍完数据库，现在介绍数据库实例

1. 系统进程，是Oracle服务端的进程，用来处理用户的请求，用户进程是用户实例进程，用来发送请求给服务端
2. 红色圈内是实例的系统全局区，蓝色圈内是实例的后台进程（用来维持物理结构与逻辑结构的关系）

1. 用户进程，同一时间支持多个用户进程建立连接，每个用户进程都是一个用户实例，同时建立一个用户进程，用来给服务器发请求
2. 数据缓存区：服务器收到请求，将相关数据拿到数据缓冲区，然后操作，此时操作的是数据缓冲区的数据，而数据文件没有受到改变，此区，所有用户进程共享
3. DBWR：数据缓存区的数据操作完成后通过此进程写到数据文件中
4. 重做日志缓冲区：可循环使用，存放用户所有修改操作的日志条目，方便日后恢复数据
5. LGWR：将重做日志缓冲区的日志条目写到物理结构中的日志文件，日志文件的位置会写到控制文件中，也就是将日志持久化到硬盘
6. 字典缓存区：存放数据库对象信息，比如表、索引、视图等，每次到数据文件中拿对象太慢，而第一次获取后，将其放在缓存，日后再有人用，就直接缓存中拿，速度更快
7. SQL共享池：存放字典缓存区和库信息，记录用户sql语句的使用记录，当其它用户进程使用了重复的语句，就不用重复执行sql语句，而是直接将上次这条语句执行的结果（这些结果在字典缓存区）返回，节省资源
8. PMON：当某操作异常终止时，清除数据缓存区内不使用的空间，以及之前使用的资源，当某个服务进程停摆，将其重新启动
9. SMON：系统监控进程，遇到停电等不确定因素导致数据库无法正常关闭时，此进程在下次数据库启动时，进行必要数据库修复
10. CKPT：检查点进程，记录一个事件执行时间，确保缓存区内经常变动的数据，及时写回到数据文件和控制文件（通知DBWR进程及时将数据缓存区数据写回，不是检查点进程进行写回操作）
11. ARCH：归档进程，可选进程，开启后，会自动归档日志文件

4. 细节

1. java池，就是这些缓存池的统称，因为Oracle用java实现，所以很多面试官问的时候会说简单介绍一下Oracle的java池，其实就是介绍Sql共享池这些东西

二、sqlPlus

sqlPlus

1. 是Oracle自带的数据库操作软件，没有图形化界面，主要采用命令行形式操作数据库
2. 是数据库管理软件的基础，管理软件底层都是封装sqlPlus来操作Oracle

sqlPlus启动

sqlPlus主要命令

1. 登录用户命令

SQLPLUS username[/password][@connect_identifier] [AS SYSOPER|SYSDBA]

1. username：表示登录用户名
2. password：表示登录口令
3. @connect_identifier：表示连接的全局数据库名，若连接本机上的默认数据库，则可以省略
4. [AS SYSOPER|SYSDBA] ： 表示以何种权限登录数据库，SYSDBA是系统权限，如果省略此参数，表示以默认default权限进入，SYSOPER是比SYSDBA稍低一点的权限，会少一些可以使用的命令
5. 两种使用此命令的方式

2.连接用户命令

用于当前用户连接到另一个用户，以操作数据

connect system/root as sysdba 

3. set命令

设置 SQL*Plus 的运行环境

SET system_variable value

1. system_variable：变量名
2. value：变量值
3. 常用变量汇总

变量名(不区分大小写)	变量值	说明
Pagesize	任意数值（默认值14）	一页显示的行数，顶部标题至页结束之间的行数
Linesize	任意数值（默认值80）	设置 SQLPlus 在一行中显示的最大字符数
Pause	OFF（默认值） /ON/TEXT	设置 SQL*Plus 输出结果是否滚动显示。为 ON 时，表示输出结果每一页暂停，用户按 Enter 键后继续显示
ArraySize	任意数值（默认值20）	查询时 SQLPlus 单次从数据库获取的行数，有效值 1 ~ 5000
AutoCommit	ON/OFF(默认)	修改数据库数据时，是否自动提交（提交后修改操作才会真正生效），ON为开启自动提交，OFF为不开启，那么我们需要使用Commit命令手动提交

4. COLUMN 命令

该命令用于格式化查询结果、设置列宽度、重新设置列标题等

COLUMN [column_name | alias | option]

1. column_name：用于指定要设置的列的名称
2. alias：用于指定列的别名，通过它可以把英文列标题设置为汉字
3. option：用于指定某个列的显示格式

option 选项的值	说 明
FORMAT	col 列别名 format $999,999.00：以指定格式显示某列或者某些列（多个列用逗号分隔）

5. SHOW命令

显示 SQLPlus 系统变量的值或 SQLPlus 环境变量的值

Show option

常用的option有 ALL、 PARAMETERS [parameter_name]、 SGA、SPOOL、 USER 等

6. 用户解锁与改密等操作

修改指定用户密码

alter user 用户名 identified by 要修改成的密码;

解锁指定用户

alter user 用户名 account unlock;

三、基本操作

1、创建表空间

使用软件创建

使用SQL语句创建

-- 创建表空间
CREATE TABLESPACE "yzp_tablespace" --表空间名字  
DATAFILE  'D:\ORACLE\ORADATA\yzp_tablespace.dbf' --表空间文件路径\数据文件名  D:\ORACLE\ORADATA\ORCL是文件路径 \test1是数据文件名
SIZE 100 M --表空间初始大小
AUTOEXTEND ON --表空间容量达到上限是否自动扩展（ON是/OFF否）
NEXT 10 M --每次自动扩展大小
MAXSIZE UNLIMITED --最大容量为无上限
;

2、删除表空间

-- 删除表空间
drop tablespace "yzp_tablespace";

3、创建用户

-- 创建用户
create user yzp --创建用户yzp
identified by root --密码 为 root
default tablespace "yzp_tablespace" --设置用户默认表空间为test，如果不指定，默认为system表空间 
;

4、为用户分配权限

软件授权

sql授权

-- 用户授权
-- oracle中常用角色
connect -- 连接角色，最基本的角色，没有此角色权限将无法连接数据库
resource -- 开发者角色
dba -- 超级管理员角色，和system具有相同的权限

-- 给用户授权
grant dba to yzp;

5、切换角色

scott用户,密码tiger

-- 解锁scott用户
alter user scott account unlock;
-- 解锁scott用户密码，加入by关键字可以重置密码
alter user scott identified by tiger -- by tiger可以不加，加上就是重置密码为tiger
;

6、导出与导入

1. 导出表（使用exp命令）

exp 用户名/密码[@数据库名] file='导出文件后存储的路径.dmp' tables=(表名1[,表名2,...]);# 可以直接输入exp进入交互模式，也可以使用这条完整的命令，其中@数据库名可省略，默认为orcl库
# tables用于指定导出哪些表(多个表名用逗号分隔)

2. 导入表（使用imp）

imp 用户名/密码[@数据库名] file='文件存储路径.dmp' tables=(表名);# 可以直接输入imp进入交互模式

3. 导出整个数据库

exp 用户名/密码@orcl file='文件路径.dmp' full=y# full表示整个数据库 y表示yes

4. 将指定用户的用户表导出（与上面的不同，这个命令需要用具有指定权限的用户来操作）

exp 高权限用户/密码@orcl file="指定路径.dmp" owner=(指定用户)# 使用高权限用户将指定用户的所有表导出

四、sql

因为多种关系型数据库的sql大相庭径，请参考下面文章的sql语句，以下只介绍不同之处
https://blog.youkuaiyun.com/grd_java/article/details/105747428

清除表数据

-- 清除表，但不销毁表，只清除数据
truncate table persion;

1、序列和索引

序列

-- 序列是一个辅助功能，可以与一个表做绑定，用来获取一个一直变化的序列值比如1,2，3,4,....
-- 默认从1开始，依次递增，主要用于主键赋值

create sequence s_persion; --创建一个序列
select s_persion.nextval from dual; 	--s_persion.nextval 是取序列当前值的下一个值，默认当前是1，将nextval改为currval，那么取当前序列值，
										--如果第一次使用序列，无法直接currval取到当前值，因为初始没有值，但是默认值是1
										-- dual 一个虚表，只是为了补全语法，无意义。

举例
insert into persion(pid,username) values(s_persion.nextval,'张三'); --插入一条数据，主键不能重复，使用序列将会一直递增不重复
commit; -- oracle数据库执行完操作，可以提交操作，相当于保存操作结果，下次如果出错，可以回滚到这次提交的结果

建立索引查询（索引查询效率高，增删改效率变低）
单列索引就是对一列做索引，多列索引就是多个列复合索引

create index idx_ename on emp(ename); --创建单列索引

create index idx_enamejob on emp(ename,job); --创建多列索引

2、PL/SQL

PL/SQL

1. 对sql语言的扩展，使得sql语言具有过程化编程的特性
2. 比一般的过程化编程语言，更加灵活
3. 主要用来编写存储过程和存储函数等

1、基本变量的声明和使用

--定义变量的关键字
declare
--定义基本数据类型
pnum number(2):=2;--定义时赋值
pnum number(7,2);--定义number类型变量，变量名为pnum
pname varchar2(20);--定义字符类型变量
pdate date;--定义日期类型变量

--声明代码块，与begin关键字开始，end关键字结束
begin 
  pnum:=1;--给pnum变量赋值1
  dbms_output.put_line(pnum);--输出pnum变量
  pname:='tom';
  dbms_output.put_line(pname);
  pdate:=sysdate;--赋值当前系统日期
  dbms_output.put_line(pdate);
  dbms_output.put_line(pdate+1);--给当前日期加一天再输出
end;