一、分组函数常用到以下五个函(MIN、MAX、SUM、AVG、COUNT)
SELECT COUNT(列名) FROM 表名
1、MIN和MAX函数主要是返回每组的最小值和最大值,MIN和MAX可以用于任何数据类型。
2、SUM和AVG函数分别返回每组的总和及平均值,SUM和AVG函数都是只能够对数值类型的列或表达式操作。
3、COUNT函数的主要功能是返回满足条件的每组记录条数,COUNT(*):返回表中满足条件的行记录数。
注:除了COUNT(*)之外,其它所有分组函数都会忽略列中的空值,然后再进行计算。
二、去重
组函数中DISTINCT会消除重复记录后再使用组函数
SELECT COUNT(DISTINCT 列名1) FROM 表;
三、IFNULL函数用法
在分组函数中使用IFNULL函数可以使分组函数强制包含含有空值的记录
SELECT AVG(IFNULL(列名,0)) FROM 表名;
四、创建数据组
1、按单个列分组:SELECT 列名, 组函数 FROM 表名 GROUP BY 列名;
注:一旦group by分组了,select后面能查寻的就只有分组条件和组函数计算出来的结果。
2、按多个列分组:SELECT 列名1, 列名2, 组函数 FROM 表名 GROUP BY 列名1, 列名2;
理解:一个班分男女,再分身高。
五、排除组结果
使用组函数的非法的查询,不能在 WHERE子句中限制组,可以通过 HAVING 子句限制组。
SELECT 分组的列, 分组函数 FROM 表名 GROUP BY 分组的列 HAVING 筛选条件;
六、SELECT语句执行过程
1、通过FROM子句中找到需要查询的表;
2、通过WHERE子句进行非分组函数筛选判断;
3、通过GROUP BY子句完成分组操作;
4、通过HAVING子句完成组函数筛选判断;
5、通过SELECT子句选择显示的列或表达式及组函数;
6、通过ORDER BY子句进行排序操作。
七、子句查询
括号内的查询叫做子查询,也叫内部查询,先于主查询执行。
1、子查询的结果被主查询使用
1)子句查询可以嵌入where子句;
SELECT 列名1,列名2 FROM 表名 WHERE 列名_1 IN (SELECT 列名_1 FROM 表名 WHERE 条件);
2)子句查询可以嵌入having子句;
SELECT 列名1 FROM 表名 GROUP BY 列名1 having 列名1= (SELECT 列名1 FROM 表名 WHERE 条件);
3)子句查询可以嵌入from子句,子查询必须有别名;
SELECT 列名1,列名2 FROM (SELECT * FROM 表名1,表名2 WHERE 表名1.列名=表名2.列名)a WHERE 条件;
4)子句查询可以当列使用。
SELECT 列名1,(SELECT max(列名) FROM 表名2 WHERE 列名_1=表名1.列名_1) FROM student 表名1
理解:学生信息的姓名,与对应姓名的max(列名)。
注:在此情况下,返回的子查询语句的结果不能为多条。
2、单行子查询语句
子查询只返回一行一列,使用单行运算符:= 、 >、 >=、 <、 <= 、 <>
3、多行子查询语句
子查询返回记录的条数 可以是一条或多条,和多行子查询进行比较时,需要使用多行操作符,多行操作符包括:IN、ANY、ALL。
注:ANY:表示和子查询的任意一行结果进行比较,有一个满足条件即可
1)< ANY:表示小于子查询结果集中的任意一个,即小于最大值就可以。
2)> ANY:表示大于子查询结果集中的任意一个,即大于最小值就可以。
3)= ANY:表示等于子查询结果中的任意一个,即等于谁都可以,相当于IN。
ALL:表示和子查询的所有行结果进行比较,每一行必须都满足条件。
1)< ALL:表示小于子查询结果集中的所有行,即小于最小值。
2)>ALL:表示大于子查询结果集中的所有行,即大于最大值。
3)= ALL :表示等于子查询结果集中的所有行,即等于所有值,通常无意义。
4、子查询中的空值
因为子查询的结果中有一条空值,这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分,就不能使用NOT IN 运算符。
5、EXISTS(判断有没有,一般配合多行子查询语句使用)
判断查询子句有记录:SELECT * FROM 表名 WHERE EXISTS (查询子句)
与此等效:SELECT * FROM 表名 WHERE (查询子句)>0
判断查询子句无记录:SELECT * FROM 表名 WHERE NOT EXISTS (查询子句)