第3章：教程

目錄

3.1. 連接與中斷伺服器
3.2. 輸入查詢
3.3. 建立並使用資料庫
3.3.1. 建立並選擇資料庫
3.3.2. 建立資料表
3.3.3. 將數據裝入資料表中
3.3.4. 從資料表檢索訊息
3.4. 獲得資料庫和資料表的訊息
3.5. 在批處理模式下使用mysql
3.6. 常用查詢的例子
3.6.1. 列的最大值
3.6.2. 擁有某個列的最大值的行
3.6.3. 列的最大值：按組
3.6.4. 擁有某個字段的組間最大值的行
3.6.5. 使用用戶變數
3.6.6. 使用外部鍵
3.6.7. 根據兩個鍵搜索
3.6.8. 根據天計算訪問量
3.6.9. 使用AUTO_INCREMENT
3.7. 孿生項目的查詢
3.7.1. 搜尋所有未分發的孿生項
3.7.2. 顯示孿生對狀態的資料表
3.8. 與Apache一起使用MySQL

本章通過演示如何使用mysql客戶程式創造和使用一個簡單的資料庫，提供一個MySQL的入門教程。mysql（有時稱為「終端監視器」或只是「監視」）是一個交互式程式，允許您連接一個MySQL伺服器，運行查詢並察看結果。mysql可以用於批模式：您預先把查詢放在一個檔案中，然後告訴mysql執行檔案的內容。本章將介紹使用mysql的兩個方法。

要想查看由mysql提供的選擇項目資料表，可以用--help選項來使用：

shell> mysql --help

本章假定mysql已經被安裝在您的機器上，並且有一個MySQL伺服器可以連接。否則，請聯絡MySQL管理員。（如果您是管理員，則需要查閱本手冊的其它章節，例如第5章：資料庫管理。）

本章描述建立和使用一個資料庫的全過程。如果您僅僅對訪問一個已經存在的資料庫感興趣，可以跳過描述怎樣建立資料庫及它所包含的資料表的章節。

由於本章是一個教程，省略了許多細節。關於這裡所涉及的主題的詳細訊息，請查閱本手冊的相關章節。

3.1. 連接與中斷伺服器

為了連接伺服器，當使用mysql時，通常需要提供一個MySQL帳號並且很可能需要一個 密碼。如果伺服器運行在登錄伺服器之外的其它機器上，還需要指定主機名。聯繫管理員以找出進行連接所使用的參數 (即，連接的主機、帳號和使用的密碼)。知道正確的參數後，可以按照以下方式進行連接：
shell> mysql -h host -u user -p
Enter password: ********

host和user分別代資料表MySQL伺服器運行的主機名和MySQL帳號帳號。設置時替換為正確的值。******** 代資料表您的密碼；當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 25338 to server version: 5.1.2-alpha-standard
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

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

一些MySQL安裝允許用戶以匿名（未命名）用戶連接到本地主機上運行的伺服器。如果您的機器是這種情況，您應該能不帶任何選項地使用mysql與該伺服器連接：

shell> mysql

成功地連接後，可以在mysql>提示下輸入QUIT (或\q)隨時退出：

mysql> QUIT
Bye

在Unix中，也可以按control-D鍵中斷伺服器。

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

3.2. 輸入查詢

確保您連接上了伺服器，如在先前的章節討論的。連接上伺服器並布代資料表選擇了任何資料庫，但這樣就可以了。知道關於如何查詢的基本知識，比馬上跳至建立資料表、給他們裝載數據並且從他們檢索數據更重要。本節描述輸入命令的基本原則，使用幾個查詢，您能嘗試瞭解mysql是如何工作的。

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

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------------+--------------+
| VERSION()       | CURRENT_DATE |
+-----------------+--------------+
| 5.1.2-alpha-log | 2005-10-11   |
+-----------------+--------------+
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.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)

至此顯示的命令是相當短的單行語句。您可以在一行上輸入多條語句，只需要以一個分號間隔開各語句：

mysql> SELECT VERSION(); SELECT NOW();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.2-alpha-log |
+-----------------+
1 row in set (0.00 sec)
 
+---------------------+
| NOW()               |
+---------------------+
| 2005-10-11 15:15:00 |
+---------------------+
1 row in set (0.00 sec)

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

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

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+---------------+--------------+
| USER()        | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-10-11   |
+---------------+--------------+

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

如果您決定不想執行正在輸入過程中的一個命令，輸入\c取消它：

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

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

下資料表顯示出可以看見的各個提示符並簡述它們所資料表示的mysql的狀態：

提示符

含義

mysql>

準備好接受新命令。

->

等待多行命令的下一行。

'>

等待下一行，等待以單引號(「'」)開始的字串的結束。

">

等待下一行，等待以雙引號(「"」)開始的字串的結束。

`>

等待下一行，等待以反斜點(『`』)開始的識別符的結束。

/*>

等待下一行，等待以/*開始的註釋的結束。

當您打算在一個單行上發出一個命令時，通常會「偶然」出現多行語句，但是沒有終止分號。在這種情況中，mysql等待進一步輸入：

mysql> SELECT USER()
    ->

如果出現這種情況(您認為輸完了語句，但是只有一個->提示符響應)，很可能mysql正在等待分號。如果您沒有注意到提示符的提示，在意識到您需要做什麼之前，您可能會呆坐一會兒。輸入一個分號完成語句，mysql將執行：

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

在字串收集期間將出現 '> 和 "> 提示符（提示MySQL正等待字串的結束）。在MySQL中，可以寫由『'』或『"』字元括起來的字串 (例如，'hello'或"goodbye")，並且mysql允許輸入跨越多行的字串。當看到一個 '> 或 "> 提示符時，這意味著已經輸入了包含以『'』或『"』括號字元開始的字串的一行，但是還沒有輸入終止字串的匹配引號。這顯示您粗心地省掉了一個引號字元。例如：

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

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

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

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

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

`> 提示符類似於 '> 和"> 提示符，但資料表示您已經開始但沒有結束以`> 開始的識別符。

知道'>和">提示符的含義很重要，因為如果您錯誤地輸入一個未終止的字串，任何後面輸入的行將要被mysql忽略--包括包含QUIT的行！這可能令人相當困惑，特別是如果取消當前命令前還不知道您需要提供終止引號。

3.3. 建立並使用資料庫

3.3.1. 建立並選擇資料庫
3.3.2. 建立資料表
3.3.3. 將數據裝入資料表中
3.3.4. 從資料表檢索訊息

知道怎樣輸入命令，便可以訪問資料庫了。

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

·         建立資料庫

·         建立資料庫資料表

·         裝載數據到資料庫資料表

·         以各種方法從資料表中檢索數據

·         使用多個資料表

動物園資料庫很簡單(特意的)，但是不難把它想像成可能用到類似資料庫的真實世界情況。例如，農夫可以使用這樣的一個資料庫來追蹤家畜，或者獸醫可以用它跟蹤病畜記錄。從MySQL網址上可以獲得後面章節中將用到的含有部分查詢和樣例數據的動物園分發。有tar壓縮格式 (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz)和Zip壓縮格式 (http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip)。

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

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

可能您的機器上的資料庫列資料表是不同的，但是很可能有mysql和test資料庫。mysql是必需的，因為它描述用戶訪問權限，test資料庫經常作為用戶試身手的工作區。

請注意如果沒有SHOW DATABASES權限，則不能看見所有資料庫。參見13.5.1.3節，「GRANT和REVOKE語法」。

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

mysql> USE test
Database changed

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

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

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

這裡your_mysql_name是分配給您的MySQL帳號，your_client_host是所連接的伺服器所在的主機。

3.3.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)。但是，不建議在命令行輸入密碼，因為這樣會暴露 密碼，能被在機器上登錄的其它用戶窺探到。

3.3.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到65535的任何長度，從中選擇一個最合理的值。（如果選擇得不合適，後來證明您需要一個更長的字段，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，例如，如果您忘記資料表中的列的名稱或類型時。

3.3.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

1979-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（反斜線，字母N）。例如，Whistler鳥的記錄應為(這裡值之間的空白是一個定位符)：

name

owner

species

sex

birth

death

Whistler

Gwen

bird

\N

1997-12-09

\N

要想將文本檔案「pet.txt」裝載到pet資料表中，使用這個命令：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

請注意如果用Windows中的編輯器（使用\r\n做為行的結束符）建立檔案，應使用：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

（在運行OS X的Apple機上，應使用行結束符'\r'。）

如果您願意，您能明確地在LOAD DATA語句中指出列值的分隔符和行尾標記，但是預設標記是定位符和換行符。這對讀入檔案「pet.txt」的語句已經足夠。

如果該語句失敗，可能是您安裝的MySQL不與使用預設值的本地檔案兼容。關於如何更改請參見5.6.4節，「LOAD DATA LOCAL安全問題」。

如果想要一次增加一個新記錄，可以使用INSERT語句。最簡單的形式是，提供每一列的值，其順序與CREATE TABLE語句中列的順序相同。假定Diane把一隻新倉鼠命名為Puffball，您可以使用下面的INSERT語句新增一條新記錄：

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

注意，這裡字串和日期值均為引號擴起來的字串。另外，可以直接用INSERT語句插入NULL代資料表不存在的值。不能使用LOAD DATA中所示的的\N。

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

3.3.4. 從資料表檢索訊息

3.3.4.1. 選擇所有數據
3.3.4.2. 選擇特殊行
3.3.4.3. 選擇特殊列
3.3.4.4. 分類行
3.3.4.5. 日期計算
3.3.4.6. NULL值操作
3.3.4.7. 模式匹配
3.3.4.8. 計數行
3.3.4.9. 使用1個以上的資料表
 
SELECT語句用來從數據資料表中檢索訊息。語句的一般格式是：
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select指出您想要看到的內容，可以是列的一個資料表，或*資料表示「所有的列」。which_table指出您想要從其檢索數據的資料表。WHERE子句是可選項，如果選擇該項，conditions_to_satisfy指定行必須滿足的檢索條件。

3.3.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    | 1979-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，而不是1979。

至少有兩種修正方法：

·         編輯檔案「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';

UPDATE只更改有問題的記錄，不需要重新裝載資料庫資料表。

3.3.4.2. 選擇特殊行

如上所示，檢索整個資料表是容易的。只需要從SELECT語句中刪掉WHERE子句。但是一般您不想看到整個資料表，特別地當資料表變得很大時。相反，您通常對回答一個具體的問題更感興趣，在這種情況下在您想要的訊息上進行一些限制。讓我們看一些他們回答的有關您寵物的問題的選擇查詢。

可以從資料表中只選擇特定的行。例如，如果您想要驗證您對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，而不是1979。

字串比較時通常對大小些不敏感，因此您可以將名字指定為"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可以混用，但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  |
+-------+--------+---------+------+------------+-------+

3.3.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 |
+--------+---------+------------+

3.3.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 |
+----------+------------+

在字元類型列上，與所有其他比較操作類似，分類功能正常情況下是以區分大小寫的方式執行的。這意味著，對於等同但大小寫不同的列，並未定義其順序。對於某一列，可以使用BINARY強制執行區分大小寫的分類功能，如：ORDER BY BINARY col_name.

預設排序是升序，最小的值在第一。要想以降序排序，在您正在排序的列名上增加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列的排序順序。

3.3.4.5. 日期計算

MySQL提供了幾個函數，可以用來計算日期，例如，計算年齡或提取日期部分。

要想確定每個寵物有多大，可以計算當前日期的年和出生日期之間的差。如果當前日期的日曆年比出生日期早，則減去一年。以下查詢顯示了每個寵物的出生日期、當前日期和年齡數值的年數字。

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

此處，YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日曆年)部分的最右面5個字元。比較MM-DD值的資料表達式部分的值一般為1或0，如果CURDATE()的年比birth的年早，則年份應減去1。整個資料表達式有些難懂，使用alias (age)來使輸出的列標記更有意義。

儘管查詢可行，如果以某個順序排列行，則能更容易地瀏覽結果。新增ORDER BY name子句按照名字對輸出進行排序則能夠實現。

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

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

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

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

mysql> 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;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

查詢使用death IS NOT NULL而非death != NULL，因為NULL是特殊的值，不能使用普通比較符來比較，以後會給出解釋。參見3.3.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(CURDATE(),INTERVAL 1 MONTH));

完成該任務的另一個方法是加1以得出當前月份的下一個月(在使用取模函數(MOD)後，如果月份當前值是12，則「回滾」到值0)：

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

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

3.3.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或 NULL意味著假而其它值意味著真。布爾運算的預設真值是1。

對NULL的特殊處理即是在前面的章節中，為了決定哪個動物不再是活著的，使用death IS NOT NULL而不使用death != NULL的原因。

在GROUP BY中，兩個NULL值視為相同。

執行ORDER BY時，如果運行 ORDER BY ... ASC，則NULL值出現在最前面，若運行ORDER BY ... DESC，則NULL值出現在最後面。

NULL操作的常見錯誤是不能在定義為NOT NULL的列內插入0或空字串，但事實並非如此。在NULL資料表示"沒有數值"的地方有數值。使用IS [NOT] NULL則可以很容易地進行測試，如下所示：

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

因此完全可以在定義為NOT NULL的列內插入0或空字串，實際是NOT NULL。參見A.5.3節，「與NULL值有關的問題」。

3.3.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]*」匹配任何數量的數字，而「.*」匹配任何數量的任何字元。

  • 如果REGEXP模式與被測試值的任何地方匹配，模式就匹配(這不同於LIKE模式匹配，只有與整個值匹配，模式才匹配)。
  • 為了定位一個模式以便它必須匹配被測試值的開始或結尾，在模式開始處使用「^」或在模式的結尾用「$」。

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

為了找出以「b」開頭的名字，使用「^」匹配名字的開始：

mysql> SELECT * FROM pet WHERE name REGEXP '^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 |
+--------+--------+---------+------+------------+------------+

如果您想強制使REGEXP比較區分大小寫，使用BINARY關鍵字使其中一個字串變為二進制字串。該查詢只匹配名稱首字母的小寫『b』。

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

為了找出以「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」的名字，使用以下查詢：

mysql> SELECT * FROM pet WHERE name REGEXP '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       |
+----------+-------+---------+------+------------+------------+

既然如果一個正則資料表達式出現在值的任何地方，其模式匹配了，就不必在先前的查詢中在模式的兩側放置一個通配符以使得它匹配整個值，就像您使用了一個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  |
+-------+--------+---------+------+------------+-------+

附錄G：MySQL正則資料表達式 提供了關於正則資料表達式的句法的詳細訊息。

3.3.4.8. 計數行

資料庫經常用於回答這個問題，「某個類型的數據在資料表中出現的頻度?」例如，您可能想要知道您有多少寵物，或每位主人有多少寵物，或您可能想要對您的動物進行各種類型的普查。

計算您擁有動物的總數目與「在pet資料表中有多少行?」是同樣的問題，因為每個寵物有一個記錄。COUNT(*)函數計算行數，所以計算動物數目的查詢應為：

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(*) FROM pet;
ERROR 1140 (42000): 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 |
+---------+------+----------+

3.3.4.9. 使用1個以上的資料表
 

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

·         它需要包含寵物名字以便您知道每個事件屬於哪個動物。

·         需要一個日期以便您知道事件是什麼時候發生的。

·         需要一個描述事件的字段。

·         如果您想要對事件進行分類，則需要一個事件類型字段。

綜合上述因素，event資料表的CREATE TABLE語句應為：

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

對於pet資料表，最容易的方法是建立包含訊息的用定位符分隔的文本檔案來裝載初始記錄：

name

date

type

remark

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,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

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

  • FROM子句列出兩個資料表，因為查詢需要從兩個資料表提取訊息。
  • 當從多個資料表組合(聯結)訊息時，您需要指定一個資料表中的記錄怎樣能匹配其它資料表的記錄。這很簡單，因為它們都有一個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     |
+--------+------+--------+------+---------+

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

3.4. 獲得資料庫和資料表的訊息

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

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

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

如果您還沒選擇任何資料庫，結果是NULL。

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

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生成有關索引的訊息。

3.5. 在批處理模式下使用mysql

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

如果在Windows下運行mysql，並且檔案中有一些可以造成問題的特殊字元，可以這樣操作：

C:\> mysql -e "source batch-file"

如果您需要在命令行上指定連接參數，命令應為：

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

當這樣操作mysql時，則建立一個指令檔案，然後執行指令。

如果您想在語句出現錯誤的時候仍想繼續執行指令，則應使用--force命令行選項。

為什麼要使用一個指令？有很多原因：

  • 如果您需要重複運行查詢(比如說，每天或每週)，可以把它編成一個指令，則每次執行時不必重新鍵入。
  • 可以通過拷貝並編輯指令檔案從類似的現有的查詢生成一個新查詢。
  • 當您正在開發查詢時，批模式也是很有用的，特別對多行命令或多語句命令序列。如果您犯了一個錯誤，您不必重新輸入所有內容，只需要編輯指令來改正錯誤，然後告訴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。

您還可以使用源代碼或 \.命令從mysql提示符運行指令：

mysql> source filename;
mysql> \. filename

3.6. 常用查詢的例子

3.6.1. 列的最大值
3.6.2. 擁有某個列的最大值的行
3.6.3. 列的最大值：按組
3.6.4. 擁有某個字段的組間最大值的行
3.6.5. 使用用戶變數
3.6.6. 使用外部鍵
3.6.7. 根據兩個鍵搜索
3.6.8. 根據天計算訪問量
3.6.9. 使用AUTO_INCREMENT

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

在一些例子中，使用資料庫資料表「shop」來儲存某個商人（經銷商）的每件物品(物品號)的價格。假定每個商人對每項物品有一個固定價格，那麼(物品，商人)即為該記錄的主關鍵字。

啟動命令行工具mysql並選擇資料庫：

shell> mysql your-database-name

（在大多數MySQL中，您可以使用test資料庫）。

您可以使用以下語句建立示範資料表：

mysql> 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));
mysql> 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);

執行語句後，資料表應包含以下內容：

mysql> 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 |
+---------+--------+-------+

3.6.1. 列的最大值

「最大的物品號是什麼？」

SELECT MAX(article) AS article FROM shop;

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

3.6.2. 擁有某個列的最大值的行

任務：找出最貴物品的編號、銷售商和價格。

這很容易用一個子查詢做到：

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

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

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

注:如果有多項最貴的物品( 例如每個的價格為19.95)，LIMIT解決方案僅僅顯示其中一個！

3.6.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 |
+---------+-------+

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

任務：對每項物品，找出最貴價格的物品的經銷商。

可以用這樣一個子查詢解決該問題：

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

3.6.5. 使用用戶變數

您可以清空MySQL用戶變數以記錄結果，不必將它們保存到客戶端的臨時變數中。（參見 9.3節，「用戶變數」.）。

例如，要找出價格最高或最低的物品的，其方法是：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.6. 使用外部鍵

在MySQL中，InnoDB資料表支援對外部關鍵字約束條件的檢查。參見15.2節，「InnoDB儲存引擎」。還可以參見 1.8.5.5節，「外部鍵」。

只是聯接兩個資料表時，不需要外部關鍵字。對於除InnoDB類型的資料表，當使用REFERENCES tbl_name(col_name)子句定義列時可以使用外部關鍵字，該子句沒有實際的效果，只作為備忘錄或註釋來提醒，您目前正定義的列指向另一個資料表中的一個列。執行該語句時，實現下面很重要：

·         MySQL不執行資料表tbl_name 中的動作，例如作為您正定義的資料表中的行的動作的響應而刪除行；換句話說，該句法不會致使ON DELETE或ON UPDATE行為（如果您在REFERENCES子句中寫入ON DELETE或ON UPDATE子句，將被忽略）。

·         該句法可以建立一個column；但不建立任何索引或關鍵字。

·         如果用該句法定義InnoDB資料表，將會導致錯誤。

您可以使用作為聯接列建立的列，如下所示：

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE shirt (
    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 person(id),
    PRIMARY KEY (id)
);
 
INSERT INTO person VALUES (NULL, 'Antonio Paz');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
 
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
 
SELECT @last := LAST_INSERT_ID();
 
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't