第十一章 資料庫設計與權限設定.

Presentation on theme: "第十一章 資料庫設計與權限設定."— Presentation transcript:

1 第十一章 資料庫設計與權限設定

2 章節目錄 建立資料庫 建立資料表之前談MySQL資料型態 建立資料表 帳號權限調整 本章結論 重點提示 問題與討論

3 建立資料庫 以終端機登入的方式建立資料庫 以phpMyAdmin網頁登入方式建立資料庫 返回章節目錄

4 以終端機登入的方式建立資料庫-1/3 登入的方式有兩種： 第一種是輸入「mysql –u帳號 –p密碼」，密碼會顯示。
預設目前只有root這個帳號，而密碼預設為「phpmysql」，請以「mysql –uroot -pphpmysql」方式登入系統，若密碼不同請自行更換。 若已有其他具資料庫管理權限的帳號，也可使用其他帳號登入。

5 以終端機登入的方式建立資料庫-2/3 MySQL預設提供「mysql」與「test」這兩個資料庫： 「mysql」資料庫是系統資料庫，不能移除。 「test」資料庫是公用資料庫，提供給大家測試之用。 建立資料庫之前可先使用「show databases;」瞭解系統 目前有幾個資料庫。而建立一個資料庫的語法為「create database 資料庫名稱;」。 欲建立一個名為「pcschool」的資料庫，請用「create database pcschool;」語法建立。

6 以終端機登入的方式建立資料庫-3/3 返回建立資料庫

7 以phpMyAdmin網頁登入方式建立資料庫-1/3
請建立「pcschool2」這個資料庫，而MySQL連線校對請選「utf8-general_ci」，再按下「建立」即可。

8 以phpMyAdmin網頁登入方式建立資料庫-2/3

9 以phpMyAdmin網頁登入方式建立資料庫-3/3
「phpMyAdmin」是一套管理MySQL Server的PHP網頁系統，「phpMyAdmin」不是「MySQL Sever」，兩者不能混為一談。 連線校對代表字元編碼的比較，基本上只要與您預設的資料庫編碼相同就可以。 MySQL在4.0版之前預設的編碼是拉丁，當時MySQL不做編碼檢查，網頁丟什麼資料過來，MySQL就照單全收。 MySQL 4.1版之後不僅預設編碼為utf8，而且可在資料庫、資料表、欄位分別設定不同的編碼，而資料傳遞過程中也會檢查編碼。這樣做的目的是可讓資料庫儲存不同語系的資料，而MySQL檢查編碼的動作也可讓資料接收或送出時能確保資料編碼的正確性，所以在MySQL 4.1之後才會有「連線校對」的項目出現。 MySQL在各階段是如何處理資料的編碼，這將於第十六章介紹。 返回建立資料庫

10 建立資料表之前談MySQL資料型態 建立資料庫之後就可建立資料表，而建立資料表時必須建立欄位。
我們在建立資料表之前，得先瞭解欄位的資料型態，否則資料表就 無法順利的建立。 MySQL的資料形態可分為幾大類：數字、文字、日期時間、列舉等 資料形態。 數字型態 日期時間型態 字串型態 列舉型態 返回章節目錄

11 數字型態-1/4 數字型態的資料，有多個型態可以選擇。 數字類型裡標示「L」指的是「整數位數」，最大為255。
數字類型裡標示「D」指的是「小數位數」。 除decimal(L,D)型態外，其他資料型態沒有強制規定要加上L或D。 而unsigned屬性代表可設定為正整數，若設定時加上「zerofill」代 表表示位數不足補0，如int(5)話，且設為zerofill，則存49這個數字， 資料庫會將之存成00049。

12 數字型態-2/4 tinyint若為正整數型態也只能到255，所以適合超小數值資料，例如：成績。
smallint就比tinyint大多了，正整數型態可到六萬多，所以適合小數值資料，例如：物品價錢。 mediumint 的正整數可以計算到1600萬左右的數字，適合中型數值，例如：公司銷售金額統計。 int的正整數已經可以用到42億左右的數字，您可評估網站資料是否需要用到「億」這樣的單位。 如果int的範圍還不夠，bigint的正整數已經可以用到18萬兆這麼大的數字，除非您的資料有使用到這麼大的單位，否則建議建立資料表時不要設計成這樣的欄位。

13 數字型態-3/4 float能夠記錄小數點，例如成績計算就可用此欄位設計。
double和float的用途一樣，但double所用掉的空間是float的兩倍，除非特別需要高精度或範圍極大的值，一般來說用float來儲存資料就夠了。 decimal類型不同於float和double，其有效的取值範圍由L和D的值決定。如果改變L而固定D，則其取值範圍將隨L的變大而變大。 整數型態的欄位可以加上auto_increment屬性。當您為整數型態欄位加上這個屬性後，MySQL就會每次新增記錄時為這個欄位指定「目前資料表內該欄位最大值」加1，設定為auto_increment屬性欄位通常設定為資料表的主索引。

14 數字型態-4/4 auto_increment屬性具有以下的特性：
這個屬性在「Not Null」、「Primary Key」或「Unique」這三種屬性至少具備一種的時候才能使用。（「Primary Key」及「Unique」將在11-3-1節及14-4節介紹） 一個資料表只能有一個auto_increment屬性。 只有在新增紀錄時，在沒有明確指定數值及沒有指定Null值情況下才會自動產生新編號。您可以手動為新紀錄指定數值，只要您指定的數值沒有被用掉就可以。 返回建立資料表之前談MySQL資料型態

15 日期時間型態-1/5 日期時間型態的欄位，可表示年、月、日、時間。
在以下的儲存方式介紹裡，Y代表「年」，YY代表「2位數的年」，如08年，YYYY代表「4位數的年」，如2008年，M代表「月」，D代表「日」，h代表「小時」，m代表「分」，s代表「秒」。 這裡將日期時間類型做成一張表如下：

16 日期時間型態-2/5 date類型可儲存日期資料，若欲輸入的日期為「2009年10月12日」，以下的輸入方式均可接受：
2009/10/12 09/10/12 年月日之間有可以辨識的分隔符號就可以接受 或請依序輸入完整的年月日資料，例如「 」則系統也可接受。

17 日期時間型態-3/5 datetime類型可儲存日期時間資料。
若欲輸入的日期時間為「2005年1月28日早上3點58分」，以下的輸入方式均可接受： 2005/01/28 03:58:00 年月日時分秒之間有可以辨識的分隔符號就可以接受 請依序輸入完整的年月日時分秒資料，例如「 」則系統也可接受。

18 日期時間型態-4/5 timestamp與datetime類型相似，但timestamp可以設定時間顯示的寬度：

19 日期時間型態-5/5 time類型只儲存時間資料，若欲輸入的時間為「15點7分20秒」，以下的輸入方式均可接受：
15:07:20 150720 只要時分秒之間有可以辨識的分隔符號就可以接受。 但若只有輸入「1135」，那麼會被當成「00:11:35」，換言之，若有位數不足的情況下，MySQL會自動在前方補0。 若欲輸入11時35分，該輸入什麼資料？請參考本書11-X頁說明。 返回建立資料表之前談MySQL資料型態

20 字串型態 字串型態的欄位設定均蠻相似的，請先參考字串型態之基本規格。 char與varchar text與blob blob限制與解決
返回建立資料表之前談MySQL資料型態

21 字串型態：char與varchar-1/4 char是固定長度字元資料型態。 char(5)則只能儲存5個字元資料，若超出範圍則無法儲存。
char(5)的欄位存入「pcschool」， 則只剩下「pcsch」這五個字。 如果資料內容少於設定儲存的長度，char會自動補上空白字元。 無論存入的內容長短為何，char所需的儲存空間都是固定的。 使用這種資料型態時請您確定字串不會超過某個範圍，例如IP都是固定15個位元組。

22 字串型態：char與varchar-2/4 varchar是個長度可變的型態。我們就舉以下的例子，來比較一下這兩 者的差異：

23 字串型態：char與varchar-3/4 varchar(5)欄位遇到「abcdefg」字串，該欄位只能存「abcde」。
若varchar(5)欄位儲存「abc」字串，會自動縮小所需空間，只要4個 bytes就夠了。 varchar與char最多只能設定到255。

24 字串型態：char與varchar-4/4 varchar與各種text與blob都是長度可變的型態。
當資料表中同時選用 cahr 和這類長度可變的型態時，char會被自動改 為 varchar，除非它的最大儲存長度少於 4。 若資料表中varchar 欄位的最大儲存長度少於 4 時，其型態也會被自動 改為char。 為什麼char與varcharchar在4bytes會自動作修改？請參考本書11-X頁 說明。 varchar與char的優點是什麼？請參考本書11-X頁說明。 返回字串型態

25 字串型態：text與blob 各種text欄位類型均適合用來儲存大容量資料，例如討論區、最新消息等等。除了空間大小不一樣，其餘皆相同。
blob或text在MySQL 3.23以後版本中可以進行索引，不過因為blob與text的內容甚多，且內容會因為更新等因素進行變更，筆者傾向不建議使用blob或text做索引，因為內容變化很大，若做為索引，反倒容易拖垮系統執行的速度。 返回字串型態

26 字串型態：blob限制與解決-1/3 blob欄位類型來儲存上傳的檔案在碰到檔案過大問題，畢竟65KB是不夠用的。但如果規劃成mediumblob欄位，雖可儲存大型圖檔，但有幾個限制必須解決。 1.MySQL資料庫傳送的封包大小限制：MySQL資料庫傳送的封包大小的限制預設是1Mb，所以雖將資料表欄位的型態設為mediumblob，傳遞超過1Mb的資料仍會發生錯誤，必須修改MySQL設定才能支援。請開啟主機內的my.ini（Windows環境）或my.cnf（Linux環境），修改以下片段，將傳輸的值改為4M，設定檔修改完成後請記得重新啟動MySQL Server。

27 字串型態：blob限制與解決-2/3 2.網頁傳送資料大小限制：一般而言，我們是透過網頁將資料送到MySQL資料庫內，所以雖然修改了MySQL設定，可以傳送的封包大小變大，但網頁處理仍會發生錯誤情形。 因此請開啟主機內的php.ini，修改以下的片段，將上傳資料的檔案大小改為4M，設定檔修改完成後請記得重新啟動WWW Server。

28 字串型態：blob限制與解決-3/3 如果傳輸資料時經常出現逾時（Timeout）回應，建議您也檢視主機 內的httpd.conf，將Timeout的時間加大（單位為秒），以避免逾時 情況發生，但不建議調整太大，免得網頁主機負擔過重。設定檔修 改完成後請記得重新啟動WWW Server。 返回字串型態

29 列舉型態 列舉（enumeration）型態可分為enum與set兩種。
enum是單選選項，您自己預設一些內容，該欄位只能存入您所設定 的內容之一。固定且單一答案的選項，適合使用enum資料欄位。 set和enum的差別，除了容量大小不同外（enum型態最多可以建立 個不同的選項，而 set 型態只能建立 64 個不同的選項。）， 另一個差異是set可以複選。 返回建立資料表之前談MySQL資料型態

30 建立資料表 以終端機登入型式建立 在phpMyAdmin 內以輸入SQL語法建立資料表 於phpMyAdmin內新增資料瞭解資料型態的差異
返回章節目錄

31 以終端機登入型式建立-1/5 當您以指令方式登入MySQL系統後，您可以輸入「show databases;」瞭解目前系統裡已經安裝了哪些資料庫，再請輸入「use 資料庫;」語法選擇資料庫，選擇資料庫後就可輸入「show tables;」瞭解這個資料庫內有哪些資料表。 建立資料表的方式如下：

32 以終端機登入型式建立-2/5 「欄位名稱」和「資料類型」都是必需的。 「欄位設定選項」則是 視情形存在。 常見的「欄位設定選項」有以下這些：

33 以終端機登入型式建立-3/5 前面提到的「索引」，指的是index，index是什麼呢？index就如同 書籤，可讓您加快資料的搜尋。
Primary與Unique兩種索引將於第十四章為各位分析介紹。

34 以終端機登入型式建立-4/5 MySQL資料庫引擎有MyISAM與InnoDB兩種常用的類型：

35 以終端機登入型式建立-5/5 請依本書第11-12頁在「pcschool」內建立一個「gbook」資料表， 這個資料表內所需要的欄位如下：
返回建立資料表

36 在phpMyAdmin 內以輸入SQL語法建立資料表
請於pcschool資料庫內建立資料表「test2」，而資料表內欄位規劃 如下表。步驟為： 1.請於phpMyAdmin左側點選「pcschool」資料庫。 2.請點選上方的SQL語法。 3.請依本書第11-13頁輸入SQL語法。 4.請按下「執行」鈕。 返回建立資料表

37 於phpMyAdmin內新增資料瞭解 資料型態的差異-1/4
pcschool資料庫的test2資料表四個欄位分屬於不同類型的欄位。 請您於phpMyAdmin內點選「pcschool」資料庫後點選「test2」資 料表，再請點選上方的「新增」，您會看到新增一筆資料的欄位清 單。而日期型態資料可透過點選方式選取，blob型態資料則以檔案上 傳的方式加入資料。

38 於phpMyAdmin內新增資料瞭解 資料型態的差異-2/4
3 1 2 1.點選「pcschool」資料庫 2.點選「test2」資料表 3.請點選上方的「新增」。

39 於phpMyAdmin內新增資料瞭解 資料型態的差異-3/4

40 於phpMyAdmin內新增資料瞭解 資料型態的差異-4/4
若輸入的資料超過原規劃欄位長度，資料會被裁切。 test1欄位規劃為 varchar(10)型態，所以只保留10個字 test2欄位規劃為tinyint(3)型態，而tinyint型態資料範圍 為-128到 127。 返回建立資料表

41 在phpMyAdmin 內以逐一建立欄位方式 建立資料表-1/4
請依本書第11-16頁在「pcschool」內建立一個「 vote 」資料表， 這個資料表內所需要的欄位如下：

42 在phpMyAdmin 內以逐一建立欄位方式 建立資料表-2/4
請於phpMyAdmin內點選「pcschool」資料庫名稱後於 右方視窗「建立新資料表於資料庫 pcschool」處輸入資料表 名稱為「vote」，欄位數量為「8」後按下右邊的「執行」 鈕進入資料表欄位設計。 請逐一輸入欄位的名稱、選擇型態、輸入長度、預設值設定等各種 設定後，最後按下「執行」鈕就可以建立一個新的資料表。

43 在phpMyAdmin 內以逐一建立欄位方式 建立資料表-3/4
1 點選「pcschool」資料庫名稱 4 2 3 輸入資料表 名稱為「vote」 欄位數量為「8」

44 在phpMyAdmin 內以逐一建立欄位方式 建立資料表-4/4
返回建立資料表

45 帳號權限調整 由上一章可知，mysql資料庫內五個資料表與使用者帳號權限有關。
若由phpMyAdmin網頁點選調整帳號權限，得在不同網頁間切換而頗 為不便，因此在此說明終端機如何新增使用者與賦予權限。 新增帳號與權限調整 資源限制 移除權限與刪除使用者 返回章節目錄

46 新增帳號與權限調整-1/13 以MySQL管理者root身份登入MySQL後，您可以使用「grant on」指 令新增使用者與權限的調整。
grant 權限 on 資料庫.資料表 to identified by ‘密碼’

47 新增帳號與權限調整-2/13 權限可分為「使用者層級權限」 與「管理者層級權限」共計十五項。「使用者層級權限」為以下的列表，共有八種：

48 新增帳號與權限調整-3/13 「管理者層級權限」共有七種，如以下列表：

49 新增帳號與權限調整-4/13 新增一個使用者帳號為「pcschool」，由localhost登入，擁有所有 的權限，密碼為 「phpmysql」，請依本書第11-19頁語法建立使用者帳號。 這個帳號可以看到所有的資料庫，包含「mysql」資料庫。

50 新增帳號與權限調整-5/13

51 新增帳號與權限調整-6/13 新增一個使用者帳號為「php1」，由localhost登入，擁有「board」資料庫上所有的權限，密碼為 「mysqlstart1」，請依本書第11-21頁語法建立使用者帳號。 此時並無「board」資料庫，所以當php1由phpMyAdmin登入，會 看到什麼畫面呢？

52 新增帳號與權限調整-7/13

53 新增帳號與權限調整-8/13 由於php1這個帳號只給予board資料庫權限，若系統內無board資料 庫時則右邊視窗「建立新資料庫」會自動加上「board」而希望您來 建立。 假設您另外建立資料庫，例如「test2」，phpMyAdmin會給您「Access denied for user to database ‘test2’」 錯誤訊息，通知您因沒有權限所以不可以建立其他名稱資料庫。

54 新增帳號與權限調整-9/13 「grant on」指令可協助帳號新增權限。
請新增「test2」資料庫的所有權現給php1，依本書第11-22頁語法 新增使用者權限帳號。 請重新登入php1帳號，就可以建立「test2」這一個資料庫。 請php1於test2資料庫內建立資料表「test2」，SQL語法同 節。稍後建立新帳號測試是否可讀取test2資料庫內test2資料表。

55 新增帳號與權限調整-10/13 新增一個使用者帳號為「php2」，由localhost登入，擁有「test2」 資料庫上「test」資料表的所有權限，密碼為 「mysqlstart2」，請 依本書第11-23頁語法建立使用者帳號。 由於php2帳號只有test2資料庫上test資料表的所有權限，所以php2 帳號登入後看不到test2資料庫上test2資料表。

56 新增帳號與權限調整-11/13 test2資料庫真的沒有資料表嗎？

57 新增帳號與權限調整-12/13 新增一個使用者帳號為「php3」，由localhost登入，在所有資料庫 上只有select權限，密碼為 「mysqlstart3」」，請依本書第11-23頁 語法建立使用者帳號。 以php3帳號登入系統後，因沒有「新增資料」權限，所以當您要新 增資料時會產生錯誤。

58 新增帳號與權限調整-13/13 返回帳號權限調整

59 資源限制-1/5 我們也可以在設定帳號權限後針對這個帳號使用MySQL進行資源 限制。MySQL提供以下資源限制的方法。
請留意資源限制的對象是帳號（account）而不是使用者端（client），且資源限制必須以更新的方式增加使用者資源限制。設 定之後若要取消資源限制，請將該項資源限制為0。

60 資源限制-2/5 限制「php1」這個帳號每一個小時只能執行3次，請依本書第11-25 頁語法限制帳號資源使用。
一個小時內執行超過3次，帳號就不能登入。

61 資源限制-3/5 請將剛剛設定的「每一個小時只能執行3次」設定取消，以避免之後執行發生問題。
請依本書第11-25頁語法取消限制帳號資源使用。

62 資源限制-4/5 若限制「php1」這個帳號每一個小時只能更新3次，請依本書第11- 25頁語法限制帳號資源使用。
登入到資料庫後請嘗試進行多次更新，當您進行至第三次時系統將 會出現警告訊息阻止您再使用。

63 資源限制-5/5 若限制「php1」這個帳號每小時可以連結Server的次數為3次，並且取消「一個小時只能執行3次」及「每一個小時只能更新3次」限制，請依本書第11-26頁語法限制帳號資源使用。 請開啟多個瀏覽器分頁視窗，您會看到以下的錯誤訊息。代表連線 時不能超過三次。 返回帳號權限調整

64 移除權限與刪除使用者 -1/6 請以MySQL管理者root身份登入後，您可以使用「revoke on」指令移除使用者權限。
revoke 權限 on 資料庫.資料表 from 請取消pcschool在資料庫裡delete權限，請依本書第11-27頁語法移除 權限。

65 移除權限與刪除使用者 -2/6 以pcschool帳號登入後請進行刪除資料的動作，會產生 錯誤訊息。

66 移除權限與刪除使用者 -3/6 若取消pcschool帳號所有權限，請依本書第11-27頁語法 移除所有權限。
這個帳號看不到剛剛所建立的資料庫，也無建立資料庫 的權限。這個帳號無法對資料庫做任何動作。

67 移除權限與刪除使用者 -4/6

68 移除權限與刪除使用者 -5/6 「revoke on」指令只能移除帳號權限，但無法將帳號刪除。 若要將帳號刪除，請執行以下步驟：
1 以MySQL管理者root身份登入MySQL，若root密碼為phpmysql， 請以「mysql –uroot -pphpmysql」方式登入。 2 執行「use mysql」指令使用mysql資料庫。 3 執行「delete from user where user='pcschool'; 」刪除使用者。 4 執行「flush privileges; 」指令使系統重新整理。 5 執行「exit」指令離開系統。

69 移除權限與刪除使用者-6/6 返回帳號權限調整

70 本章結論 在PHP存取MySQL之前，請先規劃好資料庫及資料表。資料表設計建議可依循以下的原則：
1.資料表內不應該包涵多餘或重複的資料，如果您需要反覆輸入相 同的資料，代表資料表設計需做調整。 2.資料表命名應該有意義，不應該是class1、class2等方式命名。 3.資料表需要的儲存空間應該愈小愈好。 4.建議在正式上線之前先做各種資料的新增刪除更新測試，確保資 料在更動時不會產生資料遺失的風險。 建議各位可參考以上的原則來規劃設計您的資料表。資料庫必須有 資料才有其意義，資料來源有自行輸入或匯入已有MySQL資料。下 一章將說明如何做好資料的匯出與匯入。 返回章節目錄

71 重點提示-1/3 登入的方式有兩種，第一種是輸入「mysql –u帳號 –p密碼」若不 希望密碼顯示，請改用「mysql –u帳號 -p」，當您按下enter後才 可輸入密碼。 終端機登入後可用「show databases;」指令顯示目前系統已安裝的 資料庫。 終端機登入後可用「create database 資料庫名稱;」指令建立資料 庫。 MySQL的資料形態可分為幾大類：數字、文字、日期時間、特殊類 等資料形態。

72 重點提示-2/3 auto_increment屬性具有以下的特性：
1.這個屬性在「Not Null」、「Primary Key」或「Unique」這三種屬性至 少具備一種的時候才能使用。 2.一個資料表只能有一個auto_increment屬性。 3.只有在新增紀錄時，在沒有明確指定數值及沒有指定Null值情況下才會自 動產生新編號。您可以手動為新紀錄指定數值，只要您指定的數值沒有被 用掉就可以。 char資料型態是固定長度字元，而varchar是個長度可變的型態。 索引有Primary Key、Unique、Index、Fulltext四種。

73 重點提示-3/3 權限可分為「使用者層級權限」與「管理者層級權限」共計15項。
新增帳號權限方法為「grant 權限 on 資料庫.資料表 to identified by ‘密碼’」。 移除帳號權限方法為「revoke 權限 on 資料庫.資料表 from 主機 」。 返回章節目錄

74 問題與討論-1/2 1.mysql終端機登入方式有哪幾種？ 2.以終端機方式登入系統後如何顯示現有資料庫名稱 及建立資料庫？
2.以終端機方式登入系統後如何顯示現有資料庫名稱 及建立資料庫？ 3.請說明欄位的auto_increment屬性特性。 4.請建立一個資料表，名稱為「list」，內有三個欄位，設定如下：

75 問題與討論-2/2 5.請列舉四種帳號權限。 6.請新增一個使用者帳號為「test1」，由localhost登入，擁有 「phptest」資料庫上所有的權限，密碼為 「testphp1」。 7.請新增一個使用者帳號為「test2」，由localhost登入，在所有資 料庫上只有delete權限，密碼為 「testphp2」。 8.請限制「test1」這個帳號每一個小時只能執行5次(由localhost登 入，密碼為 「testphp1」)。 9.請移除「test1」帳號select權限。 10.請刪除「test2」帳號。 返回章節目錄