MySQL - 从表中查询数据信息

SELECT语句是SQL中用来查询表中数据的语句。其基本的语法格式：

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

what_to_select指明你想查看什么，可以是表中的列组成的列表或用*表示表中所有的列。which_table指明你想从哪个表中查询数据。where子句是可选的，如果有where子句，conditions_to_satisfy指定数据行需满足的一个或多个条件。

1. Selecting所有数据

mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL  |
| Puffball | Diane | hamster | f    | 1989-08-31 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)


mysql>

2. Selecting指定的行

查询name列为Bowser的行

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


mysql>

查询在1998年后出生的宠物的行

mysql> select * from pet where birth >= '1998-1-1';
+--------+-------+---------+------+------------+-------+
| name   | owner | species | sex  | birth      | death |
+--------+-------+---------+------+------------+-------+
| Chirpy | Gwen  | bird    | f    | 1998-09-11 | NULL  |
+--------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)


mysql>

合并查询条件，如查询母狗

mysql> select * from pet where sex = 'f' and species = 'dog';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)


mysql>

逻辑运算符OR

mysql> select * from pet where species = 'snake' or species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL  |
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)


mysql>

逻辑运算符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  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)


mysql>

3. Selecting指定的列

查看整列数据

mysql> select name, birth from pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Whistler | 0000-00-00 |
| Puffball | 1989-08-31 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1979-08-31 |
| Chirpy   | 1998-09-11 |
| Slim     | 1996-04-29 |
+----------+------------+
9 rows in set (0.00 sec)


mysql>

查看整列数据时去重

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


mysql>

使用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     | 1979-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)


mysql>

4. 排序

ORDER BY子句可以将结果排序

mysql> select name, birth from pet order by birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Whistler | 0000-00-00 |
| Bowser   | 1979-08-31 |
| Buffy    | 1989-05-13 |
| Puffball | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Chirpy   | 1998-09-11 |
+----------+------------+
9 rows in set (0.00 sec)


mysql>

字符类型的列默认的排序方式是不区分大小写，如果想使用区分大小写的方式，需要使用BINARY(格式：ORDER BY BINARY col_name)。

默认的排序方式是升序排列，最小的值在第一行，最大的值在最后一行。使用降序排列，需在排序的列名后增加DESC字段说明

mysql> select name, birth from pet order by birth desc;
+----------+------------+
| name     | birth      |
+----------+------------+
| Chirpy   | 1998-09-11 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Puffball | 1989-08-31 |
| Buffy    | 1989-05-13 |
| Bowser   | 1979-08-31 |
| Whistler | 0000-00-00 |
+----------+------------+
9 rows in set (0.00 sec)


mysql>

按多列字段排序并给每列字段指定不同的排序方向，如下：先是species字段升序排列，再在之前的基础上按birth字段倒序排列

mysql> select name, species, birth from pet order by species, birth desc;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 0000-00-00 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Buffy    | dog     | 1989-05-13 |
| Bowser   | dog     | 1979-08-31 |
| Puffball | hamster | 1989-08-31 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)


mysql>

由此可看出DESC只作用于它之前的一个列名指定的列。