MySQL学习笔记3

自定义从表中取回的信息

The SELECT statement is used to pull information from a table. The general form of the statement is:

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

使用WHERE指定筛选条件

官网文档的示例中，有下表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       |
+----------+--------+---------+------+------------+------------+

选择特定行

You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser’s birth date, select Bowser’s record like this:

SELECT * FROM pet WHERE name = 'Bowser';

输出为：

+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

更复杂的示例使用了逻辑运算符：

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

还有的指定日期范围的，比如：

SELECT * FROM pet WHERE birth >= '1998-1-1';

选择特定列

比如，只选择某个栏目：

SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

为了使重复的项目只出现一次，可以使用DISTINCT关键字：

SELECT DISTINCT owner FROM pet;

排序等其他选项

• 使用ORDER BY进行排序。例如：

SELECT name, birth FROM pet ORDER BY birth;

默认升序排列，可以使用DESC降序排列，比如：

SELECT name, birth FROM pet ORDER BY birth DESC;

根据多个栏目排序，比如：

SELECT name, species, birth FROM pet
ORDER BY species, birth DESC;

• 日期差值计算

使用TIMESTAMPDIFF()函数进行日期差值计算，其中，CURDATE()函数代入当前日期。

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

使用IS NOT NULL，只计算已经死亡宠物的年龄：

SELECT name, birth, death,
    -> TIMESTAMPDIFF(YEAR,birth,death) AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

假设当前月份是4月，查找下个月生日的宠物：

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

计算日期差值的复杂例子：

假设当前月份是12月，你想查一下在下个月生日的宠物，使用WHERE MONTH(birth) =13进行筛选是不可行的，可以使用：

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

另一种实现的方法是利用了余数：

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