MySQL分组与聚合函数以及运算符
一、分组与聚合函数
1.分组
分组统计
选择一个库,建立sh_goods表(博客中有写过如何创建该表)。
根据category_id进行分组,然后获取每个category_id分组下商品的最高价格,其中max()是MySQL提供的一个聚合函数,用于获取price字段的最大值。
select category_id,max(price) from sh_goods group by category_id;
(在MySQL 5.6等老版本中,分组后获取的字段列表,若非分组字段没有使用聚合函数,默认情况下只保留每组中的第一条记录,但是此操作在MySQL 5.7及以上版本中已被禁止。)
分组排序
根据sh_goods表中的分类id进行分组降序操作,查询并显示分组后每组的商品id以及商品的名称。
聚合函数group_concat()表示将指定字段值连接成一个字符串。
select category_id ,group_concat(id),group_concat(name)
from sh_goods group by category_id desc;
多分组统计
对sh_goods表,以评分score降序分组后,再以评论数升序排序,获取的数据包括商品数量、指定分组下的商品名以及对应的评论数,
select score,count(*),group_concat(name),comment
from sh_goods group by score desc,comment;
回溯统计
在根据指定字段分组后,系统又自动对分组的字段向上进行了一次新的统计数据,且该数据对应的分组字段值为NULL。
select category_id,count(* ) from sh_goods group by category_id with rollup;
(执行后的结果可知。在获取每种商品分类category_id下的商品数量后,系统又自动对获取的数量进行了一次累加统计,并且此累加的新数据对应的分组字段的值为NULL。)
多分组进行回溯统计
select score,comment,count(*) from sh_goods group by score,comment with rollup;
(注:MySQL的同一个查询语句中回溯统计(with rollup)与排序(order by)仅能出现一个。)
统计筛选
当对查询的数据执行分组操作时,可以利用having根据条件进行数据筛选,它与where功能相同,但二者有一定区别。
(1)where操作是从数据表中获取数据,将数据从磁盘存取到内存中,而having是对已存放到内存中的数据进行操作。
(2)having位于group by之后,而where为于group by之前
(3)having关键字后可以使用聚合函数,而where则不可以,通常情况下,having关键字与group by一起使用,对分组后的结果进行过滤。
查询sh_goods表中,获取评分score和评分数comment不同情况下,含有两件商品的对应商品id。
select score,comment,group_concat(id) from sh_goods group by score,comment having count(*)=2;
(首先根据评分(score)进行分组,然后再根据评分数(comment)进行分组,分组后利用having筛选商品数量等于2的数据信息。)
聚合函数
其中count()的参数设置为“ * ”时。表示统计符合条件的所有记录(包含NULL)。
二、运算符
1.算术运算符
无符号的加减乘法运算
select id,id+1,id-1,id*2 from sh_goods limit 5;
有符号的减法运算结果
select cast(id as signed)-3 from sh_goods limit 5;
含有精度的运算
下面查询sh_goods表,获取5星好评的商品添加850件库存后的值,以及75折促销后的价格。
select name,price,stock,price0.75,stock+850.00 from sh_goods where score =5;
(price的精度为2,stock没有精度。因此,price0.75的运算结果精度为4(2+2),stock+850.00的运算结果精度为2(0与2比较2大))。
“/”运算
除法操作运算结果使用浮点数表示,浮点数的精度等于被除数的精度加上系统变量div_precision_increment设置的除法精度增长值,可通过以下SQL语句查找其默认值。
show variables like ‘div_precision_increment’;
从sh_goods表中获取库存量大于200的商品,然后再查询对应的商品名称、库存量以及剩余1/5的库存量(假设75折后卖出了4/5)。
select name,stock,stock/5 from sh_goods where stock>200;
NULL参与算术运算符
参与运算结果均为NULL
select null+1,3-null,2*null,2/null,null/3;
(结果都为NULL)
DIV与MOD运算符
DIV与“/”都能实现除法运算,前者除法运算会去除小数部分,只返回整数部分。
select 8/5,8 div 5 ,0.6/1.2,0.6 div 1.2;
MOD与“%”功能相同,都用于取模运算。
select 8 mod 5, -8 mod 5, 8 mod -5, -8 mod -5;
2.比较运算符
比较运算符的结果有三种,分别为1(ture,表示为真)、0(false,表示为假)或NULL。
数据类型自动转换
对数字和字符串进行比较,若参与比较的操作数的数据类型不同,则MySQL会自动将其转换为同类型的数据进行比较。
select 5>=‘5’,3.0<>3;
(“<>”表示不等于)
比较结果为NULL
select 0=null,null<1,null>2;
(执行结果均为null)
“=”与“<=>”的区别
二者均可用于比较数据是否相等,区别在于后者可以对null值进行比较。
select null=null,null=1,null<=>null,null<=>1;
between and
获取sh_goods表中价格在2000到6000的商品信息
select id,name,price from sh_goods where price between 2000 and 6000;
is null与is not null
获取sh_goods表中关键词不为空且价格最高的两件商品id、name、price和keyword。
select id,name,price,keyword from sh_goods where keyword is not null order by price desc limit 2;
ike与not like
like的作用是模糊匹配,not like 用于获取匹配不到的数据。
在sh_goods表中,获取商品名称中含有“笔”的商品id、name、price和content。
select id,name,price,content from sh_goods where name like’%笔%’;
(“%”可以匹配任意0到多个字符)
正字匹配查询
获取sh_goods表中描述字段内含有“人”或“必备”词语的商品id、name和content字段内容。
select id,name,content from sh_goods where content regexp’人|必备’;
(符号“|”在正则中表示分隔符,用于分隔多种条件,在匹配时只要指定字段满足分隔符左右两边条件中的一个,就表示匹配成功。)
3.逻辑运算符
逻辑与
select id,name,price from sh_goods where keyword=‘电子产品’&&score=5;
(相当于select id,name,price from sh_goods where (keyword,score)=(‘电子产品’,5);)
逻辑或
select id,name,price ,score from sh_goods where score=4.5||price<10;
(只要满足score等于4.5,或price小于10中任意一个条件,数据就会被查出。)
在进行逻辑或操作时,若操作数中含有null,而另一个操作数为1(真),则结果为1;若另一个操作数为0(假),则结果为null。
select 1||null,null||1,0||null, null||0;
逻辑非
逻辑非的操作数仅有一个,当操作数为0(假)时,则运算结果为1;当操作数为1(真)时,则运算结果为0;当操作数为null时,运算结果为null。
select not 10,not 0,not null,not 0+!0,!0+!0;
4.赋值运算符
“=”既可以用于比较数据是否相等,又可以表示赋值。因此,为了避免系统分不清楚运算符“=”表示赋值还是比较的含义,用“:=”表示赋值运算符。
update sh_goods set stock =1000 where score=4.5;
(stock =1000中的运算符“=”表示赋值的含义,将符合where比较条件的记录对应的stock字段设置为1000。因此,“stock=1000”中的“=”可用“:=”代替。)
5.位运算符
位运算符是针对二进制的每一位进行运算的符号,运算结果类型为bigint,最大范围可以是64位。
MySQL 5.7中参与位运算的数据只能是bigint类型,而在MySQL 8.0中则允许二进制字符串类型的参数,如bigary、varbinary和blob。因此,MySQL5.7中二进制类型字段的位运算可能在MySQL 8.0中产生不同的结果,系统也会报相关的警告信息。
下面案例演示varbinary类型的数据位运算:
创建数据表,含有varbinary类型的字段
create table mybin (b1 varbinary(20),b2 varbinary(20));
插入数据
insert into mybin values(2,6),(3,1),(4,9);
查看数据表中两个字段的按位与、按位或结果
select b1&b2,b1|b2 from mybin;