MySQL 執(zhí)行過(guò)程

select xx from xx where??xx?group by xx having?xx?ordey by?xx limit xx

SELECT `name`,COUNT(`name`) AS num FROM student WHERE grade < 60 GROUP BY `name` HAVING num >= 2 ORDER BY num DESC,`name` ASC LIMIT 0,2;

主表結(jié)構(gòu)：

主表結(jié)構(gòu)

過(guò)程：

1、執(zhí)行where 條件語(yǔ)句 生成臨時(shí)表

等價(jià)于執(zhí)行:

? ? ? ? ?select id ,name,subject,grade from student where grade < 60;

生成臨時(shí)表a1的數(shù)據(jù)：

where 條件執(zhí)行結(jié)果

2、對(duì)生成的臨時(shí)表a1進(jìn)行分組操作

1）等價(jià)于：先將臨時(shí)表a1按name進(jìn)行分組查詢，生成多個(gè)臨時(shí)表

select id ,name,subject,grade from a1 where? `name` =’aom‘

臨時(shí)表 b1

select id ,name,subject,grade from a1 where? `name` =’jack‘

臨時(shí)表b2

select id ,name,subject,grade from a1 where? `name` =’susan‘

臨時(shí)表b3

select id ,name,subject,grade from a1 where? `name` =’rajo‘

臨時(shí)表b4

2）在對(duì)每個(gè)臨時(shí)表進(jìn)行字段查詢并合并結(jié)果

select `name`,count(`name`) as num from b1;

select `name`,count(`name`) as num from b2;

select `name`,count(`name`) as num from b3;

select `name`,count(`name`) as num from b4;

臨時(shí)表c1

3、對(duì)生成的臨時(shí)表c1進(jìn)行having條件查詢

因?yàn)榇藭r(shí)已對(duì)表字段進(jìn)行查詢，所以having后條件可以直接使用別名，而where語(yǔ)句不行

SELECT `name`,COUNT(`name`) AS num FROM student WHERE grade < 60 GROUP BY `name` HAVING num >= 2 ;

臨時(shí)表d1

4、對(duì)臨時(shí)表進(jìn)行排序查詢

5、最好執(zhí)行l(wèi)imit

結(jié)果

執(zhí)行順序：
where 查詢條件 ——> group分組?——> 執(zhí)行查詢字段，包含聚合函數(shù)查詢?——>having 條件查詢?——> order排序?——> limit?

參考：https://www.cnblogs.com/fanguangdexiaoyuer/p/10268570.html