SQL学习笔记-数据汇总(avg、max、min、count)

计算平均值AVG

SELECT AVG(price) AS '平均价' from goods;

设置计算结果的精度 

SELECT round (AVG(price),2) AS '平均价' from goods;

如果列的数据类型不是数字类型，还需要使用cast进行类型转换

SELECT CAST(round (AVG(price),2) AS REAL) AS '平均价' from goods;

 ACG与算术运算符结合使用

SELECT AVG(price*2) AS '平均价' from goods;

 与where语句结合使用

SELECT AVG(price) AS '平均价' from goods where price>70;

SELECT name AS '商品名' from goods where(price> (SELECT AVG(price) from goods));

count()计算某列数据的行数

统计所有列的行数:会统计空值

select count(*) AS '数据的行数' from goods;

统计某一列的行数：会忽略空值

select count(sales) AS '销量的个数' from goods;

count()与where的混用

select count(price) AS '售价高于70的个数' from goods where price>70;

计算最大值和最小值 

会忽略null值

select MAX(price) AS '售价最高',min(price) AS '售价最低' from goods;

参数可以是字符串和时间 

select MAX(time1) AS '最新上架',min(time1) AS '最早上架' from goods;

select MAX(name) AS '文本最大',min(name) AS '文本最小' from goods;

 与where子句的混用

select MAX(price) AS '售价最高',min(price) AS '售价最低' from goods where price>70;

与avg函数的混合使用 

select AVG(price) AS 'q' FROM goods
 WHERE price  not IN
 (
 (SELECT MAX(price) FROM goods),
 (SELECT MIN(price) FROM goods)
 );        

求和 

select  SUM(sales) AS '销量的和' from goods;

结合算术运算符使用

select  SUM(price*sales) AS '公司A产品的销售额' from goods where company='公司A';

结合where语句混合使用 

select  SUM(sales) AS '销售的和' from goods where price>70;