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個字符來顯示。
用途：

快速訪問表中某行數據 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 ,then12,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;

總結

以上是生活随笔為你收集整理的Oracle数据库基础学习的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。