Oracle数据库

---

title: Oracle

Oracle

• 在dos窗口连接自己的数据库

sqlplus sys/orcl123@//localhost:1521/orcl as sysdba

• 数据库和数据库实例

• 完整的数据库通常包含两部分组曾：Oracle数据库和数据库实例

• 数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件）

• Oracle数据库实例是一组Oracle后台进程/线程以及在服务器分配的共享内存区。在启动Oracle数据库服务器是，实际上是在服务器的内存中创建的一个Oracle实例（即在服务器内存中分配共享内存并创建相关的后台内存），然后由这个数据库实例来访问和控制磁盘中的数据文件

• 新创建的用户只有授权之后才能用

• 为什么要新建表空间

• 从管理方面，一般为大用户指定专用的表空间，这样该用户所有表和其他数据库对象都保存在该表空间关联的物理文件上边，方便后期的维护和数据库的迁移等

• 从性能上，我们可以将多个表空间挂载到不同的磁盘，减少磁盘io竞争提升性能

• 一般伟大用户、大表的索引存放大数据的表等制定专门的表空间

• 大对象数据类型

• CLOB：最大为（4GB-1）数据库块大小，存储字符数据，主要用于存储大型英文字符

• BLOB：最大为（4GB-1）数据库块大小，存储非结构化二进制数据，主要存储图像、声音、视频等文件。

• 数据库实例，表空间，用户，表之间的关系

• 事务：数据库事务是构成单一逻辑工作单元的操作集合

• 理想状态下事务具有ACID这四个特性，而数据库是实现这些特性需要通过并发控制和日志回复

• 事务并发时常出现的问题

• 脏读：一个事务读取了另一个事务未提交的数据

  

• 不可重复读：指一个事务对同一事物的读取结果前后不一致

  • 脏读和不可重复读的区别：前者读取的是事务未提交的数据，后者读取的是已经提交的数据，只不过因为数据被其他事务修改过导致前后两次读取的结果不一致
  • 

• 幻读

  • 侧重的方面是某一次的select操作得到的结果所表征的数据状态无法支撑后续的业务操作
  • 如：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

• 通过事务隔离解决并发问题

• 理想状态：

  事务具有隔离性,理论上来说事务之间的执行不应该相互产生影响,其对数据库的影响应该和它们串行执行时一样。

• 折中实现：

  然而完全的隔离性会导致系统并发性能很低,降低对资源的利用率,因而实际上对隔离性的要求会有所放宽,这也会一定程度造成对数据库一致性要求降低

• SQL标准为事务定义了不同的隔离级别，从低到高依次是

• 读未提交（READ UNCOMMITED）

• 读已提交（READ COMMITED）

• 可重复度（REPEATABLE READ）

• 串行化（SERIALIZABLE）

• 事务的隔离级别越低，可能出现的并发异常越多，但是通常而言系统能提供的并发能力越强

• 事务隔离的实现-并发控制技术

• 并发控制技术是实现事务隔离性以及不同隔离级别的关键,实现方式有很多，比如：

  基于锁、基于时间戳、基于有效性检查、基于快照等

隔离级别与对应的并发问题：

Oracle默认采用 Read committed；

Mysql默认采用 Repeatable Read;

数据库的工作模型

• 访问数据库的客户端 ------->发送指令------------->数据库服务器端

简单的数据库查询以及对数据的操作

• 去重

  • 关键字：distanct

  • 语法结构 select distinct 字段名1，字段名2 from 表名

    select distinct manager_id from employees
    

• 排序

  • order by

  • 语法结构：select … from 表名 order by 字段名1 asc(升序)|desc(降序)，字段名2 asc|desc

    -- 需求：查询员工表中所有员工的编号，名字，工资，并根据工资做降序排序
    select employee_id,first_name,salary
    from employees
    order by salary desc
    -- 需求：查询员工表中所有员工的编号，名字，工资，并根据工资做降序排序，若工资相同，根据员工编号做升序排序
    select employee_id,first_name,salary
    from employees
    order by salary desc,employee_id asc
    

• 模糊查询

• like not like

• case when查询

  --需求：查询员工表中所有员工的编号，名字，工资，并对工资做一个分类
  select employee_id,first_name,salary,
  case
  	when salary>17000 then '高薪'
  	when salary>10000 then '中薪'
  	else '低薪'
  end as 工资等级
  from employees
  

函数

单行函数

• length(‘字符串’) -------- 计算字符串的长度

  -- 需求：计算’agasdfasdfasdf‘的长度
  select length(’agasdfasdfasdf‘) from dual
  
  -- 需求：查询员工表中所有first_name长度为4的员工信息
  select *
  from employees
  where length(first_name) = 4
  

• to_date（‘字符串类型日期’，‘日期格式’）------>将字符串类型的日期按照日期格式转换为日期格式

  日期	格式
  年	yyyy
  月	mm
  日	dd
  时	hh24
  分	mi
  秒	ss
  星期	day

• to_char(‘日期’，‘日期格式’)-------将日期按照日期格式转换为字符串类型的日期

  -- 需求：获取字符串类型的系统时间（年月日）
  select to_char(sysdate,'yyyy-mm-dd') from dual
  
  -- 需求：请得出各位同学出生那天星期几
  select to_char(to_date('2020/9/2','yyyy/mm/dd'),'day') from dual
  

组函数

• 特点 ： 对确定的表或组进行处理，每组数据只会得到一个结果

  • 若使用组函数，select后面只能跟组函数
  • where后面不能跟组函数
  • 组函数对null不做任何处理

• 分组

  • 概念：根据某一字段，对表中数据进行分组
  • 关键词：group by 字段名
  • 注意：分组过后，select后面只能跟组函数及分组依据字段
  • 语法结构： select from 表明 where group by 分组依据字段 order by
  • 执行顺序：from where group by select order by

  -- 需求：求出各个部门的平均薪资
  select department_id,avg(salary) 
  from employees 
  group by department_id
  

• 分组过滤

  • 对分组之后的组，进行筛选
  • 关键字：having 过滤条件
  • 语法结构：select … from … where … group by … having … order by …
  • 执行顺序：from where group by having select order by
  • 注意：where是对分组之前的数据进行筛选，having是对分组之后的数据进行筛选

  -- 找出所有平均工资大于8000的部门ID
  select department_id,avg(salary)
  from employees
  group by department_id
  having avg(salary)>8000
  
  -- 注意：若可以使用where也可以使用having,优先使用where
  

• SQL总结

  • 书写顺序：select from where group by having order by

  • 执行顺序：

    1. from
    2. where
    3. group by
    4. having
    5. select
    6. order by

伪列

• 概念：建表时不存在的列，但是可以查出来

rowid

• 概念：一行数据在硬盘上的物理存储地址，可以唯一标识一行数据

rownum

• 概念：为每一行查询数据做编号，从1开始，依次递增
• 注意：
  • 若使用where判断，只会对满足where条件的数据做编号
  • 若使用rownum做判断，只能用于 < <= （查询前n行） >=1（全部查询） =1（只查询第一行）
• 若想用rownum做为判断条件，需要将rownum做为实列（分页查询）

-- 需求：查询员工表中工资最高的第38名到68名
select * 
from （
	select t1.* ,rownum rn 
	from （
		select * 
		from employees 
		order by salary desc
	）t1
） 
where rn between 38 and 68

子查询

• 子查询结果为一行一列
  • 这种子查询一般用于主查询中的等值判断、不等值判断
• 子查询结果为多行一列
  • 这种子查询一般用于主查询中的枚举查询
• 子查询结果为多行多列
  • 这种子查询一般被当做临时表，供主查询使用，放在主查询from后面

多表连接

• 将多张表根据特定的连接条件，连接成一张表，以供使用

内连接

• 特点：只有两张表中的对应的数据才会放到新的表中；若一张表中存在，另一张表中没有对应的数据，直接舍弃
• inner join

外连接

• 特点：左表中的数据全部保留，若右表中没有与之对应的，补空

左外连接

• left join

右外连接

• right join

全外连接

• full join

建表

数据类型

• 数字类型

  • number(n) 整数，n为整数的长度
  • number(n,m) 小数，n为数字的总长度，m为小数部分长度，整数部分长度为n-m
    • 注意：若实际小数部分长度不足m,后面补0；若超出小数部分长度，四舍五入
  • number 注意：对数据不做任何限制，添加什么，存入什么；不推荐

• 字符串类型

  • char(n):定长字符串，n为字符串的最大长度，若不足n,则补充空白字符（空格）

    • 便于管理，但是浪费空间
    • 若不写n默认n=1

  • varchar2(n)： 可变长字符串，n为字符串的最大长度，若不足n,不补充空白字符（空格）

    注意：必须带（n）

• 补充：

  1. 一个英文字符（英文字母、英文符号）、数字占一个长度
  2. 一个中文字符（中文汉字，中文符号）
     • UTF-8 占3个长度
     • GBK 占2个长度

• 日期类型

  date 包含年月日时分秒

约束

• 主键约束 primary key (PK)

  唯一标识一行数据 非空+唯一

  应用场景：ID

• 非空约束 not null

  值不能为null

  应用场景：用户名，性别

• 唯一约束 unique

  值不能重复

  应用场景：手机号，身份证号

• 检查约束 check（限制）

  例如：

  ​ check(length(password)=6) 密码长度为6

  ​ check(sex in (‘男’,‘女’,‘其他’)) 限制sex的取值

  ​ check(email like ‘%@%.com’) 限制邮箱格式

• 外键约束 foreign key (FK)

  关键词：references

  作用：从数据的角度上为两张表建立一个联系

  语法结构： 字段名 数据类型 references 主表(字段) 例如： class_id number references class(class_id)

  注意：外键约束指向的主表的字段，必须是主键或者unique

  注意：一般的，将存在外键约束的表称为子表，外键约束指向的表，称为主表

• 先创建主表，再创建子表

  • 先添加主表，再添加子表
    • 先删除子表，再删除主表

修改表中数据

添加数据

• 语法：insert into 表名 (字段名1，字段名2) values（值1，值2）

• 注意：

  1. 值的顺序必须和表名后面的字段顺序一致
  2. 表名后面的字段名可以省略，此时默认按照建表时的字段顺序，为所有字段添加值

删除数据

• 语法：delete from 表名 [where 条件]

• 注意：删除满足条件的数据；若没有写where，默认逐行删除所有数据

• 补充：

​ delete from 表名 可以删除全表数据，但是逐行删除，效率低

​ 表截断：truncate table 表名 直接把数据部分删除，效率快

修改数据

• 语法: update 表名 set 字段1 = 新值1，字段2 = 新值2 [where 条件]

  注意：修改满足条件的行的数据；若没有写where，默认修改全表所有行的该字段的值

修改表结构

增加字段

• alter table students add (province varchar2(30));
  

删除字段

• 如果只是删除一个字段，需要加上关键字column

• --删除一个字段
  alter table students drop column province
  --同时删除两个字段
  alter table students drop (sex,age);
  

修改字段

• alter table students modify departno varchar2(4);
  

重命名表

• alter table students rename to students_a
  

序列

• 概念：Oracle自带的一个小工具，可以自动递增值

• 应用：经常用于给主键赋值

• 创建序列：create sequence 序列名 [start with 值] 默认从1开始取值

• 使用序列：

• 1. 序列名.nextval      获取序列中下一个值
  

• -- 创建序列
  create sequence t_seq start with 5
  
  select t_seq.nextval from dual -- 5
  select t_seq.nextval from dual -- 6
  select t_seq.currval from dual -- 6
  
  create sequence tt_seq 
  
  select tt_seq.nextval from dual -- 1
  select tt_seq.nextval from dual -- 2
  select tt_seq.currval from dual -- 2
  
  -- 创建学生序列
  create sequence stu_seq start with 3
  -- 向学生表中添加数据
  insert into t_stu values(stu_seq.nextval,'小明','其他',20,60,2008)
  

• 删除序列：drop sequence 序列名

视图

• 概念：将查询语句的结果当做一个临时表，即视图

• 语法：create view 视图名 as select语句

• 使用：当做表来使用，简化SQL

• 删除视图：drop view 视图名

• 对于一些“特殊的人员”屏蔽掉关键的表名，字段名

-- 创建视图
create view stu_v as select stu_name haha,stu_sex lala,stu_age xixi from t_stu
-- 查询视图
select * from stu_v
-- 删除视图
drop view stu_v

• 视图本质上只是一个select语句，执行时，依然是执行的select语句，并不会提高查询效率

• 系统在执行create view语句创建视图时，只是将视图的定义信息存入数据字典，并不会执行 其中的select语句，在对试图进行查询时，系统才会根据使徒的定义从基本表中获取数据。

• 创建只读视图

  create view stu_v as select stu_name haha,stu_sex lala,stu_age xixi from t_stu
  where stu_age = 18 with read only;
  

管理视图

• 查看视图定义
  • desc user_views;
• 修改视图定义
  • create or replace view 视图名

索引

当随着数据库中存储的数据增多，查询效率会不可避免的下降!!1000w;
可以使用索引提升查询效率！
索引就是类似图书目录的一种结构

• 概念：类似于书中目录的一种结构
• 创建索引：create index 索引名 on 表名(字段名)
• 使用索引：数据库自动使用，无需手动！
• 删除索引：drop index 索引名字
• 作用：提高查询效率

1. 索引可以提高查询效率，但也不是创建的越多越好

• 首先，索引本身也会占用硬盘空间，创建的多了，占用的空间就多了；其次，索引会提高查询效率，但同时也会降低DML（数据操作语言）操作的效率，当执行DML操作时，数据库会自动的维护索引，降低DML效率

2. 当使用创建索引的字段进行查询时，数据库才会使用该索引，从而提高查询效率

3. 应该创建索引的字段

• 经常用于查询条件的字段

• 不经常修改的字段

• 补充：主键和唯一列会自动加上索引
• 按数据存储的方式可以将索引分为
  • B树索引
  • 位图索引
  • 反向键索引
  • 基于函数的索引
• 按索引列的唯一性可分为
  • 唯一索引
  • 非唯一索引
• 按索引列的个数可以分为
  • 单列索引
  • 复合索引

事务

• 概念：DML操作的最小执行单元，多个DML操作是一个整体

• 特点：要么全部执行成功，要么全部执行失败

• 事务的开启时机：

  1. 上一个事务结束
  2. 执行DML操作

• 结束事务：

  • 成功结束事务 commit（提交），此事务中所有的DML操作，对数据的改变，都会同步到表中

    • commit
    • 正常退出客户端
    • 执行DDL操作

  • 失败结束事务 rollback（回滚），此事务中所有的DML操作，对数据的改变，都不会同步到表中，直接舍弃

    • rollback
    • 非正常退出客户端

• 事务的原理

  • 数据库为每个客户端都分配了一个临时空间(回滚段 seagment),该空间中保存着该事务对表中数据的处理结果，只有执行commit操作，才会将结果同步到表中；若执行rollback操作，则直接舍弃回滚段中的结果，不会对表中数据造成任何影响

• 事务的特性

  • 原子性：事务是一个整体的工作单元，事务对数据库的操作要么全部执行，要么全部取消，如果一条语句执行失败，所有语句全部回滚
  • 一致性：事务在完成时，必须使所有的数据都保持一致状态，无论成功或者失败，对数据的改变必须是一致的
  • 持久性：当事务提交后，对数据库所做的修改就会永久的保存下来
  • 隔离性：事物之间不可见，一个事务结束前，其他客户端不可见

触发器

• 触发器是通过触发事件来执行的，能够引起触发器运行的时间就是触发事件

• 触发事件：执行DML语句，执行DDL语句，引发数据库系统事件（系统启动或退出，产生异常错误等），引发用户事件（登陆或退出数据库操作）

• 语法：

  create or replace trigger tri_name
  

• Oracle支持的触发器类型（5种）

  • 行级触发器，当DDL语句对每一行数据进行操作时都会引起该触发器的运行

  • 语句级触发器，无论DML语句影响多少行数据，其所引起的触发器仅执行一次

  • 替换触发器，该触发器是定义在视图上的，而不是定义在表上的，他是用来替换所使用实际语句的触发器

  • 用户事件触发器

  • 系统事件触发器

SQL的分类

• DDL 数据定义语言 create alter drop

• DML 数据操作语言 insert delete update

• DQL 数据查询语言 select

• TCL 事务控制语言

表空间

通过本地化管理方式创建表空间

• -- 创建表空间语法
  create tablesapce tablespace_name
  datafile '/path/filename'
  size
  
  -- 通过本地化管理方式创建一个表空间，其扩展大小为等同的256KB
  create tablespace tbs_1
  datafile '路径'
  size 10m
  extent management local uniform size 256K;
  
  -- 通过通过本地化管理方式创建一个表空间，其扩展大小为自动管理
  create tablespace tbs_2
  datafiel '路径'
  size 10m
  extent management local autoallocate;
  

通过段管理方式创建表空间

-- 通过本地化管理方式创建一个表空间，其扩展大小为自动管理，段管理方式为手工
create tablespace tbs_2 datafiel '数据文件路径'
size 10m
extent management local autoallocate
segment space management manual;

-- 自动段空间管理方式
-- 通过本地化管理方式创建一个表空间，其扩展大小为自动管理，段管理方式为自动
create tablespace tbs_2 datafiel '数据文件路径'
size 10m
extent management local autoallocate
segment space management auto;

1. 自动段管理空间管理方式不能用于创建临时表空间和系统表空间

2. Oracle推荐使用自动表空间管理方式管理永久表空间，但其默认情况下时（manual）手工管理方式，所以在创建表空间时需要指定为AUTO

创建一个大文件表空间

create bigfile tablespace tbs_2 datafiel '数据文件路径'
size 2g;

--修改文件大小
alter tablespace tbs_2 resize 1g;

设置默认表空间

-- 将临时表空间temp_1设置为默认的临时表空间
alter database default temporary tablespace temp_1;

-- 将表空间tbs_example设置为默认的永久表空间
alter database default tablespace tbs_example;

更改表空间的状态

• 满足条件
  • 该表空间必须是online状态
  • 该表空间不能包含任何回滚段
  • 改表空间不能再任何归档模式下

-- 修改为只读状态
alter tablespace tbs_1 read only;
-- 修改为可读状态
alter tablespace tbs_2 read write;
-- 重命名表空间
alter tablespace tbs_3 rename tbs_4 new;

--删除表空间
drop tablespace tbs_1
including contents
cascade constraints;

撤销表空间的作用

• 使读写一致
• 可以回退事务
• 事务恢复
• 闪回操作

临时表空间

create temporary tablespace temp_01 tempfile '数据文件路径' size 300m;

创建用户

创建用户

• create user east identified by 123456	-- 创建用户east，密码 123456
  default tablespace users 				--默认表空间为users
  temproary tablespace temp				--默认表空间是temp的用户
  quota 10m on tbsp_1;					--该用户在tbsp_1上最多可使用的大小为10m
  

修改用户

• 将create改为index

删除用户

-- 级联删除，将df用户用户拥有的对象一起删除
drop user df cascade

用户权限管理

• 关键字：grant

• conn system/123456;
  grant connect,resource to east;	--将connect,resource权限授予east
  

lespace tbs_3 rename tbs_4 new;

–删除表空间
drop tablespace tbs_1
including contents
cascade constraints;

#### 撤销表空间的作用

* 使读写一致
* 可以回退事务
* 事务恢复
* 闪回操作

#### 临时表空间

~~~sql
create temporary tablespace temp_01 tempfile '数据文件路径' size 300m;

创建用户

创建用户

• create user east identified by 123456	-- 创建用户east，密码 123456
  default tablespace users 				--默认表空间为users
  temproary tablespace temp				--默认表空间是temp的用户
  quota 10m on tbsp_1;					--该用户在tbsp_1上最多可使用的大小为10m
  

修改用户

• 将create改为index

删除用户

-- 级联删除，将df用户用户拥有的对象一起删除
drop user df cascade

用户权限管理

• 关键字：grant

• conn system/123456;
  grant connect,resource to east;	--将connect,resource权限授予east