简单查询

本章使用的案例数据库如下:

mysql> use aarondb;
Database changed

mysql> show tables;
+-------------------+
| Tables_in_aarondb |
+-------------------+
| department        |
| employ            |
| student           |
| user              |
+-------------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

employ 表结构如下：

mysql> desc employ;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(60) | YES  |     | NULL    |                |
| age         | int(11)     | YES  |     | NULL    |                |
| gender      | char(3)     | YES  |     | NULL    |                |
| birthday    | datetime    | YES  |     | NULL    |                |
| salary      | double      | YES  |     | NULL    |                |
| subsidy     | int(11)     | YES  |     | NULL    |                |
| overtimepay | int(11)     | YES  |     | NULL    |                |
| depart_id   | int(11)     | YES  | MUL | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

student 表结构如下：

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(60) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| chinese  | double      | YES  |     | NULL    |       |
| gender   | char(3)     | YES  |     | NULL    |       |
| birthday | datetime    | YES  |     | NULL    |       |
| math     | int(11)     | YES  |     | NULL    |       |
| english  | int(11)     | YES  |     | NULL    |       |
| address  | varchar(20) | YES  |     | NULL    |       |
| grade    | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 查询一个字段

mysql> select name from employ;
+------+
| name |
+------+
| 王维 |
| 李白 |
| 杜甫 |
| 郭靖 |
| 王良 |
| 刘邦 |
| 王维 |
+------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 查询多个字段

mysql> select name,age from employ;
+------+------+
| name | age  |
+------+------+
| 王维 |   33 |
| 李白 |   43 |
| 杜甫 |   34 |
| 郭靖 |   36 |
| 王良 |   66 |
| 刘邦 |   46 |
| 王维 |   33 |
+------+------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 查询所有字段

方法一：

mysql> select id,name,age,gender,birthday,salary,subsidy,overtimepay,depart_id from employ;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

方法二：

mysql> select * from employ;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在实际开发中不建议这样使用，在 DOS 命令窗口中想快速的看一看全表数据可以采用这种方式。

效率低
可读性差

# 查询一个字段并起别名

mysql> select birthday as shengri from employ;
+---------------------+
| shengri             |
+---------------------+
| 1923-01-01 00:00:00 |
| 1913-01-01 00:00:00 |
| 1929-01-01 00:00:00 |
| 1923-01-01 00:00:00 |
| 1943-01-01 00:00:00 |
| 1953-01-01 00:00:00 |
| 1923-01-01 00:00:00 |
+---------------------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 查询多个字段并起别名

mysql> select birthday as shengri,age as nianling from employ;
+---------------------+----------+
| shengri             | nianling |
+---------------------+----------+
| 1923-01-01 00:00:00 |       33 |
| 1913-01-01 00:00:00 |       43 |
| 1929-01-01 00:00:00 |       34 |
| 1923-01-01 00:00:00 |       36 |
| 1943-01-01 00:00:00 |       66 |
| 1953-01-01 00:00:00 |       46 |
| 1923-01-01 00:00:00 |       33 |
+---------------------+----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

as可以省略不写，所以下面的写法也是合法的。

# 起别名时 as 可以省略不写

mysql> select birthday shengri,age nianling from employ;
+---------------------+----------+
| shengri             | nianling |
+---------------------+----------+
| 1923-01-01 00:00:00 |       33 |
| 1913-01-01 00:00:00 |       43 |
| 1929-01-01 00:00:00 |       34 |
| 1923-01-01 00:00:00 |       36 |
| 1943-01-01 00:00:00 |       66 |
| 1953-01-01 00:00:00 |       46 |
| 1923-01-01 00:00:00 |       33 |
+---------------------+----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

别名含有空格，可以用单引号或者双引号括起来。建议使用单引号。

# 别名含有空格

mysql> select birthday 'sheng ri',age 'nian ling' from employ;
+---------------------+-----------+
| sheng ri            | nian ling |
+---------------------+-----------+
| 1923-01-01 00:00:00 |        33 |
| 1913-01-01 00:00:00 |        43 |
| 1929-01-01 00:00:00 |        34 |
| 1923-01-01 00:00:00 |        36 |
| 1943-01-01 00:00:00 |        66 |
| 1953-01-01 00:00:00 |        46 |
| 1923-01-01 00:00:00 |        33 |
+---------------------+-----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

别名是中文的，也可以用单引号括起来。

# 别名含有中文

mysql> select birthday '生日',age '年龄' from employ;
+---------------------+------+
| 生日                | 年龄 |
+---------------------+------+
| 1923-01-01 00:00:00 |   33 |
| 1913-01-01 00:00:00 |   43 |
| 1929-01-01 00:00:00 |   34 |
| 1923-01-01 00:00:00 |   36 |
| 1943-01-01 00:00:00 |   66 |
| 1953-01-01 00:00:00 |   46 |
| 1923-01-01 00:00:00 |   33 |
+---------------------+------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

单引号与双引号区别

在所有的数据库中，字符串统一使用单引号括起来。单引号是标准，双引号在 oracle 中就使用不了，但是 mysql 可以使用。

# 条件查询

提示

不是将表中的数据都查出来，是查询符合条件的数据。

=   等于
<>或!=  不等于
<   小于
<=  小于等于
>   大于
>=  大于等于
between ... and ...   两个值之间，等同于>= and <=
is null   为 null (is not null 不为空)
and   并且
or  或者
in  包含，相当于多个 or (not in 不在这个范围中)
not   not 可以取非，主要用在 is 或 in 中
like  like 称为模糊查询，支持 % 或 _ 匹配。% 匹配任意个字符，_匹配以个字符。

1
2
3
4
5
6
7
8
9
10
11
12
13

employ 案例表中的数据显示如下：

mysql> select * from employ;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# = 查询

mysql> select * from employ where age = 66;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# != 查询

mysql> select * from employ where age != 66;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

# 不等于 <> 查询

mysql> select * from employ where age <> 66;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

# 大于> 查询

mysql> select * from employ where age > 40;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 小于< 查询

mysql> select * from employ where age < 40;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

# 小于等于<= 查询

mysql> select * from employ where age <= 34;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  1 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  7 | 王维 |   33 | 男     | 1923-01-01 00:00:00 |     98 |      87 |          98 |         1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 大于等于>= 查询

mysql> select * from employ where age >= 34;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# 大于等于小于等于>= and <= 查询

mysql> select * from employ where age >= 34 and age <=66;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# between and 查询

mysql> select * from employ where age between 34 and 66;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age  | gender | birthday            | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
|  2 | 李白 |   43 | 女     | 1913-01-01 00:00:00 |     88 |      89 |          98 |         2 |
|  3 | 杜甫 |   34 | 男     | 1929-01-01 00:00:00 |     96 |      79 |          98 |         1 |
|  4 | 郭靖 |   36 | 男     | 1923-01-01 00:00:00 |     96 |      99 |          98 |         2 |
|  5 | 王良 |   66 | 女     | 1943-01-01 00:00:00 |     90 |      88 |          98 |         1 |
|  6 | 刘邦 |   46 | 男     | 1953-01-01 00:00:00 |     93 |      88 |          98 |         2 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

student 案例表中的数据显示如下：

mysql> select * from student;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

# is null 查询

mysql> select * from student where address is null;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# is not null 查询

mysql> select * from student where address is not null;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# and 查询

mysql> select * from student where address is not null and math >=96;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# or 查询

mysql> select * from student where address is not null or math >=96;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# and or 查询：同时存在 and 优先

mysql> select * from student where gender = '男' and address = '上海' or address = '天津';
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# and or 查询：带括号的优先

mysql> select * from student where gender = '男' and (address = '上海' or address = '天津');
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8

# in 查询

mysql> select * from student where address in ('天津','上海');
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

# not in 查询

mysql> select * from student where address not in ('天津','上海');
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# like % 查询

mysql> select * from student where name like '王%';
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8

# like _ 查询

mysql> select * from student where address like '_津';
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 升序排序查询

mysql> select * from student order by math asc;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

默认是升序，asc 可以忽略不写。

# 降序排序查询

mysql> select * from student order by math desc;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

# 多个排序条件查询，第一个条件不能区分时使用第二个条件，依此类推

mysql> select * from student order by math desc,english desc;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

先按照 math 降序排列，如果有相同的再按照 english 降序排列。

# 按照某个列数排序

mysql> select * from student order by 3;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    1 | 王维 |   33 |     100 | 男     | 1923-01-01 00:00:00 |   98 |      87 | 天津    | 三年级1班 |
|    3 | 杜甫 |   34 |      98 | 男     | 1929-01-01 00:00:00 |   96 |      79 | 天津    | 三年级3班 |
|    4 | 郭靖 |   36 |      97 | 男     | 1923-01-01 00:00:00 |   96 |      99 | 北京    | 三年级1班 |
|    2 | 李白 |   43 |      99 | 女     | 1913-01-01 00:00:00 |   88 |      89 | 上海    | 三年级2班 |
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

3 代指按照第 3 列排序。开发中不建议这样写，因为不健壮。因为列的顺序容易发生变化。

# 带 where 条件的排序查询

mysql> select * from student where math between 90 and 95 order by math desc;
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
| id   | name | age  | chinese | gender | birthday            | math | english | address | grade     |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
|    6 | 刘邦 |   46 |      95 | 男     | 1953-01-01 00:00:00 |   93 |      88 | NULL    | 三年级2班 |
|    5 | 王良 |   66 |      97 | 女     | 1943-01-01 00:00:00 |   90 |      88 | 天津    | 三年级3班 |
+------+------+------+---------+--------+---------------------+------+---------+---------+-----------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8

编辑

#MYSQL

上次更新: 2025/02/10, 20:20:37

← 常用命令函数→