条件查询Where
2019-08-03 本文已影响0人
御都
分组前的查询条件Where，where条件语句中常见运算符如下：
1 >,<，>=,<=,<>(!=)
mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)
mysql> select * from employee where age!=23;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
+----+------+------+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from employee where age<>23;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
+----+------+------+--------+--------+--------+
9 rows in set (0.00 sec)
2 Between...and...查询一个区间
查询年龄在[20,30]范围内的员工信息
mysql> select * from employee where age between 20 and 30;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
+----+------+------+--------+--------+--------+
8 rows in set (0.00 sec)
mysql>
3 IN 和 NOT IN
关键词 IN 和 NOT IN 的作用和它们的名字一样明显，用于筛选“在”或“不在”某个范围内的结果
mysql> select * from employee where in_dpt in('dpt1','dpt3');
--查询在dpt1或者dpt3的员工信息
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
mysql> select * from employee where in_dpt not in('dpt1','dpt3');
---查询不在dpt1或者dpt3的员工的信息
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
+----+------+------+--------+--------+--------+
6 rows in set (0.00 sec)
4 IS NULL 为空，IS NOT NULL非空
mysql中不能用=null，判断为空，要用is null
查询age为null的员工信息
mysql> select * from employee where age is null;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from employee where age is not null;
--查询age非空的员工信息
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
+----+------+------+--------+--------+--------+
10 rows in set (0.00 sec)
5 LIKE 模糊查询,单引号括起来模糊条件
LIKE可用于实现模糊查询，常见于搜索功能中。
和 LIKE 联用的通常还有通配符，代表未知字符。SQL中的通配符是 _ 和 % 。其中 _ 代表单个任意字符，% 0或者多个任意字符。
5.1 电话号码前四位数为1101的员工信息
mysql> select * from employee where phone like '1101__';
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
+----+------+------+--------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from employee where phone like '1101%';
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
+----+------+------+--------+--------+--------+
2 rows in set (0.00 sec)
5.2
mysql> select * from employee where name like 'J%';
---查询名字以J开头的员工信息
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
+----+------+------+--------+--------+--------+
4 rows in set (0.01 sec)
mysql> select * from employee where name like '%a%';
---查询名字中有a的员工的信息（不区分大小写）
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
+----+------+------+--------+--------+--------+
3 rows in set (0.00 sec)
mysql>
6 and---且，or---或者
