第3章MySQL教学范本.ppt

1、主從式資料庫系統 - CH3,1,第3章 MySQL教學範本,主從式資料庫系統 - CH3,2,本章宗旨,簡介伺服器連線與離線指令 簡介MySQL指令編輯方式 簡介資料庫建立、資料庫管理與資料處理常用指令 簡介MySQL進階應用指令型式,主從式資料庫系統 - CH3,3,大 綱,3-1 伺服器連線與離線 3-2 指令編輯方式 3-3 MySQL常用指令 3-4 MySQL進階應用,主從式資料庫系統 - CH3,4,3-1 伺服器連線與離線,連線指令常用型式： 指令一：mysql h Host u User -pPassword 指令二：mysql h Host u User p Enter p

2、assword: * 指令三：mysql,主從式資料庫系統 - CH3,5,連線成功畫面,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,主從式資料庫系統 - CH3,6,連線失敗畫面,ERROR 1045 (28000) Access denied for userlocalhost (using

3、 password No) mysql,主從式資料庫系統 - CH3,7,3-1-2 離線,離線指令型式： 指令一： QUIT 指令二： q 離線畫面：,mysqlQUIT Bye,主從式資料庫系統 - CH3,8,3-2 指令編輯方式,一行指令一道命令：指令編輯完成後，直接按【Enter】鍵執行命令 數行指令一道命令：指令佔滿一行時，按【Enter】鍵換行（可不按），繼續編輯後續指令,主從式資料庫系統 - CH3,9,3-2 指令編輯方式(續),指令列若為標準SQL指令，必須加上 ”;”結束指令 不管指令列跨越幾行，一道命令只能包含一個 ”;” 指令列若為MySQL內部指令，則不用加上”;”

4、，例如Use, Quit等,主從式資料庫系統 - CH3,10,3-2 指令編輯方式(續),範例,主從式資料庫系統 - CH3,11,3-3 MySQL常用指令,3-3-1資料庫處理 3-2-2 資料表處理 3-3-3 索引表處理 3-3-4 載入資料 3-3-5 瀏覽資料 3-3-6 刪除資料 3-6-7 更新資料,主從式資料庫系統 - CH3,12,3-3-1資料庫處理 1. 建立資料庫,語法：Create Database資料庫名稱 ; 範例：Create Database Temp; 建立一個名稱為【Temp】之資料庫。 執行結果：,主從式資料庫系統 - CH3,13,2. 顯示資料庫

5、明細,語法：Show Databases; 執行結果：,主從式資料庫系統 - CH3,14,3. 選用資料庫,語法：Use 資料庫名稱 範例：Use Temp; 螢幕會提示：Database Changed之信息,主從式資料庫系統 - CH3,15,4. 刪除資料庫,語法： Drop Database資料庫名稱; 範例： Drop Database Temp;,主從式資料庫系統 - CH3,16,3-2-2 資料表處理,1. 建立資料表 語法：Create Table資料表名稱（欄位定義）; 範例：CREATE TABLE pet (name VARCHAR(20), owner VARCHA

6、R(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);,主從式資料庫系統 - CH3,17,1. 建立資料表,欄位定義之格式為 欄位名稱 資料型態 （寬度） Null | NOT Null Default 值 欄位間須以”,”間隔,主從式資料庫系統 - CH3,18,2. 顯示資料表,語法：Show Tables; 執行結果：,主從式資料庫系統 - CH3,19,3. 顯示資料表結構,語法：Describe 資料表名稱; 範例：Describe pet; 執行結果：,主從式資料庫系統 - CH3,20,3-3-3 索引

7、表處理1. 建立索引表,語法：Create Index 索引表名稱 On 資料表名稱 （欄位名稱）; 範例：CREATE Index pet1 On pet (name, owner);,主從式資料庫系統 - CH3,21,2. 顯示索引表,語法：Show Index From 資料表名稱 ; 範例：Show Index From pet; 結果：,主從式資料庫系統 - CH3,22,3-3-4 載入資料,1. 批次載入：限定固定格式文字檔 載入指令：Load Data Local Infile “外部檔案來源” Into Table 資料表名稱 Fields Terminated By “

8、“ Line Terminated By rn; 範例：Load Data Local Infile “C:mysqldatatestpet.txt” Into Table pet Fields Terminated By “ ”;,主從式資料庫系統 - CH3,23,1. 批次載入說明,文字檔內容以列為單位，固定格式 欄位間須以逗號或空格間隔開來，若以空格間隔，需記住空格個數 編輯檔案時，若每一列是以rn符號換行的話，則輸入命令需加上Line Terminated By rn 參數 一筆紀錄若有部分欄位為空白資料時，對應欄位需加上 N 記號，MySQL會將此記號解譯為Null,主從式資料庫系

9、統 - CH3,24,1. 批次載入說明,文字檔之欄數需和資料表欄數相同，否則會產生錯誤 若資料是從主控端上傳伺服端，則須加上Local 參數 檔案可置於其他路徑，命令列之檔案來源需加上檔案路徑,主從式資料庫系統 - CH3,25,2. 逐筆輸入,指令一： Insert Into 資料表名 Values (欄位資料串) ; 範例：Insert Into pet Values (Puffball, Diane, hamster, f, 1990-03-30, Null); 指令二： Insert Into 資料表名 (欄位串列) Values (欄位資料串) ;,主從式資料庫系統 - CH3,2

10、6,2. 逐筆輸入說明,若所有欄位都需要輸入資料時，使用指令一，否則使用指令二 指令二需要指定欄名，欄名間以逗號間隔 除了數值資料外，欄位資料須以雙引號或單引號夾住 欄位資料若為空值，請填入Null,主從式資料庫系統 - CH3,27,3-3-5 瀏覽資料,基本的指令格式 SELECT 欄位串列 FROM 資料來源 WHERE 資料範圍 ORDER BY欄位串列 GROUP BY欄位串列;,主從式資料庫系統 - CH3,28,3-3-5 瀏覽資料說明,SELECT： 用來指定輸出欄位名稱，可用*表示全部欄位，欄名間使用逗號間隔 FROM：用來指定資料來源，可同時查看兩個以上資料表 WHERE：

11、用來設定輸出資料範圍，常見格式為指定欄名，再搭配關係運算子（諸如、=、(代表不等於)），然後輸入待比對之欄位條件值。若有需要，還可以設定第二個比較欄位，但兩個欄位間要用邏輯運算子結合，諸如AND、OR,主從式資料庫系統 - CH3,29,3-3-5 瀏覽資料說明(續),ORDER BY 用來指定資料要以升冪（修飾辭為ASC）或降冪（修飾辭為DESC）順序輸出 需要排序之欄位，請在BY後面定義，資料排序方式內定為升冪形式，ASC修飾辭可省略。若為降冪排列，DESC修飾辭不能省略 同時指定兩個以上欄位排序時，欄位間需以逗號間隔，排序順序可不相同,主從式資料庫系統 - CH3,30,3-3-5 瀏覽

12、資料說明(續),GROUP BY 用來指定群組欄位，若資料表內相同鍵值紀錄有多筆情況，而輸出資料需予以加總、平均、計算筆數等時，需指定群組欄位 群組欄位可指定一個以上，欄位間以逗號間隔,主從式資料庫系統 - CH3,31,1. 查看全部資料,指令： SELECT * FROM 單一資料表 ; 範例： SELECT * FROM pet; 執行結果：,主從式資料庫系統 - CH3,32,2. 查看部份資料（1）,指令：SELECT * FROM 單一資料表 WHERE 資料範圍 ; 範例：SELECT * FROM pet WHERE name = Bowser; 執行結果：,主從式資料庫系統

13、- CH3,33,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;,主從式資料庫系統 - CH3,34,3. 查看部份資料,範例(5)： Select * from pet Where (species=cat And sex=m) Or (species=dog And sex=f);

14、,主從式資料庫系統 - CH3,35,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;,主從式資料庫系統 - CH3,36,10. 資料排序,升冪： Select name, species, birth From pet Order By speci

15、es,birth Desc; 降冪 ： Select name, birth From pet Order By birth desc; 混合排序 ： Select name, species, birth From pet Order By species, birth Desc;,主從式資料庫系統 - CH3,37,13. 日期欄位計算,範例(1)： Select name, birth, curdate(),(year(curdate()-year(birth)-(right(curdate(),5) right(birth,5) as age From pet; 計算結果另存一欄，可使

16、用AS修飾辭指定欄名，本例欄名為”age”。指令中之curdate(), year(), right() 為MySQL內建函數，代表當日系統日期,主從式資料庫系統 - CH3,38,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)-(

17、right(curdate(),5) right(birth,5) as age From pet Order By age; 以計算欄位排序,主從式資料庫系統 - CH3,39,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

18、() 為MySQL內建函數，請注意欄名,主從式資料庫系統 - CH3,40,13. 日期欄位計算,範例(6)： Select name, birth From pet Where month(birth) = 5; 此例以函數取值並據以當過濾條件,主從式資料庫系統 - CH3,41,19 欄位資料與樣板值比較,範例(1 ) Select * From pet where name LIKE b%“ ; LIKE為條件值比較運算子，字母 b% 表示所有b 開頭之資料均符合 範例(2)： Select * From pet where name LIKE %fy“ ; %fy 表示所有字串尾有 f

19、y 字母之資料均符合,主從式資料庫系統 - CH3,42,19 欄位資料與樣板值比較,範例(3) Select * From pet where name LIKE %w%“ ; %w%表示所有字串中有w字母之資料均符合 範例(4)： Select * From pet where name LIKE _“ ; 表示所有字串中字母個數等於指定空格數之資料均符合,主從式資料庫系統 - CH3,43,23. 紀錄筆數計算,範例(1) Select Count(*) From pet ; Count()為計算紀錄數之函數，*為統計對象 範例(2)： Select owner, Count(*) Fr

20、om pet Group By owner ; Count()函數搭配其他欄位使用時，必須加上GROUP BY修飾辭來使用,主從式資料庫系統 - CH3,44,23. 紀錄筆數計算,範例(3) Select owner, Count(*) AS “筆數” From pet Group By owner ; 以AS指定欄位別名，可用中文命名，但需以引號夾住 範例(4)： Select species, sex, Count(*) From pet Group By species, sex ; 透過兩個群組欄位來統計筆數,主從式資料庫系統 - CH3,45,23. 紀錄筆數計算,範例(5) Se

21、lect species, sex, Count(*) From pet Where sex Is Not Null Group By species, sex ; 搭配過濾條件來統計筆數,主從式資料庫系統 - CH3,46,3-3-6 刪除資料,基本指令型式： DELETE FROM 資料來源 WHERE 資料範圍 ;,主從式資料庫系統 - CH3,47,3-3-6 刪除資料(續),刪除資料不需指定欄位串列，因為刪除資料時，是將整筆紀錄刪除，而不能刪除部份欄位 刪除資料之時機通常是有時效性者 刪除資料和刪除資料表是不同性質的，刪除資料時，資料表結構還留存在資料庫內，但刪除資料表卻是將整個資料

22、表從資料庫移除,主從式資料庫系統 - CH3,48,3-3-6 刪除資料(續),範例 Delete From pet Where birth 1993-01-01 ; Delete From pet Where owner=Gwen And species=cat ; Delete From pet;,主從式資料庫系統 - CH3,49,3-3-6 刪除資料(續),透過第二個資料表來挑選待刪除資料 DELETE t1 FROM t1, t2 WHERE t1.key_field = t2.key_filed ; 一道刪除指令同時刪除兩個表的資料 DELETE t1, t2 FROM t1, t

23、2, t3 WHERE t1.id=t2.id AND t2.id1=t3.id1;,主從式資料庫系統 - CH3,50,3-6-7 更新資料,基本指令型式： UPDATE資料表名稱 SET 欄位名稱 更新資料 , 後續欄位 WHERE 資料範圍 ;,主從式資料庫系統 - CH3,51,3-6-7 更新資料(續),更新資料之時機 : 原輸入之資料錯誤，事後發現錯誤 時效過期 異動檔資料轉入主控檔（例如會計之過帳）,主從式資料庫系統 - CH3,52,3-6-7 更新資料(續),將全部資料之死亡日期移除 Update pet SET death=NULL; 將名字叫”Bowser”之死亡日期註記

24、 Update pet SET death=1995-07-29 Where name = Bowser; 將名字為Slim之出生日期及性別更改 Update pet SET birth=1995-04-29 , sex=m Where name=Slim;,主從式資料庫系統 - CH3,53,3-4 MySQL進階應用 3-4-1 連結兩個資料表,同時輸出兩個資料表之處理指令： SELECT 欄位串列（可以間隔使用第一和第二資料表之欄位） FROM 第一資料表，第二資料表 WHERE第一資料表.鍵值欄名 第二資料表.鍵值欄名 ; 鍵值欄名通常是兩個表欄名相同者，鍵值欄名可指定一個以上，但要用

25、AND連結。,主從式資料庫系統 - CH3,54,同時輸出兩個資料表,範例： Select (Year(date)-year(birth)-(Right(date,5)Right(birth,5) AS 年齡, remark From pet, event Where = And type= 生產,主從式資料庫系統 - CH3,55,3-4-2 同一資料表呼叫兩次,範例： Select , p1.sex, , p2.sex, p1.species From pet AS p1, pet As p2 Where

26、 p1.species = p2.species And p1.sex = f And p2.sex = m;,主從式資料庫系統 - CH3,56,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 price DESC LIMIT 1;,主從式資料庫系統 - CH3,57,3-4-3 線上資料