目录
2、条件查询运算符详解(=、<、>、>=、<=、<>、!=)
8、is null/is not null(NULL值专用查询)
环境:mysql5.7.30,cmd命令中进⾏演⽰。
电商中:我们想查看某个⽤户所有的订单,或者想查看某个⽤户在某个时间段内所有的订单,此时我们需要对订单表数据进⾏筛选,按照⽤户、时间进⾏过滤,得到我们期望的结果。
此时我们需要使⽤条件查询来对指定表进⾏操作,我们需要了解sql中的条件查询常见的玩法。
1、条件查询语法
语法:
select 列名 from 表名 where 列 运算符 值
说明:
注意关键字where,where后⾯跟上⼀个或者多个条件,条件是对前⾯数据的过滤,只有满⾜where后⾯条件的数据才会被返回
2、条件查询运算符详解(=、<、>、>=、<=、<>、!=)
2.1、等于(=)
select 列名 from 表名 where 列 = 值;
说明:
查询出指定的列和对应的值相等的记录。
值如果是字符串类型,需要⽤单引号或者双引号引起来。
示例:
mysql> create table test1(a int,b varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test1 values(1,'abc'),(2,'bbb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | abc |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where a=2;
+------+------+
| a | b |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where b='abc';
+------+------+
| a | b |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.04 sec)
mysql> select * from test1 where b='abc';
+------+------+
| a | b |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
mysql>
2.2、不等于(<>、!=)
不等于有两种写法:<>或者!=
select 列名 from 表名 where 列 <> 值;
或者
select 列名 from 表名 where 列 != 值;
示例:
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | abc |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where a<>1;
+------+------+
| a | b |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where a!=1;
+------+------+
| a | b |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
mysql>
注意:
- <> 这个是最早的⽤法。
- !=是后来才加上的。
- 两者意义相同,在可移植性上前者优于后者
- 故⽽sql语句中尽量使⽤<>来做不等判断
2.3、⼤于(>)
select 列名 from 表名 where 列 > 值;
示例:
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | abc |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where a>1;
+------+------+
| a | b |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test1 where b>'a';
+------+------+
| a | b |
+------+------+
| 1 | abc |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 where b>'ac';
+------+------+
| a | b |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
mysql>
说明:
- 数值按照⼤⼩⽐较。
- 字符按照ASCII码对应的值进⾏⽐较,⽐较时按照字符对应的位置⼀个字符⼀个字符的⽐较。
3、逻辑查询运算符详解(and、or)
当我们需要使⽤多个条件进⾏查询的时候,需要使⽤逻辑查询运算符。
3.1、AND(并且)
select 列名 from 表名 where 条件1 and 条件2;
表⽰返回满⾜条件1和条件2的记录。
示例:
mysql> create table test3(
-> a int not null,
-> b varchar(10) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | | NULL | |
| b | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test3(a,b) values(1,'a'),(2,'b'),(2,'c'),(3,'c');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 2 | c |
| 3 | c |
+---+---+
4 rows in set (0.00 sec)
mysql> select * from test3 t where t.a=2 and t.b='c';
+---+---+
| a | b |
+---+---+
| 2 | c |
+---+---+
1 row in set (0.01 sec)
mysql>
查询出了a=2 并且 b='c'的记录,返回了⼀条结果。
3.2、OR(或者)
select 列名 from 表名 where 条件1 or 条件2;
满⾜条件1或者满⾜条件2的记录都会被返回。
示例:
mysql> select * from test3;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
| 2 | c |
| 3 | c |
+---+---+
4 rows in set (0.00 sec)
mysql> select * from test3 t where t.a=1 or t.b='c';
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | c |
| 3 | c |
+---+---+
3 rows in set (0.00 sec)
mysql>
查询出了a=1或者b='c'的记录,返回了3条记录。
4、like(模糊查询)
有个学⽣表,包含(学⽣id,年龄,姓名),当我们需要查询姓“张”的学⽣的时候,如何查询呢?
此时我们可以使⽤sql中的like关键字。语法:
select 列名 from 表名 where 列 like pattern;
- pattern中可以包含通配符,有以下通配符:
- %:表⽰匹配任意⼀个或多个字符
- _:表⽰匹配任意⼀个字符。
1)学⽣表,查询名字姓“张”的学⽣,如下:
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.03 sec)
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | smallint(6) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into stu values (1,22,'张三'),(2,25,'李四'),(3,26,'张学友'),(4,32,'刘德华'),(5,55,'张学良');
Query OK, 5 rows affected (0.04 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>
2)查询名字中带有'学'的学⽣,'学'的位置不固定,可以这么查询,如下:
mysql> select * from stu a where a.name like '%学%';
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 3 | 26 | 张学友 |
| 5 | 55 | 张学良 |
+----+-----+-----------+
2 rows in set (0.00 sec)
mysql>
3)查询姓'张',名字2个字的学⽣:
mysql> select * from stu a where a.name like '张_';
+----+-----+--------+
| id | age | name |
+----+-----+--------+
| 1 | 22 | 张三 |
+----+-----+--------+
1 row in set (0.00 sec)
mysql>
上⾯的_代表任意⼀个字符,如果要查询姓'张'的3个字的学⽣,条件变为了'张_ _',2个下划线符号。
5、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.01 sec)
mysql>
mysql> select * from stu t where t.age >= 25 and t.age <=32;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 2 | 25 | 李四 |
| 3 | 26 | 张学友 |
| 4 | 32 | 刘德华 |
+----+-----+-----------+
3 rows in set (0.00 sec)
mysql>
6、 in, not in查询
1)IN查询
我们需要查询年龄为10岁、15岁、20岁、30岁的⼈,怎么查询呢?可以⽤or查询,如下:
mysql> create table test6(id int,age smallint);
Query OK, 0 rows affected (0.05 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.01 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;
+------+------+
| id | age |
+------+------+
| 1 | 14 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 28 |
| 6 | 10 |
| 7 | 10 |
| 8 | 30 |
+------+------+
8 rows in set (0.01 sec)
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.04 sec)
mysql>
相对于or简洁了很多。
2)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;
+------+------+
| 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 not in(10,15,20,30);
+------+------+
| id | age |
+------+------+
| 1 | 14 |
| 3 | 18 |
| 5 | 28 |
+------+------+
3 rows in set (0.00 sec)
mysql>
7、NULL值存在的坑
我们先看⼀下效果,然后再解释,⽰例如下:
mysql> create table test5(a int not null,b int,c varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> desc test5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | | NULL | |
| b | int(11) | YES | | NULL | |
| c | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (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: 0
mysql> select * from test5;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 2 | a |
| 3 | NULL | b |
| 4 | 5 | NULL |
+---+------+------+
3 rows in set (0.00 sec)
mysql>
上⾯我们创建了⼀个表test5,3个字段,a不能为空,b、c可以为空,插⼊了3条数据,看效果:
mysql> select * from test5;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 2 | a |
| 3 | NULL | b |
| 4 | 5 | NULL |
+---+------+------+
3 rows in set (0.00 sec)
mysql> select * from test5 where b>0;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 2 | a |
| 4 | 5 | NULL |
+---+------+------+
2 rows in set (0.04 sec)
mysql> select * from test5 where b<0;
Empty set (0.00 sec)
mysql> select * from test5 where b=NULL;
Empty set (0.01 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.04 sec)
mysql> select * from test5 where c like '%';
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 2 | a |
| 3 | NULL | b |
+---+------+------+
2 rows in set (0.04 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)
mysql>
认真看⼀下上⾯的查询:
上⾯带有条件的查询,对字段b进⾏条件查询的,b的值为NULL的都没有出现。对c字段进⾏like '%'查询、in、not查询,c中为NULL的记录始终没有查询出来。between and查询,为空的记录也没有查询出来。
结论:查询运算符、like、between and、in、not in对NULL值查询不起效。
那NULL如何查询呢?继续向下看
8、is null/is not null(NULL值专用查询)
上⾯介绍的各种运算符对NULL值均不起效,mysql为我们提供了查询空值的语法:IS NULL、IS NOT NULL。
1)IS NULL(返回值为空的记录)
select 列名 from 表名 where 列 is null;
查询指定的列的值为NULL的记录。
如:
mysql> create table test7(a int,b varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test7(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: 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)
mysql>
2)IS NOT NULL(返回值不为空的记录)
select 列名 from 表名 where 列 is not null;
查询指定的列的值不为NULL的记录。
如:
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 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)
mysql>
9、<=>(安全等于)运算符
<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低,⽤得较少
示例:
mysql> create table test8(a int,b varchar(10));
Query OK, 0 rows affected (0.03 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: 0
mysql> 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)
mysql>
可以看到<=>可以将NULL查询出来。
10、经典面试题
下⾯的2个sql查询结果⼀样么?
select * from students;
select * from students where name like '%';
结果分2种情况:
当name没有NULL值时,返回的结果⼀样。
当name有NULL值时,第2个sql查询不出name为NULL的记录。
11、总结
- like中的%可以匹配⼀个到多个任意的字符,_可以匹配任意⼀个字符
- 空值查询需要使⽤IS NULL或者IS NOT NULL,其他查询运算符对NULL值⽆效
- 建议创建表的时候,尽量设置表的字段不能为空,给字段设置⼀个默认值
- <=>(安全等于)玩玩可以,建议少使⽤