Mysql系列第七講 玩轉select條件查詢，避免採坑

條件查詢

語法：

select 列名 from 表名 where 列 運算子 值1

說明：

注意關鍵字where，where後面跟上一個或者多個條件，條件是對前面資料的過濾，只有滿足where後面條件的資料才會被返回。

下面介紹常見的查詢運算子。

等於（=）

select 列名 from 表名 where 列 = 值;1

說明：

查詢出指定的列和對應的值相等的記錄。

值如果是字串型別，需要用單引號或者雙引號引起來。

示例：

mysql> create table test1 (a int,b varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into test1 values (1,'abc'),(2,'bbb');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> 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.00 sec)mysql> select * from test1 where b = "abc";+------+------+| a    | b    |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)123456789101112131415161718192021222324252627282930313233343536373839

不等於（<>、!=）

不等於有兩種寫法：<>或者!=

select 列名 from 表名 where 列 <> 值;或者select 列名 from 表名 where 列 != 值;123

示例：

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)123456789101112131415

注意：

<> 這個是最早的用法。

!=是後來才加上的。

兩者意義相同，在可移植性上前者優於後者

故而sql語句中儘量使用<>來做不等判斷

大於（>）

select 列名 from 表名 where 列 > 值;1

示例：

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)123456789101112131415161718192021222324

說明：

數值按照大小比較。

字元按照ASCII碼對應的值進行比較，比較時按照字元對應的位置一個字元一個字元的比較。

其他幾個運算子（<、<=、>=）在此就不介紹了，用法和上面類似，大家可以自己練習一下。

邏輯查詢運算子

當我們需要使用多個條件進行查詢的時候，需要使用邏輯查詢運算子。

AND（並且）

select 列名 from 表名 where 條件1 and 條件2;1

表示返回滿足條件1和條件2的記錄。

示例：

mysql> create table test3(a int not null,b varchar(10) not null);Query OK, 0 rows affected (0.01 sec)mysql> insert into test3 (a,b) values (1,'a'),(2,'b'),(2,'c'),(3,'c');Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> 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.00 sec)12345678910111213141516171819202122232425

查詢出了a=2 並且 b='c’的記錄，返回了一條結果。

OR（或者）

select 列名 from 表名 where 條件1 or 條件2;1

滿足條件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)1234567891011121314151617181920

查詢出了a=1或者b='c’的記錄，返回了3條記錄。

like（模糊查詢）

有個學生表，包含（學生id，年齡，姓名），當我們需要查詢姓“張”的學生的時候，如何查詢呢？

此時我們可以使用sql中的like關鍵字。語法：

select 列名 from 表名 where 列 like pattern;1

• 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: 0mysql> 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)12345678910111213141516171819202122232425262728

查詢名字中帶有’學’的學生，'學’的位置不固定，可以這麼查詢，如下：

mysql> select * from stu a where a.name like '%學%'; ;+----+-----+-----------+| id | age | name      |+----+-----+-----------+|  3 |  26 | 張學友    ||  5 |  55 | 張學良    |+----+-----+-----------+2 rows in set (0.00 sec)12345678

查詢姓’張’，名字2個字的學生：

mysql> select * from stu a where a.name like '張_';+----+-----+--------+| id | age | name   |+----+-----+--------+|  1 |  22 | 張三   |+----+-----+--------+1 row in set (0.00 sec)1234567

上面的代表任意一個字元，如果要查詢姓’張’的3個字的學生，條件變為了’張_’，2個下劃線符號。

BETWEEN AND(區間查詢)

運算子 BETWEEN … AND 會選取介於兩個值之間的資料範圍，這些值可以是數值、文字或者日期，屬於一個閉區間查詢。

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

返回對應的列的值在[值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)123456789101112131415161718192021

下面兩條sql效果一樣

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

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: 0mysql> 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)123456789101112131415161718192021222324252627282930313233

用了這麼多or，有沒有更簡單的寫法？有，用IN查詢

IN 運算子允許我們在 WHERE 子句中規定多個值。

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

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)1234567891011

相對於or簡潔了很多。

NOT IN查詢

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

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

如查詢年齡不在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)123456789

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)12345678910111213141516

上面我們建立了一個表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)1234567891011121314151617181920212223242526272829303132333435363738394041424344

認真看一下上面的查詢：

上面帶有條件的查詢，對欄位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;1

查詢指定的列的值為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: 0mysql> 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)12345678910111213141516171819202122232425262728293031323334353637

IS NOT NULL（返回值不為空的記錄）

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

查詢指定的列的值不為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)123456789101112131415161718

<=>（安全等於）

<=>：既可以判斷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)1234567891011121314151617181920212223242526272829303132333435

可以看到<=>可以將NULL查詢出來。

經典面試題

下面的2個sql查詢結果一樣麼？

select * from students;select * from students where name like '%';12

結果分2種情況：

當name沒有NULL值時，返回的結果一樣。

當name有NULL值時，第2個sql查詢不出name為NULL的記錄。