课程是国外Mosh Hamedani 的付费Mysql课程。 该课程干货满满,没有废话。并且授课方式目的性强,适合各个基础的学生。 课程在B站有搬运,大家动动手就可以找到~~ 希望能帮助到各位同学
- 本章讲解 对数据的处理,内容包括常见函数,group by,having,rollup 等关键字
第一节 aggregate function 统计函数
- max()
- min()
- avg()
- sum()
- count() :只计算非空值 要想包含null 使用count(*)
select
max(invoice_total),
min(invoice_total)
from invoices
章节练习
select 'First half of 2019' as date_range,
sum(invoice_total) as total_sales,
sum(payment_total) as total_payments,
sum(invoice_total - payment_total) as what_we_expect
from invoices
where invoice_date between '2019-01-01' and '2019-6-30'
union
select 'second half of 2019' as date_range,
sum(invoice_total) as total_sales,
sum(payment_total) as total_payments,
sum(invoice_total - payment_total) as what_we_expect
from invoices
where invoice_date between '2019-07-01' and '2019-12-30'
第二节 group by
- group by 是对结果集的重筛选,再处理,所以用的时候需要注意这一点。
select *
from invoices
group by client_id
order by invoice_id
第三节 having
- having 用于对结果集的再处理,可以用于group by 处理之后的结果集,即 having 用于 group by 之后。
这里展示一个对前面一节的结果集再处理,要求只选择invoice_total大于 120的用户
select *
from invoices
group by client_id
having invoice_total > 120
order by invoice_id
章节练习
–获取用户
–位于 弗吉尼亚
–消费超过100
第四节 with roll up
意思就是汇总
select
client_id,
sum(invoice_total) as total_sales
from invoices
group by client_id with rollup