條件查詢Where

分組前的查詢條件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...查詢一個區(qū)間
查詢年齡在[20,30]范圍內(nèi)的員工信息

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
關(guān)鍵詞 IN 和 NOT IN 的作用和它們的名字一樣明顯，用于篩選“在”或“不在”某個范圍內(nèi)的結(jié)果

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可用于實現(xiàn)模糊查詢，常見于搜索功能中。
和 LIKE 聯(lián)用的通常還有通配符，代表未知字符。SQL中的通配符是 _ 和 % 。其中 _ 代表單個任意字符，% 0或者多個任意字符。
5.1 電話號碼前四位數(shù)為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的員工的信息（不區(qū)分大小寫）
+----+------+------+--------+--------+--------+
| 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---或者

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容