数据过滤

-- AND操作符
-- 检索由1003制造且价格小于等于10美元的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

-- OR操作符
-- 检索由1002和1003制造的产品的名称和价格
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;

-- 计算次序
-- AND的优先级高于OR
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

-- IN操作符
-- 用来指定条件范围，取合法值的由逗号分隔的清单全部在圆括号中。
-- IN比OR执行更快，最大的优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

-- NOT操作符
-- 列出除1002，1003之外所有供应商供应的产品
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

用通配符进行过滤

LIKE操作符

LIKE指示MYSQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

-- 百分号（%）通配符
-- 表示任何字符出现任意次数
-- 例：找出所有jet起头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
-- 例：使用多个通配符,匹配任何位置包含anvil的值，不论它之前或之后出现什么字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
-- 例：找出s起头e结尾的所有产品
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

%可以匹配0个字符，%代表搜索模式中给定位置的0个、1个或多个字符 尾空格可能会干扰通配符，例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模 式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

-- 下划线(_)通配符
-- 只匹配单个字符而不是多个字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

使用技巧

• 不要过度使用通配符，如果其他操作符能够达到目的应该使用其他操作符
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索的开始处。 把通配符置于搜索模式的开始处搜索起来是最慢的。
• 仔细注意通配符的位置

用正则表达式进行搜索

使用MySQL正则表达式

基本字符匹配

-- 例：检索prod_name包含文本1000的所有行
-- REGEXP后所跟的东西作为正则表达式处理
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

-- `.`表示匹配任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

• LIKE和REGEXP的区别： LIKE '1000'匹配整个列值，等于'1000'时才会返回相应行，而REGEXP '1000'在列值内进行匹配，如果包含'1000'则会返回相应行。

-- 区分大小写
-- 使用关键字BINARY,例如
WHERE prod_name REGEXP BINARY 'JetPack .000';

进行OR匹配

-- `|`为正则表达式的OR操作符，表示匹配其中之一
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
-- 可以给出两个以上的OR条件
`1000|2000|3000`

匹配几个字符之一

-- `[]`表示匹配[]中的任意一个字符，例如`[123]`是`[1|2|3]`的缩写
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
-- output
+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
+-------------+

-- 和直接使用OR的区别：
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name
-- 匹配的是1 OR 2 OR 3 Ton，应该使用'[1|2|3] Ton'
-- output
+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
|JetPack 1000 |
|JetPack 2000 |
|TNT (1 stick)|
+-------------+

字符集合也可以被否定，为否定一个字集，在集合的开始处放置^，例如[^123]匹配除这些字符的任何东西

匹配范围

-- `[0123456789]`可以写成`[0-9]`，其他范围如`[a-z]`
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name
-- output
+-------------+
|  prod_name  |
+-------------+
| .5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
+-------------+

匹配特殊字符

-- 匹配'.'字符，如果使用
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
ORDER BY vend_name;
-- output
+---------------+
|   vend_name   |
+---------------+
| ACME          |
| Anvils R Us   |
| Furball Inc.  |
| Jet Set       |
| Jouets Et Ours|
| LT Supplies   |
+---------------+

-- 因为'.'为匹配任意字符，因此匹配特殊字符，必须用'\\'为前导

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
-- output
+---------------+
|   vend_name   |
+---------------+
| Furball Inc.  |
+---------------+

正则表达式中具有特殊意义的所有字符都要通过这种方式转义 \\也用来引用元字符

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

为了匹配\本身，需要使用\\\

匹配字符类

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:cntrl:]	空格和制表（同[\\t]）
[:digit:]	ASCII控制字符（ASCII）0到31和127
[:graph:]	任意数字（同[0-9]）
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
？	0个或1个匹配（等于{0，1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n.m}	匹配数目的范围（m不超过255）

例：

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name
-- output
+---------------+
|   prod_name   |
+---------------+
| TNT (1 stick) |
| TNT (5 sticks)|
+---------------+

-- '\\('匹配'('
'[0-9]'匹配任意数字
'stick?'匹配'stick'和'sticks'
'\\)'匹配')'

例：匹配连在一起的4位数字

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
-- output
+---------------+
|   prod_name   |
+---------------+
| JetPack 1000  |
| JetPack 2000  |
+---------------+
-- 也可以写成 '[0-9][0-9][0-9][0-9]'

定位符

元字符	说明
^	文本的开始
$	文本的结尾
[:<:]	词的开始
[:>:]	词的结尾

例：找出以一个数（包括小数点开头）开始的所有产品

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
-- output
+---------------+
|   prod_name   |
+---------------+
| .5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
+---------------+