mysql-4 数据检索(2)
2024-08-28 17:33:45
用通配符进行过滤
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 '' 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 '' 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 '' 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;
最新文章
- Kooboo CMS 无聊随笔（2）
- HTML 5 视频(video)
- Leetcode: Range Addition
- MyEclipse XFire Web Service
- goldengate一些参数整理
- dpkg error
- BZOJ 1057 棋盘制作
- HDU 1247
- IT项目经理
- easyui datagrid属性和方法
- Google的两种广告推广方式
- delphi
- 一次完整的http请求过程
- 二、Python数据类型（一）
- extract method
- JavaScript解析机制与闭包原理实例详解
- centos7救援模式--单机模式(单用户模式)
- TCP/IP协议---UDP协议
- Qt画笔实现折线图
- 【Python】sql-内连接，左连接，右连接，union