mysql-4 数据检索(2)

用通配符进行过滤

like操作符  %通配符   %可以匹配任意字符

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';

下划线通配符   下划线只可以匹配一个字符

SELECT prod_name , prod_id FROM products WHERE prod_name LIKE '_ ton anvil';

用正则表达式进行搜索

基本字符匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

检索prod_name包含文本1000的所有行

 

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

注意

 SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;

 

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

为什么第一like的语句会显示为空呢，这是因为like在匹配整个列，如果被匹配的文本在列值中出现，like将不会找它，相应的行也不会被返回，除非是用通配符；而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回，这是一个非常重要的差别。

 

进行OR匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

匹配范围  [1-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;   .是匹配任意字符

为了匹配特殊字符，需要使用\\为前导，    \\- 表示查找-

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

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

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

sticks?匹配stick 或者sticks   \\(    匹配小括号

 

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符  （同[a-zA-Z]）
[:blank:]	空格和字符表 (同  \\t)
[:cntrl:]	ASCLL控制字符，
[:digit:]	任意数字  (同[0-9])
[:graph:]	与print相同，但是不包含空格
[:lower:]	任意小写字母   同（[a-z]）
[:pirnt:]	任意可以打印的字符
[:upper:]	任意大写字母  同[A-Z]
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

 

 定位符

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

 

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

以一个数字（包括小数点开始的数）开始的所有产品

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';
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;
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;