【笔记】3小时学会MySQL 初学入门完整教程(第五章)

1-聚合函数 / Aggregate Functions

SELECT MAX(invoice_total/3) AS highest,
       MIN(invoice_total-2) AS lowest,
       AVG(invoice_total+30) AS average,
	   SUM(invoice_total*0.9) AS total,
       COUNT(invoice_total) AS number_of_invoices,
       COUNT(payment_date) AS count_of_payments,
       COUNT(*) AS total_records
FROM invoices
WHERE payment_date>'2019-03-01'

1.COUNT只数非空数据

2.四则运算的函数括号里都可以是表达式

3.WHERE 可以加在最后面筛选

4.DISTINCT 重复项只记一次

COUNT(DISTINCT client_id) AS total_records 

2.GROUP BY 子句 / The GROUP BY Clause

GROUP BY 后的量一定是SELECT里的

SELECT client_id,
	   SUM(invoice_total) AS total_sales
FROM invoices 
WHERE invoice_date >='2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC

WHERE要在GROUP BY前， ORDER BY要在GROUP BY后

SELECT state,
       city,
       client_id,
	   SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id) -- 添加相应的信息，非筛选条件
WHERE invoice_date >='2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC

作业: 拆分任务，先按时间GROUP BY

SELECT date,
       SUM(amount)
FROM payments 
GROUP BY date
ORDER BY date

再加上payment_methods

SELECT date,
       pm.name,
       SUM(amount) AS total_amount
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子句 / The HAVING Clause

若需要用GROUP BY出的结果进行筛选，由于WHERE必须放在GROUP BY前面，用WHERE不得行

SELECT client_id,
	   SUM(invoice_total) AS total_sales
FROM invoices 
GROUP BY client_id
HAVING total_sales>500
ORDER BY total_sales DESC

多加一个筛选条件

SELECT client_id,
	   SUM(invoice_total) AS total_sales,
	  COUNT(*) AS number_of_invoices
FROM invoices 
GROUP BY client_id
HAVING total_sales>500 AND number_of_invoices>5
ORDER BY total_sales DESC

注意:WHERE 和 HAVING的不同

1. WHERE在GROUP BY之前， HAVING在GROUP BY之后

2. HAVING只能筛选在SELECT的量，WHERE则可以筛选选定表中所有的量

作业

SELECT c.customer_id,
       SUM(oi.quantity*oi.unit_price) AS total_amount
FROM customers c
JOIN orders o
     USING (customer_id)
JOIN order_items oi
     USING (order_id)
WHERE state='VA'
GROUP BY c.customer_id
HAVING total_amount>100

注意: 只要前面的JOIN中含后面JOIN需连接的量，则可以这样:

FROM customers c
JOIN orders o
     USING (customer_id)
JOIN order_items oi
     USING (order_id)

 4.ROLLUP运算符 / The ROLLUP Operator

SELECT state,
	   city,
	   SUM(invoice_total) AS total_sales
FROM invoices 
JOIN clients c USING (client_id)
GROUP BY state,
	    city WITH ROLLUP