MySQL8.0学习笔记（4）—— 算法函数，group by，rollup

内嵌函数

selectMAX(invoice_total) AS highest,	//最大值MIN(invoice_total) AS lowest,	//最小值AVG(invoice_total) AS average,	//平均值SUM(invoice_total) AS total,	//全累加COUNT(invoice_total) AS number_of_invoices,	//计数COUNT(payment_date) AS count_of_payments,COUNT(*) AS total_records	 //记录表中一共多少行数据
from invoices

注意，这些函数会自动省略空值（null），如下图,count_of_payments含有大量的null

• 获取独一无二的计数
  count计数包含重复的值，如果需要独一无二的计数需要使用distinct关键字

selectSUM(invoice_total * 1.1) AS total,	//可以使用表达式COUNT(distinct client_id) AS total_records	
from invoices
where invoice_date > '2019-07-01'

group by

对于sum函数总是计算总的数据，但是要根据某个条件去对齐进行分组计算就需要用到group by。

• 通过分组计算每个客户下半年货物总销售,并以总额数值降序的形式排列

selectclient_id,sum(invoice_total) as total_sales
from invoices
where invoice_date >= '2019-07-01'
group by client_id
order by total_sales desc

group by总是在from where之后，order by之前

• group by后跟随多个参数实现分组

selectp.date,pm.name as payment_method,sum(p.amount) as total_payments
from payments p
join payment_methods pmon p.payment_method = pm.payment_method_id
group by p.date,payment_method	//根据两个条件分组来计算

payments 表：

payment_methods 表

结果显示

HAVING

1. where无法对分组前的数据进行筛选，having可以对分组后的数据进行筛选
2. where的选择条件无论是否在select里面，having的筛选条件一定要在select里面

• 在之前customer表中选出state = ‘VA’,且消费总额大于100的客户。

select c.customer_id,c.first_name,c.last_name,sum(oi.quantity * oi.unit_price) as total_sales
from customers c
join orders ousing(customer_id)
join order_items oiusing(order_id)
where c.state = 'VA'
group byc.customer_id,c.first_name,c.last_name
having total_sales > 100

rollup

用于计算每个分组的累计和以及所有分组的累计和，只用于sum()函数中

use sql_invoicing;
select state,city,sum(invoice_total) as total_sales
from invoices i
join clients c using (client_id)
group by state,city with rollup

每个州每个城市的总额和一共的总额都显示出来了

• 在使用rollup时不能在group by 中使用别名

use sql_invoicing;
select pm.name as payment_method,sum(p.amount) as total
from payments p
join payment_methods  pm on p.payment_method = pm.payment_method_id
group by pm.name with rollup	//不能使用别名