[mysql基础文档]-26-内存查询having

来源:互联网 时间:1970-01-01

在select子查询中,where查询只能对MySQL保存在硬盘上的数据进行查询,而使用as定义的变量,或使用where查询返回的结果集是在内存中的,想要对内存中这一部分数据进行查询,可以使用本文介绍的having。

文章目录 [1].如何使用having进行内存查询 [2].having内存查询实例 [1].如何使用having进行内存查询

本文所使用的数据表为“[mysql基础文档]-24-select查询基础”第一部分所创建的cellphone表,请参考:练习数据表

having运行方式实例:

//实例1,使用as运算符将结果集保存在内存中,并使用having筛选内存中的结果集//前面已经提到,数据表中每一列的列头字段,相当于一个变量,变量是能够参与运算的,下面的语句中数据库会读取每条记录selling_price列的值,并用它减去对应这条记录中sale_price的值,最后用as运算符将结果集赋值给以save命名的列mysql> select (selling_price-sale_price) as save from cellphone;+--------+| save |+--------+| 150.00 || 200.00 || 170.00 || 161.00 || 600.00 || 598.00 || 300.00 || 300.00 || 200.00 || 189.00 || 300.00 || 431.00 || 300.00 || 200.00 || 290.00 || 200.00 || 600.00 || 300.00 || 200.00 || 200.00 |+--------+//使用as运算符将结果赋予一个变量,变量是储存在内存中的,而where只能对硬盘中的数据进行筛选,所以这里报错了,告诉你没有save这一列mysql> select (selling_price-sale_price) as save from cellphone where save>300;ERROR 1054 (42S22): Unknown column 'save' in 'where clause'//对保存在内存中的结果集,可以使用having筛选,选出save列中大于300的字段显示mysql> select (selling_price-sale_price) as save from cellphone having save>300;+--------+| save |+--------+| 600.00 || 598.00 || 431.00 || 600.00 |+--------+//实例2,使用where子运算筛选出一个结果集,这个结果集是保存在内存中的,再用having对这个内存中的结果集进行再次筛选//cellphone表中标价比打折价高出200的商品名称筛选结果mysql> select goods_name,sale_price,selling_price from cellphone where selling_price-sale_price>200; +--------------+------------+---------------+| goods_name | sale_price | selling_price |+--------------+------------+---------------+| GalaxyN9200 | 5388.00 | 5988.00 || iPhone6A1586 | 4288.00 | 4886.00 || MeizuNote2 | 899.00 | 1199.00 || Huawei4X | 999.00 | 1299.00 || MX5 | 1899.00 | 2199.00 || iPhone5s | 3188.00 | 3619.00 || GalaxyN9109W | 2698.00 | 2998.00 || iPhone4s | 1398.00 | 1688.00 || iPhone6s | 5288.00 | 5888.00 || MX4Pro | 1599.00 | 1899.00 |+--------------+------------+---------------+//在where后添加having语句,对where筛选结果再次进行筛选,选出标价比打折价高出300的商品名称,where筛选出来的结果集暂时保存在内存中,对内存中的结果集进行操作需要使用havingmysql> select goods_name,sale_price,selling_price from cellphone where selling_price-sale_price>200 having selling_price-sale_price>300; +--------------+------------+---------------+| goods_name | sale_price | selling_price |+--------------+------------+---------------+| GalaxyN9200 | 5388.00 | 5988.00 || iPhone6A1586 | 4288.00 | 4886.00 || iPhone5s | 3188.00 | 3619.00 || iPhone6s | 5288.00 | 5888.00 |+--------------+------------+---------------+

P.s:where和having共存,where肯定是在having前面,因为必须要将磁盘上的数据查出来放在内存之后才能用having查到这些数据。

[2].having内存查询实例

下面来看一个学生成绩查询实例,要求查询出下表中2门及2门以上不及格者(mark<60)的平均成绩:

//创建成绩表scoremysql> create table score(name char(20) not null default '',subject char(20) not null default '',mark tinyint unsigned not null default 0);//插入6条记录mysql> insert into score values-> ('tom','math',90),-> ('tom','english',50),-> ('tom','art',59),-> ('john','math',55),-> ('john','art',40),-> ('jack','art',52);//表完整结构mysql> select * from score;+------+---------+------+| name | subject | mark |+------+---------+------+| tom | math| 90 || tom | english | 50 || tom | art | 59 || john | math| 55 || john | art | 40 || jack | art | 52 |+------+---------+------+//将初始问题拆分成不同的步骤,对步骤进行再加工是解决MySQL查询问题的关键,下面的每一步都是对上面一步结果的再加工,而实际工作中只需要写出最后一步即可,这里仅仅是想告诉大家这种思维方式//第一步,查询出score表中每条记录mark<60的布尔值,0表示false,1表示true mysql> select name,mark<60 from score;+------+---------+| name | mark<60 |+------+---------+| tom | 0 || tom | 1 || tom | 1 || john | 1 || john | 1 || jack | 1 |+------+---------+//group by使用name进行分组,并使用sum()函数将分组后每一组的(mark<60)结果加起来 mysql> select name,sum(mark<60) from score group by name;+------+--------------+| name | sum(mark<60) |+------+--------------+| jack |1 || john |2 || tom |2 |+------+--------------+//将sum(mark<60)的结果赋予ms变量,并使用having查询出ms大于等于2的结果,ms>=2就意味着“2门及2门以上不及格”mysql> select name,sum(mark<60) as ms from score group by name having ms>=2;+------+------+| name | ms |+------+------+| john |2 || tom |2 |+------+------+//最后一步,添加一列,使用avg()函数计算这些“2门及2门以上不及格”者的平均成绩,得到最终结果mysql> select name,sum(mark<60) as ms,avg(mark) from score group by name having ms>=2;+------+------+-----------+| name | ms | avg(mark) |+------+------+-----------+| john |2 | 47.5000 || tom |2 | 66.3333 |+------+------+-----------+

对于上面这个问题,还存在着一个经典标准错误答案,请看下面的实例

//将sum()函数换成count()函数,得到的结果看上去也是对的mysql> select name,count(mark<60) as ms,avg(mark) from score group by name having ms>=2;+------+----+-----------+| name | ms | avg(mark) |+------+----+-----------+| john | 2 | 47.5000 || tom | 3 | 66.3333 |+------+----+-----------+//现在我们对score表再添加两条记录mysql> insert into score values-> ('qingsword','math',100),-> ('qingsword','english',100);//使用相同的语句发现了问题,本不该出现在结果中的qingsword,也出现了mysql> select name,count(mark<60) as ms,avg(mark) from score group by name having ms>=2;+-----------+----+-----------+| name | ms | avg(mark) |+-----------+----+-----------+| john | 2 | 47.5000 || qingsword | 2 | 100.0000 || tom | 3 | 66.3333 |+-----------+----+-----------+//出现这种问题的关键就是count()函数,还记得第一步中的结果吗?tom三行,john两行,jack一行,count()函数仅仅统计的是行数,只要行数大于等于2的结果,都会出现在这里面,因为jack只有一行,所以第一次操作中,并没有出现这样的错误。

P.s:上面的实例中,错误的地方在于count()统计函数,score<60会得到一个布尔返回值条件为真结果是1,条件为假结果是0,此时不管分数是否小于60,都能得到一行值,而count仅仅只统计行数,这就是为什么第一次错误的查询会看来正确的原因,因为jack只有一条记录,当有一个全部及格且大于或等于两条记录的人出现的时候,count统计出这个人的score<60的结果条数是>=2的,满足having gk>=2,所以显示出这个人。

[**] 注:如文中未特别声明转载请注明出自:QingSword.COM


相关阅读:
Top