13.2.9 单表查询2 where子句 order by子句

发表于 更新于 分类于 ， ， Waline： 本文字数： 11k 阅读时长 ≈ 10 分钟

where子句

前面已经看到了**where子句的作用——可以控制只选择指定的行。因为where子句里包含的是一个条件表达式，所以可以使用>、>=、<、<=、=和<>等基本的比较运算符。SQL中的比较运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小。
注意
SQL中判断两个值是否相等的比较运算符是单等号**,判断不相等的运算符是<>;SQL中的赋值运算符不是等号,而是冒号等号(:=)。
除此之外，SQL还支持如表13.3所示的特殊的比较运算符。

表13.3特殊的比较运算符
运算符 含义
表达式1 between表达式2 and 表达式3 要求表达式1>=表达式2,并且表达式1<=表达式3
表达式1 in(表达式2,表达式2,表达式3,表达式4,...) 要求表达式1等于后面括号里任意一个表达式的值就行,括号中的表达式可以有多个,但只要与其中一个相等即可
like 字符串匹配,like后面的字符串支持通配符
is null 要求制定值等于null

between运算符

下面的SQL语句选出student_id大于等于2，且student_id小于等于4的所有记录。

1
2
select * from student_table
where student_id between 2 and 4;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_id between 2 and 4;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
+------------+--------------+--------------+
3 rows in set

使用between value1 and value2必须保证value1小于value2，否则将选不出任何记录。除此之外，between value1 and value2中的两个值不仅可以是常量，也可以是变量，或者是列名也行。
如下SQL语句选出java_teacher小于等于2，student_id大于等于2的所有记录。

1
2
3
4
select * from student_table
# 要求java_teacher<=2,并且2<=student_id
# 也就是java_teacher<=2,并且student_id>=2
where 2 between java_teacher and student_id;

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 要求java_teacher<=2,并且2<=student_id
# 也就是java_teacher<=2,并且student_id>=2
where 2 between java_teacher and student_id;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
+------------+--------------+--------------+
5 rows in set
mysql>

in运算符

使用in比较运算符时，必须在in后的括号里列出一个或多个值，它要求指定列必须与in括号里任意一个值相等。如下SQL语句所示：

1
2
3
select * from student_table
# 要求student_id等于2或者student_id等于4即可
where student_id in(2,4);

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 要求student_id等于2或者student_id等于4即可
where student_id in(2,4);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          4 | 王五         |            2 |
+------------+--------------+--------------+
2 rows in set

与之类似的是，in括号里的值既可以是常量，也可以是变量或者列名，如下SQL语句所示：

1
2
3
4
select * from student_table
# 要求2等于student_id或者2等于java_teacher
# 也就是student_id等于2或者java_teacher等于2
where 2 in(student_id,java_teacher);

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where 2 in(student_id,java_teacher);
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          2 | 张三         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
+------------+--------------+--------------+
4 rows in set

like运算符

like运算符主要用于进行模糊查询，例如，若要查询名字以“张”开头的所有记录，这就需要用到模糊查询，在模糊查询中需要使用like关键字。

通配符

SQL语句中可以使用两个通配符：下画线（_）和百分号（%），其中**下画线可以代表一个任意的字符，百分号可以代表任意多个字符**。如下SQL语句将查询出所有学生中名字以“张”开头的学生。

查询性张的学生

1
2
select * from student_table
where student_name like '张%';

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '张%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
+------------+--------------+--------------+
2 rows in set

两个名字的学生

下面的SQL语句将查询出名字为两个字符的所有学生。

1
2
select * from student_table
where student_name like '__';

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '__';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
5 rows in set

转义字符

反斜线转义

在某些特殊的情况下,查询的条件里需要使用下画线或百分号,不希望SQL把下画线和百分号当成通配符使用,这就需要使用转义字符,MySQL使用反斜线(\)作为转义字符,如下SQL语句所示:

1
2
3
#选出所有名字以下划线开头的学生.
select * from student_table
where student_name like '\_%';

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '\_%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          5 | _王五        |            2 |
+------------+--------------+--------------+
1 row in set

使用escape关键字自定义转义字符

标准SQL语句并没有提供反斜线(\)的转义字符,而是使用escape关键字显式进行转义。例如,为了实现上面功能需要使用如下SQL语句:

1
2
3
4
#选出所有名字以下划线开头的学生
select * from student_table
# 自定义`/`作为转义字符
where student_name like '/_%' escape '/';

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
# 自定义`/`作为转义字符
where student_name like '/_%' escape '/';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          5 | _王五        |            2 |
+------------+--------------+--------------+

is null

is null用于判断某些值是否为空,判断是否为空不要用=null来判断,因为SQL中null=null返回null。如下SQL语句将选择出student_table表中student_name为null的所有记录。

1
2
select * from student_table
where student_name is null;

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name is null;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          6 | NULL         |            2 |
+------------+--------------+--------------+
1 row in set

and or not运算符

如果where子句后有多个条件需要组合,SQL提供了and和or逻辑运算符来组合两个条件,并提供了not来对逻辑表达式求否。如下SQL语句将选出学生名字为2个字符,且student_id大于3的所有记录。

1
2
select * from student_table
where student_name like '__' and student_id >3;

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where student_name like '__' and student_id >3;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
2 rows in set

下面的SQL语句将选出student_table表中姓名不以下画线开头的所有记录。

1
2
select * from student_table
where not student_name like '\_%';

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
where not student_name like '\_%';
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
5 rows in set

比较运算符 逻辑运算符的优先级

当使用比较运算符、逻辑运算符来连接表达式时,必须注意这些运算符的优先级。SQL中比较运算符、逻辑运算符的优先级如表13.4所示。

表13.4 `SQL`中比较运算符、逻辑运算符的优先级
运算符 优先级(越小越优先)
所有的比较运算符 1
not 2
and 3
or 4

如果SQL代码需要改变优先级的默认顺序,则可以使用括号,括号的优先级比所有的运算符高。如下SQL语句使用括号来改变逻辑运算符的优先级。

1
2
3
4
select * from student_table
# 使用括号强制先计算or运算
where (student_id>3 or student_name>'张')
    and java_teacher>1;

order by子句

执行查询后的查询结果默认按插入顺序排列;如果需要查询结果按某列值的大小进行排序,则可以使用order by子句。order by子句的语法格式如下:

1
order by column_name1 [desc],column_name2...

升序排列

进行排序时默认按升序排列

降序排列

如果强制按降序排列,则需要在列后使用**desc关键字**(升序排列的是asc关键字,用不用该关键字的效果完全一样,因为默认是按升序排列)。
上面语法中**设定排序列时可采用列名、列序号和列别名**。如下SQL语句选出student_table表中的所有记录,选出后按java_teacher列的升序排列。

1
2
select * from student_table
order by java_teacher;

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
order by java_teacher;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set

按多列排序

如果需要按多列排序,则每列的asc、desc必须单独设定。如果指定了多个排序列,则第一个排序列是首要排序列,只有当第一列中存在多个相同的值时,第二个排序列才会起作用。如下SQL语句先按java_teacher列的降序排列,当java_teacher列的值相同时按student_name列的升序排列。

1
2
select * from student_table
order by java_teacher desc,student_name;

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from student_table
order by java_teacher desc,student_name;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          7 | 赵六         |            3 |
|          6 | NULL         |            2 |
|          5 | _王五        |            2 |
|          4 | 王五         |            2 |
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
+------------+--------------+--------------+
7 rows in set