第七章 多表設(shè)計

1.外鍵是表中的某一列，它引用到另一個表的具有唯一性的鍵，一般是主鍵。外鍵用于確認(rèn)一張表中的行與另一張表中的行相對應(yīng)。

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

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

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

5.約束定義了表中的某些規(guī)則，防止表結(jié)構(gòu)遭到破壞。

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);

這里注意：外鍵的類型要和主鍵的類型相同，否則會出現(xiàn)errno150錯誤。

7.表之間的關(guān)系

一對一：用的很少

一對多：通過外鍵連接

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

組合鍵：多個鍵構(gòu)成主鍵

8.范式：

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

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

u 函數(shù)依賴：一列的值根據(jù)另一列的值的改變而改變。

u 部分函數(shù)依賴：非主鍵的列依賴于主鍵的一部分。

u 傳遞函數(shù)依賴：任意非鍵列與另一非鍵列有關(guān)聯(lián)。

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

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

關(guān)于三個范式的理解(以下內(nèi)容引用自：

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

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

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

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

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

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

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

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

◆ 第三范式(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)從而達(dá)到 3NF。

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

第八章 聯(lián)接與多張表的操作

1.使用AS將查詢結(jié)果作為數(shù)據(jù)插入新表。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.交叉聯(lián)接：

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

4.inner join利用條件式里的比較運算符結(jié)合兩張表;

Select boys.boy, toys.toy from boys inner join toys on boys.toy_id = toys.toy_id;相等聯(lián)接

Select boys.boy, toys.toy from boys inner join toys on boys.toy_id<>toys.toy_id;不等聯(lián)接

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

第九章 子查詢

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

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 是把外表和內(nèi)表作hash 連接，而exists是對外表作loop循環(huán)，每次loop循環(huán)再對內(nèi)表進(jìn)行查詢。一直以來認(rèn)為exists比in效率高的說法是不準(zhǔn)確的。

如果查詢的兩個表大小相當(dāng)，那么用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 那么內(nèi)外表都進(jìn)行全表掃描，沒有用到索引;而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。

in 與 =的區(qū)別

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'

的結(jié)果是相同的。

第十章 外聯(lián)接、內(nèi)聯(lián)接與聯(lián)合

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

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

2.外聯(lián)接與內(nèi)聯(lián)接的區(qū)別是：外聯(lián)接一定能夠返回結(jié)果集數(shù)據(jù)行(找不到相符合的返回NULL)，結(jié)果行數(shù)等于右表行數(shù)。

3.右外聯(lián)接：right out join，結(jié)果行數(shù)等于左表函數(shù)。

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

5.自聯(lián)接：適用于含有子引用外鍵的表，將單一表當(dāng)成兩張具有完全相同信息的表進(jìn)行查詢。使用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：聯(lián)合，組合查詢結(jié)果集;

Select profession from A union select profession from B;相同profession只出現(xiàn)一次

Select profession from A union all select profession from B;相同profession出現(xiàn)多次

7.使用union創(chuàng)建新表：任何select都可以創(chuàng)建新表

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

8.intersect:交集

9.Except:差集

第十一章 約束、視圖和事務(wù)

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.視圖：虛擬數(shù)據(jù)表

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

查看視圖：select * from webdesign;

刪除視圖：drip view webdesign;

3.事務(wù)：transaction完成一組工作的sql，所有步驟必須能夠全部完成，否則不完成任何一項任務(wù)。

事務(wù)過程：start transaction->執(zhí)行sql語句->commit/rollback

顯示創(chuàng)作數(shù)據(jù)表代碼：show create table players;

必須使用支持事務(wù)的存儲引擎：InnoDB和BDB.

改變存儲引擎：alter table yourtable TYPE=InnoDB;

第十二章 安全性

1.設(shè)定用戶密碼：set password for ‘root’@’localhost’ = password(‘a(chǎn)aa’);

2.添加新用戶：create user conan identified by ‘conanswp’;

3.授權(quán)：grant select on table1 to conan

4.撤銷權(quán)限：revoke select on table1 from conan

5.創(chuàng)建角色:create role data_entry;

6.授權(quán)：grant select,update,insert on table1 to data_entry;

7.使用角色：grant data_entry to conan;

8.刪除角色：drop role data_entry;

更多信息請查看IT技術(shù)專欄