Oracle学习笔记

举一隅不以三隅反，则不复也。

安装Oracle数据库

• 参考博客：https://blog.csdn.net/ychgyyn/article/details/85880038

Oracle数据库账户管理

预定义sys账户

• 系统管理员，拥有最高权限
• 默认解锁
• 授予了DBA角色
• 具有admin option的所有权限
• 具有启动、关闭和某些维护命令时需要的账户
• 拥有数据字典
• 拥有自动工作量资料档案库（AWR）
• 使用as sysdba字句连接（任何具有SYSDBA权限的用户均可通过使用as sysdba连接到sys账户）

预定义system账户

• 本地管理员，次高权限
• 默认解锁
• 授予了DBA角色

预定义scott账户

• 普通用户，密码默认为 tiger
• 默认未解锁，安装时手动解锁，一般不直接用系统账户

使用sys账户登录报错

• 问题描述：使用sys账户登录oracle数据库报错 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

• 解决方案：

请输入用户名：sys as sysdba
请输入口令：xxx

常用的系统权限

• 系统权限: 对于数据库的权限
  
  

常用的对象权限

• 对象权限: 操作数据库对象的权限
  
  

授权

创建用户

CREATE USER scott IDENTIFIED BY tiger;

• 用户登录至少需要create session权限
  

授予权限

GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...];

-- 开发需要的权限
GRANT create session, create table, create sequence, create view, create procedure TO scott;

创建用户表空间

• 用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表

ALTER USER scott QUOTA UNLIMITED ON users

创建角色

create role manager;

为角色赋予权限

grant create session,create table to manager;

将角色赋予用户

grant manager to scott;
-- boom!!!
grant dba to zhangsan;

修改密码

ALTER USER scott IDENTIFIED BY lion;

分配对象权限

• 不同的对象具有不同的对象权限
• 对象的拥有者拥有所有权限
• 对象的拥有者可以向外分配权限

GRANT	object_priv [(columns)]
ON		object
TO		{user|role|PUBLIC}
[WITH GRANT OPTION];
-- WITH GRANT OPTION 使得被分配的用户有再分配该权限的权利
-- public是想数据库中所有用户分配权限
grant select on tableA to userA,userB;
grant insert,update on userA.tableA to userB;

回收对象权限

REVOKE {privilege [, privilege...]|ALL}
ON	  object
FROM   {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
-- 使用 WITH GRANT OPTION 子句所分配的权限同样被收回
REVOKE  select, insert ON departments FROM scott;

查看权限分配情况

数据类型

序列

-- 创建序列
create sequence INR_REQUIRMENT_SQUENCE
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXVALUE -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    CACHE 10;

-- 获取序列的下个值
select INR_REQUIRMENT_SQUENCE.nextval from dual;
-- 获取序列的当前值
select INR_REQUIRMENT_SQUENCE.currval from dual;

create sequence seq_test1
increment by 2
start with 3
minvalue 10
maxvalue 100
cycle
cache 5;

-- 修改序列
alter sequence seq_test1;
-- start with不能修改

• 将序列值装入内存可提高访问效率

索引

• 自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
• 手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

create index 索引名称 on 表名(列名)
-- 创建普通索引
create index idx_name on t_index_test(name);
-- 创建唯一索引
create unique index idx_id on t_index_test(id);
-- 创建复合索引
create unique index idx_id_name on t_index_test(id,name);

同义词

• 别名！

create [public] sysnonym 别名 for object
object可以表示表，视图，序列

-- 创建表的同义词
create synonym test1test1 for test1;

视图

• 简单视图：单表、没有聚合函数；改视图也会修改原表数据
• 带检查约束的视图：with check option
• 只读视图：with read only
• 先有视图后建表：create force view xxx
• 多表查询的视图，键保留表：在视图中把主键保留下来的那个表，只能update键保留表的字段
• 分组聚合统计的视图：只读，无法修改
• 物化视图：真实表的副本，create materialized view

存储函数

create function myFunc(参数名 参数类型，参数名 参数类型)
return 结果变量数据类型
is
    变量声明部分；
begin
    逻辑部分；
    return 结果变量；
    [exception]
        异常处理部分
end

存储过程

• 存储函数有返回值，且必须返回，存储过程没有返回值
• 存储函数可以在select中直接使用，而存储过程不能，过程多数是被应用程序所调用
• 存储函数一般是是封装一个查询结果，而存储过程一般是封装一段事务

-- 参数模式 IN(default)，OUT, IN OUT
create procedure myProc(参数名 参数类型，参数名 参数类型)
is/as
    变量声明部分；
begin
    逻辑部分；
    [exception]
    异常处理部分
end

伪列

• 伪列：建表时自动加上的，常见有rowid, rownum
• rownum关系运算只能是<，≤

-- 伪列ROWID是一个物理地址，长这样：AAAUxeAAEAAAAIOAAA
select ROWID from test1;

-- 伪列ROWNUM是一个结果集的序号，从1开始
SELECT ROWNUM from test1;

-- 同时使用伪列和其他字段必须给表起别名
select ROWID, ROWNUM, t1.* from test1 t1;

分页

-- 前10条数据
select rownum,t.* from test t where rownum<=10;

-- 简单查询的分页
select t.* from (select rownum r,t.* from test t) where r<=20 and r>10;

-- 排序后的分页
select ttt.* from (select rownum r,tt.* from (select * from test t order by usernum desc) tt) ttt where r<=20 and r>10;

数据库表元数据

-- 查看当前用户拥有的表
select table_name from user_tables;

-- 查看所有用户的表
select table_name from all_tables;

-- 查看当前用户下某表所有字段
select * from user_tab_columns where TABLE_NAME='某表名称';

-- 查看所有用户的某表所有字段
select * from all_tab_columns where TABLE_NAME='某表名称';

-- 查看当前用户下所有表注释
select * from user_tab_comments;

-- 查看所有用户的表注释
select * from all_tab_comments;

- 查看当前用户下某表所有字段注释
select * from user_col_comments where TABLE_NAME='某表名称'；

-- 查看所有用户的某表所有字段注释
select * from all_col_comments where TABLE_NAME='某表名称'；

备份

整库导入导出

# 在命令行操作
exp system/密码 file=文件名 full=y
# 如果不指定文件名，默认为EXPDAT.DMP
imp system/密码 full=y file=文件名

按用户导入导出

# 在命令行操作
exp system/密码 owner=用户名aaa file=fae.dmp
imp system/密码 file=fae.dmp fromuser=用户名aaa

按表导入导出

# 在命令行操作
exp 用户名/密码  file=xxx tables=t1,t2
imp 用户名/密码 file=xxx tables=t1,t2

导入导出指定用户的指定表

各种函数

trunc

-- 只取日期部分
select trunc(sysdate) from dual; 
-- 截取月，即本月第一天   
select trunc(sysdate，'mm') from dual;  
-- 截取年，即今年第一天
select trunc(sysdate，'yyyy') from dual;  

find_in_set

• find_in_set是mysql中的函数，参考博客

create function find_in_set(arg1 in varchar2,arg2 in varchar)
return number is Result number;
begin
select instr(','||arg2||',' , ','||arg1||',') into Result from dual;
return(Result);
end find_in_set;

uuid

select sys_guid() from dual;

nvl、nvl2

• 空值处理函数

-- 第一个参数xxx不为NULL时，返回第一个参数，否则返回第二个参数
select nvl(xxx,233) from dual;

-- 判断第一个参数yyy的值，不为NULL时返回第二个参数，为NULL时返回第三个参数的值
select nvl2(xxx,3545,4645) from dual;

decode

• decode(判断值，值1，翻译值1，值2，翻译值2,…,缺省值)
• 条件取值，可以没有缺省值

select decode(sex, '0','男','1','女','未知') from xxx

分析函数

-- 值相同，排名相同，序号跳跃
select rank() over(order by score desc), t.* from exams t;

-- 值相同，排名相同，序号连续
select dense_rank() over(order by score desc), t.* from exams t;

-- 返回连续的排名，无论值是否相等
select row_number() over(order by score desc), t.* from exams t;

-- 利用分析函数实现分页
select * from (select row_number() over(order by score desc) rownumber, t.* from exams t) where rownumber <=20 and rownumber>10;

语法杂记

• insert update delete要commit
• delete可以rollback，truncate不能rollback
• delete可能产生碎片，不释放空间
• truncate是摧毁表结构，再重建表结构
• varchar是定长字符串，varchar2是不定长字符串
• 分页查询：SELECT * FROM Persons WHERE ROWNUM <= 5
• concat函数只能接受两个参数，要连续拼接多个字符串可以用 ||
• 别名：紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
• 日期和字符只能在单引号中出现
• 变量赋值使用 := 符号
• DUAL 是一个‘伪表’，可以用来测试函数和表达式
• 通配符转义：可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号
• having 是对聚合统计后的结果进行筛选
• 集合运算：union all 可重复 union 不重复
• case when then

select (case sex when '0' then '男' when '1' then '女' else '未知' end) from xxx

• 创建DB Link

create public database link 数据库链接名称
 
connect to 远程数据库用户名 identified by 远程数据库密码  
 
using '远程数据库IP:端口/实例名';

注：部分图片来自尚硅谷网课