oracle数据库笔记

数据库笔记

DML数据操纵语言
增删改查
DDL数据定义语言
create，alter，drop
DCL数据控制语言
grant,

其他知识

元组：数据库中一行记录就是一个元组
执行批量sql脚本：使用.pdc文件，参考：pdc执行多个sql示例1、pdc执行多个sql示例2
归档：数据库有联机重做日志，一般数据库有两个联机重做日志，一个写满了就会切换，在归档模式下，在切换时，日志1的数据会被保存到归档目录中（这个文件叫归档重做日志），不归档就直接消失了。
空值null参与算数运算，结果为空
一般都是单引号

表空间

是一种用于存储数据库对象的逻辑空间，是oracle中信息存储最大的逻辑单元，其下还有段、区、数据块等逻辑数据类型。
分类：

• 永久表空间：存储要永久化存储的对象，如表、视图、存储过程等。
• 临时表空间：存储数据库的中间执行过程，比如保存order by 排序，分组时产生的临时数据，存储完成后自动释放。
• undo表空间：保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

权限

系统权限：规定用户使用数据库的权限。针对用户
对象权限：用户对其他用户的表或视图的存取权限。针对视图

系统权限分类：

• DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
• RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
• CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户，给与connect和resource权限
dba用户给与connect、resource和dba权限

授权命令：

--授予系统权限
grant connect,[resource],[dba] to 用户名1,[用户名2];
--授予对象权限
grant create session,create table to [用户名][with admin option];
给用户转授权限的权限：
with grant option级联，比如给A转授，A授予了B权限，收回A权限时，B的权限也会失效。
with admin option不是级联，收回A的权限，B的权限不会失效。

增删改查语法

insert into 表名 [(列名)] values (值列表)
update 表名 set 列名 = 更新值 [where 更新条件]
delete [from] 表名 [where <删除条件>]
select [(列名)或*] from 表名 [where 查询条件]
批量更新：
merge into 目标表
using 数据来源（表/视图/查询）
on 目标和数据来源的条件(table1.c = table2.c , ......)
when matched then
条件满足则更新 update set table1.a = table2.a , table1.b = table2.b ;
when not matched then
条件不满足则插入 insert (table1.a,table1.b) values (table2.a,table2.b);

查询表

desc 表名;--查询表结构
where to_char(date,'yyyy-mm-dd') = '2021-09-25';--判断日期属于9.25号的
where [not] between 20 and 40;--查询大于等于20小于等于40的

order by排序

order by A asc,B asc;--先按A升序，之后再按B升序
select wareqty*12 as salary from t_ware order by salary;--可以用别名进行排序

模糊查询

where 字段名 [not] like '%红%';--查询带红字的
where 字段名 [not] like '%红%黑%';--查询红某黑/某红黑某/某红黑/红黑某，红在前，黑在后
where 字段名 [not] like '_红'; --查询某红的
where name like '%/_%' escspe '/';--查询名字中有_的

子查询

--from 子查询：
from(select ...)where...
--select 子查询：
select 字段,(select ...) as 别名 from...

相关子查询

外层查询的条件作为一个元组与内层查询判断，判断完再将下一个元组与内层查询判断

相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。执行过程：
（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
（2）执行内层查询，得到子查询操作的值。
（3）外查询根据子查询返回的结果或结果集得到满足条件的行。
（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

SElECT 图书名,出版社,类编号,价格
FROM Books As a
WHERE 价格 >
(
SELECT AVG(价格)
FROM Books AS b
WHERE a.类编号=b.类编号
)

先将Books表中的第一条记录的“类编号”的值“2”代入子查询中，子查询变为：
　　　　　　SELECT AVG(价格)
　　 　　　　FROM Books AS b
　　　　 WHERE b.类编号=2
子查询的结果为该类图书的平均价格，所以外部查询变为：
　　　　　　SElECT 图书名,出版社,类编号,价格
　　　　　　 FROM Books As a
　　　　　WHERE 价格 > 34

修改表结构

alter table 表名 add 字段名 字段类型；--增加字段
alter table 表名 modify 已存在字段名 类型；--修改字段类型，在字段无数据时
--字段有数据时，长度可以改（可以增大，减小时要看字段中数据最长值），类型不能改
alter table 表名 drop column 字段名;--删除字段

删除数据truncate 和 delete区别

数据量小，没有区别，数据量大，truncate更快
delete支持rolllback回滚，truncate不支持
delete会记录日志，truncate不会记录日志
delete可以结合where部分删除，truncate只能删除所有数据
truncate删除后自增id会初始化，delete删除后自增id不变

异常

raise_application_error(error_number,message[,[truefalse]]);
用于在plsql使用程序中自定义不正确消息，该异常只好在数据库端的子程序（流程、函数、包、触发器）中运用。
 - error_number用于定义不正确号，该不正确号必须在-20000到-20999之间的负整数；
 - message用于指定不正确消息，并且该消息的长度无法超过2048字节；
 - 第三个参数假如为true，则该不正确会被放在先前不正确堆栈中，假如为false（默认值）则会替代先前所有不正确。
用法：if 条件 then raise_application_error();

循环

for 变量 in (select 语句) loop 执行语句 end loop;
变量中可以存储多个值。

去空格+取首字母

select f_st2zjm('王瑞') from dual;
update s_user_base set USERABC = f_st2zjm(USERNAME);--取首字母
update s_user_base set USERNAME = replace(USERNAME,' ','');--去掉姓名中空格

取中文首字母函数

CREATE OR REPLACE FUNCTION f_st2zjm(p_name IN VARCHAR2) RETURN VARCHAR2 AS
  v_compare  VARCHAR2(100);
  v_return   VARCHAR2(4000