 # 1. 數據通常以資料庫用戶所需的最低層級的粒度存儲 # 2. 分組 ## 2.1. 隱式分組 ### 2.1. ...
1. 數據通常以資料庫用戶所需的最低層級的粒度存儲
2. 分組
2.1. 隱式分組
2.1.1. mysql
-＞ SELECT MAX(amount) max_amt,
-＞ MIN(amount) min_amt,
-＞ AVG(amount) avg_amt,
-＞ SUM(amount) tot_amt,
-＞ COUNT(*) num_payments
-＞ FROM payment;
2.1.1.1. 查詢返回的每個值都是由聚合函數生成的
2.1.1.2. 沒有使用group by子句
2.1.1.3. 只有一個隱式分組
2.1.1.3.1. payment數據表中的所有行
2.2. 顯式分組
2.2.1. mysql
-＞ SELECT customer_id,
-＞ MAX(amount) max_amt,
-＞ MIN(amount) min_amt,
-＞ AVG(amount) avg_amt,
-＞ SUM(amount) tot_amt,
-＞ COUNT(*) num_payments
-＞ FROM payment
-＞ GROUP BY customer_id;
2.2.1.1. 添加一個group by子句來指定聚合函數應該應用於哪個分組
2.3. 單列分組
2.3.1. 最簡單，也是最常用的分組類型
2.3.2. mysql
-＞ SELECT actor_id, count(*)
-＞ FROM film_actor
-＞ GROUP BY actor_id;
2.4. 多列分組
2.4.1. 需要跨越多列生成分組
2.4.2. mysql
-＞ SELECT fa.actor_id, f.rating, count(*)
-＞ FROM film_actor fa
-＞ INNER JOIN film f
-＞ ON fa.film_id = f.film_id
-＞ GROUP BY fa.actor_id, f.rating
-＞ ORDER BY 1,2;
2.5. 通過表達式分組
2.5.1. 根據表達式產生的值構建分組
2.5.2. mysql
-＞ SELECT extract(YEAR FROM rental_date) year,
-＞ COUNT(*) how_many
-＞ FROM rental
-＞ GROUP BY extract(YEAR FROM rental_date);
2.6. 分組過濾條件
2.6.1. 由於group by子句是在where子句被評估之後運行的，因此無法為此對where子句增加過濾條件
2.6.2. 無法在where子句中引用聚合函數count(*)
2.6.2.1. 因為在評估where子句時，分組尚未生成，因而必須將分組過濾條件放入having子句
2.6.3. 向包含group by子句的查詢中添加過濾條件時，仔細考慮是過濾原始數據（將過濾條件放入where子句），還是過濾分組後的數據（將過濾條件放入having子句）
3. 聚合函數
3.1. 對分組中的所有行執行特定的操作
3.2. max()
3.2.1. 返回集合中的最大值
3.3. min()
3.3.1. 返回集合中的最小值
3.4. avg()
3.4.1. 返回集合中的平均值
3.5. sum()
3.5.1. 返回集合中所有值之和
3.6. count()
3.6.1. 返回集合中所有值的個數
4. 統計不同的值
4.1. mysql
-＞ SELECT COUNT(customer_id) num_rows,
-＞ COUNT(DISTINCT customer_id) num_customers
-＞ FROM payment;
4.2. 通過指定distinct，count()函數檢查分組中每個成員的列值，以便查找和刪除重覆項，而不是簡單地計算分組中值的數量
4.3. mysql
-＞ SELECT COUNT(*) num_rows,
-＞ COUNT(val) num_vals,
-＞ SUM(val) total,
-＞ MAX(val) max_val,
-＞ AVG(val) avg_val
-＞ FROM number_tbl;
4.4. count(*)統計行數
4.5. count(val)統計val列包含多少個值並且忽略所有遇到的null值
5. 使用表達式
5.1. mysql
-＞ SELECT MAX(datediff(return_date,rental_date))
-＞ FROM rental;
5.2. 除了使用列作為聚合函數的參數，也可以使用表達式
6. 生成彙總
6.1. 假設在計算每位演員/評級組合的總計數的同時，還想知道不同演員參演的電影總數，這時可以運行一個額外的查詢併合並結果
6.2. with rollup選項來讓資料庫伺服器完成這些工作
6.3. mysql
-＞ SELECT fa.actor_id, f.rating, count(*)
-＞ FROM film_actor fa
-＞ INNER JOIN film f
-＞ ON fa.film_id = f.film_id
-＞ GROUP BY fa.actor_id, f.rating WITH ROLLUP
-＞ ORDER BY 1,2;
6.4. Oracle Database
6.4.1. GROUP BY ROLLUP(fa.actor_id, f.rating)
6.4.2. 可以在group_by子句中對部分列彙總
6.4.3. 如果按照列a、b、c進行分組，可以指示伺服器通過下列語句僅對列b和c執行彙總
6.4.3.1. GROUP BY a, ROLLUP(b, c)
6.5. with cube選項
6.5.1. 為分組列的所有可能的組合生成彙總行
6.5.2. MySQL 8.0版並未提供
6.5.3. SQL Server和Oracle Database中可以使
7. 子查詢
7.1. 子查詢總是被包圍在括弧中，通常先於包含語句執行
7.2. 子查詢像是一個具有語句作用域的臨時數據表（這意味著伺服器在執行SQL語句後會清空分配給子查詢結果的記憶體）
7.3. 如果不清楚子查詢究竟做了什麼，可以單獨運行子查詢（不加括弧）並查看返回結果
7.4. 返回的結果集
7.4.1. 單行單列
7.4.2. 多行單列
7.4.2.1. in和not in運算符
7.4.2.1.1. 雖然不能把單個值與一組值進行相等比較，但是可以檢查這個值能否包含在一組值中
7.4.2.1.2. mysql
-＞ SELECT country_id
-＞ FROM country
-＞ WHERE country IN ('Canada','Mexico');
7.4.2.1.3. mysql
-＞ SELECT city_id, city
-＞ FROM city
-＞ WHERE country_id IN
-＞ (SELECT country_id
-＞ FROM country
-＞ WHERE country IN ('Canada','Mexico'));
7.4.2.1.4. sql
SELECT first_name, last_name
FROM customer
WHERE customer_id NOT IN
(SELECT customer_id
FROM payment
WHERE amount = 0)
7.4.2.1.4.1. not in的版本更易於理解
7.4.2.2. all運算符
7.4.2.2.1. 將某個值與集合中的所有值進行比較
7.4.2.2.2. mysql
-＞ SELECT first_name, last_name
-＞ FROM customer
-＞ WHERE customer_id ＜＞ ALL
-＞ (SELECT customer_id
-＞ FROM payment
-＞ WHERE amount = 0);
7.4.2.3. any運算符
7.4.2.3.1. 允許將單個值與一組值中的各個值進行比較
7.4.2.3.2. 只要有一次比較成立，使用any運算符的條件即為真
7.4.2.3.3. any與in等效
7.4.2.4. 使用not in或＜＞運算符比較一個值和一組值時，必須確保這組值中不包含null值，這是因為伺服器會將表達式左側的值與組中的各個值進行比較，任何值與null作相等比較時都會產生unknown
7.4.3. 多行多列
7.4.3.1. mysql
-＞ SELECT fa.actor_id, fa.film_id
-＞ FROM film_actor fa
-＞ WHERE fa.actor_id IN
-＞ (SELECT actor_id FROM actor WHERE last_name = 'MONROE')
-＞ AND fa.film_id IN
-＞ (SELECT film_id FROM film WHERE rating = 'PG');
7.4.3.2. 可以將兩個單列子查詢合併成一個多列子查詢
7.4.3.3. mysql
-＞ SELECT actor_id, film_id
-＞ FROM film_actor
-＞ WHERE (actor_id, film_id) IN
-＞ (SELECT a.actor_id, f.film_id
-＞ FROM actor a
-＞ CROSS JOIN film f
-＞ WHERE a.last_name = 'MONROE'
-＞ AND f.rating = 'PG');
