資料庫相關知識點(秋招整理)

資料庫

1.   資料庫事務的 4 個特性是：原子性、一致性、持續性、隔離性

1)   原子性：事務是資料庫的邏輯工作單位，它對資料庫的修改要麼全部執行，要麼全部不執行。

2)   一致性：事務前後，資料庫的狀態都滿足所有的完整性約束。

3)   隔離性：併發執行的事務是隔離的，一個不影響一個。如果有兩個事務，運行在相同的時間內，執行相同的功能，同一時間僅有一個請求用於同一數據。設置資料庫的隔離級別，可以達到不同的隔離效果。

4)   持久性：在事務完成以後，該事務所對資料庫所作的更改便持久的保存在資料庫之中，並不會被回滾。

2.   零散知識點：

1)   資料庫系統的主要特點：數據結構化、數據度的冗餘度小、較高的數據獨立性。

2)   關係數據模型的三個組成部分：數據結構，完整性，數據模型

3)   資料庫管理系統的主要功能：數據定義，數據操作，資料庫的運行管理，數據組織、存儲與管理，資料庫的保護和維護，通信

4)   資料庫系統：由資料庫、資料庫管理系統以及其開發工具、應用系統、資料庫管理員構成。其中，資料庫系統軟體包括資料庫管理系統、開發工具和資料庫應用系統。

3.   SQL語句如何實現收回 user2 查詢基本表 T 的許可權？

revoke select on t from user2

1)   deny：在安全系統中創建一項，以拒絕給當前資料庫內的安全帳戶授予許可權並防止安全帳戶通過其組或角色成員資格繼承許可權。

例如：拒絕user4查詢視圖MyView的許可權 deny SELECT on MyView to user4

2)   revoke：刪除以前在當前資料庫內的用戶上授予或拒絕的許可權。

4.   資料庫中的幾種語言：

1)   數據查詢語言(DQL):用來查詢記錄。保留字：select、where、order by、group by、having。

2)   數據操作語言(DML):用來操縱資料庫記錄。保留字：insert、update、delete。

3)   數據定義語言(DDL):用來定義資料庫的表、庫、列等對象。保留字：create、alter、drop、truncate

4)   數據控制語言(DCL)：用來定義訪問許可權和安全級別。保留字：commit，rollback、savepoint、grant、revoke。

授予許可權 grant privileges on dbname.tablename to‘username’@’host’

a)    privileges：可以是SELECT，INSERT，UPDATE等，如果要授予所的許可權則用ALL

b)    dbname：資料庫名

c)    tablename：表名，若要授予該用戶對資料庫中所有表的操作許可權則可用表示，如.*

例如：grant select on BookStore.* to UserA

撤銷許可權 revoke privilege on dbname.tablename from ‘username’@’host’;

5.   零散知識點：

1)   超鍵(super key):在關係中能唯一標識元組的屬性集稱為關係模式的超鍵

2)   候選鍵(candidate key):不含有多餘屬性的超鍵稱為候選鍵

3)   主鍵(primary key):用戶選作元組標識的一個候選鍵稱為主鍵

6.   對於資料庫索引的說法錯誤的是（B）

A 索引可以提升查詢，分組和排序的性能；

B 索引不會影響表的更新、插入和刪除操作的效率；

C 全表掃描不一定比使用索引的執行效率低；

D 對於只有很少數據值的列，不應該創建索引

有關資料庫中索引的相關知識點：

A.   索引的概念

資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢、更新資料庫表中數據。索引的實現通常使用B樹及其變種B+樹 。在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找演算法。這種數據結構，就是索引。為表設置索引要付出代價的：一是增加了資料庫的存儲空間 ，二是在插入和修改數據時要花費較多的時間(因為索引也要隨之變動) 。

B.   創建索引的優點：

1)   通過創建唯一性索引，可以保證資料庫表中每一行數據的唯一性。

2)   大大加快數據的檢索速度，這也是創建索引的最主要的原因。

3)   加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

4)   使用分組和排序子句進行檢索，減少查詢中分組和排序的時間。

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

C.   增加索引的弊端：

1)   對錶中的數據進行增刪改時，索引也要動態維護，降低了資料庫的維護性。

2)   索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。

3)   創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。  

D.   需要創建索引的列：

索引是建立在資料庫表中的某些列的上面。在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引。 一般來說，應該在以下這些列上創建索引：

1)   在經常需要搜索的列，可以加快搜索的速度；

2)   在作為主鍵的列，強制該列的唯一性和組織表中數據的排列結構；

3)   在經常用在連接的列，這些列主要是一些外鍵，可以加快連接的速度；

4)   在經常需要根據範圍進行搜索的列，因為索引已經排序，其指定的範圍是連續的；

5)   在經常需要排序的列上創建索引，這樣查詢可以利用索引的排序，加快排序查詢時間；

6)   在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

E.   不適合創建索引的列：

不應該創建索引的的這些列具有下列特點：

1)   在查詢中很少使用或者參考的列不應該創建索引。既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。

2)   對於那些只有很少數據值的列也不應該增加索引。由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，即需要在表中搜索的數據行的比例很大。增加索引，並不能明顯加快檢索速度。

3)   對於那些定義為text, image和bit數據類型的列不應該增加索引。這些列的數據量要麼相當大，要麼取值很少。

4)   當修改性能遠遠大於檢索性能時，不應該創建索引。修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大於檢索性能時，不應該創建索引。

F.   索引的分類：

三種索引： 唯一索引、主鍵索引和聚集索引。

1)   普通索引：最基本的索引，而且沒有唯一性之類的限制。

創建：create index [indexName] on [TableName] ([column],…)

刪除：drop index [tableName].[indexName]

唯一索引：不允許其中任何兩行具有相同索引值的索引。當現有數據中存在重覆的鍵值時，大多數資料庫不允許將新創建的唯一索引與表一起保存。資料庫還防止添加，將在表中創建重覆鍵值的新數據。

例如，如果在 employee 表中職員的姓 (lname) 上創建了唯一索引，則任何兩個員工都不能同姓。

創建： create Unique index [indexName] on [TableName] ([column],…)

刪除：drop index [TableName].[IndexName]

2)   主鍵索引

資料庫表經常有一列或列組合，其值唯一標識表中的每一行。該列稱為表的主鍵。在資料庫關係圖中為表定義主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對數據的快速訪問。建議建立唯一索引時，直接創建主鍵索引就好了。

3)   聚集索引

該索引中鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引類似於電話簿，按姓氏排列數據。由於聚集索引規定數據在表中的物理存儲順序，因此一個表只能包含一個聚集索引。但該索引可以包含多個列（組合索引），就像電話簿按姓氏和名字進行組織一樣。

創建：CREATE cluster index [IndexName] ON [TableName]([ColumnName],[ColumnName],...)

聚集索引使用註意事項：

(1) 定義聚集索引鍵時使用的列越少越好；

(2) 使用下列運算符返回一個範圍值的查詢：BETWEEN、>、>=、< 和 <=,可以按物理順序更快的返回一個範圍;

(3) 被連續訪問的列;

(4) 返回大型結果集的查詢；

(5) 經常被使用表聯接或 GROUP BY 子句的查詢訪問的列；一般來說，這些是外鍵列。對 ORDER BY 或 GROUP BY 子句中指定的列進行索引，可以使 SQL Server 不必對數據進行排序，因為這些行已經排序。這樣可以提高查詢性能;

(6) OLTP 類型的應用程式，這些程式要求進行非常快速的單行查找（一般通過主鍵）。應在主鍵上創建聚集索引;

4)   非聚集索引

數據存儲在一個地方，索引存儲在另一個地方，索引帶有指針指向數據的存儲位置。非聚集索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲（這可以由聚集索引規定）。對於非聚集索引，可以為在表非聚集索引中查找數據時常用的每個列創建一個非聚集索引。有些書籍包含多個索引。例如，一本介紹園藝的書可能會包含一個植物通俗名稱索引，和一個植物學名索引，因為這是讀者查找信息的兩種最常用的方法。

創建CREATE noclustered index [IndexName] ON [TableName]([ColumnName],[ColumnName]...)

7.   數據表建立複合索引tab_index(“name”,”age”)，下麵哪些語句能用上索引？（A）

A select*from table where age=18 and name=’test’;

B select*from table where name=’test’and age=18;

C select*from table where name like”%test%”and age=18;

D select*from table where name like”%test”and age=18;

1)   什麼是複合索引？

索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作 複合索引。

2)   複合索引作用

利用索引中的附加列，您可以縮小搜索的範圍，但使用一個具有兩列的索引不同於使用兩個單獨的索引。

3)   例子

 CREATE INDEX name ON employee (emp_lname, emp_fname)

複合索引的結構與電話簿類似，它首先按姓氏對雇員進行排序，然後按名字對所有姓氏相同的雇員進行排序。如果您知道姓氏，電話簿將非常有用，如果您知道名字和姓氏，電話簿則更為有用，但如果您只知道名字而不知道姓氏，電話簿將沒有用處。所以複合索引，欄位的先後順序是很重要的。 列的順序：在創建複合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，複合索引非常有用；僅對後面的任意列執行搜索時，複合索引則沒有用處。

解析：

A，沒有註意複合索引順序

B 正確

C 凡是對索引列，使用了‘%XXX’，都不走索引。如果改為where name = 'test%'則可以走索引。

8.   資料庫設計分為四個階段：

1）需求分析階段：編寫軟體規格說明書及初步的用戶手冊，提交評審。

2）概念設計階段：E-R圖設計階段。

3）邏輯設計階段：主要是E_R轉換成關係模式（或者說是建立關係模式的階段）。

4）物理設計階段

註：在資料庫設計中，描述數據間內在語義聯繫得到E-R圖的過程屬於（概念設計階段）

9.   資料庫中併發操作的相關知識點：

A.   併發與並行的區分：

1)   併發(Concurrent)：當有多個線程在操作時,如果系統只有一個CPU,則它不可能真正同時進行一個以上的線程，它只能把CPU運行時間劃分成若幹個時間段,再將時間段分配給各個線程執行，在一個時間段的線程代碼運行時，其它線程處於掛起狀。

2)   並行(Parallel)：當系統有一個以上CPU時,則線程的操作有可能非併發。當一個CPU執行一個線程時，另一個CPU可以執行另一個線程，兩個線程互不搶占CPU資源，可以同時進行。

3)   區別：併發和並行是即相似又有區別的兩個概念，並行是指兩個或者多個事件在同一時刻發生；而併發是指兩個或多個事件在同一時間間隔內發生。

B.   資料庫事務併發帶來的數據不一致問題有：更新丟失、臟讀、不可重覆讀、幻象讀。

1)   丟失修改：一個事務的更新覆蓋了另一個事務的更新。兩個事務讀入同一數據並修改，後提交的結果破壞先提交的結果，導致先提交的事務修改被丟失；

2)   不可重覆讀：一個事務兩次讀取同一個數據，兩次讀取的數據不一致。先提交的事務讀取數據後，後提交的事務執行更新操作，使得前面的事務不能讀取前一次結果。

3)   讀臟數據：一個事務讀取了另一個事務未提交的數據。某事務修改某一數據，並將它寫回磁碟，後來的事務讀取同一數據後，前一事務由於某種原因回滾（rollback），這時前面已經修改的數據已經恢複原值，後讀到的數據就與資料庫的數據不一致。

4)   幻象讀：一個事務兩次讀取一個範圍的記錄，兩次讀取的記錄數不一致。

a)   事務T1按一定條件從資料庫中讀取了某些數據記錄後,事務T2刪除了其中部分記錄,當T1再次按照相同條件讀取數據時,發現某些記錄神秘的消失了.

b)   事務T1按一定條件從資料庫中讀取了某些數據記錄後,事務T2插入了一些記錄,當T1再次按照相同條件讀取數據時,發現多了一些記錄.

事務A：張三妻子兩次查詢張三有幾張銀行卡。事務B：張三新辦一張銀行卡。事務A第一次查詢銀行卡數的時候，張三還沒有新辦銀行卡，第二次查詢銀行卡數的時候，張三已經新辦了一張銀行卡，導致兩次讀取的銀行卡數不一樣。幻象讀本質上是讀寫操作的衝突，解決辦法是讀完再寫。

以上三種現象的原因是併發操作破壞了事務的隔離性。為了應對這些數據不一致性，主要技術主要有：封鎖，時間戳，樂觀控製法。

10.  資料庫中的封鎖機制相關知識點

A.   封鎖機制的概念

封鎖就是事務T在對某個數據對象操作之前，先向系統發出請求對其加鎖。加鎖後事務T就對該數據對象有了一定的控制，在事務T釋放它的鎖之前，其他事務不能更新此數據對象。基本的鎖類型有兩種：排他鎖（又稱寫鎖，X鎖）和共用鎖（又稱讀鎖，S鎖），此處再加一個更新鎖和意向鎖

1)   共用鎖（讀鎖S鎖），是讀取操作創建的鎖。事務T對數據A加上共用鎖後，則其他事務只能對A再加共用鎖，不能加排他鎖。獲准共用鎖的事務只能讀數據，不能修改數據。

2)   排他鎖（寫鎖X鎖），如果事務T對數據A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲准排他鎖的事務T既能讀數據，又能修改數據。

3)   更新鎖（U鎖）：更新鎖在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共用鎖造成的死鎖現象。因為使用共用鎖時，修改數據的操作分為兩步，首先獲得一個共用鎖，讀取數據，然後將共用鎖升級為排它鎖，然後再執行修改操作。這樣如果同時有兩個或多個事務同時對一個事務申請了共用鎖，在修改數據的時候，這些事務都要將共用鎖升級為排它鎖。這時，這些事務都不會釋放共用鎖而是一直等待對方釋放，這樣就造成了死鎖。如果一個數據在修改前直接申請更新鎖，在數據修改的時候再升級為排它鎖，就可以避免死鎖。

4)   意向鎖：對多粒度樹中的結點加意向鎖，則說明該結點的下層結點正在被加鎖；對任一結點加鎖時，必須先對它的上層結點加意向鎖。

意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖占用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共用鎖，那麼就在表上面添加一個意向共用鎖。而如果自己需要的是某行（或者某些行）上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。意向共用鎖可以同時並存多個，但是意向排他鎖同時只能有一個存在。

意向鎖是InnoDB自動加的，不需用戶干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X)；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共用鎖或排他鎖。

共用鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

B.   封鎖協議

在運用X鎖和S鎖這兩種基本封鎖對數據對象加鎖時，還需要約定一些規則。例如，何時申請X鎖或S鎖、持鎖時間、何時釋放等。這些規則稱為封鎖協議。通常使用三級封鎖協議來在不同程度上解決併發操作的不正確調度帶來的丟失修改、不可重覆讀和讀“臟”數據等不一致性問題。

1)   一級封鎖協議

事務T在修改數據R之前必須先對其加X鎖，直到事務結束才釋放。一級封鎖協議可以防止丟失修改，並保證事務T是可恢復的。

2)   二級封鎖協議

在一級封鎖協議基礎上增加事務T在讀數據R之前必須先對其加S鎖，讀完後釋放S鎖。二級封鎖協議防止丟失修改，讀臟數據。

3)   三級封鎖協議

在一級封鎖協議的基礎上增加事務T在讀數據R之前必須先對其加S鎖，直到事務結束才釋放。三級封鎖協議出防止了丟失修改和讀“臟”數據外，還可以進一步防止了不可重覆讀。

C.   Mysql中鎖的粒度劃分

MySQL各存儲引擎使用了三種級別的鎖定機制：表級鎖定，行級鎖定和頁級鎖定

1)   表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低；

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

2)   行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高；

3)   頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

4)   適用範圍：從鎖的角度來說，表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；而行級鎖則更適合於有大量按索引條件併發更新少量不同數據，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。

例題;

若事務 T 對數據 R 已加 X 鎖，則其他事務對數據 R（D）

A 可以加 S 鎖不能加 X 鎖 ；B 不能加 S 鎖可以加 X 鎖

C 可以加 S 鎖也可以加 X 鎖 ; D 不能加任何鎖

11.  資料庫中的三級模式：外模式，（概念）模式，內模式。

1)   模式：模式又稱概念模式或邏輯模式，對應於概念級。它是由資料庫設計者綜合所有用戶的數據，按照統一的觀點構造的全局邏輯結構，是對資料庫中全部數據的邏輯結構和特征的總體描述，是所有用戶的公共數據視圖(全局視圖)。由DDL來描述、定義的，體現、反映了資料庫系統的整體觀。

2)   外模式：外模式又稱子模式或用戶模式，對應於用戶級。它是某個或某幾個用戶所看到的資料庫的數據視圖，是與某一應用有關的數據的邏輯表示。利用數據操縱語言DML對這些數據記錄進行

3)   內模式：內模式又稱存儲模式，對應於物理級，它是資料庫中全體數據的內部表示或底層描述，是資料庫最低一級的邏輯描述，它描述了數據在存儲介質上的存儲方式和物理結構，對應著實際存儲在外存儲介質上的資料庫。內模式由內模式描述語言來描述、定義，它是資料庫的存儲觀。

外模式和模式建立邏輯獨立性數據的邏輯獨立性是指(用戶的應用程式與資料庫的邏輯結構是相互獨立的)；模式和內模式建立物理獨立性

12.  OODB中，包含其他對象的對象，稱為（複合對象）

ORDB 中，同類元素的無序集合，並且允許一個成員可多次出現，稱為（多集類型也稱作包類型）

13.  資料庫中的幾種故障

1)   事務故障：由非預期的、不正常的程式結束所造成的故障。如輸入數據錯誤、運算溢出、違反存儲保護、並行事務發生死鎖；

2)   系統故障：系統的運行過程中，由於某種原因造成系統停止運轉，致所有正在運行的事務都以非正常的方式終止，要求系統重新啟動。如CPU錯誤、DBMS代碼錯誤；

3)   介質故障：系統在運行過程中，輔助存儲器介質受到破壞，使存儲在外存中的數據部分或全部丟失。

14.  關於JDBC PreparedStatement：可以用來進行動態查詢、通過預編譯和緩存機制提升了執行的效率、有助於防止SQL註入，因為它會自動對特殊字元轉義。

15.  資料庫中範式相關知識點：

A.   有關“鍵”的概念

1)   超鍵：在關係中能唯一標識元組的屬性或屬性集稱為關鍵模式的超鍵。

2)   候選鍵：不含有多餘屬性的超鍵稱為候選鍵。也就是在候選鍵中在刪除屬性就不是鍵了。

3)   主鍵：用戶選作元組標識的候選鍵稱為主鍵。一般不加說明，鍵就是指主鍵。

4)   外鍵：如果模式R中屬性K是其他模式的主鍵，那麼K在模式R中稱為外鍵。

B.   關係資料庫中的“依賴”定義

若在一張表中，在屬性（或屬性組）X的值確定的情況下，必定能確定屬性Y的值，那麼就可以說Y函數依賴於X，寫作 X → Y。

例如在學生表中，找不到任何一條記錄，它們的學號相同而對應的姓名不同。所以我們可以說姓名函數依賴於學號，寫作 學號 → 姓名。但是反過來，因為可能出現同名的學生，所以有可能不同的兩條學生記錄，它們在姓名上的值相同，但對應的學號不同，所以我們不能說學號函數依賴於姓名。

1)   部分函數依賴：設X,Y是關係R的兩個屬性集合，存在X→Y，若X’是X的真子集，存在X’→Y，則稱Y部分函數依賴於X。

舉個例子：學生基本信息表R中（學號，身份證號，姓名）當然學號屬性取值是唯一的，在R關係中，（學號，身份證號）->（姓名），（學號）->（姓名），（身份證號）->（姓名）；所以姓名部分函數依賴與（學號，身份證號）；

2)   完全函數依賴：設X,Y是關係R的兩個屬性集合，X’是X的真子集，存在X→Y，但對每一個X’都有X’!→Y，則稱Y完全函數依賴於X。

例子：學生基本信息表R（學號，班級，姓名）假設不同的班級學號有相同的，班級內學號不能相同，在R關係中，（學號，班級）->（姓名），但是（學號）->(姓名)不成立，（班級）->(姓名)不成立，所以姓名完全函數依賴與（學號，班級）；

3)   傳遞函數依賴：設X,Y,Z是關係R中互不相同的屬性集合，存在X→Y(Y !→X),Y→Z，則稱Z傳遞函數依賴於X。

例子：在關係R(學號 ,宿舍, 費用)中，(學號)->(宿舍),宿舍！=學號，(宿舍)->(費用),費用!=宿舍，所以符合傳遞函數的要求；

C.   幾個範式的定義

1)   第一範式（1NF）

主屬性（主鍵）不為空且不重覆，欄位不可再分。

2)   第二範式（2NF）

在第一範式的基礎上，要求非主屬性完全依賴主屬性。（不存在非主屬性對主鍵的部分依賴）

3)   第三範式（3NF）

3NF在2NF的基礎之上，消除了非主屬性對於碼的傳遞依賴。(不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況。)

4)   BC範式（BCNF）

BC範式在 3NF 的基礎上，消除主屬性對於碼的部分與傳遞函數依賴。

（1）所有非主屬性對每一個碼都是完全函數依賴；

（2）所有的主屬性對於每一個不包含它的碼，也是完全函數依賴；

（3）沒有任何屬性完全函數依賴於非碼的任意一個組合。

D.   幾個範式的定義

1)   第一範式

改為

2)   第二範式

當數據表中是聯合主鍵，但是有的列只依賴聯合主鍵中的一個或一部分屬性組成的聯合主鍵，此時需要拆表才能複合第二範式。

兩個主屬性：學號 和 課程。“學號“和”課程“就組成了聯合主鍵。

對於（學號，課名） → 姓名，有 學號 → 姓名，存在非主屬性 姓名 對碼（學號，課名）的部分依賴。

對於（學號，課名） → 系名，有 學號 → 系名，存在非主屬性 系名 對碼（學號，課名）的部依賴。

對於（學號，課名） → 系主任，有 學號 → 系主任，存在非主屬性 系主任  對碼（學號，課名）的部分依賴

3)   第三範式

Employee(emp_id,emp_name,emp_age,dept_id,dept_name,dept_info)，當員工表中emp_id能夠唯一確定員工員工信息，但是dept_name可由dept_id唯一確定，此時，該表不符合第三範式，此時可以刪除除了dept_id之外的其他部門信息，把所有部門信息單獨建立一張部門表。

4)   BC範式

某公司有若幹個倉庫；每個倉庫只能有一名管理員，一名管理員只能在一個倉庫中工作； 一個倉庫中可以存放多種物品，一種物品也可以存放在不同的倉庫中。每種物品在每個倉庫中都有對應的數量。

那麼關係模式 倉庫（倉庫名，管理員，物品名，數量） 屬於哪一級範式？

答：已知函數依賴集：倉庫名 → 管理員，管理員 → 倉庫名，（倉庫名，物品名）→ 數量

碼：（管理員，物品名），（倉庫名，物品名）

主屬性：倉庫名、管理員、物品名 ；非主屬性：數量

∵ 不存在非主屬性對碼的部分函數依賴和傳遞函數依賴。∴ 此屬於3NF。

某倉庫被清空後，需要刪除所有與這個倉庫相關的物品存放記錄，會帶來什麼問題？——倉庫本身與管理員的信息也被隨之刪除了。 如果某倉庫更換了管理員，會帶來什麼問題？——這個倉庫有幾條物品存放記錄，就要修改多少次管理員信息。

但是存在著主屬性對於碼的部分函數依賴與傳遞函數依賴。（在此例中就是存在主屬性 倉庫名 對於碼（管理員，物品名）的部分函數依賴：倉庫名 → 管理員。

倉庫（倉庫名，管理員） 庫存（倉庫名，物品名，數量）。這樣，之前的插入異常，修改異常與刪除異常的問題就被解決了。

16.  按照規範設計，我們將資料庫的設計過程分為六個階段： 

1)   系統需求分析階段： 1：調查分析用戶活動；2：收集和分析需求數據，確定系統邊界信息需求，處理需求，安全性和完整性需求；3：編寫系統分析報告

2)   概念結構設計階段：1、需求分析數據；2、局部E-R模型；3、全局E-R模型

3)   邏輯結構設計階段：1、初始關係模式設計；2、關係模式規範化:3：模式評價

4)   物理結構設計階段；1、確定物理結構；2、評價物理結構

5)   資料庫實施階段：1、建立實際資料庫結構；2、裝入數據；3、資料庫試運行；4、應用程式編碼與調試；5、整理文檔

6)   資料庫運行與維護階段：1、維護資料庫的安全性和完整性；2、監測並改善資料庫性能；3、重新組織和構造資料庫

17.  視圖設計一般有3種設計次序

1、自頂向下。先全局框架，然後逐步細化 

2、自底向上。先局部概念結構，再集成為全局結構 

3、由里向外。先核心結構，再向外擴張 

4、混合策略。1與2相結合，先自頂向下設計一個概念結構的框架，再自底向上為框架設計局部概念結構

18.  用命令（）可以查看mysql資料庫中user表的表結構？

desc user;  show create table user;   describe user; 三種都可以。

19.  關係規範化中的刪除異常是指(不該刪除的數據被刪除); 應該刪除的數據未被刪除 是運行時異常，我們一般說的異常都是指運行時異常

20.  SQL 語言是（非過程化）的語言，易學習。

21.  在SQL語句中引用宿主變數時，為了區別資料庫中變數，宿主變數前須加“：”，它可與資料庫中變數同名。在宿主語言語句中，宿主變數可與其它變數一樣使用，不須加冒號。當宿主變數的數據類型與資料庫中不一致時，由系統按約定轉換。

22.  資料庫中的視圖（子查詢）相關知識點：

視圖（子查詢）：是從一個或多個表導出的虛擬的表，其內容由查詢定義。具有普通表的結構，但是不實現數據存儲。對視圖的修改：單表視圖一般用於查詢和修改，會改變基本表的數據，多表視圖一般用於查詢，不會改變基本表的數據。

1)   定義和概念

①　從一個或幾個基本表中根據用戶需要而做成一個虛表

②　視圖是虛表,它在存儲時只存儲視圖的定義,而沒有存儲對應的數據 

③　視圖只在剛剛打開的一瞬間,通過定義從基表中搜集數據,並展現給用戶

2)   視圖的優點

①　能分割數據,簡化觀點。可以通過select和where來定義視圖,從而可以分割數據基表中某些對於用戶不關心的數據,使用戶把註意力集中到所關心的數據列.進一步簡化瀏覽數據工作 

②　為數據提供一定的邏輯獨立性。 如果為某一個基表定義一個視圖,即使以後基本表的內容的發生改變了也不會影響“視圖定義”所得到的數據 

③　提供自動的安全保護功能。 視圖能像基本表一樣授予或撤消訪問許可權，給用戶是表的一部分訪問許可權而不是全部。

④　視圖可以間接對錶進行更新,因此視圖的更新就是表的更新 

3)   使用視圖的一些限制和規則

①　視圖必須唯一命名，不能和表或者其他視圖重名

②　視圖可以嵌套，可以從其他視圖中構造視圖

③　order by可以用在視圖定義中，但是如果select語句從視圖中檢索數據時候，在視圖中order by將被覆蓋。

4)   視圖和基本表的區別

a)   視圖是已經編譯好的sql語句。而表不是  

b)   視圖沒有實際的物理記錄。而表有。

c)   表是內容，視圖是視窗

d)   表只用物理空間而視圖不占用物理空間，視圖只是邏輯概念的存在，表可以及時對它進行修改，但視圖只能有創建的語句來修改

e)   表是內模式，視圖是外模式

f)   視圖是查看數據表的一種方法，可以查詢數據表中某些欄位構成的數據，只是一些SQL語句的集合。從安全的角度說，視圖可以不給用戶接觸數據表，從而不知道表結構。

g)   表屬於全局模式中的表，是實表；視圖屬於局部模式的表，是虛表。 

h)   視圖的建立和刪除隻影響視圖本身，不影響對應的基本表。

5)   視圖的創建、刪除、使用、查看：

6)   通過視圖變更數據

INSERT INTO v_order(pid,pname,price) VALUES('p010','柴油','34');

跨表插入數據系統反饋報錯，提示不能修改超過一個表的數據。因此，可以通過視圖插入數據，但是只能基於一個基礎表進行插入，不能跨表更新數據。

補充考點：創建視圖的時候的 with check 語句

對視圖進行update或者insert操作時，保證更新或者插入的行滿足圖中定義的謂詞條件。例如：一張表裡有個欄位是專業的;你創建視圖的時候 create view stu as select 學生 from table where 專業='電腦' with check option 這樣where後面就實現了對專業的限定 以後你如果對視圖添加記錄的時候專業如果不是電腦的話不讓添加進去的。

    Access的數據表視圖中，可以修改欄位名稱、刪除欄位和刪除記錄，但是不能夠修改欄位類型。欄位的類型需要在設計視圖中修改。

例題：在視圖上不能完成的操作是（C）

A 更新視圖 ; B 查詢 ; C 在視圖上定義新的表 ; D 在視圖上定義新的視圖

23.  數據的物理獨立性是指用戶的應用程式與存儲在磁碟上的資料庫中數據是相互獨立的。即，數據在磁碟上怎樣存儲由DBMS管理，用戶程式不需要瞭解，應用程式要處理的只是數據的邏輯結構，這樣當數據的物理存儲改變了，應用程式不用改變。

24.  對基本表 S，執行操作 DROP TABLES RESTRICT 的作用是當沒有由 S 中的列產生的視圖或約束時將表 S 刪除

drop table:從一個資料庫中刪除一個表

drop table Restrict:確保只有不存在相關視圖或完整性約束的表才可以被刪除

drop table cascade:任何引用的視圖或完整性約束都將被刪除

25.  查詢訂購單號(字元型，長度為4)尾字元是"1"的錯誤命令是______。

A SELECT * FROM 訂單 WHERE SUBSTR(訂購單號,4)="1"

B SELECT * FROM 訂單 WHERE SUBSTR(訂購單號,4,1)="1"

C SELECT * FROM 訂單 WHERE "1"$訂購單號

D SELECT * FROM 訂單 WHERE RIGHT(訂購單號,1)="1"

SUBSTR用法：

1.SBUSTR(str,pos); 就是從pos開始的位置，一直截取到最後;

2.1.SBUSTR(str,pos,len); 就是從pos開始的位置，截取len長度；

RIGHT用法

right（a,b）函數表示的是從字元表達式最右邊一個字元開始返回指定數目的字元.若 b 的值大於 a 的長度,則返回字元表達式的全部字元a.如果 b 為負值或0,則返回空字元串。

26.  在DDBS中，數據傳輸量是衡量查詢時間的一個主要指標，導致數據傳輸量大的主要原因是（ C ）

A 場地間距離過大;B資料庫的數據量大;C不同場地間的聯接操作;D在CPU上處理通信的代價高

DDBS 分散式資料庫系統

27.  資料庫中的字元串的連接操作符  

連接運算符主要用於連接字元串，其運算符有三個：+，&，||;

1)   &用來強調兩個表達式作為字元串連接，如“hello”&23&"word"，結果為“hello23word”

2)   +連接兩個字元串，要求+兩端的類型必須一致，如“hello”+23+"word"，結果會報錯“類型不匹配”

3)   註意點：oracle資料庫：用“||”可以，用“&不可以”

        MySQL資料庫：用“||”和“&”都可以

在MySql中，concat函數的作用是是將傳入的參數連接成為一個字元串，則concat（’aaa’,null,’bbb’）的結果是（Null）

CONCAT合併字元串，只要有一個字元串為空，則輸出為空；CONCAT_WS合併字元串，只要第一個字元串不為空，則輸出不為空。

28.  有關數據字典的相關知識點：

數據字典是資料庫的重要組成部分。資料庫數據字典是一組表和視圖結構。它們存放在SYSTEM表空間中。它存放有資料庫所用的有關信息，對用戶來說是一組只讀的表。數據字典內容包括： 1、資料庫中所有模式對象的信息，如表、視圖、簇、及索引等。

2、分配多少空間，當前使用了多少空間等。 3、列的預設值。 4、約束信息的完整性。 5、用戶的名字。 6、用戶及角色被授予的許可權。 7、用戶訪問或使用的審計信息。

8、其它產生的資料庫信息。

29.  有關異常的總結：

1)   “插入異常”是指當要往資料庫中插入新的數據的時候，插入不成功導致異常；

2)   “刪除異常”是指當要刪除資料庫中數據的時候，不能刪除，刪除不成功；

3)   ”修改異常”是指當要修改資料庫中數據的時候，修改不成功；

4)   資料庫中的“插入異常”、“刪除異常”、“修改異常”是資料庫模式中存在依賴關係導致

30.  在資料庫三級模式間引入二級映象的主要作用是( 提高數據與程式的獨立性 )?

31.  模糊查詢語句，資料庫中的模糊查詢關鍵字為like，並提供了四種模糊匹配條件：

1)   %：表示0~n個任意個字元

把flow_user這張表裡面，列名username中含有“王”的記錄全部查詢出來

select * from flow_user where username like '%王%'；

2)   _:表示一個字元

只能找到“王英琨”這樣username為三個字且中間一個字是“英”的內容。

select * from flow_user where username like '_英_';

3)   []：表示括弧內所列字元中的一個

將找出“王飛”“李飛”“張飛”（而不是“張王李飛”）。

select * from flow_user where username LIKE'[王李張]飛';

4)   [^]：表示不在括弧所列之內的單個字元。

將找出不是“王飛”“李飛”“張飛“的”趙飛“、”吳飛“等。

select * from flow_user where username LIKE'[^王李張]飛';

32.  資料庫技術的根本目標是要解決數據共用的問題；資料庫管理系統的工作不包括：為定義的資料庫提供操作系統

33.  死鎖發生時( 撤消其中一個事務，並恢復到初態 )

34.  Mysql實現的四種通信協議（TCP/IP，Socket，共用記憶體，命名管道）

35.  資料庫中的"空值" 和"NULL"的概念：

1)   空值('')是不占用空間的,判斷空字元用 = '' 或者 <> '' 來進行處理;

2)   NULL值是未知的，且占用空間，不走索引;判斷 NULL 用 IS NULL 或者 is not null , SQL 語句函數中可以使用 ifnull ()函數來進行處理.

3)   在進行 count ()統計某列的記錄數的時候，如果採用的 NULL 值，會別系統自動忽略掉，但是空值是統計到其中

36.  設關係模式R(A，B，C)，F是R上成立的FD集，F={A→B，C→B}，ρ={AB，AC}是R的一個分解，那麼分解ρ( C )？

A 保持函數依賴集F；B 丟失了A→B；

C 丟失了C→B；D 丟失了B→C

37.  資料庫恢復的基礎是利用轉儲的冗餘數據。這些轉儲的冗餘數據包括（日誌文件，資料庫後備副本）

38.  （1）PCTFREE：為一個塊保留的空間百分比，表示數據塊在什麼情況下可以被insert。

（2）PCTUSED：是指當塊里的數據低於多少百分比時，又可以重新被insert。

形象舉例說明：

假如：一個杯子一共可裝10分水:

PCTFREE = 10,說明杯子裝到9分水，就不能再裝了，即:不能進行insert操作，但可以進行update操作。

PCTUSED = 40，說明杯子中的水喝到4分一下，就可以往裡面裝水，即：進行insert操作。

39.  某查詢語句運行後返回的結果集為：

則最有可能的查詢語句是以下：C

A SELECT class, AVG(score) FROM test WHERE class<3

B SELECT class, AVG(score) FROM test WHERE class<3 GROUP BY class

C SELECT class, AVG(score) FROM test WHERE class<3 GROUP BY ALL class

D SELECT class, AVG(score) FROM test GROUP BY class HAVING class<3

因為出現了3班 NULL, 所以如果是D的話，就不會出現Null了。條件限制了選擇的class<3 所以只有1 ，2班有成績。

下麵是幫助中group by +all的用法：

GROUP BY 子句中提供 ALL 關鍵字。只有在 SELECT 語句還包括 WHERE 子句時，ALL 關鍵字才有意義。如果使用 ALL 關鍵字，那麼查詢結果將包括由 GROUP BY 子句產生的所有組，即使某些組沒有符合搜索條件的行。沒有 ALL 關鍵字，包含 GROUP BY 子句的 SELECT 語句將不顯示沒有符合條件的行的組。

註：having與where的區別：

1、  having是在分組後對數據進行過濾的。

Where是在分組前對數據進行過濾的。

2、  having後面可以使用聚合函數（統計函數）

where後面不可以使用聚合函數。

40.  五種基本關係代數運算是？ （∪，－，×，π，σ ）

關係運算：選擇、投影、連接、除

傳統集合運算：並、差、交、笛卡爾

5種基本運算：並、差、笛卡爾、選擇、投影

關係代數運算中的基本運算包括並(∪)、差(-)、廣義笛卡爾積(×)、投影(π)和選擇(σ)，其他運算的功能都可以由這五種基本運算來實現。

投影操作是從表中選出某些列而丟棄另一些列。如果只對一個關係中的某些屬性感興趣，那麼就需要使用投影操作在這些屬性上投影該關係。

關係R是的投影是從關係R中選擇出若幹屬性列組成新的關係。分為兩步：

(1)選擇出指定的屬性，形成一個可能含有重覆行的表。

(2)刪除重覆行，形成新的關係

對關係R進行投影運算後，得到關係S，則( 關係R的元組數大於或等於關係S的元組數 )。

41.  在 SQL 語句中，與 X between 20 and 30 等價的表達式是： X>=20 AND X<=30。

42.  關係資料庫規範化是為解決關係資料庫中（ 插入、刪除和數據冗餘 ）問題而引入的。

43.  SQL語句性能分析的關鍵字是什麼？ EXPLAIN

explain命令在解決資料庫性能上是第一推薦使用命令，大部分的性能問題可以通過此命令來簡單的解決，Explain可以用來查看SQL語句的執行效 果，可以幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。explain語法：explain select … from … [where ...] 例如：explain select * from news;

44.  資料庫中的E-R圖

1)   實體類型的轉換：將一個實體型轉換為一個關係模式，關係的屬性就是實體的屬性；

2)   關係類型轉換分為如下幾種情況

a)   1：1聯繫：可以轉換成一個獨立的關係模式，也可以與任意一端對應的關係模式合併。

如果轉換為一個獨立的關係模式，則與該聯繫相連的各實體的碼以及聯繫本身的屬性均轉換為關係的屬性，每個實體的碼均是該關係的候選碼。

如果與某一端實體對應的關係模式合併，則需要在該關係模式的屬性中加入另一個關係模式的碼和聯繫本身的屬性。

b)   1：n聯繫:可以轉換為一個獨立的關係模式，也可以與n端對應的關係模式合併。如果轉換為一個獨立的關係模式，則與該聯繫相連的各實體的碼以及聯繫本身的屬性均轉換為關係的屬性，而關係的碼為n端實體的碼。

c)   m：n聯繫:轉換為一個關係模式，與該聯繫相連的各實體的碼以及聯繫本身的屬性均轉換為關係的屬性，各實體的碼組成關係的碼或關係碼的一部分。

實例：

1)   部門（部門號，部門名，經理的職工號，…）

此為部門實體對應的關係模式，該關係模式已包含了聯繫“領導所對應的關係模式。經理的職工號是關係的候選碼。

2)   職工（職工號，部門號，職工名，職務，…）

此為職工實體對應的關係模式，該關係模式已包含了聯繫“屬於”所對應的關係模式。

3)   產品（產品號，產品名，產品組長的職工號，…）此為產品實體對應的關係模式。

4)   供應商(供應商號，姓名，…）此為供應商實體對應的關係模式 。

5)   零件（零件號，零件名，…）為零件實體對應的關係模式。

6)    生產（職工號，產品號，工作天數，…）此為聯繫“生產”所對應的關係模式。

7)   供應（產品號，供應商號，零件號，供應量）此為聯繫“聯繫”所對應的關係模式。

45.  資料庫中的觸發器

1)   觸發器的概念

觸發器（trigger）是SQL server 提供給程式員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用於加強數據的完整性約束和業務規則等。

不會觸發觸發器的包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。

2)   觸發器的語法

Create trigger trigger_name ON {table_name | view_name}

{FOR | After | Instead of } [ insert, update,delete ]

AS  sql_statement

刪除觸發器：drop trigger 觸發器名

3)   觸發器的分類

a)   After觸發器，After觸發器要求只有執行某一操作insert、update、delete之後觸發器才被觸發，且只能定義在表上。

b)   Instead of 觸發器，Instead of 觸發器表示並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身。既可以在表上定義instead of觸發器，也可以在視圖上定義。

c)   後觸髮型觸發器

使用for或after選項定義的觸發器，執行過程如下：1. 執行到引發觸發器執行的語句。2. 執行該語句 。3. 執行觸發器

4)   觸發器的作用

a)   完成比約束更複雜的數據約束

b)   檢查所做的sql是否允許

c)   調用更多的存儲過程

d)   防止數據表結構更改或數據表被刪除

e)   修改其他數據表的數據

f)   發送sql mail

g)   返回自定義的錯誤信息

5)   註意事項

a)   一個表可以有多個觸發器，但一個觸發器只能對應一個表，但可以引用資料庫以外的對象

b)   同一個數據表中，對每個操作而言，可以建立多個after觸發器，但instead of 觸發器只能創建一個

c)   對某個操作，既建了after觸發器，又設置了instead of觸發器，instead of觸發器一定會激活，after觸發器不一定會被激活

46.  資料庫中的約束

1)   實體完整性：主鍵完整性的約束條件，要求主鍵不能為空（NULL）且不可以重覆。

約束類型：主鍵約束（primary key）、唯一約束（unique）、自增長（auto_increment）

Sqlserver(identity)、oracle(sequence)

或者使用： alter table student add primary key(id)

           alter table add constraint pk_stuid primary key(id)

刪除主鍵約束：alter table 表名 drop primary key

2)   參照完整性：參照完整性是相對於外鍵而言的，在資料庫中，外鍵常用來關聯兩個表，它的值要麼為NULL，要麼就是它參照的那個表的主鍵值。

或：

alter table score1 add constraint fk_stu_score foreign key(sid) references stu(id);

3)   用戶自定義完整性:在CREATE TABLE中定義屬性時，可以根據應用程式的