Mysql从表中检索信息

来自泡泡学习笔记
BrainBs（讨论 | 贡献）2023年7月3日 (一) 17:32的版本 （→‎计算行数）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索

SELECT 语句用于从表格中提取信息。语句的一般形式如下： 

SELECT 要选择的内容
FROM 哪个表格
WHERE 满足的条件；

要选择的内容指示您想要查看什么。这可以是列的列表，或者 * 表示“所有列”。哪个表格指示您要从中检索数据的表格。WHERE 子句是可选的。如果存在，满足的条件指定一个或多个行必须满足的条件才能符合检索要求。


选择所有数据

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仅更改有问题的记录，无需重新加载表格。

有一个例外原则，即SELECT 选择所有列。如果表中包含不可见列，则不包括它们。


选择特定的行

如前一节所示，检索整个表很容易，只需从SELECT语句中省略WHERE子句即可。但通常你不想看到整个表，特别是当它变得很大时。相反，你通常更感兴趣的是回答一个特定的问题，在这种情况下，你需要指定一些约束条件来获取所需的信息。让我们看看一些关于你的宠物的问题所回答的选择查询。

你可以只从你的表中选择特定的行。例如，如果你想验证你对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，而不是1979。

字符串比较通常是不区分大小写的，所以你可以将名字指定为'bowser'、'BOWSER'等等。查询结果是相同的。

你可以在任何列上指定条件，而不仅仅是名字。例如，如果你想知道哪些动物是在1998年或之后出生的，就测试出生列： 

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


选择特定的列

如果您不想看到表中的整行数据，只需用逗号分隔所需的列名即可。例如，如果您想知道您的动物是何时出生的，请选择名称和出生列： 

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

请注意，查询仅从每个记录中检索所有者列，并且其中一些列出现了多次。通过添加关键字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 |
+--------+---------+------------+


对行排序

你可能已经注意到在前面的例子中，结果行的显示顺序是没有特定顺序的。当行按某种有意义的方式排序时，检查查询结果更容易。要对结果进行排序，使用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列的排序顺序。


日期计算

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

要按年龄而不是按名称对输出进行排序，只需使用不同的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值的动物，计算死亡和出生值之间的差异： 

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是一个特殊值，不能使用通常的比较运算符进行比较。这将在后面讨论。

如果您想知道哪些动物下个月过生日怎么办？对于这种计算，年份和日期都不重要；您只需提取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，你可以这样查找出出生在五月（月份为5）的动物： 

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

如果当前月份是十二月，会有一点小复杂。你不能简单地将月份数字（12）加一，然后查找出生在第13个月的动物，因为没有这样的月份。相反，你可以查找出生在一月份（月份为1）的动物。

你可以编写查询，使其无论当前月份是什么，都能正常工作，这样你就不必使用特定月份的数字。DATE_ADD()函数允许你在给定日期上添加一个时间间隔。如果你在CURDATE()的值上添加一个月份，然后使用MONTH()提取月份部分，结果就会得到要查找生日的月份： 

mysql> SELECT name, birth FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

实现相同任务的另一种方法是在使用模数函数（MOD）将月份值包装为0（如果当前为12）之后，加1来获取当前月份之后的下一个月份： 

mysql> SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH()函数返回1到12之间的数字。而MOD(something,12)返回0到11之间的数字。所以加法必须在MOD()之后，否则我们会从11月（11）到1月（1）。

如果计算使用无效的日期，计算将失败并产生警告： 

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01 |
+-------------------------------+

mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL |
+-------------------------------+

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+


使用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表示false，而其他任何值都表示true。布尔运算的默认真值是1。

这种对NULL的特殊处理是为什么在前一节中需要使用death IS NOT NULL而不是death <> NULL来确定哪些动物已经死亡。

在GROUP BY中，两个NULL值被视为相等。

在进行ORDER BY时，如果使用ORDER BY ... ASC，则NULL值将首先显示，如果使用ORDER BY ... DESC，则NULL值将最后显示。

在处理NULL时的一个常见错误是假设无法将零或空字符串插入到定义为NOT NULL的列中，但事实并非如此。这些实际上是值，而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列中，因为它们实际上是NOT NULL的值。


模式匹配

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

要找到包含正好五个字符的名称，使用五个_模式字符的实例： 

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_LIKE()函数（或REGEXP或RLIKE运算符，它们是REGEXP_LIKE()的同义词）。

以下列表描述了扩展正则表达式的一些特点：

  • .匹配任何单个字符。
  • 字符类[...]匹配括号内的任何字符。例如，[abc]匹配a、b或c。要指定字符范围，请使用破折号。[a-z]匹配任何字母，而[0-9]匹配任何数字。
  • *匹配前面的内容的零个或多个实例。例如，x*匹配任意数量的x字符，[0-9]*匹配任意数量的数字，.*匹配任意数量的任何字符。
  • 如果模式匹配中的正则表达式成功匹配值的任何位置，则正则表达式模式匹配成功（这与LIKE模式匹配不同，后者仅在模式匹配整个值时才成功）。
  • 要锚定模式，使其必须匹配值的开头或结尾，请在模式的开头使用^，在模式的结尾使用$。


为了演示扩展正则表达式的工作原理，将上面显示的LIKE查询重新编写为使用REGEXP_LIKE()。

要找到以b开头的名称，请使用^来匹配名称的开头： 

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

要强制正则表达式比较区分大小写，请使用区分大小写的排序规则，或使用BINARY关键字使其中一个字符串成为二进制字符串，或指定c匹配控制字符。以下每个查询仅匹配名称开头的小写b： 

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

要找到以fy结尾的名称，请使用$来匹配名称的结尾： 

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '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 REGEXP_LIKE(name, '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模式不同。

要找到包含正好五个字符的名称，请使用^和$来匹配名称的开头和结尾，并在中间使用五个.的实例： 

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| 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 REGEXP_LIKE(name, '^.{5}$');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+


计算行数

数据库经常被用来回答一个问题：“一个表中某种类型的数据出现了多少次？”例如，你可能想知道你有多少宠物，或每个主人有多少宠物，或者你可能想对你的动物进行各种人口普查操作。

计算你拥有的动物总数就是问“宠物表中有多少行？”因为每个宠物都有一条记录。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 |
+--------+----------+

上述查询使用GROUP BY将每个主人的所有记录分组。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 |
+---------+------+----------+

如果在COUNT(*)值之外还要选择列名，则应该有一个GROUP BY子句来命名这些相同的列。否则，会发生以下情况：

  • 如果启用了ONLY_FULL_GROUP_BY SQL模式，则会发生错误：
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): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
  • 如果没有启用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)


使用多个表

pet表用于跟踪您拥有的宠物。如果您想记录有关宠物的其他信息，例如它们生活中的事件，比如去兽医那里或产仔时，您需要另一个表。这个表应该是什么样子的？它需要包含以下信息：

  • 宠物的名字，以便您知道每个事件涉及哪个动物。
  • 日期，以便您知道事件发生的时间。
  • 描述事件的字段。
  • 事件类型字段，如果您想对事件进行分类。


根据这些考虑，event表的CREATE TABLE语句可能如下所示： 

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

与pet表一样，最简单的方法是通过创建一个包含以下信息的制表符分隔的文本文件来加载初始记录。

name date type remark
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel
Fang 1991-10-12 kennel
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday

像这样加载记录： 

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

根据您在pet表上运行的查询所学到的知识，您应该能够对event表中的记录进行检索；原则是相同的。但是，什么时候event表本身无法回答您可能提出的问题？

假设您想找出每只宠物生育的年龄。我们之前已经看到了如何从两个日期计算年龄。母亲的产仔日期在event表中，但要计算她在那天的年龄，您需要她的出生日期，这个日期存储在pet表中。这意味着查询需要两个表： 

mysql> SELECT pet.name,
TIMESTAMPDIFF(YEAR,birth,date) 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子句连接了两个表，因为查询需要从两个表中获取信息。
  • 当将多个表的信息组合（连接）在一起时，您需要指定如何将一个表中的记录与另一个表中的记录匹配。这很容易，因为它们都有一个名字列。查询使用ON子句根据名称值将两个表中的记录匹配起来。
  • 查询使用INNER JOIN来组合这两个表。INNER JOIN允许只有在两个表都满足ON子句中指定的条件时，才能在结果中出现来自任一表的行。在这个例子中，ON子句指定pet表中的name列必须与event表中的name列匹配。如果一个名称出现在一个表中而不出现在另一个表中，则该行不会出现在结果中，因为ON子句中的条件不满足。
  • 由于name列在两个表中都出现，所以在引用该列时必须明确指定是指哪个表。这是通过在列名前加上表名来实现的。


进行连接不一定需要两个不同的表。有时，如果要将表中的记录与该表中的其他记录进行比较，将表与自身连接也是有用的。例如，要找出您的宠物中的配对繁殖，您可以将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 p1.death IS NULL
AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name | sex | name | sex | species |
+--------+------+-------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
+--------+------+-------+------+---------+

在这个查询中，我们为表名指定了别名，以引用列并确保每个列引用与表的实例相对应。

取自“http://bubblestudy.info/wiki/index.php?title=Mysql从表中检索信息&oldid=1300”