MySQL全面瓦解之查詢(xún)的過(guò)濾條件詳解
概述
在實(shí)際的業(yè)務(wù)場(chǎng)景應(yīng)用中，我們經(jīng)常要根據(jù)業(yè)務(wù)條件獲取并篩選出我們的目標(biāo)數(shù)據(jù)。這個(gè)過(guò)程我們稱(chēng)之為數(shù)據(jù)查詢(xún)的過(guò)濾。而過(guò)濾過(guò)程使用的各種條件（比如日期時(shí)間、用戶(hù)、狀態(tài)）是我們獲取精準(zhǔn)數(shù)據(jù)的必要步驟，
這樣才能得到我們期望的結(jié)果。所以本章我們來(lái)學(xué)習(xí)MySQL中查詢(xún)過(guò)濾條件的各種用法。
關(guān)系運(yùn)算
關(guān)系運(yùn)算就是where語(yǔ)句后跟上一個(gè)或者n個(gè)條件，滿(mǎn)足where后面條件的數(shù)據(jù)會(huì)被返回，反之不滿(mǎn)足的就會(huì)被過(guò)濾掉。operators指的是運(yùn)算符 ，有如下幾種情況：
| 運(yùn)算符 | 說(shuō)明 |
| = | 等于 |
| <> 或者 != | 不等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
關(guān)系運(yùn)算基本的語(yǔ)法格式如下:
select cname1,cname2,... from tname where cname operators cval
等于=
查詢(xún)出 列和后面的值嚴(yán)格相等的數(shù)據(jù)，非值類(lèi)型的需要對(duì)后面值加上引號(hào)，值類(lèi)型的不需要。
語(yǔ)法格式如下：
select cname1,cname2,... from tname where cname = cval;
mysql> select * from user2; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where name='helen'; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | +----+-------+-----+----------+-----+ 1 row in set mysql> select * from user2 where age=21; +----+-------+-----+---------+-----+ | id | name | age | address | sex | +----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+---------+-----+ 2 rows in set
不等于（<>、!=）
不等于有兩種寫(xiě)法，一種是<>，另一種是!=，意思一樣，可隨意切換使用，但是 <> 先于 != 出現(xiàn)，所以看很多以前的例子，<> 出現(xiàn)頻率比較高，可移植性更強(qiáng)，推薦使用。
不等于的目的是查詢(xún)出與條件不符和結(jié)果，格式如下：
select cname1,cname2,... from tname where cname <> cval; 或 select cname1,cname2,... from tname where cname != cval;
mysql> select * from user2; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where age<>20; +----+-------+-----+---------+-----+ | id | name | age | address | sex | +----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+---------+-----+ 2 rows in set
大于小于（> <）
一般用于數(shù)值或者日期、時(shí)間類(lèi)型的比較，格式如下：
select cname1,cname2,... from tname where cname > cval; select cname1,cname2,... from tname where cname < cval; select cname1,cname2,... from tname where cname >= cval; select cname1,cname2,... from tname where cname <= cval;
mysql> select * from user2 where age>20; +----+-------+-----+---------+-----+ | id | name | age | address | sex | +----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+---------+-----+ 2 rows in set mysql> select * from user2 where age>=20; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+----------+-----+ 3 rows in set mysql> select * from user2 where age<21; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | +----+-------+-----+----------+-----+ 1 row in set mysql> select * from user2 where age<=21; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | +----+-------+-----+----------+-----+ 3 rows in set
邏輯運(yùn)算
| 運(yùn)算符 | 說(shuō)明 |
|---|---|
| AND | 多個(gè)條件都成立 |
| OR | 多個(gè)條件中滿(mǎn)足一個(gè) |
| NOT | 對(duì)條件進(jìn)行取非操作 |
AND（且）
當(dāng)需要多個(gè)條件進(jìn)行數(shù)據(jù)過(guò)濾的時(shí)候，使用這種方式，and的每個(gè)表達(dá)式都是要成立，過(guò)濾出來(lái)的數(shù)據(jù)就是用戶(hù)需要的。
下面過(guò)濾出年齡和性別兩個(gè)條件都成立的數(shù)據(jù)，語(yǔ)法格式如下：
select cname1,cname2,... from tname where cname1 operators cval1 and cname2 operators cval2
mysql> select * from user2; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where age >20 and sex=1; +----+-------+-----+---------+-----+ | id | name | age | address | sex | +----+-------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+---------+-----+ 2 rows in set
OR（或）
當(dāng)多個(gè)條件中只要滿(mǎn)足一個(gè)條件即進(jìn)行數(shù)據(jù)過(guò)濾。
下面條件過(guò)濾出年齡大于21歲和小于21歲的數(shù)據(jù)，語(yǔ)法格式如下：
select cname1,cname2,... from tname where cname1 operators cval1 or cname2 operators cval2
mysql> select * from user2; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where age>21 or age<21; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+----------+-----+ 2 rows in set
NOT（取非）
對(duì)某個(gè)滿(mǎn)足的條件進(jìn)行取反，過(guò)濾出來(lái)的數(shù)據(jù)就是用戶(hù)需要的。
下面過(guò)濾不屬于年齡大于20的數(shù)據(jù)，語(yǔ)法格式如下：
select cname1,cname2,... from tname where not(cname operators cval)
mysql> select * from user2; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+----------+-----+ 4 rows in set mysql> select * from user2 where not(age>20); +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 2 | helen | 20 | quanzhou | 0 | +----+-------+-----+----------+-----+ 1 row in set
模糊匹配
就像我們上面的那個(gè)用戶(hù)表信息表（包含名稱(chēng)、年齡、地址、性別），當(dāng)我們要查詢(xún)名稱(chēng)為s開(kāi)頭的用戶(hù)時(shí)，就可以用到 like 關(guān)鍵字了，他用以模糊匹配數(shù)據(jù)。
語(yǔ)法格式如下,pattern中可以包含通配符，有兩種。%：表示匹配任意一個(gè)或n個(gè)字符； _：表示匹配任意一個(gè)字符。
select cname1,cname2,... from tname where cname like pattern;
%的使用
mysql> select * from user2; +----+--------+-----+----------+-----+ | id | name | age | address | sex | +----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where name like 's%'; +----+--------+-----+---------+-----+ | id | name | age | address | sex | +----+--------+-----+---------+-----+ | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+---------+-----+ 2 rows in set
_的使用
mysql> select * from user2; +----+--------+-----+----------+-----+ | id | name | age | address | sex | +----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where name like 's_l'; +----+------+-----+---------+-----+ | id | name | age | address | sex | +----+------+-----+---------+-----+ | 3 | sol | 21 | xiamen | 0 | +----+------+-----+---------+-----+ 1 row in set
注意點(diǎn)
1、不要過(guò)度使用模糊匹配得通配符。如果其他操作符能達(dá)到相同的目的，應(yīng)該使用其他操作符
2、對(duì)大體量的表進(jìn)行模糊匹配的時(shí)候盡量不要以%開(kāi)頭，比如 like '%username',這樣會(huì)執(zhí)行掃表，效率較慢。盡量明確模糊查找的開(kāi)頭部分，比如 like 'brand%'，會(huì)先定位到brand開(kāi)頭的數(shù)據(jù)，效率高很多。
范圍值檢查
BETWEEN AND(區(qū)間查詢(xún))
操作符 BETWEEN … AND 會(huì)選取介于兩個(gè)值之間的數(shù)據(jù)范圍，這些值可以是數(shù)值、文本或者日期，屬于一個(gè)閉區(qū)間查詢(xún)。
and 的左邊val1 和 右邊 val2 分別表示兩個(gè)臨界值，等同于數(shù)學(xué)公式[val1,val2] ，屬于這兩個(gè)區(qū)間的數(shù)據(jù)會(huì)被過(guò)濾出來(lái)（>=val1 和 <=val2）,所以語(yǔ)法格式如下：
selec cname1,cname2,... from tname where cname between val1 and val2; 等同于 selec cname1,cname2,... from tname where cname >= val1 and cname <= val2;
查詢(xún)年齡在[21,25]之間的數(shù)據(jù)：
mysql> select * from user2; +----+--------+-----+----------+-----+ | id | name | age | address | sex | +----+--------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+----------+-----+ 5 rows in set mysql> select * from user2 where age between 21 and 25; +----+--------+-----+---------+-----+ | id | name | age | address | sex | +----+--------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+---------+-----+ 3 rows in set mysql> select * from user2 where age >= 21 and age <= 25; +----+--------+-----+---------+-----+ | id | name | age | address | sex | +----+--------+-----+---------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 3 | sol | 21 | xiamen | 0 | | 5 | selina | 25 | taiwang | 0 | +----+--------+-----+---------+-----+ 3 rows in set
IN（包含查詢(xún)）
按照上面得數(shù)據(jù)，如果我們想查出居住地位于福州和廈門(mén)得用戶(hù)數(shù)據(jù)，應(yīng)該使用 IN操作符，因?yàn)?IN 操作符允許我們?cè)?WHERE 子句中指定多個(gè)值，符合這些值中得某一項(xiàng)，既滿(mǎn)足條件返回?cái)?shù)據(jù)。
語(yǔ)法格式如下，in 后面列表的值類(lèi)型必須一致或兼容，且不支持通配符：
select cname1,cname2,... from tname where cname in (val1,val2,...);
mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set
mysql> select * from user2 where address in('fuzhou','xiamen');
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+---------+-----+
2 rows in set
NOT IN（對(duì)包含查詢(xún)?nèi)》矗?/strong>
我們上面已經(jīng)學(xué)習(xí)過(guò)了not得用戶(hù)，對(duì)not后面執(zhí)行得表達(dá)式進(jìn)行取反得操作，測(cè)試下：
mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set
mysql> select * from user2 where address not in('fuzhou','quanzhou','xiamen');
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+---------+-----+
2 rows in set
空值檢查
IS NULL/IS NOT NULL
判斷是否為空，語(yǔ)法格式如下，這邊注意的是，對(duì)值為null的數(shù)據(jù)，各種比較運(yùn)算符、like、between and、in、not in查詢(xún)都不起作用，只有is null 能夠過(guò)濾出來(lái)。
select cname1,cname2,... from tname where cname is null; 或者 select cname1,cname2,... from tname where cname is not null;
mysql> select * from user2 where address is null; +----+--------+-----+---------+-----+ | id | name | age | address | sex | +----+--------+-----+---------+-----+ | 5 | selina | 25 | NULL | 0 | +----+--------+-----+---------+-----+ 1 row in set mysql> select * from user2 where address is not null; +----+-------+-----+----------+-----+ | id | name | age | address | sex | +----+-------+-----+----------+-----+ | 1 | brand | 21 | fuzhou | 1 | | 2 | helen | 20 | quanzhou | 0 | | 3 | sol | 21 | xiamen | 0 | | 4 | weng | 33 | guizhou | 1 | +----+-------+-----+----------+-----+ 4 rows in set
有一種關(guān)鍵字 <=>，可以包含對(duì)null值得判斷，但是目前用的比較少了，有興趣可以去查查，這邊不贅述。
總結(jié)
1、like表達(dá)式中的%匹配一個(gè)到多個(gè)任意字符，_匹配一個(gè)任意字符
2、空值查詢(xún)需要使用IS NULL或者IS NOT NULL，其他查詢(xún)運(yùn)算符對(duì)NULL值無(wú)效。即使%通配符可以匹配任何東西，也不能匹配值NULL的數(shù)據(jù)。
3、建議創(chuàng)建表的時(shí)候，表字段不設(shè)置空，給字段一個(gè)default 默認(rèn)值。
4、MySQL支持使用NOT對(duì)IN 、BETWEEN 和EXISTS子句取反 。
到此這篇關(guān)于MySQL全面瓦解之查詢(xún)的過(guò)濾條件的文章就介紹到這了,更多相關(guān)MySQL查詢(xún)的過(guò)濾條件內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
windows下mysql數(shù)據(jù)庫(kù)主從配置教程
這篇文章主要為大家詳細(xì)介紹了windows下mysql數(shù)據(jù)庫(kù)主從配置教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-05-05
詳解MySQL單實(shí)例和多實(shí)例啟動(dòng)腳本
這篇文章主要介紹了MySQL單實(shí)例和多實(shí)例啟動(dòng)腳本,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-08-08
Mysql事務(wù)并發(fā)臟讀+不可重復(fù)讀+幻讀詳解
這篇文章主要介紹了Mysql事務(wù)并發(fā)臟讀+不可重復(fù)讀+幻讀詳解，文章基于Mysql事務(wù)的相關(guān)資料展開(kāi)對(duì)主題的詳細(xì)介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-04-04
Mysql數(shù)據(jù)庫(kù)group?by原理詳解
這篇文章主要為大家介紹了Mysql數(shù)據(jù)庫(kù)group?by的原理詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-07-07
這篇文章主要介紹了詳解mysql不等于null和等于null的寫(xiě)法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
Ubuntu搭建Mysql+Keepalived高可用的實(shí)現(xiàn)(雙主熱備)
本文主要介紹了Ubuntu搭建Mysql+Keepalived高可用的實(shí)現(xiàn)(雙主熱備)，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-11-11
EXPLAIN用于SELECT語(yǔ)句中的每個(gè)表返回一行信息。表以它們?cè)谔幚聿樵?xún)過(guò)程中將被MySQL讀入的順序被列出2013-02-02
這篇文章給大家從理論到實(shí)戰(zhàn)詳細(xì)分享了MySQL升級(jí)的最佳方法，有需要的朋友跟著學(xué)習(xí)操作下吧。2017-12-12
安裝MySQL phpMyAdmin cpolar實(shí)現(xiàn)遠(yuǎn)程訪問(wèn)連接的操作步驟
這篇文章主要給大家介紹了安裝 MySQL phpMyAdmin cpolar實(shí)現(xiàn)遠(yuǎn)程訪問(wèn)連接的流程步驟,文中有詳細(xì)的圖文介紹,具有一定的參考價(jià)值,需要的朋友可以參考下2023-08-08
本篇文章給大家介紹了mysql存儲(chǔ)過(guò)程之邏輯判斷、條件控制，涉及到的知識(shí)點(diǎn)有邏輯判斷、條件控制方面的內(nèi)容，對(duì)mysql存儲(chǔ)過(guò)程邏輯判斷和條件控制感興趣的朋友可以參考下本文2015-10-10