MySQL常用命令（三）-IN摔握、LIKE

IN

與OR的功能相似寄狼，但是與OR相比具有更多優(yōu)勢

更簡潔丁寄；
計(jì)算速度更快；
可以包含其他的SELECT語句泊愧，功能更加強(qiáng)大伊磺；

mysql> SELECT productName,price FROM product;
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10002       |   30.00 |
| 10002       |  888.00 |
| 10003       | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName IN ('10001','10003');
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10003       | 1000.00 |
+-------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName = '10001' OR productName = '10003';
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10003       | 1000.00 |
+-------------+---------+
3 rows in set (0.00 sec)

NOT

作用：否定它之后的任何條件；
常見的搭配：NOT IN删咱、NOT BETWEEN屑埋、NOT EXIST、NOT NULL痰滋；

mysql> SELECT productName,price FROM product;
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10002       |   30.00 |
| 10002       |  888.00 |
| 10003       | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName NOT  IN ('10001','10003');
+-------------+--------+
| productName | price  |
+-------------+--------+
| 10002       |  30.00 |
| 10002       | 888.00 |
+-------------+--------+
2 rows in set (0.00 sec)

LIKE

作用：進(jìn)行模糊搜索摘能；

%通配符

表示任何字符串出現(xiàn)任何次數(shù)；
注意：

在給定的位置出現(xiàn)0個(gè)敲街、1個(gè)和任意多個(gè)字符
不可以匹配NULL团搞；


mysql> SELECT productName,price FROM product;
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10002       |   30.00 |
| 10002       |  888.00 |
| 10003       | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName LIKE '10001%';
+-------------+--------+
| productName | price  |
+-------------+--------+
| 10001       | 100.00 |
| 10001       | 450.00 |
+-------------+--------+
2 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName LIKE '1000%';
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10002       |   30.00 |
| 10002       |  888.00 |
| 10003       | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName LIKE '%001%';
+-------------+--------+
| productName | price  |
+-------------+--------+
| 10001       | 100.00 |
| 10001       | 450.00 |
+-------------+--------+
2 rows in set (0.00 sec)

_通配符

與%可以匹配任意多個(gè)字符不同，它只能匹配一個(gè)字符多艇；

mysql> SELECT productName,price FROM product;
+-------------+---------+
| productName | price   |
+-------------+---------+
| 10001       |  100.00 |
| 10001       |  450.00 |
| 10002       |   30.00 |
| 10002       |  888.00 |
| 10003       | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName LIKE '_001';
Empty set (0.00 sec)

mysql> SELECT productName,price FROM product WHERE productName LIKE '_0001';
+-------------+--------+
| productName | price  |
+-------------+--------+
| 10001       | 100.00 |
| 10001       | 450.00 |
+-------------+--------+
2 rows in set (0.00 sec)

通配符使用的利弊

通配符是模糊檢索逻恐，效率沒有精確檢索快；
非不要情況下峻黍，要少使用通配符進(jìn)行檢索复隆；

最后編輯于：2020.04.17 11:55:48

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

禁止轉(zhuǎn)載，如需轉(zhuǎn)載請通過簡信或評論聯(lián)系作者姆涩。