資料庫基礎知識
1.認識主鍵、外鍵、超鍵和候選鍵
主鍵：對資料對象進行唯一的辨別，是個資料列或者屬性的組合。
外鍵：在一個表中存在另一個表的主鍵叫外鍵。
超鍵：在全系中可以唯一辨別元組的屬性或屬性集。
候選鍵：最小的超鍵，即沒有備援的超鍵。
元組：
屬性：
2.資料庫事務的四個特性
事務：
完整性限制：
原子性 Atomicity：整個事務的所有操作，要麼全部完成，要麼全部不完成，不可以停滞在某個操作中。一旦事務在執行中出現錯誤，會被復原到事務開始前的狀态（可以聯系linux中的快照來了解）。
一緻性 Correspondence：在事務開始之前和事務執行之後，資料庫的完整性限制沒有被破壞。
隔離性 Isolation：如果有兩個事務同時執行，執行相同的功能時，隔離性就保證這兩個事務是串行執行的，隻允許在同一時間内隻有一個請求在通路同一資料。
持久性 Durability：在事務完成後，該事務對資料庫所做的更改會持久的儲存在資料庫中，不會被復原。
3.視圖
視圖時虛拟的表，與資料表不一樣，隻包含動态檢索資料的查詢。
視圖可以有和表一樣的方式操作它。
視圖不能被索引，建立視圖的指令：create view XXX as XXXXXX;
4.drop,delete和truncate的差別
drop直接删除表 truncate删除表中資料，再插入時自增張id又從1開始 delete删除表中資料
（1）delete從表中每次删除一行，且作為事務記錄在日志中，可以復原。它是DML（data maintain language），delete操作會被放到rollback segement中，事務送出後才生效，如果有相應的trigger，則執行的時候會被觸發。truncate table則一次性從表中删除全部資料，不做日志記錄，也不能復原，在執行删除的操作時不會激活與表有關的删除觸發器，執行速度快。它同drop語句都是DLL（data define language），操作會立即生效。
（2）對表和索引的空間有影響。當表被truncate後，這個表和索引占用的空間會恢複到初始大小，而delete操作不會減少表或索引所占的空間，drop操作則将表所占的空間全釋放掉。
（3）一般而言，drop > truncate > delete。
（4）應用範圍。truncate隻能對table進行操作，delete可以操作table和view。
（5）truncate和delete隻能删除表中資料，drop可以删除整個表（資料和結構）。
新行辨別：
（6）truncate與不帶where的delete：隻删除表資料，保留表結構，其列、限制、索引等不會改變，新行辨別所用的計數值重置為該列的種子（truncate），辨別計數值保留（delete）；drop則将二者都删除，并删除依賴該表的限制（constrain）、觸發器（trigger）、索引（index），存儲過程/函數将被保留，但其狀态會變為：invalid。
（7）在沒有備份的 情況下，謹慎使用drop和delete語句。要删除部分資料采用delete且注意結合where來限制影響範圍。要删除表用drop；若想保留表而将資料删除，如果與事務無關，可用truncate實作；如果與事務有關且想觸發trigger，則使用delete。
（8）truncate table 執行速度快切效率高。truncate與不帶where的delete功能一樣，二者均删除表中所有的資料。但truncate table使用系統和事務日志資源少，它通過釋放存儲表資料所用到的資料也來删除資料，并隻在事務日志中記載資料頁的釋放，而delete每次删除一行都要在事務日志中為所删除的行記錄一項。
（9）對于由外鍵限制引用的表，不能使用truncate table，而應使用不帶where的delete語句。由于truncate table不記錄在日志中，不能激活觸發器。
5.索引
資料索引，是一種用于排序的資料結構，協助快速查詢、更新資料庫表中的資料。
除資料之外，資料庫還維護着滿足特定查找算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實作進階查找算法。這些資料結構就是索引。
為表設定索引要付出代價的：一是增加了資料庫的存儲空間，二是在插入和修改資料時要花費較多的時間（因為索引要随之變動）。
圖展示；額一種可能的索引方式。左邊是資料庫，右邊是資料記錄的實體位址（注意：邏輯上相鄰的記錄在磁盤上并不一定是實體相鄰的）。
為了加快字段Col2的查找，可以維護一個右邊所示的二叉樹，每個節點分别包含索引鍵值和一個指向對應資料記錄實體位址的指針，這樣就可以利用二叉樹查找O（log2n）的複雜度内擷取相應的資料。
建立索引可以大大提高系統的性能。
第一，通過建立唯一索引，看保證資料庫表中每一行資料的唯一性，如學号。
第二，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
第三，可以加速表和表之間的連接配接，特别是在實作資料的參考完整性方面特别有意義。
第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，通過使用索引，可以在查詢過程中，使用優化隐藏器，提高系統的性能。
增加索引也會帶來不利的一面。
第一，建立索引和維護索引要耗費時間，這種時間随着資料的增加而增加。
第二，除了資料表占資料空間外，索引也要占用實體空間，如果要建立聚簇索引，那麼需要的空間就會更大。
第三，當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，這樣就降低了資料的維護速度。
索引是建立在資料表中的某些列的上面的。在建立索引的時候，應該考慮在哪些列上建立索引，在那些列上不能建立索引。一般來說，應該在這些列上建立索引：
在經常需要搜尋的列上可以加快搜尋的速度；
在作為主鍵的列上，強制該列的唯一性群組織表中資料的排序結構；
在經常用在連接配接的列上，這些列主要是一些外鍵，可以加快連接配接的速度；
在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
在經常粗腰拍訊的列上建立索引，因為索引已經排序，這樣的查詢可以利用索引的排序，加快排序查詢的速度；
在經常使用在where子句法人列上建立索引，加快條件的判斷速度。
同樣，對于一些列不應該建立索引，不該建立的索引的這些列具備以下特點：
第一，對于那些在查詢中很少使用或者參考的列不應該建立索引。
第二，對于那些隻有很少資料值的列也不應該增加索引。
這是因為，由于這些列的取值很少，列入人事表中的性别列，在查詢的結構中，結果集的資料航占了表中資料行的很大比例，即需要在表中搜尋的資料行的比列很大。增加索引，并不能明顯的加快檢索速度。
第三，對于那些定義為text,mage和bit資料類型的列不應該增加索引。這是因為，這些列的資料量要麼相當地大，要麼取值很少。
第四，當修改性能遠遠大于檢索性能時，不應該建立索引。這是因為，修改性能和檢索性能時互相沖突的。當增加索引是，會提高檢索性能，但會降低修改性能。當減少索引是，會提高修改性能，降低檢索性能。是以，當修改性能遠遠大于檢索性能時，不應該建立索引。
根據資料庫的功能，可在資料庫設計器中建立三種索引：唯一索引、主鍵索引和聚集索引。
唯一索引：
不允許其中任意兩行具有相同索引值的索引。
當現有資料中存在重複的鍵值時，大多數資料庫不允許将新建立的唯一索引與表一起儲存。資料庫還可能防止添加将在表中建立重複鍵值的新資料。例如，如果在employee表中職員的姓（name）上建立了唯一索引，則任何兩個員工都得不能同姓。
主鍵索引：
資料庫表中經常有一列或列組合，其值唯一表示表中的每一行。該列稱為表的主鍵。在資料庫關系圖中為表定義主鍵将自動建立主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都是唯一的。當在查詢中使用主鍵索引時，它允許對資料的快速通路。
聚集索引：
在聚集索引中，表中行的實體順序和鍵值的邏輯順序是相同的，一個表隻能包含一個聚集索引。
如果某索引不是聚集索引，則表中行的實體順序與鍵值的邏輯順序不比對。與非聚集索引相比，聚集索引通常提供更快的資料通路速度。
6.局部性原理與磁盤預讀
由于存儲媒體的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分之一，是以為了提高效率，要盡量減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，二是每次都會預讀，即使隻需要一個位元組，磁盤也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是計算機科學中著名的局部性原理：當一個資料被用到時，其附近的資料通常也會馬上被使用。程式運作期間所需的資料通常比較集中。
由于磁盤順序讀取的效率很高（不需要尋道時間，隻需很少的旋轉時間），是以對于具有局部性的程式來說，預讀可以提高I/O效率。
預讀的長度一般為頁（page）的整倍數。頁是計算機管理存儲器的邏輯塊，硬體及作業系統往往将主存和磁盤存儲分割為連續的大小相等的塊，每個存儲塊稱為一頁（在許多作業系統中，頁的大小通常為4K），主存和磁盤一頁為機關交換資料。當程式要讀取的資料不在記憶體中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信号，磁盤會找到資料的起始位置并并向後讀取一頁或者幾頁載入記憶體中，當然異常傳回，程式繼續運作。
7.B-/+Tree索引的性能分析
到這裡可以分析B-/+Tree索引的性能了。
上文說過一般使用磁盤I/O次數來評價索引結構的優劣。先從B-/+Tree分析，根據B-Tree的定義，可知檢索一次最多需要通路h個結點。資料庫系統的設計者巧妙利用了磁盤預讀原理，将一個節點的大小設為一頁大小，這樣每個節點隻需要一次I/Oj就可以完全載入。我可達到這個目的，在實際實作B-Tree還需要使用如下技巧：
每次建立節點時，直接申請一個頁的空間，這樣就保證一個節點實體上也存儲在一個頁裡，加之計算機存儲配置設定都是按頁對齊的，就實作了一個node隻需一次I/O。
B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logdN)。一般實際應用中，出度是非常大的數字，通常超過100，是以h非常小（通常不超過3）。
出度：
而紅黑樹這種結構，h明顯要深的多。由于邏輯上很近的節點（父子）實體上可能很遠，無法利用局部性，是以紅黑樹的I/O漸進複雜度也為O(h)，效率明顯比B-Tree差很多。
8.連接配接的種類
查詢分析器中執行：
--建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,\'lee\'
insert into table1 select 2,\'zhang\'
insert into table1 select 4,\'wang\'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
-------------------------------------------------
table1 | table2 |
-------------------------------------------------
id name |id score |
1 lee |1 90|
2 zhang| 2 100|
4 wang| 3 70|
-------------------------------------------------
外連接配接：包括左外聯接、右外聯接和完整外部聯接。
左外聯接：left join、left outer join
1）其結果集包括left outer 子句中指定的左表的所有行，而不僅僅是聯接列所比對的行。如果左表的某行在右表中沒有比對的行，則在相關聯的結果集中右表的所有清單均為空值（null）。
2）sql 語句：
select * from table1 left join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
-----------------------------
注釋：包含table1的所有子句，根據指定條件傳回table2相應的字段，不符合的以null顯示
右連接配接：right join、right outer join
1）右外聯接是與左外聯接的反向聯接。将傳回右表的所有行。如果右表的某行在左表中沒有比對的行，則将為左表傳回空值（null）。
2）sql 語句
select * from table1 right join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
NULLNULL370
------------------------------
注釋：包含table2的所有子句，根據指定條件傳回table1相應的字段，不符合的以null顯示
完整外部聯接：full join、join outer join
1）完整外部聯接：傳回左表和右表所有的行。當某行在另一個表中沒有比對行時，則另一個表的選擇清單列包含空值。如果表之間有比對行，則整個結果集行包含基表的資料值。
2）sql 語句
select * from table1 full join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
-----------------------------
注釋：傳回左右連接配接的和（見上左、右連接配接）
内連接配接（有where語句）：用比較運算符比較要聯接列的值的聯接。join、inner join
1）sql 語句
select * from table1 join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
------------------------------
注釋：隻傳回符合條件的table1和table2的列
2）等價
A.select a.*,b.* from table1 a, table2 b where a.id = b.id
B.select * from table1 across join table2 where table1.id = table2.id （注：cross join後加條件隻能用where，不能用on）
完全連接配接：（沒有where語句，又叫交叉連接配接）,cross join
1）沒有where語句的交叉聯接将産生笛卡爾積。第一個表的行數乘以第二個表的行數等于笛卡爾積結果集的大小。
2）sql語句
select * from table1 cross join table2
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
------------------------------
注釋：傳回3*3=9條記錄，即笛卡爾積
3）等價（與下列執行效果相同）
A:select * from table1,table2
9.資料庫範式
第一範式（1NF）
在任何關系資料庫中，第一範式（1NF）是對關系模式的基本要求，不滿足第一範式（1NF）的資料庫就不是關系資料庫。
所謂第一範式（1NF）是指資料庫表的每一列都是不可再分割的基本資料項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重複的屬性。如果出現重複的屬性，就可能需要定義一個新的實體，新的實體由重複的屬性組成，新實體與原實體之間為一對多關系。在第一範式（1NF）中表的每一行隻包含一個執行個體的資訊。
簡而言之，第一範式就是沒有重複的列。
第二範式（2NF）
建立在第一範式基礎之上，即滿足第二範式必須先滿足第一範式。第二範式要求資料庫表中的每個執行個體（行）必須可以被唯一地區分。為了實作區分通常需要加上一個列，以存儲各個實體的唯一辨別。這個唯一屬性列被稱為主關鍵字或主鍵、主碼。
第二範式（2NF）要求實體的屬性完全依賴于主關鍵字。完全依賴是指不能存在僅一部分屬性依賴關鍵字，如果存在，那麼這些屬性應該和主關鍵字這一部分分離出來組成出來形成新的實體，新實體與原實體之間是一對多的關系。為了實作區分通常需要為表加上一個列，以存儲各個實體的唯一辨別。
簡而言之，第二範式（2NF）即使非主屬性非部分依賴主關鍵字。
第三範式（3NF）
滿足第三範式（3NF）必須先滿足第二範式（2NF）。第三範式要求一個資料庫表不包含已在其它表中包含的非主屬性關鍵字。
例如，在一個部門資訊表中，其中每個部門有部門編号，部門名稱，部門簡介等，那麼在員工表中包含部門編号這一屬性就好了，不用再将部門名稱、部門簡介等與部門有關的資訊再加入員工表中了。如果不存在部門表，則根據第三範式應該建構它，否則就會與大量的資料備援。
簡而言之，第三範式就是屬性不依賴其他非主屬性，即消除備援。
10.資料庫優化的思路
SQL語句優化：
1）避免在where語句中使用!=或者<>操作符，否則引擎會放棄使用索引而進行全表掃描。
2）避免使用在where語句對字段進行是否是null判斷，也會單隻引擎放棄使用索引而進行全表掃描。
select id from t where name is null
可在name上設定預設值為0，確定表中的num列沒有null，然後這樣查詢：
select id from t where name=0
3）很多時候用exists代替in比較好。
4）用where子句代替having子句，因為having子句隻會在檢查所有記錄之後才對結果集進行過濾。
10.資料庫結構優化
1）範式優化：比如消除備援，（節省空間）。
2）反範式優化：比如适當加備援等（減少join）。
3）拆分表：分區将資料在實體上分割開，不同分區的資料可以制定存在處于不同磁盤上的資料檔案中。這樣，對表進行查詢時，指需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處于不同磁盤的分區也将對這個表的資料傳輸分散在不同的磁盤I/O，一個精心設定的分區可以将資料傳輸對磁盤I/O競争均勻地分散開。對資料量大的資料表可采取此方法。可按月自動建立表分區。
4）拆分又分垂直拆分和水準拆分
案例：1.産品表（資料量10w，穩定）， 2.訂單表（資料量200w，且有增長趨勢），3.使用者表（資料量100w，且有增長趨勢）。以mysql為例講述垂直拆分和水準拆分。mysql能容忍的數量級在百萬級靜态資料可以到千萬靜态資料。
垂直拆分
解決的問題：表與表之間的io競争 不解決的問題：單表中資料量增長出現的壓力
方案:把産品表和使用者表放到一個server上，訂單表單獨放到一個server上。
水準拆分
解決的問題：單表中資料增長帶來的壓力
不解決的問題：表與表之間的io競争
方案：使用者表通過性别拆分男使用者表和女使用者表，訂單表通過已完成和完成中拆分成：已完成訂單表、未完成訂單表。産品表和未完成訂單表放在一個server上，已完成訂單表和男使用者表放在一個server上，女使用者表放在一個server中（女的愛購物）。
伺服器硬體優化
多花錢。
11.存儲過程和觸發器的差別
觸發器與存儲過程很相似，二者都是SQL語句集，它們的唯一差別是觸發器不能用execute語句調用，而是在使用者執行Transact-SQL語句是自動觸發（激活）執行。
觸發器：是在一個修改了指定表中資料時執行的存儲過程。通常通過建立觸發器來強制實作不同表中的邏輯相關資料的引用完整性和一緻性。由于 使用者不能繞過觸發器，是以可以用它來強制實施複雜的業務規則，以確定資料的完整性。觸發器不同于存儲過程，觸發器主要是通過事件而執行觸發而被執行的，而存儲過程可以通過存儲過程名稱來直接調用。當對某一表進行諸如update、insert、delete操作時，sql server就會自動執行觸發器所定義的SQL語句，進而確定對資料的處理必須符合這些SQL語句所定義的規則。
