LPIC 1 分科討論 – MySQL 語法補帖

前言

本文承接 LPIC 1 分科討論 – 105.3 SQL data management ,整理所有可能會考的 MySQL 語法,並盡量給予範例。


先推幾個別人的網站

我建議都要看看! 反正多看幾種寫法又不損失什麼,對吧? :P

語法與功能的快速瀏覽

我們很快的把SQL 的語法瀏覽過一遍,建議別忙著記格式,要先有大方向的概念,知道哪種工作是用哪一個語法,後面再去記住詳細格式。

  1. 新增資料庫、資料表:CREATE
  2. 刪除資料庫、資料表: DROP
  3. 使用資料庫: USE
  4. 插入(新增)一筆記錄:INSERT INTO
  5. 刪除記錄:DELETE
  6. 更新一筆記錄:REPLACE
  7. 更新多筆記錄:UPDATE
  8. 查詢資料:SELECT
  9. 修改資料表的結構: ALTER
  10. 條件: WHERE LIMIT
  11. 集結: GROUP BY  ORDER BY
  12. 多表: JOIN

記住:「指令大小寫沒有區別,但我們喜歡用大寫來幫助自己辨別一長串的指令

以下會針對上面的 12 個主題來討論。

我們下面會用到許多的範例,其執行結果大部分會以 phpMyAdmin 的介面呈現,這樣可能會比文字模式的輸出來得比較容易懂。而你也可用phpMyAdmin  來練習,phpMyAdmin 提供你一個文字框可以直接輸入來執行。如下圖:

image

圖一、 phpMyAdmin 介面


1. 新增資料庫、資料表:CREATE

基本格式是:

CREATE DATABASE 資料庫名;

例如、新增一個資料庫,名稱是 members1,語法如下:

CREATE DATABASE members1;

或是,新增一個資料表,名稱是 data1,語法如下:

CREATE TABLE data1( id int, name varchar(255) );

由於新增資料表的同時,必須要指定好所有的欄位跟資料型態,所以往往會落落長,在這裡我簡化了,只新增兩個欄位,如你所見,新增資料表的指令格式是:

CREATE TABLE 資料表名(欄位1 資料型態(限制), 欄位2 資料型態(限制)..更多欄位);

嗯…你可能會想知道有哪些資料型態可以用,請參考網頁: 05 MySQL 資料表的資料型態

2. 刪除資料庫、資料表: DROP

基本格式是:

DROP DATABASE 資料庫名;

DROP TABLE 資料表名;

把上面的資料庫跟資料表都刪除的範例:

DROP TABLE data1;
DROP DATABASE members1;

3. 使用資料庫: USE

現在假裝上面的資料庫沒刪除,那麼,想使用該資料庫的指令如下:

USE memebers1;

這樣就告訴 MySQL:「我要用 members1 這個資料庫」。 那可不可以不下指令,而直接使用某資料庫? 可以啊! 只要你把每一個指令的對象都完整的寫成

`資料庫名`.`資料表名`

例如:

SELECT * FROM `members1`.`data1`;

這樣也可以使用 members1 的 data1,而不用在一開始下 USE members1 指令。

以上三個重點就是最簡易的增刪資料庫、資料表的指令,下面我們繼續看如何新增資料表內的records(記錄)

註‧修改資料表的欄位跟結構要用 ALTER 指令,我放在最後面^^


4. 插入(新增)一筆記錄:INSERT

是時候來新增記錄了。請使用 INSERT , 一般來說需要加上 INTO ,基本格式是:

INSERT INTO  資料表名(“欄位1“, “欄位2“, …)  VALUES (“內容1“, “內容2“, …);

這樣,就是最簡單的新增一筆記錄 ( 其實,你可以仔細看一下上面的圖一就是在操作新增一筆記錄 ),在 members1 的 data1 資料表新增一筆記錄的範例指令如下:

INSERT INTO `members1`.`data1` (`id`, `姓名`, `生日`, `電話`, `血型`, `身高`, `體重`, `興趣`, `工作`, `戶籍地址`, `通訊地址`, `信箱`, ` 備註`) VALUES ('13', '大鯊魚', '2013-10-07', '13223654789632', 'A', '165', '65', 'C++', '大樓清潔工', '竹南', '竹北', 'shark@gmail.com', '');

結果如下:

image

這樣,你就完成插入一筆記錄啦!

提醒你,若你是新手,我建議你嚴格仿照 phpMyAdmin 回饋的格式以及符號來完整撰寫 SQL 指令,雖然有很多符號是可以省略的,但是等你熟悉了以後再來偷懶吧! ^^

5. 刪除記錄:DELETE

如何刪除資料呢? 這真是太簡單了!基本格式就是:

DELETE  FROM 資料表名 WHERE 條件;

比如,刪除上面那筆記錄,我們就想成是刪除 id =13 的紀錄 (id =13 就是條件),範例如下:

DELETE FROM `members`.`data` WHERE `data`.`id` = 13

6. 更新一筆記錄:REPLACE

REPLACE 的語法跟 INSERT 一樣,基本格式如下:

REPLACE INTO  資料表名(“欄位1“, “欄位2“, …)  VALUES (“內容1“, “內容2“, …);

但是注意啦! REPLACE 的基本概念是「全改」! 所以你不能只填寫部分欄位喔!如果你沒寫欄位1也沒寫內容1,或者你單純把內容1留空,那你會發現該欄位會被覆寫為空值 XD

一個 REPLACE 的範例如下:

REPLACE INTO `members1`.`data1` (`id`, `姓名`, `生日`, `電話`, `血型`, `身高`, `體重`, `興趣`, `工作`, `戶籍地址`, `通訊地址`, `信箱`, ` 備註`) VALUES ('13', '小鯊魚', '2013-10-07', '13223654789632', 'A', '165', '65', 'C++', '大樓清潔工', '竹南', '竹北', 'shark@gmail.com', '');

這是基於上面的 INSERT 範例的大鯊魚這筆資料。我只修改姓名,把大鯊魚改成小鯊魚,其他資料不變。嗯 …你可以自己試驗看看,留空會有啥結果。

7. 更新多筆記錄:UPDATE

或者可以說,條件式的更新部分記錄,需要配合關鍵字 SET ,基本格式如下:

UPDATE 資料表名 SET (“欄位1“=”內容1“, “欄位1“=”內容1” ,…   WHERE 條件;

注意,如果沒設定 WHERE 條件,那麼整個資料表的欄位1會全部變成內容1 噢!XD

例如我要更新上面範例的大鯊魚的電話,就要記得設定 WHERE 條件為 id=13(或 WHERE 姓名=大鯊魚),範例如下:

UPDATE `members1`.`data1` SET `電話` = '0000000000000000000000' WHERE `data1`.`id` =13;

好了,大鯊魚的電話已經成功的改為 20 個 0 了

image


8. 查詢資料:SELECT

查找資料也很簡單的啊! 基本格式就是

SELECT 目標欄位 FROM 資料表名 WHERE 條件 LIMIT 資料筆數;

那個目標欄位表示你想收到的欄位數目。

假如你要全部欄位,那就 SELECT *,假如你只要電話這個欄位,那就 SELECT 電話,比如我要在 members1 的 data1 內,找出身高高於 180 的人的所有記錄,範例如下:

SELECT * FROM `members1`.`data1` WHERE `身高`>180

查詢結果像這樣:

image

如果你沒興趣看那麼多欄位,其實你只要他們的姓名電話,那就這樣查詢:

SELECT 姓名,電話 FROM `members1`.`data1` WHERE `身高` >180

查詢結果如下,很精簡囉!

image

最後說一下關鍵字 LIMIT,那個 LIMIT 後面接的是回饋給你的紀錄筆數,譬如你最多收30筆記錄就不要多的,那就寫LIMIT 30 就可啦! 不過他不是一個絕對必要的關鍵字,只不過通常對於你整理資料很有幫助。

9. 修改資料庫、資料表: ALTER

修改資料表結構,指令 ALTER 基本格式如下:

ALTER TABLE 資料表名 ADD/CHANGE/MODIFY/DROP/RENAME  定的資料結構;

上述的 ADD、CHANGE 是動作,後面接上要更動的資料結構。例如我想修改姓名這個欄位的名稱,改成叫做 … 會員全名吧!然後再加一個姓名縮寫的欄位。

兩個範例如下

ALTER TABLE `members1`.`data1` CHANGE `姓名` `會員全名` VARCHAR(255);
ALTER TABLE `members1`.`data1` ADD `姓名縮寫` VARCHAR(255) AFTER `會員全名`;

成果如下:

image


10. 條件: WHERE    LIMIT

其實這個應該是不用再多說了吧? 上面已經不少應用了,不過我們還是再提醒自己一次吧!

WHERE 條件,這條件大多是這樣的形式:  欄位> 某個值、欄位=某個值 …

注意啊!所謂的值可不是光指數字而已,可以是文字、日期 … 等等喔!別忘了 MySQL 眾多的資料型態:05 MySQL 資料表的資料型態  。

LIMIT 資料筆數,代表最多接收幾筆資料,如果寫 LIMIT 100 就是最多接收 100 筆。如果寫 LIMIT 0, 30,就是起點第 0 筆,總共輸出 30 筆。

11. 集結: GROUP BY  ORDER BY

其實這兩個關鍵字是要依附於上面的指令,作為一種資料後續處理的手段。他們的基本格式大概是這樣:

[你的 SQL  指令]  GROUP BY 欄位;

[你的 SQL  指令]  ORDER BY 欄位;

那他們到底會做什麼呢?

GROUP BY ,會以你指定的欄位的值為基準做分類,然後把分類結果展示給你,例如,我們在 SELECT 語句後面接上了「 GROUP BY 血型」,那 MySQL 會依照會員的血型分類,然後展示代表性的資料給你,指令如下:

SELECT * FROM `members1`.`data1` GROUP BY `血型`;

結果會這樣:

image

因為血型有四種,所以 MySQL 就丟這四種的代表記錄給你看,資料當然就會是四筆囉!

當然,這樣可能沒什麼意義,也許你是想知道每種血型有多少人,那就在 SELECT 裡多索要一個總計的結果: count(血型) 。

SELECT count(血型),血型 FROM `members1`.`data1` GROUP BY `血型`;

結果如下,就只會顯示血型跟總和人數:

image

ORDER BY 則超簡單的啦,他就是拿你指定的欄位來作為排序依據,例如你可以依據生日來排序會員資料,嗯…這麼簡單所以…我就懶得舉例啦~~XD

12. 多表: JOIN

嗯 … JOIN 就開始稍微進入比較複雜的資料操作,但別害怕! 據說考得不會很難(但看起來沒考過…xD)

JOIN 的基本概念就是你已經有兩大包查詢結果,可能來自很不同表,也可能來自同一個表,那你現在想把這兩包資料合併顯示,這就是 JOIN 要做的事情,參考下面這樣的說明:

image

你可能用初步的指令得到兩包資料:「學校跟姓名」、「不同學校的桌號」,剩下就是要把資料合併顯示啦!

JOIN關鍵字的基本格式是:

SELECT `資料表1`.`欄位名`, `資料表2`.`欄位名` FROM 資料表1 JOIN 資料表2 ON 條件;

其中,JOIN 前面還可以加 LEFT/RIGHT/INNER/OUTER 等等關鍵字,LEFT 就是「以左邊指令 1 的記錄為準輸出,無論指令 2 有沒有符合的紀錄」。

為了弄懂,我們很快用 LEFT JOIN 示範上面的解說圖片,假設上面的資料分別來自 data1 資料表以及 act1 資料表吧!

假設查詢到第一包資料的指令是這樣:

SELECT `data1`.`學校`,  `data1`.`姓名` FROM data1;

假設查詢到第二包資料的指令是這樣:

SELECT `act1`.`學校`,  `act1`.`桌號` FROM act1;

用 JOIN 合併的查詢應該長這樣:

SELECT `data1`.`姓名` `act1`.`桌號` FROM data1 LEFT JOIN act1 on `data1`.`學校`=`act1`.`學校`;

我的範例使用 LEFT JOIN,也就是會以第一包資料的格式為準,去排列出結果。

如同上圖的最右邊的表格,正由於我們用的是 LEFT JOIN ,請注意第二包資料之中,中央是沒有桌號的,因此你就看到中央的小鍋,他名字後面沒有桌次囉!

其他的關鍵字呢 … RIGHT 是以右邊的指令2 的紀錄為準,INNER跟OUTER則是交集與聯集。(注意! 這是以 MySQL 為準,不同 SQL 可能有不同行為,使用前先翻閱手冊比較好!)

其他 JOIN  的進一步理解,請參考:


以上就是 MySQL 基本的指令、關鍵字運用,以及簡單的範例說明,祝大家理解愉快!


噓 …  在這偷偷給MySQL一刀,別忘了 MySQL 已經在甲骨文旗下了,請參考:

Google棄甲骨文MySQL,將大規模導入MariaDB

喔! 如果你只是要通過 LPIC 1 那你應該不必在意這件事 XDDD

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料