資料庫
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) 檢視的建立、刪除、使用、檢視：
|
CREATE or replace view VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
|
DROP VIEW view_name |
|
select * from view當成表使用就好 |
|
由於檢視相關與一張虛表，使用show tables檢視當前資料中的檢視： |
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中定義屬性時，可以根據應用程式的要求在屬性上新增約束條件(屬性限制)，包括:列值非空（NOT NULL）、列值唯一（UNIQUE）、check約束（mysql不支援）check（sex =’男’or ‘女’）、預設值約束（default）
例如定義的時候可以寫：sex varchar(10) default’男’
47. 資料庫中關於刪除的幾個關鍵詞
當你不再需要該表時， 用 drop;
當你仍要保留該表，但要刪除所有記錄時， 用 truncate;
當你要刪除部分記錄時（always with a WHERE clause), 用 delete.
48. SQL 語句中修改表結構的命令是（ALTER TABLE）
修改表結構包括：增加欄位、刪除欄位、增加約束、刪除約束、修改預設值、修改欄位資料型別、重新命名欄位、重新命名錶等。這些操作都是用 alter table 命令來完成。常用用法如下：
1、增加欄位：ALTER TABLE 表名 ADD 欄位名 欄位型別；
2、刪除欄位：ALTER TABLE 表名 DROP COLUMN 欄位列名；
3、增加約束：ALTER TABLE 表名 ADD CHECK(欄位名<>“)或者 ALTER TABLE 表名 ADD CONSTRAINT 約束名 UNIQUE(欄位名)；
4、刪除約束：ALTER TABLE 表名 DROP CONSTRAINT 約束名；
5、修改欄位預設值：ALTER TABLE 表名 ALTER COLUMN 欄位名 SET DEFAULT 預設值；
6、 修改欄位資料型別：ALTER TABLE 表名 ALTER COLUMN 欄位名TYPE l型別；
7、重新命名欄位：ALTER TABLE 表名 RENAME COLUMN 舊欄位名TO 新欄位名；
8、重新命名錶：ALTER TABLE 表名 RENAME TO 新表名。
49. 六、多表查詢（重要）
多表查詢有如下幾種：
l 合併結果集；UNION 、 UNION ALL
l 連線查詢
Ø 內連線 [INNER] JOIN ON
Ø 外連線 OUTER JOIN ON
² 左外連線 LEFT [OUTER] JOIN
² 右外連線 RIGHT [OUTER] JOIN
² 全外連線（MySQL不支援）FULL JOIN
Ø 自然連線 NATURAL JOIN
l 子查詢
1 合併結果集
1. 作用：合併結果集就是把兩個select語句的查詢結果合併到一起！
2. 合併結果集有兩種方式：
l UNION：去除重複記錄，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
l UNION ALL：不去除重複記錄，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
3. 要求：被合併的兩個結果：列數、列型別必須相同。
2 連線查詢（非常重要）
連線查詢就是求出多個表的乘積，例如t1連線t2，那麼查詢出的結果就是t1*t2。
連線查詢會產生笛卡爾積，假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴充套件到多個集合的情況。
那麼多表查詢產生這樣的結果並不是我們想要的，那麼怎麼去除重複的，不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關係，那麼就通過關聯關係去除笛卡爾積。
你能想像到emp和dept表連線查詢的結果麼？emp一共14行記錄，dept表一共4行記錄，那麼連線後查詢出的結果是56行記錄。
也就你只是想在查詢emp表的同時，把每個員工的所在部門資訊顯示出來，那麼就需要使用主外來鍵來去除無用資訊了。
使用主外來鍵關係做為條件來去除無用資訊
|
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; |
在多表查詢中，在使用列時必須指定列所從屬的表，例如emp.deptno表示emp表的deptno列。
上面查詢結果會把兩張表的所有列都查詢出來，也許你不需要那麼多列，這時就可以指定要查詢的列了。
|
SELECT emp.ename,emp.sal,emp.comm,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno; |
還可以為表指定別名，然後在引用列時使用別名即可。
|
SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;其中AS是可以省略的 |
多表連線查詢執行機制：多表查詢的過程可以理解為一個巢狀迴圈的過程。
for t in teacher //依次遍歷teacher表中的每一條記錄
{
for s in student //依次遍歷student表中的每一條記錄
{
if(s.java_teacher=t.teacher_id) //當滿足連線條件的時候輸出連個表連線後的結果
Output s+t
}
}
2.1 內連線
上面的連線語句就是內連線，但它不是SQL標準中的查詢方式，可以理解為方言！SQL標準的內連線為：
|
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno; |
INNER可以省略，MySQL預設的連線方式就是內連線
不使用WHERE，而是使用ON
內連線的特點：查詢結果必須滿足條件。例如我們向emp表中插入一條記錄：
其中deptno為50，而在dept表中只有10、20、30、40部門，那麼上面的查詢結果中就不會出現“張三”這條記錄，因為它不能滿足e.deptno=d.deptno這個條件。
On子句：將連線的條件放在on子句中，而且每個on子句只能指定一個連線條件。如果需要連線N個表，則需要有N-1個join-on對。
2.2 外連線（左連線、右連線）
外連線的特點：查詢出的結果存在不滿足條件的可能。
左連線：
|
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno; |
OUTER可以省略
左連線是先查詢出左表（即以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。
這麼說你可能不太明白，我們還是用上面的例子來說明。其中emp表中“張三”這條記錄中，部門編號為50，而dept表中不存在部門編號為50的記錄，所以“張三”這條記錄，不能滿足e.deptno=d.deptno這條件。但在左連線中，因為emp表是左表，所以左表中的記錄都會查詢出來，即“張三”這條記錄也會查出，但相應的右表部分顯示NULL。
2.3 右連線
右連線就是先把右表中所有記錄都查詢出來，然後左表滿足條件的顯示，不滿足顯示NULL。例如在dept表中的40部門並不存在員工，但在右連線中，如果dept表為右表，那麼還是會查出40部門，但相應的員工資訊為NULL。
|
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno; |
連線查詢心得：
連線不限與兩張表，連線查詢也可以是三張、四張，甚至N張表的連線查詢。通常連線查詢不可能需要整個笛卡爾積，而只是需要其中一部分，那麼這時就需要使用條件來去除不需要的記錄。這個條件大多數情況下都是使用主外來鍵關係去除。
兩張表的連線查詢一定有一個主外來鍵關係，三張表的連線查詢就一定有兩個主外來鍵關係，所以在大家不是很熟悉連線查詢時，首先要學會去除無用笛卡爾積，那麼就是用主外來鍵關係作為條件來處理。如果兩張表的查詢，那麼至少有一個主外來鍵條件，三張表連線至少有兩個主外來鍵條件。
3 自然連線
大家也都知道，連線查詢會產生無用笛卡爾積，我們通常使用主外來鍵關係等式來去除它。而自然連線無需你去給出主外來鍵等式，它會自動找到這一等式：
l 兩張連線的表中名稱和型別完全一致的列作為條件，例如emp和dept表都存在deptno列，並且型別一致，所以會被自然連線找到！
當然自然連線還有其他的查詢條件的方式，但其他方式都可能存在問題！
|
SELECT * FROM emp NATURAL JOIN dept;內連線 SELECT * FROM emp NATURAL LEFT JOIN dept;左連線 SELECT * FROM emp NATURAL RIGHT JOIN dept;右連線 |
Using子句：用於顯示指定兩個表中的同名列，並將其作為連線條件。假設兩個表中有查過一列的同名列，如果使用自然連線(natural join),則會把所有的同名列當成連線條件，使用using子句，就可以顯示指定那些同名列作為連線條件。
4 子查詢（非常重要）
一個select語句中包含另一個完整的select語句。
子查詢就是巢狀查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那麼就是子查詢語句了。
l 子查詢出現的位置：
Ø where後，作為條為被查詢的一條件的一部分；
Ø from後，作表；
l 當子查詢出現在where後作為條件時，還可以使用如下關鍵字：
Ø any
Ø all
l 子查詢結果集的形式：
Ø 單行單列（用於條件）
Ø 單行多列（用於條件）
Ø 多行單列（用於條件）
Ø 多行多列（用於表）
練習：
1. 工資高於JONES的員工。
分析：
查詢條件：工資>JONES工資，其中JONES工資需要一條子查詢。
第一步：查詢JONES的工資
|
SELECT sal FROM emp WHERE ename=`JONES` |
第二步：查詢高於甘寧工資的員工
|
SELECT * FROM emp WHERE sal > (${第一步}) |
結果：
|
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename=`JONES`) |
2、查詢與SCOTT同一個部門的員工。
l 子查詢作為條件
l 子查詢形式為單行單列
3、工資高於30號部門所有人的員工資訊
分析：
SELECT * FROM emp WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30);
查詢條件：工資高於30部門所有人工資，其中30部門所有人工資是子查詢。高於所有需要使用all關鍵字。
第一步：查詢30部門所有人工資
|
SELECT sal FROM emp WHERE deptno=30; |
第二步：查詢高於30部門所有人工資的員工資訊
|
SELECT * FROM emp WHERE sal > ALL (${第一步}) |
結果：
|
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30) 大於所有 |
l 子查詢作為條件
l 子查詢形式為多行單列（當子查詢結果集形式為多行單列時可以使用ALL或ANY關鍵字）
4、查詢工作和工資與MARTIN（馬丁）完全相同的員工資訊
分析：
查詢條件：工作和工資與MARTIN完全相同，這是子查詢
第一步：查詢出MARTIN的工作和工資
|
SELECT job,sal FROM emp WHERE ename=`MARTIN` |
第二步：查詢出與MARTIN工作和工資相同的人
|
SELECT * FROM emp WHERE (job,sal) IN (${第一步}) |
結果：
|
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename=`MARTIN`) |
5、有2個以上直接下屬的員工資訊
SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);
l 子查詢作為條件
l 子查詢形式為單行多列
5、查詢員工編號為7788的員工名稱、員工工資、部門名稱、部門地址
分析：（無需子查詢）
查詢列：員工名稱、員工工資、部門名稱、部門地址
查詢表：emp和dept，分析得出，不需要外連線（外連線的特性：某一行（或某些行）記錄上會出現一半有值，一半為NULL值）
條件：員工編號為7788
第一步：去除多表，只查一張表，這裡去除部門表，只查員工表
|
SELECT ename, sal FROM emp e WHERE empno=7788 |
第二步：讓第一步與dept做內連線查詢，新增主外來鍵條件去除無用笛卡爾積
|
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.deptno=d.deptno AND empno=7788 |
第二步中的dept表表示所有行所有列的一張完整的表，這裡可以把dept替換成所有行，但只有dname和loc列的表，這需要子查詢。
第三步：查詢dept表中dname和loc兩列，因為deptno會被作為條件，用來去除無用笛卡爾積，所以需要查詢它。
|
SELECT dname,loc,deptno FROM dept; |
第四步：替換第二步中的dept
|
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788 |
l 子查詢作為表
l 子查詢形式為多行多列
有一種子查詢可以返回多行多列，此時where子句中應當有對應的資料列，並使用圓括號將多列資料括起來。
Select *
From Student
Where (Student_id,Student_name) in (
Select teacher_id,teacher_name
From teacher
);
6、自連線:自己連線自己，起別名
求7369員工編號、姓名、經理編號和經理姓名
SELECT e1.empno , e1.ename,e2.mgr,e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369;
練習：
求各個部門薪水最高的員工所有資訊
select e.* from emp e,
–部門最高工資
(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal