面試匯總（六）--Oracle資料庫基礎知識匯總

Oracle資料庫基礎知識匯總

1.一個執行個體對應一個資料庫

2.模式

3.資料表空間(段>區>塊)提高管理效率

從使用者的角度看，一個庫中有若干模式（模式唯一對應同名使用者），一個模式下有表、視圖和預存程序等資料庫物件。

從儲存的角度看，一個庫中有若干資料表空間，負責儲存資料庫中的各種對象。

注意： 一個模式下的表可以儲存於不同的資料表空間中。

         儲存於同一資料表空間中的表可以在不同的模式下。

對資料庫訪問使用模式概念，對資料存放區管理使用資料表空間概念。

 

資料表空間

資料表空間：邏輯結構，由多個資料檔案組成，

           從物理上講，表是存在於資料檔案裡

           從邏輯上講，表是存在於資料表空間

/**

(oracle資料庫邏輯結構上包括資料表空間、段、區和塊，物理上由資料檔案組成，一個資料表空間包含一個或多個資料檔案。)

資料表空間用於從邏輯上組織資料庫的資料，通過資料表空間可以控制資料庫佔用的磁碟空間，同時dba可以將不同的資料類型部署到不同的位置，這樣有利於提高I/O效能，同時利於備份和恢複等操作。

**/

建立資料表空間一般由特權使用者或dba角色來執行，需要有許可權

 

4.授權使用grant,收回許可權使用 revoke (解鎖賬戶--使用profile系統管理使用者)

grant connect to xiaoming (with admin option )

revoke select on emp from xiaoming;
alter user tea  account unlock; 

概述：profile是口令限制，資源限制的命令集合，當建立資料庫的，oracle會自動建立名稱為default的profile。當建立使用者沒有指定profile選項，那麼oracle就會將default分配給使用者。

 

5.實施步驟:

   a.建立使用者

    create user user1 identified by u123 default tablespace aa;(密碼必須以字母開頭)

   b.為使用者指派connect許可權

    grant connect  to user1;

   c.分配資料表空間使用許可權   

    grant unlimited tablespace to user1;

 

===============================================================

修改密碼：

如果給自己修改密碼可以直接使用
password 使用者名稱
如果給別人修改密碼則需要具有dba的許可權，或是擁有alter user的系統許可權
SQL> alter user 使用者名稱 identified by 新密碼

sys使用者是超級使用者，該使用者預設的密碼是change_on_install

system使用者是管理操作員，預設的密碼是manager

sys和system這兩個使用者最大的區別是在於有沒有create database的許可權

================================================

*如何取消重複行DISTINCT

*使用列的別名： ...............as .....

*如何處理null值,使用nvl函數來處理

*如何連接字串（||）

*如何使用like操作符
%：表示0到多個字元  _：表示任意單個字元

*資料分組 ——max，min， avg， sum， count

*如果要刪除的使用者，已經建立了表，那麼就需要在刪除的時候帶一個參數cascade;

 

6.insert  update   delete/truncate    drop

 

7.函數：日期 ,數學，字串函數，系統函數 decode

（1）日期函數

     預設情況下日期格式是dd-mon月-yy 即12-7月-78
     sysdate: 該函數返回系統時間
     add_months(d,n)
     last_day(d)：返回指定日期所在月份的最後一天
（2）數學函數

數學函數的輸入參數和傳回值的資料類型都是數字類型的。數學函數包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，

常用的有：

round(n,[m])該函數用於執行四捨五入，如果省掉m，則四捨五入到整數，如果m是正數，則四捨五入到小數點的m位後。如果m是負數，則四捨五入到小數點的m位前。
trunc(n,[m]) 該函數用於截取數字。如果省掉m，就截去小數部分，如果m是正數就截取到小數點的m位後，如果m是負數，則截取到小數點的前m位。
mod(m,n)
 floor(n) 返回小於或是等於n的最大整數
 ceil(n)   返回大於或是等於n的最小整數

（3）系統函數

sys_context
1）terminal：當前會話客戶所對應的終端的標示符
2）lanuage: 語言
3）db_name： 當前資料庫名稱
4）nls_date_format： 當前會話客戶所對應的日期格式
5）session_user： 當前會話客戶所對應的資料庫使用者名稱
6）current_schema： 當前會話客戶所對應的預設方案名
7）host： 返回資料庫所在主機的名稱
通過該函數，可以查詢一些重要訊息，比如你正在使用哪個資料庫。
select sys_context('USERENV','db_name') from dual;
注意：USERENV是固定的，不能改的，db_name可以換成其它,比如select sys_context('USERENV','lanuage') from dual;又比如select sys_context('USERENV','current_schema') from dual;

 

 

8.分組函數  min max count ...

 

9.查詢  分組查   串連查   子查詢  

如果在select語句中同時包含有group by, having, order by 那麼它們的順序是group by, having, order by

 子查詢 ：
   子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢。
oracle的分頁一共有三種方式 ：

（1）根據rowid來分 （執行時間0.03秒） 
（2）按分析函數來分 （執行時間1.01秒 ）

（3）按rownum來分 (執行時間0.1秒 )

Select * from （select s.*,rownum rn from

(select  *  from stuinfo where stu_id=’1001’ ）s  

where rownum<=10）

Where rn>=6

     

 

10.結果集   

  差集， minus 顯示存在第一個集合中，而不存在第二個集合中的資料。  

  交集，intersect 使用該操作符用於取得兩個結果集的交集。

  並集  union當使用該操作符時，會自動去掉結果集中重複行       union all它不會取消重複行，而且不會排序

 

11.pl  變數(幾種資料類型，遊標 系統遊標)  條件   迴圈  

Oracle支援的資料類型：

（1）字元類

Char：定長 ，最大2000個字元。

varchar2(20)： 變長，最大4000個字元。

clob(character large object) 字元型大對象 最大4G
char 查詢的速度極快，浪費空間，查詢比較多的資料用。
varchar 節省空間的

（2）數字型

 number範圍 -10的38次方到10的38次方 ，可以表示整數，也可以表示小數

number(5,2) number(5)

（3）日期類型

date 包含年月日和時分秒 

timestamp 可以精確到毫秒

（4）圖片

blob 位元據 可以存放圖片/聲音  4G 

 

定義遊標：CURSOR 遊標名 IS SELECT查詢語句

開啟遊標：open 遊標名

提取資料：fetch 遊標名 into 變數名

關閉遊標：CLOSE 遊標名

 

遊標：從表中檢索出結果集，從中每次指向一條記錄進行互動的機制。 關聯式資料庫中的操作是在完整的行集合上執行的。

遊標有什麼作用。

 ①指定結果集中特定行的位置。

 ②基於當前的結果集位置檢索一行或連續的幾行。

 ③在結果集的當前位置修改行中的資料。

 ④對其他使用者所做的資料更改定義不同的敏感性層級。

 ⑤可以以編程的方式訪問資料庫。

 

效率低（實際資料較多情況下）盡量避免在遊標迴圈中再進行表串連的操作

 

12.預存程序  函數  包   觸發器  序列  事務(原子性，一致性，隔離性，持久性)

預存程序:一組為了完成特定功能的SQL 陳述式集儲存在資料庫中，經過第一次編譯後再次調用不需要再次編譯，使用者通過指定預存程序的名字並給出參數來執行它。

觸發器：觸發器是一種特殊類型的預存程序，觸發器主要是通過事件進行觸發而被執行的，而預存程序可以通過預存程序名字而被直接調用

觸發器作用:強化約束 跟蹤變化  級聯運行 預存程序的調用 觸發器可以調用一個或多個預存程序

函數：函數用於返回特定資料。執行時得找一個變數接收函數的傳回值

包：所以通過使用包就可以分類管理過程和函數 包分兩部分，包規範和包體

 

序列： 是oacle提供的用於產生一系列唯一數位資料庫物件

       主要用於提供主索引值 自動提供唯一的數值 提高訪問效率

 

事務：事務的任務便是使資料庫從一種狀態變換成為另一種狀態 commit rollback

 

過程(Procedure) 是一個儲存在資料庫中命名了的P L / S Q L語句集合。它可以包含輸入輸出參數，是可執行檔代碼集合，並可以被其他應用程式調用。

函數(Function)也是儲存在資料庫中的P L / S Q L語句集合。與過程的主要區別是函數必須有一個傳回值。

包(Package) 是根據相同功能的特點把過程和函數捆綁在一起的集合。所有的內部過程和函數在資料字典中作為一個單一的儲存包記錄，便於程式的組織和管理。

資料庫觸發器(Trigger) 也是儲存的可執行P L / S Q L語句集合。觸發器主要是通過事件進行觸發而被執行的，資料庫觸發器在插入、更改或刪除語句之前或之後運行。

 

 

 

 

 

 

 

13.索引  視圖

索引是一種供伺服器在表中快速尋找一個行的資料庫結構

　　（1）快速存取資料。

　　（2）既可以改善資料庫效能，又可以保證列值的唯一性。

　　（3）實現表與表之間的參照完整性

　　（4）在使用orderby、groupby子句進行資料檢索時，利用索引可以減少排序和分組的時間。

 

視圖：

    1.為使用者集中資料，簡化使用者的資料查詢和處理。

2.屏蔽資料庫的複雜性，使用者不必瞭解資料庫的複雜性。

3.簡化使用者權限的管理，只授予使用者使用視圖的許可權。

4.便於資料共用，多個使用者不必都定義所需的資料。

5.可以重新組織資料，以便關聯到其他應用中。

 

14.最佳化

 Oracle SQL效能最佳化：

   WHERE子句中的串連順序．表之間的串連必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.

   SELECT子句中避免使用 ‘ * ‘：

   盡量多使用COMMIT

   用TRUNCATE替代DELETE

   減少對錶的查詢

   使用表的別名

   整合簡單,無關聯的資料庫訪問

 

15.三範式

（1）欄位的原子性（列不可再分/欄位無二義性）；

（2）非主屬性必須依賴主屬性（一個表描述一個事物）；

（3）非主屬性必須直接依賴主屬性（不能存在傳遞依賴）。

 

第一範式就是無重複的列

第二範式就是非主屬性非部分依賴於主關鍵字

第三範式就是屬性不依賴於其它非主屬性。

16.數字字典

 資料字典記錄了資料庫的系統資訊，它是唯讀表和視圖的集合，資料字典的所有者為sys使用者。 
  使用者只能在資料字典上執行查詢操作（select語句），而其維護和修改是由系統自動完成的。
 資料字典的組成：資料字典包括資料字典基表和資料字典視圖，其中基表格儲存體資料庫的基本資料，普通使用者不能直接存取資料字典的基表。資料字典視圖是基於資料字典基表所建立的視圖，普通使用者可以通過查詢資料字典視圖取得系統資訊。資料字典視圖主要包括user_xxx，all_xxx，dba_xxx三種類型。