1.聚合函数
函数是一段可用代码,MY SQL自带一堆内置函数,其中一些叫聚合函数,因为它们取系列值并聚合它们 ,导出一个单一值
这些函数都要使用括号来调用或者执行
不仅可以应用于数值列也可以应用于日期和字符串
聚合函数只运行非空值,如果列中有空值他不会被算在函数里
SELECT
MAX(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-- 计算 payment_date 字段的非空值数量
FROM invoices
如果想得到表格中所有记录的条目,不管是不是空值
要用COUNT (*) AS total_records
COUNT(*) AS total_records
括号里面大多时候是列名,但是我们也可以写表达式
加上筛选器
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total * 1.1) AS total,
COUNT(*) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01' -- 添加筛选器
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-06-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-31'
UNION
SELECT
'Total' 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-12-31'
2.GROUP BY子句
GROUP BY表示以.....分组,每一组返回一个结果
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices -- 之后可以添加WHERE子句
GROUP BY client id
ORDER BY total_sales DESC
需要添加条件WHERE的话,加在GROUP BY之前
顺序:SELECT.........FROM.........WHERE........GROUP BY.........ORDER BY........
在GROUP BY字句中使用多列,确保所有非聚合列都包含在 GROUP BY
子句中,否则会报错
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client id)
GROUP BY state,city
SELECT
date,
pm. name AS payment_method,
SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method -- 按照日期和支付方式分类
ORDER BY date
3.HAVING子句
在分组的中间不能用WHERE子句,因为还没进行分组(GROUP BY在最后),如果运行,会返回:未知XX列
依托WHERE子句,我们可以在分组行之前筛选数据
而HAVING子句,可以让我们在分组行之后筛选数据
SELECT
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoices
FROM invoices -- where不可以使用,因为total_sales里的内容在这里还没有生成
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_sales > 100
4.ROLLUP运算符
在MySQL我们有一个强大的运算符用来汇总数据 叫 with rollup
- 注意这行的client id是空值,因为把这些值加起来没有意义
- 所以ROLLUP运算符只能应用于聚合值的列
多个数据进行分组时:(确保所有非聚合列都包含在 GROUP BY
子句中)
可以看到每个组及整个结果集的汇总值(对每个州的全部城市以及全部州的账单分别进行汇总)
rollup运算符很有用,在现实世界中应用广泛,然而这仅仅在MySql里有,它不是一个标准的SQL语言,所以你无法在SQL server或者oracle里执行这个查询,但肯定这些数据库引擎一定有相似的汇总数据功能的运算符
SELECT
pm.name AS payment_method,
SUM(P.amount) AS total
FROM payments p
JOIN payment_methods pm
ON pm.payment_method_id = p.payment_method
GROUP BY pm.name WITH ROLLUP -- 这里不能使用payment_method
使用rollup运算符的时候,不能在group by子句中使用列别名,这里我们必须输入列的实际名称