本文是《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()函数进行计数,可以使用它来确定表中的函数或者符合特定条件的行的数目,两种使用情况:
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参数不需要指定,是默认行为
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;
分组使用的是两个子句:
分组是使用SELECT子句的GROUP BY子句建立的,看个例子:
SELECT
vend_id
,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id; -- 分组的列
GROUP BY子句使用时候的常见规定:
在WHERE子句中指定过滤的是行而不是分组;实际上WHERE种并没有分组的概念。在SQL使用HAVING来实现过滤分组;
笔记:WHERE过滤行,HAVING过滤分组
SELECT
cust_id
,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2; -- 过滤分组
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 | 要返回的列或者表达式 | 是 |
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。实现上面语句的内联结代码:
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种不同的联结:
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 -- 分组
总结一下联结和使用要点: