MySQL

参考材料

  1. MySQL 5.1 参考手册

连接与断开服务器

连接服务器

1
2
3
4
5
6
7
8
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>提示下输入QUIT (或)随时退出：

1
2
mysql> QUIT
Bye

在Unix中，也可以按control-D键断开服务器。

输入查询

  • 一个命令通常由SQL语句组成，随后跟着一个分号。（有一些例外不需要分号。早先提到的QUIT是一个例子。后面我们将看到其它的例子。）
  • 当发出一个命令时，mysql将它发送给服务器并显示执行结果，然后显示另一个mysql>显示它准备好接受其它命令。
  • mysql用表格(行和列)方式显示查询输出。第一行包含列的标签，随后的行是查询结果。通常，列标签是你取自数据库表的列的名字。如果你正在检索一个表达式而非表列的值(如刚才的例子)，mysql用表达式本身标记列。
  • mysql显示返回了多少行，以及查询花了多长时间，它给你提供服务器性能的一个大致概念。因为他们表示时钟时间(不是 CPU 或机器时间)，并且因为他们受到诸如服务器负载和网络延时的影响，因此这些值是不精确的。（为了简洁，在本章其它例子中不再显示“集合中的行”。）

示例1

1
2
3
4
5
6
7
8
mysql> SELECT VERSION(), CURRENT_DATE;
+-----------------|--------------+
| VERSION()       | CURRENT_DATE |
+-----------------|--------------+
| 5.1.2-alpha-log | 2005-10-11   |
+-----------------|--------------+
1 row in set (0.01 sec)
mysql>

示例2：简单计算器

这是另外一个查询，它说明你能将mysql用作一个简单的计算器：

1
2
3
4
5
6
7
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------|---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------|---------+
| 0.70710678118655 |      25 |
+------------------|---------+
1 row in set (0.02 sec)

示例3：分号和多条语句

至此显示的命令是相当短的单行语句。你可以在一行上输入多条语句，只需要以一个分号间隔开各语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

示例4：多行语句

不必全在一个行内给出一个命令，较长命令可以输入到多个行中。mysql通过寻找终止分号而不是输入行的结束来决定语句在哪儿结束。（换句话说，mysql接受自由格式的输入：它收集输入行但直到看见分号才执行。）

这里是一个简单的多行语句的例子：

1
2
3
4
5
6
7
8
9
mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+---------------|--------------+
| USER()        | CURRENT_DATE |
+---------------|--------------+
| jon@localhost | 2005-10-11   |
+---------------|--------------+

在这个例子中，在输入多行查询的第一行后，要注意提示符如何从 mysql> 变为 -> ，这正是mysql如何指出它没见到完整的语句并且正在等待剩余的部分。提示符是你的朋友，因为它提供有价值的反馈，如果使用该反馈，将总是知道mysql正在等待什么。

如果你决定不想执行正在输入过程中的一个命令，输入 \c 取消它：

1
2
3
4
mysql> SELECT
    -> USER()
    -> \c
mysql>

这里也要注意提示符，在你输入 \c 以后，它切换回到 mysql> ，提供反馈以表明mysql准备接受一个新命令。

创建并使用数据库

查看当前服务器上所有MySQL数据库

.sql}
1
2
3
4
5
6
7
8
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

请注意如果没有SHOW DATABASES权限，则不能看见所有数据库。

分配数据库管理权限

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

这里 your_mysql_name 是分配给你的MySQL用户名， your_client_host 是所连接的服务器所在的主机。

创建并选择数据库

如果管理员在设置权限时为你创建了数据库，你可以开始使用它。否则，你需要自己创建数据库：

1
mysql> CREATE DATABASE menagerie;

在Unix下，数据库名称是 区分大小写的 (不像SQL关键字)，因此你必须总是以menagerie访问数据库，而不能用Menagerie、MENAGERIE或其它一些变量。对表名也是这样的。

访问数据库

.sql}
1
2
mysql> USE menagerie
Database changed

注意： USE ，类似 QUIT ，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍） USE 语句在使用上也有另外一个特殊的地方：它必须在一个单行上给出。

创建表

使用一个CREATE TABLE语句指定你的数据库表的布局。例如给 “宠物” 建一个表：

1
2
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应该产生一些输出：

1
2
3
4
5
6
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    |       |
+---------|-------------|------|-----|---------|-------+

增

创建表后，需要填入内容。通过 LOAD DATA 和 INSERT 语句可以完成该任务。

方法1：LOAD DATA 从文本导入

因为你是从一个空表开始的，填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。

你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件中表示这些内容，使用\N（反斜线，字母N）。例如，Whistler鸟的记录应为(这里值之间的空白是一个定位符)：

name owner species sex birth death

Whistler Gwen bird 1997-12-09

要想将文本文件“pet.txt”装载到pet表中，使用这个命令：

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

请注意如果用Windows中的编辑器（使用）创建文件，应使用：

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

（在运行OS X的Apple机上，应使用行结束符’’。）

如果你愿意，你能明确地在 LOAD DATA 语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。这对读入文件“pet.txt”的语句已经足够。

方法2. INSERT 语句新增记录

用 INSERT 语句一次增加一个新记录：

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

注意，这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用INSERT语句插入NULL代表不存在的值。不能使用LOAD DATA中所示的的\N。

删

删除 pet 表

1
mysql> DELETE FROM pet;

改

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

查

1
2
3
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select 指出你想要看到的内容，可以是列的一个表，或 * 表示“所有的列”。which_table指出你想要从其检索数据的表。WHERE子句是可选项，如果选择该项，conditions_to_satisfy指定行必须满足的检索条件。

查全部

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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       |
+----------|--------|---------|------|------------|------------+

查某一行

查询名字叫做 Bowser 的一只宠物的记录：

1
2
3
4
5
6
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------|-------|---------|------|------------|------------+
| name   | owner | species | sex  | birth      | death      |
+--------|-------|---------|------|------------|------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------|-------|---------|------|------------|------------+

查询哪个动物在 1998 年后出生：

1
2
3
4
5
6
7
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  |
+----------|-------|---------|------|------------|-------+

组合条件 AND 和 OR

组合条件，例如，找出雌性的狗：

1
2
3
4
5
6
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操作符：

1
2
3
4
5
6
7
8
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具有更高的优先级。 如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意：

1
2
3
4
5
6
7
8
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  |
+-------|--------|---------|------|------------|-------+

查一列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 |
+----------|------------+

找出谁拥有宠物，使用这个查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

请注意该查询只是简单地检索每个记录的owner列，并且他们中的一些出现多次。为了使输出减到最少，增加关键字 DISTINCT 检索出每个唯一的输出记录：

1
2
3
4
5
6
7
8
9
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

可以使用一个WHERE子句结合行选择与列选择。例如，要想查询狗和猫的出生日期，使用这个查询：

1
2
3
4
5
6
7
8
9
10
11
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 |
+--------|---------|------------+

模式查找

MySQL提供标准的SQL模式匹配 [1]，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。

标准的 SQL 模式匹配以及 LIKE 操作符

  • “_”匹配任何单个字符
  • “%”匹配任意数目字符(包括零字符)
  • 比较时不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

要想找出以“b”开头的名字：

1
2
3
4
5
6
7
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”结尾的名字：

1
2
3
4
5
6
7
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”的名字：

1
2
3
4
5
6
7
8
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个字符的名字，使用“_”模式字符：

1
2
3
4
5
6
7
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  |
+-------|--------|---------|------|------------|-------+

正则表达式 REGEXP 操作符

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式 [2](#fn1)^。当你对这类模式进行匹配测试时，使用 REGEXP 和 NOT REGEXP 操作符(或RLIKE和NOT RLIKE，它们是同义词)。

为了找出以“b”开头的名字，使用“^”匹配名字的开始：

1
2
3
4
5
6
7
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”结尾的名字，使用“$”匹配名字的结尾：

1
2
3
4
5
6
7
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”的名字，使用以下查询：

1
2
3
4
5
6
7
8
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       |
+----------|-------|---------|------|------------|------------+

注意： 如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。

既然如果一个正则表达式出现在值的任何地方，其模式匹配了，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。

为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：

1
2
3
4
5
6
7
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次”操作符重写前面的查询：

1
2
3
4
5
6
7
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  |
+-------|--------|---------|------|------------|-------+

查找结果排序

为了排序结果，使用 ORDER BY 子句。

这里是动物生日，按日期排序：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 （降序 ）关键字：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 |
+----------|------------+

可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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列的排序顺序。

日期计算

MySQL提供了几个函数，可以用来计算日期。

  • CURDATE() 获取现在的日期
  • NOW() 获取现在的日期和时间
  • YEAR() 提取日期的年部分
  • MONTH() 提取日期的月部分
  • DAYOFMONTH() 提取
  • RIGHT() 提取日期的 MM-DD (日历年)部分的最右面5个字符
  • DATE_ADD() 允许在一个给定的日期上加上时间间隔
  1. 示例1：计算年龄

要想确定每个宠物有多大，可以计算当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年。以下查询显示了每个宠物的出生日期、当前日期和年龄数值的年数字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 |
+----------|------------|------------|------+

此处，比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。整个表达式有些难懂，使用 alias (age) 来使输出的列标记更有意义。

  1. 示例2：找出每个动物生日的月份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 |
+----------|------------|--------------+
  1. 示例3：找出下个月生日的动物
  • 方法1：使用 DATE_ADD() 函数
1
2
mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
  • 方法2：加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果月份当前值是12，则“回滚”到值0)：
1
2
mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

分类和统计

COUNT() 和GROUP BY以各种方式分类你的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

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

注意： 使用 GROUP BY 对每个owner的所有记录分组，没有它，你会得到错误消息：

1
2
3
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

下列例子显示出进行动物普查操作的不同方式。

每种动物的数量：

1
2
3
4
5
6
7
8
9
10
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------|----------+
| species | COUNT(*) |
+---------|----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------|----------+

每种性别的动物数量：

1
2
3
4
5
6
7
8
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------|----------+
| sex  | COUNT(*) |
+------|----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------|----------+

（在这个输出中，NULL表示“未知性别”。）

按种类和性别组合的动物数量：

1
2
3
4
5
6
7
8
9
10
11
12
13
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 |
+---------|------|----------+

使用1个以上的表

1
2
3
4
5
6
7
8
9
10
11
12
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         |
+--------|------|-----------------------------+

同一个表与自身的连接也是常见的。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对：

1
2
3
4
5
6
7
8
9
10
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     |
+--------|------|--------|------|---------+

获得数据库和表的信息

可以用 SHOW TABLES 来查看数据库的表：

mysql> SHOW TABLES;
Empty set (0.00 sec)

为了找出当前选择了哪个数据库，使用DATABASE( )函数：

1
2
3
4
5
6
7
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+
如果你还没选择任何数据库，结果是NULL。

为了找出当前的数据库包含什么表(例如，当你不能确定一个表的名字)，使用这个命令：

1
2
3
4
5
6
7
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

如果你想要知道一个表的结构，可以使用DESCRIBE命令；它显示表中每个列的信息：

1
2
3
4
5
6
7
8
9
10
11
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    |       |
+---------|-------------|------|-----|---------|-------+

补完

使用mysql脚本

1
shell> mysql < batch-file

NULL值

概念上，NULL意味着“没有值”或“未知值”，且它被看作与众不同的值。为了测试NULL，你可以使用IS NULL和IS NOT NULL操作符：

1
2
3
4
5
6
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------|---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------|---------------+
|         0 |             1 |
+-----------|---------------+

使用用户变量

你可以清空MySQL用户变量以记录结果，不必将它们保存到客户端的临时变量中。

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

1
2
3
4
5
6
7
8
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 |
+---------|--------|-------+

根据两个键搜索

可以充分利用使用单关键字的 OR 子句，如同 AND 的处理。

一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

1
2
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

该情形是已经优化过的。参见7.2.6节，“索引合并优化”。

还可以使用 UNION 将两个单独的 SELECT 语句的输出合成到一起来更有效地解决该问题。

1
2
3
4
5
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';

  1. 参见 通配符 ↩︎

  2. 1 ↩︎

Comments

▲