操作符

AND操作符

mysql> SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 AND prod_price<=10; +---------+------------+----------------+ | prod_id | prod_price | prod_name      | +---------+------------+----------------+ | FB      | 10.00 | Bird seed      | | FC      | 2.50 | Carrots        | | SLING   | 4.49 | Sling          | | TNT1    | 2.50 | TNT (1 stick) | | TNT2    | 10.00 | TNT (5 sticks) | +---------+------------+----------------+ 5 rows in set (0.00 sec) 

• 此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

OR操作符

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id=1002 OR vend_id=1003; +----------------+------------+ | prod_name      | prod_price | +----------------+------------+ | Fuses          | 3.42 | | Oil can        | 8.99 | | Detonator      | 13.00 | | Bird seed      | 10.00 | | Carrots        | 2.50 | | Safe           | 50.00 | | Sling          | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec) 

• 此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。

IN 操作符

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name; +----------------+------------+ | prod_name      | prod_price | +----------------+------------+ | Bird seed      | 10.00 | | Carrots        | 2.50 | | Detonator      | 13.00 | | Fuses          | 3.42 | | Oil can        | 8.99 | | Safe           | 50.00 | | Sling          | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec) 

• 此SELECT语句检索供应商1002和1003制造的所有产品。

NOT 操作符

mysql> SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003) ORDER BY prod_name; +--------------+------------+ | prod_name    | prod_price | +--------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil  | 9.99 | | 2 ton anvil  | 14.99 | | JetPack 1000 | 35.00 | | JetPack 2000 | 55.00 | +--------------+------------+ 5 rows in set (0.01 sec) 

• 此SELECT语句检索供应商不是1002和1003制造的所有产品；
• MySQL中NOT支持NOT对IN，BETWEEN和EXISTS子句取反。

运算符优先级

LIKE操作符

百分号（%）通配符

mysql> SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%'; +---------+--------------+ | prod_id | prod_name    | +---------+--------------+ | JP1000  | JetPack 1000 | | JP2000  | JetPack 2000 | +---------+--------------+ 2 rows in set (0.00 sec) 

• 将检索任意以jet起头的词，%告诉MySQL接受jet之后的任意字符。

下划线（—）通配符

mysql> SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; +---------+-------------+ | prod_id | prod_name   | +---------+-------------+ | ANV02   | 1 ton anvil | | ANV03   | 2 ton anvil | +---------+-------------+ 2 rows in set (0.00 sec) 

• 下划线（—）总是匹配一个字符。

正则表达式

基础字符匹配

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; +--------------+ | prod_name    | +--------------+ | JetPack 1000 | +--------------+ 1 row in set (0.04 sec)  mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name; +--------------+ | prod_name    | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec) 

• REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理；
• .它表示匹配任意一个字符。

进行OR匹配

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name; +--------------+ | prod_name    | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec) 

匹配几个字符之一

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; +-------------+ | prod_name   | +-------------+ | 1 ton anvil | | 2 ton anvil | +-------------+ 2 rows in set (0.00 sec) 

• [123]定义一组字符，它的意思是匹配1或2或3。

匹配范围

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name; +--------------+ | prod_name    | +--------------+ | .5 ton anvil | | 1 ton anvil  | | 2 ton anvil  | +--------------+ 3 rows in set (0.00 sec) 

• [1-5]定义了一个范围，这个表达式意思是匹配1到5。

匹配特殊字符

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '\.' ORDER BY prod_name; +--------------+ | prod_name    | +--------------+ | .5 ton anvil | +--------------+ 1 row in set (0.00 sec) 

• .匹配.，所以只检索出一行。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。

匹配字符类

• 为更方便工作，可以使用预定义的字符集，称为字符类（character class）。

匹配多个实例

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)' ORDER BY prod_name; +----------------+ | prod_name      | +----------------+ | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 2 rows in set (0.00 sec) 

• \([0-9] sticks?\)：\(匹配)，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\)匹配)。没有?，匹配stick和sticks会非常困难。

定位符

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\.]'; +--------------+ | prod_name    | +--------------+ | .5 ton anvil | | 1 ton anvil  | | 2 ton anvil  | +--------------+ 3 rows in set (0.00 sec) 

• ^匹配串的开始。因此，^[0-9\.]只在.或任意数字为串中第一个字符时才匹配它们。