mysql-4 数据检索(2)

2018-06-17 23:14:30来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

用通配符进行过滤

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;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MYSQL介绍安装及一些问题解决

下一篇:mysql_建立索引的优缺点 #转自Starzm#