MySql学习记录3-聚合函数

聚合函数：

Count(*)行数量—不包含空行 null 
avg平均。
Sum求和。
Max最大，
Min最小。
Distinct－去除相同的信息。
Exists存在判断

count(*)：输出(非NULL)的行数

SELECT COUNT(*) FROM stud; //输出行数，一般要用别名
SELECT COUNT(*) AS rows FROM stud; 
SELECT COUNT(1) AS ROWS FROM stud;//功能同上，据说效率更高
ELECT COUNT(saddress) AS sumNum FROM stud; //统计该字段值非NULL的数量

AVG(age):输出age的平均值

SELECT AVG(age) AS averageAge FROM stud;
SELECT AVG(age) AS averageAge FROM stud WHERE age<20;

SUM(age)：输出age的和

SELECT SUM(age) AS sumAge FROM stud

MAX(age):输出age的最大值

SELECT MAX(age) AS maxAge FROM stud;

MIN(age)：输出age的最小值

SELECT MIN(age) AS minAge FROM stud;

DISTINCT age:把各种年龄段的值输出--相同的合并

SELECT DISTINCT age FROM stud;

exists()：由括号中的子查询返回结果集决定该方法的调用结果，如果子查询结果集为空则函数返回false,否则返回true

SELECT * FROM stud WHERE EXISTS( SELECT * FROM stud WHERE saddress IS NULL); //只要子查询结果非空则所有信息都输出，否则一条不输出
select * from stud where saddress is NULL;//与上一句效果一致

//排序ORDER BY  order by

SELECT * FROM stud ORDER BY age; //默认升序
SELECT * FROM stud ORDER BY age ASC;//升序
SELECT * FROM stud ORDER BY age DESC;//降序