Mosh--Mysql 学习笔记（五）数据的处理

课程是国外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