汇总数据
1.聚集函数:运行在行组上,计算和返回单个值的函数
Avg()函数:
select avg(prod_price) AS avg_price
from products
where vend_id = 1003;
Avg()函数只用于单个列,要获得多个列的平均值,必须使用多个Avg()函数
Avg()函数忽略值为NULL的行
Count函数:
select count(cust_email) AS num_cust
from customers;
如果指定列名,则指定列为NULL的行被忽略,如果使用Count(*),则不忽略
Max(),Min()函数:
select max(prod_price) AS MAX_price
from products;
Max,Min忽略NULL行
Sum()函数:
select sum(item_price*quantity) AS total_price
from orderitems
where order_num = 20005;
Sum函数忽略NULL行
2.聚集不同的值
对于以上五个函数,都可以指定ALL值或不给参数(默认ALL),为了只包含不同的值,指定Distinct参数
select avg(distinct prod_price) AS vg_price
from products
where vend_id = 1003;
返回不同价格的平均值,Distinct不能用于Count(*)
3.组合聚集函数
select count(*) AS num_items,
min(prod_price) AS price_min,
max(prod_price) AS price_max,
avg(prod_price) AS price_avg
from products;
分组数据
1.创建分组:使用Group by子句
select vend_id,count(*) AS num_prods
from products
group by vend_id;
统计不同vend_id的个数
Group by子句必须出现在Where子句之后,Order by子句之前
2.过滤分组:使用Having子句
select cust_id,count(*) AS orders
from orders
group by cust_id
having count(*) >= 2;
所有地Where子句都可以用Having来替代,Where过滤行,Having过滤分组
3.Select语句中子句的顺序:
select
from
where
group by
having
order by
limit