既然你知道怎样输入命令，现在是存取一个数据库的时候了。 假定在你的家(你的“动物园”)中有很多宠物，并且你想追踪关于他们各种各样类型的信息。你可以通过创建表来保存你的数据并根据所需要的信息装载他们做到，然后你可以通过从表中检索数据来回答关于你的动物不同种类的问题。本节显示如何做到所有这些事情： 怎样创建一个数据库 怎样创建一个数据库表 怎样装载数据到数据库表 怎样以各种方法从表中检索数据 怎样使用多个表 动物园数据库将会是简单的(故意的)，但是不难把它想象成可能用到相似类型数据库的真实世界情况。例如，这样的一个数据库能被一个农夫用来追踪家畜，或由一个兽医追踪病畜记录。 使用SHOW语句找出在服务器上当前存在什么数据库： mysql> SHOW DATABASES; ---------- | Database | ---------- | mysql | | test | | tmp | ---------- 数据库列表可能在你的机器上是不同的，但是mysql和test数据库很可能的在其间。mysql是必需的，因为它描述用户存取权限，test数据库经常作为一个工作区提供给用户试试身手。 如果test数据库存在，尝试存取它： mysql> USE test Database changed 注意，USE，类似QUIT，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍)USE语句在使用上也有另外一个特殊的地方：它必须在一个单行上给出。 你可列在后面的例子中使用test数据库(如果你能访问它)，但是你在该数据库创建的任何东西可以被与访问它的其他人删除，为了这个原因，你可能应该询问你的MySQL管理员许可你自己使用的一个数据库。假定你想要调用你的menagerie，管理员需要执行一个这样的命令： mysql> GRANT ALL ON menagerie.* TO your_mysql_name; 这里your_mysql_name是分配给你的MySQL用户名。 1 创建并选用一个数据库 如果在设置你的权限时，管理员为你创建了数据库，你可以开始使用它。否则，你需要自己创建它： mysql> CREATE DATABASE menagerie; 在Unix下，数据库名字是区分大小写的(不像SQL关键词)，因此你必须总是以menagerie引用你的数据库，不是Menagerie、MENAGERIE或一些其他变种。对表名也是这样的。（在Windows下，该限制不适用，尽管你必须在一个给定的查询中使用同样的大小写来引用数据库和表。） 创建了一个数据库并不选定以使用它，你必须明确地做这件事。为了使menagerie称为当前的数据库，使用这个命令： mysql> USE menagerie Database changed 你的数据库只需要创建一次，但是你必须在每次启动一个mysql会话时为使用而选择它。你可以由发出上面一个USE语句做到。另外，当你调用时mysql，你可在命令行上选择数据库，就在你可能需要提供的任何连接参数之后指定其名字。例如： shell> mysql -h host -u user -p menagerie Enter password: ******** 注意，menagerie不是你在刚才所示命令的口令。如果你想要在命令行上在-p选项后提供你的口令，你必须做到没有多余的空格(例如，如-pmypassword，不是-p mypassword)。然而，不建议把你的口令放在命令行上，因为这样做把它暴露出来，能被在你的机器上登录的其他用户窥探到。
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到255的任何长度，无论哪个对你来说好象最合理。（如果你做了较差的选择，以后会变得你需要一个更长的字段，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，例如，如果你忘记在你表中的列的名字或他们是什么类型。
3 将数据装入一个数据库表 在你创建表后，你需要充实它。LOAD DATA和INSERT语句用于此。 假定你的宠物纪录描述如下。（观察到MySQL期望日期时以YYYY-MM-DD格式；这可能与你习惯的不同。） name owner species sex birth death Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13 Fang Benny dog m 1990-08-27 Bowser Diane dog m 1998-08-31 1995-07-29 Chirpy Gwen bird f 1998-09-11 Whistler Gwen bird 1997-12-09 Slim Benny snake m 1996-04-29 因为你是从一张空表开始的，充实它的一个容易方法是创建包含为你的动物各一行一个文本文件，然后用一个单个语句装载文件的内容到表中。 你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以在CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件表示这些，使用\N。例如，对Whistler鸟的记录看起来像这样的(这里在值之间的空白是一个单个的定位字符)： Whistler Gwen bird \N 1997-12-09 \N 为了装载文本文件“pet.txt”到pet表中，使用这个命令： mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; 如果你愿意，你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是缺省是定位符和换行符。这些对争取读入文件“pet.txt”的语句是足够的。 当你想要一次增加一个新记录时，INSERT语句是有用的。在它最简单的形式，你为每一列提供值，以列在CREATE TABLE语句被列出的顺序。假定Diane把一只新仓鼠命名为Puffball，你可以使用一个这样INSERT语句增加一条新记录： mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 注意，这里字符串和日期值被指定为引号扩起来的字符串。另外，用INSERT，你能直接插入NULL代表不存在的值。你不能使用\N，就像你用LOAD DATA做的那样。 从这个例子，你应该能看到涉及很多的键入用多个INSERT语句而非单个LOAD DATA语句装载你的初始记录。
4 从一个数据库表检索信息 SELECT语句被用来从一张桌子拉出信息。语句的一般格式是： SELECT what_to_select FROM which_table WHERE conditions_to_satisfy what_to_select指出你想要看到的，这可以是列的一张表，或*表明“所有的列”。which_table指出你想要从其检索数据的表。WHERE子句是可选的，如果它在，conditions_to_satisfy指定行必须满足的检索条件。 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 | 1998-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，而不是1998。 至少有一些修正它的方法： 编辑文件“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"; 如上所示，检索整个表是容易的，但是一般你不想那样做，特别地当表变得很大时。相反，你通常对回答一个特别的问题更感兴趣，在这种情况下你在你想要的信息上指定一些限制。让我们看一些他们回答有关你宠物的问题的选择查询。 4.2 选择特定行 你能从你的表中只选择特定的行。例如，如果你想要验证你对Bowser的出生日期所做的改变，像这样精选Bowser的记录： mysql> SELECT * FROM pet WHERE name = "Bowser"; -------- ------- --------- ------ ------------ ------------ | name | owner | species | sex | birth | death | -------- ------- --------- ------ ------------ ------------ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | -------- ------- --------- ------ ------------ ------------ 输出证实年份现在正确记录为1989，而不是1998。 字符串比较通常是大小些无关的，因此你可以指定名字为"bowser"、"BOWSER"等等，查询结果将是相同的。 你能在任何列上指定条件，不只是name。例如，如果你想要知道哪个动物在1998以后出生的，测试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可以混用。如果你这样做，使用括号指明条件应该如何被分组是一个好主意： 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 | ------- -------- --------- ------ ------------ ------- 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 | -------- --------- ------------ 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 | ---------- ------------ 为了以逆序排序，增加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值仍然以升序被排序。
4.5 日期计算 MySQL提供几个函数，你能用来执行在日期上的计算，例如，计算年龄或提取日期的部分。 为了决定你的每个宠物有多大，用出生日期和当前日期之间的差别计算年龄。通过变换2个日期到天数，取差值，并且用365除(在一年里的天数)： mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet; ---------- ------------------------------------- | name | (TO_DAYS(NOW())-TO_DAYS(birth))/365 | ---------- ------------------------------------- | Fluffy | 6.15 | | Claws | 5.04 | | Buffy | 9.88 | | Fang | 8.59 | | Bowser | 9.58 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Puffball | 0.00 | ---------- ------------------------------------- 尽管查询可行，关于它还有能被改进的一些事情。首先，如果行以某个次序表示，其结果能更容易被扫描。第二，年龄列的标题不是很有意义的。 第一个问题通过增加一个ORDER BY name子句按名字排序输出来解决。为了处理列标题，为列提供一个名字以便一个不同的标签出现在输出中(这被称为一个列别名)： mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY name; ---------- ------ | name | age | ---------- ------ | Bowser | 9.58 | | Buffy | 9.88 | | Chirpy | 0.55 | | Claws | 5.04 | | Fang | 8.59 | | Fluffy | 6.15 | | Puffball | 0.00 | | Slim | 2.92 | | Whistler | 1.30 | ---------- ------ 为了按age而非name排序输出，只要使用一个不同ORDER BY子句： mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY age; ---------- ------ | name | age | ---------- ------ | Puffball | 0.00 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Claws | 5.04 | | Fluffy | 6.15 | | Fang | 8.59 | | Bowser | 9.58 | | Buffy | 9.88 | ---------- ------ 一个类似的查询可以被用来确定已经死亡动物的死亡年龄。你通过检查death值是否是NULL来决定那些是哪些动物，然后，对于那些有非NULL值，计算在death和birth值之间的差别： mysql> SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; -------- ------------ ------------ ------ | name | birth | death | age | -------- ------------ ------------ ------ | Bowser | 1989-08-31 | 1995-07-29 | 5.91 | -------- ------------ ------------ ------ 差询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，这以后会解释。见4.6 用NULL值工作。 如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你简单地想要提取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月出生的动物 (5月)， 象这样： mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; ------- ------------ | name | birth | ------- ------------ | Buffy | 1989-05-13 | ------- ------------ 当然如果当前月份是12月，就有点复杂了。你不是只把加1到月份数(12)上并且寻找在13月出生的动物，因为没有这样的月份。相反，你寻找在1月出生的动物(1月) 。 你甚至可以编写查询以便不管当前月份是什么它都能工作。这种方法你不必在查询中使用一个特定的月份数字，DATE_ADD()允许你把时间间隔加到一个给定的日期。如果你把一个月加到NOW()值上，然后用MONTH()提取月份部分，结果产生寻找生日的月份： mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH)); 完成同样任务的一个不同方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果它当前是12，则“绕回”月份到值0)： mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) 1; 注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD()以后加1，否则我们将从11月( 11 )跳到1月(1)。 4.6 NULL值操作 NULL值可能很奇怪直到你习惯于它。概念上，NULL意味着“没有值”或“未知值”，且它被看作有点与众不同的值。为了测试NULL，你不能使用算术比较运算符例如=、<或!=。为了说明它，试试下列查询： mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; ---------- ----------- ---------- ---------- | 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL | ---------- ----------- ---------- ---------- | 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 | ----------- --------------- 在MySQL中，0意味着假而1意味着真。 NULL这样特殊的处理是为什么，在前面的章节中，为了决定哪个动物不再是活着的，使用death IS NOT NULL而不是death != NULL是必要的。 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 | -------- -------- --------- ------ ------------ ------- 为了找出包含一个“w”的名字： 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个字符的名字，使用“_”模式字符： 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]*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。 正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“[aA]”匹配小写或大写的“a”而“[a-zA-Z]”匹配两种写法的任何字母。 如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，SQL模式匹配)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。 为了说明扩展正则表达式如何工作，上面所示的LIKE查询在下面使用REGEXP重写： 为了找出以“b”开头的名字，使用“^”匹配名字的开始并且“[bB]”匹配小写或大写的“b”： mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; -------- -------- --------- ------ ------------ ------------ | 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 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”的名字，使用“[wW]”匹配小写或大写的“w”： mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; ---------- ------- --------- ------ ------------ ------------ | 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 | ------- -------- --------- ------ ------------ ------- 4.8 行计数 数据库经常用于回答这个问题，“某个类型的数据在一张表中出现的频度?”例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要在你的动物上施行各种类型的普查。 计算你拥有动物的总数字与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT()函数计数非NULL结果的数目，所以数你的动物的查询看起来像这样： 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 | -------- ---------- 注意，使用GROUP BY对每个owner分组所有记录，没有它，你得到的一切是一条错误消息： mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause COUNT()和GROUP 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 | --------- ------ ----------
5 使用多个数据库表 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, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; -------- ------ ----------------------------- | name | age | remark | -------- ------ ----------------------------- | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | -------- ------ ----------------------------- 关于该查询要注意的几件事情： FROM子句列出两个表，因为查询需要从他们两个拉出信息。 当组合(联结-join)来自多个表的信息时，你需要指定在一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。 因为name列出现在两个表中，当引用列时，你一定要指定哪个表。这通过把表名附在列名前做到。 你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较，联结一个表到自身有时是有用的。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对： 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 | -------- ------ -------- ------ --------- 在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。