-------------------------------------------------------------------------------------------------------------
教程大纲:
http://blog.csdn.net/qq78442761/article/details/76018310
-------------------------------------------------------------------------------------------------------------
7.文本匹配
a) 使用正则表达式进行比较 关键字为 regexp
b) 首先我们更新一组数据,再添加一组数据
接着我们来对比三张匹配方式
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBNRFV4TURReVAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
由上图易知,三种匹配的不同
如果要区分大小写,则加上binary
如下操作
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBNVEV4TXpNNFAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
另外一种为选择匹配
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBNVE14TWpJMFAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
重复匹配
1.*:0个或多个匹配
2.+:1个或多个匹配
3.?:0 个或1个匹配
4.{n}:指定数目的匹配
5.{n, }:不少于指定数目的匹配
6.{n,m}:匹配数目的范围(其中,m不超过255)
我们先插入一组数据
使用定位匹配符
定位符:
^:文本的开始
$:文本的结尾
[[:<:]]:词的开始
[[:>:]]:词的结尾
如查询cust_id以3-5开头的信息
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBNalV4T0RnelAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
判定范围
两种:
1.between and
2.In
下面看操作
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBNekUwTURJNVAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
判断空值
使用关键字:IS
如下所示:
子查询
1.表子查询:结果返回一个表
2.行子查询:结果是带一个或多个值的一行数据
3.列子查询:结果是一列数据,该列可以有一行或多行
4.标量子查询:结果返回一个值
Order by 子句
按照一定的顺序进行排列
Asc:升序 desc:降序
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBOREEyTWpReVAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
Limit子句
用于限制select语句
查找从第二位客户开始三位客户的所有信息和从第一位客户开始二位客户的所有信息
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBOVFE1Tnpnd1AzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
Union语句
多行查询
如下操作
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBOak0zTURVMFAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
可以换成如下
Group by子句
将结果集中的数据行根据选择列的值进行逻辑分组
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBOelEyT1RFelAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
Cust_sex分组
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBPREF6T1RnNFAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)
Rollup为统计所有
Having 子句
![MySQL入门之扩展匹配符、order by、limit、union、group by、having子句 MySQL入门之扩展匹配符、order by、limit、union、group by、having子句](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9nby9hSFIwY0RvdkwybHRaeTVpYkc5bkxtTnpaRzR1Ym1WMEx6SXdNVGN3TnpJeE1qRTBPRE14TXpBMVAzZGhkR1Z5YldGeWF5OHlMM1JsZUhRdllVaFNNR05FYjNaTU1rcHpZakpqZFZrelRtdGlhVFYxV2xoUmRtTllSVE5QUkZFd1RXcGpNazFSUFQwdlptOXVkQzgxWVRaTU5Vd3lWQzltYjI1MGMybDZaUzgwTURBdlptbHNiQzlKTUVwQ1VXdEdRMDFCUFQwdlpHbHpjMjlzZG1Vdk56QXZaM0poZG1sMGVTOURaVzUwWlhJPQ%3D%3D.jpg?w=700)