Mysql高手系列 – 第7篇:玩轉select條件查詢,避免踩坑
- 2019 年 10 月 3 日
- 筆記
這是Mysql系列第7篇。
環境:mysql5.7.25,cmd命令中進行演示。
電商中:我們想查看某個用戶所有的訂單,或者想查看某個用戶在某個時間段內所有的訂單,此時我們需要對訂單表數據進行篩選,按照用戶、時間進行過濾,得到我們期望的結果。
此時我們需要使用條件查詢來對指定表進行操作,我們需要了解sql中的條件查詢常見的玩法。
本篇內容
- 條件查詢語法
- 條件查詢運算符詳解(=、<、>、>=、<=、<>、!=)
- 邏輯查詢運算符詳解(and、or)
- like模糊查詢介紹
- between and查詢
- in、not in查詢
- NULL值存在的坑
- is null/is not null(NULL值專用查詢)
- <=>(安全等於)運算符
- 經典面試題
條件查詢
語法:
select 列名 from 表名 where 列 運算符 值
說明:
注意關鍵字where,where後面跟上一個或者多個條件,條件是對前面數據的過濾,只有滿足where後麵條件的數據才會被返回。
下面介紹常見的查詢運算符。
條件查詢運算符
操作符 | 描述 |
---|---|
= | 等於 |
<> 或者 != | 不等於 |
> | 大於 |
< | 小於 |
>= | 大於等於 |
<= | 小於等於 |
等於(=)
select 列名 from 表名 where 列 = 值;
說明:
查詢出指定的列和對應的值相等的記錄。
值如果是字元串類型,需要用單引號或者雙引號引起來。
示例:
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: 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.00 sec) mysql> select * from test1 where b = "abc"; +------+------+ | a | b | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec)
不等於(<>、!=)
不等於有兩種寫法:<>或者!=
select 列名 from 表名 where 列 <> 值; 或者 select 列名 from 表名 where 列 != 值;
示例:
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)
注意:
<> 這個是最早的用法。
!=是後來才加上的。
兩者意義相同,在可移植性上前者優於後者
故而sql語句中盡量使用<>來做不等判斷
大於(>)
select 列名 from 表名 where 列 > 值;
示例:
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)
說明:
數值按照大小比較。
字元按照ASCII碼對應的值進行比較,比較時按照字元對應的位置一個字元一個字元的比較。
其他幾個運算符(<、<=、>=)在此就不介紹了,用法和上面類似,大家可以自己練習一下。
邏輯查詢運算符
當我們需要使用多個條件進行查詢的時候,需要使用邏輯查詢運算符。
邏輯運算符 | 描述 |
---|---|
AND | 多個條件都成立 |
OR | 多個條件中滿足一個 |
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.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: 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.00 sec)
查詢出了a=2 並且 b=’c’的記錄,返回了一條結果。
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)
查詢出了a=1或者b=’c’的記錄,返回了3條記錄。
like(模糊查詢)
有個學生表,包含(學生id,年齡,姓名),當我們需要查詢姓「張」的學生的時候,如何查詢呢?
此時我們可以使用sql中的like關鍵字。語法:
select 列名 from 表名 where 列 like pattern;
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: 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> select * from stu a where a.name like '%學%'; ; +----+-----+-----------+ | id | age | name | +----+-----+-----------+ | 3 | 26 | 張學友 | | 5 | 55 | 張學良 | +----+-----+-----------+ 2 rows in set (0.00 sec)
查詢姓’張’,名字2個字的學生:
mysql> select * from stu a where a.name like '張_'; +----+-----+--------+ | id | age | name | +----+-----+--------+ | 1 | 22 | 張三 | +----+-----+--------+ 1 row in set (0.00 sec)
上面的_代表任意一個字元,如果要查詢姓’張’的3個字的學生,條件變為了’張__’,2個下劃線符號。
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.00 sec)
下面兩條sql效果一樣
select * from stu t where t.age between 25 and 32; select * from stu t where t.age >= 25 and t.age <= 32;
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: 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 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)
相對於or簡潔了很多。
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 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)
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: 0 mysql> select * from test5; +---+------+------+ | a | b | c | +---+------+------+ | 1 | 2 | a | | 3 | NULL | b | | 4 | 5 | NULL | +---+------+------+ 3 rows in set (0.00 sec)
上面我們創建了一個表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)
認真看一下上面的查詢:
上面帶有條件的查詢,對欄位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;
查詢指定的列的值為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: 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)
IS NULL(返回值不為空的記錄)
select 列名 from 表名 where 列 is not null;
查詢指定的列的值不為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)
<=>(安全等於)
<=>:既可以判斷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: 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)
可以看到<=>可以將NULL查詢出來。
經典面試題
下面的2個sql查詢結果一樣么?
select * from students; select * from students where name like '%';
結果分2種情況:
當name沒有NULL值時,返回的結果一樣。
當name有NULL值時,第2個sql查詢不出name為NULL的記錄。
總結
- **like中的%可以匹配一個到多個任意的字元,_可以匹配任意一個字元**
- 空值查詢需要使用IS NULL或者IS NOT NULL,其他查詢運算符對NULL值無效
- 建議創建表的時候,盡量設置表的欄位不能為空,給欄位設置一個默認值
- <=>(安全等於)玩玩可以,建議少使用
- sql方面有問題的歡迎留言?或者加我微信itsoku交流。
Mysql系列目錄
- 第1篇:mysql基礎知識
- 第2篇:詳解mysql數據類型(重點)
- 第3篇:管理員必備技能(必須掌握)
- 第4篇:DDL常見操作
- 第5篇:DML操作匯總(insert,update,delete)
- 第6篇:select查詢基礎篇
mysql系列大概有20多篇,喜歡的請關注一下!