NULL检测
NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
mysql> select cust_id,cust_name,cust_email from customers where cust_email is null; +---------+-------------+------------+ | cust_id | cust_name | cust_email | +---------+-------------+------------+ | 10002 | Mouse House | NULL | | 10005 | E Fudd | NULL | +---------+-------------+------------+ 2 rows in set (0.00 sec) mysql>
and操作符
AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
如果有多个条件,每加一个条件就需要加一个and。
mysql> select prod_id,prod_name,prod_price from products where vend_id = 1003 and prod_price <=10 ; +---------+----------------+------------+ | prod_id | prod_name | prod_price | +---------+----------------+------------+ | FB | Bird seed | 10.00 | | FC | Carrots | 2.50 | | SLING | Sling | 4.49 | | TNT1 | TNT (1 stick) | 2.50 | | TNT2 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 5 rows in set (0.00 sec)
or操作符
OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。
mysql> select prod_id,prod_name,prod_price,vend_id from products where vend_id = 1003 or vend_id=1002 ; +---------+----------------+------------+---------+ | prod_id | prod_name | prod_price | vend_id | +---------+----------------+------------+---------+ | DTNTR | Detonator | 13.00 | 1003 | | FB | Bird seed | 10.00 | 1003 | | FC | Carrots | 2.50 | 1003 | | FU1 | Fuses | 3.42 | 1002 | | OL1 | Oil can | 8.99 | 1002 | | SAFE | Safe | 50.00 | 1003 | | SLING | Sling | 4.49 | 1003 | | TNT1 | TNT (1 stick) | 2.50 | 1003 | | TNT2 | TNT (5 sticks) | 10.00 | 1003 | +---------+----------------+------------+---------+ 9 rows in set (0.00 sec)
组合次序
组合优先级:
and > or
SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。
假如需要列出价格为10美元（含）以上且由1002或1003制造的所有产品。
mysql> select vend_id,prod_name ,prod_price from products where vend_id =1002 or vend_id =1003 and prod_price >=10; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Detonator | 13.00 | | 1003 | Bird seed | 10.00 | | 1002 | Fuses | 3.42 | | 1002 | Oil can | 8.99 | | 1003 | Safe | 50.00 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 6 rows in set (0.00 sec)
只有1003的价格大于10
使用括号组合条件:
mysql> select vend_id,prod_name ,prod_price from products where (vend_id =1002 or vend_id =1003) and prod_price >=10; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Detonator | 13.00 | | 1003 | Bird seed | 10.00 | | 1003 | Safe | 50.00 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 4 rows in set (0.00 sec)
in操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。
mysql> select vend_id,prod_name ,prod_price from products where vend_id in (1002,1003) order by prod_name; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Bird seed | 10.00 | | 1003 | Carrots | 2.50 | | 1003 | Detonator | 13.00 | | 1002 | Fuses | 3.42 | | 1002 | Oil can | 8.99 | | 1003 | Safe | 50.00 | | 1003 | Sling | 4.49 | | 1003 | TNT (1 stick) | 2.50 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 9 rows in set (0.00 sec)
in的功能与where相当,其优点在于:
- 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
- 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
- IN操作符一般比OR操作符清单执行更快。
- IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
not操作符
NOT WHERE子句中用来否定后跟条件的关键字。
列出除1002和1003之外的所有供应商制造的产品
mysql> select vend_id,prod_name ,prod_price from products where vend_id not in (1002,1003) order by prod_name; +---------+--------------+------------+ | vend_id | prod_name | prod_price | +---------+--------------+------------+ | 1001 | .5 ton anvil | 5.99 | | 1001 | 1 ton anvil | 9.99 | | 1001 | 2 ton anvil | 14.99 | | 1005 | JetPack 1000 | 35.00 | | 1005 | JetPack 2000 | 55.00 | +---------+--------------+------------+ 5 rows in set (0.00 sec)
MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
like操作符
通配符:
- '%'表示任何字符出现任意次数。(0,1,2,3......)
- '_'下划线只匹配单个字符而不是多个字符。
mysql> select vend_id,prod_name ,prod_price from products where prod_name like '%anvil%'; +---------+--------------+------------+ | vend_id | prod_name | prod_price | +---------+--------------+------------+ | 1001 | .5 ton anvil | 5.99 | | 1001 | 1 ton anvil | 9.99 | | 1001 | 2 ton anvil | 14.99 | +---------+--------------+------------+ 3 rows in set (0.00 sec)
mysql> select vend_id,prod_name ,prod_price from products where prod_name like '_ ton anvil'; +---------+-------------+------------+ | vend_id | prod_name | prod_price | +---------+-------------+------------+ | 1001 | 1 ton anvil | 9.99 | | 1001 | 2 ton anvil | 14.99 | +---------+-------------+------------+ 2 rows in set (0.00 sec)
正则表达式
like与正则表达式
LIKE 匹配整个列，如果被匹配的文本在列值中出现，LIKE 将不会找到它，相应的行也不会被返回（除非使用通配符）。而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP 将会找到它，相应的行将被返回，并且 REGEXP 能匹配整个列值（与 LIKE 相同的作用）。
MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。
mysql> select vend_id,prod_name ,prod_price from products where prod_name like '1000'; Empty set (0.00 sec) mysql> select vend_id,prod_name ,prod_price from products where prod_name regexp '1000'; +---------+--------------+------------+ | vend_id | prod_name | prod_price | +---------+--------------+------------+ | 1005 | JetPack 1000 | 35.00 | +---------+--------------+------------+ 1 row in set (0.00 mysql> select vend_id,prod_name ,prod_price from products where prod_name like 'jetpack 1000'; +---------+--------------+------------+ | vend_id | prod_name | prod_price | +---------+--------------+------------+ | 1005 | JetPack 1000 | 35.00 | +---------+--------------+------------+ 1 row in set (0.00 sec)
or匹配
为搜索两个串之一（或者为这个串，或者为另一个串），使用 '|'
mysql> select prod_name from products where prod_name regexp '1000|2000'; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.00 sec) 🐛🐛之前乱加空格，导致出了点问题
mysql> select prod_name from products where prod_name regexp '1000 | 2000'; +--------------+ | prod_name | +--------------+ | JetPack 2000 | +--------------+ 1 row in set (0.00 sec)
⚠️⚠️
仔细看 regexp '1000 | 2000'
1000后有一个空格，2000前有一个空格
对于最开始的结果
JetPack 1000和JetPack 2000由于
JetPack 1000在1000后没有空格，所以并没有匹配上JetPack 2000在2000前存在空格，所以匹配上了
匹配几个字符之一
匹配任何单一字符,通过指定一组用 [和] 括起来的字符来完成
mysql> select prod_name from products where prod_name regexp '[123] Ton'; +-------------+ | prod_name | +-------------+ | 1 ton anvil | | 2 ton anvil | +-------------+ 2 rows in set (0.00 sec)
区别于 '1|2|3 Ton'
mysql> select prod_name from products where prod_name regexp '1|2|3 Ton'; +---------------+ | prod_name | +---------------+ | 1 ton anvil | | 2 ton anvil | | JetPack 1000 | | JetPack 2000 | | TNT (1 stick) | +---------------+ 5 rows in set (0.00
之所以这样是由于MySQL假定你的意思是'1'或'2'或'3 ton'。除非把字符|括在一个集合中，否则它将应用于整个串。
匹配范围
mysql> select prod_name from products where prod_name regexp '[1-5] Ton'; +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.00 sec)
匹配特殊字符
为了匹配特殊字符，必须用 \ 为前导。\- 表示查找-， \. 表示查找.。
mysql> select vend_name from vendors where vend_name regexp '\.' order by vend_name; +--------------+ | vend_name | +--------------+ | Furball Inc. | +--------------+ 1 row in set (0.00 sec)
匹配字符类
存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类（character class）。
| 类 | 说明 |
| [:alnum:] | 任意字母和数字（同[a-zA-Z0-9]） |
| [:alpha:] | 任意字符（同[a-zA-Z]） |
| [:blank:] | 空格和制表（同[\t]） |
| [:cntrl:] | ASCII控制字符（ASCII 0到31和127） |
| [:digit:] | 任意数字（同[0-9]） |
| [:graph:] | 与[:print:]相同，但不包括空格 |
| [: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） |
定位符
| 元字符 | 说明 |
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
表格
| 字符 | 其它信息 | |
| [ 和 ] | 左右方括号用于指定字符类。字符类是进行匹配时所要依据的一组字符。除连字符 (-) 和脱字符 (^) 外，在字符类中指定的元字符和量词（如 * 和 {m}，分别为元字符和量词）没有特殊意义，可当作实际字符进行运算。 | |
| ***** | 星号可用于与字符匹配 0 次或多次。例如，REGEXP '.*abc' 匹配的字符串以 abc 结尾并以任何前缀开头。因此，aabc、xyzabc 和 abc 匹配，但 bc 和 abcc 则不匹配。 |
|
| ? | 问号可用于与字符匹配 0 次或 1 次。例如，'colou?r' 匹配 color 和 colour。 |
|
| + | 加号可用于与字符匹配 1 次或多次。例如，'bre+' 匹配 bre 和 bree，但不匹配 br。 |
|
| - | 可以在字符类中使用一个连字符来表示一个范围。例如，REGEXP '[a-e]' 匹配 a、b、c、d 和 e。 |
|
| % | 百分号可与 SIMILAR TO 配合使用来匹配任意数目的字符。不将百分号视为 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。当指定时，它匹配百分号 (%)。 | |
| _ （下划线字符） | 可将下划线与 SIMILAR TO 配合使用来匹配单个字符。不将下划线视为 REGEXP 和 REGEXP_SUBSTR 所使用的元字符。当指定时，它匹配下划线 (_)。 | |
| ** | ** | 管道符号用于指定匹配字符串时要使用的替代模式。在由竖线分隔的一行模式中，竖线被解释为 OR，匹配过程从最左侧的模式开始，在找到第一个匹配项时停止。因此，您应按优先级的降序顺序列出模式。您可以指定任意数量的替代模式。 |
| ( 和 ) | 当左括号和右括号用于正则表达式的各个组合部分时，它们为元字符。例如，(ab)* 匹配零个或多个 ab 的重复项。与使用数学表达式一样，您使用组合来控制正则表达式各部分的计算顺序。 |
|
| { 和 } | 当左大括号和右大括号用于指定量词时，它们为元字符。量词指定一个模式要构成某个匹配所必须重复的次数。例如：- {m} 匹配某个字符正好 m 次。例如，'519-[0-9]{3}-[0-9]{4}' 匹配 519 地区号中的一个电话号码（假定数据按语法中定义的方式进行格式设置）。 |
- {m,} 匹配某个字符至少 m 次。例如，
'[0-9]{5,}'匹配任何含有五个或更多数字的字符串。 - {m,n} 匹配某个字符至少 m 次，但不超过 n 次。例如，
SIMILAR TO '_{5,10}'匹配任何含有 5 到 10（含 5 和 10）个字符的字符串。 | | ** | 反斜线被用作元字符的转义字符。它也可被用于转义非元字符。 | | ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 | | **∗∗∣匹配输入字符串的结束位置。如果设置了RegExp对象的Multiline属性，** | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，∗∗∣匹配输入字符串的结束位置。如果设置了RegExp对象的Multiline属性， 也匹配 '\n' 或 '\r' 之前的位置。 | | . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 | | : | 在字符集中使用冒号来指定子字符类。例如，'[[:alnum:]]'。 |
