SQL必知必会总结2-第8到13章

本文是《SQL必知必会》一书的精华总结，帮助读者快速入门SQL或者MySQL，主要内容包含：

• 数据库基础知识
• 库表的相关操作
• 检索数据的方法
• …

本文中介绍的第8到13章，前面的章节请看SQL必知必会总结1-第1到7章

汇总数据

聚集函数

聚集函数指的是对某些行运行的一个函数，并且返回一个值，常用的聚集函数有：

函数	作用
AVG()	返回列的平均值
COUNT()	返回列的函数
MAX()	返回列的最大值
MIN()	返回列的最小值
SUM()	返回某列值之和

1、AVG()函数

SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products;

上面求解的是所有行各自的平均值，也可以指定某个特定的行来求解：

SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

笔记：AVG()函数会忽略掉值NULL的行

2、COUNT()函数

COUNT()函数进行计数，可以使用它来确定表中的函数或者符合特定条件的行的数目，两种使用情况：

• count(*)：不管是空值（NULL）还是非空值，都会统计进去
• count(column)：对特定的列进行计数，会忽略表该列的NULL值

SELECT COUNT(*) AS num_cust
FROM Customers;

num_cust
--------
5

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

num_cust
--------
3

笔记：如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但是如果COUNT()函数使用的是星号，则不会忽略

3、MAX()/MIN()函数

返回指定列中的最大值或者最小值

SELECT MAX(prod_price) AS MAX_price   -- 求最大值
SELECT MAX(prod_price) AS MIN_price   -- 求最小值
FROM Products;

笔记：上面的两个最值函数会自动忽略掉值为NULL的行

4、SUM()函数

返回指定列值的和（总计）

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

SUM()函数也可以用来合计计算值：

SELECT SUM(item_price * quantity) AS total_price   -- 返回所有物品的价钱之和
FROM OrderItems
WHERE order_num = 20005;

笔记：SUM()函数会自动忽略值为NULL的行

聚集不同值

上面的5个聚集函数都可以如下使用：

• 对所有的行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）
• 只包含不同的值，指定DISTINCT参数，表示去重之后再进行计算

笔记：ALL参数不需要指定，是默认行为

SELECT AVG(DISTINCT prod_price) AS avg_price   -- 去重之后再求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

笔记：

1、DISTINCT不能用于COUNT(*)；如果指定列名，则DISTINCT只能用于COUNT()

2、DISTINCT必须使用列名，不能用于计算或者表达式

3、DISTINCT用于MAX()和MIN()意义不大，因为最值不管是否考虑去重，都是一样的

组合聚集函数

在SELECT子句中是可以包含多个聚集函数

SELECT
	AVG(prod_price) AS avg_price   -- 求平均值
	,MAX(prod_price) AS max_price   -- 求最大值
	,MIN(prod_price) AS min_price   -- 求最小值
	,COUNT(*) AS num_items   -- 物品的数目

FROM Products;

分组数据

分组使用的是两个子句：

• GROUP BY()
• HAVING()

创建分组

分组是使用SELECT子句的GROUP BY子句建立的，看个例子：

SELECT
	vend_id
	,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;   -- 分组的列

GROUP BY子句使用时候的常见规定：

1. GROUP BY子句可以包含任意数目的列，可以对分组进行嵌套
2. GROUP BY子句中列出的每一列都是检索列或者有效的表达式（但是不能是聚集函数）
3. 如果在SELECT中使用表达式，则必须在GROUP BY子句中使用相同的表达式，而不是使用别名
4. 除了聚集函数外，SELECT语句中的每列都必须在GROUP BY子句中列出
5. 如果分组中包含具有NULL的行，则NULL将作为一个分组返回；如果列中出现多个NULL，它们将分成一个组
6. GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前
7. GROUP BY子句中可以使用相对位置：GROUP BY 2, 1 表示先根据第二个列分组，再根据第一个列分组

过滤分组

在WHERE子句中指定过滤的是行而不是分组；实际上WHERE种并没有分组的概念。在SQL使用HAVING来实现过滤分组；

笔记：WHERE过滤行，HAVING过滤分组

SELECT
	cust_id
	,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;    -- 过滤分组

WHERE和HAVING的区别：

• WHERE在数据过滤前分组，排除的行不在分组统计中
• HAVING在数据分组后进行过滤

SELECT
	vend_id
	,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4   -- 分组前先执行，找出符合条件的数据
GROUP BY vend_id
HAVING COUNT(*) >= 2;  -- 分组后再执行，找出数目大于2的数据

分组和排序

ORDER BY 和GROUP BY的差异：

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（非选择的列也可以使用）	只可能使用选择列或者表达式列，而且必须使用每个选择列表达式
不一定需要	如果和聚集函数一起使用列，则必须使用

SELECT
	order_num
	,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;   -- 先分组再过滤，最后排序输出

SELECT子句顺序

在这里总结一下SELECT子句的相关顺序：

子句	说明	是否必须使用
SELECT	要返回的列或者表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按照组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

使用子查询

任何SELECT语句都是查询，SQL还允许在查询中嵌套查询。

SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
FROM Orders
WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

笔记：子查询总是从内向外处理

SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                                    FROM OrderItems
                                    WHERE prod_id = 'RGAN01'));

作为计算字段使用子查询

使用子查询的另一个方法是创建计算字段

SELECT
	cust_name
	,cust_state
	,(SELECT COUNT(*)   -- 将子查询作为一个计算字段输出：统计每个cust_id的数量
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders   -- Orders.cust_id = Customers.cust_id 使用完全限定列名来避免歧义
FROM Customers
ORDER BY cust_name;

联结表

SQL最强大的功能就是数据查询的过程中使用联结表（join）。

创建联结

通过指定要联结的表和它们的联结方式即可创建联结。

SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;    -- 指定联结条件

如果上面的代码中没有WHERE子句来指定联结条件，则返回的是笛卡尔积，返回出来数的行就是第一个表中的行乘以第二个表中的行。

笔记：返回笛卡尔积的联结，也称做叉联结cross join

内联结inner join

使用最广泛的联结是等值联结，也称之为内联结inner join。实现上面语句的内联结代码：

SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors
INNER JOIN Products   -- 内联结
ON Vendors.vend_id = Products.vend_id;    -- 指定联结条件

联结多个表

SELECT
	vend_name,
	prod_name,
	prod_price
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id   -- 多个表的联结
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

我们通过联结方式来实现子查询的结果：

-- 子查询
SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                                    FROM OrderItems
                                    WHERE prod_id = 'RGAN01'));

-- 内联结
SELECT
	cust_name,
	cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id   -- 多个表联结查询
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01'

创建高级联结

使用表别名

在SQL语句中可以给表取别名：

SELECT
	cust_name,
	cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI   -- 取别名，看上去更简洁
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'

使用不同类型的联结

介绍3种不同的联结：

• 自联结self join
• 自然联结natural join
• 外联结outer join

1、自联结self join

-- 子查询
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

-- 内联结
SELECT c1.cust_id, c2.cust_name, c1.cust_contact
FROM Customers AS c1,  Customers AS c2   -- 相同的表使用两次
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

上面使用了Customers表两次，为了避免歧义，必须使用不同的别名加以区分。

2、自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。自然联结排除多次出现，是每一列只返回一次。

SELECT
	C.*
	,O.order_num
	,O.order_date
	,OI.prod_id
	,OI.quantity
	,OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'

3、外联结

有时候我们需要将一个表中的行和另一个表中行相关联，但是有时候也需要包含那些没有关联行的行记录，比如下面的场景中：

• 对每个顾客下的订单数进行统计，包含那些至今尚未下单的顾客
• 列出所有产品以及订购数量，包含没有人订购的产品
• 计算平均销售规模，包含那些至今尚未下订单的顾客

当联结中包含了那些在相关表中没有关联行的行，这种联结称之为外联结。比如：检索出包括没有订单顾客在内的所有顾客。

SELECT
	C.cust_id
	,O.order_num
FROM Customers AS C
LEFT OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id

上面的代码中表示包含左边所有行的记录；如果是右边，使用RIGHT OUTER。因此外联结实际上有两种形式，它们之间可以互换

• 左外联结
• 右外联结

还有一种比较特殊的外联结，叫做全外联结full outer join，它检索的是两个表中的所有行并关联那些可以关联的行。全外联结包含两个表的不关联的行

SELECT
	C.cust_id
	,O.order_num
FROM Customers AS C
FULL OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id

带有聚集函数的联结

检索所有顾客及每个顾客所有的订单数：

SELECT
	C.cust_id
	,COUNT(O.order_num) AS num_ord   -- 使用聚集函数统计订单数
FROM Customers AS C
INNER JOIN Orders
ON C.cust_id = O.cust_id   -- 关联两个表
GROUP BY Customers.cust_id   -- 分组

使用联结和联结条件

总结一下联结和使用要点：

1. 注意使用联结的类型：一般是使用内联结，有时候外联结有有效
2. 要保证使用正确的联结条件，否则会返回不正确的数据
3. 记得提供联结条件，否则返回的是笛卡尔积
4. 一个联结中可以包含多个表，甚至可以对不同的表使用不同的联结类型。要注意测试每个联结