MySQL数据库学习，详解select条件查询(二)

like（模糊查询）

有个学⽣表，包含（学⽣id，年龄，姓名），当我们需要查询姓“张”的学⽣的时候，如何

查询呢？

此时我们可以使⽤sql中的like关键字。语法：

select 列名 from 表名 where 列 like pattern;

pattern中可以包含通配符，有以下通配符：

%：表⽰匹配任意⼀个或多个字符

_：表⽰匹配任意⼀个字符。

学⽣表，查询名字姓“张”的学⽣，如下：

mysql> create table stu (id int not null comment '编号',age smallint

not null comment '年龄',name varchar(10) not null comment '姓名');

Query OK, 0 rows affected (0.01 sec)mysql> insert into stu values (1,22,'张三'),(2,25,'李四'),(3,26,'张学

友'),(4,32,'刘德华'),(5,55,'张学良');

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from stu;

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 1 | 22 | 张三 |

| 2 | 25 | 李四 |

| 3 | 26 | 张学友 |

| 4 | 32 | 刘德华 |

| 5 | 55 | 张学良 |

+----+-----+-----------+

5 rows in set (0.00 sec)

mysql> select * from stu a where a.name like '张%';

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 1 | 22 | 张三 |

| 3 | 26 | 张学友 |

| 5 | 55 | 张学良 |

+----+-----+-----------+

3 rows in set (0.00 sec)

查询名字中带有'学'的学⽣，'学'的位置不固定，可以这么查询，如下：

mysql> select * from stu a where a.name like '%学%'; ;

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 3 | 26 | 张学友 |

| 5 | 55 | 张学良 |

+----+-----+-----------+

2 rows in set (0.00 sec)

查询姓'张'，名字2个字的学⽣：mysql> select * from stu a where a.name like '张_';

+----+-----+--------+

| id | age | name |

+----+-----+--------+

| 1 | 22 | 张三 |

+----+-----+--------+

1 row in set (0.00 sec)

上⾯的_代表任意⼀个字符，如果要查询姓'张'的3个字的学⽣，条件变为了'张

__'，2个下划线符号。

BETWEEN AND(区间查询)

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围，这些值可以是数值、⽂本或

者⽇期，属于⼀个闭区间查询。

selec 列名 from 表名 where 列名 between 值1 and 值2;

返回对应的列的值在[值1,值2]区间中的记录

使⽤between and可以提⾼语句的简洁度

两个临界值不要调换位置，只能是⼤于等于左边的值，并且⼩于等于右边的值。

⽰例：

查询年龄在[25,32]的，如下：

mysql> select * from stu;

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 1 | 22 | 张三 |

| 2 | 25 | 李四 |

| 3 | 26 | 张学友 |

| 4 | 32 | 刘德华 |

| 5 | 55 | 张学良 |

+----+-----+-----------+

5 rows in set (0.00 sec)mysql> select * from stu t where t.age between 25 and 32;

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 2 | 25 | 李四 |

| 3 | 26 | 张学友 |

| 4 | 32 | 刘德华 |

+----+-----+-----------+

3 rows in set (0.00 sec)

下⾯两条sql效果⼀样

select * from stu t where t.age between 25 and 32;

select * from stu t where t.age >= 25 and t.age <= 32;

IN查询

我们需要查询年龄为10岁、15岁、20岁、30岁的⼈，怎么查询呢？可以⽤or查询，如

下：

mysql> create table test6(id int,age smallint);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test6 values(1,14),(2,15),(3,18),(4,20),(5,28),

(6,10),(7,10),(8,30);

Query OK, 8 rows affected (0.00 sec)

Records: 8 Duplicates: 0 Warnings: 0

mysql> select * from test6;

+------+------+

| id | age |

+------+------+

| 1 | 14 |

| 2 | 15 |

| 3 | 18 |

| 4 | 20 |

| 5 | 28 || 6 | 10 |

| 7 | 10 |

| 8 | 30 |

+------+------+

8 rows in set (0.00 sec)

mysql> select * from test6 t where t.age=10 or t.age=15 or t.age=20 or

t.age = 30;

+------+------+

| id | age |

+------+------+

| 2 | 15 |

| 4 | 20 |

| 6 | 10 |

| 7 | 10 |

| 8 | 30 |

+------+------+

5 rows in set (0.00 sec)

⽤了这么多or，有没有更简单的写法？有，⽤IN查询

IN 操作符允许我们在 WHERE ⼦句中规定多个值。

select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

in 后⾯括号中可以包含多个值，对应记录的字段满⾜in中任意⼀个都会被返回

in列表的值类型必须⼀致或兼容

in列表中不⽀持通配符。

上⾯的⽰例⽤IN实现如下：

mysql> select * from test6 t where t.age in (10,15,20,30);

+------+------+

| id | age |

+------+------+

| 2 | 15 |

| 4 | 20 |

| 6 | 10 || 7 | 10 |

| 8 | 30 |

+------+------+

5 rows in set (0.00 sec)

相对于or简洁了很多。

NOT IN查询

not in和in刚好相反，in是列表中被匹配的都会被返回，NOT IN是和列表中都不匹配的会

被返回。

select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);

如查询年龄不在10、15、20、30之内的，如下：

mysql> select * from test6 t where t.age not in (10,15,20,30);

+------+------+

| id | age |

+------+------+

| 1 | 14 |

| 3 | 18 |

| 5 | 28 |

+------+------+

3 rows in set (0.00 sec)

NULL存在的坑

我们先看⼀下效果，然后在解释，⽰例如下：

mysql> create table test5 (a int not null,b int,c varchar(10));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 values (1,2,'a'),(3,null,'b'),(4,5,null);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test5;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | a |

| 3 | NULL | b |

| 4 | 5 | NULL |

+---+------+------+

3 rows in set (0.00 sec)

上⾯我们创建了⼀个表test5，3个字段，a不能为空，b、c可以为空，插⼊了3条数据，睁

⼤眼睛看效果了：

mysql> select * from test5 where b>0;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | a |

| 4 | 5 | NULL |

+---+------+------+

2 rows in set (0.00 sec)

mysql> select * from test5 where b<=0;

Empty set (0.00 sec)

mysql> select * from test5 where b=NULL;

Empty set (0.00 sec)

mysql> select * from test5 t where t.b between 0 and 100;

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | a |

| 4 | 5 | NULL |

+---+------+------+

2 rows in set (0.00 sec)

mysql> select * from test5 where c like '%';

+---+------+------+

| a | b | c |+---+------+------+

| 1 | 2 | a |

| 3 | NULL | b |

+---+------+------+

2 rows in set (0.00 sec)

mysql> select * from test5 where c in ('a','b',NULL);

+---+------+------+

| a | b | c |

+---+------+------+

| 1 | 2 | a |

| 3 | NULL | b |

+---+------+------+

2 rows in set (0.00 sec)

mysql> select * from test5 where c not in ('a','b',NULL);

Empty set (0.00 sec)

认真看⼀下上⾯的查询：

上⾯带有条件的查询，对字段b进⾏条件查询的，b的值为NULL的都没有出现。

对c字段进⾏like '%'查询、in、not查询，c中为NULL的记录始终没有查询出来。

between and查询，为空的记录也没有查询出来。

结论：查询运算符、like、between and、in、not in对NULL值查询不起效。

那NULL如何查询呢？继续向下看

IS NULL/IS NOT NULL（NULL值专⽤查询）

上⾯介绍的各种运算符对NULL值均不起效，mysql为我们提供了查询空值的语法：IS

NULL、IS NOT NULL。

IS NULL（返回值为空的记录）

select 列名 from 表名 where 列 is null;

查询指定的列的值为NULL的记录。如：

mysql> create table test7 (a int,b varchar(10));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test7 (a,b) values (1,'a'),(null,'b'),(3,null),

(null,null),(4,'c');

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test7;

+------+------+

| a | b |

+------+------+

| 1 | a |

| NULL | b |

| 3 | NULL |

| NULL | NULL |

| 4 | c |

+------+------+

5 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is null;

+------+------+

| a | b |

+------+------+

| NULL | b |

| NULL | NULL |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is null or t.b is null;

+------+------+

| a | b |

+------+------+

| NULL | b |

| 3 | NULL |

| NULL | NULL |

+------+------+

3 rows in set (0.00 sec)IS NULL（返回值不为空的记录）

select 列名 from 表名 where 列 is not null;

查询指定的列的值不为NULL的记录。

如：

mysql> select * from test7 t where t.a is not null;

+------+------+

| a | b |

+------+------+

| 1 | a |

| 3 | NULL |

| 4 | c |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is not null and t.b is not

null;

+------+------+

| a | b |

+------+------+

| 1 | a |

| 4 | c |

+------+------+

2 rows in set (0.00 sec)

<=>（安全等于）

<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低，⽤得较少

⽰例：

mysql> create table test8 (a int,b varchar(10));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test8 (a,b) values (1,'a'),(null,'b'),(3,null),

(null,null),(4,'c');

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0mysql> select * from test8;

+------+------+

| a | b |

+------+------+

| 1 | a |

| NULL | b |

| 3 | NULL |

| NULL | NULL |

| 4 | c |

+------+------+

5 rows in set (0.00 sec)

mysql> select * from test8 t where t.a<=>null;

+------+------+

| a | b |

+------+------+

| NULL | b |

| NULL | NULL |

+------+------+

2 rows in set (0.00 sec)

mysql> select * from test8 t where t.a<=>1;

+------+------+

| a | b |

+------+------+

| 1 | a |

+------+------+

1 row in set (0.00 sec)

可以看到<=>可以将NULL查询出来。