3.3 创建并使用数据库

于 2016-01-24 22:03:38 发布
阅读量1.1k 收藏 1
点赞数
分类专栏： mysql MySQL 5.6 参考 文章标签： mysql 创建表 检索 数据库

一旦你知道如何输入SQL语句，便可以去使用数据库了。

假定在你的家(你的“动物园”)中有很多宠物，并且你想跟踪关于它们各种类型的信息。你可以通过创建表来保存你的数据并且载入你需要的信息。然后你就可以通过从表中检索数据来回答关于你的动物不同种类的问题。这个章节向你展示如何演示下列的操作：

  • 创建数据库
  • 创建表
  • 往表中载入数据
  • 用各种方法从表中检索数据
  • 使用多个表

动物园数据库很简单(故意地),但是不难把它想象成可能用到类似数据库的真实世界的情景。例如，农夫可以使用这样的一个数据库来追踪家畜，或者兽医可以用它跟踪病畜记录。从MySQL网址上可以获得后面章节中将用到的含有部分查询和样例数据的动物园分发。在 http://
dev.mysql.com/doc/可找到tar和Zip格式的压缩文件。

使用SHOW语句找出服务器上当前存在什么数据库：

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

mysql 数据库描述用户访问权限。test数据库经常作为一个为用户提供实践的工作区。

可能你的机器上的数据库列表是不同的；如果你没有SHOW DATABASES权限，SHOW DATABASES语句将不会显示数据库。查看Section 13.7.5.15, “SHOW DATABASES Syntax”.

如果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下，该限制不适用，尽管你必须在一个给定的查询中使用同样的大小写来引用数据库和表。但是，由于多种原因，作为最好的惯例，一定要使用与数据库创建时的同样的大小写。）

注意：如果当你尝试创建数据库时，遇到一个像 ERROR 1044 (42000): Access denied for user ‘micah’@’localhost’ to database ‘menagerie’的错误，这意味着你的用户账户没有需要的权限去这么做。那么你得去和你和管理员讨论或者查看Section 6.2, “The MySQL Access Privilege System”.

创建数据库并不表示选择了它来用；你必须明确的操作。为了使menagerie成为当前的数据库，使用这个命令：

mysql> USE menagerie
Database changed

数据库只需要创建一次，但是必须在每次启动mysql会话时在使用前先选择它。你可以根据上面的例子执行一个USE语句来实现。或者，当你调用mysql的时候，你可以通过命令行选择数据库。只需要在提供任何连接参数后面指定数据库名。例如：

shell> mysql -h host-u user-p menagerie
Enter password: ********

重要
刚才显示的命令行中的menagerie不是你的密码。如果你想要在命名行上-p选项后提供你的密码，则不能插入空格(例如，如-pmypassword，不是-p mypassword)。但是，不建议在命令行输入密码，因为这样会暴露 密码，能被在机器上登录的其它用户窥探到。

注意
使用SELECT DATABASE()命令，你可以在任何时候查看你选择了哪一个数据库。

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，例如，如果你忘记了列名或者它们的类型。

更多mysql数据类型的信息，查看Chapter 11, Data Types。

3.3.3 添加数据

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

假设你的宠物记录描述如下。（假定在MySQL中期望的日期格式是YYYY-MM-DD；这可能与你习惯的不同。）

nameownerspeciessexbirthdeath
FluffyHaroldcatf1993-02-04
ClawsGwencatm1994-03-17
FangBennydogm1990-08-27
BowserDianedogm1979-08-311995-07-29
ChirpyGwenbirdf1998-09-11
WhistlerGwenbird1997-12-09
SlimBennysnakem1996-04-29

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

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

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系统的苹果机器上，你应该使用行结束符LINES TERMINATED BY ‘\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);

这里的String 和date 类型的值被指定用单引号括起来。另外，可以直接用INSERT语句插入NULL代表不存在的值。不能使用LOAD DATA中所示的的\N。

从这个例子，你应该看到涉及很多的键入用多个INSERT语句而非单个LOAD DATA语句装载你的初始记录。

3.3.4 从表中检索信息

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’s 的生日做的修改，像这样选择Bowser’s 的记录：

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. 例如，如果你想要知道在1988年以后出现的动物，测试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提供了几个函数，可以用来计算日期，例如，计算年龄或提取日期部分。

要想确定每个宠物的年龄，可以使用TIMESTAMPDIFF()函数。它的参数是在你想要表达的结果里的单元，并且是为了计算两个日期之间的差。以下查询显示了每个宠物的出生日期、当前日期和年龄的年数字。别名(age)的使用是为了是最后的输出列名更加有意义。

mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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 |
+----------+------------+------------+------+

尽管查询可行，如果以某个顺序排列行，则能更容易地浏览结果。添加ORDER BY name子句按照名字对输出进行排序则能够实现。

mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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,
-> TIMESTAMPDIFF(YEAR,birth,death) 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是一个特殊的值，不能使用通用比较符比较。以后会给出解释。查看Section 3.3.4.6, “Working with NULL Values”.

如果你想知道哪个动物下个月过生日，怎么办？对于这种类型的计算，年和日是不相关的；你只需要提取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月出生的宠物：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

如果当前月是12月，那么就有一点复杂。你不能仅仅在月份（12）上加1，并寻找在13月出生的宠物，因为没有这个月。相反，你查找在1月出生的宠物。

你可以写一个查询，不管当前月份是什么它都能工作，你就不用对一个特殊的月份使用一个数字。DATE_ADD()允许你在一个给定的日期上加上一个时间间隔。如果你在CURDATE()值上加一个月，然后提取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,使用IS NULL 和 IS NOT NULL操作符，展示如下：

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

你不能使用像-,<或者<>这样的算术比较符去测试NULL。为了说明它，试试下列查询：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+

因为算术比较符和NULL在一起的结果还是NULL,你从这样的比较符不能得到任何有意义的结果。

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

为了找出包含字母“m”的名字：

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个字符的名字，使用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 |
+-------+--------+---------+------+------------+-------+

Section 12.5.2, “Regular Expressions”提供了关于正则表达式的句法的详细信息。

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

前面的查询使用GPOUP BY按照owner把所有记录分组。把COUNT()是和GPOUP 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 |
+---------+------+----------+

除了COUNT()值，如果你命名选择的列，那么为了命名那些相同的列，GROUP BY子句需要出现否则，下面的情况就会出现：

  • 如果 ONLY_FULL_GROUP_BY 模式开启了，那么一个错误就会出现：
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
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
  • 如果ONLY_FULL_GROUP_BY 模式没有开启，这个查询会把所有的行看成一个单个的组运行下去，但是对于每个命名的列，选择的值是不确定的。服务器会从任意行选择值：
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 | 
+--------+----------+
1 row in set (0.00 sec)

参见 Section 12.19.3, “MySQL Handling of GROUP BY”.

3.3.4.9 使用多张表

pet表追踪你有哪些宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医的情况或何时后代出生，你需要另外的表。这张表应该像什么呢？它必须包含以下信息：

  • 宠物名字以便你知道每个事件属于哪个动物。
  • 一个日期以便你知道事件是什么时候发生的。
  • 一个描述事件的字段。
  • 如果你想要对事件进行分类，则需要一个事件类型字段。

综合上述因素，event表的CREATE TABLE语句应为：

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

对于pet表，最容易的方法是创建包含信息的用定位符分隔的文本文件来装载初始记录：

表

采用如下方式装载记录：

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 INNER JOIN event
-> ON pet.name = event.name
-> WHERE 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值来匹配两个表中的记录。
  • 这个查询使用一个INNER JOIN去连接这些表。INNER JOIN允许任何表的行出现在结果中，当且仅当两个表都满足ON子句中指定的条件。在这个例子中，pet表中ON子句中指定的name列必须和event表中的name列匹配。如果一个名字出现在这个表中，而另一个表中没有，那么这行就不会出现在结果中，因为不符合on子句的条件。
  • 因为name列出现在两个表中，当引用列时，你一定要指定哪个表。把表名附在列名前即可以实现。

你不必有2个不同的表来进行联结。如果你想要将一个表的记录与同一个表的其它记录进行比较，可以将一个表联结到自身。例如，为了在你的宠物之中繁殖配对，你可以用pet联结自身来进行相似种类的雄雌配对：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1 INNER JOIN pet AS p2
-> ON 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 |
+--------+------+--------+------+---------+

在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用与哪个表实例相关联更直观。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值