mysql>SELECT prod_name,prod_price FROM products WHERE vend_id NOTIN(1002,1003)ORDERBY 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|+--------------+------------+5rowsinset(0.01 sec)
此SELECT语句检索供应商不是1002和1003制造的所有产品;
MySQL中NOT支持NOT对IN,BETWEEN和EXISTS子句取反。
运算符优先级
优 先 级 由 低 到 高 排 列
运 算 符
1
=(赋值运算)、:=
2
II、OR
3
XOR
–
–
5
NOT
6
BETWEEN、CASE、WHEN、THEN、ELSE
7
=(比较运算)、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8
|
9
&
10
<<、>>
11
-(减号)、+
12
*、/、%
13
^
14
-(负号)、〜(位反转)
15
!
LIKE操作符
百分号(%)通配符
mysql>SELECT prod_id,prod_name FROM products WHERE prod_name LIKE'jet%';+---------+--------------+| prod_id | prod_name |+---------+--------------+| JP1000 | JetPack 1000|| JP2000 | JetPack 2000|+---------+--------------+2rowsinset(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 |+---------+-------------+2rowsinset(0.00 sec)
下划线(—)总是匹配一个字符。
正则表达式
基础字符匹配
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'1000'ORDERBY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000|+--------------+1rowinset(0.04 sec)
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'.000'ORDERBY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000|| JetPack 2000|+--------------+2rowsinset(0.00 sec)
REGEXP后所跟的东西作为正则表达式(与文字正文1000匹配的一个正则表达式)处理;
.它表示匹配任意一个字符。
进行OR匹配
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'1000|2000'ORDERBY prod_name;+--------------+| prod_name |+--------------+| JetPack 1000|| JetPack 2000|+--------------+2rowsinset(0.00 sec)
匹配几个字符之一
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'[123] Ton'ORDERBY prod_name;+-------------+| prod_name |+-------------+|1 ton anvil ||2 ton anvil |+-------------+2rowsinset(0.00 sec)
[123]定义一组字符,它的意思是匹配1或2或3。
匹配范围
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'[1-5] Ton'ORDERBY prod_name;+--------------+| prod_name |+--------------+|.5 ton anvil ||1 ton anvil ||2 ton anvil |+--------------+3rowsinset(0.00 sec)
[1-5]定义了一个范围,这个表达式意思是匹配1到5。
匹配特殊字符
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'\\.'ORDERBY prod_name;+--------------+| prod_name |+--------------+|.5 ton anvil |+--------------+1rowinset(0.00 sec)
mysql>SELECT prod_name FROM products WHERE prod_name REGEXP'^[0-9\\.]';+--------------+| prod_name |+--------------+|.5 ton anvil ||1 ton anvil ||2 ton anvil |+--------------+3rowsinset(0.00 sec)