第 4 章

1. 第4章 MySQL

2. 大綱 • MySQL 資料庫概述 • MySQL 基礎操作 • 基本 SQL 指令 • 進階 SQL 指令 • 其他操作介面 • 結論

3. MySQL 資料庫概述 • MySQL 是一個運作速度相當快、支援多執行緒（multithread）、與多使用者同時上線操作，且功能相當強大的關聯式資料庫管理系統（relational database management system, RDBMS），由Mysql AB公司開發，其官方網站在http://www.mysql.com/。 • 透過許多驅動程式的支援，MySQL 可以很容易與 C、C++、Java、Perl、PHP、JSP、ASP等語言做資料連結，也提供了多種不同平台的版本，使MySQL可以運行於多種平台上，例如：Solaris、Linux、FreeBSD、OS/2、Windows ...... 等等。

4. MySQL 資料庫概述 Cont. • MySQL 並不是一個 Open Source 的軟體計畫，因為其版權在某些情況下是需要付費的，例如將它與其他產品包裝販售。不過，大體上來說，個人、教育單位及非營利事業使用它是免費的，而 MySQL 所收取的授權費主要來協助 MySQL 研發所需，使MySQL 得以更加茁壯。 • 在MySQL 4.0 之後，MySQL也支援了Unicode(UTF-8)字元集，徹底解決了長久以來因為中文BIG5碼所造成的『許功蓋』等亂碼問題。同時亦支援子查詢的功能，搭配其極快的查詢效率與許多third-party周邊，足敷一般中型資料庫伺服器之所需，同時也是一極佳的資料庫入門磚。

5. MySQL 特性 • 使用C和C++語言撰寫，並使用了多種編譯器進行測試，原始碼可移植性極佳。 • 支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統與硬體平台 • 為多種程式語言語言提供了操作之 API。這些程式語言語言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。 • 具備多執行緒功能，充分利用CPU資源。 • 最佳化的的 SQL 查詢機制，有效地提高查詢速度。 • 提供 Unicode 多國語言支援。 • 提供 TCP/IP、ODBC 和 JDBC 等多種資料庫連接方式。 • 可以處理擁有上千萬筆記錄的大型資料庫。

6. MySQL 授權模式 • 目前，MySQL AB公司對MySQL提供了兩種授權模式，分別是： • 商業授權（The Commercial License）： • 自由軟體授權（The Open Source License）： • 在商業授權模式下，亦即商業出售的套件包含MySQL資料庫伺服器，或所提供的商業服務中使用到MySQL資料庫伺服器，均屬商業授權範圍。必須向MySQL AB公司購買授權方能運用。 • 在自由軟體授權下，亦即學術、研究或個人非營利用途上，可自由使用。但如針對MySQL原始碼有任何修改，不可以此修改販售營利，而需將改進成果回饋給開放原始碼社群，關於此部分更詳盡的內容，請參閱GPL合約。

7. 安裝/設定注意事項 • 在MySQL資料庫內碼的設定上，由於4.0版後，MySQL開始支援UTF-8，徹底解決中文亂碼的問題，在此選擇第二項『Best support for multilingualism』、或第三項並選擇UTF-8即可。

8. 基礎操作：進入 MySQL • 最簡單進入MySQL的方法，便是由『開始』->『MySQL』->『MySQL Server 4.1』->『MySQL Command Line Client』，點選『MySQL Command Line Client』，便可進入密碼確認畫面，鍵入密碼（本書設定root密碼為123456），按下Enter鍵即以MySQL Client連線MySQL資料庫。

9. 基礎操作：密碼變更 • 在一開始時初始化設定時，我們將root密碼設為123456，如果日後要改變指令也很容易，只要在DOS下鍵入以下指令即可： • mysqladmin -h localhost -u root --password password 新密碼 • 在出現Enter password:時，鍵入原密碼。 • 例如，原本root密碼是『123456』，要改成『abcdef』，則鍵入： • mysqladmin -h localhost -u root --password password abcdef • 在出現Enter password:時，鍵入『123456』，密碼即變更成功。

10. 基礎操作：帳號與權限 • 例：新增來自localhost的帳號user，密碼為123456，並給予全部權限（等同於root）： • 指令：GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY '123456'; • 例：新增來自localhost的帳號dbuser，密碼為123456，僅給予管理db資料庫的所有權限： • 指令：GRANT ALL ON db.* TO dbuser@localhost IDENTIFIED BY '123456'; • 例：變更來自localhost的帳號dbuser，密碼為123456，取消管理db資料庫的所有權限： • 指令：REVOKE ALL ON db.* FROM dbuser@localhost IDENTIFIED BY '123456';

11. 基礎 SQL：建立與刪除資料庫 • 建立一個資料庫，名稱為db • 請以root登入資料庫，並鍵入以下指令（注意：MySQL指令結尾均需有分號）： • mysql>create database db; • mysql>show databases; 則結果應為： +----------+ | Database | +----------+ | db | | mysql | | test | +----------+

12. 基礎 SQL：建立與刪除資料庫 • 資料庫的名字最好只使用ASCII中的ANU字符：即26個大寫英文字母、26個小寫英文字母、10個數目字和底線符號 _ 。不要超過 64 個字元。並請絕對不要使用中文。 • 資料庫的名字不可以由數目字開頭。 • 如果 MySQL Server的作業系統規定檔案名的大小寫字母視為不同，則資料庫的名稱大小寫亦會被視為不同。如果MySQL安裝在Windwos上，則db、Db與DB都是指同一個資料庫。但如安裝在Linux或UNIX系統上，則db和DB是兩個不同的資料庫名稱。

13. 基礎 SQL：選用資料庫 • 在使用MySQL內，同一時間只能選用一個資料庫進行操作。假設db是資料庫的名字，則欲操作db資料庫，則執行以下指令： • mysal>use db; • MySQL應回應： • Database changed • 此時，即使用此一資料庫，要注意的是，如登入MySQL時並未選用任何資料庫，MySQL並不會主動幫使用者選擇任一資料庫。

14. 基礎 SQL：選用資料庫 • 如果想要知道目前正在使用的資料庫，則執行以下指令： • mysql>select database(); • MySQL 會做如下回應： +------------+ | database() | +------------+ | dbname | +------------+

15. 基礎 SQL：得知目前狀態 • mysql>select now( ); • 得知現在的日期和時間。 • mysql>select user( ); • 得知目前登入MySQL的使用者。 • mysql>select version( ); • 得知目前的 MySQL 版本。 • 在MySQL中的指令和函式，都不分大小寫

16. 基礎 SQL：建立表格 • 建立表格的基本語法是： • create table 表格名稱 (field1, field2, ..., fieldN ); • 在選用表格名稱時，其定義規則與限制和資料庫名稱選用是一樣的。 • 表格的名字最好只使用 ASCII 中的 ANU 字符：26 個大寫英文字母、 26 個小寫英文字母、10 個數目字和底線符號 _ 。不可使用中文，亦不要超過 64 個字元。 • 表格名不能以數目字開頭。 • 通常應假設表格名是大小寫有分別的；例如 abc 和 Abc 是兩個不同的表格名，為避免混淆，以下所有表格名稱與欄位名稱均使用小寫。

17. 基礎 SQL：建立表格 Cont. • 例如：mysql>create table abc (number int, name char(10)); • 如要檢視表格架構，則執行 • mysql>desc abc; • 則得到以下結果： +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | number | int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+-------+

18. 基礎 SQL：刪除表格 • 刪除表格的指令是： • mysql>drop table 表格名稱; • 此一指令如同drop database，是一個無法反悔且無法救回的指令，且 MySQL不會再度確認，而是在執行此一指令後立即刪除。因此要小心此一指令。

19. 基礎 SQL：建立表格練習 +-------+------------------+-----+----+-------+--------------+ | Field | Type |Null |Key |Default|Extra | +-------+------------------+-----+----+-------+--------------+ | id | int(10) unsigned | |PRI |NULL |auto_increment| | name | char(20) | | | | | | phone | char(32) |YES | |NULL | | +-------+------------------+----------+-------+--------------+ 試建立以下表格

20. 基礎 SQL：資料輸入 • insert into 表格名稱 values (欄位1之值 , 欄位2之值, ... , 欄位N之值); • 其中「表格名稱」代表一個以存在資料庫中的表格，「欄位n」代表第 n 個欄位的值，而 1 <= n <= N。要注意的是，敘述中的 , 數量必須與欄位數量-1吻合。

21. 基礎 SQL：資料輸入 Cont. • 而欄位的值 (value) 必須符合當初宣告那個欄位的資料型態。如果是數值類型態（例如整數、無號整數、浮點數），則直接寫數目字即可。如果是文字類型態(例如固定長度字串或不定長度字串)，則必須以一對單引號或雙引號將其框選，方能正確辨識（考量到日後撰寫PHP需要，以下SQL指令中文字框選均以單引號行之）。 • 如果是日期，也需以一對單引號框起，而日期的標準寫法是以 2006-02-25 代表西元 2006 年二月二十五日。在MySQL中並不認得「民國」年數，但可在日後撰寫程式時透過運算得到。 • 而如果一個欄位是空的，不管它的型態是什麼，都可以NULL輸入。

22. 基礎 SQL：建立 user 資料表 create table user ( serial int unsigned auto_increment primary key, name varchar(10) not null, gender enum('F', 'M') not null, birthday date, phone char(20) );

23. 基礎 SQL：建立 user 資料表 • id是一個無號整數，且為主要鍵值、並具自動增加（auto_increment）功能，通常每個資料表都會有一個可供找出一獨一無二的資料列之序號，避免資料相同時無法判斷之窘境。 • enum 是宣告一個使用者自定的列舉型態。例如 enum('F', 'M') 意指此一欄位的值，只可以是 'F' 或 'M' 兩種字串。再例如enum('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')，意指這個欄位的值，只准許是 'Mon' ... 等七種字串。 • 為什麼phone欄位要宣告 20 個字元？似乎char(13) 就足敷需求。因為考慮到未來或許需包含分機號碼在內。例如 (02)2783-5611-75815等等，所以還是宣告長一點比較安全。此亦點出在資料庫設計上，資料長度要有預留空間。應在系統容許的範圍內，盡量考慮到未來的擴充性。

24. 基礎 SQL：輸入資料 • 在 user 表格中插入資料 • insert into user values (NULL, '黃蓉', 'F', '1975-10-13', '0922123456');

25. 基礎 SQL：輸入資料 • 檢視user的內容：執行以下指令：  • mysql>select * from user; +--------+------+--------+------------+------------+ | serial | name | gender | birthday | phone | +--------+------+--------+------------+------------+ | 1 | 黃蓉 | F | 1975-10-13 | 0922123456 | +--------+------+--------+------------+------------+ 1 row in set (0.00 sec)

26. 基礎 SQL：輸入資料 • 再增加幾筆資料： insert into user values (NULL, '楊過', 'M', '1964-08-12', '0921456789'); insert into user values (NULL, '郭靖', 'M', '1955-01-23', '0923000123'); insert into user values (NULL, '小昭', 'F', '1988-12-25', '0924987654'); • 新增完後檢視檢果，請注意serial序號欄位的變化。 +--------+------+--------+------------+------------+ | serial | name | gender | birthday | phone | +--------+------+--------+------------+------------+ | 1 | 黃蓉 | F | 1975-10-13 | 0922123456 | | 2 | 楊過 | M | 1964-08-12 | 0921456789 | | 3 | 郭靖 | M | 1955-01-23 | 0923000123 | | 4 | 小昭 | F | 1988-12-25 | 0924987654 | +--------+------+--------+------------+------------+

27. 基礎 SQL：查閱表格 select * from user; 其結果為： +--------+------+--------+------------+------------+ | serial | name | gender | birthday | phone | +--------+------+--------+------------+------------+ | 1 | 黃蓉 | F | 1975-10-13 | 0922123456 | | 2 | 楊過 | M | 1964-08-12 | 0921456789 | | 3 | 郭靖 | M | 1955-01-23 | 0923000123 | | 4 | 小昭 | F | 1988-12-25 | 0924987654 | +--------+------+--------+------------+------------+

28. 基礎 SQL：查閱表格 Cont. 僅查詢使用者姓名，即執行以下指令： mysql>select name from user; 其結果為： +------+ | name | +------+ | 黃蓉 | | 楊過 | | 郭靖 | | 小昭 | +------+

29. 基礎 SQL：查閱表格 Cont. 同時查詢使用者姓名與電話號碼，即執行以下指令： mysql>select name,phone from user; 其結果為： +------+------------+ | name | phone | +------+------------+ | 黃蓉 | 0922123456 | | 楊過 | 0921456789 | | 郭靖 | 0923000123 | | 小昭 | 0924987654 | +------+------------+

30. 基礎 SQL：刪除資料 一次刪除某一表格內的全部資料，指令如下： mysql>delete from 表格名稱; 以user表格為例，要刪除user表格內所有資料，指令為： mysql>delete from user;

31. 基礎 SQL：刪除資料 Cont. 只刪除特定的某筆資料，便需要指定條件，指令如下： delete from 表格名稱 where 欄位名稱 = V; 這樣，MySQL 就會把「欄位」值是 V 的資料都整筆刪除掉。以下是一個完整的資料插入與刪除動作範例，先插入一筆範例資料： insert into user values (NULL, '方證', 'M', '1930-12-25', '0920965347'); 插入完成後，如要刪除此筆資料，則執行以下指令： delete from user where name = '方證'; 或 delete from user where phone = '0920965347'; 兩者均可

32. 基礎 SQL：變更資料 • 所謂修改資料，是指某筆資料已經存在，只是要更改那筆資料某些欄位的值。其基本語法是： • update 表格 set 欄位1 = V1 where 欄位2 = V2

33. 基礎 SQL：變更資料 Cont. 先插入一筆資料： insert into user values (NULL, '令狐沖', 'M', '1975-05-05', '0929098987'); 變更其電話： update user set phone = '0938765642' where name= '令狐沖'; 當然，透過名字來辨識，可能會發生同名同姓的問題，因此，較佳方式是由序號來辨識： update user set phone = '0938763332' where serial= 10;

34. 基礎 SQL：變更資料 Cont. • 亦改兩個欄位，以 , 區分欲修改之欄位即可，例如： • update user set phone = '0938763332' , birthday = '1998-10-10' where serial= 10;

35. 基礎 SQL：回溯指令 • 在 MySQL 的MySQL Command Line Client裡，按向上的箭頭可以回溯前一列字串。依此類推可以一直向前回溯，也可以按向下的箭頭返回。 • 如果回溯的字串本身是一個完整的指令 (有頭有尾有分號)，那麼，按 Enter 就能執行。您也可以用左、右箭頭，或是 Backspace 或 Delete 按鍵來修改那一列的字串。

36. 基礎 SQL：表格結構修改 • 要在user資料表中新增一個郵遞區號zipcode的欄位，作法如下： • mysql>alter table user add zipcode int unsigned; • 修改 user 資料表名稱為 usr： • mysql>alter table user rename usr;

37. 基礎 SQL：表格結構修改 Cont. • 修改user 資料表 phone 欄位型態為 VARCHAR(30)： • mysql>alter table user modify phone varchar(30); • 修改 user 資料表 phone 欄位名稱為 mobile 並且型態修改為 VARCHAR(20)： • mysql>alter table user change phone mobile varchar(20);

38. 基礎 SQL：表格結構修改 Cont. • 刪除 user 資料表的 name 欄位 • mysql>alter table user drop name; • 增加 user 資料表 address 欄位 • mysql>alter table user add address char(100);

39. 基礎 SQL：匯入資料 • 在使用腳本程式時，需切換到命令提示字元下執行，指令如下： • mysql資料庫名稱 -h 機器位置 -u 使用者位置 -p <腳本程式所在位置

40. 基礎 SQL：匯入資料 Cont. • 在 C 槽根目錄下，建立 create_zip.sql，內容如下： drop table if exists zip; create table zip ( id smallint unsigned not null auto_increment primary key, zip int unsigned, zone char(10), area char(10) ); • 匯入指令如下：在 db 資料庫中建立 zip 表格 • mysql db -h localhost -u root -p < c:\create_zip.sql

41. 基礎 SQL：匯入資料 Cont. • 匯入全臺郵遞區號資料 • 將insert_zip.sql放在 C 槽根目錄下 • mysql db -h localhost -u root -p < c:\insert_zip.sql

42. 基礎 SQL：匯出資料 • 既然MySQL能夠批次匯入資料，自然也能夠批次匯出資料，此功能尤其在資料備份時最為有用，如同批次匯入，批次匯出功能亦需切換到命令提示字元下執行，指令如下： • mysqldump資料庫名稱 -h 機器位置 -u 使用者位置 -p > 匯出目標檔

43. 基礎 SQL：匯出資料 Cont. • 例如，我們要把整個db資料庫（連同資料庫內的資料），完全匯出到c:\db_bak.sql檔案中，則指令如下： • mysqldump db -h localhost -u root -p > c:\db_bak.sql

44. 基礎 SQL：匯出資料 Cont. • 如果只要匯出資料表的結構（不要內含資料），則指令如下： • mysqldump db –d -h localhost -u root -p > c:\db_bak.sql • 二進位欄位（如LONGBLOB，提供在資料庫中儲存大量二進位型態資料的功能），則這種型態的資料，無法被匯出到備份檔中

45. 進階 SQL：進階查詢 • 查詢所有桃園縣內的郵遞區號、表格編號、地區 • mysql>select * from zip where zone=‘桃園縣’; • 查詢所有桃園縣內的郵遞區號、地區 • mysql>select zip, area from zip where zone='桃園縣';

46. 進階 SQL：進階查詢 Cont. • 如果要查詢地區名叫做「東勢」的郵遞區號和它所在的縣市，指令為： • mysql>select zip, zone, area from zip where area=‘東勢’; • 便可列出所有非臺北市的郵遞區號。 • select zip from zip where zone != '臺北市';

47. 進階 SQL：進階查詢 Cont. • 進行有條件查詢 • select 輸出欄位 from 表格 where 子句; • 數值類型的比較 • mysql>select * from ZIP where zip = 100; • mysql>select * from ZIP where zip <= 100; • mysql>select * from ZIP where zip > 980;

48. 進階 SQL：進階查詢 Cont. • 字元（字串）類型的比較 mysql>select * from ZIP where zone like '嘉義%'; mysql>select * from ZIP where area like '中%'; mysql>select * from ZIP where area like '%和'; mysql>select * from ZIP where area like '%和%'; mysql>select * from ZIP where area like '臺%市'; <-- 錯誤指令 mysql>select * from ZIP where area not like '臺北%'; mysql>select zip,zone,area from ZIP where zone like '臺中%' and area = '東勢'; mysql>select zip,zone,area from ZIP where zip >= 350 and zip <= 359; mysql>select * from ZIP where zone like '%海%' or area like '%海%';

49. 進階 SQL：接合 Join • 重新建立 user 表格 drop table user; create table user ( serial int unsigned auto_increment primary key, name varchar(10) not null, gender enum('F', 'M') not null, birthday date, phone char(20) ); alter table user add zipcode int unsigned;

50. 進階 SQL：接合 Join Cont. • 並新增兩筆資料 insert into user values (NULL, '喬峰', 'M', '1966-08-11', '0929763995', '726'); insert into user values (NULL, '慕容', 'M', '1975-04-04', '0929843956', '542');