Mysql 中文参考手册（摘录4） --MySQL操作之数据加载,检索，查询（2）

加载数据到表中

创建表之后，你需要填充数据，你可以通过LOAD DATA 和 INSERT来实现。

数据格式如下

whistler        Gwen        bird        \N        1997-12-09        \N

列之间使用 \t 间隔 （LOAD DATA 默认的列间间隔符），\N表示NULL。

加载文件pet.text中的数据到表pet中，使用以下命令：

mysql> LOAD DATA LOCAL INFILE 'path/pet.txt' INTO TABLE pet;

如果pet.txt文件是在Windows下编辑的，则换行符为\r\n，需要指定\r\n为行终止 符，如下所示：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';

 （如果是在苹果系统中，你可能需要使用\r行终止符。）

在LOAD DATA语句中，你可以显示指定列间隔符和行终止符。默认的列间隔符为 TAB，行终止符为换行。

当你想新增一条数据时，可以使用INSERT语句：

mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

 检索数据

SELECT语句用于从表中检索数据，通常格式如下：

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select 表示你想看到的数据，可以是具体列，也可以是*(代表所有列)。

WHERE 是可选的，如果有的话，conditions_to_satisfy表示指定一个或多个行应该满足的条件。

查询所有数据

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

查询指定行数据

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

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

行排序(ASC),以下是按降序(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 |
+----------+------------+

日期计算

使用TIMESTAMPDIFF查询宠物年龄：

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

查询death不为NULL，按年龄升序排列：ORDER BY 默认升序

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是一个特殊的 值，不能使用常规的方法来比较。

查询出年日期是5月份的宠物：

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

查询下一个月过生日的宠物名称和生日，其中DATE_ADD表示日期加，在这里表 示，在当前日期上再加一个月的间隔，也就是，如果当前日期为2016-7-30,则加一 个月间隔就是8月份。为什么要这么加呢？大家知道，如果直接加的话，到了12月 就变成13月了，明显这不合理，当然，我们也可以通过MOD方法来做到12月到1月 的过渡。

mysql> SELECT name, birth FROM pet 
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));//当前时间加上一个月的区间。

mysql> SELECT name, birth FROM pet 
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1; //MOD取余后加一