MySQL检索信息

从表中检索信息

该SELECT语句用于从表中提取信息。声明的一般形式是：

1
2
3

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select表示你想看到什么，这可以是列表中的一列， * 可以表示“ 所有列”。which_table表示要从中检索数据的表。该WHERE 条款是可选的。如果存在，则 conditions_to_satisfy指定行必须满足的一个或多个条件才有资格进行检索。

mysql> select name
    -> from pet
    -> where sex='m'；
+--------+
| name   |
+--------+
| Claws  |
| Fang   |
| Bowser |
+--------+
3 rows in set (0.00 sec)

1.选择所有数据

SELECT最简单的形式从表中检索所有内容

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1999-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 | Genw   | bird    | f    | 1998-09-11 | NULL       |
+--------+--------+---------+------+------------+------------+
6 rows in set (0.00 sec)

UPDATE语句修复错误记录：

mysql> update pet
    -> set birth='1990-01-01'
    -> where name='Bowser';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

2.选择特定行

例如，如果想验证刚才对Bowser出生日期所做的更改，请选择Bowser的记录，如下所示：

mysql> select *     
    -> from pet
    -> where name='Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1990-01-01 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

mysql>

字符串比较通常不区分大小写，因此可以将名称指定为’bowser’， ‘BOWSER’等等。查询结果是一样的。

也可以在任何列上指定条件，例如想知道1997年或之后出生的动物：

mysql> select *                                       
    -> from pet
    -> where birth >= '1997-02-22';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1999-02-04 | NULL  |
| Chirpy | Genw   | bird    | f    | 1998-09-11 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

还可以使用 AND 进行组合条件进行查询

mysql> select *      
    -> from pet 
    -> where species = 'dog' and sex = 'm';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Fang   | Benny | dog     | m    | 1990-08-27 | NULL       |
| Bowser | Diane | dog     | m    | 1990-01-01 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

除了AND逻辑运算符还有OR。

mysql> select *                                                        
    -> from pet
    -> where species = 'dog' or species = 'bird';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang   | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser | Diane  | dog     | m    | 1990-01-01 | 1995-07-29 |
| Chirpy | Genw   | bird    | f    | 1998-09-11 | NULL       |
+--------+--------+---------+------+------------+------------+
4 rows in set (0.00 sec)

AND和OR可以混合，但 AND优先级高于 OR。如果同时使用这两个运算符，最好使用括号明确指出条件应如何分组。

mysql> select *     
    -> from pet
    -> where (species='dog' and sex='f') or (species='bird' and sex='f');
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
| Chirpy | Genw   | bird    | f    | 1998-09-11 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

3.选择特定列

如果不查看表中的整行，只需将感兴趣的列名进行逗号分隔。例如如果查询谁的动物什么时候出生，请选择name和 birth列：

mysql> SELECT name, birth FROM pet;
+--------+------------+
| name   | birth      |
+--------+------------+
| Fluffy | 1999-02-04 |
| Claws  | 1994-03-17 |
| Buffy  | 1989-05-13 |
| Fang   | 1990-08-27 |
| Bowser | 1990-01-01 |
| Chirpy | 1998-09-11 |
+--------+------------+
6 rows in set (0.00 sec)

要找出宠物属于谁，如下查询

mysql> select owner from pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Genw   |
+--------+
6 rows in set (0.00 sec)

要最小化输出，请通过添加关键字 DISTINCT 去除重复输出：

mysql> select distinct owner from pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Benny  |
| Diane  |
| Genw   |
+--------+
5 rows in set (0.00 sec)

使用WHERE子句将行选择与列选择组合在一起。例如，仅获取狗和猫的出生日期，如下查询：

mysql> select name,species,birth
    -> from pet
    -> where species = 'dog' or species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1999-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1990-01-01 |
+--------+---------+------------+
5 rows in set (0.00 sec)

4.对行排序

当行以某种有意义的方式排序时，通常更容易查询输出。要对结果进行排序，请使用ORDER BY子句。
默认排序顺序是升序。要按反向（降序）排序，请将DESC关键字添加到要排序的列名称后面

mysql> select name, birth from pet order by birth;
+--------+------------+
| name   | birth      |
+--------+------------+
| Buffy  | 1989-05-13 |
| Bowser | 1990-01-01 |
| Fang   | 1990-08-27 |
| Claws  | 1994-03-17 |
| Chirpy | 1998-09-11 |
| Fluffy | 1999-02-04 |
+--------+------------+
6 rows in set (0.00 sec)

mysql> select name, species, birth from pet
    -> order by species,birth desc;
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Chirpy | bird    | 1998-09-11 |
| Fluffy | cat     | 1999-02-04 |
| Claws  | cat     | 1994-03-17 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1990-01-01 |
| Buffy  | dog     | 1989-05-13 |
+--------+---------+------------+
6 rows in set (0.00 sec)

该DESC关键字仅适用于紧邻的列名（birth）; 它不会影响species列排序顺序。

LIMIT 子句用于限制由 SELECT 语句返回的数据数量。下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

1
2
3

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行。

5.日期计算

要确定每只宠物的年龄，使用 TIMESTAMPDIFF()功能。它的参数是你所希望结果表达的单位，以及取得差异的两个日期。以下查询显示了每只宠物的出生日期，当前日期和年龄。一个别名（age）使得输出列更有意义。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;
+--------+------------+------------+------+
| name   | birth      | CURDATE()  | age  |
+--------+------------+------------+------+
| Fluffy | 1999-02-04 | 2019-02-19 |   20 |
| Claws  | 1994-03-17 | 2019-02-19 |   25 |
| Buffy  | 1989-05-13 | 2019-02-19 |   29 |
| Fang   | 1990-08-27 | 2019-02-19 |   28 |
| Bowser | 1990-01-01 | 2019-02-19 |   29 |
| Chirpy | 1998-09-11 | 2019-02-19 |   20 |
+--------+------------+------------+------+
6 rows in set (0.00 sec)

也可以使用order by 对age进行排序，不再演示

下面说明TIMESTAMPDIFF函数的语法。

1	TIMESTAMPDIFF(unit,begin,end);

TIMESTAMPDIFF函数返回begin-end的结果，其中begin和end是DATE或DATETIME表达式。

TIMESTAMPDIFF函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。如果使用DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。

unit参数是确定(end-begin)的结果的单位，表示为整数。以下是有效单位：
MICROSECOND(微秒)、SECOND(秒)、MINUTE(分钟)、HOUR(小时)、DAY(天)、WEEK(星期)、MONTH(月)、QUARTER(季度)、YEAR(年)

类似的查询可用于确定死亡动物的死亡年龄。可以通过检查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 | 1990-01-01 | 1995-07-29 |    5 |
+--------+------------+------------+------+
1 row in set (0.00 sec)

MySQL提供了用于提取日期的部分，如一些功能 YEAR()， MONTH()和 DAYOFMONTH()。如下一个简单的查询birth和 MONTH(birth)：

mysql> select name,birth,month(birth) from pet; 
+--------+------------+--------------+
| name   | birth      | month(birth) |
+--------+------------+--------------+
| Fluffy | 1999-02-04 |            2 |
| Claws  | 1994-03-17 |            3 |
| Buffy  | 1989-05-13 |            5 |
| Fang   | 1997-02-22 |            2 |
| Bowser | 1990-01-01 |            1 |
| Chirpy | 1998-09-11 |            9 |
+--------+------------+--------------+
6 rows in set (0.00 sec)

mysql> select name,birth 
    -> from pet 
    -> where month(birth)=5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)

DATE_ADD()可以将时间间隔添加到给定日期。如果将值添加到CURDATE()，然后提取月份部分MONTH()，结果将生成查找生日的月份：

mysql> select name,birth from pet
    -> where month(birth)=month(date_add(curdate(),interval 2 month));
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)

6.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 |
+-----------+---------------+
1 row in set (0.00 sec)

任何算术比较NULL的结果也是NULL，所以你不能从这种比较中获得任何有意义的结果。
在MySQL中，0或NULL 为假，其他则为真。布尔运算的默认真值是1。

7.模式匹配

MySQL提供标准的SQL模式匹配以及基于扩展正则表达式的模式匹配，类似于Unix实用程序（如vi，grep和 sed）使用的扩展正则表达式。

SQL模式匹配可以使用 “_” 匹配任何单个字符并且%匹配任意数量的字符（包括零个字符）。在MySQL中，SQL模式默认情况下不区分大小写。使用SQL模式时不要使用 =或<>，改用LIKE或 NOT LIKE比较运算符。

查找以b（b%）开头。%fy（以fy结尾），%w%（包含w字符）

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    | 1990-01-01 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

要查找包含五个字符的名称：

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  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

MySQL提供的另一种模式匹配使用扩展的正则表达式。当为这种类型的模式测试匹配时，使用 REGEXP和NOT REGEXP运算符（或 RLIKE和 NOT RLIKE，它们是同义词）。

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

. 匹配任何单个字符。
字符类[…]匹配括号内的任何字符。例如， [abc]匹配a， b或c。[a-z] 匹配任何字母，而[0-9] 匹配任何数字。

*匹配前面事物的零个或多个实例。例如，x 匹配任意数量的x字符， [0-9]匹配任意数量的数字，.*匹配任意数量的任何字符。

如果模式与测试值中的任何位置匹配，则正则表达式模式匹配成功。（这与LIKE模式匹配不同，仅在模式与整个值匹配时才会成功。）
使用锚定模式以使其与要测试的值的开头或结尾匹配，请使用模式^开头或$结尾。

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    | 1990-01-01 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

8.计数行

COUNT(*)计算行数，因此计算动物数量的查询如下所示：

mysql> select count(*) from pet;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

计算每个人拥有几个宠物

mysql> select owner,count(*) from pet group by owner;
+--------+----------+
| owner  | count(*) |
+--------+----------+
| Benny  |        1 |
| Diane  |        1 |
| Genw   |        1 |
| Gwen   |        1 |
| Harold |        2 |
+--------+----------+
5 rows in set (0.00 sec)

上述查询使用GROUP BY对每个owner进行分组。使用COUNT()结合 GROUP BY在各种分组表征数据时非常有用。

想要得到每种性别的动物数量仅适用于已知性别的动物：

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 |
+---------+------+----------+
5 rows in set (0.00 sec)

9.使用多个表

pet 表记录了宠物的基本信息，还需要一张表记录他们生活中的事件，如下所示

mysql> create table event (name varchar(20),date date,type varchar(20),remark varchar(250));
Query OK, 0 rows affected (0.30 sec)
mysql> select * from event;                                          
+--------+------------+--------+-----------------+
| name   | date       | type   | remark          |
+--------+------------+--------+-----------------+
| Fluffy | 1995-05-15 | litter | 4,3female,1male |
| Buffy  | 1993-06-23 | litter | 4,3female,1male |
| Buffy  | 1994-06-19 | litter | 4,3female,1male |
| Chirpy | 1999-03-19 | daily  | go away         |
| Fang   | 1991-10-12 | kennel | NULL            |
| Bowser | 1991-10-12 | kennel | NULL            |
+--------+------------+--------+-----------------+
6 rows in set (0.00 sec)

假设你想要得到宠物下崽的年龄。宠物的下崽日期在 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 |    3 | 4,3female,1male |
| Buffy  |    4 | 4,3female,1male |
| Buffy  |    5 | 4,3female,1male |
+--------+------+-----------------+
3 rows in set (0.00 sec)

该FROM子句连接两个表，因为查询需要从两个表中提取信息

组合（连接）来自多个表的信息时，需要指定一个表中的记录如何与另一个表中的记录匹配。

该查询使用 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     |
+--------+------+-------+------+---------+
2 rows in set (0.00 sec)

在此查询中，我们为表名指定别名以引用列，并保持每个列引用与表关联的表的实例。