注：这一系列的文章是《SQL必知必会》第四版的读书笔记。

7.创建计算字段

什么是计算字段，怎么创建计算字段，以及如何从应用程序中使用别名引用它们。

7.1 计算字段

字段：基本上与列的意思相同，经常互换使用。术语字段通常与计算字段一起使用。

计算字段是运行时在SELECT语句内创建的，并不实际存在于数据库表中。

只有数据库直到SELECT语句中哪些列时实际列，哪些列时计算字段。从客户端来看，实际列和计算字段返回的方式相同。

7.2 拼接字段

拼接：将值联结到一起构成单个值。

输入：

SELECT Concat(vend_name,' (',vend_country,')')

FROM Vendors

ORDER BY vend_name;

上面输出的列没有名字，不能用于客户端应用中。为了解决这个问题，SQL支持列别名。

别名：一个字段或值得替换名。别名使用AS关键字赋予。

输入：

SELECT Concat(vend_name,' (',vend_country,')')

AS vend_title

FROM Vendors

ORDER BY vend_name;

7.3 执行算术计算

计算字段另一常见用途是对检索出的数据进行算术计算。

输入：

SELECT prod_id,

quantity,

item_price,

quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num=20008;

8.使用数据处理函数

介绍什么是函数，DBMS支持何种函数，如何使用这些函数，以及SQL函数的使用可能带来的问题。

8.1 函数

每一个DBMS都有特定的函数，即使功能一样的函数，但在不同的DBMS中名称和语法可能极其不同。

SQL函数不是可移植的。

8.2 使用函数

8.2.1 文本处理函数

UPPER()函数的使用。

输入：

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase

FROM Vendors

ORDER BY vend_name;

SOUNDEX()函数，将任何文本串转换为其语音表示的字母数字模式的算法。对字符串进行发音比较而不是字母比较。

输入：

SELECT cust_name,cust_contact

FROM Customers

WHERE SOUNDEX(cust_contact)=SOUNDEX('Michael Green');

8.2.2 日期和时间处理函数

以MySQL中的YEAR()函数为例。

输入：

SELECT order_num

FROM Orders

WHERE YEAR(order_date)=2012;

8.2.3 数值处理函数

在主要的DBMS中，数值处理函数时最一致、最统一的函数。

常用的数值处理函数：

ABS()，COS()，EXP()，PI()，SIN()，SQRT()，TAN()

9.汇总数据

介绍什么是SQL的聚集函数，利用它们汇总表的数据。

9.1 聚集函数

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

SQL聚集函数：AVG()，COUNT()，MAX()，MIN()，SUM()

9.1.1 AVG()函数

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

输入：

SELECT AVG(prod_price) AS avg_price

FROM Products;

输入：

SELECT AVG(prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

注意：AVG()只用于单个列。AVG()函数忽略值为NULL的行。

9.1.2 COUNT()函数

COUNT()函数进行计数，有两种使用方式：

（1）COUNT(*)：对表中行的数目进行计数，不管包含的是NULL值还是非NULL值。

（2）COUNT(column)：对特定列中具有值得行进行计数，忽略NULL值。

输入：

SELECT COUNT(*) AS num_cust

FROM Customers;

输入：

SELECT COUNT(cust_email) AS num_cust

FROM Customers;

9.1.3 MAX()函数

MAX()函数要求指定列名，并且忽略列值为NULL的行。

输入：

SELECT MAX(prod_price) AS max_price

FROM Products;

9.1.4 MIN()函数

与MAX()函数类似。

9.1.5 SUM()函数

SUM()函数用来返回指定列值的和。忽略值为NULL的行。

输入：

SELECT SUM(quantity) AS item_ordered

FROM OrderItems

WHERE order_num=20005;

SUM()函数也能用来合计计算值。

输入：

SELECT SUM(item_price*quantity) AS total_price

FROM OrderItems

WHERE order_num=20005;

9.2 聚集不同值

以上五个聚集函数可以如下使用：

（1）对所有行执行计算，指定ALL参数或不指定参数（因为默认是ALL参数）。

（2）只包含不同的值，指定DISTINCT参数。

输入：

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

DISTINCT只能用于COUNT()，不能用于COUNT(*)，必须使用列名，不能用于计算或表达式。

9.3 组合聚集函数

聚集函数能够组合起来使用。

输入：

SELECT COUNT(*) AS num_items,

MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,

AVG(prod_price) AS price_avg

FROM Products;

10.分组数据

介绍如何分组数据，以便汇总表内容的子集。涉及的新SELECT子句：GROUP BY子句，HAVING子句。

10.1 数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

10.2 创建分组

分组是使用GROUP BY子句建立的。

输入：

SELECT vend_id,COUNT(*) AS num_prods

FROM Products

GROUP BY vend_id;

使用GROUP BY子句需要知道的一些重要规定：

（1）GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更加细致的进行数据分组。

（2）如果GROUP BY子句嵌套了分组，数据将在最后指定的分组上进行汇总。即，在建立分组时，指定的所有列都一起计算。

（3）GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT子句中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

（4）大多数SQL实现不允许GROUP BY子句列带有长度可变的数据类型。

（5）除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。

（6）如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多个行是NULL值，它们将分为一组。

（7）GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

10.3 过滤分组

WHERE子句：过滤指定的是行。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;

10.4 分组和排序

GROUP BY：对行分组，但输出可能不是分组的顺序；只可能使用选择列或表达式列，而且必须使用每个选择列表达式；如果与聚集函数一起使用列（或表达式），则必须使用。

ORDER BY：对产生的输出排序；任意列都可以使用；不一定需要。

输入：

SELECT order_num,COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*)>=3

ORDER BY items,order_num;

10.5 SELECT子句的顺序

SELECT：必须使用

FROM：仅在从表选择数据时使用

WHERE：非必须

GROUP BY：仅在按组计算聚集时使用

HAVING：非必须

ORDER BY：非必须

11.使用子查询

介绍什么是子查询，使用子查询。子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。

11.1 子查询

子查询：嵌套在其他查询中的查询。

MySQL对子查询是从4.1版本开始的。

11.2 利用子查询进行过滤

子查询总是从内向外处理。对能嵌套的子查询的数目没有限制，但在实际使用时，出于性能的限制，不能嵌套太多的子查询。

作为子查询的SELECT语句只能查询单个列。

列出订购物品RGAN01的所有顾客。

输入：

SELECT cust_name,cust_contact

FROM Customers

WHERE cust_id IN (SELECT cust_id

                                 FROM Orders

                                 WHERE order_num IN (SELECT order_num

                                                                        FROM OrderItems

                                                                        WHERE prod_id='RGAN01'));

11.3 作为计算字段使用子查询

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

需要显示Customers表中每个顾客的订单总数。

输入：

SELECT cust_name,

               cust_state,

               (SELECT COUNT(*)

                FROM Orders

                WHERE Orders.cust_id=Customers.cust_id) AS orders

FROM Customers

ORDER BY cust_name;

注意使用完全限定列名来避免歧义。

12.联结表

介绍什么是联结，为什么使用联结，如何编写使用联结的SELECT语句。

12.1 联结

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

12.1.1 关系表

关系表的设计就是将信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联，这样可以有效的存储，方便的管理。关系数据库的可伸缩性远比非关系数据库要好。

12.1.2 为什么使用联结

联结是一种机制，用来在一条SELECT语句中关联表。使用特殊的语法，可以联结多个表返回一组输出。

12.2 创建联结

创建联结非常简单，指定要联结的所有表和关联它们的方式即可。

用WHERE子句联结。

输入：

SELECT vend_name,prod_name,prod_price

FROM Vendors,Products

WHERE Vendors.vend_id=Products.vend_id;

12.2.1 WHERE子句的重要性

联结两个表时，实际要做的是将第一个表的每一行与第二个表的每一行配对。WHERE子句作为过滤条件，只包含满足给定条件的行。没有WHERE子句的话，两个表联结的结果将是一个笛卡尔积。

12.2.2 内联结

等值联结：基于两个表之间的相等测试，也称为内联结。

关键字：INNER JOIN，ON

输入：

SELECT vend_name,prod_name,prod_price

FROM Vendors INNER JOIN Products

 ON Vendors.vend_id=Products.vend_id;

12.2.3 联结多个表

显示订单20007中的物品。

输入：

SELECT prod_name,vend_name,prod_price,quantity

FROM OrderItems,Products,Vendors

WHERE Products.vend_id=Vendors.vend_id

 AND OrderItems.prod_id=Products.prod_id

 AND order_num=20007;

13.创建高级联结

讲解另一些联结，介绍如何使用表别名，如何对被联结的表使用聚集函数。

13.1 使用表别名

使用表别名的两个理由：

（1）缩短SQL语句

（2）允许在一条SELECT语句中多次使用相同的表

输入：

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';

13.2 使用不同类型的联结

其他的三种联结：自联结，自然联结，外联结。

13.2.1 自联结

要给与Jim Jones同一公司的所有顾客发送一封邮件。

输入：

（1）使用子查询

SELECT cust_id,cust_name,cust_contact

FROM Customers

WHERE cust_name=(SELECT cust_name

FROM Customers

WHERE cust_contact='Jim Jones');

（2）使用自联结

SELECT c1.cust_id,c1.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';

用自联结而不用子查询：许多DBMS处理联结远比处理子查询快的多，应该试一下两种不同的方法，以确定哪一种性能更好。

13.2.2 自然联结

自然联结排除多次出现，使每一列只返回一次。系统不完成这项工作，由自己完成。自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。

输入：

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';

13.2.3 外联结

外联结：包含了那些在相关表中没有关联的行。

关键字：OUTER JOIN，ON。使用RIGHT或LEFT关键字指定包括其所有行的表。

输入：

SELECT Customers.cust_id,Orders.order_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id=Orders.cust_id;

注意：MySQL不支持FULL OUTER JOIN 的语法。

13.3 使用带聚集函数的联结

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

输入：

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ord

FROM Customers INNER JOIN Orders

ON Customers.cust_id=Orders.cust_id

GROUP BY Customers.cust_id;

13.4 使用联结和联结条件

（1）注意所使用的联结类型。

（2）关于确切的联结语法，应当查看具体的文档。

（3）保证使用正确的联结条件。

（4）应该总是提供联结条件。

（5）在一个联结中可以包含多个表，甚至对每个联结都可以采用不同的联结类型。在一起测试它们之前，先分别测试每个联结。