现在在实验室,总结一下上午学到的一个SQL方法。下面我创建一个数据表:
SELECT
* FROM `students`;
可以看到数据表中存在六条数据,如果使用传统的查询方法,我们需要三条SQL语句,分别是:
select count(*) from students where score<60;
select count(*) from students where score>80;
select count(*) from students where score between 60 and 80;
现在介绍三种使用一条SQL语句实现这个操作的方法:
case 0:
select
sum(if(score<60 , 1,0)) as
'<60',sum(if(score between 60 and 80,1,0)) as
'60~80',sum(if(score >80,1,0)) as
'>80' from students;
case 1:
select
if(score<60,'<60',if(score between 60
and 80,'60~80','>80')) as rang,count(*) from
students group by
if(score<60,'<60',if(score
between 60 and
80,'60~80','>80'));
case 2:
select
score,count(*) from students group by case
when score<60 then
'<60' when score between 60 and 80
then '60~80' else '>80' end;
为什么能够用三条简单的语句解决的数据库查询我们要使用这么复杂的SQL语句呢?
这个可不是为了装逼;
因为每一条SQL语句事实上都是扫描一次数据库,把这三条SQL语句写成一条符合逻辑的SQL语句,实际上只需要扫描一遍数据库,而这大大节省了系统开销;
因为在处理商用数据库的时候,动辄几千万上亿的数据量,一次全表扫描就可能几个小时,这个时候复杂结构的SQL语句的优势就表现出来了;