0. oracle

关系型数据库六大范式

第一范式 1FN

第一范式是第二第三范式的基础

• 数据组的每个属性只可以是一个值，不可再分
• 关系中每个数组必须拥有相同多的值
• 关系中的每个数据一定不能相同

第二范式 2FN

第二范式必须满足第一范式

• 要求表中每行都必须可以被唯一区分，区分的属性被称为主关键字或主键
• 实体属性必须完全依赖于主关键字，否则需要拆分

第三范式 3FN

第三范式必须满足第二范式

• 关系表中不能包含，已在其它表中包含的非主关键字信息

BC范式 BCFN

BCFN满足第三范式

• 主关键字不能依赖于其它主关键字

第四范式 4FN

• 当表的非主主属性相互独立时，非主属性不能有多值

第五范式 5FN 最终范式

第五范式必须满足第四范式

• 表必须可以分解为更小的表，除非这些表在逻辑上和原表有相同的主键

启动和关闭数据库

startup [nomount|mount|open|force|restrict] pfile=filename;

--关闭数据库语法
shutdown [normal|transactional|immediate|abort];

• normal 正常关闭方式 正常的方式来关闭数据库

  1. 阻止任何用户建立新的连接
  2. 等待当前连接用户主动断开连接
  3. 当所有用户断开后，立即关闭数据库

• transactional 事务关闭方式 在当前活动事务提交后关闭数据库

  1. 阻止用户建立新连接和开始新事务
  2. 等待所有活动事务提交完毕后，断开用户连接
  3. 当所有活动事务提交完毕，用户都断开连接后，关闭 数据库

• immediate 立即关闭方式 在尽可能短的时间内关闭数据库

  1. 阻止用户建立新连接和开始新事务
  2. 将未提交的事务回退
  3. 关闭数据库

• abort 终止式关闭数据库 强制中断数据库操作，可能丢失部分数据

  1. 阻止用户建立新连接和开始新事务
  2. 取消未提交的活动事务，不是回退
  3. 立即终止正在执行的任何sql语句
  4. 立即关闭数据库

逻辑存储结构

数据块

数据块是oracle逻辑存储结果中的最小逻辑单位，也是执行数据库输入输出的最小储存单位，oracle数据存放在oracle数据块里，通常oracle数据块是操作系统数据块的整数倍。

oracle数据块的大小被写入到初始化参数DB_BLOCK_SIEZ中

数据块结构如下

数据区

数据区是一组连续的oracle数据块所构成的oracle存储结构

数据区的目的是保存特定数据类型的数据，也是表数据增长的基本单位

段

段是由一个或多个数据区构成，不是存储空间的分配单位，是一个独立的逻辑结果，用于存储表、索引簇等数据对象

一个段只属于一个特定的数据对象，段包含的数据区可以连续也可以不连续，并且可以跨越多个文件

表空间

oracle使用表空间将相关的逻辑结构组合在一起，表空间是数据库的最大逻辑划分区域，通常用于存放数据表、索引、回滚段等数据对象，任何数据表创建时都需要被指定表空间

表空间(逻辑存储结构)和数据文件(物理存储结构)相对应

表空间在物理上包含操作系统的一个或多个数据文件，当表空间只包含一个数据文件时，该表空间的全部对象存储在这个数据文件内。当表空间包含多个数据文件时，oracle可以将该表空间的数据对象存储在任一数据文件里，也可以将一个数据对象的数据分布在多个数据文件里

物理存储结构

数据文件

数据文件用于保存用户应用程序数据和oracle系统内部数据

• 在创建表空间时，oracle会创建该表空间的数据文件
• 通过查询dba_data_files 或者v$datafile数据字典来查看oracle系统的数据文件信息
• 通过查询dba_tmp_files或v$tempfile数据字典查询临时文件的信息

控制文件

控制文件是一个二进制文件，记录了数据库的物理结构，主要包含数据库名、数据文件和日志文件的名字和路径、数据库建立日期、系统更改号、检查点信息及归档的当前状态等信息

• 控制文件一般是数据库建立时oracle建立，路径由服务器参数文件SPFILEORCL.ORA的CONTROL_FILES参数指定
• 由于控制文件存放数据文件和日志文件等相关信息，oracle实例在启动时，必须正常访问控制文件才能打开数据库
• 每个数据库至少拥有一个控制文件，但一个控制文件只能属于一个数据库
• 通过v$controlfile数据字典查看oracle系统的控制文件信息

日志文件

日志文件的主要功能是记录对数据的修改，对数据库的所有修改都记录在日志文件里

重做日志文件

重做日志文件记录数据库所有的修改和oracle内部行为，在用户执行commit时，先将每笔操作的原始数据写入日志文件，然后把新的数据传递

• 通过对表或表空间设置NOLOGGING，使DML操作不会产生日志信息，减少日志信息的产生
• 为保障数据库系统安全，每个实例都会启用一个日志线程来记录数据库的变化，日志线程由若干个日志组构成，每个日志组由一个或多个日志文件构成
• 通过v$logfile来查询日志文件信息
• 系统产生的日志文件信息，首先临时存放在系统全局区SGA的重做日志缓冲区，当发出commit命令或日志缓冲区满1/3时，日志信息写入LGWR进程将日志信息从重做日志缓冲区读取出，写入到日志文件组中序列号较小的文件，一个日志组写满后接着写下一个日志组，当全部使用后，再从第一个日志文件组覆盖写入

归档日志文件

归档模式中，归档进程ARCH将即将被覆盖的日志文件信息读出，写入到归档日志文件

• 归档模式里，LGWR进程需要等待ARCH进程结束才能覆盖日志文件，且归档日志文件占据大量磁盘空间，影响oracle系统性能，所以默认情况很少使用归档模式运行
• 通过v$database查询的确是否采用归档模式

服务器参数文件

服务器参数文件SPFILE 是二进制文件，记录数据库的基本参数信息

• 数据库实例创建前，oracle会读取服务器参数文件，通过参数来初始化和创建实例
• 文件名为SPFILE<sid>.ora，sid为数据库实例名
• 通过查询v$parameter或者使用SQL*Plus的SHOW PARAMETER来显示服务器参数
• 修改服务器参数不能直接修改服务器参数文件，需要使用企业管理器OEM或ALTER SYSTEM修改

--修改服务器参数
alter system set db_block_size=4096;

密码文件

密码文件是oracle用于验证sysda权限的二进制文件，当用户远程以sysda或sysoper连接时，一般用密码文件验证

• 密码文件默认在%dbhome_1%\database，命名格式为PWD<sid>，sid为数据库实例名
• 密码文件是oracle自动创建，也可以使用ORAPWD.mp4工具手动创建

--创建密码文件
C:>ORAPWD FILE=filename PASSWORD=password ENTRIES=max_user;

警告文件

警告文件即警告日志文件，是存储在oracle系统目录下的文本文件，通常为alert_orcl.log，用来记录oracle的运行信息和错误信息

• 通过oracle系统的BACKGROUND_DUMP_DEST参数来查看路径
• 警告文件会越来越大，数据库管理员应该定期删除警告文件

跟踪文件

跟踪文件包括后台进程跟踪文件和用户进程跟踪文件，后台进程跟踪文件用于记录后台进程的警告和错误信息

• 后台进程跟踪文件的磁盘位置由初始化参数BACKGROUND_DUMP_DEST确定，后台进程跟踪文件命名格式为<sid>_<processname>_<spid>.trc
• 用户进程跟踪文件的磁盘位置由初始化参数USER_DUMO_DEST确定，用户进程跟踪文件命名格式为<sid>_ora_<spid>.trc
• 除了.trc 文件外，还有.trm跟踪元数据文件，.trm包含.trc文件的结构化信息

oracle服务器结构

oracle服务器主要由实例（系统全局区和后台进程）、数据库、程序全局区和前台进程组成

系统全局区SGA

系统全局区是所有用户进程共享的一块内存区域，数据资源可以被多个用户进程使用

SGA随数据库实例启动加载到内存，随数据库实例关闭而关闭

高速数据缓冲区

存放oracle系统最近访问过的数据块

• 脏数据区 存放已经被修改的数据，等待被写入数据文件中
• 空闲区 不包含任何数据，可以被写入数据
• 保留区 包含那些正在被用户访问的数据块和明确保留作为未来使用的数据块

重做日志缓冲区

存放对数据库进行修改操作时产生的日志信息，这些日志信息在 写入重做日志文件之前，首先存放到重做日志缓冲区，最后被日志写入进程LGWR写入重做日志文件

共享池

SGA保留的内存区域，用于缓存SQL语句、PL/SQL语句、数据字典、资源锁、字符集和其它控制结构

• 库高速缓冲区 包含共享SQL和私有SQL区，存放最近使用的SQL、PL/SQL的文本和执行计划，下次执行相同的语句时，在库高速缓冲区找到之前生成的，不需要再次解析语句，提高系统执行效率
• 字典高速缓冲区 存放oracle内部管理所需的数据字典信息

共享池的内存大小可以被改变

--修改共享池内存大小
alter system set shared_pool_size=30m;

大型池

大型池在SGA区不是必需的内存结构，只在某些特殊的情况下，实例需要使用大型池来减轻共享池压力

常用情况如下

• 使用恢复管理器进行备份和恢复操作，大型池作为I/O缓冲池使用
• 使用I/OSlave仿真异步I/O功能时，大型池作为I/O缓冲池使用
• 执行大量排序操作的SQL语句
• 使用并行查询时，大型池作为并行查询进程彼此交换信息的地方

--修改大型池内存大小
alter system set large_pool_size=30m;

JAVA池

用来提供内存空间给JAVA虚拟机使用，目的是支持在数据库运行JAVA程序包，大小由JAVA_POOL_SIZE决定

程序全局区

程序全局区PGA也可称作用户进程全局区，内存区在进程私有区而不是共享区

前台进程

前台进程实现用户和实例的沟通

用户进程

用户进程指那些能够产生或执行sql的应用程序

• 连接时用户进程和实例的通信渠道
• 会话值用户进程和实例建立连接后，用户和实例之间的交互方式

服务器进程

服务器进程是用于处理用户会话进程发送的sql语句或者SQL*Plus命令

后台进程

oracle后台进程是一组运行于服务器端的后台程序

• SMON PMON DBWR LGWR CKPT 这五个后台进程必须正常启动

数据写入进程 DBWR

数据写入进程的主要任务是将内存的脏数据回写到数据文件

脏数据是告诉数据缓冲区中被修改的数据

以下情况时，写入脏数据

• 当高速数据缓冲区没有足够的空闲空间存放新数据时
• 检查点进程启动后，要求数据写入进程执行
• 当脏数据在告诉缓冲区存放超过3秒，DBWR进程会自动将部分脏数据写入

修改服务器参数文件SPFILE的DB_WRITER_PROCESSES参数，可以使用多个DBWR进程，但DBWR

进程数量不能超过系统处理器的数量，否则降低性能

检查点进程 CKPT

检查点进程可以看成一个事件，当检查点事件发生时，CKPT会要求DBWR将脏数据写入数据文件

• 通过修改服务器参数文件SPFILE的参数CHECKPOINT_PROCESS为true来启动检查点进程

日志写入进程 LGWR

日志写入进程用于将重做日志缓冲区的数据写入重做日志文件

• 实例会产生大量日志信息，先记录在SGA重做日志缓冲区，发生提交任务或者日志缓冲区信息满1/3，或日志信息存放超过3秒，LGWR进程将日志信息从重做日志缓冲区读出并写入到序数较小的日志文件中

归档进程 ARCH

归档进程是可选择的进程，当实例为oracle归档模式时，启动进程

• 当各个日志文件组写满时，由ARCH将日志文件读出，再将这些日志信息写入到归档日志文件

1. sql基础^[1]

sql定义

• sql全称是结构化查询语言。
• Sql属于非过程化语言。
• 命令动词共9个

语句类型

用户模式

用户模式是一个数据库对象和集合，模式为一个数据库用户所有，并和该用户同名，在一个模式内部无法访问其它模式的数据库对象，即使有权限访问，也需要指定其它模式的名称。

sql编写规则

• sql关键字，对象名和列名不分大小写。
• 字符值分大小写。
• sql以; 为结束符。

空值和null

• 插入空值时，会转化为null
• 空值只和空值相等

如何处理null

• 使用is null和is not null进行判断
• 使用nvl(num1,num2)函数，判断num1是否为null，为null则返回num2
• 使用nvl2(num1,num2,num3)函数，判断num1是否为null，是返回num3，否返回num2，该函数只能在sql使用

2. 常用sql语句

查询语句

select语法^[2]

select ([distinct|all]|columns|*)  --查询字段或指定字段，可以选择去重
(into new_table)    --插入到新的表 可选
from (other_tbale|view|select) --指定查询来源，可以是表，视图和其它查询 可选
(where condition)   --指定搜索条件 可选
(group by columns)  --分组语句，字段可多个 可选
(having condition)  --分组后的筛选，必须在group后使用
(order by columns)  --指定字段排序，可多个字段

• sql可使用+-*/以及()进行数据运算。
• 使用as或者直接为列指定别名，不需要用单引号包括，可以用双引号包含别名。
  参考 select sal (as) 工资 from emp;
• 字段前加disinct可查询不重复记录
• 使用nvl函数处理null值，当值为null返回0
• 使用||或concat函数连接字符串，可以连接数字

筛选查询

• 比较筛选
  在where使用运算符进行筛选
• 关键字筛选，关键字前加not来否定
  like 匹配字符串 使用%(一个或多个字符),(一个字符)匹配字符串，使用\来转义%
  in(值1,值2,值3) 判断是否属于后面值。
  between 值1 and 值2 判断数值是否在值1到值2间，包含值1和值2.
  is null 判断是否null
• 逻辑筛选
  使用and or not来组合多个筛选条件。

分组查询

--分组语法
select column1 from tbalename (where condition)
group by column2 (having 判断)
(order by column3 asc|desc);

• 使用group by指定分组字段名，可指定多个。
• select后只能指定分组字段和统计函数。
• 排序时可指定多字段，默认asc升序。
• 使用having进行分组后数据过滤，可使用聚合函数。
• 使用rollup和cube生成分组总计

select col1,col2,avg(col3) from 表名 group by rollup(col1,col2);

显示字段1和字段2分组的平均字段3值。

• grouping(column) 使用grouping函数判断分组统计是否用到该字段。
• group by grouping sets(col1,col2) 显示多种分组查询结果

排序查询

--排序语法
select * from tablename order by col1[ASC|DESC];

• 排序语句一定是sql最后子句。
• 默认为asc升序。
• 当进行多字段排序时，第一个字段为主要排序根据。
• 字段名可用整数序数表示第n字段，但不推荐使用。

多表关联查询

表别名

--表别名语法
select t.name from tablename t;

• 在表名后可以添加表别名，最多30字符。
• 使用表别名后，字段必须为使用别名。
• 表别名仅在该查询语句有效。

内连接表查询

--内连接查询语法
select t1.name,t2.core from table1 t1 
[inner] join
table2 t2
on t1.uid=t2.uid;

• 内连接只查询符合条件的数据。

外连接表查询

--外连接表查询语法
select t1.name,t2.core from table1 t1 
[left|full|right] join
table2 t2
on t1.uid=t2.uid;

• 外连接除了查询匹配的数据，还查询了左表(left)或右表(right)或两表的数据。
• 使用where table1.col1 (+)=table2.col2 可以替换左连接，右连接使用 =(+) 替换。

自然连接表查询

--自然表连接查询语法
select column from tbale1
natural join table2
where col1>10;

• 使用自然表连接时，oracle会自动连接两表相同名称的字段。
• 自然连接使用较少，使用时不能指定列的表名。

自连接表查询

--自连接表查询语法
select column from table1 t1
left join table1 t2
on t1.col1=t2.col2;

• 自连接即连接自身表，此时必须指定表名，防止冲突。

交叉连接表查询

---交叉连接表查询语法
select columns from tbale1 t1
dept cross join
table2 t2;

• 直接将两表构成一个笛卡尔积表，如果不使用where过滤，效率将极低。

子查询

子查询即在sql语句内的另外一条select语句，也叫内查询和内select语句。子查询也可以被包含于另外一条子查询内。

--子查询语法
select t1.name from tbale1 t1 
where t1.uid=(select uid from table2);

• 子查询必须被括号包含。
• 子查询不能使用order by。
• 子查询可以嵌套，但不能超过255层。

单行子查询

• 指返回一行数据的子查询语句。
• 在where 可以使用= > >= < <= <>来比较

多行子查询

• 指返回多行数据的子查询语句。
• 在where使用in any all 进行判断。

关联子查询

• 子查询使用外查询的数据，外查询的判断需要子查询的结果。
• 关联子查询需要遍历全部数据，执行速度较慢。
• 操作数据库时，也可以使用关联子查询。

并集和交集

• union 两个集合的并集，去掉重复数据
• union all 两个集合的并集，不去掉重复数据
• minus 前集合减下个集合的结果
• intersect 两个集合的交集

若有a，b两表，存在重合的数据

a>[1,2,3,4,5]

b>[3,4,5,6,7]

--查询a+b表的数据，且去掉重复的
select col from a
union
select col from b;
> [1,2,3,4,5,6,7]

--查询a+b表的数据，不去除重复
select col from a
union all
select col from b;
> [1,2,3,4,5,3,4,5,6,7]

--查询a表有，b表无的数据
select col from a
minus
select col from b;
> [1,2]

--查询b表有，a表无的数据
--除了模仿上例
select col from a
union
select col from b
minus
select col from b;
> [6,7]

--查询只有a和b表有的数据
select col from a
intersect
select col from b;
> [3,4,5]

--查询ab交集外的数据
(select col from a
minus
select col from b;)
union
(select col from b
minus
select col from a);

插入数据

可使用insert插入单条，也可以使用select批量插入

• 给数值字段提供数据时，可以用单引号包括。
• 给字符串或日期字段添加数据时，必须使用单引号。
• 添加数据时，数据需要满足约束，且主键和not null字段必须提供数据。
• 添加数据时，数据和列的个数与顺序需要一致。

--insert插入单条
insert into tablename1 (col1,col2,col3) values (val1,val2,val3);

--使用select插入多条
insert into tablename1 (col1,col2) (select col3,col4 from tablename2);

• 两个字段列名可以不一样，但数据类型必须一致。
• 可以使用deafult选择默认值，如果没有默认值则为null。

更新数据

--单表更新
update tablename1 set(uid=2,name=‘更新‘) where uid=1;
--使用查询更新
update tablename1 set(uid,name)=
(select uid,name from tablename2 where uid=1);

• 满足判断条件的数据才能进行更新。
• 值为数值可以用单引号包括。
• 值为字符串或日期必须单引号包括。
• 插入的值和字段必须对应个数与顺序。
• 插入的值必须满足约束和主键。
• 可以使用deafult选择默认值，如果没有默认值则为null。
• 可以使用子查询更新数据。

删除数据

--delete删除语法
delete from tablename where uid=1;
--truncate语法
truncate table tablename;

• delete删除满足条件的数据。
• 不指定where将删除表内全部数据。
• truncate删除表内全部数据时使用，并比delete更快，不会产生回滚记录。

3. pl/sql块结构

语法

pl/sql 是一种过程化语言。

--pl/sql语法
declare  --定义变量
name char(10);
begin  --开始指令
	--代码块
end;  --结束指令

注释

--这个是单行注释。
/*
	这个是多行注释。
*/

定义与赋值

name CHAR(10);
name:=‘名字‘;

运算符

关系运算符

逻辑运算符

其它运算符

数据类型

基础数据类型

特殊数据类型

%type 获取指定表的列，相同的数据类型。

declare
	type_test table.column%type;
--后面省略

record 可储存多个列值的一个记录类型

declare
	type record_type is record(
	name char(10);
	uid number(10);
	);
	record_test record_type;
--后面省略

• 使用select into传入值。

%rowtype 获取表的结构

declare
	rowtype_test table%rowtype;
--后面省略

• 使用select into传入值。

条件判断（if和case）

--判断语句语法
declare
	a number(1);
begin
	a:=1;
	--if条件判断
	if a>1 then
		--代码块;
	end if;
	
	if a>1 then
		--代码块;
	else
		--代码块;
	end if;
	
	if a>1 then
		--代码块;
	elsif a>2 then
		--代码块;
	end if;
	--case条件判断,值和变量必须同一类型
	case a
		when 1 then
			--代码块;
		when 2 then
			--代码块;
		else
			--代码块;
	end case;
end;

循环语句

--循环语句语法
declare
	flag number(2);
begin
	flag:=10;
	--while循环
	while flag>0 loop
		--代码块;
		flag:=flag-1;
	end loop;
	--do循环
	loop
		--代码块;
		flag:=flag-1;
	exit when flag<=0;
	end loop;	
	--for循环
	for i in 1..flag loop
		--代码块;
	end loop;

• return 退出当前函数或者存储过程
• exit 退出当前循环，不再进行循环
• continue 退出本次循环，开启下次循环

游标

• 游标分为显式游标和隐式游标。
• 显式游标是用户定义和操作的，隐式游标为oracle操纵。
• 显式游标的使用步骤为，声明、打开、读取和关闭。

显式游标

--游标使用
declare
	--	定义游标，传入参数可以直接赋值
	cursor cursor_test(pat in char(10):=‘传入参数‘)
	is (select uid from table1 where uname=pat);
	uid number(10);
begin
	open cursor_test;  --开启游标
	fetch cursor_test into uid;  --读取游标
	while cursor_test%found loop  --循环读取游标
		fetch cursor_test into uid; 
	end loop;
	close cursor_test;
end;

--for循环读取游标，不需要操作游标的打开关闭
declare
	cursor cursor_test(pat in char(10):=‘传入参数‘)  --也可以在使用游标时传参，cursor_test(‘t‘)
	is (select uid from table1 where uname=pat);
	uid number(10);
begin
	for cursor_one in cursor_test loop
		dbms_output.put_line(cursor_one.uid);
	end loop;
end;

• 使用游标前需要检查是否打开。
• 每次取出数据都需要检查是否返回成功。
• 取出数据时，需要和变量组的各个数据类型一样。
• 使用完游标必须关闭，释放内存。

游标属性(只能在pl/sql使用)

• %found 布尔值，是否影响到任何数据。
• %notfound 布尔值，与%found相反。
• %rowcount 数字，返回影响语句的行数。
• %isopen 布尔值，返回游标是否打开。

隐式游标

sql是oracle的隐式游标，默认sql为上一条语句的隐式游标。

异常处理和抛出

• 当运行pl/sql程序时，可能出现代码错误，磁盘出错等问题，需要使用异常来处理。
• 异常处理分为预定义异常处理和用户自定义异常处理。

--异常使用语法
declare
	exception_test exception;  --定义异常
	--初始化异常，数字是异常在系统中的编号，-06502表示数值溢出
	pragma exception_init(exception_test,-06502);  
begin
	--可能发生异常的代码块
exception
	when exception_test then
		--捕获到数值溢出的异常，进行处理的代码块
		--也可以主动抛出异常
		raise exception_test;
	when other then
		--other表示全部异常
end;

存储过程

存储过程是一种被命名的pl/sql块，可以有或没有传入参数，但一般没有返回值。
一般保存为prc文件

--存储过程语法
--传入传出变量，不指定变量长度
create or replace procedure 
prc_first(pat1 in char,pat2 out char)  
is
--定义内部变量
uid number(10);
begin
	--代码块
(exception)
	--异常处理
end prc_first;

调用存储过程时，传入多个变量的常用方法
使用=>进行传入参数，当使用了指定名称传参后，后面不能使用位置传参。 可以使用pat1 in number default 10来设置参数默认值。 存在in out`表示参数传入且传出。

--其它步骤省略
prc_first(pat1=>val1,pat2=>val2,pat3>val3);
prc_first(val1,val2,val3);
prc_first(pat1=>val1,val2,val3);

获取传出参数时，需要在外部定义变量，传入存储过程，再获取变量。
删除存储过程，使用drop procedure prc_first;

FORALL和BULK COLLECT

FORALL增强批量操作DML语句能力

--FORALL批量插入
declare
begin
	FORALL i in 1..10
		insert into tablename (col1,col2) values (val1,val2);
end;

BULK COLLECT将查询结果一次性加载，减少loop开销

--BULK COLLECT查询
declare
	type name_type is table of varchar2(20);
	names name_type;
begin
	select table_name BULK COLLECT into names from all_tables;
end;
--BULK COLLECT从游标批量获取
declare
	type name_type is table of varchar2(20);
	names name_type;
	cursor cursor_test is (select table_name from all_tables);
begin
	open cursor_test;
	loop
		fetch cursor_test BULK COLLECT into names limit 10;
		exit when tables.count=0;
	end loop;
	close cursor_test;
end;

函数

函数一般是用来计算和返回一个值，必须要返回值。

--定义函数
--传入参数
create or replace function func_test(pat in char)
--设置传出参数的数据类型
return char
is 
begin 
	--代码块;
(exception)
	--异常处理
end;

删除函数，使用drop function func_test;

触发器

触发器可以看做特殊的存储过程，当数据库发生变化时，可以执行指定的程序，用于管理表的复杂约束，监控表的变化。

--触发器语法
create or replace trigger trigger_test      --创建触发器名称
before|after|instead of tri_event			--触发的时机
on table_name|view_name|user_name|db_name   --触发的对象
(for each row) when trigger_condition		--是否行级触发和触发条件
begin
	--代码块;
end trigger_test;

触发器类型

• 行级触发器，每一行数据的变化都会启动触发器
• 语句级触发器，DML语句启动触发器，和影响数据数量无关
• 替换触发器，对视图使用
• 用户事件触发器，用户登录或修改表结构时启动触发器
• 系统事件触发器，系统级的事件，例如数据库实例的启动和关闭

--删除触发器
drop trriger trigger_test;

程序包

4. oracle常用函数

字符类函数

数字类函数

日期时间函数

使用sysdate 不加()，获取当前日期

转换类函数

聚集函数

5. 表空间

orcale的磁盘空间最高逻辑是表空间。

下一层是段，段的下一层是盘区，盘区的下一层是数据块，数据块是磁盘空间逻辑最底层。

--创建永久表空间
create tablespace tablespacename
datafile ‘\home\me\data\tbs_me.dbf‘  --表空间数据文件
size 10m;

6. 数据表操作

数据表是oracle的主要存储方式。

创建表

--创建数据表语法
create table tablename(
	uid number(10),  --定义表内的参数
	name char(10)
)
tablespace  --表空间名
storage(    --存储参数，表空间为自动化管理时，才能指定参数
    initial 256k  --第一个盘区的大小
    next 1m		  --下一个盘区的大小
    minextents 1  --最少盘区数
    maxextents unlimited  --不限制最大盘区数
)  
--以下是数据块管理参数，未赋值
Pctfree 	--表保留块所占的百分比
Pctused 	--表示数据块高于指定百分比时，可以插入
Initrans 	--初始化事务槽个数
Maxtrans 	--最大事务槽数量
Initial 	--初始块大小，默认单位B
Next 		--初始块满后，每次扩展大小，默认单位B
Minextrents --初始创建分配块数量
Maxextrents --最多可分配块数量

维护表

增加字段

alter table tablename add(colname char(10));

• 注意add中间是空格分隔，不是逗号！

修改字段

alter table tablename modify colname char(12);

• 当表内有数据时，字段只能改为可以兼容的类型，比如char(10)=>char(12)

删除字段

--删除单个字段必须使用column关键字
alter table tablename drop column colname;
--删除多个字段，必须使用括号包裹，且不能使用该关键字，也不能删除全部字段
alter table tablename drop (col1,col2,col3);

重命名数据表

alter table tablename rename to table_newname;

• oracle会自动更新相关外键，约束定义，但存储结构等需要手动更新。

修改表空间

alter table tablename move tablespace tbs_new;

修改存储参数

alter table tablename pctfree 20 pctused 60;

修改表的状态

--使表只读
alter table tablename read only;
--使表可读写
alter table tablename read write;

删除表

删除表时，会删除所有表内数据，相关索引和触发器，收回分配空间，从字典数据中删除定义。加上cascade constraints会删除所有引用该表的视图，约束和触发器。

--删除数据表语法
drop table tablename (cascade constrains);

表约束

主键约束

一个表内最多一个主键约束，主键默认非空，当主键约束为单列时，称为行级约束，主键约束为多列组成时，为表级约束。

--添加主键约束
alter table tablename add constraint pk_column primary key(clumn);

非空约束

非空约束该列必须有值，不得为null。

--添加非空约束
alter table tablename modify column not null;

唯一约束

唯一约束不允许该列有重复的值，但可以有多个null

--添加唯一约束
alter table tablename add constraint uniq_col unique(columns);

外键约束

外键将引用另一张表的列，被引用的列需具有唯一约束或者是主键，该列值必须在被引用的列中存在

使用on delete cascade后，被引用列删除数据后，外键列也会删除引用的数据。

--添加外键约束
alter table tablename1 add constraint fore_col foreign key(column1)
references tablename2(column2);

也可以在创建表的时候直接设置上述约束

--创建表时添加约束
create table tablename(
	uid number(10),
    name not null,
    school_id number(10) unique,
    class_id number(10),
    constraint pk_uid primary key(uid),
    constraint fore_class_id foreign key(class_id) references tbale_class(cid)
)

激活禁用和删除约束

--禁用约束
alter table tablename disable constraint con_name;
--激活约束
alter table tablename enable constraint con_name;
--删除约束
alter table tablename drop constraint con_name;

7. 索引

• 索引是为了优化查询的数据库对象。

• 索引应建立在where经常使用的字段上。

• 经常需要使用列排序，需要考虑建立索引。

• 索引的个数需要限制，过多会降低dml操作速度。

• 根据表使用情况设置索引的快空间参数。

• 表和索引在同一表空间便于管理，不同表空间提高性能。

• 在大表建立索引，使用nologging节省重做日志空间，降低建立索引时间，提高索引性能。

• 小表不做索引。

• 多表查询时，连接的列应该建立索引。

--创建索引语法
create (normal|unique|bitmap) index index_test
on tablename(columns) (reverse)
pctfree 25
tablespace tablespacename;

唯一索引

唯一索引相当于添加了唯一索引，值可以为null

b树索引

• b树索引是默认的索引，存放数据升序排序.

• b树索引保证每个搜索层次一致。

• b树索引适合基数大的字段。

位图索引

create bitmap index index_test on tablename(column);

• 位图索引适合基数小的字段。

反向索引

create index index_test on tablename(column) reverse;

• 反向索引阻止b树偏向于一个方向。

• 反向索引使数据随机分散。

• 反向索引适合单调递增的字段。

函数索引

create index index_test on func(tablename(column));

• 函数索引适用于查询条件经常使用该函数的情况。

复合索引

create index index_test on tablename(col1,col2,col3);

索引控制

--修改索引表空间
alter index index_test rebuild tablespace tablespacename;
--合并索引
alter index index_test coalesce deallocate unused;
--重建索引
alter index index_test rebuild;
--删除索引
drop index index_test;

• 重建索引可以修改索引参数

• 合并和修改索引都可以清除索引的碎片，提高效率。

8. 视图

视图是由查询输出的虚拟表。

--创建视图语法
create or replace view view_name (columnsname_new)
as select columns from tablename
(with check)
(with read only);

--查看视图定义
desc viewname;
--修改视图定义
create or replace view  --(省略后续定义)
--重新编译视图
alter view vivewname compile;
--删除视图
drop view viewname;

删除视图不会影响到视图查询的数据。

9. 同义词和序列

同义词

同义词是表、索引和视图的别名。

同义词分为公有和私有，公有同义词可以直接访问，私有同义词被其它用户访问时，需要指定模式名和拥有权限。

--创建同义词
create (public) synonym synonymname for username.object;
--删除同义词
drop (public) synonym synonymname;

序列

• 序列是oracle提供输出唯一数字的对象。

• 序列可以提供递增或递减的数字。

• 序列可以在多用户并发环境使用。

--创建序列
create sequence sequencename
maxvalue 100	--最大值
min value 1		--最小值
start with 1	--起始值
increment by 1  --递增或递减值
cycle|nocycle	--是否循环
cache|nocache	--是否缓存
order|noorder;	--是否按序变化
--使用序列
select sequencename.nextval from dual; --初始化和获取下一位序列
select sequencename.currval from dual; --获取当前序列值
--修改序列结果
alter sequence sequencename increment by -1;  --修改为递减1
--删除序列
drop sequence sequencename;

10. 用户与角色

用户

创建用户

--创建用户语法
create user username identified by password		--设置用户名和密码
default tablespace tablespacename   			--指定默认表空间
temporary tablespace temporary_tablespacename   --指定临时表空间
quota [10 m|unlimited] on tablespacename		--设置用户的表空间额度

• 用户名和密码，一般是字母、数字、’#’和’_’

• 不指定表空间时，默认使用system为用户表空间。

用户管理

--修改用户密码
alter user username identified by newpassword;
--修改表空间
alter user username remove tablespace to tablespacename_new;
--修改表空间额度
alter user username quota [10 m|unlimited] on tablespacename;
--解锁用户
alter user username account unlock;
--删除用户
drop user username (cascade);  --cascade表示删除用户的数据库对象

用户权限管理

授权

--授权用户
grant privis to username  --privis 表示授予权限名，可以多个
(with admin option);

• with admin option表示被授予用户可以传递权限

取消授权

--收回权限
revoke privis from user;

• 在收回权限前，被授权的用户，不会因为授权用户被收回权限而收回权限。

角色

角色即一组权限，可以赋予给用户。

角色分为系统预定角色和自定义权限。

--创建角色
create cole colename identified by password;
--授权给角色
grant privis to colename;
--收回权限
revoke privis from colename;
--设置无密码
alter cole colename not identified;
--修改密码
alter cole colename identified by password_new;
--删除角色
drop cole colename;
--查询当前有效权限
select * from session_roles;

11. 事务控制

事务是一系列语句构成的逻辑工作单位。

隐式事务是oracle自动执行

oracle认为结束事务

1. commit语句提交事务。

2. rollback回滚数据。

3. 执行ddl或dcl语句。

4. 正常断开数据库连接。

--事务操作
--设置事务
set transaction [read only|read write];  --设置事务是只读还是读写

set autocommit on  	 --设置事务自动提交，每条dml都会提交事务，不推荐。

rollback;          	 --回滚事务

savepoint point1;	 --保存回退点
rollback to point11; --回滚到事务开始前或指定回退点。

12. 并发和死锁

当多个事务同时访问或修改共享数据库，将破坏事务隔离性。

使用锁保证数据的并发。

--共享锁
lock table tablename in share mode;
--排他锁
lock table tablename in exclusive mode;
--行级共享锁
lock table tablename in row share mode;
--行级排他锁
lock table tablename in row exclusive mode;
--共享排他锁
lock table tablename in share row exclusive mode;

各种锁之间的排它性

• 死锁为两个会话互相等待对方持有的资源。

• 执行事务时需要尽快提交，因为事务提交前会一直持有当前的锁。

13. sql优化

什么时候使用索引

1. 使用字段名替代*查询，节约系统查询数据字典时间。

2. 使用truncate 替代delete删除表数据，delete将使用撤销表空间存储删除数据。

3. 在pl/sql及时使用commit，释放锁，回滚段的数据。

4. 查询时减少表查询次数，防止无效的多次查询。

5. 大部分情况使用exists替代in，in将会进行数据排序。

6. 将更适合做驱动表的表放在右边。

7. 在where语句里，按过滤可能从左至右连接条件。

合理使用索引

1. 不对索引使用非空判断，不等于判断。

2. 不对索引使用计算表达式。

14. sql*plus

oracle基础学习