6 MySQL 資料庫及資料表建立 維護 技能指標 1. 了解 MySQL 資料型別 2. 了解如何使用 phpmyadmin 建立資料庫 資料表 3. 了解 phpmyadmin 的管理及維護功能 學習總結 完成本章後 您將了解如何搭配 phpmyadmin 方便好用的工具, 來管理 MySQL

6 MySQL 資料庫及資料表建立 維護 技能指標 1. 了解 MySQL 資料型別 2. 了解如何使用 phpmyadmin 建立資料庫 資料表 3. 了解 phpmyadmin 的管理及維護功能 學習總結 完成本章後 您將了解如何搭配 phpmyadmin 方便好用的工具, 來管理 MySQL 資料庫, 在不需要熟記太多的語法情況下, 您一樣能夠方便的管理好 MySQL 資料庫 ; 這是一個強大而且免費用的工具, 希望您可以好好用善加利用它強大而且完善的功能

MySQL 資料庫及資料表建立 維 了解 MySQL 資料型別 跟程式語言一樣,MySQL 提供了三種基本的資料型別, 另外 MySQL 也提供了欄位 (Field) 設定子 (Column Modifier), 它可以用來定義資料欄位的屬性, 欄位設定子包含了 自動增量 (Auto Increment) 無號數(Unsigned) 主要索引值 (Primary Key) NULL 值 (NULL) 不可為 NULL(Not Null) 二位元 (Binary) 等 資料型別一 : 數值資料 護6-2 表 6-1 數值資料 整數 型態型別名稱 占用空間 最小值 最大值 ( 有號數 / 無號數 ) ( 有號數 / 無號數 ) TINYINT 1bytes -128 127 0 255 SMALLINT 2bytes -32768 32767 0 65535 MEDIUMINT 3bytes -8388608 8388607 0 16777215 INT 4bytes -2147483648 2147483647 INTEGER 0 4294967295 BIGINT 8bytes -9223372036854775808 9223372036854775807 0 18446744073709551615 注意事項 : 當兩個運算元都是 BIGINT 時, 在進行四則運算 (+,-,*,/) 時, 皆全部使用 BIGINT 來進行運算, 因此若運算結果超出 BIGINT 可容納範圍上限時會發生問題, 同樣的情況也會發生在其它整數型別中

近似符點精確符點 表 6-2 數值資料 符點數 型態型別名稱 占用空間 有號數範圍 FLOAT(M,D) 8bytes 依變數值 M 而定 FLOAT 4bytes 無 DOUBLE(M,D) M+2bytes 依變數值 M 而定 REAL(M,D) DECIMAL(M,D) M+2bytes 依變數值 M 而定 NUMERIC(M,D) 注意事項 : 所謂的符點數, 也就是可以用來存放含有小數的數字,MySQL 在符點數的處理區分為 近似符點 與 精確符點 兩種, 近似符點所占用的空間較小, 但是會有誤差值產生, 精確符點占用的空間較大, 因為它使用字元形式存放數值, 因此不會有誤差值產生 FLOAT DOUBLE DECIMAL 皆有 (M,D) 的用法, 其中 (M,D) 表示資料長度,M 用來表示該型別最大位數 ( 長度 ),D 用來表示小數點位數 資料型別二 : 字元或字串字串是由字元所集合而成, 除了需要宣告型別之外, 還要宣告所存放資料的長 度大小, 請看底下表 6-3 的說明 表 6-3 字元或字串型別 型別名稱 最大長度 ( 集合 ) 占用空間 CHAR(M) 0<= M <=255 M bytes VARCHAR(M) 0<= M <= 65535 L+1 bytes L <= M( 註 ) BINARY(M) 0 <= M <= 255 Mbytes VARBINARY(M) 0<= M <= 255 L+1bytes L <= M TINYTEXT,TINYBLOG L < 28 L+1 bytes TEXT,BLOB L < 216 L+2 bytes MEDIUMTEXT,MEDIUMBLOB L < 224 L+3 bytes LONGTEXT,LONGBLOB L < 232 L+4bytes ENUM(' 值 1, 值 2, ) 由值的數量決定, 最多不 1,2byte 超過 65536 個值 SET(' 值 1, 值 2, ) 由值數量決定, 最多不得超過 64 值 1,2,4,8bytes 02 03 04 05 06 07 08 09 10 11 12 13 14 15 6-3

MySQL 資料庫及資料表建立 維 VARCHAR BLOB 與 TEXT 是變動長度型別 每個型別的儲存長度決定 在, 實際資料的長度 ( 如表 6-3 中的 L), 而不是該型別的最大可能長度 例 如 :VARCHAR(10) 可以容納最大長度為 10 個字的字串, 但是實際儲存的需求 是字串長度 (L), 再加上一個字元 如果字串值為 " gjun ", 那麼 L 是 4, 儲存時 需要 L+1bytes 為 5bytes 資料型別三 : 其它型別 日期及時間型別, 是可以分別分開使用, 或是合併使用皆可, 方便達到您可 單獨需要存放日期 (DATE), 或僅僅需要時間 (TIME), 當然也可您需要日期及時間 (DATETIME) 甚至於您需要時間戳記 (TIMESTAMP) 等, 皆可滿足您的需求 護6-4 表 6-4 日期及時間型別 型別名稱 " 零 值 DATETIME ' 0000-00-00 00:00:00' DATE '0000-00-00' TIMESTAMP 00000000000000 TIME '00:00:00' YEAR 0000 ' 零 ' 值是特殊值, 您可以使用 '0' 0 的方式來使用 欄位設定值 (Column Modifier) 如表 6-5 將欄位 (Field) 可用的欄位設定值整理如下, 欄位設子是經常會使用到的建議您多注意一下也多練習

表 6-5 欄位設定值列表 設定子名稱 適用資料型別 說明 AUTO_INCREMENT 所有整數型別 自動編號 DEFAULT 除了 預設值 NOT NULL 所有資料型別 不允許空值 NULL 所有資料型別 允許空值 PRIMARY KEY 所有資料型別 主索引鍵值, 不含空值 UNIQUE 所有資料型別 唯一值, 含空值 UNSIGNED 所有整數型別 無號整數 ZEROFILL 所有數值型別 數值自動補 0 BINARY CHAR,VARCHAR 依 ASCII 區分大小寫 02 03 04 05 06 07 08 09 10 11 12 13 14 15 6-5

MySQL 資料庫及資料表建立 維護6-6 了解如何使用 phpmyadmin 建立資料庫 資料表 phpmyadmin 在一開始安裝 AppServ 時, 已經包含了在安裝的項目中, 因此您的 伺服器目前應該是能夠運行 phpmyadmin, 底下我們就來介紹如何透過 phpmyadmin 來進行初期的相關動作 執行 phpmyadmin 管理界面 開啟瀏覽器, 並在網址列輸入 http://localhost, 並點選 圖 6-1 02 如圖 6-2, 請分別輸入 使用者名稱 密碼 後登入 phpmyadmin 系統主畫 面 圖 6-2 phpmyadmin 登入畫面 03 如圖 6-3 所示, 這是 phpmyadmin 管理主畫面, 其中可以清楚知道目前伺服器資 訊以及 phpmyadmin 工具版本

02 03 04 05 06 建立資料庫 圖 6-3 phpmyadmin 系統主畫面 如圖 6-4 所示, 連線校對選取 utf8_unicode_ci, 接著在建立新資料庫中輸入 testdb 後, 按立 建立 按鈕, 即完成資料庫建立 07 08 09 10 11 12 圖 6-4 建立資料庫操作畫面 13 14 15 6-7

MySQL 資料庫及資料表建立 維護6-8 02 如圖 6-5 所示, 資料庫完成建立後, 在畫面中即可看完成訊息, 同時 phpmyadmin 會將建立之 SQL 語法顯示在畫面上, 供我們學習參考 建立資料表 圖 6-5 資料庫建立完成畫面 如圖 6-6 所示, 首先點選您所要使用的資料庫, 在此請點選先前所建立的 testdb 資料庫 圖 6-6 選取資料庫 建立資料表 02 如圖 6-7 所示, 請在 名稱 填入所要建立的資料名稱, 在 欄位數目 填入所 需要的欄位數量, 接著按下 執行 按鈕繼續 圖 6-7 建立資料表

03 如圖 6-8 所示, 請在 欄位 填入需要的名稱後, 並且在 型態 中選取該欄位 所需要存放的類型, 若是字元 ( 串 ) 則需要在 長度集合 中設定長度 ; 完成 後按下 儲存 按鈕繼續 02 03 04 圖 6-8 student 資料表建立結構 04 如圖 6-9 所示, 完成 student 資料表建立後, 接著重覆前面的動作, 再建立 一個 score 資料表, 按下 執行 按鈕繼續 05 06 07 08 09 10 圖 6-9 完成建立資料表 05 如圖 6-10 所示, 跟 Step 3 相同, 請依序完成每一個闌位的設定, 完成後請 按 儲存 繼續 11 12 13 14 圖 6-10 score 資料表建立結構 15 6-9

MySQL 資料庫及資料表建立 維護6-10 匯入資料 如圖 6-11 所示, 請點選 載入 按鈕, 並且依序完成底下的動作 : 1. 載入檔案 : 請按下 瀏覽 按鈕, 找到隨書光碟中所的 學生資料.txt 2. 部份載入 : 維持預設 3. 載入檔案格式 : 請點選 SQL, 其它維持預設 完成後請按下 執行 按鈕繼續作業 圖 6-11 匯入 student 資料 02 如圖 6-12 所示, 匯入完成後, 即在畫面上顯示出 新增列數 xxx( 查詢需時 x.xxxx 秒 ) 訊息, 並且將列出 SQL 相關語法 圖 6-12 匯入完成

03 如圖 6-13 所示, 請點選 載入 按鈕, 並且依序完成底下的動作 : 4. 載入檔案 : 請按下 瀏覽 按鈕, 找到隨書光碟中所的 成績單.txt 5. 部份載入 : 維持預設 6. 載入檔案格式 : 請點選 SQL, 其它維持預設 完成後請按下 執行 按鈕繼續作業 02 03 04 05 06 07 圖 6-13 匯入 score 資料 08 04 如圖 6-14 所示, 匯入完成後, 即在畫面上顯示出 新增列數 xxx( 查詢需時 x.xxxx 秒 ) 訊息, 並且將列出 SQL 相關語法 09 10 11 12 圖 6-14 匯入完成 13 14 15 6-11

MySQL 資料庫及資料表建立 維護6-12 瀏覽記錄 如圖 6-15 所示, 在目前檢視的資料表中, 按下 瀏覽 按鈕, 即可瀏覽該資表 記錄, 同時您也可以任意改變瀏覽方向 筆數 等 查看與變更資料表結構 圖 6-15 瀏覽記錄 如圖 6-16 所示, 在目前檢視的資料表中, 按下 結構 按鈕, 您可以修改 刪 除 新增欄位等功能 接著請在 chi 欄位上點選 修改該欄位, 請繼續下一步 圖 6-16 查看資料表結構

02 如圖 6-17 所示, 請將 chi 欄位的型態變更為 SMALLINT, 接著按下 儲 存 完成修改 請重覆 Step 1, 2 的動作, 分別完成 chi, eng, math 三個欄位的變更 02 03 圖 6-17 修改欄位結構 03 如圖 6-18 所示, 完成 chi, eng, math 三個欄位變更後, 目前的資料表結構應 該跟底下圖形相同 04 05 06 07 08 09 10 11 圖 6-18 修改結構完成結果 12 13 14 15 6-13

MySQL 資料庫及資料表建立 維護6-14 了解 phpmyadmin 的管理及維護功能 前面介紹了如何使用 phpmyadmin 建立資料庫 建立資料表 匯入資料等, 您一 定會用到的重要功能, 接下來 我們將繼續介紹, 如何維護以及管理的重要功能, 您一定不能錯過 SQL 語法 如圖 6-19 所示, 請按下 SQL 按鈕, 就可以在此編輯所有 SQL 語法, 可以搭 配 欄位 列示清單的輔助, 當然也可以自行編輯, 語法完成後請按 執行 按鈕 圖 6-19 SQL 語法編輯 02 如圖 6-20 所示, 您將會看到 Step 1 的 SQL 語法, 並將所有執行結果顯示如 下圖

02 03 圖 6-20 SQL 語法執行結果 03 如圖 6-21 所示, 表示您的 SQL 語法並未正確, 請按下 回上一頁 繼續編輯後 再重新執行 04 05 06 07 08 09 10 圖 6-21 執行 SQL 語法錯誤 11 12 13 14 15 6-15

MySQL 資料庫及資料表建立 維護6-16 搜尋資料 如圖 6-22 所示, 各群組說明如下 : 1. 選擇欄位 : 欲顯示之欄位, 至少選取一個欄位 2. 筆記錄 / 每頁 : 分頁顯示時, 每一頁想要顯示的筆數 3. 顯示次序 : 先選取欲排序欄位, 再選取 遞增 或 遞減 4. 增加檢索條件 : 請直接輸入所想要過濾的條件即可 不需要再加上 where 關 鍵字 完成後請按下 執行 按鈕查看執行結果 圖 6-22 搜尋畫面上半 02 如圖 6-23 所示, 可以針對各欄位進行相關條件設定, 設定完成請按 執行 按 鈕查看執行結果 圖 6-23 搜尋畫面下半

新增記錄 如圖 6-24 所示, 請依序輸入各欄位所需要的值, 接著請按下 執行 按鈕完成 新增記錄 02 03 04 05 06 07 08 輸出記錄 圖 6-24 新增記錄 如圖 6-25 所示, 請在輸出中選取您所想要匯出的資料格式, 在此筆者選取了 XML, 接著請按下 執行 按鈕繼續下一步 09 10 11 12 13 14 15 圖 6-25 輸出格式選擇 6-17

MySQL 資料庫及資料表建立 維護6-18 02 如圖 6-26 所示, 請決定您是否需要存檔, 筆者在此選擇存檔, 請按 儲存 (S) 按鈕繼續下一步 圖 6-26 選擇是否存檔 03 如圖 6-27 所示, 請選擇您欲儲存該 XML 檔案位置, 完成請按下 儲存 (S) 按鈕 繼續下一步 圖 6-27 選擇儲存位置

04 如圖 6-28 所示, 您可以直接將該 XML 檔案, 使用 Dreamweaver 開啟檢視 02 03 04 05 06 07 圖 6-28 Dreamweaver 檢視結果管理 1. 排序 : 如圖 6-29 所示, 修改資料表結構, 並且將該資料表依據所選取欄位進行 遞增 或 遞減 排序 08 09 10 圖 6-29 決定排序欄位 2. 移動資料表 : 如圖 6-30 所示, 選擇目的 資料庫 後, 按下 執行 按鈕, 即可將該資料表搬移至新資料庫中 11 12 13 14 圖 6-30 移動資料表 15 6-19

MySQL 資料庫及資料表建立 維護6-20 3. 變更資料表名稱 : 如圖 6-31 所示, 可以變更資料表名稱 註解 儲存引擎 等 圖 6-31 變更資料表名稱 4. 複製資料表 : 如圖 6-32 所示, 可以將資料表複製到同一資料庫或不同資料庫 ; 接著填入新資料表名稱, 然後選擇複製的方式 只有結構 : 表示沒有記錄, 僅有格式 結構與資料 : 表示包含格式及記錄 只有資料 : 表示複製到已可先建立好的資料表當中 完成後 請按 執行 按鈕完成複製動作 圖 6-32 複製資料表 5. 資料表維護 : 如圖 6-33 所示, 資料表維護提供了日常維護資料表的各種相關功能 檢查資料表 : 檢查一個或多個資料表是否有錯誤 分析資料表 : 用於分析和儲存資料表的關鍵字分佈 修復資料表 : 用於修復被破壞的資料表

最佳化資料表 : 您可以使用 OPTIMIZE TABLE 來重新利用未使用的空 間, 並整理數據檔案的碎片 02 03 圖 6-33 資料表維護 04 清空記錄與刪除資料表 1. 清空記錄 : 如圖 6-34 所示, 當您按下 清空 按鈕後, 系統即再次詢問您是否要清除掉所有記錄 記錄刪除後是無法復原刪除, 請小心使執行 所謂 請空 就是僅刪除記錄, 而不將資料表結構移除 05 06 07 08 圖 6-34 清空記錄確認 2. 刪除資料表 : 如圖 6-35 所示, 當您按下 刪除 按鈕後, 系統即再次詢問您是否要將整個資料表刪除 資料表刪除會連同記錄一併刪除, 請務必小心執行 09 10 11 12 13 圖 6-35 刪除資料表確認 14 15 6-21

MySQL 資料庫及資料表建立 維護6-22 自我測驗 & 能力檢核 完成每一項測驗後, 請在 中打勾 代表完成您已了解 phpmyadmin 操作 請試著完成底下每道練習 我已了解 1. 請試著在 phpmyadmin 建立一個新的資料庫, 名稱為 MyDB 2. 請試著在 MyDB 資料庫中, 建立一個新資料表, 名稱 personal, 資料表結構如下表所示 : 欄位名稱 型態 長度 允許 NULL 否 其它 ID CHAR 10 NOT NULL Primary key NAME CHAR 20 NOT NULL Unique PHONE CHAR 13 NULL BIRTHDAY DATE NULL SEX ENUM 'F','M' NULL HEIGHT TINYINT NULL WEIGHT TINYINT NULL 3. 請試著在 MyDB 資料庫中, 建立一個新資料表, 名稱 friend, 資斗表 結構如下表所示 : 欄位名稱 型態 長度 允許 NULL 否 其它 ID CHAR 10 NOT NULL SERIAL INT NOT NULL auto_increment NAME CHAR 20 NOT NULL CELLPHONE CHAR 13 BIRTHDAY DATE MAIL VARCHAR 60 ADDRESS VARCHAR 60