mysql> CREATE DATABASE menagerie;

mysql> USE menagerie

Database changed

shell> mysql -h host -u user -p menagerie

Enter password: ********

建立資料庫是很容易的部分，但是在這時它是空的，正如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，例如，如果您忘記資料表中的列的名稱或類型時。

建立資料表後，需要填入內容。通過LOAD DATA和INSERT語句可以完成該任務。

假定您的寵物紀錄描述如下。（假定在MySQL中期望的日期格式是YYYY-MM-DD；這可能與您習慣的不同。）

因為您是從一個空資料表開始的，填充它的一個簡易方法是建立一個文本檔案，每個動物各一行，然後用一個語句將檔案的內容裝載到資料表中。

您可以建立一個文本檔案「pet.txt」，每行包含一個記錄，用定位符(tab)把值分開，並且以CREATE TABLE語句中列出的列次序給出。對於丟失的值(例如未知的性別，或仍然活著的動物的死亡日期)，您可以使用NULL值。為了在您的文本檔案中資料表示這些內容，使用\N（反斜線，字母N）。例如，Whistler鳥的記錄應為(這裡值之間的空白是一個定位符)：

要想將文本檔案「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語句裝載您的初始記錄。

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

SELECT MAX(article) AS article FROM shop;

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

SELECT article, dealer, price

FROM   shop

WHERE  price=(SELECT MAX(price) FROM shop);

SELECT article, dealer, price

FROM shop

ORDER BY price DESC

LIMIT 1;

任務：每項物品的的最高價格是多少？

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

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

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

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

您可以清空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 |
+---------+--------+-------+

在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-shirt', 'white', @last);

SELECT * FROM person;

+----+---------------------+

| id | name                |

+----+---------------------+

|  1 | Antonio Paz         |

|  2 | Lilliana Angelovska |

+----+---------------------+

SELECT * FROM shirt;

+----+---------+--------+-------+

| 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 person p, shirt s

 WHERE p.name LIKE 'Lilliana%'

   AND s.owner = p.id

   AND s.color <> 'white';

+----+-------+--------+-------+

| id | style | color  | owner |

+----+-------+--------+-------+

|  4 | dress | orange |     2 |

|  5 | polo  | red    |     2 |

|  6 | dress | blue   |     2 |

+----+-------+--------+-------+

按照這種方式使用，REFERENCES子句不會顯示在SHOW CREATE TABLE或DESCRIBE的輸出中:

SHOW CREATE TABLE shirt\G

*************************** 1. row ***************************

Table: shirt

Create Table: CREATE TABLE `shirt` (

`id` smallint(5) unsigned NOT NULL auto_increment,

`style` enum('t-shirt','polo','dress') NOT NULL,

`color` enum('red','blue','orange','white','black') NOT NULL,

`owner` smallint(5) unsigned NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

在列定義中，按這種方式使用REFERENCES作為註釋或「提示」適用於資料表MyISAM和BerkeleyDB。

可以充分利用使用單關鍵字的OR子句，如同AND的處理。

一個比較靈活的例子是尋找兩個通過OR組合到一起的關鍵字：

SELECT field1_index, field2_index FROM test_table

WHERE field1_index = '1' OR  field2_index = '1'

該情形是已經最佳化過的。參見7.2.6節，「索引合併最佳化」。

還可以使用UNION將兩個單獨的SELECT語句的輸出合成到一起來更有效地解決該問題。參見13.2.7.2節，「UNION語法
」。

每個SELECT只搜索一個關鍵字，可以進行最佳化：

SELECT field1_index, field2_index

    FROM test_table WHERE field1_index = '1'

UNION

SELECT field1_index, field2_index

    FROM test_table WHERE field2_index = '1';

下面的例子顯示了如何使用位組函數來計算每個月中用戶訪問網頁的天數。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

             day INT(2) UNSIGNED ZEROFILL);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

            (2000,2,23),(2000,2,23);

示範資料表中含有代資料表用戶訪問網頁的年－月－日值。可以使用以下查詢來確定每個月的訪問天數：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1

       GROUP BY year,month;

將返回：

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2000 |    01 |    3 |

| 2000 |    02 |    2 |

+------+-------+------+

該查詢計算了在資料表中按年/月組合的不同天數，可以自動去除重複的詢問。

可以通過AUTO_INCREMENT屬性為新的行產生唯一的標識：

CREATE TABLE animals (

     id MEDIUMINT NOT NULL AUTO_INCREMENT,

     name CHAR(30) NOT NULL,

     PRIMARY KEY (id)

 );

INSERT INTO animals (name) VALUES 

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

將返回：

+----+---------+

| id | name    |

+----+---------+

|  1 | dog     |

|  2 | cat     |

|  3 | penguin |

|  4 | lax     |

|  5 | whale   |

|  6 | ostrich |

+----+---------+

您可以使用LAST_INSERT_ID()SQL函數或mysql_insert_id() C API函數來查詢最新的AUTO_INCREMENT值。這些函數與具體連接有關，因此其返回值不會被其它執行插入功能的連接影響。

註釋：對於多行插入，LAST_INSERT_ID()和mysql_insert_id()從插入的第一行實際返回AUTO_INCREMENT關鍵字。在複製設置中，通過該函數可以在其它伺服器上正確複製多行插入。

對於MyISAM和BDB資料表，您可以在第二欄指定AUTO_INCREMENT以及多列索引。此時，AUTO_INCREMENT列生成的值的計算方法為：MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要將數據放入到排序的組中可以使用該方法。

CREATE TABLE animals (

    grp ENUM('fish','mammal','bird') NOT NULL,

    id MEDIUMINT NOT NULL AUTO_INCREMENT,

    name CHAR(30) NOT NULL,

    PRIMARY KEY (grp,id)

);

INSERT INTO animals (grp,name) VALUES 

    ('mammal','dog'),('mammal','cat'),

    ('bird','penguin'),('fish','lax'),('mammal','whale'),

    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

將返回：

+--------+----+---------+

| grp    | id | name    |

+--------+----+---------+

| fish   |  1 | lax     |

| mammal |  1 | dog     |

| mammal |  2 | cat     |

| mammal |  3 | whale   |

| bird   |  1 | penguin |

| bird   |  2 | ostrich |

+--------+----+---------+

請注意在這種情況下（AUTO_INCREMENT列是多列索引的一部分），如果您在任何組中刪除有最大AUTO_INCREMENT值的行，將會重新用到AUTO_INCREMENT值。對於MyISAM資料表也如此,對於該資料表一般不重複使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL將使用該索引生成以AUTO_INCREMENT列開始的序列值。。例如，如果animals資料表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值時將忽略PRIMARY KEY。結果是，該資料表包含一個單個的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值開始而不是1，您可以通過CREATE TABLE或ALTER TABLE來設置該值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

關於AUTO_INCREMENT的詳細訊息：

·         如何為列指定AUTO_INCREMENT屬性：13.1.5節，「CREATE TABLE語法」和 13.1.2節，「ALTER TABLE語法」。

·         AUTO_INCREMENT的動作取決於SQL模式：5.3.2節，「SQL伺服器模式」。

·         找出含有最新AUTO_INCREMENT值的行：12.1.3節，「比較函數和操作符」。

·         設置將用到的AUTO_INCREMENT值: 13.5.3節，「SET語法」 。

·         AUTO_INCREMENT和複製：6.7節，「複製特性和已知問題」.

·         AUTO_INCREMENT相關的可用於複製的Server-system變數(auto_increment_increment和auto_increment_offset)：5.3.3節，「伺服器系統變數」。

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

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 screening 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 行並且時間不超過一秒。