PHP+MySQL - dywang.csie.cyut.edu.twmysql.pdf · PHP+MySQL Department of Computer Science and...

PHP+MySQL

Department of Computer Science and Information EngineeringChaoyang University of Technology

Taichung, Taiwan, Republic of China

Instructor: De-Yu Wang (王德譽)E-mail: [email protected]

Homepage: http://dywang.csie.cyut.edu.twPhone: (04)23323000 ext 4538

Office: E738

p q r s t u v w x y z { | } ~ ~August 23, 2019

• Instructor: De-Yu Wang

1. Email: [email protected]. Homepage: http://dywang.csie.cyut.edu.tw3. 本文件 pdf 檔下載4. Phone: (04)23323000 ext 45385. Office: E738

• 參考資料

1. PHP Tutorial2. MySQL Tutorial3. PHP 線上程式練習

De-Yu Wang CSIE CYUT i

http://dywang.csie.cyut.edu.tw

https://dywang.csie.cyut.edu.tw/dywang/download/pdf/php+mysql.pdf

http://www.tutorialspoint.com/php

http://www.tutorialspoint.com/mysql

http://www.tutorialspoint.com/execute_php_online.php

De-Yu Wang CSIE CYUT ii

CONTENTS CONTENTS

Contents

1 實機練習系統 11.1 動機 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.2 系統設計 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.3 NAT 設計 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.4 虛擬機管理界面 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.5 系統管理與評分 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.6 證照考試 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.7 上課前先確定三件事 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.8 虛擬機設定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.9 系統伺服器管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

1.9.1 DNS server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.9.2 LDAP server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71.9.3 NFS server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2 簡介 92.1 MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92.2 PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

3 MySQL 安裝 113.1 建立使用環境 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113.2 第一次使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123.3 變更 root 密碼 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143.4 忘記 root 密碼 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153.5 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

4 MyCLI 194.1 前言 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194.2 安裝於 CentOS 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194.3 安裝於 CentOS 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214.4 使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234.5 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

5 MySQL 管理 275.1 建立用戶 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275.2 刪除用戶 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315.3 Server 允許遠端存取 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325.4 Client 遠端存取 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345.5 mysqld 設定檔 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345.6 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

De-Yu Wang CSIE CYUT iii

CONTENTS CONTENTS

6 PHP 安裝 396.1 建立使用環境 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396.2 PHP MySQL 語法 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426.3 PHP 輸出到檔案 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446.4 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

7 Database 資料庫 457.1 產生資料庫 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457.2 選取工作資料庫 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477.3 刪除資料庫 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487.4 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

8 Tables 資料表 518.1 資料型態 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518.2 產生資料表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528.3 刪除資料表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548.4 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

9 Query 紀錄詢問 579.1 Insert 新增紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579.2 Select 查詢資料表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599.3 Where 條件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619.4 Update 變更紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639.5 Delete 刪除紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659.6 Like 條件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 679.7 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

10 Clause 紀錄詢問條件 7110.1 Sorting 排序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7110.2 Join 結合查詢 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7210.3 NULL 值 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7410.4 REGEXP 正規表示 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7510.5 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

11 ALTER 語法 8111.1 ALTER 簡介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8111.2 ADD 新增 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8111.3 MODIFY 修改 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8211.4 CHANGE 改變 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8211.5 DROP 刪除 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8311.6 SET DEFAULT 設定預設值 . . . . . . . . . . . . . . . . . . . . . . . . . 8311.7 RENAME 重新命令 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8411.8 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

12 KEY 及 INDEX 8712.1 Index 簡介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8712.2 Primary key 主鍵 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8712.3 組合主鍵 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8812.4 Unique key 不重複鍵 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

De-Yu Wang CSIE CYUT iv

CONTENTS CONTENTS

12.5 Index 索引鍵 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9312.6 組合索引鍵 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9412.7 Sequence 序列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9612.8 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

13 其他 10113.1 Clone Tables 複製資料表 . . . . . . . . . . . . . . . . . . . . . . . . . . . 10113.2 Metadata 後設資料 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10313.3 Temporary 暫時資料表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10513.4 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

14 Duplicates 重複紀錄 10914.1 避免重複紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10914.2 計算重複紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11114.3 不重複輸出重複紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11214.4 刪除重複紀錄 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11314.5 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

15 SQL Injection 注入 11915.1 前言 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11915.2 SQL 注入查詢 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11915.3 限制輸入字元 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12015.4 PHP 避免 SQL 注入 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12115.5 SQL injection 實例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12215.6 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

16 Export 匯出 12516.1 匯出到檔案 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12516.2 匯出 Raw Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12616.3 匯出資料庫 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12816.4 匯出到其他主機 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12916.5 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130

17 Import 匯入 13117.1 LOAD DATA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13117.2 LOAD DATA 指定格式 . . . . . . . . . . . . . . . . . . . . . . . . . . . 13217.3 LOAD DATA 指定欄位 . . . . . . . . . . . . . . . . . . . . . . . . . . . 13317.4 mysqlimport . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13317.5 資料表還原 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13517.6 資料庫還原 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13717.7 實機操作練習題 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138

18 問題與解決 14118.1 自動補齊 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

De-Yu Wang CSIE CYUT v

CHAPTER 1. 實機練習系統

Chapter 1

實機練習系統

1.1 動機

1. 教學若有實際環境，讓學生實機操作練習，可以大大提昇學習成效。

2. Linux 實機練習，很容易因學生操作錯誤，而造成系統損壞。例如：硬碟分割、格式化及管理的練習。

3. 實機練習結束或操作不當造成當機，系統必須可以迅速還原，才能重複練習。

4. 系統必須可以批次快速修改或更新。

5. 系統必須可以依據學生實際操作狀況，批次進行檢查或評分。

6. 虛擬機練習所需伺服器資源，例如：LDAP 網路帳號、YUM repository 資料庫伺服器、NFS 分享、NTP 校時伺服器、ISCSI 硬碟分享、內部網域 DNS 伺服器、FTP 資源下載…等環境必須架設好，才能提供虛擬機做各種狀況的練習。

1.2 系統設計

1. Linux 教學系統設計

(a) 客製化 Linux 系統。

(b) Linux 伺服器架設。

(c) 虛擬機設計。

2. Linux 教學系統安裝

(a) E517 電腦教室：方便學生上課使用。

De-Yu Wang CSIE CYUT 1

http://dywang.csie.cyut.edu.tw/moodle23/dywang/customlinux/

http://dywang.csie.cyut.edu.tw/moodle23/dywang/linuxserver/

1.3. NAT 設計 CHAPTER 1. 實機練習系統

(b) 個人隨身碟：插上隨身碟，以安裝光碟開機，不需要任何設定，自動安裝於隨身碟，不會影響電腦中的硬碟。

(c) LiveCD：以 LiveCD 光碟開機，開機後即可使用，只是所有操作與設定都是暫存，重新開機後即回到原始狀態。

1.3 NAT 設計1. NAT 架設??：電腦教室網路設置、管控學生上課上網??。

2. 在 Linux，一行命令即可完成設定；在 Windows，Honda 老師亦幫忙撰寫批次檔。控制方式：

3. 任何設定皆不管控老師機，老師機可可自由上網。

4. 學生機上網方式：

(a) 自由上網(b) 不能上網(c) 限制只能上學內網(d) 限制只能上某一網站(e) 限制只能上「依老師設定」的部分網站(f) 只有某一學生機可自由上網

5. 不管老師做任何網路設定，下課後系統自動恢復成所有電腦皆可自由上網，以避免因老師忘了復原，而影響下節上課的老師。

1.4 虛擬機管理界面

1. 設計虛擬機管理 GUI 程式，方便學生管理虛擬機。程式包含虛擬機重安裝、還原、重新啟動及終端界面等四個功能。

(a) Rebuild KVM：重新安裝 KVM 虛擬機，約 10 幾分鐘。


1.4. 虛擬機管理界面 CHAPTER 1. 實機練習系統

(b) Revert KVM：還原 KVM 虛擬機，約幾秒鐘到幾分鐘，請耐心等侯，不要在還原過程按管理界面的其他功能，以免造成虛擬機損壞。

(c) Reboot KVM：重新啟動 KVM 虛擬機。(d) KVM Console：打開 KVM 虛擬機終端畫面。

2. 系統桌面中黑色背景視窗為 KVM 虛擬機操作界面。原始 KVM 虛擬機主機名稱為 111111，因已練習過，主機也已被設定成 kvm8，點擊 Revert KVM，幾秒後就可還原回原始狀態重新練習。

3. 在終端機以指令 virt-view 直接開啟虛擬機，出現錯誤訊息。�1 [dywang@dywH ~]$ sudo virt-viewer kvmusb

No protocol specified3 Cannot open display:

Run ’virt-viewer --help’ to see a full list of available command lineoptions� �

4. 先檢查 Xwindow 認證變數 $XAUTHORITY 存不存在？�[dywang@dywH ~]$ echo $XAUTHORITY� �

5. 變數 XAUTHORITY 不存在，必須先設定 XAUTHORITY=~/.Xauthority 後再使用sudo 啟動虛擬機，才能正常啟動。


1.5. 系統管理與評分 CHAPTER 1. 實機練習系統

�[dywang@dywH ~]$ XAUTHORITY=~/.Xauthority sudo virt-viewer kvmusb� �

1.5 系統管理與評分

1. 系統管理腳本程式自動掃瞄目前開機的電腦，選擇要進行的工作，輸入密碼後即可自動完成。�1 [root@dywH ~]# e517sshpass.sh

myip=1043 hosts=103,105

q(quit) iscsi halt reboot c(clear) l(lvm) rhcsa rhce5 > t(mounttest) k(kvmreboot) v(kvmrevert) s(servicerestart)

> vi1 c1 lpvar c999 sh999 hp(hostpatch)? c9997 keyin remote host root password:� �

2. 預設工作項目：

(a) q(quit)：退出，無動作。

(b) iscsi：學生機 ISCSI 硬碟分享架設。

(c) halt：學生機關機。

(d) reboot：學生機重新啟動。

(e) c(clear)：登入學生機後清除部分資料，此部分保留修改彈性，目前入作為限制學生機互相登入之限制，以防代作答情況。

(f) l(lvm)：硬碟分割、格式化、自動掛掛評分。

(g) rhcsa：RHCSA 考照練習總評分。

(h) rhce：RHCE 考照練習總評分。

(i) t(mounttest)：測試學生機是否有掛載隨身碟或光碟。

(j) k(kvmreboot)：學生機 KVM 虛擬機重新啟動。

(k) v(kvmrevert)：學生機 KVM 虛擬機還原。

(l) s(servicerestart)：啟動學生機主機服務。

(m) vi1：vi 編輯器實機練習評分。

(n) c1：C 語言第一支程式練習評分。

(o) lpvar：shell 變數變化練習評分。

(p) c999：C 語言程式實機練習評分。

(q) sh999：Shell script 程式練習評分。

(r) hp(hostpatch)：學生機網路修正後重新啟動網路。


1.6. 證照考試 CHAPTER 1. 實機練習系統

1.6 證照考試

1. 證照考試是手段，不是目的。為的是讓學生藉由實機考照的訓練，熟悉 Linux 系統管理及伺服器架設。

2. 經紅帽原廠認證講師，可以提供學生最新的 Linux 技術。

3. 經紅帽原廠認證考官，可以主持 RHCSA, RHCE 認證考試，學生可以在自已最熟悉的環境考試。

4. 暑期證照班歡迎參加。

1.7 上課前先確定三件事

1. E517 教室位置，靠窗為第一排，確認自己電腦位置為?-?。例如：1-1。

2. 在伺服器 (也就是開機進入圖形界面中有虛擬機管理界面的這台主機)，不是虛擬機，確認自己的電腦 IP。以下例子為 192.168.1.140，IP 尾數為 140。�1 [dywang@deyu ~]$ ifconfig | grep 192

inet addr:192.168.1.140 Bcast:192.168.1.255 Mask:255.255.255.0

3 inet addr:192.168.122.1 Bcast:192.168.122.255 Mask:255.255.255.0� �

3. 在伺服器 deyu.wang 主機一般帳號 dywang 家目錄，產生學號姓名檔 sid，檔案資訊格式為「電腦位置 IP 尾數學號姓名」，每一欄位中間以一個空格隔開。以下例子電腦位置 1-11、IP 尾數 199、學號 123456，姓名王大呆。�1 [dywang@deyu ~]$ echo ’1-11 199 123456 王大呆’ > sid

[dywang@deyu ~]$ cat sid3 1-11 199 123456 王小呆� �

1.8 虛擬機設定

1. 伺服器(也就是開機進入圖形界面中有虛擬機管理界面的這台主機)，不確定的話，先看命令列提示符號是不是 [dywang@deyu ~]$，或是 ifconfig 查目前所在主機的 IP：

(a) Server 主機有兩個名稱 deyu.wang 及 server.deyu.wang(b) Server 與虛擬機網域 deyu.wang，使用網段 192.168.122.0/24。(c) Server IP 為 192.168.122.1，虛擬機 IP 為 192.168.122.X ，其中 X 依題目要求設定。

(d) RHCE 練習環境增加一網域 my111.wang，使用網段 192.168.111.0/24，server 主機增加此網域 IP 192.168.111.1。


1.9. 系統伺服器管理 CHAPTER 1. 實機練習系統

2. 虛擬機環境說明：

(a) 虛擬機主機名稱 kvmX.deyu.wang IP 192.168.122.X，X為虛擬機編號，例如5 或 7。

(b) 虛擬機 root 密碼無法取得，統一重新設定為 123qwe。(c) 虛擬機網路設定：�

1 IP Address: 192.168.122.XNetmask: 255.255.255.0

3 Gateway: 192.168.122.1Name server: 192.168.122.1� �

(d) repo server: http://dywang.csie.cyut.edu.tw/centos7.(e) kernel packages: 網路下載核心 RPM 檔

http://dywang.csie.cyut.edu.tw/kernel/centos7.(f) LDAP server 設定：

i. 系統 deyu.wangii. base DN 為 dc=deyu,dc=wangiii. 證書 ftp://deyu.wang/pub/cacert.pem.

1.9 系統伺服器管理

1. 使用虛擬機練習時，伺服器 (也就是開機進入圖形界面中有虛擬機管理界面的這台主機) 必須正常運作，否則有些驗證無法進行。

2. 練習用到的 server 都已寫成腳本，如果運作有問題，可以執行腳本重啟。

1.9.1 DNS server(a) 如果確定你的練習虛擬機 DNS 設定沒問題，但 ssh 卻無法使用主機名稱連線到虛擬機，可以在 server 機使用 host 命令看是否可以查到虛擬機的 IP？

(b) 沒有 DNS server 可查。�[dywang@deyu ~]$ host kvm7.deyu.wang

2 ;; connection timed out; trying next origin;; connection timed out; no servers could be reached� �

(c) 在 DNS server 查不到 kvm7.deyu.wang 的 IP。�1 [dywang@deyu ~]$ host kvm7.deyu.wang

Host kvm7.deyu.wang not found: 3(NXDOMAIN)� �(d) 在伺服器 deyu.wang 主機切換成 root 身份。


http://dywang.csie.cyut.edu.tw/centos7

http://dywang.csie.cyut.edu.tw/kernel/centos7

ftp://deyu.wang/pub/cacert.pem


�[dywang@deyu ~]$ su -

2 Password: 123123[root@deyu ~]#� �

(e) 執行 setdnsmasq.sh 腳本重啟 dnsmasq DNS 服務。�1 [root@deyu ~]# setdnsmasq.sh restart� �

(f) 再使用 host 查詢 kvm7.deyu.wang，已可查到其 IP 為 192.168.122.7。�1 [root@deyu ~]# host kvm7.deyu.wang

kvm7.deyu.wang has address 192.168.122.73 kvm7.deyu.wang mail is handled by 1 20.� �

(g) 如果執行 setdnsmasq.sh 腳本重啟 dnsmasq DNS 服務未能解決問題，還可以使用 setdns.sh 腳本啟動 named DNS 服務。�1 [root@deyu ~]# setdns.sh restart� �

1.9.2 LDAP server

(a) 如果確認你的虛擬機 LDAP 設定沒問題，但還是無法以 ldapuser1 登入。在伺服器 deyu.wang 主機 (不是你的練習虛擬機)，先切換成 root 身份。�1 [dywang@deyu ~]$ su -

Password: 1231233 [root@deyu ~]#� �

(b) 在伺服器執行 setldap1.sh restart 重新產生 LDAP 憑證。因為憑證是新產生的，所以虛擬機必須再重新執行認證設定更新。�1 [root@deyu ~]# setldap1.sh restart� �

1.9.3 NFS server

(a) 虛擬機設定自動掛載必須查得到 server 有分享目錄，server 的 NFS 不像 LDAP 有憑證過期問題，所以一般都不需要重啟，但如果要重啟 NFSserver，先在伺服器 deyu.wang 主機切換成 root 身份。



�1 [dywang@deyu ~]$ su -

Password: 1231233 [root@deyu ~]#� �

(b) 執行 setnfs.sh 腳本重新啟動。�1 [root@deyu ~]# setnfs.sh restart� �


CHAPTER 2. 簡介

Chapter 2

簡介

2.1 MySQL1. 什麼是資料庫？

(a) 資料庫 (Database)：資料的集合，每個資料庫有一個或多個獨立的應用程式界面 (Application Programming Interface, API) 去產生、存取、管理、搜尋及複製資料。

(b) 關聯式資料庫 (Relational Database)：使用關聯式資料庫管理系統 (Rela-tional Database Management Systems, RDBMS) 去存儲及管理龐大的資料。

(c) 關聯式資料庫中資料儲存在不同的資料表，並使用 primary keys 建立資料表之間的關係。

2. 關聯式資料庫管理系統相關定義：

(a) Database: 資料表的集合。(b) Table: 資料的表格。(c) Column: 一列為資料表中包含相同型態資料的欄位。(d) Row: 一行為資料表的一筆紀錄。(e) Redundancy: 儲存資料兩次，目的是加快系統存取。(f) Primary Key: 主鍵，其值在資料表中是唯一，不能出現兩次以上。(g) Foreign Key: 外來鍵，兩資料表之間的連結針。(h) Compound Key: 複合鍵，多列的組合，用來補足一列唯一性的不完整。(i) Index: 索引。(j) Referential Integrity: 參考完整性，確保外來鍵指向存在的紀錄行。

3. MySQL資料庫是一個快速、容易使用的關聯式資料庫，運用在許多的伺服器上。本系統 Moodle 就是以 PHP+MySQL 建置。MySQL 受歡迎的原因：

(a) 開放原始碼之自由軟體，不需付費。(b) 功能非常強大。(c) 使用 SQL 資料語言的標準型式。(d) 適合於許多作業系統如 Linux, Windows 及許多程式語言包含 PHP, PERL,

C, C++, JAVA 等。


2.2. PHP CHAPTER 2. 簡介

(e) 運行快速且順暢，即使對於大量資料也一樣。(f) 與 PHP 非常匹配，是最常被用來建置互動式網頁的方式。(g) 支援大資料庫，一個資料表可以建立 5 千萬筆以上的資料。資料表預設的上限為 4G，但若作業系統可支援，理論上可以增加到 8 百萬 TB。

(h) 因為是 GPL 授權的開放源碼，所以可以自行修改 MySQL，以適合自己的特殊環境。

2.2 PHP1. MySQL 可搭配許多程式語言使用，而最常也最配的應該是 PHP。

2. 本文中的 PHP 介紹僅針對存取 MySQL 資料庫的語法。


CHAPTER 3. MYSQL 安裝

Chapter 3

MySQL 安裝

3.1 建立使用環境

1. Linux KVM 虛擬機。

2. 遠端登入 kvm8.deyu.wang 虛擬機。

�1 [root@dyw219 ~]# ssh [email protected]

[email protected]’s password:3 Last login: Sat Dec 6 09:45:29 2014 from 192.168.122.1

[root@kvm8 ~]#� �3. 安裝 mysql-server。

�[root@kvm8 ~]# yum -y install mysql-server� �

4. 啟動 mysqld 服務。

�1 [root@kvm8 ~]# /etc/init.d/mysqld start� �

5. 設定開機自動啟動 mysqld 服務。

�1 [root@kvm8 ~]# chkconfig mysqld on

[root@kvm8 ~]# chkconfig --list mysqld3 mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off� �


3.2. 第一次使用 CHAPTER 3. MYSQL 安裝

6. 查看 mysql 版本。

�1 [root@kvm8 ~]# mysqladmin --version

mysqladmin Ver 8.42 Distrib 5.5.38, for Linux on x86_64� �7. 查看 mysqld port 為 3306。

�[root@kvm6 ~]# netstat -tlunp | grep mysqld

2 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1638/mysqld� �

3.2 第一次使用

1. 開始 mysql 的安全設定。

�[root@kvm8 ~]# mysql_secure_installation

2NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

4 SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

6 In order to log into MySQL to secure it, we’ll need the currentpassword for the root user. If you’ve just installed MySQL, and

8 you haven’t set the root password yet, the password will be blank,so you should just press enter here.

10Enter current password for root (enter for none):

12 OK, successfully used password, moving on...

14 Setting the root password ensures that nobody can log into the MySQLroot user without the proper authorisation.

16Set root password? [Y/n] Y

18 New password:Re-enter new password:

20 Password updated successfully!Reloading privilege tables..

22 ... Success!

24 By default, a MySQL installation has an anonymous user, allowinganyone

to log into MySQL without having to have a user account created for26 them. This is intended only for testing, and to make the

installationgo a bit smoother. You should remove them before moving into a

28 production environment.


3.2. 第一次使用 CHAPTER 3. MYSQL 安裝

30 Remove anonymous users? [Y/n] n... skipping.

32Normally, root should only be allowed to connect from ’localhost’.

This34 ensures that someone cannot guess at the root password from the

network.

36 Disallow root login remotely? [Y/n] n... skipping.

38By default, MySQL comes with a database named ’test’ that anyone can

40 access. This is also intended only for testing, and should beremoved

before moving into a production environment.42

Remove test database and access to it? [Y/n] n44 ... skipping.

46 Reloading the privilege tables will ensure that all changes made sofar

will take effect immediately.48

Reload privilege tables now? [Y/n] Y50 ... Success!

52 Cleaning up...

54 All done! If you’ve completed all of the above steps, your MySQLinstallation should now be secure.

56Thanks for using MySQL!� �

2. 使用剛剛設定的 root 密碼登入 mysql。

�1 [root@kvm8 ~]# mysql -uroot -p

Enter password:3 Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 95 Server version: 5.5.38 MySQL Community Server (GPL) by Remi

7 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rightsreserved.

9 Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective

11 owners.

13 Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current inputstatement.

15 mysql>


3.3. 變更 ROOT 密碼 CHAPTER 3. MYSQL 安裝

� �3. 查看目前的資料庫。

�1 mysql> show databases;

+--------------------+3 | Database |

+--------------------+5 | information_schema |

| mysql |7 | performance_schema |

| test |9 +--------------------+

4 rows in set (0.00 sec)11

mysql>� �4. 退出 mysql 資料庫管理。

�mysql> exit

2 Bye[root@kvm8 ~]#� �

3.3 變更 root 密碼1. 使用 mysqladmin 變更 root 密碼。

�1 [root@kvm6 ~]# mysqladmin -uroot -p123qwe password ’qweqwe’� �

2. 變更後，以新密碼 qweqwe 成功登入。

�1 [root@kvm6 ~]# mysql -uroot -pqweqwe

Welcome to the MySQL monitor. Commands end with ; or \g.3 Your MySQL connection id is 3745

Server version: 5.5.49 MySQL Community Server (GPL) by Remi5

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rightsreserved.

7Oracle is a registered trademark of Oracle Corporation and/or its


3.4. 忘記 ROOT 密碼 CHAPTER 3. MYSQL 安裝

9 affiliates. Other names may be trademarks of their respectiveowners.

11Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current input

statement.13

mysql>� �

3.4 忘記 root 密碼1. 先關閉 mysqld 服務。

�[root@kvm6 ~]# /etc/init.d/mysqld stop

2 Stopping mysqld: [ OK ]� �2. mysqld_safe 跳過 grant tables，因為接著要下命令，所以讓其背景執行。

�[root@kvm6 ~]# mysqld_safe --skip-grant-tables &

2 [1] 30343[root@kvm6 ~]# 190520 20:21:32 mysqld_safe Logging to ’/var/log/

mysqld.log’.4 190520 20:21:32 mysqld_safe Starting mysqld daemon with databases

from /var/lib/mysql� �3. 接著就可以免密碼登入 mysql 了。

�[root@kvm6 ~]# mysql -uroot

2 Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1

4 Server version: 5.5.49 MySQL Community Server (GPL) by Remi



10 owners.

12 Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current inputstatement.� �


3.5. 實機操作練習題 CHAPTER 3. MYSQL 安裝

4. 使用 mysql 資料庫。

�mysql> use mysql;

2 Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

4Database changed� �

5. 變更 root 密碼。

�1 mysql> update user set password=password("123qwe") where user=’root’;

Query OK, 0 rows affected (0.00 sec)3 Rows matched: 3 Changed: 0 Warnings: 0� �

6. 更新權限。

�1 mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)� �7. 退出。

�mysql> quit

2 Bye� �8. 重新啟動 mysqld 服務。

�[root@kvm6 ~]# /etc/init.d/mysqld restart

2 190520 20:22:55 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Stopping mysqld: [ OK ]4 Starting mysqld: [ OK ]

[1]+ Done mysqld_safe --skip-grant-tables� �

3.5 實機操作練習題

1. 以下列步驟完成 mysql 伺服器架設。



(a) 遠端登入 kvm6 虛擬機。(b) 安裝 mysql-server 套件。(c) 啟動 mysqld 服務，並設定開機自動啟動。(d) 查看 mysql 版本並儲存到檔案/tmp/mysqlversion。(e) 進行 mysql 安全安裝，設定 root 密碼為 123qwe、重新載入權限表，其餘都回答 no。

(f) 以設定的 root 密碼登入 mysql，查詢資料庫後退出。


CHAPTER 4. MYCLI

Chapter 4

MyCLI

4.1 前言

1. MyCLI 是 MySQL，MariaDB 和 Percona 等命令行界面，語法自動補齊或提示功能工具。

4.2 安裝於 CentOS 61. CentOS 6.x 預設使用 python 2.6，但 MyCLI 1.9 已不支援 python 2.6 安裝，所以必須先安裝 python27 及 python27-devel 套件。

�1 [root@kvm6 ~]# yum install python27 python27-devel� �

2. CentOS 6.x 預設 python-pip 套件提供 pip 程式使用 python 2.6，必須再安裝python27-pip，執行命令為 pip2.7。

�1 [root@kvm6 ~]# yum install python27-pip� �

3. 執行 pip2.7 install mycli 下載 mycli 程式。

�1 [root@kvm6 ~]# pip2.7 install mycli

Collecting mycli3 Downloading https://files.pythonhosted.org/packages/f9/8a/

9f96f12b482ff2e1bbb1d4252e14b4a24ed37daa5b1dcd14c3d990eca9dd/5 mycli-1.19.0-py2.py3-none-any.whl (56kB)

100% || 57kB 236kB/s7 Collecting cryptography>=1.0.0 (from mycli)

Downloading https://files.pythonhosted.org/packages/07/ca/9 bc827c5e55918ad223d59d299fff92f3563476c3b00d0a9157d9c0217449/

cryptography-2.6.1.tar.gz (491kB)11 100% || 495kB 499kB/s


4.2. 安裝於 CENTOS 6 CHAPTER 4. MYCLI

Complete output from command python setup.py egg_info:13 Traceback (most recent call last):

File "<string>", line 20, in <module>15 File "/tmp/pip-build-8rU14l/cryptography/setup.py", line 28, in

<module>"cryptography requires setuptools 18.5 or newer, please

upgrade to a "17 RuntimeError: cryptography requires setuptools 18.5 or newer,

please upgrade to a newer version of setuptools19

----------------------------------------21 Command "python setup.py egg_info" failed with error code 1 in /tmp/

pip-build-8rU14l/cryptographyYou are using pip version 7.1.2, however version 19.0.3 is available.

23 You should consider upgrading via the ’pip install --upgrade pip’command.� �

4. 結果出現錯誤，依照提示要更新 pip 版本，先更新 pip2.7 的程式。

�1 [root@kvm6 ~]# pip2.7 install --upgrade pip

Collecting pip3 Downloading https://files.pythonhosted.org/packages/d8/f3/

413bab4ff08e1fc4828dfc59996d721917df8e8583ea85385d51125dceff/5 pip-19.0.3-py2.py3-none-any.whl (1.4MB)

100% || 1.4MB 202kB/s7 Installing collected packages: pip

Found existing installation: pip 7.1.29 Uninstalling pip-7.1.2:

Successfully uninstalled pip-7.1.211 Successfully installed pip-19.0.3� �

5. 再執行 pip2.7 install mycli 下載 mycli 程式，安裝成功。

�1 [root@kvm6 ~]# pip2.7 install mycli

DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020.

3 Please upgrade your Python as Python 2.7 won’t be maintained afterthat date.

A future version of pip will drop support for Python 2.7.5 Collecting mycli

Using cached https://files.pythonhosted.org/packages/f9/8a/7 9f96f12b482ff2e1bbb1d4252e14b4a24ed37daa5b1dcd14c3d990eca9dd/mycli

-1.19.0-py2.py3-none-any.whlCollecting cryptography>=1.0.0 (from mycli)

9 100% || 163kB 734kB/s...........................

11 Installing collected packages: enum34, asn1crypto, six, pycparser,cffi, ipaddress,


cryptography, sqlparse, PyMySQL, click, backports.csv, wcwidth,tabulate, terminaltables,

13 configobj, Pygments, cli-helpers, prompt-toolkit, mycliRunning setup.py install for pycparser ... done

15 Running setup.py install for tabulate ... doneRunning setup.py install for terminaltables ... done

17 Running setup.py install for configobj ... doneSuccessfully installed PyMySQL-0.9.3 Pygments-2.3.1 asn1crypto-0.24.0

backports.csv-1.0.719 cffi-1.12.2 cli-helpers-1.1.0 click-7.0 configobj-5.0.6 cryptography

-2.6.1 enum34-1.1.6ipaddress-1.0.22 mycli-1.19.0 prompt-toolkit-2.0.9 pycparser-2.19 six

-1.12.0 sqlparse-0.2.421 tabulate-0.8.3 terminaltables-3.1.0 wcwidth-0.1.7� �

6. 執行 easy_install-2.7 安裝 mycli。

�1 [root@kvm6 ~]# easy_install-2.7 mycli

Searching for mycli3 Best match: mycli 1.19.0

Adding mycli 1.19.0 to easy-install.pth file5 Installing mycli script to /usr/bin

7 Using /usr/lib/python2.7/site-packagesProcessing dependencies for mycli

9 Finished processing dependencies for mycli� �

4.3 安裝於 CentOS 71. CentOS 7.x 預設使用 python 2.7，支援 MyCLI 1.9 安裝，但 python2-pip 套件在

epel-releases repository，所以必須先安裝 epel-release 套件。

�1 [root@kvm5 ~]# yum install epel-release.noarch� �

2. python2-pip 套件提供 pip 命令工具，先將其安裝起來。

�1 [root@kvm5 ~]# yum install python2-pip� �

3. 使用 pip 命令工具安裝 mycli。



�1 [root@kvm5 ~]# pip install mycli

Collecting mycli3 Downloading https://files.pythonhosted.org/packages/.../0-py2.py3-

none-any.whl (56kB)100% || 61kB 190kB/s

5 ........Successfully installed PyMySQL-0.9.3 Pygments-2.4.0 .........

7 You are using pip version 8.1.2, however version 19.1.1 is available.You should consider upgrading via the ’pip install --upgrade pip’

command.9 [root@kvm5 ~]#� �

4. 必須先 upgrade pip。

�1 [root@kvm5 ~]# pip install --upgrade pip

Collecting pip3 Downloading https://files.pythonhosted.org/packages/...

/pip-19.1.1-py2.py3-none-any.whl (1.4MB)5 100% || 1.4MB 1.1MB/s

Installing collected packages: pip7 Found existing installation: pip 8.1.2

Uninstalling pip-8.1.2:9 Successfully uninstalled pip-8.1.2

Successfully installed pip-19.1.1� �

5. 再執行 pip 安裝 mycli。

�[root@kvm5 ~]# pip install mycli� �

6. 執行 easy_install 安裝 mycli。

�1 [root@kvm5 ~]# easy_install mycli

Searching for mycli3 Best match: mycli 1.19.0

Adding mycli 1.19.0 to easy-install.pth file5 Installing mycli script to /usr/bin

7 Using /usr/lib/python2.7/site-packagesProcessing dependencies for mycli

9 Finished processing dependencies for mycli� �


4.4. 使用 CHAPTER 4. MYCLI

7. 使用 mycli 登入 mysql。

�1 [root@kvm5 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -pqweqwe

mariadb 5.5.56-MariaDB3 mycli 1.19.0

Chat: https://gitter.im/dbcli/mycli5 Mail: https://groups.google.com/forum/#!forum/mycli-users

Home: http://mycli.net7 Thanks to the contributor - Scrappy Soft

mariadb root@localhost:(none)>� �

4.4 使用

1. mycli –help 查看輔助說明。

�[root@kvm6 ~]# mycli --help

2 Usage: mycli [OPTIONS] [DATABASE]

4 A MySQL terminal client with auto-completion and syntaxhighlighting.

6 Examples:- mycli my_database

8 - mycli -u my_user -h my_host.com my_database- mycli mysql://my_user@my_host.com:3306/my_database

10Options:

12 -h, --host TEXT Host address of the database.-P, --port INTEGER Port number to use for connection.

Honors14 $MYSQL_TCP_PORT.

-u, --user TEXT User name to connect to the database.16 -S, --socket TEXT The socket file to use for connection

.-p, --password TEXT Password to connect to the database.

18 --pass TEXT Password to connect to the database.--ssh-user TEXT User name to connect to ssh server.

20 --ssh-host TEXT Host name to connect to ssh server.--ssh-port INTEGER Port to connect to ssh server.

22 --ssh-password TEXT Password to connect to ssh server.--ssh-key-filename TEXT Private key filename (identify file)

for the24 ssh connection.

--ssl-ca PATH CA file in PEM format.26 --ssl-capath TEXT CA directory.

--ssl-cert PATH X509 cert in PEM format.28 --ssl-key PATH X509 key in PEM format.

--ssl-cipher TEXT SSL cipher to use.



30 --ssl-verify-server-cert Verify server’s "Common Name" in itscert

against hostname used when connecting. This

32 option is disabled by default.-V, --version Output mycli’s version.

34 -v, --verbose Verbose output.-D, --database TEXT Database to use.

36 -d, --dsn TEXT Use DSN configured into the [alias_dsn]

section of myclirc file.38 --list-dsn list of DSN configured into the [

alias_dsn]section of myclirc file.

40 -R, --prompt TEXT Prompt format (Default: "\t \u@\h:\d>").

-l, --logfile FILENAME Log every query and its results to afile.

42 --defaults-group-suffix TEXT Read MySQL config groups with thespecified

suffix.44 --defaults-file PATH Only read MySQL options from the

given file.--myclirc PATH Location of myclirc file.

46 --auto-vertical-output Automatically switch to verticaloutput mode

if the result is wider than theterminal

48 width.-t, --table Display batch output in table format.

50 --csv Display batch output in CSV format.--warn / --no-warn Warn before running a destructive

query.52 --local-infile BOOLEAN Enable/disable LOAD DATA LOCAL INFILE

.--login-path TEXT Read this path from the login file.

54 -e, --execute TEXT Execute command and quit.--help Show this message and exit.� �

2. 執行 mycli 以 root 帳號登入 mysql，出現無法連線 mysql server 的錯誤訊息，但還是可以下命令。

�1 [root@kvm6 ~]# mycli -uroot -p123qwe mysql

(2003, "Can’t connect to MySQL server on ’localhost’ ([Errno 2] Nosuch file or directory)")

3 Failed to connect by socket, retrying over TCP/IPmysql 5.5.49

5 mycli 1.19.0Chat: https://gitter.im/dbcli/mycli

7 Mail: https://groups.google.com/forum/#!forum/mycli-usersHome: http://mycli.net

9 Thanks to the contributor - ushuz



mysql root@localhost:mysql> exit11 Goodbye!� �

3. mycli 下命令時使用 -S 選項指定 mysql socket 檔就不會再出現錯誤訊息。

�1 [root@kvm6 ~]# mycli -uroot -S /var/lib/mysql/mysql.sock -p123qwe

mysqlmysql 5.5.49



7 Thanks to the contributor - Darik Gamblemysql root@localhost:mysql>� �

4. 下命令 show 加空白就會出現提示視窗，列出可以接的參數供您選擇，不用再把完整命令打完。

�mysql root@localhost:mysql> show AUTHORS

2 AUTHORSBINARY LOGS

4 BINLOG EVENTSCHARACTER SET

6 COLLATIONCOLUMNS� �

5. 選擇 databases 後執行。

�1 mysql root@localhost:mysql> show DATABASES;

+--------------------+3 | Database |



+--------------------+9 3 rows in set

Time: 0.024s11 mysql root@localhost:mysql> exit

Goodbye!13

15 [F3] Multiline: OFF Right-arrow to complete suggestion� �De-Yu Wang CSIE CYUT 25

4.5. 實機操作練習題 CHAPTER 4. MYCLI


1. 以下列步驟完成 mycli 安裝及使用。

(a) 遠端登入 kvm6 虛擬機。(b) 安裝 python 2.7 版。(c) 使用 pip2.7 下載 mycli。(d) easy_install-2.7 安裝 mycli。(e) 使用 mycli 登入 mysql。(f) 下命令看看是否有提示功能？


CHAPTER 5. MYSQL 管理

Chapter 5

MySQL 管理

5.1 建立用戶




[root@kvm8 ~]#� �2. 登入 mysql。

�[root@kvm8 ~]# mysql -uroot -p

2 Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.

4 Your MySQL connection id is 115Server version: 5.5.38 MySQL Community Server (GPL) by Remi

6Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights

reserved.8

Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective

owners.12

Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current inputstatement.� �

3. 使用 mysql 資料庫。

�1 mysql> use mysql;

Reading table information for completion of table and column names



7. 更新用戶權限。

�1 mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.11 sec)� �8. 列出剛剛新增的用戶的 host,user,password 欄位，其中密碼已經加密。

�mysql> select host,user,password from user where user=’dywang’;

2 +-----------+--------+-------------------------------------------+| host | user | password |

4 +-----------+--------+-------------------------------------------+| localhost | dywang | *050376F3855A67F5E2C6514FD3130B31006C1276 |

6 +-----------+--------+-------------------------------------------+1 row in set (0.00 sec)� �

9. 退出資料庫。

�1 mysql> exit

Bye� �10. 使用新用戶登入 mysql 資料庫。

�[root@kvm8 ~]# mysql -udywang -p

2 Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.

4 Your MySQL connection id is 116Server version: 5.5.38 MySQL Community Server (GPL) by Remi

6Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights

reserved.8

Oracle is a registered trademark of Oracle Corporation and/or its10 affiliates. Other names may be trademarks of their respective

owners.12

Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current inputstatement.

14mysql>� �

11. 退出資料庫。


5.2. 刪除用戶 CHAPTER 5. MYSQL 管理

�1 mysql> exit

Bye� �

5.2 刪除用戶

1. 先新增用戶 dywtest。

�[root@kvm6 ~]# mycli -uroot -S /var/lib/mysql/mysql.sock -p123qwe

mysql2 mysql 5.5.49

mycli 1.19.04 Chat: https://gitter.im/dbcli/mycli

Mail: https://groups.google.com/forum/#!forum/mycli-users6 Home: http://mycli.net

Thanks to the contributor - Johannes Hoff8 mysql root@localhost:mysql> INSERT INTO user (host,user,password,

select_priv,insert_priv,update_priv) VALUES (’localhost

’,’dywtest’,10 password(’123qwe’),’Y’,’Y’,’Y’);

Query OK, 1 row affected12 Time: 0.003s

mysql root@localhost:mysql> Flush privileges;14 Query OK, 0 rows affected

Time: 0.001s� �2. 退出資料庫。

�1 mysql root@localhost:mysql> exit;

Goodbye!� �3. 再登入 mysql 刪除 dywtest。

�[root@kvm6 ~]# mycli -uroot -S /var/lib/mysql/mysql.sock -p123qwe

mysql2 mysql 5.5.49

mycli 1.19.04 Chat: https://gitter.im/dbcli/mycli

Mail: https://groups.google.com/forum/#!forum/mycli-users6 Home: http://mycli.net

Thanks to the contributor - Ryan Smith8 mysql root@localhost:mysql> DROP USER ’dywtest’@’localhost’;

You’re about to run a destructive command.


5.3. SERVER 允許遠端存取 CHAPTER 5. MYSQL 管理

10 Do you want to proceed? (y/n): yYour call!

12 Query OK, 0 rows affectedTime: 0.002s

14 mysql root@localhost:mysql> exit;Goodbye!� �

5.3 Server 允許遠端存取1. mysql 啟動後會監聽在 3306 port。

�1 [root@kvm6 ~]# netstat -tlunp | grep mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN2712/mysqld� �

2. 允許遠端存取，防火牆必須開啟 3306 port，CentOS 6 防火牆使用 iptables，查看目前的防火牆，3306 port 沒開啟。

�[root@kvm6 ~]# iptables-save

2 # Generated by iptables-save v1.4.7 on Fri Mar 15 19:27:03 2019*filter

4 :INPUT ACCEPT [0:0]:FORWARD ACCEPT [0:0]

6 :OUTPUT ACCEPT [12762:1492182]-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

8 -A INPUT -p icmp -j ACCEPT-A INPUT -i lo -j ACCEPT

10 -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT-A INPUT -j REJECT --reject-with icmp-host-prohibited

12 -A FORWARD -j REJECT --reject-with icmp-host-prohibitedCOMMIT

14 # Completed on Fri Mar 15 19:27:03 2019� �3. vim 編輯 iptables 設定檔，增加防火牆開啟 3306 port。

�[root@kvm6 ~]# vim /etc/sysconfig/iptables

2 [root@kvm6 ~]# cat /etc/sysconfig/iptables# Firewall configuration written by system-config-firewall

4 # Manual customization of this file is not recommended.*filter

6 :INPUT ACCEPT [0:0]:FORWARD ACCEPT [0:0]

8 :OUTPUT ACCEPT [0:0]


5.3. SERVER 允許遠端存取 CHAPTER 5. MYSQL 管理

-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT10 -A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT12 -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT14 -A INPUT -j REJECT --reject-with icmp-host-prohibited

-A FORWARD -j REJECT --reject-with icmp-host-prohibited16 COMMIT� �

4. 重新啟動 iptables。

�[root@kvm6 ~]# /etc/init.d/iptables restart

2 iptables: Setting chains to policy ACCEPT: filter [ OK ]iptables: Flushing firewall rules: [ OK ]

4 iptables: Unloading modules: [ OK ]iptables: Applying firewall rules: [ OK ]� �

5. 登入 mysql 增加一只能從 192.168.122.1 存取的帳號 hosttest。

�1 [root@kvm6 ~]# mycli -uroot -S /var/lib/mysql/mysql.sock -p123qwe

mysqlmysql 5.5.49



7 Thanks to the contributor - jweiland.netmysql root@localhost:mysql> INSERT INTO user (host,user,password,

select_priv,inser9 t_priv,update_priv) VALUES

(’192.168.122.1’,’hosttest’,password(’123qwe’),’Y’,’Y’,’Y’);

11 Query OK, 1 row affectedTime: 0.009s

13 mysql root@localhost:mysql> Flush privileges;Query OK, 0 rows affected

15 Time: 0.001smysql root@localhost:mysql> exit

17 Goodbye!� �6. 從本機以 hosttest 帳號無法登入 mysql。

�1 [root@kvm6 ~]# mysql -uhosttest -p123qwe

ERROR 1045 (28000): Access denied for user ’hosttest’@’localhost’ (using password: YES)


5.4. CLIENT 遠端存取 CHAPTER 5. MYSQL 管理

� �

5.4 Client 遠端存取1. Client 要存取遠端的 mysql，必須以 root 權限先安裝 mysql。

�[root@dyw219 ~]# yum install mysql� �

2. Client 端以 hosttest 帳號登入遠端 192.168.122.6 的 mysql。

�1 [root@dyw219 ~]# mysql -uhosttest -p123qwe -h 192.168.122.6







statement.13

mysql>15 mysql> exit

Bye� �3. Client 端以 root 帳號無法登入遠端 192.168.122.6 的 mysql，因為 server 設定

root 帳號的 host 是 localhost，所以 root 只能在本機存取 mysql 。

�[root@dyw219 ~]# mysql -uroot -p123qwe -h 192.168.122.6

2 ERROR 1045 (28000): Access denied for user ’root’@’deyu.wang’ (usingpassword: YES)� �

5.5 mysqld 設定檔1. mysql 的設定檔在/etc/my.cfg，如果沒有特別要求，可以使用預設設定。


5.5. MYSQLD 設定檔 CHAPTER 5. MYSQL 管理

�[root@kvm8 ~]# cat /etc/my.cnf

2 [mysqld]datadir=/var/lib/mysql

4 socket=/var/lib/mysql/mysql.sock

6 # Disabling symbolic-links is recommended to prevent assortedsecurity risks

symbolic-links=08

# Settings user and group are ignored when systemd is used (fedora >=15).

10 # If you need to run mysqld under a different user or group,# customize your systemd unit file for mysqld according to the

12 # instructions in http://fedoraproject.org/wiki/Systemduser=mysql

14# Semisynchronous Replication

16 # http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html# uncomment next line on MASTER

18 ;plugin-load=rpl_semi_sync_master=semisync_master.so# uncomment next line on SLAVE

20 ;plugin-load=rpl_semi_sync_slave=semisync_slave.so

22 # Others options for Semisynchronous Replication;rpl_semi_sync_master_enabled=1

24 ;rpl_semi_sync_master_timeout=10;rpl_semi_sync_slave_enabled=1

26# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html

28 ;performance_schema

30 [mysqld_safe]log-error=/var/log/mysqld.log

32 pid-file=/var/run/mysqld/mysqld.pid� �2. 設定 mysql 伺服器 section 使用 utf-8 編碼。

�[root@kvm8 ~]# vim /etc/my.cnf

2 [mysqld]collation-server=utf8_general_ci

4 character-set-server=utf8� �3. 設定所有連線的用戶端預設使用 utf-8 編碼。

�[root@kvm8 ~]# vim /etc/my.cnf

2 [client]default-character-set=utf8


5.5. MYSQLD 設定檔 CHAPTER 5. MYSQL 管理

� �4. 設定 mysql 命令列用戶端預設使用 utf-8 編碼。

�1 [root@kvm8 ~]# vim /etc/my.cnf

[mysql]3 default-character-set=utf8� �

5. 查詢/etc/my.cnf 設定。

�1 [root@kvm6 ~]# egrep -v ’^(#|;|$)’ /etc/my.cnf

[mysqld]3 datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock5 collation-server=utf8_general_ci

character-set-server=utf87 symbolic-links=0

user=mysql9 [mysqld_safe]

log-error=/var/log/mysqld.log11 pid-file=/var/run/mysqld/mysqld.pid

[client]13 default-character-set=utf8

[mysql]15 default-character-set=utf8

!includedir /etc/my.cnf.d� �6. 重新啟動 mysqld。

�[root@kvm8 ~]# /etc/init.d/mysqld restart

2 Stopping mysqld: [ OK ]Starting mysqld: [ OK ]� �

7. 查詢變數是否生效？

�1 [root@kvm6 ~]# mysql -uroot -p123qwe

mysql> show variables like ’%character%’;3 +--------------------------+----------------------------+

| Variable_name | Value |5 +--------------------------+----------------------------+

| character_set_client | utf8 |


5.6. 實機操作練習題 CHAPTER 5. MYSQL 管理

7 | character_set_connection | utf8 || character_set_database | utf8 |

9 | character_set_filesystem | binary || character_set_results | utf8 |

11 | character_set_server | utf8 || character_set_system | utf8 |

13 | character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+

15 8 rows in set (0.00 sec)

17 mysql>� �


1. 以下列步驟完成 mysql 使用者建立。

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 使用 mysql 資料庫。(d) 查詢 mysql 資料表。(e) 新增一用戶

i. 用戶登入主機 localhostii. 用戶名稱 user1iii. 密碼 userpw1iv. 擁有 select, insert, update 權限。

(f) 新增另一用戶i. 用戶登入主機 192.168.122.1ii. 用戶名稱 user2iii. 密碼 userpw2iv. 只擁有 select 權限。

(g) 更新用戶權限。(h) 退出 mysql。(i) 以 user1 及 user2 帳號登入 mysql，看是否能登入？(j) 在 192.168.122.1 主機遠端，以 user1 及 user2 帳號登入 mysql，看是否能登入？

(k) 登入後退出 mysql 資料庫。

2. 編輯/etc/my.cnf 變更 myslqd 設定。

(a) 設定 mysqld, mysql, cilent 等 sections 都預設使用 utf-8 編碼。(b) 重新啟動 mysqld 服務。


5.6. 實機操作練習題 CHAPTER 5. MYSQL 管理


CHAPTER 6. PHP 安裝

Chapter 6

PHP 安裝

6.1 建立使用環境




[root@kvm8 ~]#� �

2. 使用 yum 安裝 php，會將其相依套件 httpd，也就是網頁伺服器也裝起來。

�[root@kvm8 ~]# yum install php -y

2 Loaded plugins: fastestmirror, refresh-packagekitDetermining fastest mirrors

4 dywang | 1.9 kB00:00

Setting up Install Process6 Resolving Dependencies

--> Running transaction check8

Dependencies Resolved10

================================================================================

12 Package Arch VersionRepository Size

================================================================================

14 Installing:php x86_64 5.4.30-1.el6.remi dywang

2.7 M16 Installing for dependencies:

apr x86_64 1.3.9-5.el6_2 dywang123 k


6.1. 建立使用環境 CHAPTER 6. PHP 安裝

18 apr-util x86_64 1.3.9-3.el6_0.1 dywang87 k

apr-util-ldap x86_64 1.3.9-3.el6_0.1 dywang15 k

20 httpd x86_64 2.2.15-26.el6 dywang821 k

httpd-tools x86_64 2.2.15-26.el6 dywang72 k

22 mailcap noarch 2.1.31-2.el6 dywang27 k

php-cli x86_64 5.4.30-1.el6.remi dywang2.6 M

24 php-common x86_64 5.4.30-1.el6.remi dywang932 k

26 Transaction Summary================================================================================

28 Install 9 Package(s)Upgrade 0 Package(s)

30..........

32Complete!� �

3. httpd 的設定檔/etc/httpd/conf/httpd.conf，預設設定中只要加入你的網頁伺服器網域名稱就可以了。

�1 [root@kvm8 ~]# vim /etc/httpd/conf/httpd.conf

[root@kvm8 ~]# grep kvm8 /etc/httpd/conf/httpd.conf3 ServerName kvm8.deyu.wang:80� �

4. 啟動 httpd 服務。

�1 [root@kvm8 ~]# /etc/init.d/httpd start

Starting httpd: [ OK ]� �5. 設定開機啟動 httpd 服務。

�[root@kvm8 ~]# chkconfig httpd on� �

6. 檢查 httpd 開機啟動狀況。


6.1. 建立使用環境 CHAPTER 6. PHP 安裝

�1 [root@kvm8 ~]# chkconfig --list httpd

httpd 0:off 1:off 2:on 3:on 4:on 5:on 6:off� �7. 在此練習中，先不考慮防火牆設定，故先關閉防火牆。

�[root@kvm8 ~]# /etc/init.d/iptables stop

2 iptables: Flushing firewall rules: [ OK ]iptables: Setting chains to policy ACCEPT: filter [ OK ]

4 iptables: Unloading modules: [ OK ][root@kvm8 ~]# chkconfig iptables off

6 [root@kvm8 ~]# chkconfig --list iptablesiptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off� �

8. 伺服器架設，不可能不使用防火牆。防火牆設定開啟 80 port。

�1 [root@kvm8 ~]# vim /etc/sysconfig/iptables

[root@kvm8 ~]# cat /etc/sysconfig/iptables3 # Firewall configuration written by system-config-firewall

# Manual customization of this file is not recommended.5 *filter

:INPUT ACCEPT [0:0]7 :FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [0:0]9 -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

-A INPUT -p icmp -j ACCEPT11 -A INPUT -i lo -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT13 -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited15 -A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT� �9. 防火牆重新啟動，設定開機啟動。注意：要確定防火牆編輯正確，否則重新啟動後，自己可能被擋在外面，無法連線。

�[root@kvm8 ~]# /etc/init.d/iptables restart

2 iptables: Applying firewall rules: [ OK ][root@kvm8 ~]# chkconfig iptables on

4 [root@kvm8 ~]# chkconfig --list iptablesiptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off� �

10. 從 deyu.wang 主機必須可以連線 http://kvm8.deyu.wang。


6.2. PHP MYSQL 語法 CHAPTER 6. PHP 安裝

6.2 PHP MySQL 語法1. 使用 yum 安裝 php-mysql，否則連線網頁會直接顯示 php 碼，不會先執行 php。

�1 [root@kvm8 ~]# yum install php-mysql -y� �

2. 記得要重新啟動 httpd 服務，否則無法連線網頁。

�1 [root@kvm8 ~]# /etc/init.d/httpd restart

Stopping httpd: [ OK ]3 Starting httpd: [ OK ]� �

3. PHP 中使用 MySQL 資料的語法，一般格式如下，其中 function 就是要動作的函數，例如：connet 連線，close 斷線。

�1 mysql_function(value,value,...);� �

4. PHP 開啟 mysql 資料庫連線，其中 new_link,client_flag 可以不輸入，但一定要提供 server,user,passwd 才能連線。

�1 connection mysql_connect(server,user,passwd,new_link,client_flag);� �

5. PHP 關閉 mysql 資料庫連線，若成功傳回 true，否則回傳 false。

�1 bool mysql_close ( resource $link_identifier );� �

6. 變更目錄至/var/www/html。

�1 [root@kvm8 ~]# cd /var/www/html/� �

7. 編輯一個 php 檔，以新增用戶 dywang 測試是否可以連線 mysql，預設的 port 是3306，先故意把密碼設錯為 111123qwe。


6.2. PHP MYSQL 語法 CHAPTER 6. PHP 安裝

�1 [root@kvm8 html]# vim a.php

<html>3 <head>

<title>Connecting MySQL Server</title>5 </head>

<body>7 <?php

$dbhost = ’localhost:3306’;9 $dbuser = ’dywang’;

$dbpass = ’111123qwe’;11 $conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )13 {

die(’Could not connect: ’ . mysql_error());15 }

echo ’Connected successfully’;17 mysql_close($conn);

?>19 </body>

</html>� �8. 在 deyu.wang 主機使用 curl 觀看 a.php 網頁，或開啟瀏覽器開啟 a.php 網頁，可以看到執行結果為存取限制’Could not connect: Access denied for user....’。

�[root@dyw219 ~]# curl http://kvm8.deyu.wang/a.php

2 <html><head>

4 <title>Connecting MySQL Server</title></head>

6 <body>Could not connect: Access denied for user ’dywang’@’localhost’ (using

password:� �9. 修改 a.php，把密碼改成正確的 123qwe。

�1 [root@kvm8 html]# vim a.php

[root@kvm8 html]# grep pass a.php3 $dbpass = ’123qwe’;

$conn = mysql_connect($dbhost, $dbuser, $dbpass);� �10. 再次在 deyu.wang 主機使用 curl 觀看 a.php 網頁，或開啟瀏覽器開啟 a.php 網頁，可以看到執行結果為連線成功’Connected successfully’。

�[root@dyw219 ~]# curl http://kvm8.deyu.wang/a.php

6.3. PHP 輸出到檔案 CHAPTER 6. PHP 安裝

2 <html><head>

4 <title>Connecting MySQL Server</title></head>

6 <body>Connected successfully</body>

8 </html>� �

6.3 PHP 輸出到檔案1. 將 $data 以函式 print_r 輸出到檔案 /tmp/file.txt。

�$output = print_r($data, true);

2 file_put_contents(’/tmp/file.txt’, $output);� �


1. 以下列步驟完成 php 環境。

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 安裝 php httpd php-mysql(d) 新增 mysql 帳號 user1 密碼 user1pw，可以 select, insert, update mysql 資料庫。

(e) 在網頁伺服器下增加 a.php 檔，使用 user1 帳號 (帳號變數名稱為 dbuser)測試連線 mysql 資料庫，如果成功則將’Connected successfully’ 字串寫到/tmp/file 檔中。

CHAPTER 7. DATABASE 資料庫

Chapter 7

Database 資料庫

7.1 產生資料庫


�[root@dyw219 ~]# ssh [email protected]

2 [email protected]’s password:Last login: Sat Dec 6 09:45:29 2014 from 192.168.122.1

4 [root@kvm8 ~]#� �2. 文字命令列直接產生資料庫 dyw。

�[root@kvm8 ~]# mysqladmin -uroot -p create dyw

2 Enter password:� �3. 使用 php 函數 mysql_query 產生資料庫 dywphp。

�[root@kvm8 ~]# cd /var/www/html/

2 [root@kvm8 html]# vim createdb.php[root@kvm8 html]# cat createdb.php

4 <html><head>

6 <title>Creating MySQL Database</title></head>

8 <body><?php

10 $dbhost = ’localhost:3306’;$dbuser = ’root’;

12 $dbpass = ’123qwe’;$conn = mysql_connect($dbhost, $dbuser, $dbpass);

14 if(! $conn ){

7.1. 產生資料庫 CHAPTER 7. DATABASE 資料庫

16 die(’Could not connect: ’ . mysql_error());}

18 echo ’Connected successfully ’;$sql = ’CREATE DATABASE dywphp’;

20 $retval = mysql_query( $sql, $conn );if(! $retval )

22 {die(’Could not create database: ’ . mysql_error());

24 }echo "Database dywphp created successfully\n";

26 mysql_close($conn);?>

28 </body></html>� �

4. 以 php 命令執行 createdb.php 產生 dywphp 資料庫，使用瀏覽器開啟 creat-edb.php 也可以。

�1 [root@kvm8 html]# php createdb.php

<html>3 <head>

<title>Creating MySQL Database</title>5 </head>

<body>7 Connected successfully Database dywphp created successfully

</body>9 </html>� �

5. 直接在命令列查詢 mysql 中的資料庫，已包含剛剛產生的 dyw 及 dywphp 兩個資料庫。

�1 [root@kvm8 html]# mysql -uroot -p123qwe -e "show databases"

+--------------------+3 | Database |


| dyw |7 | dywphp |


| test |11 +--------------------+� �

7.2. 選取工作資料庫 CHAPTER 7. DATABASE 資料庫

7.2 選取工作資料庫

1. 連線到 mysql，必須選擇工作資料庫，否則每行指令都必須指明要處理的資料庫，比較麻煩，以文字命令列登入工作後，使用 use 命令選取要工作的資料庫為dyw。

�1 [root@kvm8 html]# mysql -uroot -p


Your MySQL connection id is 215 Server version: 5.5.38 MySQL Community Server (GPL) by Remi



11 owners.


15 mysql> use dyw;Database changed

17 mysql> show tables;Empty set (0.00 sec)

19mysql> exit

21 Bye� �2. 使用 php 函數 mysql_select 選取資料庫 dywphp。

�1 [root@kvm8 ~]# cd /var/www/html

[root@kvm8 html]# vim selectdb.php3 [root@kvm8 html]# cat selectdb.php

<html>5 <head>

<title>Selecting MySQL Database</title>7 </head>

<body>9 <?php

$dbhost = ’localhost:3306’;11 $dbuser = ’dywang’;


if(! $conn )15 {


echo ’Connected successfully’;

7.3. 刪除資料庫 CHAPTER 7. DATABASE 資料庫

19 mysql_select_db( ’dywphp’ );mysql_close($conn);

21 ?></body>

23 </html>� �3. 以 php命令執行 selectdb.php選取 dywphp資料庫，使用瀏覽器開啟 selectdb.php也可以。

�1 [root@kvm8 html]# php selectdb.php

<html>3 <head>

<title>Selecting MySQL Database</title>5 </head>

<body>7 Connected successfully</body>

</html>� �

7.3 刪除資料庫

1. 文字命令列直接刪除資料庫 dyw。

�[root@kvm8 html]# mysqladmin -uroot -p123qwe drop dyw

2 Dropping the database is potentially a very bad thing to do.Any data stored in the database will be destroyed.

4Do you really want to drop the ’dyw’ database [y/N] y

6 Database "dyw" dropped� �2. 使用 php 函數 mysql_query 刪除資料庫 dywphp。

�[root@kvm8 html]# vim dropdb.php

2 [root@kvm8 html]# cat dropdb.php<html>

4 <head><title>Deleting MySQL Database</title>

6 </head><body>

8 <?php$dbhost = ’localhost:3306’;

10 $dbuser = ’root’;$dbpass = ’123qwe’;

12 $conn = mysql_connect($dbhost, $dbuser, $dbpass);

7.4. 實機操作練習題 CHAPTER 7. DATABASE 資料庫

if(! $conn )14 {


echo ’Connected successfully ’;18 $sql = ’DROP DATABASE dywphp’;

$retval = mysql_query( $sql, $conn );20 if(! $retval )

{22 die(’Could not delete database: ’ . mysql_error());

}24 echo "Database dywphp deleted successfully\n";

mysql_close($conn);26 ?>


3. 以 php 命令執行 dropdb.php 刪除 dywphp 資料庫，使用瀏覽器開啟 dropdb.php也可以。

�[root@kvm8 html]# php dropdb.php

2 <html><head>

4 <title>Deleting MySQL Database</title></head>

6 <body>Connected successfully Database dywphp deleted successfully


4. 直接在命令列查詢 mysql 中的資料庫，dyw 及 dywphp 兩個資料庫及不見。

�1 [root@kvm8 html]# mysql -uroot -p123qwe -e "show databases"

+--------------------+3 | Database |



| test |9 +--------------------+� �


1. 以下列步驟完成 mysql 資料庫建立、刪除。

7.4. 實機操作練習題 CHAPTER 7. DATABASE 資料庫

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 產生資料庫 dbx。(d) 建立 createdb.php 產生資料庫 dbxphp。(e) 建立 selectdb.php 選取資料庫 dbxphp，無法選取時印出錯誤訊息 (抬頭：

Could not select: )，成功選取資料庫，印出"Selected successfully"。(f) 建立 dropdb.php 刪除資料庫 dbxphp。(g) 刪除資料庫 test。


CHAPTER 8. TABLES 資料表

Chapter 8

Tables 資料表

8.1 資料型態

1. 資料表是由多個欄位的資料集合而成，每個欄位必須設定適當的資料型態是非常重要的事，也才能讓資料庫最佳化。例如：某個欄位的資料只有 2 個字元，就不要定義該欄位為 10 字元寬。資料型態詳細說明參考

2. Numeric 數字資料型態

(a) INT - 最大 11 個位元，signed 允許範圍從 -2147483648 到 2147483647；unsigned 允許範圍從 0 到 4294967295。

(b) TINYINT - 最大 4 個位元，signed 允許範圍從 -128 到 127；unsigned 允許範圍從 0 到 255。

(c) SMALLINT - 最大 5 個位元，signed 允許範圍從 -32768 到 32767；unsigned允許範圍從 0 到 65535。

(d) MEDIUMINT - 最大 9 個位元，signed 允許範圍從 -8388608 到 8388607；unsigned 允許範圍從 0 到 16777215。

(e) BIGINT - 最大 20 個位元，signed 允許範圍從 -9223372036854775808 到9223372036854775807；unsigned 允許範圍從 0 到 18446744073709551615。

(f) FLOAT(M,D) - 浮點數總長度 M，小數點 D 位，預設 (M,D) = (10,2)。(g) DOUBLE(M,D) - 雙精確度浮點數，總長度 M，小數點 D 位，預設 (M,D)

= (16,4)。(h) DECIMAL(M,D) - 同 NUMERIC(M,D) 是 unpacked 浮點數。

3. 日期及時間型態：

(a) DATE - 日期格式 YYYY-MM-DD，介於 1000-01-01 到 9999-12-31。(b) DATETIME -日期時間合併格式 YYYY-MM-DD HH:MM:SS，從 1000-01-01

00:00:00 到 9999-12-31 23:59:59。(c) TIMESTAMP - 時間戳，從 1970 年 1 月 1 日 0 時 0 分開始計秒。(d) TIME - 時間格式 HH:MM:SS。(e) YEAR(M) - 年格式可以使用 2-digit 或 4-digit。

4. 字串型態：


http://www.tutorialspoint.com/mysql/mysql-data-types.htm

8.2. 產生資料表 CHAPTER 8. TABLES 資料表

(a) CHAR(M) - 固定長度的字串，M 可以從 1 到 255 字元，預設是 1。(b) VARCHAR(M) - 可變長度字串，M 可以從 1 到 255 字元，沒有預設值，使用時必須指定長度。

(c) BLOB or TEXT - 最長 65535 字元，BLOBs 是"Binary Large Objects" 儲存大量的二進位資料，例如 images。TEXT 與 BLOB 不同之處是搜尋與排序動作時 BOLB分大小寫，TEXT不分大小寫。兩者使用時都不須指定長度。

(d) TINYBLOB or TINYTEXT - 最大長度為 255 字元的 BLOB 或 TEXT。(e) MEDIUMBLOB or MEDIUMTEXT - 最大長度為 16777215 字元的 BLOB或 TEXT。

(f) LONGBLOB or LONGTEXT - 最大長度為 4294967295 字元的 BLOB 或TEXT。

(g) ENUM - 列舉格式，例如 ENUM (’A’, ’B’, ’C’) 表示該欄位只能是’A’, ’B’,’C’ 三種字元或 NULL。

8.2 產生資料表

1. 登入 mysql產生資料表 dyw_tbl，共有 4個欄位，dyw_id是主鍵，不能是 NULL且自動增加；dyw_title 最長 100 個字元的字串，不能是空的；dyw_author 最長 40 個字元的字串，不能是空的；submission_date 記錄寫入時間。

�1 [root@kvm8 html]# mysql -uroot -p


.........5 mysql> use dyw;

Database changed7 mysql> create table dyw_tbl(

-> dyw_id int not null auto_increment,9 -> dyw_title varchar(100) not null,

-> dyw_author varchar(40) not null,11 -> submission_date date,

-> primary key (dyw_id)13 -> );

Query OK, 0 rows affected (0.19 sec)� �2. 查看 dyw_tbl 資料表欄位型態。

�mysql> show columns from dyw_tbl;

2 +-----------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra|

4 +-----------------+--------------+------+-----+---------+----------------+


8.2. 產生資料表 CHAPTER 8. TABLES 資料表

| dyw_id | int(11) | NO | PRI | NULL |auto_increment |

6 | dyw_title | varchar(100) | NO | | NULL ||

| dyw_author | varchar(40) | NO | | NULL ||

8 | submission_date | date | YES | | NULL ||

+-----------------+--------------+------+-----+---------+----------------+

10 4 rows in set (0.00 sec)� �3. 查看 dyw 資料庫的資料表，出現 dyw_tbl。

�[root@kvm8 html]# mysql -uroot -p123qwe -e "use dyw; show tables;"

2 +---------------+| Tables_in_dyw |

4 +---------------+| dyw_tbl |

6 +---------------+� �4. 使用 php 函數 mysql_query 產生資料表 dywphp_tbl。

�[root@kvm8 html]# vim createtbl.php

2 [root@kvm8 html]# cat createtbl.php<html>

4 <head><title>Creating MySQL Tables</title>

6 </head><body>



12 $conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn )

14 {die(’Could not connect: ’ . mysql_error());

16 }echo ’Connected successfully ’;

18 $sql = "CREATE TABLE dywphp_tbl( "."dywphp_id INT NOT NULL AUTO_INCREMENT, ".

20 "dywphp_title VARCHAR(100) NOT NULL, "."dywphp_author VARCHAR(40) NOT NULL, ".

22 "submission_date DATE, "."PRIMARY KEY ( dywphp_id )); ";

24 mysql_select_db( ’dywphp’ );$retval = mysql_query( $sql, $conn );

8.3. 刪除資料表 CHAPTER 8. TABLES 資料表

26 if(! $retval ){

28 die(’Could not create table: ’ . mysql_error());}

30 echo "Table created successfully\n";mysql_close($conn);

32 ?></body>

34 </html>� �5. 以 php 命令執行 createtbl.php 在 dywphp 資料庫產生資料表 dywphp_tbl，使用瀏覽器開啟 selectdb.php 也可以。

�[root@kvm8 html]# php createtbl.php

2 <html><head>

4 <title>Creating MySQL Tables</title></head>

6 <body>Connected successfully Table created successfully


6. 查看 dywphp 資料庫的資料表，出現 dywphp_tbl。

�1 [root@kvm8 html]# mysql -uroot -p123qwe -e "use dywphp; show tables;"

+------------------+3 | Tables_in_dywphp |

+------------------+5 | dywphp_tbl |

+------------------+� �

8.3 刪除資料表

1. 登入 mysql 刪除資料表 dyw_tbl。

�[root@kvm8 html]# mysql -uroot -p123qwe

2 Welcome to the MySQL monitor. Commands end with ; or \g................

4 mysql> use dyw;Reading table information for completion of table and column names

6 You can turn off this feature to get a quicker startup with -A

8.3. 刪除資料表 CHAPTER 8. TABLES 資料表

8 Database changedmysql> drop table dyw_tbl;

10 Query OK, 0 rows affected (0.09 sec)

12 mysql> exitBye� �

2. 直接在命令列查詢 mysql 中的資料庫 dyw，沒有任何的資料表。

�1 [root@kvm8 html]# mysql -uroot -p123qwe -e "use dyw; show tables;"� �

3. 使用 php 函數 mysql_query 刪除資料庫 dywphp 中的資料表 dywphp_tbl。

�1 [root@kvm8 html]# vim droptbl.php

[root@kvm8 html]# cat droptbl.php3 <html>

<head>5 <title>Deleting MySQL Tables</title>

</head>7 <body>

<?php9 $dbhost = ’localhost:3306’;

$dbuser = ’root’;11 $dbpass = ’123qwe’;

$conn = mysql_connect($dbhost, $dbuser, $dbpass);13 if(! $conn )

{15 die(’Could not connect: ’ . mysql_error());

}17 echo ’Connected successfully ’;

$sql = "DROP TABLE dywphp_tbl";19 mysql_select_db( ’dywphp’ );

$retval = mysql_query( $sql, $conn );21 if(! $retval )

{23 die(’Could not delete table: ’ . mysql_error());

}25 echo "Table deleted successfully\n";

mysql_close($conn);27 ?>


4. 以 php 命令執行 droptbl.php 刪除 dywphp 資料庫中的資料表 dywphp_tbl，使用瀏覽器開啟 droptbl.php 也可以。

8.4. 實機操作練習題 CHAPTER 8. TABLES 資料表

�1 [root@kvm8 html]# php droptbl.php

<html>3 <head>

<title>Creating MySQL Tables</title>5 </head>

<body>7 Connected successfully Table deleted successfully


5. 直接在命令列查詢 mysql 中的資料庫 dywphp，沒有任何的資料表。

�1 [root@kvm8 html]# mysql -uroot -p123qwe -e "use dywphp; show tables;"� �


1. 以下列步驟完成 mysql 資料庫及資料表建立、刪除。

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 在資料庫 dbx 中建立資料表 tblx，包含下列欄位：

i. id int not null auto_increment,ii. title varchar(100) not null,iii. author varchar(40) not null,iv. score tinyint,v. submission_date date,vi. primary key (id)

(d) 建立 createtbl.php 產生資料庫 dbxphp 中的資料表 tblxphp，資料表內容與tblx 相同。

(e) 建立 droptbl.php 刪除資料庫 dbxphp 中的資料表 tblxphp。

CHAPTER 9. QUERY 紀錄詢問

Chapter 9

Query 紀錄詢問

9.1 Insert 新增紀錄1. 登入 mysql 切換工作資料表為 dyw_tbl，使用 insert 命令插入三筆紀錄。

�1 [root@kvm8 html]# mysql -s -uroot -p123qwe

mysql> use dyw;3 mysql> insert into dyw_tbl

-> (dyw_title, dyw_author, submission_date)5 -> values

-> ("PHP", "david", NOW());7 mysql> insert into dyw_tbl


-> ("MySQL", "dywang", ’2014-12-12’);11 mysql> insert into dyw_tbl


-> ("Linux", "linda", ’2014-12-24’);15 mysql> exit� �

2. 使用 php 程式，從網頁上輸入一筆紀錄，寫入資料表 dywphp_tbl。

(a) 當 get_magic_quotes_gpc() 打開時，所有的 ’ (單引號), " (雙引號), \ (反斜線) 及空字符會自動轉為含有反斜線的溢出字符。

(b) addslashes($str) 在指定的字串 $str 前加入反斜線。

(c) $_POST 抓取網頁上輸入的資料，不同於 $_GET 抓取網址問號後的變數。

�1 [root@kvm8 html]# vim insertentry.php

[root@kvm8 html]# cat insertentry.php3 <html>

<head>5 <title>Add New Record in MySQL Database</title>

</head>7 <body>

<?php

9.1. INSERT 新增紀錄 CHAPTER 9. QUERY 紀錄詢問

9 if(isset($_POST[’add’])){



15 if(! $conn ){


19if(! get_magic_quotes_gpc() )

21 {$dywphp_title = addslashes ($_POST[’dywphp_title’]);

23 $dywphp_author = addslashes ($_POST[’dywphp_author’]);}

25 else{

27 $dywphp_title = $_POST[’dywphp_title’];$dywphp_author = $_POST[’dywphp_author’];

29 }$submission_date = $_POST[’submission_date’];

31$sql = "INSERT INTO dywphp_tbl ".

33 "(dywphp_title,dywphp_author, submission_date) "."VALUES ".

35 "(’$dywphp_title’,’$dywphp_author’,’$submission_date’)";mysql_select_db(’dywphp’);


39 {die(’Could not enter data: ’ . mysql_error());

41 }echo "Entered data successfully\n";

43 mysql_close($conn);}

45 else{

47 ?><form method="post" action="<?php $_PHP_SELF ?>">

49 <table width="600" border="0" cellspacing="1" cellpadding="2"><tr>

51 <td width="250">Dywphp Title</td><td>

53 <input name="dywphp_title" type="text" id="dywphp_title"></td>

55 </tr><tr>

57 <td width="250">Dywphp Author</td><td>

59 <input name="dywphp_author" type="text" id="dywphp_author"></td>

61 </tr><tr>

63 <td width="250">Submission Date [ yyyy-mm-dd ]</td>

9.2. SELECT 查詢資料表 CHAPTER 9. QUERY 紀錄詢問

<td>65 <input name="submission_date" type="text" id="submission_date">

</td>67 </tr>

<tr>69 <td width="250"> </td>

<td> </td>71 </tr>

<tr>73 <td width="250"> </td>

<td>75 <input name="add" type="submit" id="add" value="Add Dywphp">

</td>77 </tr>

</table>79 </form>

<?php81 }

?>83 </body>

</html>� �3. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/insertentry.php，輸入一筆紀錄。

4. 按下「Add Dywphp」按鈕，會將網頁上輸入的資料寫入資料表 dywphp_tbl，並回傳寫入成功。

9.2 Select 查詢資料表1. 文字命令列直接查詢資料表 dyw_tbl 的內容。

�[root@kvm8 ~]# mysql -uroot -p123qwe \

2 -e "use dyw; select * from dyw_tbl;"+--------+-----------+------------+-----------------+

4 | dyw_id | dyw_title | dyw_author | submission_date |+--------+-----------+------------+-----------------+

6 | 1 | PHP | david | 2014-12-24 || 3 | MySQL | dywang | 2014-12-12 |

8 | 4 | Linux | linda | 2014-12-24 |+--------+-----------+------------+-----------------+� �

9.2. SELECT 查詢資料表 CHAPTER 9. QUERY 紀錄詢問

2. 文字命令列直接查詢資料表 dyw_tbl 的內容，且以欄位 dyw_title 排序。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe \

-e "use dyw; select * from dyw_tbl order by dyw_title;"3 +--------+-----------+------------+-----------------+

| dyw_id | dyw_title | dyw_author | submission_date |5 +--------+-----------+------------+-----------------+

| 4 | Linux | linda | 2014-12-24 |7 | 3 | MySQL | dywang | 2014-12-12 |

| 1 | PHP | david | 2014-12-24 |9 +--------+-----------+------------+-----------------+� �

3. 文字命令列直接查詢資料表 dyw_tbl 的內容，限制只列出一筆。


-e "use dyw; select * from dyw_tbl limit 1;"3 +--------+-----------+------------+-----------------+


| 1 | PHP | david | 2014-12-24 |7 +--------+-----------+------------+-----------------+� �

4. 文字命令列直接查詢資料表 dyw_tbl 的內容，限制只列出一筆，且位移一筆。


-e "use dyw; select * from dyw_tbl limit 1 offset 1;"3 +--------+-----------+------------+-----------------+


| 3 | MySQL | dywang | 2014-12-12 |7 +--------+-----------+------------+-----------------+� �

5. 以 php 程式以函數 mysql_fetch_array 函數取得資料表 dywphp_tbl 中的紀錄，顯示在網頁上。

(a) 函數 mysql_fetch_array 的第 2 個參數 MYSQL_ASSOC 表示回傳的陣列以欄位名稱為索引。

(b) mysql_fetch_array($retval, MYSQL_ASSOC) 也可以直接以函數mysql_fetch_assoc($retval) 取代。

(c) 若函數 mysql_fetch_array 的第 2 個參數改為 MYSQL_NUM 則會以數字從 0開始為索引值。

(d) 養成好習慣，取得資料後使用函數 mysql_free_result($retval); 釋放記憶體。


9.3. WHERE 條件 CHAPTER 9. QUERY 紀錄詢問

�1 [root@kvm8 html]# vim selectrecord.php

[root@kvm8 html]# cat selectrecord.php3 <?php

$dbhost = ’localhost:3306’;5 $dbuser = ’root’;


if(! $conn )9 {


$sql = ’SELECT dywphp_id, dywphp_title,13 dywphp_author, submission_date

FROM dywphp_tbl’;15

mysql_select_db(’dywphp’);17 $retval = mysql_query( $sql, $conn );

if(! $retval )19 {

die(’Could not get data: ’ . mysql_error());21 }

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))23 {

echo "Dywphp ID :{$row[’dywphp_id’]} ".25 "Title: {$row[’dywphp_title’]} ".

"Author: {$row[’dywphp_author’]} ".27 "Submission Date : {$row[’submission_date’]} ".

"-------------------------------- ";29 }

mysql_free_result($retval);31 echo "Fetched data successfully\n";

mysql_close($conn);33 ?>� �

6. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectrecord.php，網頁上列出資料表 dywphp_tbl 中的紀錄，其中第一筆紀錄時間為 0000-00-00 ，因為在網頁以函數 NOW() 輸入時間，php 程式將其判斷為字串。

9.3 Where 條件1. Where 條件運算子：

�1 = != > < >= <=

9.3. WHERE 條件 CHAPTER 9. QUERY 紀錄詢問

� �2. 查詢 dyw_author=’dywang’ 的紀錄。

�1 [root@kvm8 html]# mysql -uroot -p123qwe \

-e "use dyw; select * from dyw_tbl where dyw_author=’dywang’;"3 +--------+-----------+------------+-----------------+


| 3 | MySQL | dywang | 2014-12-12 |7 +--------+-----------+------------+-----------------+� �

3. 可用 AND 及 OR 結合多個條件。


-e "use dyw; select * from dyw_tbl where dyw_author=’dywang’ ORdyw_id=1;"

3 +--------+-----------+------------+-----------------+| dyw_id | dyw_title | dyw_author | submission_date |

5 +--------+-----------+------------+-----------------+| 1 | PHP | david | 2014-12-24 |

7 | 3 | MySQL | dywang | 2014-12-12 |+--------+-----------+------------+-----------------+� �

4. 原 php 程式的查詢變數 $sql 加入 where 條件。�[root@kvm8 html]# vim selectrecord.php

2 [root@kvm8 html]# cat selectrecord.php<?php



8 if(! $conn ){


12 $sql = ’SELECT dywphp_id, dywphp_title,dywphp_author, submission_date

14 FROM dywphp_tblWHERE dywphp_author="dywang"’;

16mysql_select_db(’dywphp’);


20 {

9.4. UPDATE 變更紀錄 CHAPTER 9. QUERY 紀錄詢問

die(’Could not get data: ’ . mysql_error());22 }

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))24 {

echo "Dywphp ID :{$row[’dywphp_id’]} ".26 "Title: {$row[’dywphp_title’]} ".

"Author: {$row[’dywphp_author’]} ".28 "Submission Date : {$row[’submission_date’]} ".

"-------------------------------- ";30 }

mysql_free_result($retval);32 echo "Fetched data successfully\n";


5. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectrecord.php，網頁上列出資料表 dywphp_tbl 中的紀錄，其中第一筆紀錄時間為 0000-00-00 ，因為在網頁以函數 NOW() 輸入時間，php 程式將其判斷為字串。

9.4 Update 變更紀錄1. 先列出資料表中的紀錄。

�[root@kvm8 html]# mysql -uroot -p123qwe \



6 | 1 | PHP | david | 2014-12-24 || 3 | MySQL | dywang | 2014-12-12 |

8 | 4 | Linux | linda | 2014-12-24 |+--------+-----------+------------+-----------------+� �

2. 變更紀錄 dyw_id=1 的 dyw_title 為 python。


-e "use dyw; update dyw_tbl set dyw_title=’python’ where dyw_id=1;"� �3. 再列出資料表中的紀錄，紀錄 dyw_id=1 的 dyw_title 已更新為 python。

9.4. UPDATE 變更紀錄 CHAPTER 9. QUERY 紀錄詢問




6 | 1 | python | david | 2014-12-24 || 3 | MySQL | dywang | 2014-12-12 |

8 | 4 | Linux | linda | 2014-12-24 |+--------+-----------+------------+-----------------+� �

4. 一次更新一筆紀錄的 2 個欄位。


-e "use dyw; update dyw_tbl set dyw_title=’c++’,dyw_author=’peter’ \3 where dyw_id=1;"� �

5. 再列出資料表中的紀錄，紀錄 dyw_id=1 的 dyw_title 及 dyw_author 2 個欄位都已更新。


-e "use dyw; select * from dyw_tbl;"3 +--------+-----------+------------+-----------------+


| 1 | c++ | peter | 2014-12-24 |7 | 3 | MySQL | dywang | 2014-12-12 |

| 4 | Linux | linda | 2014-12-24 |9 +--------+-----------+------------+-----------------+� �

6. 只要更改原 php 程式的查詢變數 $sql，改為 UPDATE 命令即可。�1 [root@kvm8 html]# vim updaterecord.php

[root@kvm8 html]# cat updaterecord.php3 <?php



if(! $conn )9 {


$sql = ’UPDATE dywphp_tbl13 SET dywphp_title="python"

WHERE dywphp_id=2’;

9.5. DELETE 刪除紀錄 CHAPTER 9. QUERY 紀錄詢問

15mysql_select_db(’dywphp’);


19 {die(’Could not get data: ’ . mysql_error());

21 }while($row = mysql_fetch_array($retval, MYSQL_ASSOC))

23 {echo "Dywphp ID :{$row[’dywphp_id’]} ".

25 "Title: {$row[’dywphp_title’]} "."Author: {$row[’dywphp_author’]} ".

27 "Submission Date : {$row[’submission_date’]} "."-------------------------------- ";

29 }mysql_free_result($retval);

31 echo "Fetched data successfully\n";mysql_close($conn);

33 ?>� �7. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/updaterecord.php，網頁上回傳’Fetched data sucessfully’。

8. 再開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectrecord.php，網頁上列出紀錄 dywphp_id=2 的 title 已變更為 python。

9.5 Delete 刪除紀錄1. 先列出資料表中的紀錄。


-e "use dyw; select * from dyw_tbl;"3 +--------+-----------+------------+-----------------+



| 4 | Linux | linda | 2014-12-24 |9 +--------+-----------+------------+-----------------+� �

2. 如果 DELETE 命令不配合 WHERE 條件使用，會刪除資料表中的所有資料。只刪除 dyw_id=4 的紀錄。

9.5. DELETE 刪除紀錄 CHAPTER 9. QUERY 紀錄詢問


-e "use dyw; delete from dyw_tbl where dyw_id=4;"� �3. 再查詢資料表 dyw_tbl，只剩下兩筆紀錄。

�[root@kvm8 ~]# mysql -uroot -p123qwe \



6 | 1 | c++ | peter | 2014-12-24 || 3 | MySQL | dywang | 2014-12-12 |

8 +--------+-----------+------------+-----------------+� �4. 除了顯示內容外，主要還是在 php程式的查詢變數 $sql，改為 DELET命令即可。�

[root@kvm8 ~]# cd /var/www/html/2 [root@kvm8 html]# vim deleterecord.php

[root@kvm8 html]# cat deleterecord.php4 <?php



if(! $conn )10 {


$sql = ’DELETE FROM dywphp_tbl14 WHERE dywphp_id=2’;

16 mysql_select_db(’dywphp’);$retval = mysql_query( $sql, $conn );

18 if(! $retval ){

20 die(’Could not delete data: ’ . mysql_error());}

22 echo "Deleted data successfully\n";mysql_close($conn);

24 ?>� �5. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectrecord.php，網頁上顯示有一筆資料。

9.6. LIKE 條件 CHAPTER 9. QUERY 紀錄詢問

6. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/deleterecord.php，網頁上顯示刪除成功。

7. 再開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectrecord.php，網頁上已經沒有任何錄。

9.6 Like 條件1. LIKE 配合 WHERE 使用，只是將等號改成 LIKE 即可，LIKE 後接的字串配合百分比 % 符號，可進行字串搜尋。例如：LIKE dyw% 表示搜尋 dyw 開頭的字串。


2 -e "use dyw; select * from dyw_tbl where dyw_author like ’dyw%’;"+--------+-----------+------------+-----------------+


6 | 3 | MySQL | dywang | 2014-12-12 |+--------+-----------+------------+-----------------+� �

2. 修改原 selectrecord.php 程式的查詢變數 $sql，WHERE 後的條件改為 LIKE 命令即可。�1 [root@kvm8 html]# vim selectlikerecord.php

[root@kvm8 html]# cat selectlikerecord.php3 <?php



if(! $conn )9 {


$sql = ’SELECT dywphp_id, dywphp_title,13 dywphp_author, submission_date

FROM dywphp_tbl15 WHERE dywphp_author LIKE "dyw%"’;


19 if(! $retval ){

21 die(’Could not get data: ’ . mysql_error());}

23 while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){

9.7. 實機操作練習題 CHAPTER 9. QUERY 紀錄詢問

25 echo "Dywphp ID :{$row[’dywphp_id’]} "."Title: {$row[’dywphp_title’]} ".

27 "Author: {$row[’dywphp_author’]} "."Submission Date : {$row[’submission_date’]} ".

29 "-------------------------------- ";}

31 mysql_free_result($retval);echo "Fetched data successfully\n";

33 mysql_close($conn);?>� �

3. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/selectlikerecord.php，因為剛剛已刪除紀錄，目前沒有任何資料，可以使用 php 程式新增紀錄，再查詢。


1. 登入

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。

2. php 程式一

(a) 使用資料庫 dbxphp，資料表 tblxphp，其中 dbxphp 及 tblxphp 隨變數調整。

(b) 建立 insertentry.php 可在資料庫 dbxphp 的資料表 tblxphp 中新增資料，dbxphp 及 tblxphp 不存在則先新增。

(c) 建立 selectrecord.php 查詢 insertentry.php 新增的資料是否存在？

3. 資料庫，資料表新增。

(a) 建立資料庫 dbq。(b) 在資料庫 dbq 中建立資料表 tblq，包含下列欄位：

i. id int not null auto_increment,ii. subject varchar(100) not null,iii. author varchar(40) not null,iv. score tinyint,v. primary key (id)

(c) 在資料表 tblq，依序加入五筆資料。�subject perl, author abc321, score 79

2 subject ruby, author abc234, score 65id 10, subject PHP, author abc124, score 90

4 id 11, subject python, author abc222, score 45subject ruby, author abc222, score 85� �

4. php 程式二

(a) 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法，其中資料庫名稱依題目要求修改。�1 <?php


$dbpass = ’123qwe’;5 $author = $_GET[’author’];

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error().PHP_EOL);

7 $sql = ".............";$retval = mysql_db_query( ’dbd’,$sql ) or die(mysql_error().

PHP_EOL);9 mysql_close($conn);

?>� �(b) tblq1.php：在資料表 tblq 中新增一筆資料，author 以變數由 php get 取得內容，id 自動產生，其他欄位自行決定內容。

(c) tblq2.php：刪除資料表 tblq 中 author=$author 的資料，變數 $author 由php get 取得。

5. 使用 GET 變數$_GET[’author’] 由網址加問號取得內容，如以下範例變數author=dywang。

�http://kvm6.deyu.wang/tblq1.php?author=’dywang’� �

6. php 程式三

(a) 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法，其中資料庫名稱依題目要求修改。�1 <?php


$dbpass = ’123qwe’;5 $str = $_GET[’str’];


7 $sql = ".............";$retval = mysql_db_query( ’dbd’,$sql ) or die(mysql_error().

PHP_EOL);9 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {

echo "{$row[0]}\t{$row[1]}\t{$row[2]}\t{$row[3]}\n";11 }


(b) tblq3.php：從資料表 tblq 中找出所有欄位資料並以 $str 排序，變數$str = $_GET(’str’) 由 php get 決定，查詢以符號’;’ 結束。

(c) tblq4.php：從資料表 tblq 中找出 author 後面幾個字元為 $str 的所有欄位資料，變數 $str = $_GET(’str’) 由 php get 決定，查詢以符號’;’ 結束。

(d) tblq5.php：更新資料表 tblq 中 author 後面幾個字元為 $str 的紀錄，其欄位 subject欄位資料都更新 linux，變數 $str = $_GET(’str’)由 php get決定。


CHAPTER 10. CLAUSE 紀錄詢問條件

Chapter 10

Clause 紀錄詢問條件

10.1 Sorting 排序1. 排序的語法 ORDER BY 經常與 WHERE ... LIKE 條件一起使用。可以使用任何一個欄位排序，也可以多個欄位一起排序，排序結果可以「升序」或「降序」排列。


-e "use dyw; select * from dyw_tbl order by dyw_id asc;"3 +--------+-----------+------------+-----------------+



+--------+-----------+------------+-----------------+9 [root@kvm8 ~]# mysql -uroot -p123qwe \

-e "use dyw; select * from dyw_tbl order by dyw_id desc;"11 +--------+-----------+------------+-----------------+


| 3 | MySQL | dywang | 2014-12-12 |15 | 1 | c++ | peter | 2014-12-24 |

+--------+-----------+------------+-----------------+� �2. php 程式的查詢變數 $sql 加入 ORDER BY dywphp_author DESC 條件。�

[root@kvm8 ~]# cd /var/www/html/2 [root@kvm8 html]# vim sorting.php

[root@kvm8 html]# cat sorting.php4 <?php



if(! $conn )10 {

die(’Could not connect: ’ . mysql_error());

10.2. JOIN 結合查詢 CHAPTER 10. CLAUSE 紀錄詢問條件

12 }$sql = ’SELECT dywphp_id, dywphp_title,

14 dywphp_author, submission_dateFROM dywphp_tbl

16 ORDER BY dywphp_author DESC’;


20 if(! $retval ){

22 die(’Could not get data: ’ . mysql_error());}

24 while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){

26 echo "Tutorial ID :{$row[’dywphp_id’]} "."Title: {$row[’dywphp_title’]} ".

28 "Author: {$row[’dywphp_author’]} "."Submission Date : {$row[’submission_date’]} ".

30 "-------------------------------- ";}

32 echo "Fetched data successfully\n";mysql_close($conn);

34 ?>� �3. 開啟瀏覽器，連上網址 http://kvm8.deyu.wang/sorting.php，出現兩筆紀錄且以 ID 降序排列。

10.2 Join 結合查詢1. 到目前為止，所有的詢問都只針對一個資料表，但實際工作時，往往是結合多個工作表的詢問。為了示範 JOIN 查詢，先新增一個與資料表 dyw 相關的資料表dcount_tbl，包含 dyw_author 及 dyw_count 兩個欄位，其中 dyw_author 與資料表 dyw_tbl 中的欄位 dyw_author 是相關的資料。

�[root@kvm8 html]# mysql -s -uroot -p123qwe

2 mysql> use dyw;mysql> create table dcount_tbl(

4 -> dyw_author varchar(40) not null,-> dyw_count int

6 -> );mysql>� �

10.2. JOIN 結合查詢 CHAPTER 10. CLAUSE 紀錄詢問條件

2. 在資料表 dcount_tbl 插入 4 筆紀錄。

�1 mysql> insert into dcount_tbl(dyw_author,dyw_count)

-> values3 -> (’dywang’, 4),

-> (’linda’, 3),5 -> (’peter’, 2),

-> (’rita’, 1)7 -> ;� �

3. 查詢資料表 dcount_tbl 共有 4 筆紀錄。

�1 mysql> select * from dcount_tbl;

dyw_author dyw_count3 dywang 4

linda 35 peter 2

rita 1� �

4. 查詢資料表 dyw_tbl 共有 3 筆紀錄，其中 2 筆作者同時也出現在資料表dcount_tbl。

�mysql> select * from dyw_tbl;

2 dyw_id dyw_title dyw_author submission_date1 c++ peter 2014-12-24

4 3 MySQL dywang 2014-12-125 Perl david 2014-12-27� �

5. JOIN 結合兩個資料表 dyw_tbl 及 dcount_tbl 的查詢，FROM 後的兩個資料表各給 a 及 b 來代表，WHERE 則限制兩個資料表同時出現的紀錄。查詢方式是搜尋欄位 dyw_author 同時出現在資料表 a 與 b 的紀錄。JOIN 也可使用逗號「,」代替。

�1 mysql> select a.dyw_id,a.dyw_author,b.dyw_count

-> from dyw_tbl a JOIN dcount_tbl b3 -> where a.dyw_author=b.dyw_author;

dyw_id dyw_author dyw_count5 3 dywang 4

1 peter 2� �


10.3. NULL 值 CHAPTER 10. CLAUSE 紀錄詢問條件

6. LEFT JOIN 不同 JOIN，是選擇左邊資料表所有紀錄，若右邊資料表有共同的紀錄則一起列出。因為資料表 dcount_tbl 中並沒有 dyw_author=’david’，所以這筆紀錄的 dyw_count 為 NULL。

�mysql> select a.dyw_id,a.dyw_author,b.dyw_count

2 -> from dyw_tbl a left join dcount_tbl b-> on a.dyw_author=b.dyw_author;

4 dyw_id dyw_author dyw_count1 peter 2

6 3 dywang 45 david NULL� �

7. RIGHT JOIN 則是選擇右邊資料表所有紀錄，若邊資料表有共同的紀錄則一起列出。因為資料表 dyw_tbl 中並沒有 dyw_author=’linda’ 及dyw_author=’rita’，所以這兩筆紀錄的 dyw_id 及 dyw_author 為 NULL。

�1 mysql> select a.dyw_id,a.dyw_author,b.dyw_count

-> from dyw_tbl a right join dcount_tbl b3 -> on a.dyw_author=b.dyw_author;

dyw_id dyw_author dyw_count5 3 dywang 4

NULL NULL 37 1 peter 2

NULL NULL 1� �

10.3 NULL 值1. 先繼續在資料表 dcount_tbl 新增兩筆紀錄且其 dyw_count 都是空的值。

�2 ot@kvm8 ~]# mysql -s -uroot -p123qwe

mysql> use dyw;4 mysql> insert into dcount_tbl(dyw_author,dyw_count)

-> values6 -> (’jenny’, null),

-> (’sara’,null)8 -> ;� �

2. 要查詢資料表 dcount_tbl 中欄位 dyw_count 是空的紀錄，不能使用運算子=, !=。


10.4. REGEXP 正規表示 CHAPTER 10. CLAUSE 紀錄詢問條件

�mysql> select * from dcount_tbl where dyw_count = null;

2 mysql> select * from dcount_tbl where dyw_count != null;� �3. 要查詢資料表 dcount_tbl 中欄位 dyw_count 是空的紀錄，必須使用

IS NULL, IS NOT NULL 等語法。

�mysql> select * from dcount_tbl where dyw_count is null;

2 dyw_author dyw_countjenny NULL

4 sara NULLmysql> select * from dcount_tbl where dyw_count is not null;

6 dyw_author dyw_countdywang 4

8 linda 3peter 2

10 rita 1� �4. <=> 運算子等效 IS NULL 語法。�

ql> select * from dcount_tbl where dyw_count <=> null;2 dyw_author dyw_count

jenny NULL4 sara NULL� �

10.4 REGEXP 正規表示1. 查詢時雖可以使 LIKE ...% 搜尋符合的紀錄，但對於例如開頭為數字的字串就無法滿足。MySQL 支援正規表示法，只要使用 REGEXP 就可以使用正規表示查詢，非常方便。正規表示運算如下：�字符

2 RE 意義與範例^word 待搜尋的字串 (word)在行首。

4 #範例：grep -n ’^#’ re.txt搜尋行首為# 開始的那一行。

6 word$ 待搜尋的字串 (word)在行尾。#範例：grep -n ’!$’ re.txt將行尾為

8 ! 的那一行列印出來。. 代表『任意一個』字符，一定是一個任意字符。

10 #範例：grep -n ’e.e’ re.txt搜尋的字串可以是(eve) (eae) (eee) (e e)，但不能僅有 (ee)。亦即

12 e 與 e 中間『一定』僅有一個字元，而空白字元也是字元。\ 跳脫字符，將特殊符號的特殊意義去除。

14 #範例：grep -n \’ re.txt搜尋含有單引號

10.4. REGEXP 正規表示 CHAPTER 10. CLAUSE 紀錄詢問條件

’ 的那一行。16 * 重複零個或多個的前一個 RE 字符

#範例：grep -n ’ess*’ re.txt找出含有18 (es) (ess) (esss) 等等的字串。

{n,m} 連續 n 到 m 個的『前一個 RE 字符』若為20 {n} 則是連續 n 個的前一個 RE 字符，若是

{n,} 則是連續 n 個以上的前一個 RE 字符。22 #範例：grep -n ’go{2,3}g’ re.txt在

g 與 g 之間有 2 個到 3 個的 o 存在的字串，亦即 (goog)(gooog)24 [ ] 在 [ ] 當中『謹代表一個待搜尋的字元』

#範例：grep -n ’g[ld]’ re.txt搜尋含有26 (gl) 或 (gd) 的那一行

#範例：grep -n ’[0-9]’ re.txt搜尋含有任意數字的那一行。在字元集合

28[ ] 中的減號 - 是代表兩個字元之間的所有連續字元。：

30 [^]^ 在 [ ] 內時，代表的意義是『反向選擇』#範例：grep -n ’oo[^t]’ re.txt搜尋的字串可以是

32 (oog) (ood) 但不能是 (oot)。+ 重複『一個或一個以上』的前一個 RE 字符

34 #範例：egrep -n ’go+d’ re.txt搜尋(god) (good) (goood)... 等等的字串。

36 ? 『零個或一個』的前一個 RE 字符#範例：egrep -n ’go?d’ re.txt搜尋

38 (gd) (god) 這兩個字串。| 用或 ( or )的方式找出數個字串

40 #範例：egrep -n ’gd|good|dog’ re.txt搜尋gd 、 good 或 dog 這三個字串。

42 ( ) 找出『群組』字串或作為『多個重複群組』的判別#範例：egrep -n ’g(la|oo)d’搜尋

44 (glad) 或 (good) 這兩個字串。#範例：egrep -n ’A(xyz)+C’ re.txt找開頭是

46 A 結尾是 C ，中間有一個以上的 "xyz" 字串。� �2. 先列出資料表 docunt_tbl 中的所有紀錄。

�1 [root@kvm8 ~]# mysql -s -uroot -p123qwe

mysql> use dyw;3 mysql> select * from dcount_tbl;

dyw_author dyw_count5 dywang 4

linda 37 peter 2

rita 19 jenny NULL

sara NULL� �3. 列出資料表 docunt_tbl 中 dyw_author 以 l 開頭的紀錄。


10.5. 實機操作練習題 CHAPTER 10. CLAUSE 紀錄詢問條件

�mysql> select * from dcount_tbl where dyw_author regexp ’^l’;

2 dyw_author dyw_countlinda 3� �

4. 列出資料表 docunt_tbl 中 dyw_author 以 a 結尾的紀錄。

�1 mysql> select * from dcount_tbl where dyw_author regexp ’a$’;

dyw_author dyw_count3 linda 3

rita 15 sara NULL� �

5. 列出資料表 docunt_tbl 中 dyw_author 以 l,p,r 開頭的紀錄。

�1 mysql> select * from dcount_tbl where dyw_author regexp ’^[lpr]’;


peter 25 rita 1� �

6. 列出資料表 docunt_tbl 中 dyw_author 以 l,r 開頭或包含 ra 字串的紀錄。

�1 mysql> select * from dcount_tbl where dyw_author regexp ’^[lp]|ra’;


peter 25 sara NULL� �


1. 登入


2. 新增資料庫及資料表

(a) 新增資料庫 dbc(b) 在資料庫 dbc 中建立資料表 tblcx，包含下列欄位：


i. id int not null auto_increment,ii. title varchar(30),iii. author varchar(40) not null,iv. primary key (id)

(c) 在資料表 tblcx 中加入五筆資料。i. author abc123ii. author xyz222iii. title ruby, author abc321iv. title perl, author xyz123v. title php, author xyz456

(d) 在資料庫 dbc 中建立資料表 tblcy，包含下列欄位：i. author varchar(40) not null,ii. count int

(e) 在資料表 tblcy 中加入六筆資料。i. author abc777, count 23ii. author xyz777, count 12iii. author abc321, count 43iv. author xyz123, count 83v. author xyz456, count 92vi. author abcxyz, count 69

3. 不要自行新增或改變資料庫及資料表內容

4. 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法，其中資料庫名稱依題目要求修改。�1 <?php




7 $sql = ".............";$retval = mysql_db_query( ’dbd’,$sql ) or die(mysql_error().PHP_EOL);

9 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {echo "{$row[0]}\t{$row[1]}\t{$row[2]}\n";

11 }mysql_close($conn);

13 ?>� �5. php 程式

(a) join.php：使用 JOIN 結合 tblcx 及 tblcy 兩個資料表，找出 author 同時出現在兩個資料表，而且 author 最前面符合 $str 的紀錄，變數 $str 由 phpget 取得。列出 tblcx.id, tblcx.author, tblcy.count。


(b) leftjoin.php：使用 LEFT JOIN選擇 tblcx資料表所有紀錄，若 tblcy資料表有共同的紀錄則一起列出，而且 author最前面符合 $str的紀錄，變數 $str由 php get 取得。列出 tblcx.id, tblcx.author, tblcy.count，條件為 author 同時出現在兩個資料表的紀錄。

(c) rightjoin.php：使用 RIGHT JOIN 選擇 tblcy 資料表所有紀錄，若 tblcx 資料表有共同的紀錄則一起列出，而且 author 最前面符合 $str 的紀錄，變數 $str 由 php get 取得。列出 tblcx.id, tblcx.author, tblcy.count，條件為author 同時出現在兩個資料表的紀錄。

(d) null.php：查詢 tblcx 資料表 title 為 null 的紀錄，依序列出 id, title, author欄位，其中 title 使用 IFNULL(title, ’$str’) 將查詢到的 NULL 欄位輸出成$str，變數 $str 由 php get 取得。

(e) regexp.php：查詢 tblcy資料表 author以「$str開頭數字結尾」的紀錄，變數 $str 由 php get 取得。列出所有欄位，但因 tblcy 只有兩個欄位，所以php 檔中的陣列 row 輸出 $row[2]} 必須刪除。


CHAPTER 11. ALTER 語法

Chapter 11

ALTER 語法

11.1 ALTER 簡介1. ALTER 可以變更資料表名稱。

2. ALTER 可以變更、增加、刪除已存在資料表的任何欄位。

11.2 ADD 新增1. 實作前先查看資料表 dyw_tbl 的欄位格式。�

1 [root@kvm8 ~]# mysql -s -uroot -p123qwemysql> use dyw;

3 mysql> show columns from dyw_tbl;Field Type Null Key Default Extra

5 dyw_id int(11) NO PRI NULL auto_incrementdyw_title varchar(100) NO NULL

7 dyw_author varchar(40) NO NULLsubmission_date date YES NULL

9 mysql>� �

2. ADD 在資料表 dyw_tbl 最前面增加一個欄位 ti，格式為 TINYINT。

�1 mysql> alter table dyw_tbl add ti tinyint first;

mysql> show columns from dyw_tbl;3 Field Type Null Key Default Extra

ti tinyint(4) YES NULL5 dyw_id int(11) NO PRI NULL auto_increment

dyw_title varchar(100) YES NULL7 dyw_author varchar(40) NO NULL

submission_date date YES NULL� �


11.3. MODIFY 修改 CHAPTER 11. ALTER 語法

3. ADD 在資料表 dyw_tbl 欄位 dyw_id 後新加一個欄位 c，格式為一個字元char(1)。

�mysql> alter table dyw_tbl add c char(1) after dyw_id;


4 ti tinyint(4) YES NULLdyw_id int(11) NO PRI NULL auto_increment

6 c char(1) YES NULLdyw_title varchar(100) YES NULL

8 dyw_author varchar(40) NO NULLsubmission_date date YES NULL� �

11.3 MODIFY 修改1. MODIFY 改變資料表 dyw_tbl 欄位 c 的格式由原固定 1 個字元改為 10 個字元。

�1 mysql> alter table dyw_tbl modify c char(10);


ti tinyint(4) YES NULL5 dyw_id int(11) NO PRI NULL auto_increment

c char(10) YES NULL7 dyw_title varchar(100) YES NULL

dyw_author varchar(40) NO NULL9 submission_date date YES NULL� �

11.4 CHANGE 改變1. CHANGE 同時改變資料表 dyw_tbl 欄位 ti 的名稱及格式，由原 ti 格式

TINYINT 改成 bi 格式 bigint。

�1 mysql> alter table dyw_tbl CHANGE ti bi bigint;


bi bigint(20) YES NULL5 dyw_id int(11) NO PRI NULL auto_increment

c char(10) YES NULL7 dyw_title varchar(100) YES NULL

dyw_author varchar(40) NO NULL9 submission_date date YES NULL� �


11.5. DROP 刪除 CHAPTER 11. ALTER 語法

11.5 DROP 刪除1. DROP 刪除資料表 dyw_tbl 欄位 c。

�1 mysql> alter table dyw_tbl DROP c;


bi bigint(20) YES NULL5 dyw_id int(11) NO PRI NULL auto_increment

dyw_title varchar(100) YES NULL7 dyw_author varchar(40) NO NULL

submission_date date YES NULL� �

11.6 SET DEFAULT 設定預設值1. NOT NULL DEFAULT 設定資料表 dyw_tbl 欄位 bi 不可是空的且指定預設值為

100。

�mysql> alter table dyw_tbl modify bi bigint not null default 100;


4 bi bigint(20) NO 100dyw_id int(11) NO PRI NULL auto_increment

6 dyw_title varchar(100) YES NULLdyw_author varchar(40) NO NULL

8 submission_date date YES NULL� �2. ALTER SET DEFAULT 變更資料表 dyw_tbl 欄位 bi 的預設值為 50。

�mysql> alter table dyw_tbl alter bi set default 50;


4 bi bigint(20) NO 50dyw_id int(11) NO PRI NULL auto_increment


8 submission_date date YES NULL� �3. ALTER DROP DEFAULT 變更資料表 dyw_tbl 欄位 bi 的預設值。

�mysql> alter table dyw_tbl alter bi drop default;


11.7. RENAME 重新命令 CHAPTER 11. ALTER 語法


4 bi bigint(20) NO NULLdyw_id int(11) NO PRI NULL auto_increment


8 submission_date date YES NULL� �

11.7 RENAME 重新命令1. ALTER RENAME TO 變更資料表 dyw_tbl 名稱為 alter_tbl。

�mysql> alter table dyw_tbl rename to alter_tbl;

2 mysql> show tables;Tables_in_dyw

4 alter_tbldcount_tbl� �

2. ALTER RENAME TO 再將資料表 alter_tbl 名稱變更回 dyw_tbl。

�1 mysql> alter table alter_tbl rename to dyw_tbl;

mysql> show tables;3 Tables_in_dyw

dcount_tbl5 dyw_tbl� �


1. 登入


2. 資料庫、資料表建立：

(a) 產生資料庫 dba。(b) 在資料庫中建立資料表 tbla，包含下列欄位：

i. id int not null auto_increment,ii. student varchar(40) not null,iii. subject varchar(50) not null,iv. score int,


11.8. 實機操作練習題 CHAPTER 11. ALTER 語法

v. date date,vi. primary key (id)

3. 使用以下 shell 命令，查詢資料庫 dba 的資料表 tbla 的欄位特徵，並導向到/tmp/alter1.txt。�1 [root@kvm6 ~]# mysql -s -uroot -p123qwe -e ’use dba; .......;’� �

4. 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法，其中資料庫名稱依題目要求修改。�1 <?php




7 $sql = ".............";$retval = mysql_db_query( ’dbd’,$sql ) or die(mysql_error().PHP_EOL);


5. php 程式

(a) alter2.php：資料表 tbla 最前面增加一個欄位 $str 格式為 tinyint，變數$str 由 php get 取得。

(b) alter3.php：資料表 tbla 欄位 id 後新加一個欄位 $str 格式為 char(4)，變數$str 由 php get 取得。

(c) alter4.php：資料表 tbla 欄位 student 的名稱及格式，改成 $str 格式char(10)，變數 $str 由 php get 取得。

(d) alter5.php：刪除資料表 tbla 欄位 $str，變數 $str 由 php get 取得。(e) alter6.php：設定資料表 tbla 欄位 $str 型態 bigint、不可是空的且指定預設值是 137，變數 $str 由 php get 取得。

(f) alter7.php：變更資料表 tbla 名稱為 $str，變數 $str 由 php get 取得。

11.8. 實機操作練習題 CHAPTER 11. ALTER 語法


CHAPTER 12. KEY 及 INDEX

Chapter 12

KEY 及 INDEX

12.1 Index 簡介1. 資料庫 index 是一種可以加速操作的資料結構，就如電話薄，要找「王小明」，可以先看查索引看「王」在第幾頁，就直接翻到那一頁開始找，而不必從第一頁開始找。

2. MySQL 建立可以於一個或多個列中產生 indexes，以提供加速搜尋及有效率的紀錄排序。但跟電話薄建立索引會多佔用頁面一樣，資料庫建立 indexes 會佔用一部分記憶體。

3. MySQL 的 index (key)

(a) Primary key: 主鍵，必須不重複、不能為空值。每個資料表只能有一個主鍵。

(b) Unique key: 不重複鍵，必須不重複、可以為空值。不一定要限定某一列為不重複鍵，可以多列組合成不重複鍵。

(c) Index: 索引，不限定不重複或空值。

(d) Fulltext key: 全文字鍵，一種允許全文字搜尋的專屬的索引。

12.2 Primary key 主鍵1. 資料表 dcount_tbl 沒有索引。

�[root@kvm8 ~]# mysql -s -uroot -p123qwe

2 mysql> use dyw;mysql> show tables;

4 Tables_in_dywdcount_tbl

6 dyw_tblmysql> show index from dcount_tbl\G

8 mysql>� �


12.3. 組合主鍵 CHAPTER 12. KEY 及 INDEX

2. 於資料表 dcount_tbl 欄位 dyw_author 加入主鍵，其中 Key_name: PRIMARY 表示主鍵，Non_qunique: 0 表示不重複。Collation: How the column is sorted in the index. In MySQL, this can have values“A”(Ascending) or NULL (Not sorted).Cardinality: An estimate of the number of unique values in the index.

�mysql> alter table dcount_tbl add primary key (dyw_author);

2 mysql> show index from dcount_tbl\G*************************** 1. row ***************************

4 Table: dcount_tblNon_unique: 0

6 Key_name: PRIMARYSeq_in_index: 1

8 Column_name: dyw_authorCollation: A

10 Cardinality: 6Sub_part: NULL

12 Packed: NULLNull:

14 Index_type: BTREEComment:

16 Index_comment:� �

3. 刪除資料表 dcount_tbl 的主鍵，因每個資料表只能有一個主鍵，所以不用指定欄位。

�mysql> alter table dcount_tbl drop primary key;

2 mysql> show index from dcount_tbl\Gmysql>� �

12.3 組合主鍵

1. 登入 mysql。

�1 [root@kvm6 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe

mysql 5.5.493 mycli 1.19.0


Home: http://mycli.net7 Thanks to the contributor - Klaus Wünschel� �


2. 使用資料庫 dyw。

�1 mysql root@localhost:(none)> use dyw;

You are now connected to database "dyw" as user "root"3 Time: 0.000s� �

3. 產生資料表 employees，有 id, last_name, first_name, phone 四個欄位，其中 last_name, first_name 兩個欄位組合成 primary key。

�1 mysql root@localhost:dyw> create table employees ( id int unsigned

not null, last_namevarchar (20), first_name varchar(20),

primary key(last_name,3 first_name), phone varchar(20));

Query OK, 0 rows affected5 Time: 0.293s� �

4. 列出資料表 employees 的所有欄位特徵。

�1 mysql root@localhost:dyw> desc employees;

+------------+------------------+------+-----+---------+-------+3 | Field | Type | Null | Key | Default | Extra |

+------------+------------------+------+-----+---------+-------+5 | id | int(10) unsigned | NO | | <null> | |

| last_name | varchar(20) | NO | PRI | | |7 | first_name | varchar(20) | NO | PRI | | |

| phone | varchar(20) | YES | | <null> | |9 +------------+------------------+------+-----+---------+-------+

4 rows in set11 Time: 0.027s� �

5. employees 增加兩筆資料。

�1 mysql root@localhost:dyw> insert into employees (id,last_name,

first_name,phone) values (1,’wang’,’dy’,’09123456’), (2,’

lin’,’da’,’3 09234561’);

Query OK, 2 rows affected5 Time: 0.011s� �

6. 查詢 employees 有兩筆資料。

�1 mysql root@localhost:dyw> select * from employees;

+----+-----------+------------+----------+3 | id | last_name | first_name | phone |

+----+-----------+------------+----------+5 | 2 | lin | da | 09234561 |

| 1 | wang | dy | 09123456 |7 +----+-----------+------------+----------+

2 rows in set9 Time: 0.009s� �

7. 再增加一筆 last_name = wang，first_name = da。

�1 mysql root@localhost:dyw> insert into employees (id,last_name,

first_name,phone) values (1,’wang’,’da’,null);

3 Query OK, 1 row affectedTime: 0.003s� �

8. last_name = wang，first_name = da，last_name, first_name 都與現有資料重複，但合在一起沒有重複，所以成功加入。

�mysql root@localhost:dyw> select * from employees;

2 +----+-----------+------------+----------+| id | last_name | first_name | phone |

4 +----+-----------+------------+----------+| 2 | lin | da | 09234561 |

6 | 1 | wang | da | <null> || 1 | wang | dy | 09123456 |

8 +----+-----------+------------+----------+3 rows in set

10 Time: 0.022s� �9. 再增加一筆 last_name = lin，first_name = da，last_name, first_name 組合與現有資料重複，所以無法加入。

�mysql root@localhost:dyw> insert into employees (id,last_name,

first_name,phone2 ) values (1,’lin’,’da’,null);

(1062, u"Duplicate entry ’lin-da’ for key ’PRIMARY’")� �

12.4. UNIQUE KEY 不重複鍵 CHAPTER 12. KEY 及 INDEX

12.4 Unique key 不重複鍵1. 資料表 dcount_tbl 沒有索引。


mysql> use dyw;3 mysql> show tables;

Tables_in_dyw5 dcount_tbl

dyw_tbl7 mysql> show index from dcount_tbl\G

mysql>� �2. 先查詢資料表 dcount_tbl 欄位 dyw_author 沒有重複。

�mysql> select * from dcount_tbl;

2 +------------+-----------+| dyw_author | dyw_count |

4 +------------+-----------+| abc123 | 23 |

6 | abc321 | 43 || abcxyz | NULL |

8 | xyz123 | 83 || xyz222 | 12 |

10 | xyz456 | NULL |+------------+-----------+

12 6 rows in set (0.00 sec)� �3. 於資料表 dcount_tbl 加入一筆資料，欄位 dyw_author abc123 重複。

�mysql> insert into dcount_tbl (dyw_author,dyw_count) values ("abc123"

,"34");2 Query OK, 1 row affected (0.07 sec)� �

4. 再查詢資料表 dcount_tbl 欄位 dyw_author abc123 有重複。

�mysql> select * from dcount_tbl;

2 +------------+-----------+| dyw_author | dyw_count |

4 +------------+-----------+| abc123 | 23 |

6 | abc321 | 43 || abcxyz | NULL |


12.4. UNIQUE KEY 不重複鍵 CHAPTER 12. KEY 及 INDEX

8 | xyz123 | 83 || xyz222 | 12 |

10 | xyz456 | NULL || abc123 | 34 |

12 +------------+-----------+7 rows in set (0.00 sec)� �

5. 設定資料表 dcount_tbl 欄位 dyw_author 加入不重複鍵不成功，因為有資料重複。

�1 mysql> alter table dcount_tbl add unique key(dyw_author);

ERROR 1062 (23000): Duplicate entry ’abc123’ for key ’author’� �6. 刪除資料表 dcount_tbl 欄位 dyw_author 中重複的一筆資料。

�mysql> delete from dcount_tbl where dyw_count=’34’;

2 Query OK, 1 row affected (0.13 sec)� �7. 再設定資料表 dcount_tbl 欄位 dyw_author 加入不重複鍵成功。

�mysql> alter table dcount_tbl add unique key(dyw_author);

2 Query OK, 0 rows affected (0.32 sec)Records: 0 Duplicates: 0 Warnings: 0� �

8. 查詢資料表 dcount_tbl index，其中 Non_qunique: 0 表示不重複。add unique(field) 與 add unique key (field) 結果一樣。

�1 mysql> alter table dcount_tbl add unique (dyw_author);

mysql> show index from dcount_tbl\G3 *************************** 1. row ***************************

Table: dcount_tbl5 Non_unique: 0

Key_name: dyw_author7 Seq_in_index: 1

Column_name: dyw_author9 Collation: A

Cardinality: 611 Sub_part: NULL

Packed: NULL13 Null:

Index_type: BTREE


12.5. INDEX 索引鍵 CHAPTER 12. KEY 及 INDEX

15 Comment:Index_comment:

17 mysql>� �9. 已無法於資料表 dcount_tbl 加入欄位 dyw_author 重複的資料。

�1 mysql> insert into dcount_tbl (dyw_author,dyw_count) values ("abc123"

,"34");ERROR 1062 (23000): Duplicate entry ’abc123’ for key ’author’� �

10. 刪除資料表 dcount_tbl 的不重複鍵，因不重複鍵是索引，所以使用drop index dyw_author。

�mysql> alter table dcount_tbl drop index dyw_author;

2 mysql> show index from dcount_tbl\Gmysql>� �

12.5 Index 索引鍵1. 主鍵不可重複不可為 NULL，不重複鍵不可重複但可為 NULL，索引鍵可以重複。將資料表常用的搜尋條件設成索引鍵，可加速搜尋速度。查詢資料表dcount_tbl 沒有索引。


mysql> use dyw;3 mysql> show tables;

Tables_in_dyw5 dcount_tbl

dyw_tbl7 mysql> show index from dcount_tbl\G

mysql>� �2. 於資料表 dcount_tbl 欄位 dyw_author 加入索引，其中 Non_unique: 1 表示不限定不重複。

�mysql> alter table dcount_tbl add index (dyw_author);

2 mysql> show index from dcount_tbl\G*************************** 1. row ***************************

4 Table: dcount_tbl


12.6. 組合索引鍵 CHAPTER 12. KEY 及 INDEX

Non_unique: 16 Key_name: dyw_author

Seq_in_index: 18 Column_name: dyw_author

Collation: A10 Cardinality: 6

Sub_part: NULL12 Packed: NULL

Null:14 Index_type: BTREE

Comment:16 Index_comment:

mysql>� �

12.6 組合索引鍵

1. 多鍵組合索引又比單鍵索引的搜尋速度快，如果常搜尋兩個欄位的話，最好設定成組會索引鍵。登入 mysql。

�1 [root@kvm6 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -p123qwe

mysql 5.5.493 mycli 1.19.0


Home: http://mycli.net7 Thanks to the contributor - Phil Cohen� �

2. 使用 dyw 資料庫。

�1 mysql root@localhost:(none)> use dyw;

You are now connected to database "dyw" as user "root"3 Time: 0.001s� �

3. 產生測試資料表 indextab。

�1 mysql root@localhost:dyw> create table indextab ( id int unsigned not

null, lnamevarchar (20), fname varchar(20), phone

varchar(20));3 Query OK, 0 rows affected

Time: 0.028s� �


12.6. 組合索引鍵 CHAPTER 12. KEY 及 INDEX

4. 查看資料表 indextab 的欄位特徵。

�mysql root@localhost:dyw> desc indextab;

2 +-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |

4 +-------+------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | | <null> | |

6 | lname | varchar(20) | YES | | <null> | || fname | varchar(20) | YES | | <null> | |

8 | phone | varchar(20) | YES | | <null> | |+-------+------------------+------+-----+---------+-------+

10 4 rows in setTime: 0.031s� �

5. 設定 lname 及 fname 組合成名為 name 的索引鍵。

�1 mysql root@localhost:dyw> alter table indextab add index name (lname,

fname);You’re about to run a destructive command.

3 Do you want to proceed? (y/n): yYour call!

5 Query OK, 0 rows affectedTime: 0.305s� �

6. 新增兩筆資料。

�mysql root@localhost:dyw> insert into indextab (id,lname,fname,phone)

values (1,’2 wang’,’dy’,’09123456’), (2,’lin’,’da

’,’09234561’);Query OK, 2 rows affected

4 Time: 0.198s� �7. 使用 name 索引鍵查詢，但本範例僅兩筆資料，無法比對有無索引的搜尋速度差別。

�mysql root@localhost:dyw> select * from indextab use index(name)

where lname=’wan2 g’ and fname=’dy’;

+----+-------+-------+----------+4 | id | lname | fname | phone |

+----+-------+-------+----------+6 | 1 | wang | dy | 09123456 |

12.7. SEQUENCE 序列 CHAPTER 12. KEY 及 INDEX

+----+-------+-------+----------+8 1 row in set

Time: 0.028s� �8. 如果資料表中設定多個索引，搜尋時可用 ignore 指定不使用某個索引鍵查詢，例如：不使用 name 索引鍵查詢。

�1 mysql root@localhost:dyw> select * from indextab ignore index(name)

where lname=’wang’ and fname=’dy’;

3 +----+-------+-------+----------+| id | lname | fname | phone |

5 +----+-------+-------+----------+| 1 | wang | dy | 09123456 |

7 +----+-------+-------+----------+1 row in set

9 Time: 0.029s� �

12.7 Sequence 序列1. 序列是一組順序整數如 1,2,3,... 的集合，常使用於資料庫中。MySQL 中新增資料表 animals，包含 id,name,foot 三個欄位，其中 id 定義為 auto_increment的主鍵，讓該列數字編號不重複，且每筆紀錄新增時自動加 1。


mysql> use dyw;3 mysql> create table animals(

-> id int unsigned not null auto_increment,5 -> primary key(id),

-> name varchar(20) not null,7 -> foot tinyint unsigned not null

-> );9 mysql>� �

2. 於資料表 animals 新增三筆資料，其中 id 故意設定為空值。

�1 mysql> insert into animals(id,name,foot)

-> values3 -> (NULL,’dog’,4),

-> (NULL,’cat’,4),5 -> (NULL,’bird’,2);

mysql>� �De-Yu Wang CSIE CYUT 96


3. 查詢資料表 animals，欄位 id 並沒有因用戶設定為空值而真的為空值，而是從1 開始編號，每筆自動增加 1。

�mysql> select * from animals;

2 id name foot1 dog 4

4 2 cat 43 bird 2

6 mysql>� �4. 刪除 id=3 的紀錄。

�mysql> delete from animals where id=3;

2 mysql>� �5. 再新增一筆 chicken 紀錄。

�mysql> insert into animals(name,foot) values (’chicken’,2);

2 mysql>� �6. 查詢資料表 animals 中 chicken 紀錄的 id=4，並不是 3。



4 2 cat 44 chicken 2

6 mysql>� �7. 因為刪除了 id=3的紀錄，新增的紀錄 id=4，造成 id不連續，如果要重新編號，必須先刪除 id 欄位，再重新產生，其編號就會重新編號。不過要特別注意的是，若此資料表與其他資料表關聯，可能會造成資料對應錯誤，要特別小心。刪除欄位 id。

�mysql> alter table animals drop id;

2 mysql>� �



8. 再增加 id 欄位於資料表 animals 最前頭 (first 就是指定在第一列) 。

�mysql> alter table animals

2 -> add id int unsigned not null auto_increment first,-> add primary key (id);

4 mysql>� �9. 查詢資料表 animals，id 編號已從 1 重新編號。




6 mysql>� �10. 設定往後新增的紀錄，id 編號從 15 編起。

�mysql> ALTER TABLE animals AUTO_INCREMENT = 15;


11. 新增一筆紀錄。

�1 mysql> insert into animals(name,foot) values (’bird’,2);

Query OK, 1 row affected (0.47 sec)� �12. 查詢資料表 animals，新增的紀錄 id 編號已從 15 開始。


2 +----+---------+------+| id | name | foot |

4 +----+---------+------+| 1 | dog | 4 |

6 | 2 | cat | 4 || 3 | chicken | 2 |

8 | 15 | bird | 2 |+----+---------+------+

10 4 rows in set (0.00 sec)� �De-Yu Wang CSIE CYUT 98

12.8. 實機操作練習題 CHAPTER 12. KEY 及 INDEX


1. 前置處理

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 在資料庫 dbk 進行以下資料表的處理，如果資料庫 dbk 不存在，請自行產生。

2. primary key

(a) 建立資料表 keya，包含下列欄位，指定 phone 為 primary key。i. id int unsigned not null,ii. name varchar(30),iii. phone varchar(20)

(b) 下載檔案 key1.tbl，將其匯入 dbk 並設定欄位 id 為 primary key。(c) 下載檔案 key2.tbl，將其匯入 dbk 並清除 primary key。(d) 建立資料表 keyb，包含下列欄位，指定 lname 及 fname 組合為 primary

key。i. id int unsigned not null,ii. lname varchar(30),iii. fname varchar(30),iv. phone varchar(10)

(e) 下載檔案 key3.tbl，將其匯入 dbk 並設定欄位 lname 及 fname 組合為 pri-mary key。

(f) 下載檔案 key4.tbl，將其匯入 dbk 並清除 primary key。

3. unique key

(a) 建立資料表 keyc，包含下列欄位，指定 uid 為 unique key。i. uid int unsigned not null,ii. user varchar(30),

(b) 下載檔案 key5.tbl，將其匯入 dbk 並設定欄位 uid 為 unique key。(c) 下載檔案 key6.tbl，將其匯入 dbk 並清除 unique key。

4. index

(a) 建立資料表 keyd，包含下列欄位，指定 uid 為 index。i. uid int unsigned not null,ii. user varchar(30),

(b) 下載檔案 key7.tbl，將其匯入 dbk 並設定欄位 uid 為 index。(c) 下載檔案 key8.tbl，將其匯入 dbk 並清除 index。(d) 建立資料表 keye，包含下列欄位，指定 uid 與 user 組合為 index。

i. uid int unsigned not null,


http://dywang.csie.cyut.edu.tw/dywang/download/key1.tbl








12.8. 實機操作練習題 CHAPTER 12. KEY 及 INDEX

ii. user varchar(30),iii. home varchar(40)

(e) 下載檔案 key9.tbl，將其匯入 dbk 並設定欄位 uid 與 user 組合成名為 login的 index。

(f) 下載檔案 key10.tbl，將其匯入 dbk 並清除 index。

5. 自動編號

(a) 建立資料表 keyf，包含下列欄位，指定 uid 為自動增加數字編號。i. uid int unsigned not null,ii. user varchar(30),iii. home varchar(40)

(b) 下載檔案 key11.tbl，將其匯入 dbk 並設定欄位 uid 為自動增加數編號，編號從 500 編起，至少再新增一筆紀錄。

(c) 下載檔案 key12.tbl，將其匯入 dbk 並清除 primary key 及自動編號。






CHAPTER 13. 其他

Chapter 13

其他

13.1 Clone Tables 複製資料表1. 使用語法 CREATE TABLE ... SELECT 複製資料表，index 及 key 都無法複製。

2. 要複製一個完全一樣的資料表，可使用以下三個步驟：

(a) 使用語法 SHOW CREATE TABLE ...\G 列出原資料表完整結構。


2 mysql> use dyw;mysql> show create table animals\G

4 *************************** 1. row ***************************Table: animals

6 Create Table: CREATE TABLE ‘animals‘ (‘id‘ int(10) unsigned NOT NULL AUTO_INCREMENT,

8 ‘name‘ varchar(20) NOT NULL,‘foot‘ tinyint(3) unsigned NOT NULL,

10 PRIMARY KEY (‘id‘)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

12 mysql>� �(b) 產生一個新的資料表 clone_animals，除了第一行名稱不一樣，其他幾行直接複製貼上。

�mysql> create table ‘clone_animals‘ (

2 -> ‘id‘ int(10) unsigned NOT NULL AUTO_INCREMENT,-> ‘name‘ varchar(20) NOT NULL,

4 -> ‘foot‘ tinyint(3) unsigned NOT NULL,-> PRIMARY KEY (‘id‘)

6 -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;mysql>� �


13.1. CLONE TABLES 複製資料表 CHAPTER 13. 其他

(c) 複製貼上太麻煩了，還可以使用語法 CREATE TABLE newtable LIKE oldtable複製表格欄位。

�1 mysql> CREATE TABLE clone_animals LIKE animals;

mysql>� �(d) 使用語法 INSERT INTO ... SELECT ... FROM 從原資料表複製紀錄到新的資料表。

�mysql> insert into clone_animals (id,name,foot)

2 -> select id,name,foot from animals;mysql>� �

(e) 也可以簡化語法 INSERT newtable SELECT * FROM oldtable 從原資料表複製紀錄到新的資料表。

�1 mysql> INSERT clone_animals SELECT * FROM animals;

mysql>� �3. 新的資料的結構與原資料表完全一樣。

�mysql> show create table clone_animals\G

2 *************************** 1. row ***************************Table: clone_animals

4 Create Table: CREATE TABLE ‘clone_animals‘ (‘id‘ int(10) unsigned NOT NULL AUTO_INCREMENT,

6 ‘name‘ varchar(20) NOT NULL,‘foot‘ tinyint(3) unsigned NOT NULL,

8 PRIMARY KEY (‘id‘)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

10 mysql>� �4. 新的資料的資料與原資料表完全一樣。




6 mysql> select * from clone_animals;


13.2. METADATA 後設資料 CHAPTER 13. 其他

id name foot8 1 dog 4

2 cat 410 3 chicken 2

mysql>� �

13.2 Metadata 後設資料1. MySQL 本身就有一些指令，可以查看資料庫訊息。先登入 MySQL。


mysql>� �2. 查看版本為 5.5.38。

�mysql> select version();

2 version()5.5.38

4 mysql>� �3. 目前使用的資料庫空的。

�mysql> select database();

2 database()NULL

4 mysql>� �4. 使用 dyw 資料庫，再查詢。

�mysql> use dyw;

2 mysql> select database();database()

4 dywmysql>� �

5. 查看目前的使用者。


13.2. METADATA 後設資料 CHAPTER 13. 其他

�1 mysql> select user();

user()3 root@localhost

mysql>� �6. 使用 show status 查看狀態，但資料很多，一般都會配合 like 查詢，以下是只查有 table 字串的訊息。

�mysql> show status like "%table%";

2 Variable_name ValueCom_alter_table 0

4 Com_alter_tablespace 0Com_create_table 0

6 Com_drop_table 0Com_lock_tables 0

8 Com_rename_table 0Com_show_create_table 0

10 Com_show_open_tables 0Com_show_table_status 0

12 Com_show_tables 1Com_unlock_tables 0

14 Created_tmp_disk_tables 0Created_tmp_tables 5

16 Open_table_definitions 39Open_tables 32

18 Opened_table_definitions 0Opened_tables 0

20 Performance_schema_table_handles_lost 0Performance_schema_table_instances_lost 0

22 Slave_open_temp_tables 0Table_locks_immediate 162

24 Table_locks_waited 0mysql>� �

7. 使用 show variables 查看變數，但變數很多，一般都會配合 like 查詢，以下是只查包含 character 字串的變數。

�1 mysql> show variables like "%character%";

Variable_name Value3 character_set_client utf8

character_set_connection utf85 character_set_database utf8

character_set_filesystem binary7 character_set_results utf8

character_set_server utf89 character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/


13.3. TEMPORARY 暫時資料表 CHAPTER 13. 其他

11 mysql>� �

13.3 Temporary 暫時資料表1. 暫時資料表只有連線時使用，client session 終止後，也就是登出後就刪除。

2. 拍賣網站上的購物車機制，不需要保存到下次登入使用，就可使用 temporarytable。

3. 先登入 mysql，使用 dyw 資料庫。


mysql> use dyw;3 mysql>� �

4. 產生一個暫時資料表 sales。

�1 mysql> create temporary table sales(

-> product varchar(20) not null,3 -> price decimal(7,2) not null default 0.00

-> );� �5. 暫時資料表 sales 存在，但使用 Show tables 不會出現。

�mysql> show tables;

2 Tables_in_dywanimals

4 clone_animalsdcount_tbl

6 dyw_tblperson_tbl

8 mysql>� �6. 在暫時資料表 sales 新增一筆紀錄。

�mysql> insert into sales(product,price)

2 -> values(’computer’,20000);� �


13.4. 實機操作練習題 CHAPTER 13. 其他

7. 列出暫時資料表 sales 的紀錄。

�mysql> select * from sales;

2 product pricecomputer 20000.00

4 mysql>� �8. 登出 mysql。

�mysql> exit

2 [root@kvm8 ~]#� �9. 再登入 mysql，並使用 dyw 資料庫。


2 mysql> use dyw;� �10. 查看上次產生暫時資料表 sales 已不存在。

�mysql> select * from sales;

2 ERROR 1146 (42S02): Table ’dyw.sales’ doesn’t existmysql>� �


1. 前置處理

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 在資料庫 dbk 進行以下資料表的處理，如果資料庫 dbk 不存在，請自行產生。

2. clone table

(a) 建立資料表 clone1，包含下列欄位，設定 id為 primary key，name為 index。i. id int unsigned not null,ii. name varchar(30),


iii. address varchar(50)(b) 在資料表 clone1 中應該至少會有兩筆紀錄，若有很多筆也沒關係。如果沒有，請自行任意產生兩筆紀錄。

(c) 再將資料表 clone1 複製成 clone2，複製的不只是紀錄，還包含 index, key 等欄位特徵，比對 clone1 及 clone2 兩個資料表應該要完全一樣。

3. Metadata

(a) 請在 shell 命令列查出 mysql 的版本，導向到 /tmp/metadataversion。

(b) 請在 shell 命令列查出 mysql 中包含 ssl 字串的所有狀態 (status)，導向到/tmp/metadatassl。

(c) 請在 shell 命令列查出 mysql 含 max_join_size 字串的變數 (variables) 大小，導向到 /tmp/metadatamaxjoinsize。

(d) 在 shell 下執行 mysql，再導向到指定的檔案，範例：

�1 [root@kvm6 ~]# mysql -s -uroot -p123qwe -e ’use dbk;show

database();’ > /tmp/metadatadb� �4. 暫時資料表

(a) 寫一 sales1.php 檔放在/var/www/html 目錄下，建立資料表 sales1，包含下列欄位，id 自動編號。

i. id int unsigned not null,ii. product varchar(30),iii. price int unsigned not null default 0

(b) 在 sales1 新增一筆資料 (ssd 1399)。(c) 查詢 sales1 的所有資料並導向到/tmp/sales1.txt(d) php 程式範例：

�1 <?php


$dbpass = ’123qwe’;5 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(

mysql_error().PHP_EOL);mysql_select_db( ’dbk’ );

7 $sql = "CREATE temporary table ...... ;" ;mysql_query( $sql, $conn ) or die(mysql_error().PHP_EOL);

9 $sql = "insert into ...... ; ";mysql_query( $sql, $conn ) or die(mysql_error().PHP_EOL);

11 $sql = "select .... into outfile ’/tmp/sales1.txt’; ";$retval = mysql_query( $sql, $conn ) or die(mysql_error().

PHP_EOL);13 mysql_close($conn);


?>� �


CHAPTER 14. DUPLICATES 重複紀錄

Chapter 14

Duplicates 重複紀錄

14.1 避免重複紀錄

1. 資料表出現重複的紀錄是被允許的，但有時必須避免或移除。如果要避免寫入重複紀錄，可於產生資料表時，定義主鍵列或不重複列。以下例子建立資料表person，共有三個欄位，其中 last_name, first_name 組合成主鍵。


2 mysql> use dyw;mysql> CREATE TABLE person

4 -> (-> first_name CHAR(20) NOT NULL,

6 -> last_name CHAR(20) NOT NULL,-> sex CHAR(10),

8 -> PRIMARY KEY (last_name, first_name)-> );

10 mysql>� �2. 使用 INSERT IGNORE 語法於資料表 person 新增一筆紀錄並查詢。

�mysql> INSERT IGNORE INTO person (last_name, first_name)

2 -> values(’Wang’, ’De-Yu’);mysql> select * from person;

4 first_name last_name sexDe-Yu Wang NULL

6 mysql>� �3. 直接使用 INSERT 語法新增紀錄時，若此紀錄已存在，因為有設定主鍵，其具不重複性，所以出現紀錄重複的錯誤訊息。

�mysql> INSERT INTO person (last_name, first_name)

2 -> values(’Wang’, ’De-Yu’);


14.1. 避免重複紀錄 CHAPTER 14. DUPLICATES 重複紀錄

ERROR 1062 (23000): Duplicate entry ’Wang-De-Yu’ for key ’PRIMARY’� �4. INSERT IGNORE 語法新增紀錄時，若此紀錄已存在，則忽略不動作。因此，再執行一次沒有出現錯誤訊息，但查詢結果還是只有一筆。

�1 mysql> INSERT IGNORE INTO person (last_name, first_name)

-> values(’Wang’, ’De-Yu’);3 mysql> select * from person;

first_name last_name sex5 De-Yu Wang NULL

mysql>� �5. REPLACE 語法新增紀錄時，若此紀錄已存在，則取代原紀錄。

�mysql> REPLACE INTO person (last_name, first_name)

2 -> values(’Lin’,’Linda’);mysql> select * from person;

4 first_name last_name sexLinda Lin NULL

6 De-Yu Wang NULLmysql>� �

6. 使用 INSERT IGNORE 語法新增紀錄，此紀錄已存在，但要新增的非主鍵紀錄資料不同，還是忽略不新增。

�1 mysql> INSERT IGNORE INTO person (last_name, first_name, sex)

-> values(’Lin’,’Linda’,’F’);3 mysql> select * from person;

first_name last_name sex5 Linda Lin NULL

De-Yu Wang NULL7 mysql>� �

7. 使用 REPLACE 語法新增紀錄，此紀錄已存在，但要新增的非主鍵紀錄資料不同，會以新的資料取代舊紀錄。

�1 mysql> REPLACE INTO person (last_name, first_name,sex)

-> values(’Lin’,’Linda’,’F’);3 mysql> select * from person;

first_name last_name sex5 Linda Lin F


14.2. 計算重複紀錄 CHAPTER 14. DUPLICATES 重複紀錄

De-Yu Wang NULL7 mysql>� �

14.2 計算重複紀錄

1. 先登入 MySQL，使用 dyw 資料庫。



2. 查詢資料庫 dyw 有那些資料表。

�1 mysql> show tables;

Tables_in_dyw3 animals

clone_animals5 dcount_tbl

dyw_tbl7 person

mysql>� �3. 查詢資料表 animals 所有紀錄。



4 2 cat 43 chicken 2� �

4. 新增一筆重複的紀錄。

�1 mysql> insert into animals(name,foot)

-> values(’cat’,4);� �5. 紀錄 cat 有兩筆，但 id 不同。


14.3. 不重複輸出重複紀錄 CHAPTER 14. DUPLICATES 重複紀錄




6 4 cat 4mysql>� �

6. 使用 SELECT COUNT(*) ... GROUP BY ... HAVING ... 語法計算重複紀錄。

�1 mysql> select count(*) as repetitions, name,foot

-> from animals3 -> group by name,foot

-> having repetitions >1;5 repetitions name foot

2 cat 47 mysql>� �

14.3 不重複輸出重複紀錄

1. 使用 DISTINCT 語法讓重複紀錄只輸出一筆。

�1 mysql> select distinct name,foot

-> from animals3 -> order by name;

name foot5 cat 4

chicken 27 dog 4

mysql>� �2. 使用 SELECT ... GROUP BY ... 語法可以自行組合重複紀錄判斷欄位。

�mysql> select name,foot from animals

2 -> group by name,foot;name foot

4 cat 4chicken 2

6 dog 4mysql>� �


14.4. 刪除重複紀錄 CHAPTER 14. DUPLICATES 重複紀錄

3. 比較 DISTINCT 與 SELECT ... GROUP BY ... 語法之差異，先輸出目前資料表的內容有三筆紀錄。

�1 mysql> select * from animals;

id name foot3 1 dog 4

15 cat 45 16 chicken 2� �

4. DISTINCT 語法指定「組合的欄位」只輸出一筆，也輸出「組合的欄位」，無法輸出其他欄位。

�1 mysql> select distinct foot from animals;

foot3 4

2� �5. DISTINCT name,foot 表示欄位 name+foot 組合不重複，只輸出欄位 name 及

foot。

�mysql> select distinct name,foot from animals;

2 name footdog 4

4 cat 4chicken 2� �

6. 若要指定某一欄位不重複，但輸出所有欄位的內容，就必須使用SELECT ... GROUP BY ... 語法，select * 輸出所有欄位內容，group by foot指定欄位 foot 成群組，也就是只輸出一筆。

�1 mysql> select * from animals group by foot;

id name foot3 16 chicken 2

1 dog 4� �

14.4 刪除重複紀錄

1. 查詢資料表 animals，其中 cat 重複兩筆。


14.4. 刪除重複紀錄 CHAPTER 14. DUPLICATES 重複紀錄




6 4 cat 4mysql>� �

2. 產生一個名為 tmp 的暫時資料表，其紀錄來自資料表 animals，但重複紀錄只存一筆。

�1 mysql> create table tmp

-> select id,name,foot3 -> from animals

-> group by name,foot;5 mysql>� �

3. 刪除原始資料表 animals。

�1 mysql> drop table animals;

mysql>� �4. 重新名命資料表 tmp 為 animals。

�mysql> alter table tmp rename to animals;

2 mysql>� �5. 查詢新的資料表 animals 已沒有重複紀錄。


2 id name foot2 cat 4

4 3 chicken 21 dog 4

6 mysql>� �6. 如果照以上方式重新產生新的資料表 animals，原始設定的欄位資料格式可能不見，id 重新設定為 primary key。


14.5. 實機操作練習題 CHAPTER 14. DUPLICATES 重複紀錄

�mysql> alter table animals add primary key(id);


7. 重新設定 id 欄位的格式，包含自動增加。

�1 mysql> alter table animals modify id int unsigned not null

auto_increment;Query OK, 4 rows affected (0.16 sec)

3 Records: 4 Duplicates: 0 Warnings: 0� �8. 重新設定自動增加從 150 開始編號。

�1 mysql> ALTER TABLE animals AUTO_INCREMENT = 150;

Query OK, 4 rows affected (0.37 sec)3 Records: 4 Duplicates: 0 Warnings: 0� �


1. 前置處理

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 在資料庫 dbd 進行以下資料表的處理，如果資料庫 dbd 不存在，請自行產生。

2. 避免重複紀錄

(a) 建立資料表 dup1，包含下列欄位，設定 lname 與 fname 組合為 primarykey。

i. lname char(20) not null,ii. fname char(20) not null,iii. sex char(5)

(b) 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法。

�1 <?php


$dbpass = ’123qwe’;5 $lname = $_GET[’lname’];


$fname = $_GET[’fname’];7 $sex = $_GET[’sex’];

$conn=mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error().PHP_EOL);

9 $sql = ".............";mysql_db_query( ’dbd’,$sql ) or die(mysql_error().PHP_EOL);


(c) 三個 GET 變數$_GET[’lname’], $_GET[’fname’], $_GET[’sex’] 由網址加問號取得內容，如以下範例三個變數分別為 lname=wang, fname=dy,sex=M。

�http://kvm6.deyu.wang/dup1.php?lname=wang&fname=dy&sex=M� �

(d) dup1a.php：在 dup1 資料表新增紀錄，若 primary key 重複時只是不新增，但不能出現紀錄重複的錯誤訊息「Duplicate entry....」。

(e) dup1b.php：在 dup1 資料表新增紀錄，若 primary key 重複時取代舊紀錄。

3. 重複紀錄計算與查詢

(a) 下載檔案 dup2.tbl，將其匯入 dbd。(b) 承上題 PHP範例，修改 mysql_db_query函數，列印其輸出的第一個欄位。

�1 $result=mysql_db_query( ’dbd’,$sql ) or die(mysql_error().

PHP_EOL);echo mysql_fetch_row($result)[0];� �

(c) dup2a.php：由 get 指定 lname，搜尋 lname 符合的資料，並計算 dup2 資料表 lname 組合 fname 重複的紀錄筆數，只輸出「筆數」。

(d) dup2b.php：由 get 指定 sex，搜尋 sex 符合的資料，並計算 dup2 資料表所有欄位都相同的紀錄筆數，只輸出「筆數」。

(e) dup2c.php：使用 distinct 查詢 dup2 資料表 lname 及 fname 組合不重複紀錄限制只列印一筆且 offset 參數由 GET 變數 offset 取得。

(f) dup2d.php：使用 select ... group by .. 查詢 dup2 資料表 lname 及 fname組合不重複紀錄，限制只輸出 lname、列印一筆且 offset 參數由 GET 變數offset 取得。

4. 刪除重複紀錄

(a) 下載檔案 dup3.tbl，將其匯入 dbd。(b) 刪除欄位 name, sex 組合重複的紀錄。(c) 設定欄位 id 為 int unsigned not null auto_increment。


http://dywang.csie.cyut.edu.tw/dywang/download/dup2.tbl

http://dywang.csie.cyut.edu.tw/dywang/download/dup3.tbl


(d) 設定欄位 id 為 primary key，重新編號且從 60 開始。(e) 新增一筆紀錄 (julia , F)。


CHAPTER 15. SQL INJECTION 注入

Chapter 15

SQL Injection 注入

15.1 前言

1. 使用 PHP+MySQL 架設的網頁，當網頁要求使用者輸入資料，以存取 mysql 資料庫時，若 PHP 程式沒有預防被「注入」的處理，駭客不需要任何工具，很容易就可以破壞或讀取你的資料庫中的機密資料。

2. 程式設計者不能一相情願的認為，使用者會依照你設定的格式輸入資料，而是要竭盡可能的排除不是系統需要的輸入。

3. PHP 已有函數可以去除造成 sql 注入危險的字符。

4. SQL Injection 問題很容易解決，不處理造成的危害可大可小，要視系統本身狀況而定，輕者只是查到不該看的資料，重者整個資料庫損壞，所以不應該置之不理。

15.2 SQL 注入查詢1. 範例：查詢使用者帳號。以 $_GET[’user’] 函數取得在 php 網址後加入的

?user=... 參數查詢，其中 MYSQL_NUM 指定使用數字索引。

�[root@kvm8 ~]# cd /var/www/html

2 [root@kvm8 html]# vim injection.php[root@kvm8 html]# cat injection.php



8 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error().PHP_EOL);

$sql = "SELECT host,user FROM user WHERE user={$_GET[’user’]}";10 mysql_select_db(’mysql’);

$retval = mysql_query( $sql, $conn );12 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {

echo "HOST: {$row[0]} User: {$row[1]} ".14 "-------------------------------- ";

}16 ?>

15.3. 限制輸入字元 CHAPTER 15. SQL INJECTION 注入

� �2. 在 php 網址後加入 ?user=’dywang’，查詢結果列出 host 及 user。

3. 在 php 網址後加入 ?user=’a’ or 1，查詢結果列出所有 host 及 user，因為可以注入 or 1 的條件。

4. 在 php 網址後加入 ?user=’a’ union all select host,user from user，查詢結果列出所有 host 及 user，因為可以注入 union all .... 的條件。

5. 在 php 網址後加入 ?user=’a’; drop table user，查詢結果空白，因為 PHP的 mysql_query 函數只接受單一詢問，加入分號來進行第二個詢問不被接受。

15.3 限制輸入字元

1. 範例：查詢使用者帳號。增加查詢限制只能輸入 4 至 20 個字母、數字或下底線組合的字元。

�[root@kvm8 ~]# cd /var/www/html

2 [root@kvm8 html]# vim injection.php[root@kvm8 html]# cat injection.php

4 <?php

15.4. PHP 避免 SQL 注入 CHAPTER 15. SQL INJECTION 注入


$dbpass = ’123qwe’;8 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error()

.PHP_EOL);

10 if (preg_match("/^\w{4,20}$/", $_GET[’user’], $matches)){$sql = "SELECT host,user FROM user WHERE user=’{$matches[0]}’";

12 mysql_select_db(’mysql’);$retval = mysql_query( $sql, $conn );

14 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {echo "HOST: {$row[0]} User: {$row[1]} ".

16 "-------------------------------- ";}

18 } else {echo "user not accepted";

20 }?>� �

2. 在 php 網址後加入 ?user=abc，回應"user not accepted"。

3. 在 php 網址後加入 ?user=’a’ or 1，過濾字元後一樣回應"user not accepted"。

4. 在 php 網址後加入 ?user=dywang，查詢結果列出 host 及 user。

5. 在 php 網址後加入 ?user=1234567890，字串符合要求，但資料庫中無此紀錄。

15.4 PHP 避免 SQL 注入1. PHP 之 magic_quotes_gpc 的美麗與哀愁：PHP 為了防止 user 端送到 server 端的資料，會被惡意內容攻擊，有 SQL injection 的疑慮，因此很體貼地設計一個magic_quotes_gpc 開關，當此開關 on 時，$_GET、$_POST、$_COOKIE 等從 user端來的資料，如果含有單引號、雙引號、反斜線等內容，會自動被加上倒反斜線跳脫該字元，也就是做 addslashes() 的處理，以免程式設計師直接把資料串在SQL 指令上，導致系統沒兩天就被駭客爆台。不過 php 5.4 以上版本，不支援此開關。

2. 範例：查詢使用者帳號。使用 PHP 的 get_magic_quotes_gpc 函數判斷magic_quotes_gpc 是否開啟，如果沒有開啟，則將輸入字串中指定的字符加入倒斜線，也就是跳脫其作用。本練習系統 PHP 版本為 5.4.xx，故以下範例get_magic_quotes_gpc 函數判斷永遠為關閉，會執行 addslashes 函數。


http://ithelp.ithome.com.tw/question/10113856

15.5. SQL INJECTION 實例 CHAPTER 15. SQL INJECTION 注入

�1 [root@kvm8 ~]# cd /var/www/html

[root@kvm8 html]# vim escape.php3 [root@kvm8 html]# cat escape.php

<?php5 $dbhost = ’localhost:3306’;

$dbuser = ’root’;7 $dbpass = ’123qwe’;


9//if(! get_magic_quotes_gpc() ) {

11 $user = addslashes ($_GET[’user’]);//} else {

13 $user = $_GET[’user’];//}

15$sql = "SELECT host,user FROM user WHERE user=’{$user}’";

17 mysql_select_db(’mysql’);$retval = mysql_query( $sql );

19 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {echo "HOST: {$row[0]} User: {$row[1]} ".

21 "-------------------------------- ";}

23 ?>� �

3. 在 php 網址後加入 ?user=’a’ or 1，如果可以 SQL Injection，應該會回傳所有user，但卻回應空白，也就是沒有 |user=’a’ or 1| 這個用戶。

4. 在 php 網址後加入 ?user=dywang，查詢結果列出 host 及 user。

15.5 SQL injection 實例

1. PHP _GET 輸入資料 NewsID=’ or newsid=12--，NewID=’ 結束查詢條件，緊接著注入 or newsid=12--，其中 -- 將後接的字串註解，則會以出理 newid=12 的內容，表示可以 sql injection。

15.6. 實機操作練習題 CHAPTER 15. SQL INJECTION 注入


1. 前置處理

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 在資料庫 dbi 進行以下資料表的處理，如果資料庫 dbi 不存在，請自行產生。

2. Sql injection 查詢

(a) 建立資料表 sqlinj1，包含下列欄位，設定 iid 為 primary key。i. iid int not null auto_increment,ii. plang varchar(40) not null

(b) 資料表 sqlinj1 新增 c,ruby,perl,python 四筆資料。(c) 使用以下 php 範例程式，其中 ........ 依要求填入 mysql 語法。

�1 <?php


$dbpass = ’123qwe’;5 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or mysql_error

().PHP_EOL;$sql = "select ........ ;" ;

7 mysql_select_db(’dbi’);$retval = mysql_query( $sql, $conn );

9 while($row = mysql_fetch_array($retval, MYSQL_NUM)) {echo "{$row[0]}\t{$row[1]}";

11 }?>� �

15.6. 實機操作練習題 CHAPTER 15. SQL INJECTION 注入

(d) 使用 GET 變數$_GET[’plang’] 由網址加問號取得內容，如以下範例變數plang=python。

�http://kvm6.deyu.wang/sqlinj1.php?plang=’python’� �

(e) sqlinj1a.php：刻意不用單引號或雙引號括起查詢變數 $_GET[’plang’]，使得其可以進行 sql injection。

(f) sqlinj1b.php：承上題 sqlinj1a.php，將查詢變數 $_GET[’plang’] 以單引號括起來，以避免 sql injection。

3. 限制輸入字元

(a) pmatch1a.php：承上題 sqlinj1a.php，先使用 preg_match 函數限制變數$_GET[’plang’] 只能非空白字元，否則印出"Not Match"。

(b) pmatch1b.php：承上題 sqlinj1a.php，先使用 preg_match 函數限制變數$_GET[’plang’] 必須是小寫英文字母，否則印出"Not Match"。

(c) pmatch1c.php：承上題 sqlinj1b.php，先使用 preg_match 函數限制變數$_GET[’plang’] 必須是 1 到 6 個小寫英文字母，否則印出"Not Match"。

4. addslashes

(a) slashe1.php：承上題 sqlinj1a.php，先使用 addslashes 函數將變數$_GET[’plang’] 中的單引號、雙引號、倒斜線等符號，加上倒斜線跳脫其功能，並使用 echo 在 mysql 查詢命令之前印出變數 $_GET[’plang’] 內容。

(b) 修改 slashe1.php 讓其對於沒加單引號的查詢，可以正常工作。


CHAPTER 16. EXPORT 匯出

Chapter 16

Export 匯出

16.1 匯出到檔案

1. 先登入 mysql，使用 dyw 資料庫。



2. 使用語法 SELECT ... INTO OUTFILE 匯出資料到檔案。

�1 mysql> select * from animals

-> into outfile ’/tmp/animals.txt’;3 mysql>� �

3. 再執行一次，出現檔案已經存在的錯誤訊息，也就是要存入的檔案不能已存在，這是為了避免使用者製造嚴重的損壞。

�1 mysql> select * from animals into outfile ’/tmp/animals.txt’;

ERROR 1086 (HY000): File ’/tmp/animals.txt’ already exists3 mysql>� �

4. 在終端機查看匯出的檔案。

�1 [root@kvm8 ~]# cat /tmp/animals.txt


1 dog 4� �De-Yu Wang CSIE CYUT 125

16.2. 匯出 RAW DATA CHAPTER 16. EXPORT 匯出

5. 使用語法 SELECT ... INTO OUTFILE 並指定格式匯出資料到檔案。

�mysql> select * from animals into outfile ’/tmp/animals1.txt’

2 -> fields terminated by ’,’ enclosed by ’"’-> lines terminated by ’\r\n’;� �

6. 在終端機查看匯出的檔案。

�1 [root@kvm8 ~]# cat /tmp/animals1.txt

"2","cat","4"3 "3","chicken","2"

"1","dog","4"� �

16.2 匯出 Raw Data1. 使用 mysqldump 命令匯出 Raw Data 並導向到檔案 dump.txt。

�[root@kvm8 ~]# mysqldump -uroot -p123qwe dyw animals > dump.txt� �

2. 查看 mysqldump 命令匯出的 Raw Data 檔 dump.txt。

�1 [root@kvm8 ~]# cat dump.txt

-- MySQL dump 10.13 Distrib 5.5.38, for Linux (x86_64)3 --

-- Host: localhost Database: dyw5 -- ------------------------------------------------------

-- Server version 5.5.387

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;9 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;11 /*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;13 /*!40103 SET TIME_ZONE=’+00:00’ */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;15 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,

FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’

NO_AUTO_VALUE_ON_ZERO’ */;17 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

19 --


16.2. 匯出 RAW DATA CHAPTER 16. EXPORT 匯出

-- Table structure for table ‘animals‘21 --

23 DROP TABLE IF EXISTS ‘animals‘;/*!40101 SET @saved_cs_client = @@character_set_client */;

25 /*!40101 SET character_set_client = utf8 */;CREATE TABLE ‘animals‘ (

27 ‘id‘ int(10) unsigned NOT NULL DEFAULT ’0’,‘name‘ varchar(20) NOT NULL,

29 ‘foot‘ tinyint(3) unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

31 /*!40101 SET character_set_client = @saved_cs_client */;

33 ---- Dumping data for table ‘animals‘

35 --

37 LOCK TABLES ‘animals‘ WRITE;/*!40000 ALTER TABLE ‘animals‘ DISABLE KEYS */;

39 INSERT INTO ‘animals‘ VALUES (2,’cat’,4),(3,’chicken’,2),(1,’dog’,4);/*!40000 ALTER TABLE ‘animals‘ ENABLE KEYS */;

41 UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

43/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

45 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

47 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

49 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

51-- Dump completed on 2015-01-01 20:15:56� �

3. 使用 mysqldump 命令匯出 Raw Data，選項 --tab=/tmp 指定存檔目錄為/tmp。

�[root@kvm8 ~]# mysqldump -uroot -p123qwe --tab=/tmp dyw animals� �

4. 查看 mysqldump 命令匯出的 Raw Data 檔，/tmp/animals.txt 只存資料表內容，/tmp/animals.sql 為 sql 格式檔。

�1 [root@kvm8 ~]# cat /tmp/animals.txt


1 dog 45 [root@kvm8 ~]# cat /tmp/animals.sql

-- MySQL dump 10.13 Distrib 5.5.38, for Linux (x86_64)7 --


16.3. 匯出資料庫 CHAPTER 16. EXPORT 匯出

-- Host: localhost Database: dyw9 -- ------------------------------------------------------

-- Server version 5.5.3811

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;13 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;15 /*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;17 /*!40103 SET TIME_ZONE=’+00:00’ */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’’ */;19 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

21 ---- Table structure for table ‘animals‘

23 --

25 DROP TABLE IF EXISTS ‘animals‘;/*!40101 SET @saved_cs_client = @@character_set_client */;

27 /*!40101 SET character_set_client = utf8 */;CREATE TABLE ‘animals‘ (

29 ‘id‘ int(10) unsigned NOT NULL DEFAULT ’0’,‘name‘ varchar(20) NOT NULL,

31 ‘foot‘ tinyint(3) unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

33 /*!40101 SET character_set_client = @saved_cs_client */;

35 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

37 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

39 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

41 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

43 -- Dump completed on 2015-01-01 20:03:51� �

16.3 匯出資料庫

1. 使用 mysqldump 命令匯出某個資料庫，可以做為資料庫的備份。

�1 [root@kvm8 ~]# mysqldump -uroot -p123qwe dyw > dyw.sql� �

2. 已產生備份檔 dyw.sql。

�1 [root@kvm8 ~]# ll dyw.sql

-rw-r--r--. 1 root root 5002 Jan 1 20:23 dyw.sql


16.4. 匯出到其他主機 CHAPTER 16. EXPORT 匯出

� �

3. 使用 mysqldump 命令匯出所有資料庫，可以做為資料庫的備份，匯出時 mysql會提出警告，要使用者知道無法匯出事件資料表 mysql.event。

�[root@kvm8 ~]# mysqldump -uroot -p123qwe --all-databases > database.

sql2 -- Warning: Skipping the data of table mysql.event. Specify the --

events option explicitly.� �

4. 已產生備份檔 databse.sql。

�[root@kvm8 ~]# ll database.sql

2 -rw-r--r--. 1 root root 570359 May 3 12:17 database.sql� �

16.4 匯出到其他主機

1. 使用 mysql 命令將匯出的資料表 dump.txt 匯入 dyw 資料庫。

�[root@kvm8 ~]# mysql -uroot -p123qwe dyw < dump.txt� �

2. 使用 mysql 命令將匯出的資料庫 dyw.sql 匯入 dyw 資料庫。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe dyw < dyw.sql� �

3. 如果兩台 server 都有存取權，可從本機匯出資料庫，再使用管線處理送到遠端主機的資料庫。

�1 $ mysqldump -u root -p database_name \

| mysql -h other-host.com database_name� �

16.5. 實機操作練習題 CHAPTER 16. EXPORT 匯出


1. 以下列步驟完成存取 mysql 資料庫匯出備份：

(a) 遠端登入 kvm6 虛擬機。(b) 建立資料庫 dbei，並產生資料表 exim 包含下列欄位：

i. id intii. title varchar(40) not null

(c) 新增兩筆紀錄，其 title 分別為 eximx 及 eximy。(d) 使用語法 SELECT ... INTO OUTFILE 查詢資料表 exim，其結果匯到檔案/tmp/exim-0.txt，並指定以符號「<>」做為欄位分隔，以驚嘆號「!」將每個欄位的紀錄括起來，每行以 \r\n 換行。

(e) 使用 mysqldump 命令匯出資表料庫 dbei 中的 exim 資料表 Raw Data 並導向到檔案/tmp/dbeieximdump.txt。

(f) 使用 mysqldump 命令匯出資料庫 dbei，備份到/tmp/dbeidump.sql。

CHAPTER 17. IMPORT 匯入

Chapter 17

Import 匯入

17.1 LOAD DATA1. 先登入 mysql，使用 dyw 資料庫。


2 mysql> use dyw;mysql>� �

2. 查看目前資料表 animals 紀錄有三筆。

�1 mysql> select * from animals;

id name foot3 2 cat 4

3 chicken 25 1 dog 4

mysql>� �3. 將資料表 animals 匯出到檔案/tmp/dump.txt。

�mysql> select * from animals into outfile ’/tmp/dump.txt’;

2 mysql>� �4. 使用語法 LOAD DATA 讀取檔案資料，匯入資料到資料庫。

�mysql> load data local infile ’/tmp/dump.txt’ into table animals;

2 mysql>� �De-Yu Wang CSIE CYUT 131

17.2. LOAD DATA 指定格式 CHAPTER 17. IMPORT 匯入

5. 再查看資料表 animals 出現重複的紀錄。





8 1 dog 4mysql>� �

17.2 LOAD DATA 指定格式1. 依照檔案/tmp/animals1.txt 的格式匯入，分隔符號為逗號，字串以雙引號括起來，換行符號 ^M。

�1 [root@kvm8 ~]# cat -EvT /tmp/animals1.txt

"2","cat","4"^M$3 "3","chicken","2"^M$

"1","dog","4"^M$� �2. 依照檔案/tmp/animals1.txt 的格式匯入，分隔符號為逗號，字串以雙引號括起來，換行符號 ^M。

�mysql> load data local infile ’/tmp/animals1.txt’ into table animals

2 -> fields terminated by ’,’-> enclosed by ’"’

4 -> lines terminated by ’\r\n’;� �3. 再查看資料表 animals 又出現三筆重複的紀錄。





8 1 dog 42 cat 4

10 3 chicken 2


17.3. LOAD DATA 指定欄位 CHAPTER 17. IMPORT 匯入

1 dog 412 mysql>� �

17.3 LOAD DATA 指定欄位1. 上例匯入時沒有指定欄位，所以依序匯入，但也可以依使用者指定的欄位匯入，此例中故意將欄位 id 排在最後。

�mysql> load data local infile ’/tmp/dump.txt’

2 -> into table animals(name,foot,id);� �2. 再查看資料表 animals 出現最後三筆紀錄的 foot 欄位都是 0，原因為匯入檔案輸入的資料為動物名稱的字串，但 foot 欄位格式限定是 tinyint。





8 1 dog 42 cat 4


12 4 2 02 3 0

14 4 1 0mysql>� �

17.4 mysqlimport1. 使用 mysqlimport 指令，完成 LOAD DATA 語法的功能，將檔案/tmp/dump.txt 的資料匯入到資料庫 dyw，出現資料表 dyw.dump 不存在的警告訊息。--local 是從 client 讀取所有檔案。

�1 [root@kvm8 ~]# mysqlimport -uroot -p123qwe --local dyw /tmp/dump.txt

mysqlimport: Error: 1146, Table ’dyw.dump’ doesn’t exist, when usingtable: dump� �


17.4. MYSQLIMPORT CHAPTER 17. IMPORT 匯入

2. 匯入時會以匯入檔案名稱為匯入目標資料表，故必須更改匯入檔檔名與資料表名稱一致。

�[root@kvm8 ~]# cp /tmp/dump.txt animals.txt� �

3. 再使用 mysqlimport 指令，將檔案 animals.txt 的資料匯入到資料庫 dyw，成功匯入三筆。

�1 [root@kvm8 ~]# mysqlimport -uroot -p123qwe --local dyw animals.txt

dyw.animals: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0� �4. 再查詢資料表 animals 又多出三筆紀錄。

�[root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; select * from

animals;"2 +----+---------+------+

| id | name | foot |4 +----+---------+------+

| 2 | cat | 4 |6 | 3 | chicken | 2 |

| 1 | dog | 4 |8 | 2 | cat | 4 |

| 3 | chicken | 2 |10 | 1 | dog | 4 |

| 2 | cat | 4 |12 | 3 | chicken | 2 |

| 1 | dog | 4 |14 | 4 | 2 | 0 |

| 2 | 3 | 0 |16 | 4 | 1 | 0 |

| 2 | cat | 4 |18 | 3 | chicken | 2 |

| 1 | dog | 4 |20 +----+---------+------+� �

5. 使用 mysqlimport 指令，將檔案 animals.txt 的資料匯入到資料庫 dyw，並指定匯入欄位順序為 foot,name,id，出現警告訊息，但成功匯入三筆。

�[root@kvm8 ~]# mysqlimport -uroot -p123qwe --local --column=foot,name

,id dyw animals.txt2 Warning: Using unique option prefix column instead of columns is

deprecated and


17.5. 資料表還原 CHAPTER 17. IMPORT 匯入

will be removed in a future release. Please use the full name instead.

4 dyw.animals: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0� �6. 再查詢資料表 animals 又多出三筆紀錄，且 id 欄位存的是原 foot 的資料，而

foot 存的卻是原 id 資料。


animals;"2 "+----+---------+------+

| id | name | foot |4 +----+---------+------+

| 2 | cat | 4 |6 | 3 | chicken | 2 |

| 1 | dog | 4 |8 | 2 | cat | 4 |

| 3 | chicken | 2 |10 | 1 | dog | 4 |

| 2 | cat | 4 |12 | 3 | chicken | 2 |

| 1 | dog | 4 |14 | 4 | 2 | 0 |

| 2 | 3 | 0 |16 | 4 | 1 | 0 |

| 2 | cat | 4 |18 | 3 | chicken | 2 |

| 1 | dog | 4 |20 | 4 | cat | 2 |

| 2 | chicken | 3 |22 | 4 | dog | 1 |

+----+---------+------+� �

17.5 資料表還原

1. 先查詢目前資料表 animals 有三筆紀錄。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; select * from

animals;"+----+---------+------+

3 | id | name | foot |+----+---------+------+

5 | 2 | cat | 4 || 3 | chicken | 2 |

7 | 1 | dog | 4 |+----+---------+------+� �


17.5. 資料表還原 CHAPTER 17. IMPORT 匯入

2. 先做資料表 animals 的 raw data 匯出，也就是備份。

�[root@kvm8 ~]# mysqldump -uroot -p123qwe dyw animals > abc.sql� �

3. 將檔案 animals.txt 的資料匯入資料表 animals，成功匯入三筆紀錄。

�1 [root@kvm8 ~]# mysqlimport -uroot -p123qwe --local dyw animals.txt

dyw.animals: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0� �4. 再查詢資料表 animals 又多出三筆紀錄。


animals;"2 +----+---------+------+

| id | name | foot |4 +----+---------+------+

| 2 | cat | 4 |6 | 3 | chicken | 2 |

| 1 | dog | 4 |8 | 2 | cat | 4 |

| 3 | chicken | 2 |10 | 1 | dog | 4 |

+----+---------+------+� �5. abc.sql 為資料庫中資料表 animals 匯出的 raw data。將資料表 animals 還原成

abc.sql 時的狀態。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe dyw < abc.sql� �

6. 再查詢資料表 animals 已回復至備份時的三筆紀錄。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; select * from

animals;"+----+---------+------+

3 | id | name | foot |+----+---------+------+

5 | 2 | cat | 4 || 3 | chicken | 2 |

7 | 1 | dog | 4 |

17.6. 資料庫還原 CHAPTER 17. IMPORT 匯入

+----+---------+------+� �

17.6 資料庫還原

1. 先查詢目前資料庫 dyw 共有五個資料。

�[root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; show tables;"

2 +---------------+| Tables_in_dyw |

4 +---------------+| animals |

6 | clone_animals || dcount_tbl |

8 | dyw_tbl || person_tbl |

10 +---------------+� �2. 先做整個資料庫 dyw 的 raw data 匯出，也就是備份。

�[root@kvm8 ~]# mysqldump -uroot -p123qwe dyw > dyw.sql� �

3. 刪除資料庫 dyw 的資料表 animals。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; drop table animals

;"[root@kvm8 ~]#� �

4. 再查詢資料庫 dyw，資料表 animals 已不見了。

�[root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; show tables;"

2 +---------------+| Tables_in_dyw |

4 +---------------+| clone_animals |

6 | dcount_tbl || dyw_tbl |

8 | person_tbl |+---------------+� �


17.7. 實機操作練習題 CHAPTER 17. IMPORT 匯入

5. dyw.sql為資料庫 dyw匯出的 raw data。將資料庫 dyw還原成 dyw.sql時的狀態。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe dyw < dyw.sql� �

6. 先查詢目前資料庫 dyw 又含 animals 資料表。

�1 [root@kvm8 ~]# mysql -uroot -p123qwe -e "use dyw; show tables;"

+---------------+3 | Tables_in_dyw |

+---------------+5 | animals |

| clone_animals |7 | dcount_tbl |

| dyw_tbl |9 | person_tbl |

+---------------+� �


1. 以下列步驟完成 mysql 資料表匯入：

(a) 遠端登入 kvm6 虛擬機。(b) 以 root 身份登入 mysql。(c) 使用資料庫 dbei 中的資料表 exim，如果資料庫及資料表不存在，請自行產生，資料表 exim 包含下列欄位：

i. id intii. title varchar(40) not null

(d) 新增兩筆紀錄，其 title 分別為 eximx 及 eximy。(e) 使用 mysqldump 命令匯出資表料庫 dbei 中的 exim 資料表 Raw Data 並導向到檔案/tmp/dbeieximdump.txt。

(f) 使用 mysqldump 命令匯出資料庫 dbei，備份到/tmp/dbeidump.sql。

2. 如果上一章16實機練習已完成，就僅做以下要求：

(a) 先查看資料表 exim，看看目前的紀錄有幾筆？(b) 使用 mysqldump 命令匯出資料表 exim 的 Raw Data，選項 --tab=/tmp 指定存檔目錄為/tmp。

(c) 使用 LOAD DATA 讀取檔案/tmp/exim.txt 資料，匯入到資料表 exim。(d) 查看資料表 exim，看看是否所有紀錄皆重複兩筆？(e) 再使用 mysqldump 命令匯出目前資表料庫 dbei 中的 exim 資料表備份到檔案/tmp/dbeieximdump.sql。


(f) 再使用 mysqldump命令匯出目前資料庫 dbei，備份到/tmp/dbeidump1.sql。(g) 使用備份檔/tmp/dbeidump.sql 還原資料庫 dbei。(h) 再查看資料表 exim，看看是否已還原回原始紀錄？(i) 使用備份檔/tmp/dbeieximdump.sql 還原資料表 exim。


CHAPTER 18. 問題與解決

Chapter 18

問題與解決

18.1 自動補齊

1. mysql 5.5 版本登入後，下命令 use database 使用某資料庫時，例如以下 use dbx;出現訊息 Reading.....，此為自動補齊功能提示，如果不使用以加快執行速度，可以登入時使用 -A 選項關閉。

�mysql> use dbx;

2 Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A� �

2. 登入 mysql 後，使用 dbx 資料庫，執行 select * from tbl[TAB] 出現提示。

�1 [root@kvm6 ~]# mysql -uroot -p123qwe







statement.13

mysql> use dbx;15 Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A17

Database changed19 mysql> select * from tbl[TAB]

tbl321 tbl321.id tbly21 tbl321.author tbl321.submission_date tbly.author


18.1. 自動補齊 CHAPTER 18. 問題與解決

tbl321.fc2 tbl321.title tbly.count23 mysql> select * from tbl321;

+-----+----+-------+--------+-----------------+25 | fc2 | id | title | author | submission_date |

+-----+----+-------+--------+-----------------+27 | 0 | 10 | linux | abc123 | 2015-04-01 |

| 0 | 12 | shell | xyz222 | 2015-04-03 |29 | 0 | 14 | ruby | xyz111 | 2015-05-02 |

+-----+----+-------+--------+-----------------+31 3 rows in set (0.00 sec)

33 mysql> exitBye� �

3. 查詢 mysql 選項，-A, --no-auto-rehash 沒有自動補齊功能。

�[root@kvm6 ~]# mysql --help | grep ’\-A’

2 -A, --no-auto-rehash� �4. 以 -A 選項關閉自動補齊功能，登入 mysql 後，使用 dbx 資料庫，執行

select * from tbl[TAB] 無法出現提示。

�[root@kvm6 ~]# mysql -uroot -p123qwe -A

2 Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21

4 Server version: 5.5.49 MySQL Community Server (GPL) by Remi



10 owners.


14 mysql> use dbx;Database changed

16 mysql> select * from tbl[TAB]-> ;

18 ERROR 1146 (42S02): Table ’dbx.tbl’ doesn’t existmysql> exit

20 Bye� �


PHP+MySQL - dywang.csie.cyut.edu.twmysql.pdf · PHP+MySQL Department of Computer Science and...

Documents

Transcript of PHP+MySQL - dywang.csie.cyut.edu.twmysql.pdf · PHP+MySQL Department of Computer Science and...