mosh数据库——第五章

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

number_of_invoices的值与count_of_payments的值不相等

如果想得到表格中所有记录的条目,不管是不是空值

要用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子句中使用列别名，这里我们必须输入列的实际名称