Oracle数据库基础学习

Oracle数据库基础学习

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

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

安装教程.

---

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 则成立！

日期时间数据类型

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笔记~

不够详细, 太多了不好发… Sql语句大致都是通用的… 但也有少部分区别
oracle 中没有了数据库的概念, 所以说上面对数据库的操作, 可以忽略…

Oracle 中的伪列：

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

oracle数据库的表中的每一行数据都有一个唯一的标识符，或者称为 rowid
在oracle内部通常就是使用它来访问数据的。rowid需要 10个字节的存储空间，并用18个字符来显示。
用途：

1. 快速访问表中某行数据 2,显示表中行是如何存储的 3.可以作为表中行的唯一标识符~

ROWID格式：

ROWNUM

是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数 (分页查询)
注意 使用伪列ROWNUM做条件查询：

-- 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　常用的函数有： 单行函数 聚合函数 转换函数 …

转换函数

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

函数	功能
TO_CHAR()	将 一种数据类型 转换为字符串类型;
TO_DATE()	将 char 或 varchar类型 转换为时间类型
TO_NUMBER()	将 数字的字符类型 转换为数值类型;

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还提供一些单行函数 一般称为 “其它函数”;

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

-- 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排序;

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

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;