[关闭]
@FunC 2018-05-15T19:09:13.000000Z 字数 4552 阅读 1777

SQL必知必会 CH08~14

学习笔记

CH08 函数

函数为数据的转换和处理提供了方便，但同时也具有可移植性差的特点（即在不同的DBMS中的支持度，函数名可能不一样）
如果使用函数，应该保证做好代码注释。

函数的类型

大多数SQL实现支持下面四类函数：
1. 文本函数：用于处理字符串
2. 数值函数：对数值数据进行算术运算
3. 日期和时间函数：用于处理日期和时间值
4. 系统函数：返回DBMS相关的信息

文本处理函数

下面是一些常用的文本处理函数：

其中SOUNDEX函数是将文本串转换为描述其语音表示的字母数字模式的算法，能对字符串进行发音比较（但不支持中文）。

数值处理函数

数值函数在主要的DBMS中实现都比较一致：

日期和时间函数

一方面，在数据库中，日期和时间采用相应的数据类型储存，DBMS能快速和有效地排序和过滤，并且节省物理存储空间。
另一方面，应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。可惜的是，它们很不一致，可以移植性很差。

例子

  1. -- MYSQL使用YEAR() 函数提取日期
  2. SELECT order_num
  3. FROM Orders
  4. WHERE YEAR(order_date) = 2012;

当然，DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等函数。

CH09 汇总数据

聚集函数

有时候我们需要将数据汇总后的结果，而不需要实际表数据（如确定表中行数、表中某些行的和、列中的最大值、最小值和平均值等）
为了方便这种类型的检索，SQL给出了5个聚集函数：

聚集函数：
对某些行运行的函数，计算并返回一个值。

AVG()函数

AVG()对表中行数进行计数并计算其列值之和，求的该列的平均值。

注意，AVG()只能用于单个列，想获得多个列的平均值必须使用多个AVG()函数。

关于字符串：如果对字符串进行处理，则只计算字符串中的数字

关于NULL值：AVG()函数忽略值为NULL的行

COUNT()函数

COUNT()函数进行计数。有两种使用方式：
1. 使用COUNT(*)对表中行的数目进行计数（包括NULL值）
2. 使用COUNT(column)对特定列中具有值的行进行计数（忽略NULL值）

MAX()函数与MIN()函数

MAX() 返回指定列中的最大值。用于文本数据时，返回按该列排序后的最后一行
MIN() 返回指定列中的最小值。用于文本数据时，返回按该列排序后的最前面一行

关于NULL值：均忽略NULL值

SUM()函数

SUM()函数用于返回指定列值的和。

SUM()也可以用于合计计算值，如下所示：

  1. SELECT SUM(item_price*quantity) AS total_price
  2. FROM OrderItems
  3. WHERE order_num = 20095;

使用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

关于NULL值：忽略值为NULL的行

聚集不同值

对于以上5个聚集函数，都能有以下用法：
* 默认行为：对所有行执行计算，即指定ALL参数或不指定参数
* 只包含不同的值，指定DISTINCT参数

例子

  1. SELECT AVG(DISTINCT prod_price) AS avg_price
  2. FROM Products
  3. WHERE vend_id = 'DLL01';

注意：DISTINCT不能用于COUNT(*)

组合聚集函数

SELECT语句可以包含多个聚集函数。

> 关于别名：注意，不应该使用功能表中实际的列名作为别名。

CH10 分组数据

SQL除了能汇总数据，还能将汇总的数据根据一定的逻辑进行分组，这主要通过GROUP BY字句和HAVING字句实现

例子

  1. SELECT vend_id, COUNT(*) AS num_prods
  2. FROM Products
  3. GROUP BY vend_id;

GROUP BY 子句的规定

过滤分组

对于行的过滤，我们可以使用WHERE。但对于分组的过滤，我们需要使用HAVING（HAVING支持所有WHERE操作符）

HAVING和WHERE的差别：
WHERE在数据分组前进行过滤，HAVING在分组后进行过滤（所以WHERE排除掉的行将不被包含在分组中）

例子

  1. SELECT vend_id, COUNT(*) AS num_prods
  2. FROM Products
  3. WHERE prod_price >= 4
  4. GROUP BY vend_id
  5. HAVING COUNT(*) >= 2;

分组与排序

注意，分组的输出顺序是不能保证的，除非你是用ORDER BY来指定。

CH11 使用子查询

子查询（subquery）即嵌套在其他查询中的查询。常见的使用场景是将某个查询的结果作为另一个查询的检索范围：

示例

数据库中有多张关系表，例如有记录订单的Orders，记录订单具体内容的OrderItems，以及记录顾客信息的Customers。

场景：
需要检索购买了RGAN01的所有顾客的信息

思路：
检索包含RGAN01的订单 -> 检索上述订单中的所有顾客ID -> 根据得到的ID返回所有的顾客信息

于是我们可以利用子查询：

  1. - 适当的缩进便于阅读
  2. SELECT cust_name, cust_contact
  3. FROM Customers
  4. WHERE cust_id IN (SELECT cust_id
  5.                   FROM Order
  6.                   WHERE order_num IN (SELECT order_num
  7.                                       FROM OrderItems
  8.                                       WHERE prod_id = 'RGAN01'));

作为计算字段使用子查询

场景：
检索Customers表中每个顾客的订单总数

思路：
相当于在Customers表中临时加一列，根据每一行的ID，去订单表中COUNT(*)来得到该列数据

  1. SELECT cust_name, 
  2.        cust_state,
  3.        (SELECT COUNT(*) 
  4.         FROM Orders 
  5.         WHERE Orders.cust_id = Customers.cust_id) AS orders
  6. FROM Customers 
  7. ORDER BY cust_name;

因为orders列的数据需要每一行分别生成，所以子查询语句执行了5次（因为结果是5个顾客）。（这不是最有效的方法，用JOIN更佳）

> 因为出现了冲突列名（cust_id），为了避免歧义，这里使用了完全限定列名

CH12 联结表

在数据查询的执行中联结（JOIN）表是SQL最强大的功能之一。

为什么使用联结

将数据分解为多个表能更有效地进行存储和管理，并且伸缩性更好。通过联结，能在一条SELECT语句中关联表

例子：

  1. SELECT vend_name, prod_name, prod_price
  2. FROM Vendors, Products                      - 多个表
  3. WHERE Vendors.vend_id = Products.vend_id;
  5. - 与上面的sql等价
  6. SELECT vend_name, prod_name, prod_price
  7. FROM Vendors INNER JOIN Products
  8.  ON Vendors.vend_id = Products.vend_id;

这种基于两个表之间的相等测试的联结称为内联结。

将上一章的子查询改为联结：

  1. SELECT cust_name, cust_contact
  2. FROM Customers, Orders, OrderItems
  3. WHERE Customers.cust_id = Orders.cust_id
  4.  AND OrderItems.order_num = Orders.order_num
  5.  AND prod_id = 'RGAN01';

> 注意，不要联结不必要的表。联结的表越多，性能下降得越厉害。

CH13 创建高级联结

自联结

场景：
要检索出与Jim Jones同一公司的所有顾客

思路：
1. 先检索出Jim Jones的公司
2. 然后检索同一公司顾客

因为两步中查询的是同一张表，因此可以使用联结查询（配合别名避免歧义）

  1. SELECT c1.cust_id, c1.cust_name, c1.cust_contact
  2. FROM Customers AS c1, Customers AS c2
  3. WHERE c1.cust_name = c2.cust_name
  4.  AND c2.cust_contact = 'Jim Jones';

外联结

在联结时，有时候需要包含没有关联行的那些行（例如注册了还没有过购买记录的顾客），这是可以使用外联结来实现：

  1. SELECT Customers.cust_id, Orders.order_num
  2. FROM Customers LEFT OUTER JOIN Orders
  3.  ON Customers.cust_id = Orders.cust_id;

其中联结关键字中的LEFT表示列出左边的表的所有行，也可以使用RIGHT OUTER JOIN。

全外联结

即包含两个表的不关联行（类似于同时使用LEFT和RIGHT），使用 FULL OUTER JOIN 关键字

使用聚集函数的联结

对于联结查询的结果，我们也可以使用聚集函数来汇总数据。例如我们想检索每个顾客所下的订单数：

  1. SELECT Customers.cust_id,
  2.        COUNT(Orders.order_num) AS num_ord
  3. FROM Customers INNER JOIN Orders
  4.  ON Customers.cust_id = Orders.cust_id
  5. GROUP BY Customers.cust_id;

上述sql在联结两张表后，根据cust_id来做逻辑分组，计算同一逻辑分组中order_num的和。

CH14 组合查询

主要有两种情况需要使用组合查询：
* 在一个查询中从不同的表返回结构数据
* 对一个表执行多个查询，按一个查询返回数据

大部分情况下，组合查询与具有多个WHERE子句的SELECT语句完成的工作相同

示例

  1. SELECT cust_name, cust_contact, cust_email
  2. FROM Customers
  3. WHERE cust_state IN ('IL','IN','MI')
  4. UNION                                               - 使用UNION关键字进行组合查询
  5. SELECT cust_name, cust_contact, cust_email
  6. FROM Customers
  7. WHERE cust_name = 'Fun4All';

UNION的默认行为会去除重复的行，如果想保留重复行，可使用UNION ALL关键字（这点WHERE做不到）

UNION规则

对组合查询结果排序

注意，对组合查询结果排序时，ORDER BY只能使用一次，并且放在最后一条SELECT语句之后（实际是对组合查询的结果进行排序）

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注