標籤：style 原理 管理 關鍵字 sda 效率 定義 sql語句 select
Oracle Database-資料處理和表的管理部分
*SQL的類型
- DML（Data Manipulation Language 資料操作語言）:select/insert/update/delete
- DDL（Data Definition Language 資料定義語言 (Data Definition Language)）:create table/alter table/truncate table/drop table
- create/drop view/sequnece/index/synonym(同義字)
- DCL（Data Control Language 資料控制語言）:grant(授權)/revoke(撤銷許可權)
DML語句（增/刪/改）INSERT語句用於插入資料
- 為每一列添加一個新值
- 按列的預設順序列出各個列的值
- 在INSERT 子句中隨意列出列名和他們的值
- 字元和日期型資料應包含在單引號中
文法使用這種文法一次只能向表中插入一條資料樣本：插入空值使用地址符來佔位具體值類似JDBC的PreparedStatement，用預留位置"?"來表示以後將要插入的值的位置在Oracle中可以使用"&"符號來指定列值，位置放在VALUES子句中通常使用與列名一樣的地址符尾碼，如：id——&id
地址符幾乎可以使用在所有SQL語句中，不僅僅是插入語句
如：從其他表中拷貝資料文法是在INSERT語句後加入子查詢
- 不需要書寫VALUES子句
- 子查詢中的值列表應該與INSERT子句中要被插入的列名相對應
- 這種方式可以一次插入多條資料
當需要插入海量資料時，這種方法效率很低，通常使用以下的方法來解決海量插入資料
- Oracle提供了資料泵（PLSQL程式）：dbms_datapump（程式包）
- SQL*Loader
- 外部表格
UPDATE語句文法可以一次更新多條資料一般使用WHERE子句來確定需要更新哪些資料在UPDATE語句中使用子查詢更新操作中的資料完整性錯誤資料的完整性就是指建立表時定義的表與表之間的
約束DELETE語句文法可以一次刪除多條資料使用WHERE子句來確定需要更新哪些資料刪除操作中的資料完整性錯誤關於DELETE和TRUNCATE的異同
- 都是刪除表中的資料
- delete是逐條刪除，truncate是先摧毀表再重建
- delete速度更快，truncate速度比delete慢
- delete是DML（資料操作語言）語句，truncate是DDL（資料定義語言 (Data Definition Language)）語句
- Delete操作可以復原rollback
- Delete操作可以閃回（Oracle特有，再事務提交後可以閃回）
- 在Oracle中，大部分操作都可以閃回，即大部分操作都是可逆的
- Delete操作可能產生片段，並且不釋放空間
- 關於片段
- 片段使表的資料條目之間不連續，影響查詢的速度
- 清理片段的方法：
注意：插入、更新和刪除操作會引起資料的的變化，必須考慮資料的完整性Oracle中的事務Oracle中事務的概念資料庫事務的組成
- 一個或多個DML語句
- 一個DDL（Data Definition Language – 資料定義語言）語句
- 一個DCL（Data Control Language – 資料控制語言）語句
資料庫事務的執行流程
- 以第一個DML語句的執行作為開始
- 以下面其中一種作為結束
- 顯示結束：commit rollback
- 隱式結束（自動認可）：DDL語言、DCL語言、exit(事務正常退出)
- 隱式復原（系統異常終了）：關閉視窗、死機或掉電等
COMMIT和ROLLBACK語句使用COMMIT和ROLLBACK語句可以
- 確保資料完整性
- 資料改變被提交之前預覽
- 將邏輯上相關的操作分組
通過SAVEPOINT控制事務
- 使用SAVEPOINT語句可以在當前事務中建立儲存點。
- 使用ROLLBACK TO SAVEPOINT語句復原到建立的儲存點
- 使用SAVEPOINT來復原可以避免出現小錯誤而復原整個事務
資料庫事務的隔離等級對於同時啟動並執行多個事務，當這些事務訪問資料庫中相同的資料時，如果沒有採取必要的隔離機制，就會導致各種
並發問題:
- 髒讀：對於兩個事物T1，T2，T1讀取了已經被T2更新但還沒有被提交的欄位。之後，若T2 復原，T1讀取的內容就是臨時且無效的
- 不可重複讀取：對於兩個事物T1，T2，T1讀取了一個欄位,然後T2更新了該欄位。之後，T1再次讀取同一個欄位，值就不同了
- 幻讀：對於兩個事物T1，T2，T1從一個表中讀取了一個欄位，然後T2在該表中插入了一些新的行。之後，如果T1再次讀取同一個表，就會多出幾行
資料庫事務的隔離性資料庫系統必須具有隔離並發運行各個事務的能力，使它們不會相互影響，避免各種並發問題一個事務與其他事務隔離的程度稱為隔離等級資料庫規定了多種事務隔離等級，不同隔離等級對應不同的幹擾程度，隔離等級越高，資料一致性就越好，但並發性越弱資料庫提供的4種交易隔離等級Oracle支援的兩種交易隔離等級（或三種）
- Oracle支援2種交易隔離等級：READ COMMITED和SERIALIZABLE（或加上READ ONLY唯讀共三種)
- Oracle預設的交易隔離等級為：READ COMMITED
Mysql支援4種交易隔離等級. Mysql 預設的事務隔離等級為: REPEATABLE READDDL語句（管理及動作表）常見的資料庫物件資料表空間資料表空間是Oracle資料庫的邏輯單元資料庫——資料表空間一個資料表空間可以於多個資料檔案（物理結構）關聯一個資料庫下可以建立多個資料表空間，一個資料表空間可以建立多個使用者，一個使用者下可以建立多張表建立資料表空間的語句使用者建立使用者的語句為使用者賦許可權語句新建立的使用者沒有任何許可權，登陸後會提示角色Oracle中存在三個主要角色
- connect角色
- resource角色
- dba角色
分別有以下許可權賦權文法建立表CREATE TABLE語句*前提：
- 使用者需要具備CREATE TABLE許可權
- 有足夠的儲存空間
文法必須指定
表名，
列名及其
資料類型和
資料類型的大小樣本：DEFAULT VALUE-預設值執行insert操作時，可以為其指定預設值文法：
- 值、運算式和SQL語句都可以作為預設值
- 其他的列名或者是偽列都是非法的
- 預設值的類型必須和該列的類型一致
利用select語句建立表select語句查詢的結果相當於一張表，我們可以將這個查詢結果變成一張表，即建立一張儲存select語句查詢結果的表這樣就可以很方便的建立新的表了文法：注意
as關鍵字樣本：拷貝現有的表結構（建立與現有表的結構相同的表）利用上述通過select語句建立查詢結果對應的表的功能：create table 新表 as select * from
where 1=2;將會拷貝表但不拷貝表中的資料同理可以在where條件中加上篩選條件拷貝需要的表資料列的資料類型關於ROWID（行地址）：
*表及列名的命名規則
- 必須以字母開頭
- 必須在1–30 個字元之間
- 必須只能包含A–Z、a–z、0–9、_、$、和#
- 必須不能和使用者定義的其他對象重名
- 必須不能是Oracle的保留字
- Oracle預設儲存是都存為大寫
- 資料庫名只能是1~8位，datalink可以是128位，和其他一些特殊字元
*使用子查詢建立表使用AS subquery選項，將建立表與插入資料結合文法：要求：
- 指定的列和子查詢中的列要一一對應
- 通過列名和預設值定義列
樣本：修改表ALTER TABLE語句使用ALTER TABLE語句可以
文法列的追加/修改/刪除/重新命名追加列的樣本：修改一個列的樣本可以修改列的資料類型，資料類型大小和預設值注意：
對預設值的修改隻影響
今後對錶的修改，此前的資料不影響刪除一個列樣本刪除表DROP TABLE語句使用DROP TABLE語句
- 資料和結構都被刪除
- 所有正在啟動並執行相關事物被提交
- 所有相關索引被刪除
- DROP TABLE 語句不能復原，但是可以閃回
文法DROP TABLE不是將表徹底刪除，只是將表放到RECYCLEBIN（資源回收筒）中可以通過加雙引號的方法查出已刪除的表資源回收筒可以被清空注意：資源回收筒並不是一定存在的，某些使用者沒有資源回收筒（如sys-管理使用者），要注意purge關鍵字可以跳過資源回收筒徹底刪除從資源回收筒恢複已刪除的表有多種方式，如閃回刪除：七種方式：其中閃回資料歸檔Oracle11G才有，其餘六種11G和10G均有詳細自查重新命名對象RENAME語句執行RENAME語句將會改變表、視圖、序列或同義字的名稱前提：必須是對象的擁有者文法清空表TRUNCATE TABLE語句使用TRUNCATE TABLE語句將
文法注意：
- TRUNCATE語句不能復原
- 可以使用DELETE語句代替TRUNCATE語句刪除資料
約束約束的概念
- 約束是表一級的限制
- 如果存在依賴關係，約束可以防止錯誤的刪除資料
- 約束的類型
- NOT NULL-非空約束
- UNIQUE-唯一約束
- PRIMARY KEY-主鍵約束
- FOREIGN KEY-外鍵約束
- CHECK-檢查性約束
約束層級在Oracle中有兩種約束層級
- 資料行層級條件約束：如果沒有特別指明，一般都是資料行層級條件約束
- 表級約束
約束規則
- 使用者可以自訂約束，也可以使用OracleServer的sys_cn格式命名約束
- 約束建立的時機：
- 約束可以定義在列一級，或者是表一級
- 通過資料字典查看約束
約束的類型NOT NULL-非空約束保證該列的值不為空白UNIQUE-唯一性限制式保證該列的值唯一PRIMARY KEY-主鍵約束通過主鍵查詢資料速度最快，因為主鍵是一個唯一性的
索引，關於原理見下文索引部分約束列的值：
FOREIGN KEY-外鍵約束與主表的主鍵有約束
- FOREIGN KEY: 在子表中，定義了一個表級的約束
- REFERENCES: 指定表和父表中的列
- ON DELETE CASCADE: 當刪除父表時，級聯刪除子表記錄（謹慎使用，比較危險）
- ON DELETE SET NULL: 將子表的相關依賴記錄的外索引值置為null，此時子表這個記錄與父表沒有聯絡了
子表的外鍵必須是父表的主鍵check-檢查性約束
- 定義每一行記錄所必須滿足的條件
- 下面的運算式可以使用在check約束中:
- 引用CURRVAL、NEXTVAL、LEVEL、和ROWNUM
- 調用SYSDATE、UID、USER和USERENV函數
- 另一個表的查詢記錄
在定義檢查性約束的時候最好定義這個檢查性約束的名稱，當不滿足約束時回顯這個名稱告知約束條件，emp_salary_min就是這個約束的名稱小結建表示例：常見的資料庫物件
視圖
- 視圖是一種虛表
- 視圖建立在已有表的基礎上，視圖賴以建立的這些表稱為基表
- 向視圖提供資料內容的語句為SELECT語句，可以將視圖理解為儲存起來的SELECT語句
- 視圖向使用者提供基表資料的另一種表現形式
視圖的優點建立視圖-基本許可權一般來講普通使用者沒有建立視圖的許可權，需要被管理員授權文法
- FORCE：子查詢不一定存在
- NOFORCE：子查詢存在（預設）
- CHECK OPTION：只操作視圖對應的資料，若操作視圖不存在的資料則報錯
- 如：視圖為10號部門的員工視圖，就不可以插入部門號為其他的員工條目了
- “只能操作看得到的”
- 不建議通過視圖對錶進行操作
- WITH READ ONLY：只能做查詢操作
子查詢可以是複雜的SELECT語句樣本：顯示視圖結構：使用樣本2：建立視圖時在子查詢為列定義別名
之後應該在選擇視圖中的列時使用別名查詢檢視文法簡單視圖和複雜視圖替換（相當於修改）視圖使用CREATE OR REPLACE VIEW子句修改視圖建立或者替換CREATE VIEW子句中各列的別名應和子查詢中各列相對應建立複雜視圖複雜視圖舉例：查詢各個部門的最低工資，最高工資，平均工資視圖中使用DML的規定注意：不建議通過視圖對
表進行操作，原因就是使用DML的規定太多了
可以在
簡單視圖中執行DML操作當視圖定義中包含以下元素之一時不能使用delete
- 組函數
- GROUP BY子句
- DISTINCT（去重）關鍵字
- ROWNUM偽列
當視圖定義中包含以下元素之一時不能使用update
- 組函數
- GROUP BY子句
- DISTINCT關鍵字
- ROWNUM偽列
- 列的定義為運算式
當視圖定義中包含以下元素之一時不能使用insert
- 組函數
- GROUP BY子句
- DISTINCT關鍵字
- ROWNUM偽列
- 列的定義為運算式
- 表中非空的列在視圖定義中未包括
屏蔽DML操作
- 可以使用WITH READ ONLY 選項屏蔽對視圖的DML操作
- 任何DML操作都會返回一個Oracle server錯誤
刪除視圖刪除視圖只是刪除視圖的定義，並不會刪除基表的資料序列序列是指可供
多個使用者用來產生
唯一數值的資料庫對象
- 自動提供唯一的數值
- 共用對象
- 主要用於提供主索引值
- 將序列值裝入記憶體中可以提高訪問效率
- *建立序列就相當於建立一個數組
Oracle中的序列從功能上來說相當於MySQL中的auto_increment一樣建立序列建立序列就相當於建立一個數組，數組長度預設為20
文法
- INCREMENT BY n：定義自增量（步長），預設為1
- START WITH n：定義起始值，預設為1
- MAXVALUE n / MINVALUE n | NOMAXVALUE / NOMINVALUE：最大值/最小值 | 無最大值/無最小值，預設為無最大最小值
- CYCLE | NOCYCLE：是否迴圈（會產生重複的值），預設為NOCYCLE
- CACHE n | NOCACHE：緩衝長度（相當於這個數組的長度），預設為20 | 無緩衝，即長度為1
樣本：
- 建立序列DEPT_DEPTID_SEQ為表DEPARTMENTS提供主鍵
- 自增量為10
- 起始值為120
- 預設無最小值
- 最大值為9999
- 不迴圈
- 不定義緩衝
查詢序列樣本查詢資料字典視圖USER_SEQUENCES擷取序列定義資訊如果指定NOCACHE選項，則列LAST_NUMBER顯示序列中下一個有效值NEXTVAL和CURRVAL偽列（相當於運算元組的指標）這個指標預設是在第1個元素的前面（即CURRVAL此時無意義）
- NEXTVAL返回序列中下一個有效值，任何使用者都可以引用
- CURRVAL中存放序列的當前值
- NEXTVAL應在CURRVAL之前指定，二者應同時有效
序列的應用樣本序列的特點
- 將序列值裝入記憶體可提高訪問效率
- 序列在下列情況下出現裂縫（不連續了）
- 如果不將序列的值裝入記憶體(NOCACHE)，可使用表USER_SEQUENCES查看序列當前的有效值
修改序列樣本修改序列的增量、最大值、最小值、迴圈選項或是否裝入記憶體修改序列的注意事項
- 修改序列的使用者必須是序列的擁有者或對序列有ALTER許可權
- 只有將來的序列值會被改變
- 改變序列的初始值只能通過刪除序列之後重建序列的方法實現
刪除序列
- 使用DROP SEQUENCE語句刪除序列
- 刪除之後，序列不能再次被引用
樣本：索引索引是用於加速資料存取的資料對象，合理地使用索引可以大大降低I/O次數，從而提高資料訪問效能
- 一種獨立於表的模式對象，可以儲存在與表不同的磁碟或資料表空間中
- 索引被刪除或損壞，不會對錶產生影響，其影響的只是查詢的速度
- 索引一旦建立，Oracle管理系統會對其進行自動維護，而且由Oracle管理系統決定何時使用索引，使用者不用在查詢語句中指定使用哪個索引
- 在刪除一個表時，所有基於該表的索引會自動被刪除
- 通過指標加速Oracle伺服器的查詢速度
- 通過快速定位元據的方法，減少磁碟I/O
- “類似一本書的目錄”
建立索引索引的類型
- 單列索引：基於單個列所建立的索引
- 如CREATE index 索引名 on 表名（列名）
- 複合索引(多級索引）：基於兩個或多個列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同
- 如：CREATE index emp_idx1 on emp(ename,job);和CREATE index emp_idx2 on emp(job,ename);
索引底層原理類型
自動建立在定義PRIMARY KEY 或UNIQUE約束後系統自動在相應的列上建立
唯一性索引手動建立使用者可以在其它列上建立非唯一的索引，以加速查詢樣本執行計畫（效率對比-不建立索引和建立索引）Cost（%CPU）越低越好建立索引的時機以下情況可以建立索引
- 列中資料值分布範圍很廣
- 列經常在WHERE子句或串連條件中出現
- 表經常被訪問而且資料量很大，訪問的資料大概占資料總量的2%到4%
以下情況不應該建立索引
- 表很小
- 列不經常作為串連條件或出現在WHERE子句中
- 查詢的資料大於2%到4%
- 表經常更新（經常更新索引對應的列）
查詢索引樣本可以使用資料字典視圖USER_INDEXES和USER_IND_COLUMNS查看索引的資訊刪除索引使用DROP INDEX命令刪除索引刪除索引UPPER_LAST_NAME_IDX只有索引的擁有者或擁有DROP ANY INDEX許可權的使用者才可以刪除索引
同義字（別名）使用同義字訪問相同的對象
文法有否PUBLIC關鍵字表示公有還是私人同義字（對於目前使用者）建立和刪除同義字需要許可權建立同義字刪除同義字
Oracle Database-資料處理和表的管理部分
