MySQL數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)通過(guò)條件查詢

發(fā)布時(shí)間：2020-11-12 15:29:05 來(lái)源：億速云閱讀：261 作者：Leah 欄目：開(kāi)發(fā)技術(shù)

MySQL數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)通過(guò)條件查詢？針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

概述

在實(shí)際的業(yè)務(wù)場(chǎng)景應(yīng)用中，我們經(jīng)常要根據(jù)業(yè)務(wù)條件獲取并篩選出我們的目標(biāo)數(shù)據(jù)。這個(gè)過(guò)程我們稱之為數(shù)據(jù)查詢的過(guò)濾。而過(guò)濾過(guò)程使用的各種條件（比如日期時(shí)間、用戶、狀態(tài)）是我們獲取精準(zhǔn)數(shù)據(jù)的必要步驟，

這樣才能得到我們期望的結(jié)果。所以本章我們來(lái)學(xué)習(xí)MySQL中查詢過(guò)濾條件的各種用法。

關(guān)系運(yùn)算

關(guān)系運(yùn)算就是where語(yǔ)句后跟上一個(gè)或者n個(gè)條件，滿足where后面條件的數(shù)據(jù)會(huì)被返回，反之不滿足的就會(huì)被過(guò)濾掉。operators指的是運(yùn)算符，有如下幾種情況：

運(yùn)算符	說(shuō)明
=	等于
<> 或者 !=	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于

關(guān)系運(yùn)算基本的語(yǔ)法格式如下:

 select cname1,cname2,... from tname where cname operators cval

等于=

查詢出列和后面的值嚴(yán)格相等的數(shù)據(jù)，非值類型的需要對(duì)后面值加上引號(hào)，值類型的不需要。

語(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àn)，所以看很多以前的例子，<> 出現(xiàn)頻率比較高，可移植性更強(qiáng)，推薦使用。

不等于的目的是查詢出與條件不符和結(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í)間類型的比較，格式如下：

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è)條件中滿足一個(gè)
NOT	對(duì)條件進(jìn)行取非操作

AND（且）

當(dāng)需要多個(gè)條件進(jìn)行數(shù)據(jù)過(guò)濾的時(shí)候，使用這種方式，and的每個(gè)表達(dá)式都是要成立，過(guò)濾出來(lái)的數(shù)據(jù)就是用戶需要的。

下面過(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è)條件中只要滿足一個(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è)滿足的條件進(jìn)行取反，過(guò)濾出來(lái)的數(shù)據(jù)就是用戶需要的。

下面過(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è)用戶表信息表（包含名稱、年齡、地址、性別），當(dāng)我們要查詢名稱為s開(kāi)頭的用戶時(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ū)間查詢)

操作符 BETWEEN … AND 會(huì)選取介于兩個(gè)值之間的數(shù)據(jù)范圍，這些值可以是數(shù)值、文本或者日期，屬于一個(gè)閉區(qū)間查詢。

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;

查詢年齡在[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（包含查詢）

按照上面得數(shù)據(jù)，如果我們想查出居住地位于福州和廈門得用戶數(shù)據(jù)，應(yīng)該使用 IN操作符，因?yàn)?IN 操作符允許我們?cè)?WHERE 子句中指定多個(gè)值，符合這些值中得某一項(xiàng)，既滿足條件返回?cái)?shù)據(jù)。

語(yǔ)法格式如下，in 后面列表的值類型必須一致或兼容，且不支持通配符：

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ì)包含查詢?nèi)》矗?/strong>

我們上面已經(jīng)學(xué)習(xí)過(guò)了not得用戶，對(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查詢都不起作用，只有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、空值查詢需要使用IS NULL或者IS NOT NULL，其他查詢運(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數(shù)據(jù)庫(kù)中如何實(shí)現(xiàn)通過(guò)條件查詢問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

向AI問(wèn)一下細(xì)節(jié)

推薦閱讀：

Mysql數(shù)據(jù)庫(kù)條件查詢語(yǔ)句如何使用

linux中實(shí)現(xiàn)條件查詢的方法

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

mysql 條件查詢如何實(shí)現(xiàn)

上一篇新聞：
在vue項(xiàng)目中使用element實(shí)現(xiàn)提示中英文

下一篇新聞：
python 中內(nèi)存管理的實(shí)現(xiàn)原理是什么

猜你喜歡

hbuilder導(dǎo)出項(xiàng)目后文件沒(méi)了怎么找回

oracle怎么查看當(dāng)前字符集

linux中vim顯示行數(shù)的方法是什么

centos7安裝php的步驟是什么

Android mvvm框架的優(yōu)缺點(diǎn)是什么

c#數(shù)組的聲明方法是什么

如何修改hbuilder默認(rèn)文件保存目錄

linux怎么查看字符集編碼

Android mvvm框架搭建的方法是什么

c#如何往數(shù)組里添加數(shù)據(jù)

最新資訊

LAMP環(huán)境MySQL索引優(yōu)化方法

LAMP服務(wù)器資源監(jiān)控與管理技巧

LAMP環(huán)境PHP版本升級(jí)注意事項(xiàng)

Apache在LAMP中的角色與性能考量

LAMP架構(gòu)適合哪些類型網(wǎng)站

Linux中LAMP配置最佳實(shí)踐分享

LAMP與LEMP，Linux服務(wù)器如何選擇

LAMP環(huán)境如何保障網(wǎng)站安全

Linux下LAMP環(huán)境搭建難點(diǎn)解析

LAMP架構(gòu)如何優(yōu)化Linux服務(wù)器性能

相關(guān)推薦

redis怎么實(shí)現(xiàn)條件查詢？

SQL中怎么實(shí)現(xiàn)多條件查詢

如何實(shí)現(xiàn)多條件查詢

Linq中怎么實(shí)現(xiàn)多條件查詢

Linq中怎么實(shí)現(xiàn)動(dòng)態(tài)條件查詢

Hibernate中怎么實(shí)現(xiàn)多條件查詢

mysql instr條件查詢?nèi)绾螌?shí)現(xiàn)

Layui如何實(shí)現(xiàn)多條件查詢

mysql如何實(shí)現(xiàn)條件查詢語(yǔ)句

mybatisplus如何實(shí)現(xiàn)條件查詢

相關(guān)標(biāo)簽

--mysql mysqlslap mysql會(huì)話臨時(shí)表空間 mysqlclient mysql命令 mysql數(shù)組 mysql.sock mysql sq mysql升級(jí) mysql創(chuàng)建存儲(chǔ)過(guò)程 mysql遠(yuǎn)程訪問(wèn) windows下安裝多個(gè)mysql實(shí)例 mysql root密碼 mysql源碼 mysql關(guān)系型數(shù)據(jù)庫(kù) mysql的安裝 mysql臨時(shí)表 mysql日志 mysql8.0.17 mysql8.0.23