第 3 章 MySQL 教學範本

第 3 章

MySQL 教學範本

本章宗旨

 簡介伺服器連線與離線指令

 簡介 MySQL 指令編輯方式

 簡介資料庫建立、資料庫管理與資料處理常用 指令

 簡介 MySQL 進階應用指令型式

大 綱

3-1 伺服器連線與離線 3-2 指令編輯方式

3-3 MySQL 常用指令 3-4 MySQL 進階應用

3-1 伺服器連線與離線

 連線指令常用型式：

 指令一： mysql –h Host – u User -pPassword

 指令二： mysql –h Host – u User –p Enter password: ********

 指令三： mysql

連線成功畫面

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

Your MySQL connection id is 1 to server version: 4.1.9-nt.

Type ‘help’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

連線失敗畫面

ERROR 1045 (28000) Access denied for ‘user’@’localhost’

(using password No) mysql>

3-1-2 離線

 離線指令型式：

 指令一： QUIT

 指令二： \q

 離線畫面：

mysql>QUIT Bye

3-2 指令編輯方式

 一行指令一道命令：指令編輯完成後，直接 按【 Enter 】鍵執行命令

 數行指令一道命令：指令佔滿一行時，按

【 Enter 】鍵換行（可不按），繼續編輯後 續指令

3-2 指令編輯方式

 指令列若為標準 SQL 指令，必須加上 ” ;” 結 束指令

 不管指令列跨越幾行，一道命令只能包含一個

” ;”

 指令列若為 MySQL 內部指令，則不用加 上” ;” ，例如 Use, Quit 等

3-2 指令編輯方式

 範例 mysql>Select User(), Current_date;

(a)

mysql>Select ->User()

->,

->Current_date;

mysql>Select ->User()

->\c

(c)

3-3 MySQL 常用指令

3-3-1 資料庫處理 3-2-2 資料表處理 3-3-3 索引表處理 3-3-4 載入資料 3-3-5 瀏覽資料 3-3-6 刪除資料 3-6-7 更新資料

3-3-1 資料庫處理 1. 建立資料庫

 語法： Create Database 資料庫名稱 ;

 範例： Create Database Temp; 建立一個名稱為

【 Temp 】之資料庫。

 執行結果：

2. 顯示資料庫明細

 語法： Show Databases;

 執行結果：

3. 選用資料庫

 語法： Use 資料庫名稱

 範例： Use Temp;

 螢幕會提示： Database Changed 之信息

4. 刪除資料庫

 語法： Drop Database 資料庫名稱 ;

 範例： Drop Database Temp;

3-2-2 資料表處理

1. 建立資料表

 語法： Create Table 資料表名稱（欄位定義） ;

 範例： CREATE TABLE pet (name

VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

1. 建立資料表

 欄位定義之格式為

 欄位名稱

 資料型態

 （寬度）

 [Null | NOT Null]

 [Default 值 ]

 欄位間須以” ,” 間隔

2. 顯示資料表

語法： Show Tables;

執行結果：

3. 顯示資料表結構

 語法： Describe 資料表名稱 ;

 範例： Describe pet;

 執行結果：

3-3-3 索引表處理 1. 建立索引表

 語法： Create Index 索引表名稱 On 資料表名 稱 （欄位名稱） ;

 範例： CREATE Index pet1 On pet (name, owner);

2. 顯示索引表

 語法： Show Index From 資料表名稱 ;

 範例： Show Index From pet;

 結果：

3-3-4 載入資料

1. 批次載入：限定固定格式文字檔

 載入指令： Load Data Local Infile “ 外部檔案 來源” Into Table 資料表名稱 Fields

Terminated By “,” ;

 範例： Load Data Local Infile

“C:\\mysql\\data\\test\\pet.txt” Into Table pet Fields Terminated By “,”;

1. 批次載入說明

 文字檔內容以列為單位，固定格式

 欄位間須以逗號或空格間隔開來，若以空格間隔，需記

住空格個數

 編輯檔案時，若每一列是以’ \r\n’ 符號換行的話，則輸 入命令需加上 [Line Terminated By ‘\r\n’] 參數

 一筆紀錄若有部分欄位為空白資料時，對應欄位需加上

[’\N’] 記號， MySQL 會將此記號解譯為 Null

1. 批次載入說明

 文字檔之欄數需和資料表欄數相同，否則會產生錯 誤

 若資料是從主控端上傳伺服端，則須加上 [Local] 參 數

 檔案可置於其他路徑，命令列之檔案來源需加上檔 案路徑

2. 逐筆輸入

 指令一： Insert Into 資料表名 Values ( 欄位 資料串 ) ;

 範例： Insert Into pet Values (‘Puffball’,

’Diane’, ’hamster’, ’f’, ’1990-03-30’, Null);

 指令二： Insert Into 資料表名 ( 欄位串列 ) Values ( 欄位資料串 ) ;

2. 逐筆輸入說明

 若所有欄位都需要輸入資料時，使用指令一，

否則使用指令二

 指令二需要指定欄名，欄名間以逗號間隔

 除了數值資料外，欄位資料須以雙引號或單引 號夾住

 欄位資料若為空值，請填入 Null

3-3-5 瀏覽資料

 基本的指令格式

SELECT 欄位串列 FROM 資料來源 WHERE 資料範圍 ORDER BY 欄位串列 GROUP BY 欄位串列 ;

3-3-5 瀏覽資料說明

 SELECT ： 用來指定輸出欄位名稱，可用 * 表示全部 欄位，欄名間使用逗號間隔

 FROM ：用來指定資料來源，可同時查看兩個以上資 料表

 WHERE ：用來設定輸出資料範圍，常見格式為指定 欄名，再搭配關係運算子（諸如

＝、 > 、 >= 、 < 、 <= 、 <>( 代表不等於 ) ），然後 輸入待比對之欄位條件值。若有需要，還可以設定第 二個比較欄位，但兩個欄位間要用邏輯運算子結合，

3-3-5 瀏覽資料說明

 ORDER BY

 用來指定資料要以升冪（修飾辭為 ASC ）或 降冪（修飾辭為 DESC ）順序輸出

 需要排序之欄位，請在 BY 後面定義，資料排

序方式內定為升冪形式， ASC 修飾辭可省略。

若為降冪排列， DESC 修飾辭不能省略

 同時指定兩個以上欄位排序時，欄位間需以逗

號間隔，排序順序可不相同

3-3-5 瀏覽資料說明 ( 續 )

 GROUP BY

 用來指定群組欄位，若資料表內相同鍵值紀錄

有多筆情況，而輸出資料需予以加總、平均、

計算筆數等時，需指定群組欄位

 群組欄位可指定一個以上，欄位間以逗號間隔

1. 查看全部資料

 指令： SELECT * FROM 單一資料表 ;

 範例： SELECT * FROM pet;

 執行結果：

2. 查看部份資料（ 1 ）

 指令： SELECT * FROM 單一資料表 WHERE 資料 範圍 ;

 範例： SELECT * FROM pet WHERE name =

’Bowser’;

 執行結果：

3. 查看部份資料

範例 (2) ：

Select * from pet Where birth >= "1998-1-1";

範例 (3) ：

Select * from pet Where species ="dog" and sex="f";

範例 (4) ：

Select * from pet Where species = "snake" Or species = "bird";

3. 查看部份資料

範例 (5) ：

Select * from pet Where (species="cat" And sex="m") Or (species="dog" And sex="f");

7. 查看特定欄位資料

範例 (1) ：

Select name, birth From pet;

範例 (2) ：

Select owner From pet;

 範例 (3) ：

Select DISTINCT owner From pet;

範例 (4) ：

Select name, species, birth From pet Where species = "dog" Or species = "cat";

10. 資料排序

 升冪：

Select name, species, birth From pet Order By species,birth Desc;

 降冪 ：

Select name, birth From pet Order By birth desc;

 混合排序 ：

Select name, species, birth From pet Order By

13. 日期欄位計算

 範例 (1) ：

Select name, birth, curdate(),(year(curdate())- year(birth))-(right(curdate(),5) <right(birth,5)) as age From pet;

計算結果另存一欄，可使用 AS 修飾辭指定欄名

，本例欄名為” age” 。指令中之 curdate(), year(), right() 為 MySQL 內建函數，代表當日 系統日期

13. 日期欄位計算

 範例 (2) ：

Select name, birth, curdate(),(year(curdate())- year(birth))-(right(curdate(),5) <right(birth,5)) as age From pet Order By name;

 範例 (3) ：

Select name, birth, curdate(), (year(curdate())- year(birth))-(right(curdate(),5) <right(birth,5))

13. 日期欄位計算

 範例 (4) ：

Select name, birth, death, (year(death)-

year(birth))-(right(death,5) <right(birth,5)) as age From pet where death Is Not NULL

Order By age;

 範例 (5) ：

Select name, birth, month(birth) From pet;

month() 為 MySQL 內建函數，請注意欄名

13. 日期欄位計算

 範例 (6) ：

Select name, birth From pet Where month(birth) = 5;

此例以函數取值並據以當過濾條件

19 欄位資料與樣板值比較

 範例 (1 )

Select * From pet where name LIKE "b%“ ;

 LIKE 為條件值比較運算子，字母 b% 表示所 有 b 開頭之資料均符合

 範例 (2) ：

Select * From pet where name LIKE "%fy“ ;

 %fy 表示所有字串尾有 fy 字母之資料均符合

19 欄位資料與樣板值比較

 範例 (3)

Select * From pet where name LIKE "%w%“ ;

 %w% 表示所有字串中有 w 字母之資料均符合

 範例 (4) ：

Select * From pet where name LIKE "_____“ ;

 表示所有字串中字母個數等於指定空格數之資

料均符合

23. 紀錄筆數計算

 範例 (1)

Select Count(*) From pet ;

 Count() 為計算紀錄數之函數， * 為統計對象

 範例 (2) ：

Select owner, Count(*) From pet Group By owner ;

 Count() 函數搭配其他欄位使用時，必須加上 GROUP BY 修飾辭來使用

23. 紀錄筆數計算

 範例 (3)

Select owner, Count(*) AS “ 筆數” From pet Group By owner ;

 以 AS 指定欄位別名，可用中文命名，但需以

引號夾住

 範例 (4) ：

Select species, sex, Count(*) From pet Group

23. 紀錄筆數計算

 範例 (5)

Select species, sex, Count(*) From pet Where sex Is Not Null Group By species, sex ;

 搭配過濾條件來統計筆數

3-3-6 刪除資料

 基本指令型式：

DELETE

FROM 資料來源

WHERE 資料範圍 ;

3-3-6 刪除資料

 刪除資料不需指定欄位串列，因為刪除資料時

，是將整筆紀錄刪除，而不能刪除部份欄位

 刪除資料之時機通常是有時效性者

 刪除資料和刪除資料表是不同性質的，刪除資 料時，資料表結構還留存在資料庫內，但刪除 資料表卻是將整個資料表從資料庫移除

3-3-6 刪除資料

 範例

Delete From pet Where birth <’1993-01-01’ ; Delete From pet Where owner=’Gwen’ And

species=’cat’ ; Delete From pet;

3-3-6 刪除資料 ( 續 )

 透過第二個資料表來挑選待刪除資料

DELETE t1 FROM t1, t2 WHERE t1.key_field = t2.key_filed ;

 一道刪除指令同時刪除兩個表的資料

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id1=t3.id1;

3-6-7 更新資料

 基本指令型式：

UPDATE 資料表名稱

SET 欄位名稱 ＝ 更新資料 , [ 後續欄位 ] WHERE 資料範圍 ;

3-6-7 更新資料

 更新資料之時機 :

 原輸入之資料錯誤，事後發現錯誤

 時效過期

 異動檔資料轉入主控檔（例如會計之過帳）

3-6-7 更新資料

 將全部資料之死亡日期移除

 Update pet SET death=NULL;

 將名字叫” Bowser” 之死亡日期註記

 Update pet SET death=’1995-07-29’ Where name =’ Bowser’;

 將名字為 Slim 之出生日期及性別更改

 Update pet SET birth=’1995-04-29’ , sex=’m’

3-4 MySQL 進階應用

3-4-1 連結兩個資料表

 同時輸出兩個資料表之處理指令：

SELECT 欄位串列（可以間隔使用第一和第二資料 表之欄位）

FROM 第一資料表，第二資料表

WHERE 第一資料表 . 鍵值欄名 ＝ 第二資料表 . 鍵 值欄名 ;

 鍵值欄名通常是兩個表欄名相同者，鍵值欄名可指

同時輸出兩個資料表

 範例：

Select pet.name (Year(date)-year(birth))- (Right(date,5)<Right(birth,5)) AS ' 年齡 ',

remark From pet, event Where pet.name = event.name And type= " 生產 "

3-4-2 同一資料表呼叫兩次

 範例：

Select p1.name, p1.sex, p2.name, p2.sex,

p1.species From pet AS p1, pet As p2 Where p1.species = p2.species And p1.sex = 'f' And p2.sex = 'm';

3-4-3 線上資料分析

 挑選商品中最大值

Select Max(article) AS article From shop;

 以子查詢挑選單價最高之商品

Select article,dealer,price From shop Where price=(Select Max(price) From shop);

 搭配【 LIMIT 】修飾辭來限定輸出紀錄筆數

Select article,dealer,price From shop Order By

3-4-3 線上資料分析

 挑選各類商品中單價最高者

Select article, Max(price) AS price From shop Group By article;

 挑選各類商品中單價最高並輸出商品資訊

Select article, dealer, price From shop s1 Where price=(Select Max(s2.price) From shop s2

Where s1.article=s2.article);

3-4-3 線上資料分析

 與 Group By 搭配之常用分析函數

 AVG() ：求平均數

 MIN() ：挑最小值

 MAX() ：挑最大值

 SUM() ：求總數

 STD() ：樣本標準差，分母為紀錄數 -1

 STDDEV() ：標準差，分母為紀錄數，與

3-4-4 子查詢

 依第 2 表之結果來過濾第 1 表

Select * From t1 Where column1 = (Select column1 From t2);

 將挑選第 2 表資料新增至第 1 表

Insert Into t1 ( 欄位串列 ) Select ( 欄位串列 ) From t2 Where (t2 之過濾條件 );

 兩個資料表之欄位串列之個數必須相同，對應

型態也須相同

3-4-4 子查詢

 依第 2 表挑出之資料將第 1 表對應資料刪除

Delete From t1 Where column1 = (Select column1 From t2);

 以 IN 運算子比對資料

Select column1,column2,column3 From t1

Where (column1,column2,column3) IN (Select column1,column2,column3 From t2);