存儲過程

1、什麼是存儲過程

存儲過程是一個預編譯的SQL語句；是由SQL語句組成的代碼塊，允許模塊化的設計；只需創建一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。

2、存儲過程的調用

（1）可以用一個命令對象來調用存儲過程；

（2）可以供外部程式調用，比如：java程式。

3、存儲過程的優缺點

優點：

（1）存儲過程是預編譯過的，執行效率高；

（2）存儲過程的代碼直接存放於資料庫中，通過存儲過程名直接調用，減少網路通訊；

（3）安全性高，執行存儲過程需要有一定許可權的用戶；

（4）存儲過程可以重覆使用，可減少資料庫開發人員的工作量。

 缺點：移植性差

4、存儲過程與函數的區別

（1）存儲過程用於在資料庫中完成特定的操作或者任務（如插入、刪除等）；函數用於特定的數據（如選擇）；

（2）存儲過程程式頭部聲明用procedure；函數程式頭部聲明用function；

（3）存儲過程程式頭部聲明時不需描述返回類型； 函數程式頭部聲明時要描述返回類型，而且PL/SQL塊中至少要包括一個有效的return語句；

（4）存儲過程可作為一個獨立的PL/SQL語句來執行；函數不能獨立執行，必須作為表達式的一部分調用；

（5）都可以使用in/out/in out 三種模式的參數；存儲過程可以通過out/in out 返回零個或多個值；函數通過return語句返回一個值，且該值要與聲明部分一致，也可以是通過out類型的參數帶出的變數；

（6）SQL語句(DML 或SELECT)中不可調用存儲過程；SQL語句(DML 或SELECT)中可以調用函數。

索引

1、什麼是索引

（1）索引就一種特殊的查詢表，資料庫的搜索可以利用它加速對數據的檢索。

（2）索引是對資料庫表中一或多個列的值進行排序的結構，是幫助MySQL高效獲取數據的數據結構；

2、索引的作用

它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的數據。

3、索引的優缺點

優點：

（1）加快資料庫的檢索速度；

（2）降低了插入、刪除、修改等維護任務的速度；

（3）唯一索引可以確保每一行數據的唯一性；

（4）通過使用索引，可以在查詢的過程中使用優化隱藏器，提高系統的性能。

缺點：

索引需要占物理和數據空間，即減慢了數據錄入的速度，同時也增加了資料庫的尺寸大小。

4、什麼樣的欄位適合建索引？

唯一、不為空、經常被查詢的欄位。

5、什麼情況下用或不用索引？

如果一張表更多是用於查詢而很少插入，那麼就可以建立儘量多的索引以優化查詢性能。相反如果一張表要經常插入或更新，則儘可能少用索引，有時甚至連主鍵都不建。

6、索引的類型

MySQL中基本的索引類型：普通索引、唯一索引、主鍵索引、全文索引。

其他分類：

（1）邏輯上：

Single column 單行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based 函數索引

Domain 域索引

（2）物理上：

Partitioned 分區索引

NonPartitioned 非分區索引

（3）B-tree ：

Normal 正常型B樹

Rever Key 反轉型B樹

Bitmap 點陣圖索引

觸發器

1、什麼是觸發器？

觸發器是一種特殊的存儲過程，主要是通過事件來觸發而被執行的。

2、觸發器的作用？

（1）可以強化約束，來維護數據的完整性和一致性；可以跟蹤資料庫內的操作，從而不允許未經許可的更新和變化。

（2）可以聯級運算。如，某表上的觸發器上包含對另一個表的數據操作，而該操作又會導致該表觸發器被觸發。

3、觸發器分類

觸發時間：有before,after.

觸發事件：有insert,update,delete三種

觸發類型：有行觸發、語句觸發

事務和鎖

1、什麼是事務？

事務（Transaction）是併發控制的基本單位。它是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位。事務是資料庫維護數據一致性的單位，在每個事務結束時，都能保持數據一致性。

2、事務的四大特性

一致性、原子性、隔離性、持久性。

3、事務的三個常用命令

Begin Transaction、Commit Transaction、RollBack Transaction。

4、什麼是鎖？

鎖：在DBMS中，鎖是實現事務的關鍵，鎖可以保證事務的完整性和併發性。與現實生活中鎖一樣，它可以使某些數據的擁有者，在某段時間內不能使用某些數據或數據結構。

5、樂觀鎖和悲觀鎖

樂觀併發控制(樂觀鎖)和悲觀併發控制（悲觀鎖）是併發控制主要採用的技術手段。

悲觀鎖：假定會發生併發衝突，屏蔽一切可能違反數據完整性的操作。

樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否違反數據完整性。

視圖

1、什麼叫視圖？

視圖是一種虛擬的表，具有和物理表相同的功能。可以對視圖進行增，改，查操作，視圖通常是有一個表或者多個表的行或列的子集。對視圖的修改會影響基本表。相比多表查詢，它使得我們獲取數據更容易。

2、視圖的優缺點

優點：

（1）視圖可以有選擇性的選取資料庫里的一部分。

（2）用戶可以從簡單的查詢中得到結果。

（3）維護數據的獨立性，試圖可從多個表檢索數據。

（4）對於相同的數據可產生不同的視圖。

缺點：

查詢視圖時，必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼就無法更改數據。

3、表和視圖的關係

視圖其實就是一條查詢sql語句，用於顯示一個或多個表或其他視圖中的相關數據。表就是關係資料庫中實際存儲數據用的。

游標

1、什麼是游標？

游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄進行處理的機制。

2、什麼時候用游標？

一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

3、游標的使用步驟

（1）定義游標：

declare cursor 游標名稱 for  select查詢語句 [for {readonly|update}]

（2）打開游標：open cursor

（3）從游標中操作數據：fetch... ...   current of cursor

（4）關閉游標：close cursor

主鍵和外鍵

1、鍵的分類

超鍵：在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

候選鍵：是最小超鍵，即沒有冗餘元素的超鍵。

主鍵：資料庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能為空值（Null）。

外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

2、主鍵和外鍵的區別？

主鍵在本表中是唯一的、不可為空，外鍵可以重覆可以為空；

外鍵和另一張表的主鍵關聯，不能創建對應表中不存在的外鍵。

3、在刪除主表記錄時，一併刪除從表相關聯的記錄？

如果兩張表存在主外鍵關係，那麼在刪除主鍵表的記錄時，如果從表有相關聯的記錄，那麼將導致刪除失敗。

在定義外鍵約束時，可以同時指定3種刪除策略：

一是將從表記錄一併刪除（級聯刪除）；

二是將從表記錄外鍵欄位設置為NULL；

三是將從表記錄外鍵欄位設置為預設值。

級聯刪除示例：

alter table 從表名

add constraint 外鍵名

foreign key(欄位名) references 主表名(欄位名)

on delete cascade

資料庫約束

1、什麼是資料庫約束

資料庫約束用於保證資料庫表數據的完整性（正確性和一致性，包括實體完整性、參照完整性、用戶定義完整性）。可以通過定義約束\索引\觸發器來保證數據的完整性。

2、資料庫約束的分類

主鍵約束：primary key；

外鍵約束：foreign key；

唯一約束：unique；

檢查約束：check；

空值約束：not null；

預設值約束：default

3、維護資料庫的完整性和一致性，你喜歡用觸發器還是自寫業務邏輯？為什麼？

答：儘可能使用約束，如check,主鍵，外鍵，非空欄位等來約束，這樣做效率最高，也最方便。其次是使用觸發器，這種方法可以保證無論什麼業務系統訪問資料庫都可以保證數據的完整新和一致性。最後考慮的是自寫業務邏輯，但這樣做麻煩，編程複雜，效率低下。

資料庫三範式

1、簡化版

第一範式：表中每個欄位都不能再分。

第二範式：滿足第一範式並且表中的非主鍵欄位都依賴於主鍵欄位。

第三範式：滿足第二範式並且表中的非主鍵欄位必須不傳遞依賴於主鍵欄位，即消除傳遞依賴，方便理解，可以看做是“消除冗餘”。

2、詳細版

第一範式（1NF）：資料庫表中的欄位都是單一屬性的，不可再分。這個單一屬性由基本類型構成，包括整型、實數、字元型、邏輯型、日期型等。

第二範式（2NF）：資料庫表中不存在非關鍵欄位對任一候選關鍵欄位的部分函數依賴（部分函數依賴指的是存在組合關鍵字中的某些欄位決定非關鍵欄位的情況），也即所有非關鍵欄位都完全依賴於任意一組候選關鍵字。

第三範式（3NF）：在第二範式的基礎上，數據表中如果不存在非關鍵欄位對任一候選關鍵欄位的傳遞函數依賴則符合第三範式。所謂傳遞函數依賴，指的是如 果存在"A → B → C"的決定關係，則C傳遞函數依賴於A。因此，滿足第三範式的資料庫表應該不存在如下依賴關係： 關鍵欄位 → 非關鍵欄位 x → 非關鍵欄位y。

其他

1、列舉幾種表連接方式？

（1）內聯接（Inner Join）：匹配2張表中相關聯的記錄。

（2）左外聯接（Left Outer Join）：除了匹配2張表中相關聯的記錄外，還會匹配左表中剩餘的記錄，右表中未匹配到的欄位用NULL表示。

（3）右外聯接（Right Outer Join）：除了匹配2張表中相關聯的記錄外，還會匹配右表中剩餘的記錄，左表中未匹配到的欄位用NULL表示。

（4）全外連接：連接的表中不匹配的數據全部會顯示出來。

（5）交叉連接：笛卡爾效應，顯示的結果是鏈接表數的乘積。

2、union和union all有什麼不同?

（1）union在進行錶鏈接後會篩選掉重覆的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，刪除重覆的記錄再返回結果。實際大部分應用中是不會產生重覆的記錄，最常見的是過程表與歷史表union。

（2）union all只是簡單的將兩個結果合併後就返回。這樣，如果返回的兩個結果集中有重覆的數據，那麼返回的結果集就會包含重覆的數據了。

（3）從效率上說，union all 要比union快很多，所以，如果可以確認合併的兩個結果集中不包含重覆的數據的話，那麼就使用union all。

3、什麼是記憶體泄漏？

答：一般我們所說的記憶體泄漏指的是堆記憶體的泄漏。堆記憶體是程式從堆中為其分配的，大小任意的，使用完後要顯示釋放記憶體。當應用程式用關鍵字new等創建對象時，就從堆中為它分配一塊記憶體，使用完後程式調用free或者delete釋放該記憶體，否則就說該記憶體就不能被使用，我們就說該記憶體被泄漏了。

4、如何優化查詢語句？

（1）建索引；

（2）減少表之間的關聯；

（3）優化sql，儘量讓sql很快定位數據，不要讓sql做全表查詢，使用索引,把數據量大的表排在前面；

（4）簡化查詢欄位，沒用的欄位不要，儘量返回少量數據；

（5）儘量用PreparedStatement來查詢，不要用Statement。

Oracle資料庫

1、Oracle基本數據類型

（1）字元串類型 char、nchar、varchar、varchar2、nvarchar2

（2）數字類型 number、integer            

（3）浮點類型 binary_float、binary_double、float

（4）日期類型 date、 timestamp

（5）LOB類型 blob、clob、nclob、bfile

2、Oracle語句類型

Oracle語句分三類：DDL、DML、DCL。

（1）DDL（Data Definition Language）數據定義語言，包括：

Create語句：可以創建資料庫和資料庫的一些對象。

Drop語句：可以刪除數據表、索引、觸發程式、條件約束以及數據表的許可權等。 Alter語句：修改數據表定義及屬性。

Truncate語句：刪除表中的所有記錄,包括所有空間分配的記錄被刪除。

（2）DML（Data Manipulation Language）數據操控語言，包括：

Insert語句：向數據表張插入一條記錄。

Delete語句：刪除數據表中的一條或多條記錄，也可以刪除數據表中的所有記錄，但是它的操作對象仍是記錄。

Update語句：用於修改已存在表中的記錄的內容。

（3）DCL（Data Control Language）資料庫控制語言，包括：

Grant語句：允許對象的創建者給某用戶或某組或所有用戶（PUBLIC）某些特定的許可權。

Revoke語句：可以廢除某用戶或某組或所有用戶訪問許可權。

3、char、varchar2、varchar

（1）char的長度是固定的，而varchar2的長度是可以變化的，varchar是varchar2的同義詞；char的效率要被varchar2的效率高。

（2）目前，工業標準的varchar類型可以存儲空字元串； Oracle自己開發了一個數據類型varchar2,將在資料庫中存儲空字元串的特性改為存儲null值。

4、Oracle和Mysql的區別？

（1）庫函數不同。

（2）Oracle是用表空間來管理的，Mysql不是。

（3）顯示當前所有的表、用戶、改變連接用戶、顯示當前連接用戶、執行外部腳本的語句不同。

（4）分頁查詢時候時候，Mysql用limit ，Oracle用rownum

5、order by與group by

（1）order by 排序查詢，[asc升序、desc降序]

（2）group by 分組查詢，having 只能用於group by子句、作用於組內，having條件子句可以直接跟函數表達式。

（3）使用group by 子句的查詢語句需要使用聚合函數。

6、commit提交

oracle的commit就是DML語句提交數據，在未提交前的操作更新的都是記憶體，沒有更新到物理文件中。 commit即為從記憶體更新到物理文件。

7、PL/SQL

PL/SQL是一種程式語言，叫做過程化SQL語言（Procedural Language/SQL）。PL/SQL是Oracle資料庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL把數據操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、迴圈等操作實現複雜的功能或者計算。PL/SQL 只有 Oracle 資料庫有。 MySQL 目前不支持 PL/SQL 的。

8、序列的作用

Oracle使用序列來生成唯一編號，用來處理一個表中自增欄位。

Oracle序列是原子對象，並且是一致的。也就是說，一旦您訪問一個序列號，Oracle將在處理下一個請求之前自動遞增下一個編號，從而確保不會出現重覆值。

9、truncate、 delete、drop

（1）truncate table在功能上與不帶 where子句的 delete 語句相同；二者均刪除表中的全部行，但 truncate table比 delete 速度快，且使用的系統和事務日誌資源少。 delete 語句每次刪除一行，併在事務日誌中為所刪除的每行記錄一項；truncate table通過釋放存儲表數據所用的數據頁來刪除數據，並且只在事務日誌中記錄頁的釋放。

（2）truncate，delete，drop 放在一起比較：

truncate table：刪除內容、釋放空間但不刪除定義。

delete table：刪除內容不刪除定義，不釋放空間。

drop table：刪除內容和定義，釋放空間。

（3）drop、delete與truncate分別在什麼場景之下使用？

不再需要一張表的時候，用drop；

想刪除部分數據行時候，用delete，並且帶上where子句；

保留表而刪除所有數據的時候用truncate。

10、Oracle獲取系統時間

select to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss') from dual;

11、Oracle怎麼去除去重

使用distinct關鍵字

12、Oracle分頁查詢語句

使用rownum，兩種如下：

第一種： select * from (select t.*,rownum row_num from mytable t) b

where b.row_num between 1 and 10

第二種： select * from ( select a.*, rownum rn from mytable a where rownum <= 10 ) where rn >= 1

使用rowid， 如下：

select * from scott.emp where rowid in (select rd from

(select rowid as rd ,rownum as rn from scott.emp ) where rn<=6 and rn>3)

13、Oracle系統主要進程

數據寫進程(DBWR)：負責將更改的數據從資料庫緩衝區高速緩存寫入數據文件；

日誌寫進程(LGWR)：將重做日誌緩衝區中的更改寫入線上重做日誌文件；

系統監控  (SMON): 檢查資料庫的一致性如有必要還會在資料庫打開時啟動資料庫的恢復；

進程監控  (PMON): 負責在一個Oracle 進程失敗時清理資源；

檢查點進程(CKPT)：負責在每當緩衝區高速緩存中的更改永久地記錄在資料庫中時,更新控制文件和數據文件中的資料庫狀態信息。；

歸檔進程  (ARCH)：在每次日誌切換時把已滿的日誌組進行備份或歸檔；

恢復進程  (RECO): 保證分散式事務的一致性,在分散式事務中,要麼同時commit,要麼同時rollback；

作業調度器(CJQ ):  負責將調度與執行系統中已定義好的job,完成一些預定義的工作。

14、sql語句1

現有表:A(id ,name,regdate) B(id,groupid) C(id,name2) ,寫出下麵的SQL語句

（1）統計A表中每個月註冊用戶數

答：

select count(*),to_char(regdate,'yyyymm') from A group by to_char(regdate,'yyyymm'); （2）統計A表中有姓名相同的用戶數

答：select count(*) from (select name from A group by name having count(*) >1);

（3）如果表A中有姓名相同的用戶,把相同的查出,寫入表C中

答：insert into C(name2) select name from A group by name having count(*) >1;

（4）A中ID有多個相同的數據,A中姓名相同的ID只保留註冊時間最大的數據 答：delete from E where e.regdate < (select max(regdate) from a X where E.id = X.id);

15、sql語句2

現有表：Student(S#,SN,SD)學生表， Course(C#,CN,T#)課程表， SC（S#,C#,score）成績表。

（1）查詢選了課程‘稅收’的學生的學號和名字

答： select SN,SD from Student where S# in( select S# from Course C , SC where C.C#=SC.C# and CN= ’稅收基礎’);

（2）查詢選課數量大於5的學生的學號和名字

答： select SN,SD from Student where S# in ( select S# from SC group by S# having count(distinct C#) > 5); )

（3）建立一個學生表students，包括name,age,head_teacher,id,score(姓名，年齡，班主任，學號，成績)

答： Create table students ( Id number(9) not null primary key, Name varchar2(40) not null, Age int check(age between 0 and 100), Head_teacher vachar2(40), Score float );

（4）對上表插入一條記錄，姓名：張三，年齡：18，班主任：李四，學號：22 答： Insert into student(id,name,age,head_teacher) values(‘22’,’張三’,’18’,’李四’)； （5）對上表中的age+name創建一個索引，並說明它的作用和使用方法

答： Create index student_index on students(age,name);

----------------END-----------------

（東流不作西歸水，落花辭條羞故林）