条件查询Where

作者: 御都 | 来源:发表于2019-08-03 12:02 被阅读0次

MySQL SELECT条件查询
条件查询Where
mysql select ，where，limit查询
SQL 条件查询
mysql-不走索引的情况
写一个“特殊”的查询构造器 - (四、条件查询：复杂条件)
MySQL数据库查询
thinkphp v5 链式操作篇
python学习笔记-数据库04_where
SQL之更删改查

分组前的查询条件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---或者

MySQL SELECT条件查询
一、 SELECT 条件查询条件查询条件查询就是在查询时给出 WHERE 子句，在WHERE字句中可以使用如...
条件查询Where
分组前的查询条件Where，where条件语句中常见运算符如下：1 >,<，>=,<=,<>(!=) 2 Betw...
mysql select ，where，limit查询
1、使用查询where、limit where：查询条件 limit：限制查询结果显示的数量题目：查询stude...
SQL 条件查询
# 2. 条件查询 /* 条件查询的语法: select 查询列表 from 表名 where 筛选条件; 分类：...
mysql-不走索引的情况
1、where字句的查询条件里面有不等于号where column!=2、where字句的查询条件里面使用了函数3...
写一个“特殊”的查询构造器 - (四、条件查询：复杂条件)
复杂的条件在 SQL 的条件查询中，不只有 where、or where 这些基本的子句，还有 where in...
MySQL数据库查询
1、（查询）条件查询基本语法消除重复行 where 使用where条件对数据进行筛选逻辑运算符 notand...
thinkphp v5 链式操作篇
1. 查询表达式 2. where/whereOr 可以使用where方法进行AND条件查询：多字段相同条件的A...
python学习笔记-数据库04_where
使用where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。 where 条件查询语法格式如下...
SQL之更删改查
查询单表查询查询所有字段查询指定字段 WHERE条件查询下面的运算符可在 WHERE 子句中使用：去重复...