oracle学习笔记

理论

表空间 tablespace
表空间是数据库的最大逻辑存储结构，和物理数据文件相对应，一个表空间可以由多个数据文件组成，一个数据文件只能属于一个表空间 主要的几个表空间：
SYSTEM: 系统表空间，用于存放内部表和数据字段的数据，如表名、列名、用户名。 SYSAUX: 系统辅助表空间，由ORACLE自行维护
UNDO： 存放撤销信息的表空间 USERS; 建议用户使用的表空间，scott用户的表就在这个表空间上
建议：不同应用系统存放在不同的表空间上，不同的表空间的文件放在不同的磁盘上，减少并发，提高性能。

段 一个独立的逻辑存储结构，用于存储表、索引或簇等占用空间的对象，一个段只能属于一个数据库对象 有以下几种段： 数据段
数据段存放的是数据表中的数据，数据段的增大时向数据段增加区，创建表时，系统自动创建一个同名的段 索引段
包含了用于提高系统性能的索引，一旦建立索引，系统自动创建一个同名的索引段 回滚段
也叫撤销段，该段保存了回滚的条目，oracle将修改前的旧数据存放在回滚段中，利用这个，可以撤销未提交的操作 临时段
当执行创建索引、查询等操作时，系统会使用一些临时空间用来保存解析过的查询语句以及在排序过程中产生的临时数据 区
段空间分配时最小的单位，由连续的数据块组成 块 组成区的最小单位，通常是操作系统数据块的整数倍，ORACLE支持同时使用多种大小
引用

结构图

事务

索引

普通索引

• create index 索引名 on 表名(字段名)

唯一索引（主键就是唯一索引）

• create unique index 索引名 on 表名(字段名)

复合索引

• create index 索引名 on 表名(字段名,字段名…)
• 注意创建复合索引时 字段顺序应该与出现的频率一致 符合最左前缀法则

反向键索引

• create index 索引名 on 表名(字段名) reserve
• 当键值基本有序时，导致会成为歪脖子树（都偏向一边），树层数增加，导致检索效率变低
  使用反向索引打乱顺序（将key转为2进制，再反转），提高效率

位图索引

• create bitmap 索引名 on 表名(字段名)
  

实践

创建过程

创建表空间

 create tablespace 表空间名 
 datafile 'c:/文件存放的地址' 
size 100m初识话数据大小
 autoextend on （自动扩容） 
 next 10m扩容大小

创建用户及其赋权

create user wateruser
identified by  6666
default waterboos
grant dba to wateruser

表的创建修改删除

修改

与mysql基本一致 这里只描述一下修改表

• 增加一列

  	alter table user
   	 add(
   	 age number(3,0)
   	 )
  

  

• 修改一列

  alter table user 
  modify(
  	age number(2.0)
  ) 
  

• 改名

  alter table user
   	rename column
   	age to agee
  

  

删除

• 删除字段

  alter table user drop agee 
  

• 删除表

   drop table user
  

  

date及其相关函数

ORACLE默认日期格式为’日-月-年’,例如’17-3月-2022’

1. 将字符串转为时间的函数 to_data(string_value , date_format)
   insert into student values(‘mark’,to_date(‘21-08-2003
   20:28:03’,’DD-MM-YYYY HH24:MI:SS’))
2. 将日期转为字符串 to_char（date_value,date_format）
3. 支持对日期加减
   date+1 加一天

伪列的查询

基础语句 扩充

常用的和mysql基本一致 可以参考此篇文章

1. Fetch子句 相当于Limit返回规定的行数

   [ OFFSET offset ROWS]
    FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ] 
   

   • offset 起始的行数 默认为0
   • row_count 返回的行数
   • percent Rercent 按百分比返回的行数
   • only 上面说返回10行 就是10行
   • with ties 要配合order by使用 会多返回与最后一行相同的几行

   SELECT S_SCORE
   from STUDENT
   ORDER BY S_SCORE DESC
   offset 0
   fetch next 10 rows with ties
   

2. 伪列查询
   每行数据都有伪列
   • rowid 每一行的物理id
   • rownum 查询之后的行号
3. union(两个集合之间不重复的记录，重复的只保存一条，默认按照字母顺序排序)；
   union all（将两个集合的结果进行合并，不会剔除重复的，也不进行排序）
   intersect（两个集合之间的交集）
   minus（只返回第一个集合中存在，第二个中不存在的记录）
   以上四种运算的优先级相同，总是按照自左至右的顺序依次进行运算。
4. …for update能够实现手动的去修改列中的值。
5. oracle中常用的数据类型：字符型（character）,数值型（number）,日期型（date）和大对象型（LOB）。
6. 字符型：
   char(n):固定长度字符串，不足则自动补空格，超过n则报错。
   varchar(n):可变长度的字符串类型，不足也不会空格填充，n代表的是字符串的最大长度，最大长度不大于4000。
   varchar2(n):类似varchar(n)，但是这个具有更多的向后兼容性。
7. lpad(‘1’,4,‘0’)(0001)左补齐，rpad(‘1’,4,‘0’)(1000)右补齐
   lower()返回小写，upper()返回大写形式，
   initcap(‘like’):Like函数将单词转换为首字符大写、其它字符小写的形式,注意：对于非单词的字符都被视为单词的分隔符，这些字符原样输出，其他所有单词的首字母都变为 大写，比如：
   initcap(‘you 2 love’)：You 2 Love。
8. substr(‘12345678’,3,4);截取字符串3456。
9. instr(string,sub_string[,start_index][,times])返回子字符串在父字符串中出现的位置,string为父字符串，sub_string为子字符串；[]中的为可选参数，start_index为可选的参数，指定搜寻的起始位置，times为可选参数，表示第几次获得子字符串。
10. ltrim()函数用于删除字符串的左侧空格，rtrim同理；
11. trim()函数删除字符串两侧的空格；
12. concat(‘hello’,’ world’)将字符串进行连接;

PLSOL

基本语法 与变量声明

语法

变量声明

将数据库的值读给变量

• select 字段名 into 变量名 from 表

属性类型

• 引用型： 变量的类型 引用数据库某个变量的类型
  变量名 表.字段%type；

• 记录型：将某一行全部字段给予一个变量
  变量名 表%rowtype;

异常

基本语法

常用的异常类型

语句

条件语句

• 单个判断

 if 条件 
 	 then 结果
 	 end if;

• 双层判断

 if 条件 
 	 then 结果
 	 else 结果
 	 end if;

• 多层判断

if 条件
	then 结果
	elsif then 结果 
	elsif then 结果 
	else 结果
	end if;

循环

• loop循环

  while 条件
  loop
  循环体
  end loop;
  

  

• for循环

  for 变量 in begin end
  loop
  循环体
  end loop
  

  

游标

游标就类似java里的集合

结构

示例

for游标

存储函数（略）

自定义oracle函数 一般用于查询

结构

存储过程

与存储函数 相似 但不能用于select语句，一般是用于业务处理，被外部如java调用

结构

注 截图 及部分内容来源于黑马程序员教程