Oracle基础知识
Published in：2022-01-31 | category： Oracle
Words: 2.2k | Reading time: 8min | reading：

1 Oracle安装注意事项

  • 版本问题 ：

    10g: (gid) 700M

    11g (grid) 3G 给了2个zip，需要解压在一起

  • 解压后的源文件目录不能有空格或汉字

  • 安装目录不能带有空格或汉字

2 Oracle数据文件类型

  • DBF 数据文件
  • CTL 控制文件
  • LOG 日志文件

3 工具

  • SQLPlus 管理员首选
  • DBCA 数据库管理助手
  • NCA　网络配置助手

4 表空间

  • 数据库下最大的逻辑存储单元

  • 默认表空间

    • system : 默认的系统表空间，通常用于存储数据字典内容 大小会影响系统性能
    • systemaux ： 扩展的系统表空间10g之后才有的 ， 为新功能使用
    • users： 用户表空间，除系统默认用户外使用
    • temp：临时表空间 供交换数据使用
    • undo： 回滚表空间，用户删除后存储（类似于回收站空间）
    • example：示例表空间 系统如果安装了example示例数据库及用户所占空间

  • 创建表空间 必须是管理员才有权限

    命令：

    1
    2
    3
    4
    create tablespace 表空间名(如:tbstest)
    datafile '文件存储路径'(如：D:\oradata\orcl\tbstest.dbf)
    size 文件大小(如：10m)
    autoextend on

5 Oracle驱动安装及配置

导入依赖

1
2
3
4
5
6
7
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

properties配置

1
2
3
4
spring.datasource.driver-class-name:oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc.oracle:thin:@//localhost:1521/orcl
spring.datasource.username=******
spring.datasource.password=******

6 模式(schma)

  • 用户及其所有对象的集合(table,constrain,column,index,sequence,view,procedure,function,游标,程序报(package),触发器)

  • 用户名即模式名

  • 一个用户对应一个模式

7 相关关系

  • 一个数据库服务器可以安装n个实例

  • 一个实例对应一个数据库

  • 一个数据库含有n个用户，每个用户对应一个模式

8 关于权限

  • 创建用户:create user 用户名 identified by 密码 default tablespace 默认的用户表空间

  • 授权:

    1
    2
    3
    grant 角色1 , 角色2 to 用户名
    例如: grant connect , resource to j2008
    grant select|insert|update|delete on 表名 to 用户名

  • 权限:

    • 系统权限:例如 create session,create table
    • 系统角色:connect ,resource
    • 对象权限:依附于某个对象(表,视图,过程,函数等)

  • 授权规则:

    • 系统权限或角色,只有管理员才能授权(system,sys)

    • 对象权限对象所有者授权

    • 在级联授权中,例如: A用户–>B用户–>C用户

      如果是系统权限,当B失去权限时,C用户权限有效

      如果是对象权限,当B失去权限时,C用户权限失效

9 ddl语法

ALTER

  • 添加字段
  • 修改数据类型
  • 添加约束

TRUNCATE & DROP & DELETE 区别

  • truncate 截断表:干掉表中数据,但保留数据结构,且不用记录日志,因此数据不可恢复
  • drop 删除对象(表,用户,过程,函数等) 记录操作日志 可恢复
  • delete 删除数据,干掉数据 保留结构 记录操作日志 可恢复

10 日期类型

  • date & timestamp
  • date’日期字符串’ 例如 : date’2021-02-03’
  • to_date(‘日期字符串’,’日期模板’) 例如: to_date(‘2021/02/03’,’YYYY/MM/DD’)

11 复杂查询结构

1
2
3
4
5
select 字段列表
from 表清单
where 查询条件
group by 分组字段
having 分组查询条件

带有分组查询的显示字段只有三种情况:常量,分组函数,分组字段

12 经典查询

  • 养成从客户角度去思考sql指令的习惯
  • 子查询需要下功夫
  • 递归表的查询(自上而下,自下而上)
  • 去重
  • 行列倒置

13 视图

视图分为三级

  • all_:管理员
  • dba_ : 管理员有权访问
  • user_:普通用户访问

14 锁与实务

  • 事务ACID
  • 锁 是事务内在原理 ,事务是锁的外在呈现
  • commit 提交并释放锁
  • rollback 回滚 并释放锁

15 序列

能够创建一组连续 数字的对象

语法: 

1
2
3
4
5
6
7
CREATE SEQUENCE toys_seq
	START WITH 10	//起始    不可更改
	INCREMENT BY 10 //步长
	MAXVALUE 2000	//最大取值
	MINVALUE 10		//最小取值
	NOCYCLE 		//是否循环
	CACHE 10;		//是否缓存

特点:

  • 在Oracle中没有自增列特性,可以使用序列实现列自增或自减

  • 在Oracle中一个表可以由n个自增列,在mysql只能由一个列自增

  • 一个序列可用于不同的列不同的表的列

  • 序列的定义存放在user_sequences数据字典中

常用操作:

  • 查看序列下一值: select 序列名.nextval from dual
  • 查看序列当前值: select 序列名.currval from dual

修改序列定义

除start with 起始值不可修改以外 ,其他属性都是能改的

16 表分区

为什么要使用表分区

  • 单表数据记录大于1500万-2000万
  • 单表数据空间占用大于2G以上
  • 客户仅仅对部分表数据感兴趣,其他基本处于历史状态
  • 非常明显的数据操作特征

分区分类

  • 从9i版本开始,不断在分区领域扩张
  • mysql8.0之前的版本没有分区,sqlserver2016前没有分区

常见分区:

  • 范围分区:按照某列值的取值范围做的分区操作

复合分区

如果一次分区不能缓解使用效率 二次分区（复合分区）

  • 范围+hash
  • 范围+list

维护分区

  • 添加分区
  • 删除分区
  • 合并分区
  • 拆分分区
  • 截断分区

17 视图

  • 优势：
    • 提供了另外一种级别的表安全性（通过 单视图授权实现）
    • 隐藏的数据的复杂性
    • 简化的用户的SQL命令
    • 隔离基表结构的改变
    • 通过重命名列，从另一个角度提供数据
视图分类

  • 简单视图：单表视图 基表是一个表

    • 继承基表的约束（主键、字段约束等）
    • 对视图的操作等价于对单表的操作

  • 复杂视图：基表为两个以上表或视图

键保留表

  • 定义：视图主键所在的基表就是键保留表

  • 如果修改的字段属于键保留表的字段 则允许修改 ，否则不允许

  • 对于复杂视图无法通过视图来修改非键保留表字段（如因业务需求无法满足必须通过视图修改，则需要触发器实现）

18 索引

特点

  • 索引占用空间的
  • 索引是双刃剑，在提升查询性能同时会降低修改的效率
  • 减少IO操作
  • 经常谈及的SQL优化均是围绕着索引的应用展开的

什么场景下建什么样的索引（sql优化的基本原理)

  • 列值数量/记录数 如果趋近于0 则适合于创建位图索引；反之适合建唯一索引
  • 在查询条件所在列建标准索引或函数索引
  • 如果查询条件为字符串字段，适合建函数索引（UPPER函数)
  • 在多表关联时 (外键)字段适合建标准索引
  • 如果列的取值是连续的 且经常作为查询条件的话，此列可建反向键索引

索引的使用限定

  • 单表索引数不大于5个
  • 如果查询未使用索引，应该使索引无效（不再降低修改的效率)

索引原理

  • B+结构原理

查询优化的本质就是避免语法导致索引失效

19 PL/SQL基础

Procedure Language / Structure Query Language

记名块：过程、函数、程序包、触发器 可以多次调用

不记名块：只能执行一次

语法：

1
2
3
4
5
6
7
declare 
		声明部分;
	begin
		执行部分;
	exception
	    错误部分;
	end;

给变量赋值

  • 变量 := 值
  • select 值 into 变量 from 表;

属性类型

  • 定义不知道类型的数据时,可以使用

    1
    变量名 表名.表中数据名%type

  • 某字段类型：为了冗余后期字段数据类、大小的变化

    例如：name dept.dname%type name变量的类型及大小随着dpet.dname而变

  • 某记录类型：冗余 字段增减 而设

    例如：rec dept%type ; rec 指向dept的一条记录

20 游标

定义

一段格式化的内存 例如：JAVA编程 resultSet集合封装的游标

  • 游标的属性：notfound、rowcount、found

  • 隐式游标

    oracle默认游标名sql

    自动打开自动关闭 isopen 一直为false

    notfound当操作影响了行数 则为false，否则为true

    found与notfound相反

    rowcound累计值

  • 显式游标

    • 声明游标
    • 打开游标
    • 提取记录
    • 关闭游标

21 存储过程

语法

1
2
3
4
5
6
create or replace procedure 过程名 (入参类型。。。)
is
	局部变量 变量类型;
begin
	执行指令;
end;

参数类型

1
2
3
IN 输入类型
OUT 输出类型
INOUT 输入输出类型
Prev:
Oracle常用命令
Next:
Nginx配置高可用集群（未完）
catalog
catalog
    •

    Copyright © 2019 - 2020 KenYang | Powered by Hexo | Theme Bamboo

    Total visits： times |