HeadFirstSQL:學習筆記2/2

 　　第七章 多表設計

　　1.外鍵是表中的某一列，它引用到另一個表的具有唯一性的鍵，一般是主鍵。外鍵用於確認一張表中的行與另一張表中的行相對應。

　　2.外鍵引用的主鍵稱之為父鍵，父鍵所在的表稱之為父表。

　　3.外鍵不唯一，可以為null，為null表示在父表中沒有相應地主鍵。

　　4.引用完整性：插入外鍵列的值必須在父表的來源列中。外鍵能夠保證引用完整性，這是外鍵重要的功能。

　　5.約束定義了表中的某些規則，防止表結構遭到破壞。

　　6.定義外鍵：

　　create table nickname(

　　id int not null auto_increment primary key,

　　nname varchar(30) not null，

　　player_id int);

　　mysql> alter table nickname add constraint players_id_fk foreign key(player_id)

　　references players(id);

　　這裡注意：外鍵的類型要和主鍵的類型相同，否則會出現errno150錯誤。

　　7.表之間的關系

　　一對一：用的很少

　　一對多：通過外鍵連接

　　多對多：復雜，通過連接表junction table存儲兩張表的主鍵

　　組合鍵：多個鍵構成主鍵

　　8.范式：

　　第一范式(1NF)。規則1：數據列只包含有原子性的值(1列同時存儲多個信息字段)。規則2：沒有重復的數據組(多列存儲相同類型字段，如color1列，color2列)。

　　第二范式(2NF)。規則1：先符合1NF。規則2：沒有部分函數依賴性(列1、2是組合主鍵，如列3根據列1的數據產生，但與列2無關，稱為列3部分依賴)。

　　u 函數依賴：一列的值根據另一列的值的改變而改變。

　　u 部分函數依賴：非主鍵的列依賴於主鍵的一部分。

　　u 傳遞函數依賴：任意非鍵列與另一非鍵列有關聯。

　　滿足1NF，並且主鍵列只有一列，一定符合2NF。

　　第三范式(3NF)。規則1：首先符合2NF。規則2：沒有傳遞函數依賴性。

　　關於三個范式的理解(以下內容引用自：

　　http://blog.csdn.net/famousdt/article/details/6921622)：

　　◆ 第一范式(1NF)：強調的是列的原子性，即列不能夠再分成其他幾列。

　　考慮這樣一個表：【聯系人】(姓名，性別，電話)

　　如果在實際場景中，一個聯系人有家庭電話和公司電話，那麼這種表結構設計就沒有達到 1NF。要符合 1NF 我們只需把列(電話)拆分，即：【聯系人】(姓名，性別，家庭電話，公司電話)。1NF 很好辨別，但是 2NF 和 3NF 就容易搞混淆。

　　◆ 第二范式(2NF)：首先是 1NF，另外包含兩部分內容，一是表必須有一個主鍵;二是沒有包含在主鍵中的列必須完全依賴於主鍵，而不能只依賴於主鍵的一部分。

　　考慮一個訂單明細表：【OrderDetail】(OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName)。

　　因為我們知道在一個訂單中可以訂購多種產品，所以單單一個 OrderID 是不足以成為主鍵的，主鍵應該是(OrderID，ProductID)。顯而易見 Discount(折扣)，Quantity(數量)完全依賴(取決)於主鍵(OderID，ProductID)，而 UnitPrice，ProductName 只依賴於 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的設計容易產生冗余數據。

　　可以把【OrderDetail】表拆分為【OrderDetail】(OrderID，ProductID，Discount，Quantity)和【Product】(ProductID，UnitPrice，ProductName)來消除原訂單表中UnitPrice，ProductName多次重復的情況。

　　◆ 第三范式(3NF)：首先是 2NF，另外非主鍵列必須直接依賴於主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況。

　　考慮一個訂單表【Order】(OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity)主鍵是(OrderID)。

　　其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主鍵列都完全依賴於主鍵(OrderID)，所以符合 2NF。不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID(非主鍵列)，而不是直接依賴於主鍵，它是通過傳遞才依賴於主鍵，所以不符合 3NF。

　　通過拆分【Order】為【Order】(OrderID，OrderDate，CustomerID)和【Customer】(CustomerID，CustomerName，CustomerAddr，CustomerCity)從而達到 3NF。

　　第二范式(2NF)和第三范式(3NF)的概念很容易混淆，區分它們的關鍵點在於，2NF：非主鍵列是否完全依賴於主鍵，還是依賴於主鍵的一部分;3NF：非主鍵列是直接依賴於主鍵，還是直接依賴於非主鍵列。

　　第八章 聯接與多張表的操作

　　1.使用AS將查詢結果作為數據插入新表。Create table new (id int not null auto_increment primary key, profession varchar) as select profession from mytable group by profession order by profession;

　　2.使用AS添加別名。Select profession AS my_profes from my_contacts;AS可以省略。

　　3.交叉聯接：

　　Select t.toy, b.boy from toys as t cross join boys as b; cross join 返回兩張表的每一行相乘(笛卡爾乘)的結果。

　　4.inner join利用條件式裡的比較運算符結合兩張表;

　　Select boys.boy, toys.toy from boys inner join toys on boys.toy_id = toys.toy_id;相等聯接

　　Select boys.boy, toys.toy from boys inner join toys on boys.toy_id<>toys.toy_id;不等聯接

　　Select boys.boy, toys.toy from boys nature join toys;自然聯接(兩張表中含有相同列名的相等內連接)

　　第九章 子查詢

　　1.子查詢：被另一個查詢包圍的查詢，也可稱之為內層查詢。

　　select interest from interest as inte where inte.playerID in (select play

　　erID from interest);

　　2.子查詢通常與as和連接一起使用，提高查詢效率。

　　3.In/notin與exists/not exists比較：

　　摘自：http://blog.csdn.net/ldl22847/article/details/7800572

　　in 是把外表和內表作hash 連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢。一直以來認為exists比in效率高的說法是不准確的。

　　如果查詢的兩個表大小相當，那麼用in和exists差別不大。

　　如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：

　　例如：表A(小表)，表B(大表)

　　a：

　　select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引;

　　select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。

　　相反的

　　b：

　　select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引;

　　select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。

　　not in 和not exists如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引;而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。

　　in 與 =的區別

　　select name from student where name in ('zhang','wang','li','zhao');

　　與

　　select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

　　的結果是相同的。

　　第十章 外聯接、內聯接與聯合

　　1.左外聯接：匹配左表中的每一行及右表中符合要求的行。

　　Select g.girl,b.boy from girls as g left out join toys as t on g.toy_id = t.toy_id;

　　2.外聯接與內聯接的區別是：外聯接一定能夠返回結果集數據行(找不到相符合的返回NULL)，結果行數等於右表行數。

　　3.右外聯接：right out join，結果行數等於左表函數。

　　4.自引用外鍵：self-referencing foreign key，出於其他目的而引用同一張表的主鍵。

　　5.自聯接：適用於含有子引用外鍵的表，將單一表當成兩張具有完全相同信息的表進行查詢。使用inner join完成查詢。表cc的boss_id自引用外鍵id。

　　Select c1.name,c2.name as boss from cc c1 inner join cc c2 on c1.boss_id = c2.id;

　　6.union：聯合，組合查詢結果集;

　　Select profession from A union select profession from B;相同profession只出現一次

　　Select profession from A union all select profession from B;相同profession出現多次

　　7.使用union創建新表：任何select都可以創建新表

　　Create table test as select profession from A union select sex from B;

　　8.intersect:交集

　　9.Except:差集

　　第十一章 約束、視圖和事務

　　1.約束：例如check(限定允許插入某個列的值，coin char(1) check in(‘A’,’B’,’C’)),not null, primary key, foreign key, unique等。

　　添加約束：alter table mytable add constraint check gender in (‘M’,’F’);

　　2.視圖：虛擬數據表

　　創建視圖:create view webdesign AS select name,sex from table1 nature join table2 where table1.id = table2.id;

　　查看視圖：select * fro