8 MySQL教程

119011090

  本章通过演示如何使用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准备好接受一个新命令了。

知道&#39;>和">提示符意味着什么是很重要的，因为如果你错误地输入一个未终止的字符串，任何比你下一步输入的行好象将要被mysql忽略--包括包含QUIT的行！这可能相当含糊，特别是在你能取消当前命令前，如果你不知道你需要提出终止引号。

8.3 常用查询的例子
下面是一些学习如何用MySQL解决一些常见问题的例子。

一些例子使用数据库表“shop”，包含某个商人的每篇文章(物品号)的价格。假定每个商人的每篇文章有一个单独的固定价格，那么(物品，商人)是记录的主键。

你能这样创建例子数据库表：

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

INSERT INTO shop VALUES
(1,&#39;A&#39;,3.45),(1,&#39;B&#39;,3.99),(2,&#39;A&#39;,10.99),(3,&#39;B&#39;,1.45),(3,&#39;C&#39;,1.69),
(3,&#39;D&#39;,1.25),(4,&#39;D&#39;,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 &#39;0000&#39; NOT NULL,
      price  DOUBLE(16,2)         DEFAULT &#39;0.00&#39; 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,&#39;0&#39;),dealer) ), 7) AS dealer,
  0.00+LEFT(    MAX( CONCAT(LPAD(price,6,&#39;0&#39;),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(&#39;t-shirt&#39;, &#39;polo&#39;, &#39;dress&#39;) NOT NULL,
   color ENUM(&#39;red&#39;, &#39;blue&#39;, &#39;orange&#39;, &#39;white&#39;, &#39;black&#39;) NOT NULL,
   owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
   PRIMARY KEY (id)
);

INSERT INTO persons VALUES (NULL, &#39;Antonio Paz&#39;);

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

INSERT INTO persons VALUES (NULL, &#39;Lilliana Angelovska&#39;);

INSERT INTO shirts VALUES
(NULL, &#39;dress&#39;, &#39;orange&#39;, LAST_INSERT_ID()),
(NULL, &#39;polo&#39;, &#39;red&#39;, LAST_INSERT_ID()),
(NULL, &#39;dress&#39;, &#39;blue&#39;, LAST_INSERT_ID()),
(NULL, &#39;t-shirt&#39;, &#39;white&#39;, 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 &#39;Lilliana%&#39;
  AND s.owner = p.id
  AND s.color <> &#39;white&#39;;

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