第3章 MySQL教學範本 主從式資料庫系統 - CH3.

Presentation on theme: "第3章 MySQL教學範本 主從式資料庫系統 - CH3."— Presentation transcript:

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
Enter password: ******** 指令三：mysql 主從式資料庫系統 - CH3

5 連線成功畫面 Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: nt. Type ‘help’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> 主從式資料庫系統 - CH3

6 連線失敗畫面 ERROR 1045 (28000) Access denied for (using password No) mysql> 主從式資料庫系統 - CH3

7 3-1-2 離線 離線指令型式： 離線畫面： mysql>QUIT Bye 指令一： QUIT 指令二： \q
主從式資料庫系統 - CH3

8 3-2 指令編輯方式 一行指令一道命令：指令編輯完成後，直接按【Enter】鍵執行命令
主從式資料庫系統 - CH3

9 3-2 指令編輯方式(續) 指令列若為標準SQL指令，必須加上 ”;”結束指令 不管指令列跨越幾行，一道命令只能包含一個 ”;”
指令列若為MySQL內部指令，則不用加上”;”，例如Use, Quit等 主從式資料庫系統 - CH3

10 3-2 指令編輯方式(續) 範例 mysql>Select User(), Current_date; (a)
->, ->Current_date; (b) ->\c (c) mysql>Use mysql (d) 主從式資料庫系統 - 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資料庫名稱 ;
3-3-1資料庫處理 1. 建立資料庫 語法：Create Database資料庫名稱 ; 範例：Create Database Temp; 建立一個名稱為【Temp】之資料庫。 執行結果： 主從式資料庫系統 - CH3

13 2. 顯示資料庫明細 語法：Show Databases; 執行結果： 主從式資料庫系統 - CH3

14 3. 選用資料庫 語法：Use 資料庫名稱 範例：Use Temp; 螢幕會提示：Database Changed之信息

15 4. 刪除資料庫 語法： Drop Database資料庫名稱; 範例： Drop Database Temp;
主從式資料庫系統 - CH3

16 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); 主從式資料庫系統 - CH3

17 1. 建立資料表 欄位定義之格式為 欄位名稱 資料型態 （寬度） [Null | NOT Null] [Default 值]
欄位間須以”,”間隔 主從式資料庫系統 - CH3

18 2. 顯示資料表 語法：Show Tables; 執行結果： 主從式資料庫系統 - CH3

19 3. 顯示資料表結構 語法：Describe 資料表名稱; 範例：Describe pet; 執行結果： 主從式資料庫系統 - CH3

20 3-3-3 索引表處理 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 “ “ [Line Terminated By ‘\r\n’]; 範例：Load Data Local Infile “C:\\mysql\\data\\test\\pet.txt” Into Table pet Fields Terminated By “ ”; 主從式資料庫系統 - CH3

23 1. 批次載入說明 文字檔內容以列為單位，固定格式 欄位間須以逗號或空格間隔開來，若以空格間隔，需記住空格個數
編輯檔案時，若每一列是以’\r\n’符號換行的話，則輸入命令需加上[Line Terminated By ‘\r\n’] 參數 一筆紀錄若有部分欄位為空白資料時，對應欄位需加上 [’\N’] 記號，MySQL會將此記號解譯為Null 主從式資料庫系統 - CH3

24 1. 批次載入說明 文字檔之欄數需和資料表欄數相同，否則會產生錯誤 若資料是從主控端上傳伺服端，則須加上[Local] 參數
檔案可置於其他路徑，命令列之檔案來源需加上檔案路徑 主從式資料庫系統 - CH3

25 2. 逐筆輸入 指令一： Insert Into 資料表名 Values (欄位資料串) ;
範例：Insert Into pet Values (‘Puffball’, ’Diane’, ’hamster’, ’f’, ’ ’, Null); 指令二： Insert Into 資料表名 (欄位串列) Values (欄位資料串) ; 主從式資料庫系統 - CH3

26 2. 逐筆輸入說明 若所有欄位都需要輸入資料時，使用指令一，否則使用指令二 指令二需要指定欄名，欄名間以逗號間隔
除了數值資料外，欄位資料須以雙引號或單引號夾住 欄位資料若為空值，請填入Null 主從式資料庫系統 - CH3

27 3-3-5 瀏覽資料 基本的指令格式 SELECT 欄位串列 FROM 資料來源 WHERE 資料範圍 ORDER BY欄位串列
GROUP BY欄位串列; 主從式資料庫系統 - CH3

28 3-3-5 瀏覽資料說明 SELECT： 用來指定輸出欄位名稱，可用*表示全部欄位，欄名間使用逗號間隔
FROM：用來指定資料來源，可同時查看兩個以上資料表 WHERE：用來設定輸出資料範圍，常見格式為指定欄名，再搭配關係運算子（諸如＝、>、>=、<、<=、<>(代表不等於)），然後輸入待比對之欄位條件值。若有需要，還可以設定第二個比較欄位，但兩個欄位間要用邏輯運算子結合，諸如AND、OR 主從式資料庫系統 - CH3

29 3-3-5 瀏覽資料說明(續) ORDER BY 用來指定資料要以升冪（修飾辭為ASC）或降冪（修飾辭為DESC）順序輸出
同時指定兩個以上欄位排序時，欄位間需以逗號間隔，排序順序可不相同 主從式資料庫系統 - CH3

30 3-3-5 瀏覽資料說明(續) GROUP BY 用來指定群組欄位，若資料表內相同鍵值紀錄有多筆情況，而輸出資料需予以加總、平均、計算筆數等時，需指定群組欄位 群組欄位可指定一個以上，欄位間以逗號間隔 主從式資料庫系統 - CH3

31 1. 查看全部資料 指令： SELECT * FROM 單一資料表 ; 範例： SELECT * FROM pet; 執行結果：
主從式資料庫系統 - CH3

32 2. 查看部份資料（1） 指令：SELECT * FROM 單一資料表 WHERE 資料範圍 ;
範例：SELECT * FROM pet WHERE name = ’Bowser’; 執行結果： 主從式資料庫系統 - CH3

33 3. 查看部份資料 範例(2) ： 範例(3)： 範例(4)：
Select * from pet Where birth >= " "; 範例(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"); 主從式資料庫系統 - CH3

35 7. 查看特定欄位資料 範例(1)： 範例(2)： 範例(3)： 範例(4)： Select name, birth From pet;
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 species,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; 計算結果另存一欄，可使用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))-(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() 為MySQL內建函數，請注意欄名 主從式資料庫系統 - CH3

40 13. 日期欄位計算 範例(6)： Select name, birth From pet Where month(birth) = 5;
此例以函數取值並據以當過濾條件 主從式資料庫系統 - CH3

41 19 欄位資料與樣板值比較 範例(1 ) 範例(2)： Select * From pet where name LIKE "b%“ ;
LIKE為條件值比較運算子，字母 b% 表示所有b 開頭之資料均符合 範例(2)： Select * From pet where name LIKE "%fy“ ; %fy 表示所有字串尾有 fy 字母之資料均符合 主從式資料庫系統 - CH3

42 19 欄位資料與樣板值比較 範例(3) 範例(4)： Select * From pet where name LIKE "%w%“ ;
%w%表示所有字串中有w字母之資料均符合 範例(4)： Select * From pet where name LIKE "_____“ ; 表示所有字串中字母個數等於指定空格數之資料均符合 主從式資料庫系統 - CH3

43 23. 紀錄筆數計算 範例(1) 範例(2)： Select Count(*) From pet ;
Select owner, Count(*) From 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) Select 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 刪除資料(續) 刪除資料不需指定欄位串列，因為刪除資料時，是將整筆紀錄刪除，而不能刪除部份欄位 刪除資料之時機通常是有時效性者
刪除資料和刪除資料表是不同性質的，刪除資料時，資料表結構還留存在資料庫內，但刪除資料表卻是將整個資料表從資料庫移除 主從式資料庫系統 - 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, t2, 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 更新資料(續) 將全部資料之死亡日期移除 將名字叫”Bowser”之死亡日期註記 將名字為Slim之出生日期及性別更改
Update pet SET death=NULL; 將名字叫”Bowser”之死亡日期註記 Update pet SET death=’ ’ Where name =’ Bowser’; 將名字為Slim之出生日期及性別更改 Update pet SET birth=’ ’ , sex=’m’ Where name=’Slim’; 主從式資料庫系統 - CH3

53 3-4 MySQL進階應用 3-4-1 連結兩個資料表 同時輸出兩個資料表之處理指令：
SELECT 欄位串列（可以間隔使用第一和第二資料表之欄位） FROM 第一資料表，第二資料表 WHERE第一資料表.鍵值欄名 ＝ 第二資料表.鍵值欄名 ; 鍵值欄名通常是兩個表欄名相同者，鍵值欄名可指定一個以上，但要用AND連結。 主從式資料庫系統 - CH3

54 同時輸出兩個資料表 範例： 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= "生產" 主從式資料庫系統 - CH3

55 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'; 主從式資料庫系統 - CH3

56 3-4-3 線上資料分析 挑選商品中最大值 以子查詢挑選單價最高之商品 搭配【LIMIT】修飾辭來限定輸出紀錄筆數
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 線上資料分析(續) 挑選各類商品中單價最高者 挑選各類商品中單價最高並輸出商品資訊
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); 主從式資料庫系統 - CH3

58 3-4-3 線上資料分析(續) 與Group By搭配之常用分析函數 AVG()：求平均數 MIN()：挑最小值 MAX()：挑最大值
SUM()：求總數 STD()：樣本標準差，分母為紀錄數-1 STDDEV()：標準差，分母為紀錄數，與ORACLE相容 VARIANCE()：變異數，分母為紀錄數 主從式資料庫系統 - CH3

59 3-4-4 子查詢 依第2表之結果來過濾第1表 將挑選第2表資料新增至第1表
Select * From t1 Where column1 = (Select column1 From t2); 將挑選第2表資料新增至第1表 Insert Into t1 (欄位串列) Select (欄位串列) From t2 Where (t2之過濾條件); 兩個資料表之欄位串列之個數必須相同，對應型態也須相同 主從式資料庫系統 - CH3

60 3-4-4 子查詢(續) 依第2表挑出之資料將第1表對應資料刪除 以IN運算子比對資料
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); 前述指令之 ”IN” 限定辭，也可改用 ” NOT IN”，表示不在子查詢內的資料 主從式資料庫系統 - CH3