程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> HeadFirstSQL:學習筆記2/2

HeadFirstSQL:學習筆記2/2

編輯:關於SqlServer

       第七章 多表設計

      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 * from webdesign;

      刪除視圖:drip view webdesign;

      3.事務:transaction完成一組工作的sql,所有步驟必須能夠全部完成,否則不完成任何一項任務。

      事務過程:start transaction->執行sql語句->commit/rollback

      顯示創作數據表代碼:show create table players;

      必須使用支持事務的存儲引擎:InnoDB和BDB.

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

      第十二章 安全性

      1.設定用戶密碼:set password for ‘root’@’localhost’ = password(‘aaa’);

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

      3.授權:grant select on table1 to conan

      4.撤銷權限:revoke select on table1 from conan

      5.創建角色:create role data_entry;

      6.授權:grant select,update,insert on table1 to data_entry;

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

      8.刪除角色:drop role data_entry;

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved