mysql 有 like in吗_MySQL常用命令（三）-IN、LIKE

IN

与OR的功能相似，但是与OR相比具有更多优势

更简洁；

计算速度更快；

可以包含其他的SELECT语句，功能更加强大；

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

作用：进行模糊搜索；

%通配符

表示任何字符串出现任何次数；

注意：

在给定的位置出现0个、1个和任意多个字符

不可以匹配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)

_通配符

与%可以匹配任意多个字符不同，它只能匹配一个字符；

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)

通配符使用的利弊

通配符是模糊检索，效率没有精确检索快；

非不要情况下，要少使用通配符进行检索；