Oracle数据库基础学习

古城微笑少年丶 2022-12-13 04:39 274阅读 0赞

# Oracle数据库基础学习 #

个人之前就学习过 mysql sqlserver 但笔记没有在CSDN上可能会有些跳跃~  
[不明确地方可以看这位大佬的笔记~不是我的][Link 1].

## Oracle 简介 ##

> **`OracleDatabase`** 又名Oracle RDBMS , 是甲骨文公司的一款关系数据库管理系统。(世界上最好的数据库系统；(历史悠久))  
> **`优点：`**  
> 支持多用户的高性能的事务处理 ，在保持数据安全性完整性方面 性能卓越~ ，支持分布式数据库和分布处理  
> 具有可移植性,Oracle 可以在 window Linux 等多个操作系统上使用~ (SqlServer微软的数据库只能在window上使用！)
> 
> **`缺点:`** 收费! 服务收费… 后期的安装维护也是收费的… 中小型公司一般不会使用~  
> **`RDBMS`：关系型数据库.**  
> 多个表数据之同存在着关系 , 在这些表上的数据操作依赖于关系 (关系用来描述多个表之间的数据依存,包括了一对一 、一对多 、多对多的关系)  
> 这些关系在 Oracle数据库中表现为主键、外键这些约東条件…  
> RDBMS就是一个建立在这些关系模型基础上的 , Oracle从7.3的版本就完全支持关系型数据库  
> Mysql SqlServer db2 Oracle 都属于关系型数据库;

## Oracle基本概念： ##

Oracle 的概念：个人刚开始学的时候有点难~  
主要是因为学习过Mysql SqlServer 后, 与Oracle 有很多不同概念理论, 与之前有不同; 建议自废武功！不要想其它的数据库在学习;

### Oracle数据库 ###

> `oracle数据库` 包含 `逻辑结构` 和 `物理结构`  
> 物理结构 是指数据库是物理存储数据的集合包括： 数据文件ORA或者DBF，控制文件，联机文件，日志文件，参数文件…  
> 逻辑结构 是指描述数据组织方式的 一组逻辑概念及它们之间的关系;  
> **oracle数据库的概念和其他数据库概念有些不一样：**  
> mysql数据库创建数据库的过程是–创建库–创建表  
> 而oracle创建数据库的过程是–创建一个表空间–创建一个用户–由用户去创建/管理 表。所以oracle数据库和其他数据库是有不同之处的。  
> 可以这样理解，oracle是一个大的数据库，由用户来管理的。  
> (所以使用时候必须首先创建数据库, 才能使用Oracle ; 可以在安装时候同时创建数据库~ )  
> (一般在完成Oracle数据库安装并创建Oracle 实例后, 就会自动建立多个 表空间 系统表 system…用户) 不同的用户就可以理解为不同的库！

### 全局数据库名 ###

> 全局数据库名 是用于区分一个数据库的标识，在安装数据库 创建数据库 … 需要使用的。  
> 它由： **数据库名称**\+**域名** 构成类似于网络中的域名; 使数据库的命名在整个网络环境中唯一 。  
> **数据库名称：**  
> 数据库名就是一个数据库的标识(唯一)，在数据库安装或创建完成之后声明; (就是安装数据库时候的 起的名字~ )  
> (不建议修改会很繁琐, 因为，数据库名还被写入控制文件中，控制文件是以二进制型式存储的, 用户无法修改控制文件的内容。)  
> 假设用户修改了参数文件中的数据库名，但是在Oracle启动时。  
> 由于参数文件中的DB\_NAME与控制文件中的数据库名不一致，导致数据库启动失败，将返回`ORA-01103`错误。  
> `查询当前数据库名 :` select name from v$database; (建议system登录状态;)  
> **域名：**  
> 在分布式数据库系统中，不同版本的数据库服务器之间，不论运行的操作系统是unix或是windows  
> 各服务器之间都可以通过数据库链路进行远程复制，数据库域名主要用于分布式环境中的复制。  
> `什么时候使用域 :` 1.在oracle分布式环境中，两个数据库之间要通过数据库链路进行数据的远程传输 2.在同一网络环境下，两个数据库的数据库名相同
> 
> `查询数据库的域名 :` select value from v$parameter where name = ‘db\_domain’; (建议system登录状态;)

### 数据库实例 ###

> 每个启动的数据库都对应一个数据库实例，通过实例来访问操作数据库中数据；  
> 如果把 数据库 理解为, 硬盘上的文件(应用程序)  
> 数据库实例就是, 内存共享运行状态一组服务器后台进程; (一个数据库可以有多个实例)

### 表空间`tablespace` ###

> 每个Oracle数据库都是由 多个表空间构成的; (用户建立的数据库也是存储到对应的 表空间中)  
> 一个表空间可以由多个 数据文件组成, 但一个数据文件只能属于一个 表空间; (表空间 又属于数据库)  
> 每个数据库都有一个 SYSTEM 的系统表空间…SYSAUX TEMP … 都是创建数据库时自动创建的。  
> 管理员可以创建自定义的表空间 并分配给指定的用户 也可也为表空间增加 删除数据文件;

### 数据文件 `.dbf、.ora` ###

> 通常文件扩展名 `.dbf` 是用于存储数据库数据的文件; ( 数据库表的 记录,索引,视图… )  
> 一个数据库文件可能存储很多表的数据, 而一个 表的数据可能来源于很多 数据文件; (不存在一对一关系)

### 控制文件 `.ctl` ###

> 控制文件的扩展名 `.ctl` 是一个二进制的文件; 控制文件存储的信息有很多: 数据文件 和 日志文件的位置和名字  
> (是数据库启动和运行所必须的文件Oracle 读写数据,就是根据控制文件来查找对应的文件, 因此一个数据库至少有一个控制文件;) Oracle11g默认三个；

### 日志文件 `.log` ###

> 日志文件扩展名是 `.log` 它记录了数据所有更改信息,并提供了一种数据恢复机制; 确保系统崩溃 或其它意外重新恢复数据；  
> 日志文件是成组使用的, 每个日志文件组 有一个或多个日志文件,在工作过程中多个组循环使用.(一个组写满了换其它组~)

### 模式和模式对象 ###

> 模式是数据库对象的集合( 表 索引…) ；  
> Oracle 为每一个数据库用户创建一个模式, 次模式为当前用户所拥有。和用户具有相同的名称~

--------------------

## 安装Oracle ##

[安装教程][Link 2].

--------------------

## Oacle 数据类型 ##

### 字符数据类型 ###

**char(size)**

> 指定长字符数据(不指定情况下默认1字节), 列长度 1~2000字节 ;  
> 如果输入字节数小于指定的字节数, 空格填补； 大于指定字节报错~

**varchar2(size)**

> 可变长字符数据 , 列长度 1~4000字节  
> 在定义字符类型时候指定其大小，但是在实际赋值时候会根据输入字节数而变化；  
> 输入字节数小于定义字节数, 不会以空格替代而是变成字节数小的 (节省内存) 如果输入字节数大于指定字节数则还是会报错!

**nchar(size)**

> nchar 及中国字符集以 Unicode 存储方式;  
> nchar 和 char 区别就在于存储方式不一致, char (1) 和 nchar(1) 字段长度为 1字节 和 1字符(等于2字节);  
> 分别传入 a 是没有问题的, 但占用字节分别是 1 和 2 , 如果都插入中文字符 啊 char(1) 是插不进去的;

**nvarchar2(size)**

> 存储方式和 nchar 类似Unicode ; 在这个基础上又和 varchar2 类似, 可变字符长度…

> 扩:  
> nvarchar2 和 varchar2 在mysql 中并没有所以刚开始学习时候有一点不习惯…  
> nvarchar2 和 varchar2 是在 nvarchar和 varchar 基础上进行了优化，Oracle也又nvarchar 和 varchar 数据类型;  
> 但底层还是nvarchar2 和 varchar2 所以最后执行时候还是 2 为了效率建议使用 2的；

### 数值数据类型 ###

**number(p,s)**

> 可变长数值数据: number可以存储 正数 负数 零 定点数 浮点数…  
> p 表示精度1~38之间( 数指从左往右算 不为0 不计 负 小数点的有效数)  
> s 为范围 -84~127之间 表示小数点右边数字数 可以是0; (对于不要的小数遵循 四舍五入原则)  
> 最后得到的值有效位 ≤ p 则成立！  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center]

### 日期时间数据类型 ###

**Date**

> 日期型数据 和mysql 大致一样, Oracle中提供 SYSDATE函数返回当前日期和时间;

**TIMESTAMP**

> 存储 年月日时分秒 , SYSTIMESTAMP函数功能是返回当前日期 时间 和 时区；

### LOB数据类型 ###

> LOB 又称 ‘大对象’ 通俗易懂就是 一个特别大的对象, 可以存储多达4GB 的非结构化信息 如声音 剪辑 视频剪辑等…  
> LOB 允许对数据进行 高效 随机 分段的访问, LOB可以是外部的 也可以是内部的这取决于它相对于数据库的位置;  
> 可以通过 PL/SQL 中提供的程序包 DBMS——LOB 完成。一个表中可以有多个列被定义为 LOB数据类型;  
> LOB数据类型有：CLOB BLOB BFILE NCLOB

**clob**

> 能够存储大量字符数据，最大可达到4G, 可以存储单字符数据 和 多字符数据；(XML文档 新闻 内容介绍等含有大量内容的文档)

**blob**

> 二进制数据，最大可达到4G (图片，视频，声音)

**bfile**

> 能够将二进制文件, 存储在数据库外部的操作系统文件中, BFILE 列存储一个BFILE 定位器, 指向位于服务器文件系统上的二进制文件(最大4GB)

**nclob**

> 用于存储大量的 nchar 字符数据, nclog 支持同时 固定宽度字符 变宽字符(Unicode字符数据)

--------------------

终于结束，大量的理论…  
到了sql 语句，建议有点基础的看…

## Oracle SQl语句： ##

Sql 语言，是针对数据库而言的 一门语言，它可以 创建数据库，表，视图 ，增删改查……数据库的一系列操作；（不区分大小写）  
**全称 “结构化查询语言”；**

> SQL 组成：  
> a. DML(数据库操作语言) ：用来 增删改 表中数据 eg: INSERT , DELETE ,UPDATE  
> b. DDL(数据库定义语言) ：在 数据库中 创建或 删除数据库对象等操作 eg: CERATE,DROP,ALTER  
> c. DQL(数据库查询语言) ：用来 对 数据库中 数据进行 查询 eg: SELECT  
> d. DCL(数据库控制语言) ：用来 控制数据库组件 存钱许可 存取权限 eg:GRANT，REVOKE

**奉上OneNote笔记~**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 1]  
不够详细, 太多了不好发… Sql语句大致都是通用的… 但也有少部分区别  
oracle 中没有了数据库的概念, 所以说上面对数据库的操作, 可以忽略…

### Oracle 中的伪列： ###

伪列就像是Oracle 表中的一个列(Mysql没有~) 每个表都有的~  
伪列可以从表中查询, 不可以修改 删除 新增…暂时只介绍 ROWID ROWNUM  
**ROWID**

> oracle数据库的表中的每一行数据都有一个唯一的标识符，或者称为 rowid  
> 在oracle内部通常就是使用它来访问数据的。rowid需要 10个字节的存储空间，并用18个字符来显示。  
> 用途：
> 
> 1.  快速访问表中某行数据 2,显示表中行是如何存储的 3.可以作为表中行的唯一标识符~
> 
> ROWID格式：  
> ![在这里插入图片描述][20201015153430313.png_pic_center]

**ROWNUM**

> 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数 (分页查询)  
> 注意 使用伪列ROWNUM做条件查询：  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 2]

-- scott 用户下,随便查看一个系统表,及伪列 rowid rownum
    select e.*,rowid ,rownum  from emp e;

### CREATE TABLE命令 ###

Qracle创建表与Mysql 有些不同因为由用户管理表~

/* 多行注释 */
    
    --单行注释
    CREATE TABLE [用户.]表名(
    	列 类型[ 属性，约束，索引，注释 ],
    )
    
    -- [] 为可选选项, 用户可选,不指定情况下默认当前登录用户~

利用现有表创建新表

CREATE TABLE 新表
    AS
    SELECT * FROM 旧表;
    -- 创建一个新表 和 旧表一模一样包括数据(可以指定列,来改变创建表的列...)
    -- 一般用于：日表 (有些项目,非常大 每天会有非常多的数据, 如果都放在一个表中时间久了就会异常大越来越多 不利于备份数据..
    -- 而对于日表 每天都有 其表结构是一定每天都一样的！
    CREATE TABLE 新表
    AS
    SELECT * from 旧表 where 1=2; 	-- 加上条件使没有符合条件的数据赋值给新表,新表就是一个只有表结构的表了~

### 事务操作语句 TCL ###

在Oracle 中事务控制语句TCL 主要由：

> COMMIT ： 提交事务，即把事务中对数据库修改操作进行永久保存~  
> ROLLBACK ： 回滚事务，取消对数据库所做的任何修改;  
> SAVEPOINT ：在事务中创建**存储点** 相当于单击游戏的存档 存档点~  
> ROLLBACK TO <存储点> ：将事务回滚到指定的**存储点** 相当于, 单击游戏的回档！

--------------------

## Oracle Sql操作符 ##

**算术操作符** 加减乘除 ±\*/ …  
**比较操作符** = != < <= > >= BETWEEN~AND(在…之间） IS NULL(是否null) LIKE(模糊查询) IN(范围查询)  
**逻辑操作符** AND(与) OR(或) NOT(非)  
**集合操作符**

> 将两个查询结果组合成一个结果集：(高中数学的集合概念~)  
> 使用前需要遵循一些规则： 1.两个查询的表 列数,类型要匹配兼容对应… 2.这种查询操作不适合LOG类型的数据, 最后展示的列标题以第一个select 为准；  
> UNION（并集：将两个查询结果集合并起来,并删除重复的行 ）  
> UNION ALL（并集所有：将两个查询结果组合在一起,包括重复数据）  
> INTERSECT（交集：只返回两个表都有的相同数据）  
> MINUS（减集：在第一个查询结果中, 排除第二个查询结果中出现的行 eg: 表1数据A B C 表2数据A 减集就是B C ）

## 连接操作符 || ##

> Oracle 中使用 || 符合进行拼接字符串操作类似于 Mysql中的 + 号;  
> 实例： scott登录~  
> select EName || Job from emp; （对 EName列 和 Job 列拼接）

-- 以dept表举例
    select * from dept 
     
    -- 事务操作语句 TCL
    -- 新增
    insert into dept values (50,'开发','');
    insert into dept values (60,'测试','');
    savepoint a ;                             -- 设置存储点;
    insert into dept values (70,'人事','');   -- 新增
    rollback to a;                            -- 回滚到存储点;
    commit;                                   -- 提交事务;
       
    -- 模拟分页 5~6条记录;
    select * from (
           select d.*, rownum as r  from dept d   
    ) dd where r>4 and r<=6

## SQL函数 ##

> 函数,通俗易懂用于完成某种特定操作 Oracle对这种操作进行了实现, 就像Java的方法;  
> Oracle　常用的函数有： 单行函数 聚合函数 转换函数 …

### 转换函数 ###

将指以 一种数类型 ——> 转换为 ——> 另一种数据类型的操作;

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TO_CHAR()</td> 
   <td>将 一种数据类型 转换为字符串类型;</td> 
  </tr> 
  <tr> 
   <td>TO_DATE()</td> 
   <td>将 char 或 varchar类型 转换为时间类型</td> 
  </tr> 
  <tr> 
   <td>TO_NUMBER()</td> 
   <td>将 数字的字符类型 转换为数值类型;</td> 
  </tr> 
 </tbody> 
</table>

**TO\_CHAR()**

> 语法  
> TO\_CHAR( d|n , \[,fmt\] );  
> 参数, d 或 n 可选\[,fmt \]; d表示日期类型数据, n 就是数字类型数据；可选\[,fmt\] 是指参数的格式类型；

**TO\_DATE( char,\[fmt\] )**  
**TO\_NUMBER( char|varchar )**

select * from dual
    -- TO_CHAR(d|n,[,fmt]);
    
    -- dual 提供的最小的工作表,任何用户都可以访问; 只有一行一列，具有某bai些特殊功用。常用来通过select语句计算常数表du达式。
    -- sysdate 获取当前系统时间;
    -- fmMM fmDD: fm是指把 01 09这种,使用空格或者0填充给替换调不需要！
    SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;  -- 将当前时间转换成字符类型 年月日时分秒~;
    
    select TO_CHAR(sysdate,'MM') FROM dual;                                   -- 只获取当前月份！
    
    -- 9:表示数值的格式 S:表示 + - 数符号 
    select To_CHAR(123,'$999.99S') FROM dual;                                 -- 把数字123 转换为字符 $123.00 加$符号字符串;
    
    -- B:如果数值是给小数整数部分是0 就显示为空格;
    select TO_CHAR(0.44,'B999.9') FROM dual;                                  
    -- ...还有很多不一一举例;
    
    
    -- TO_DATE( char,[fmt] )
    
    -- 将字符类型 转换成 时间格式(就可以参与比较~但Oracle以及对字符进行隐式转换了..); fm去0！
    select TO_DATE('2000-09-09','yyyy-fmmm-fmdd') FROM dual;
    
    -- TO_NUMBER( char|varchar )
    
    -- 将字符类型 转换成 数值格式(就可以参与运算了~ Oracle貌似已将进行隐式转换了..) 
    select  SQRT(To_NUMBER('100')) FROM dual;                                 -- 将100字符转换成数值,并 sqrt(); 获取其平方根;
    -- 10*10=100;

### 其它函数： ###

除了, 日期函数，数字函数，转换函数，Oracle还提供一些单行函数 一般称为 “其它函数”;

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NVL( expl1, expl2 )</td> 
   <td>如果expl1 值为null 则返回 expl2的值; 不为null 返回expl1值;</td> 
  </tr> 
  <tr> 
   <td>NVL2( expl1, expl2, expl3 )</td> 
   <td>如果expl1 值为null 则返回 expl3的值; 不为null 返回expl2值;</td> 
  </tr> 
  <tr> 
   <td>DECODE( value, if1, then1, if2, then2, …else )</td> 
   <td>如果value 值为 if1 返回if1 值；value 值为 if2 返回if2 值… 没有符合返回else;</td> 
  </tr> 
 </tbody> 
</table>

-- NVL( expl1, expl2 )
    select nvl(null,1) from dual;
    
    -- NVL2( expl1, expl2, expl3 )
    select nvl2(null,1,2) from dual;
    
    -- DECODE( value, if1, then1, if2, then2, …else )
    select decode(
           1,         -- value 
           1 ,1,      -- if1 ,then1
           2,2,       -- if2 ,then2,
           3          -- else
    ) from dual;
    
    --实战: scott用户下emp表；
    select * from emp;
    
    -- 查询emp表: ename sal comm
    select ename , sal , comm  ,
        sal + nvl(comm,0) as s1 ,                     -- 如果comm列为null 就是0,与sal加;
        nvl2(comm , sal+comm , sal ) as s2 ,          -- 如果comm列为null 就sal 不是null就sal+comm;
        to_char(hiredate,'mm') as m,                  -- 获取入职的月份;
        decode(to_char(hiredate,'mm'),'01','一月',    -- 根据月份判断,并输出字符...
                                      '02','二月',
                                      '03','三月',
                                      '04','四月',
                                      '05','五月',
                                      '06','六月',
                                      '下半年'            
        ) as mm,
     from emp

### 分析函数： ###

Oracle 8.1.6 版本提供分析函数, 分析函数是对一组查询结果进行运算的, 然后获得结果…  
**语法：**

> 函数名(\[ 参数\]) OVER( \[ 分区子句 \] \[ 排序子句\])  
> 函数名：分析函数名字  
> 参数：函数需要传入的参数  
> 分区子句（PARTITION BY 列）：将查询结果分为不同的组，功能类似于GROUP BY语句  
> 排序子句（ORDER BY asc|desc）：将每个分区进行排序

**常见的函数：**

– 根据deptno 分组, sal排序;

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RANK</td> 
   <td>具有相等值的行排位相同，序数随后跳跃,：<code>1~3.. 如果,1记录与2记录相同则序号都是 1,不同的数据序号是3</code></td> 
  </tr> 
  <tr> 
   <td>DENSE_RANK</td> 
   <td>具有相等值的行排位相同，序号是连续的：<code>不会因为相同的记录跳跃排序,会出现多个相同的序号</code></td> 
  </tr> 
  <tr> 
   <td>ROW_NUMBER</td> 
   <td>返回连续的排位，不论值是否相等</td> 
  </tr> 
 </tbody> 
</table>

`EMP表是 Scott用户下都有的一个系统测试表! 放心使用~`

-- 分析函数
    -- emp表,以deptno 列分析~
    select 
    e.*,   
    -- 根据deptno 分组, sal排序;
    rank()       over(partition by deptno order by sal desc ) as r1,    -- 具有相等值的行排位相同，序数随后跳跃
    dense_rank() over(partition by deptno order by sal desc ) as r2 ,   -- 具有相等值的行排位相同，序号是连续的
    row_number() over(partition by deptno order by sal desc ) as r3     -- 返回连续的排位，不论值是否相等
    from emp e;

[Link 1]: https://gitee.com/zzwuweijun/learning_documents/blob/master/java%E5%BC%80%E5%8F%91/19-Oracle/Oracle01-%E5%85%A5%E9%97%A8.md#4%E6%95%B0%E6%8D%AE%E5%BA%93%E6%96%87%E4%BB%B6%E4%B8%8E%E5%AD%98%E5%82%A8/
[Link 2]: https://blog.csdn.net/qq_45542380/article/details/109149627/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/f00bd5c1b02d477dac3aa6adda0e65d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/1ce3d7e3cdee48208fe22d155d4ac362.png
[20201015153430313.png_pic_center]: /images/20221123/3f9480f9b73e4f0aa88571d0a64efae5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTQyMzgw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/f474a5e73faf49c7b807344a12df00ef.png