(新手友好)MySQL学习笔记(6):分组查询，正则表达式

目录

分组查询

创建分组

过滤分组

分组查询练习

正则表达式

匹配单个实例

匹配多个实例

正则表达式练习

练习答案

分组查询练习答案

正则表达式练习答案

---

分组查询

创建分组

        group by 子句：根据一个或多个字段对结果集进行分组，在分组的字段上可以使用count，sum，avg等聚合函数，但是聚合函数不能出现在group by的后边，这点与where一样。

select 字段1[,字段2,function(字段1),function(字段2),....]
from 表名
group by 字段;

 下面给两个例子以区分where与group by的适用场景

#统计部门ID为20的员工数
select count(*) from emp where deptno=20
#统计每个部门的人数
select deptno，count(*)
from emp
group by deptno;

注意：

• 如果分组列中具有NULL值，则NULL值将作为一个分组返回，如果列中有多行NULL值，它们将作为一组。
• group by 子句必须出现在where子句后边，order by子句之前，意思就是分组前过滤出符合where 条件的数据，再将这部分数据按照group by条件进行分组

过滤分组

having 子句：having 非常类似于where。唯一的差别是where过滤行，而having 过滤分组。having 必须和group by一起使用。

having和where的区别：where是分组前过滤，having是分组后过滤，但是聚合函数count()等可以在having后使用

举两个例子

#统计部门中员工超过5人的部门的员工数
select deptno，count(*)
from emp 
group by deptno
having count(*) > 5;#统计部门中员工月薪超过1000的员工超过两人的部门的员工数
select deptno,count(*)
from emp
where sal > 1000
group by deptno
having count(*) > 2;

分组查询练习

练习答案仍在最后

1. 查询 该 公司 有哪 几种 岗位 以及 每个岗位 的 人数

2.计算每个岗位的最高薪水，并且由低到高进行排序

3.计算每个部门平均薪水

4.计算不同部门不同岗位的最高薪水

5.找出每个工作岗位的最高薪水，除manager之外

6.找出每个工作岗位的平均薪水，显示平均薪水大于2000的

正则表达式

        regexp操作符，regexp操作符后边跟的就是正则表达式，正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。

like与regexp的区别：

        like匹配整个列，如果被匹配的文本仅在列值中出现(没有配合其他通配符)，立刻将找不到。regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。

下面举个例子帮助理解

#不加通配符时like仅仅会找到名字为s的员工
select * from emp where ename like "s";
#加通配符后like会找到名字包含s的员工
select * from emp where ename like "%s%";
#regexp则会直接找到名字里包含s的员工
select * from emp where ename regexp "s";

匹配单个实例

• |：表示匹配其中之一，使用 | 从功能上类似or
• [ ]：匹配字段之一，[ ]是另一种形式的or语句。例如[123]为[1|2|3]的缩写
• [ - ]：匹配范围，使用 - 来定义一个范围。例如[1-3],[a-z]等。
• \\：转义字符，多数正则表达式使用单个反斜杠作为转义字符，但MySQL要求两个反斜杠（MySQL自己解释一个，正则表表达式库解释另一个）。

下面有几个例子帮助理解

#查询字段中是否包含a或b
select "acddp" regexp "[ab]";
#查询字段中是否包含1-5的任意一个数字
select "89445687" regexp "[1-5]";
#查询字段中是否包含"[1-5]"字段
select "_ajegdbas" regexp "\\[1-5]";

• 匹配字符类：存在找出你自己经常使用的数字，所有字母字符或者所有数字字母字符等的匹配。为了更方便地工作，可以使用预定义的字符集，称为字符类。稍作了解，工作上用得到时能够有印象找得到就好，可以做笔记上。

类	说明
[[: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]）

匹配多个实例

• 常用元字符

元字符	说明
.	匹配任意字符
^	匹配字符串的开始，^在[]中表示否定
$	匹配字符串的结束

给几个例子帮助大家理解

#查询字符串中是否包含任意字符+'a'的子字符串
select 'baan' regexp '.a';#'ba'就属于任意字符+'a'的子字符串
#查询字符串开头是否包括'bn'子字符串
select 'baan' regexp '^bn';
#查询字符串结尾是否包括'bn'子字符串
select 'baan' regexp 'bn$';

• 重复元字符（修饰前一个字符）

元字符	说明
*	任意个匹配
+	一个或多个匹配（等于{1,}）
?	0个或1个（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于只等数目的匹配
{n,m}	匹配数目的范围（m不超过255）

给几个例子帮助理解

#查询字符串中是否出现'bn','ban','baan','baaan'等子字符串
select 'baaaaan' regexp 'ba*n';#'*'仅仅修饰'a'字符
#查询字符串中是否出现'ban','baan','baaan'等'a'不少于1个的子字符串
select 'baaaan' regexp 'ba{1,}n';#同样的'{1,}'仅修饰'a'字符

正则表达式练习

将下面利用正则表达式进行查询的结果写出来（字串存在为1或不存在为0），答案同样放在最后

select 'baan' regexp '^ba*n';
select 'bn' regexp '^ba*n';
select 'bn' regexp '^ba+n';
select 'bn' regexp '^ba?n';
select 'baan' regexp '^ba?n';select 'pin' regexp 'pi|apa';
select 'pin' regexp '^(pi|apa)$';
select 'apa' regexp '^(pi|apa)$';
select 'pin' regexp '^(p|qin)$';
select 'pin' regexp '^([pq]in)$';select 'fofo' regexp '^fo';
select 'fo\no' regexp '^fo\no$';
select 'fo\no' regexp '^fp\\no';
select 'fo\eo' regexp '^fo\\eo$';
select 'fo\|o' regexp '^fo\\|o$';
select 'fofo' regexp '^fo';

练习答案

分组查询练习答案

#查询该公司有哪几种岗位以及每个岗位的人数
select job,count(*)
from emp
group by job;
#计算每个岗位的最高薪水，并且由低到高进行排序
select job,max(sal)
from emp
group by job
order by max(sal) desc;
#计算每个部门平均薪水
select deptno,avg(sal)
from emp 
group by deptno;
#计算不同部门不同岗位的最高薪水
select deptno,job,max(sal)
from emp 
group by deptno,job;
#找出每个工作岗位的最高薪水，除manager之外
select job,max(sal)
from emp 
where job not like "manager"
group by job;
#找出每个工作岗位的平均薪水，显示平均薪水大于2000的
select job,avg(sal)
from emp 
group by job
having avg(sal) > 2000;

正则表达式练习答案

select 'baan' regexp '^ba*n';#1
select 'bn' regexp '^ba*n';#1
select 'bn' regexp '^ba+n';#0
select 'bn' regexp '^ba?n';#1
select 'baan' regexp '^ba?n';#0select 'pin' regexp 'pi|apa';#1
select 'pin' regexp '^(pi|apa)$';#0
select 'apa' regexp '^(pi|apa)$';#1
select 'pin' regexp '^(p|qin)$';#0
select 'pin' regexp '^([pq]in)$';#1select 'fofo' regexp '^fo';#1
select 'fo\no' regexp '^fo\no$';#1
select 'fo\no' regexp '^fp\\no';#0
select 'fo\eo' regexp '^fo\\eo$';#1
select 'fo\|o' regexp '^fo\\|o$';#1
select 'fofo' regexp '^fo';#1