MySQL中文參考手冊

譯者：晏子 (clyan@sohu.com）
GB 碼主頁：http://linuxdb.yeah.net

Big5 轉碼者：statue (statue@bbs.yzu.edu.tw）
詞彙轉換：彭武興 (wilson@mailbox.com.tw)
Big5 碼主頁： http://cnpa.yzu.edu.tw/~cfc/docs/mysqldoc_big5/manual_toc.html
Big5 碼分站： http://php.wilson.gs/mysqldoc/big5/manual_toc.html

第一章, 前一章, 下一章, 最後一章，目錄.

8 MySQL教學課程

本章通過演示如何使用mysql客戶程式創造和使用一個簡單的資料庫，提供一個MySQL的入門教學課程。mysql（有時稱為“終端監視器”或只是“監視”)是一個交互式程式，允許你連接一個MySQL伺服器，運行查詢並察看結果。mysql可以用於批模式：你預先把查詢放在一個文件中，然後告訴mysql執行文件的內容。使用mysql的兩個方法都在這裡涉及。

為了看清由mysql提供的一個選擇項目表了，用--help選項調用它：

shell> mysql --help

本章假定mysql已經被安裝在你的機器上，並且有一個MySQL伺服器你可以連接。如果這不是真的，聯絡你的MySQL管理員。（如果你是管理員，你將需要請教這本手冊的其他章節。）

本章描述建立和使用一個資料庫的全程序。如果你僅僅對存取一個已經存在資料庫感興趣，你可能想要跳過描述怎樣創建資料庫及它所包含的表的章節。

既然本章本質上是一個教學課程，許多細節有必要被省略。對於這裡所涉及的話題的更多資訊，咨詢本手冊的相關章節。

8.1 連接與斷開服務者

為了連接伺服器，當你調用mysql時，你通常將需要提供一個MySQL用戶名和很可能，一個密碼。如果伺服器運行在不是你登錄的一台機器上，你也將需要指定主機名。聯繫你的管理員以找出你應該使用什麼連接參數進行連接(即，那個主機，用戶名字和使用的密碼)。一旦你知道正確的參數，你應該能像這樣連接：

shell> mysql -h host -u user -p
Enter password: ********

********代表你的密碼﹔當mysql顯示Enter password:提示時輸入它。

如果能工作，你應該看見mysql>提示後的一些介紹資訊：

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 459 to server version: 3.22.20a-log

Type 'help' for help.

mysql>

提示符告訴你mysql準備為你輸入命令。

一些MySQL安裝允許用戶以“anoymous”(匿名)用戶連接在本地主機上運行的伺服器。如果在你的機器是這種情況，你應該能通過沒有任何選項地調用mysql與該伺服器連接：

shell> mysql

在你成功地連接後，你可以在mysql>提示下打入QUIT隨時斷開：

mysql> QUIT
Bye

你也可以鍵入control-D斷開。

在下列章節的大多數例子都假設你連接到伺服器。由mysql>提示指明他們。

8.2 輸入查詢

確保你連接上了伺服器，如在先前的章節討論的。這樣做本身將不選擇任何資料庫來工作，但是那很好。從這點講，知道關於如何出詢問的一點知識，比馬上跳至創建表、給他們裝載數據並且從他們檢索數據要來的重要寫。本節描述輸入命令的基本原則，使用幾個查詢，你能嘗試讓自己mysql是如何工作的。

這是一個簡單的命令，要求伺服器告訴你它的版本號和當前日期。在mysql>提示打入如下命令並按回車鍵：

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

這詢問說明關於mysql幾件事:

一個命令通常由SQL語句組成，隨後有一個分號。（有一些例外不需要一個分號。早先提到的QUIT是他們之一。我們將以後看到其它。）
當你發出一個命令時，mysql發送它給伺服器並顯示結果，然後打出另外一個mysql>顯示它準備好接受另外的命令。
mysql以一張表格(行和列)顯示查詢輸出。第一行包含列的標簽，隨後的行是詢問結果。通常，列標簽是你取自資料庫表的列的名字。如果你正在檢索一個表達式而非表列的值(如剛才的例子)，mysql用表達式本身標記列。
mysql顯示多少行被返回，和查詢花了多長執行，它給你提供伺服器性能的一個大致概念。因為他們表示時鐘時間(不是 CPU 或機器時間)，並且因為他們受到諸如伺服器負載和網路延時的影響，因此這些值是不精確的。（為了簡潔，在本章剩下的例子中不再顯示“集合中的行”。）

關鍵詞可以以任何大小寫字符被輸入。下列詢問是等價的：

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

這裡有另外一個查詢，它說明你能將mysql用作一個簡單的計算器：

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

至今顯示的命令是相當短的，單行語句。你甚至能在單行上輸入多條語句，只是以一個分號結束每一條：

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| version()    |
+--------------+
| 3.22.20a-log |
+--------------+

+---------------------+
| NOW()               |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+

一個命令不必全在一個單獨行給出，所以需要多行的較長命令不是一個問題。mysql通過尋找終止的分號而不是尋找輸入行的結束來決定你的語句在哪兒結束。（換句話說，mysql接受自由格式輸入：它收集輸入行但執行他們直到它看見分號。）

這裡是一個簡單的多行語句的例子：

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

在這個例子中，在你輸入一個多行查詢的第一行後，要注意提示符如何從mysql>變為->，這正是mysql如何指出它沒見到完整的語句並且正在等待剩餘的部分。提示符是你的朋友，因為它提供有價值的反饋，如果你使用該反饋，你將總是知道mysql正在等待什麼。

如果你決定，你不想要執行你在輸入程序中輸入的一個命令，打入\c取消它：

mysql> SELECT
    -> USER()
    -> \c
mysql>

這裡也要注意提示符，在你打入\c以後，它切換回到mysql>，提供反饋以表明mysql準備接受一個新命令。

下表顯示出你可以看見的各個提示符並總結他們意味著mysql在什麼狀態下：

提示符	意思
`mysql>`	準備好接受新命令
`->`	等待多行命令的下一行
`'>`	等待下一行，收集以單引號(“'”)開始的字符串
`">`	等待下一行，收集以雙引號(“"”)開始的字符串

當你打算在一個單行上發出一個命令時，多行語句通常“偶然”出現，但是忘記終止的分號。在這種情況中，mysql等待進一步輸入：

mysql> SELECT USER()
    ->

如果這發生在你身上(你認為你輸完了語句但是唯一的反應是一個->提示符)，很可能mysql正在等待分號。如果你沒有注意到提示符正在告訴你什麼，在認識到你需要做什麼之前，你可能花一會兒時間呆坐在那兒。進入一個分號完成語句，並且mysql將執行它：

mysql> SELECT USER()
    -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

'>和">提示符出現在在字符串收集期間。在MySQL中，你可以寫由“'”或“"”字符括起來的字符串 (例如，'hello'或"goodbye")，並且mysql讓你進入跨越多行的字符串。當你看到一個'>或">提示符時，這意味著你已經輸入了包含以“'”或“"”括號字符開始的字符串的一行，但是還沒有輸入終止字符串的匹配引號。如果你確實正在輸入一個多行字符串，很好，但是果真如此嗎？不盡然。更常見的，'>和">提示符顯示你粗心地省掉了一個引號字符。例如：

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
    ">

如果你輸入該SELECT語句，然後按回車鍵並等待結果，什麼都沒有出現。不要驚訝，“為什麼該查詢這麼長呢？”，注意">提示符提供的線索。它告訴你mysql期望見到一個未終止字符串的餘下部分。（你在語句中看見錯誤嗎？字符串"Smith正好丟失第二個引號。）

走到這一步，你該做什麼？最簡單的是取消命令。然而，在這種情況下，你不能只是打入\c，因為mysql作為它正在收集的字符串的一部分來解釋它！相反，輸入關閉的引號字符(這樣mysql知道你完成了字符串)，然後打入\c：

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
    "> "\c
mysql>

提示符回到mysql>，顯示mysql準備好接受一個新命令了。

知道'>和">提示符意味著什麼是很重要的，因為如果你錯誤地輸入一個未終止的字符串，任何比你下一步輸入的行好像將要被mysql忽略--包括包含QUIT的行！這可能相當含糊，特別是在你能取消當前命令前，如果你不知道你需要提出終止引號。

8.3 常用查詢的例子

下面是一些學習如何用MySQL解決一些常見問題的例子。

一些例子使用資料庫表“shop”，包含某個商人的每篇文章(物品號)的價格。假定每個商人的每篇文章有一個單獨的固定價格，那麼(物品，商人)是記錄的主鍵。

你能這樣創建例子資料庫表：

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

好了，例子數據是這樣的：

SELECT * FROM shop

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

8.3.1 列的最大值

“最大的物品號是什麼？”

SELECT MAX(article) AS article FROM shop

+---------+
| article |
+---------+
|       4 |
+---------+

8.3.2 擁有某個列的最大值的行

“找出最貴的文章的編號、商人和價格”

在ANSI-SQL中這很容易用一個子查詢做到：

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop)

在MySQL中（還沒有子查詢)就用2步做到：

用一個SELECT語句從表中得到最大值。

使用該值編出實際的查詢：

SELECT article, dealer, price
FROM   shop
WHERE  price=19.95

另一個解決方案是按價格降序排序所有行並用MySQL特定LIMIT子句只得到的第一行：

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1

注意:如果有多個最貴的文章( 例如每個19.95)，LIMIT解決方案僅僅顯示他們之一！

8.3.3 列的最大值：按組：只有值

“每篇文章的最高的價格是什麼？”

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

8.3.4 擁有某個字段的組間最大值的行

“對每篇文章，找出有最貴的價格的交易者。”

在ANSI SQL中，我可以用這樣一個子查詢做到：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)

在MySQL中，最好是分幾步做到：

得到一個表(文章，maxprice)。見8.3.4 擁有某個域的組間最大值的行。
對每篇文章，得到對應於儲存最大價格的行。

這可以很容易用一個臨時表做到：

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES article read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.articel AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

如果你不使用一個TEMPORARY表，你也必須鎖定“tmp”表。

“它能一個單個查詢做到嗎？”

是的，但是只有使用我稱之為“MAX-CONCAT詭計”的一個相當低效的詭計：

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

最後例子當然能通過在客戶程式中分割連結的列使它更有效一點。

8.3.5 使用外鍵

不需要外鍵聯結2個表。

MySQL唯一不做的事情是CHECK以保証你使用的鍵確實在你正在引用表中存在，並且它不自動從有一個外鍵定義的表中刪除行。如果你像平常那樣使用你的鍵值，它將工作得很好！

CREATE TABLE persons (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirts (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
    PRIMARY KEY (id)
);

INSERT INTO persons VALUES (NULL, 'Antonio Paz');

INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());

SELECT * FROM persons;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

SELECT s.* FROM persons p, shirts s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color &lt;&gt; 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

8.4 創造並使用一個資料庫

既然你知道怎樣輸入命令，現在是存取一個資料庫的時候了。

假定在你的家(你的“動物園”)中有很多寵物，並且你想追蹤關於他們各種各樣類型的資訊。你可以通過創建表來保存你的數據並根據所需要的資訊裝載他們做到，然後你可以通過從表中檢索數據來回答關於你的動物不同種類的問題。本節顯示如何做到所有這些事情：

怎樣創建一個資料庫
怎樣創建一個資料庫表
怎樣裝載數據到資料庫表
怎樣以各種方法從表中檢索數據
怎樣使用多個表

動物園資料庫將會是簡單的(故意的)，但是不難把它想像成可能用到相似類型資料庫的真實世界情況。例如，這樣的一個資料庫能被一個農夫用來追蹤家畜，或由一個獸醫追蹤病畜記錄。

使用SHOW語句找出在伺服器上當前存在什麼資料庫：

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

資料庫列表可能在你的機器上是不同的，但是mysql和test資料庫很可能的在其間。mysql是必需的，因為它描述用戶存取權限，test資料庫經常作為一個工作區提供給用戶試試身手。

如果test資料庫存在，嘗試存取它：

mysql> USE test
Database changed

注意，USE，類似QUIT，不需要一個分號。（如果你喜歡，你可以用一個分號終止這樣的語句﹔這無礙)USE語句在使用上也有另外一個特殊的地方：它必須在一個單行上給出。

你可列在後面的例子中使用test資料庫(如果你能訪問它)，但是你在該資料庫創建的任何東西可以被與訪問它的其他人刪除，為了這個原因，你可能應該詢問你的MySQL管理員許可你自己使用的一個資料庫。假定你想要調用你的menagerie，管理員需要執行一個這樣的命令：

mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

這裡your_mysql_name是分配給你的MySQL用戶名。

8.4.1 創建並選用一個資料庫

如果在設置你的權限時，管理員為你創建了資料庫，你可以開始使用它。否則，你需要自己創建它：

mysql> CREATE DATABASE menagerie;

在Unix下，資料庫名字是區分大小寫的(不像SQL關鍵詞)，因此你必須總是以menagerie引用你的資料庫，不是Menagerie、MENAGERIE或一些其他變種。對表名也是這樣的。（在Windows下，該限制不適用，盡管你必須在一個給定的查詢中使用同樣的大小寫來引用資料庫和表。）

創建了一個資料庫並不選定以使用它，你必須明確地做這件事。為了使menagerie稱為當前的資料庫，使用這個命令：

mysql> USE menagerie
Database changed

你的資料庫只需要創建一次，但是你必須在每次啟動一個mysql會話時為使用而選擇它。你可以由發出上面一個USE語句做到。另外，當你調用時mysql，你可在命令行上選擇資料庫，就在你可能需要提供的任何連接參數之後指定其名字。例如：

shell> mysql -h host -u user -p menagerie
Enter password: ********

注意，menagerie不是你在剛才所示命令的密碼。如果你想要在命令行上在-p選項後提供你的密碼，你必須做到沒有多餘的空格(例如，如-pmypassword，不是-p mypassword)。然而，不建議把你的密碼放在命令行上，因為這樣做把它暴露出來，能被在你的機器上登錄的其他用戶窺探到。

8.4.2 創建一個資料庫表

創建資料庫是容易的部分，但是在這時它是空的，正如SHOW TABLES將告訴你：

mysql> SHOW TABLES;
Empty set (0.00 sec)

較難的部分是決定你的資料庫結構應該是什麼：你將需要什麼資料庫表，和在他們中有什麼樣的列。

你將需要一個包含你每個寵物的記錄的表。它可稱為pet表，並且它應該包含，最少，每個動物的名字。因為名字本身不是很有趣，表應該包含另外的資訊。例如，如果在你豢養寵物的家庭有超過一個人，你可能想要列出每個動物的主人。你可能也想要記錄例如種類和性別的一些基本的描述資訊。

年齡呢？那可能有趣，但是在一個資料庫中儲存不是一件好事情。年齡隨著時間流逝而變化，這意味著你將要不斷地更新你的記錄。相反, 儲存一個固定值例如生日比較好，那麼，無論何時你需要年齡，你可以以當前日期和出生日期之間的差別來計算它。MySQL為日期運算提供了函數，因此這並不困難。儲存出生日期而非年齡也有其他優點：

你可以將資料庫用於這樣的任務例如產生即將到來的寵物生日的提示。（如果你認為這類查詢是點蠢，注意，這與在一個商務資料庫來標示你不久要給它發出生日祝賀的客戶的環境中是同一個問題，因為計算機幫助私人聯絡。）
你可以相對於日期而不止是當前日期來計算年齡。例如，如果你在資料庫儲存死亡日期，你能容易計算一只寵物是何時多大死的。

你可能想到pet表中其他有用的其他類型資訊，但是到目前為止這些現在是足夠了：名字、主人、種類，性別、出生和死亡日期。

使用一個CREATE TABLE語句指定你的資料庫表的布局：

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR對name、owner和species列是個好的選擇，因為列值將會是變長的。這些列的長度都不必是相同的，而且不必是20。你可以挑選從1到255的任何長度，無論哪個對你來說好像最合理。（如果你做了較差的選擇，以後會變得你需要一個更長的字段，MySQL提供一個ALTER TABLE語句。）

動物性表可以用許多方法表示，例如，"m"和"f"，或也許"male"和"female"。使用單個字符"m"和"f"是最簡單的。

為birth和death列使用DATE數據類型是相當明顯的選擇。

既然你創建了一個表，SHOW TABLES應該產生一些輸出：

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

為了驗証你的表是按你期望的方式被創建，使用一個DESCRIBE語句：

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

你能隨時DESCRIBE，例如，如果你忘記在你表中的列的名字或他們是什麼類型。

8.4.3 將數據裝入一個資料庫表

在你創建表後，你需要充實它。LOAD DATA和INSERT語句用於此。

假定你的寵物紀錄描述如下。（觀察到MySQL期望日期時以YYYY-MM-DD格式﹔這可能與你習慣的不同。）

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1998-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

因為你是從一張空表開始的，充實它的一個容易方法是創建包含為你的動物各一行一個文本文件，然後用一個單個語句裝載文件的內容到表中。

你可以創建一個文本文件“pet.txt”，每行包含一個記錄，用定位符(tab)把值分開，並且以在CREATE TABLE語句中列出的列次序給出。對於丟失的值(例如未知的性別，或仍然活著的動物的死亡日期)，你可以使用NULL值。為了在你的文本文件表示這些，使用\N。例如，對Whistler鳥的記錄看起來像這樣的(這裡在值之間的空白是一個單個的定位字符)：

Whistler Gwen bird \N 1997-12-09 \N

為了裝載文本文件“pet.txt”到pet表中，使用這個命令：

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

如果你願意，你能明確地在LOAD DATA語句中指出列值的分隔符和行尾標記，但是內定是定位符和換行符。這些對爭取讀入文件“pet.txt”的語句是足夠的。

當你想要一次增加一個新記錄時，INSERT語句是有用的。在它最簡單的形式，你為每一列提供值，以列在CREATE TABLE語句被列出的順序。假定Diane把一只新倉鼠命名為Puffball，你可以使用一個這樣INSERT語句增加一條新記錄：

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

注意，這裡字符串和日期值被指定為引號擴起來的字符串。另外，用INSERT，你能直接插入NULL代表不存在的值。你不能使用\N，就像你用LOAD DATA做的那樣。

從這個例子，你應該能看到涉及很多的鍵入用多個INSERT語句而非單個LOAD DATA語句裝載你的初始記錄。

8.4.4 從一個資料庫表檢索資訊

SELECT語句被用來從一張桌子拉出資訊。語句的一般格式是：

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

what_to_select指出你想要看到的，這可以是列的一張表，或*表明“所有的列”。which_table指出你想要從其檢索數據的表。WHERE子句是可選的，如果它在，conditions_to_satisfy指定行必須滿足的檢索條件。

8.4.4.1 選擇所有數據

SELECT最簡單的形式是從一張表中檢索每樣東西：

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1998-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

如果你想要考察整個表，這種形式的SELECT是很有用的。例如，在你剛剛給它裝載了你的初始數據集裝以後。當它發生時，剛才顯示的輸出揭示了在你的數據文件的一個錯誤：在Bowser死了以後，它好像要出生了！請教你原來的家譜，你發現正確的出生年是1989，而不是1998。

至少有一些修正它的方法：

編輯文件“pet.txt”改正錯誤，然後使用DELETE和LOAD DATA弄空表並且再次裝載它:
```
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
```
然而, 如果你這樣做，你必須重新輸入Puffball記錄。

用一個UPDATE語句僅修正錯誤記錄：

mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

如上所示，檢索整個表是容易的，但是一般你不想那樣做，特別地當表變得很大時。相反，你通常對回答一個特別的問題更感興趣，在這種情況下你在你想要的資訊上指定一些限制。讓我們看一些他們回答有關你寵物的問題的選擇查詢。

8.4.4.2 選擇特定行

你能從你的表刈荿選擇特定的行。例如，如果你想要驗証你對Bowser的出生日期所做的改變，像這樣選取Bowser的記錄：

mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

輸出証實年份現在正確記錄為1989，而不是1998。

字符串比較通常是大小些無關的，因此你可以指定名字為"bowser"、"BOWSER"等等，查詢結果將是相同的。

你能在任何列上指定條件，不只是name。例如，如果你想要知道哪個動物在1998以後出生的，測試birth列：

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

你能組合條件，例如，找出雌性的狗：

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

上面的查詢使用AND邏輯操作符，也有一個OR操作符：

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND和OR可以混用。如果你這樣做，使用括號指明條件應該如何被分組是一個好主意：

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
    -> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

8.4.4.3 選擇特定列

如果你不想要看到你的表的整個行，就命名你感興趣的列，用逗號分開。例如，如果你想要知道你的動物什麼時候出生的，選取name和birth列：

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

找出誰擁有寵物，使用這個查詢：

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

然而，注意到查詢簡單地檢索每個記錄的owner字段，並且他們中的一些出現多次。為了使輸出減到最少，通過增加關鍵詞DISTINCT檢索出每個唯一的輸出記錄：

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

你能使用一個WHERE子句把行選擇與列選擇相結合。例如，為了只得到狗和貓的出生日期，使用這個查詢：

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = "dog" OR species = "cat";
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

8.4.4.4 排序行

你可能已經注意到前面的例子中結果行沒有以特定的次序被顯示。然而，當行以某個有意義的方式排序，檢驗查詢輸出通常是更容易的。為了排序結果，使用一個ORDER BY子句。

這裡是動物生日，按日期排序：

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

為了以逆序排序，增加DESC（下降 )關鍵字到你正在排序的列名上：

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

你能在多個列上排序。例如，按動物的種類排序，然後按生日，首先是動物種類中最年輕的動物，使用下列查詢：

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

注意DESC關鍵詞僅適用於緊跟在它之前的列名字(birth)﹔species值仍然以升序被排序。

8.4.4.5 日期計算

MySQL提供幾個函數，你能用來執行在日期上的計算，例如，計算年齡或提取日期的部分。

為了決定你的每個寵物有多大，用出生日期和當前日期之間的差別計算年齡。通過變換2個日期到天數，取差值，並且用365除(在一年裡的天數)：

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet;
+----------+-------------------------------------+
| name     | (TO_DAYS(NOW())-TO_DAYS(birth))/365 |
+----------+-------------------------------------+
| Fluffy   |                                6.15 |
| Claws    |                                5.04 |
| Buffy    |                                9.88 |
| Fang     |                                8.59 |
| Bowser   |                                9.58 |
| Chirpy   |                                0.55 |
| Whistler |                                1.30 |
| Slim     |                                2.92 |
| Puffball |                                0.00 |
+----------+-------------------------------------+

盡管查詢可行，關於它還有能被改進的一些事情。首先，如果行以某個次序表示，其結果能更容易被掃描。第二，年齡列的標題不是很有意義的。

第一個問題通過增加一個ORDER BY name子句按名字排序輸出來解決。為了處理列標題，為列提供一個名字以便一個不同的標簽出現在輸出中(這被稱為一個列別名)：

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
    -> FROM pet ORDER BY name;
+----------+------+
| name     | age  |
+----------+------+
| Bowser   | 9.58 |
| Buffy    | 9.88 |
| Chirpy   | 0.55 |
| Claws    | 5.04 |
| Fang     | 8.59 |
| Fluffy   | 6.15 |
| Puffball | 0.00 |
| Slim     | 2.92 |
| Whistler | 1.30 |
+----------+------+

為了按age而非name排序輸出，只要使用一個不同ORDER BY子句：

mysql>  SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
    ->  FROM pet ORDER BY age;
+----------+------+
| name     | age  |
+----------+------+
| Puffball | 0.00 |
| Chirpy   | 0.55 |
| Whistler | 1.30 |
| Slim     | 2.92 |
| Claws    | 5.04 |
| Fluffy   | 6.15 |
| Fang     | 8.59 |
| Bowser   | 9.58 |
| Buffy    | 9.88 |
+----------+------+

一個類似的查詢可以被用來確定已經死亡動物的死亡年齡。你通過檢查death值是否是NULL來決定那些是哪些動物，然後，對於那些有非NULL值，計算在death和birth值之間的差別：

mysql>  SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age
    ->  FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
+--------+------------+------------+------+

差詢使用death IS NOT NULL而非death != NULL，因為NULL是特殊的值，這以後會解釋。見8.4.4.6 用NULL值工作。

如果你想要知道哪個動物下個月過生日，怎麼辦？對於這類計算，年和天是無關的，你簡單地想要提取birth列的月份部分。MySQL提供幾個日期部分的提取函數，例如YEAR()、MONTH()和DAYOFMONTH()。在這裡MONTH()是適合的函數。為了看它怎樣工作，運行一個簡單的查詢，顯示birth和MONTH(birth)的值：

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

用下個月的生日找出動物也是容易的。假定當前月是4月，那麼月值是4並且你尋找在5月出生的動物 (5月)，像這樣：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

當然如果當前月份是12月，就有點複雜了。你不是只把加1到月份數(12)上並且尋找在13月出生的動物，因為沒有這樣的月份。相反，你尋找在1月出生的動物(1月) 。

你甚至可以編寫查詢以便不管當前月份是什麼它都能工作。這種方法你不必在查詢中使用一個特定的月份數字，DATE_ADD()允許你把時間間隔加到一個給定的日期。如果你把一個月加到NOW()值上，然後用MONTH()提取月份部分，結果產生尋找生日的月份：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

完成同樣任務的一個不同方法是加1以得出當前月份的下一個月(在使用取模函數(MOD)後，如果它當前是12，則“繞回”月份到值0)：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

注意，MONTH返回在1和12之間的一個數字，且MOD(something,12)返回在0和11之間的一個數字，因此必須在MOD()以後加1，否則我們將從11月( 11 )跳到1月(1)。

8.4.4.6 `NULL`值操作

NULL值可能很奇怪直到你習慣於它。概念上，NULL意味著“沒有值”或“未知值”，且它被看作有點與眾不同的值。為了測試NULL，你不能使用算術比較運算符例如=、<或!=。為了說明它，試試下列查詢：

mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

很清楚你從這些比較中得到毫無意義的結果。相反使用IS NULL和IS NOT NULL操作符：

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

在MySQL中，0意味著假而1意味著真。

NULL這樣特殊的處理是為什麼，在前面的章節中，為了決定哪個動物不再是活著的，使用death IS NOT NULL而不是death != NULL是必要的。

8.4.4.7 模式匹配

MySQL提供標準的SQL模式匹配，以及一種基於像Unix實用程式如vi、grep和sed的擴展正則表達式模式匹配的格式。

SQL的模式匹配允許你使用“_”匹配任何單個字符，而“%”匹配任意數目字符(包括零個字符)。在 MySQL中，SQL的模式內定是忽略大小寫的。下面顯示一些例子。注意在你使用SQL模式時，你不能使用=或!=﹔而使用LIKE或NOT LIKE比較操作符。

為了找出以“b”開頭的名字：

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

為了找出以“fy”結尾的名字：

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

為了找出包含一個“w”的名字：

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

為了找出包含正好5個字符的名字，使用“_”模式字符：

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

由MySQL提供的模式匹配的其他類型是使用擴展正則表達式。當你對這類模式進行匹配測試時，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它們是同義詞)。

擴展正則表達式的一些字符是：

“.”匹配任何單個的字符。
一個字符類“[...]”匹配在方括號內的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。為了命名字符的一個範圍，使用一個“-”。“[a-z]”匹配任何小寫字母，而“[0-9]”匹配任何數字。
“ * ”匹配零個或多個在它前面的東西。例如，“x*”匹配任何數量的“x”字符，“[0-9]*”匹配的任何數量的數字，而“.*”匹配任何數量的任何東西。
正則表達式是區分大小寫的，但是如果你希望，你能使用一個字符類匹配兩種寫法。例如，“[aA]”匹配小寫或大寫的“a”而“[a-zA-Z]”匹配兩種寫法的任何字母。
如果它出現在被測試值的任何地方，模式就匹配(只要他們匹配整個值，SQL模式匹配)。
為了定位一個模式以便它必須匹配被測試值的開始或結尾，在模式開始處使用“^”或在模式的結尾用“$”。

為了說明擴展正則表達式如何工作，上面所示的LIKE查詢在下面使用REGEXP重寫：

為了找出以“b”開頭的名字，使用“^”匹配名字的開始並且“[bB]”匹配小寫或大寫的“b”：

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

為了找出以“fy”結尾的名字，使用“$”匹配名字的結尾：

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

為了找出包含一個“w”的名字，使用“[wW]”匹配小寫或大寫的“w”：

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

既然如果一個正規表達式出現在值的任何地方，其模式匹配了，就不必再先前的查詢中在模式的兩方面放置一個通配符以使得它匹配整個值，就像如果你使用了一個SQL模式那樣。

為了找出包含正好5個字符的名字，使用“^”和“$”匹配名字的開始和結尾，和5個“.”實例在兩者之間：

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

你也可以使用“{n}”“重複n次”操作符重寫先前的查詢：

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

8.4.4.8 行計數

資料庫經常用於回答這個問題，“某個類型的數據在一張表刈艴現的頻度?”例如，你可能想要知道你有多少寵物，或每位主人有多少寵物，或你可能想要在你的動物上施行各種類型的普查。

計算你擁有動物的總數字與“在pet表中有多少行?”是同樣的問題，因為每個寵物有一個記錄。COUNT()函數計數非NULL結果的數目，所以數你的動物的查詢看起來像這樣：

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

在前面，你檢索了擁有寵物的人的名字。如果你想要知道每個主人有多少寵物，你可以使用COUNT()函數：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

注意，使用GROUP BY對每個owner分組所有記錄，沒有它，你得到的一切是一條錯誤消息：

mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT()和GROUP BY對以各種方式分類你的數據很有用。下列例子顯示出實施動物普查操作的不同方式。

每種動物數量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

每中性別的動物數量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

（在這個輸出中，NULL表示“未知性別”。）

按種類和性別組合的動物數量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

當你使用COUNT()時，你不必檢索整個一張表。例如, 先前的查詢，當只在狗和貓上施行時，看起來像這樣：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = "dog" OR species = "cat"
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或，如果你僅需要知道已知性別的按性別的動物數目：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

8.4.5 使用多個資料庫表

pet表追蹤你有哪個寵物。如果你想要記錄他們的其他資訊，例如在他們一生中事件看獸醫或何時後代出生，你需要另外的表。這張表應該像什麼呢？

它需要包含寵物名字因此你知道每個事件屬於此動物。
它需要一個日期因此你知道事件什麼時候發生的。
需要一個字段描述事件。
如果你想要可分類事件，有一個事件類型字段將是有用的。

給出了這些考慮，為event表的CREATE TABLE語句可能看起來像這樣：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

就像pet表，最容易的示通過創建包含資訊的一個定位符分隔的文本文件裝載初始記錄：

Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

像這樣裝載記錄：

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

基於你從已經運行在pet表上的查詢中學到的，你應該能執行在event表中記錄的檢索﹔原則是一樣的。但是什麼時候是event表本身不足以回答你可能問的問題呢？

當他們有了一窩小動物時，假定你想要找出每只寵物的年齡。 event表指出何時發生，但是為了計算母親的年齡，你需要她的出生日期。既然它被儲存在pet表中，為了查詢你需要兩張表：

mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND type = "litter";
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |
| Buffy  | 4.12 | 5 puppies, 2 female, 3 male |
| Buffy  | 5.10 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

關於該查詢要注意的幾件事情：

FROM子句列出兩個表，因為查詢需要從他們兩個拉出資訊。
當組合(聯結-join)來自多個表的資訊時，你需要指定在一個表中的記錄怎樣能匹配其它表的記錄。這很簡單，因為它們都有一個name列。查詢使用WHERE子句基於name值來匹配2個表中的記錄。
因為name列出現在兩個表中，當引用列時，你一定要指定哪個表。這通過把表名附在列名前做到。

你不必有2個不同的表來執行一個聯結。如果你想要將一個表的記錄與同一個表的其他記錄進行比較，聯結一個表到自身有時是有用的。例如，為了在你的寵物之中繁殖配偶，你可以用pet聯結自身來進行相似種類的雄雌配對：

mysql> 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";
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

在這個查詢中，我們為表名指定別名以便能引用列並且使得每一個列引用關聯於哪個表實例更直觀。

8.5 獲得資料庫和表的資訊

如果你忘記一個資料庫或表的名字，或一個給定的表的結構是什麼(例如，它的列叫什麼)，怎麼辦？ MySQL通過提供資料庫及其支援的表的資訊的幾個語句解決這個問題。

你已經見到了SHOW DATABASES，它列出由伺服器管理的資料庫。為了找出當前選擇了哪個資料庫，使用DATABASE()函數：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

如果你還沒選擇任何資料庫，結果是空的。

為了找出當前的資料庫包含什麼表(例如，當你不能確定一個表的名字)，使用這個命令：

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

如果你想要知道一個表的結構，DESCRIBE命令是有很用的﹔它顯示有關一個表的每個列的資訊：

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field顯示列名字，Type是為列的數據類型，Null表示列是否能包含NULL值，Key顯示列是否被索引而Default指定列的內定值。

如果你在一個表上有索引，SHOW INDEX FROM tbl_name產生有關它們的資訊。

8.6 以批處理模式使用`mysql`

在前面的章節中，你交互式地使用mysql輸入查詢並且查看結果。你也可以以批模式運行mysql。為了做到這些，把你想要運行的命令放在一個文件中，然後告訴mysql從文件讀取它的輸入：

shell> mysql < batch-file

如果你需要在命令行上指定連接參數，命令可能看起來像這樣：

shell> mysql -h host -u user -p < batch-file
Enter password: ********

當你這樣使用mysql時，你正在創建一個腳本文件，然後執行腳本。

為什麼要使用一個腳本？有很多原因：

如果你重複地運行查詢(比如說，每天或每周)，把它做成一個腳本使得你在每次執行它時避免重新鍵入。
你能通過拷貝並編輯腳本文件從類似的現有的查詢產生一個新查詢。
當你正在開發查詢時，批模式也是很有用的，特別對多行命令或多行語句序列。如果你犯了一個錯誤，你不必重新打入所有一切，只要編輯你的腳本來改正錯誤，然後告訴mysql再次執行它。
如果你有一個產生很多輸出的查詢，你可以通過一個分頁器而不是盯著它翻屏到你屏幕的頂端來運行輸出：
```
shell> mysql < batch-file | more
```
你能捕捉輸出到一個文件中進行更一步的處理：
```
shell> mysql < batch-file > mysql.out
```
你可以散發腳本給另外的人，因此他們也能運行命令。
一些情況不允許交互地使用，例如, 當你從一個cron任務中運行查詢時。在這種情況下，你必須使用批模式。

當你以批模式運行mysql時，比起你交互地使用它時，其內定輸出格式是不同的(更簡明些)。例如，當交互式運行SELECT DISTINCT species FROM pet時，輸出看起來像這樣：

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

但是當以批模式運行時，像這樣：

species
bird
cat
dog
hamster
snake

如果你想要在批模式中得到交互的輸出格式，使用mysql -t。為了回顯以輸出被執行的命令，使用mysql -vvv。

8.7 雙胞胎項目的查詢（實例）

在Analytikerna 和 Lentus，我們為一個大的研究項目工程一直在做系統和現場工作。這個項目是Institute of Environmental Medicine at Karolinska Institutet Stockholm 和 the Section on Clinical Research in Aging and Psychology at the University of Southern California的合作項目。

雙胞胎研究的更多資訊可在下列鏈接找到：

http://www.imm.ki.se/TWIN/TWINUKW.HTM

項目的後面部分是用一個用Perl和MySQL編寫的web介面來管理。

每天晚上所有會談的數據被移入一個MySQL資料庫。

8.7.1 找出所有非獨處的雙胞胎

下列查詢用來決定誰進入項目的第二部分：

select
        concat(p1.id, p1.tvab) + 0 as tvid,
        concat(p1.christian_name, " ", p1.surname) as Name,
        p1.postal_code as Code,
        p1.city as City,
        pg.abrev as Area,
        if(td.participation = "Aborted", "A", " ") as A,
        p1.dead as dead1,
        l.event as event1,
        td.suspect as tsuspect1,
        id.suspect as isuspect1,
        td.severe as tsevere1,
        id.severe as isevere1,
        p2.dead as dead2,
        l2.event as event2,
        h2.nurse as nurse2,
        h2.doctor as doctor2,
        td2.suspect as tsuspect2,
        id2.suspect as isuspect2,
        td2.severe as tsevere2,
        id2.severe as isevere2,
        l.finish_date
from
        twin_project as tp
        /* For Twin 1 */
        left join twin_data as td on tp.id = td.id and tp.tvab = td.tvab
        left join informant_data as id on tp.id = id.id and tp.tvab = id.tvab
        left join harmony as h on tp.id = h.id and tp.tvab = h.tvab
        left join lentus as l on tp.id = l.id and tp.tvab = l.tvab
        /* For Twin 2 */
        left join twin_data as td2 on p2.id = td2.id and p2.tvab = td2.tvab
        left join informant_data as id2 on p2.id = id2.id and p2.tvab = id2.tvab
        left join harmony as h2 on p2.id = h2.id and p2.tvab = h2.tvab
        left join lentus as l2 on p2.id = l2.id and p2.tvab = l2.tvab,
        person_data as p1,
        person_data as p2,
        postal_groups as pg
where
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id and p1.tvab = tp.tvab and
        p2.id = p1.id and p2.ptvab = p1.tvab and
        /* Just the sceening survey */
        tp.survey_no = 5 and
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 or p2.dead = 9 or
         (p2.dead = 1 and
          (p2.death_date = 0 or
           (((to_days(p2.death_date) - to_days(p2.birthday)) / 365)
            >= 65))))
        and
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' and td.suspect = 2) or
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' and td.suspect = 1 and id.suspect = 1) or
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) and id.suspect = 1 and id.future_contact = 'Yes') or
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         and id.suspect = 1 and id.future_contact = 'Yes') or
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' and ISNULL(id.suspect) and p2.dead = 0))
        and
        l.event = 'Finished'
        /* Get at area code */
        and substring(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        and (h.nurse is NULL or h.nurse=00 or h.doctor=00)
        /* Has not refused or been aborted */
        and not (h.status = 'Refused' or h.status = 'Aborted'
        or h.status = 'Died' or h.status = 'Other')
order by
        tvid;

一些解釋：

concat(p1.id, p1.tvab) + 0 as tvid: 我們想要在id和tvab的連接上以數字序排序。結果加0使得MySQL把結果當作一個數字。
列id: 這標識一對雙胞胎。它是所有表中的一個鍵。
列tvab: 這標識雙胞胎中的一個。它有值1或2。
列ptvab: 這是tvab一個逆。當tvab是1，它是2，並且反過來也如此。它存在以保存鍵入並且使它更容易為MySQL最佳化查詢。

這個查詢表明，怎樣用聯結(p1和p2)從同一個表中查找表。在例子中，這被用來檢查雙胞胎的一個是否在65歲前死了。如果因此，行不返回。

上述所有雙胞胎資訊存在於所有表中。我們在id,tvab兩者上的鍵值（所有表)和在id,ptvab上的鍵(person_data)以使查詢更快。

在我們的生產機器上(一台200MHz UltraSPARC)，這個查詢返回大約 150-200 行並且不超過一秒的時間。

上面所用的表的當前記錄數是：

表	行數
`person_data`	71074
`lentus`	5291
`twin_project`	5286
`twin_data`	2012
`informant_data`	663
`harmony`	381
`postal_groups`	100

8.7.2 顯示關於雙胞胎近況的表

每一次會面以一個稱為event的狀態碼結束。下面顯示的查詢被用來顯示按事件組合的所有雙胞胎的表。這表明多少對雙胞胎已經完成，多少對的其中之一已完成而另一個拒絕了，等等。

select
        t1.event,
        t2.event,
        count(*)
from
        lentus as t1,
        lentus as t2,
        twin_project as tp
where
        /* We are looking at one pair at a time */
        t1.id = tp.id
        and t1.tvab=tp.tvab
        and t1.id = t2.id
        /* Just the sceening survey */
        and tp.survey_no = 5
        /* This makes each pair only appear once */
        and t1.tvab='1' and t2.tvab='2'
group by
        t1.event, t2.event;

到第一章, 前一章, 下一章, 最後一章，目錄.