小默

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 行并且时间不超过一秒。