读书笔记：SQL必知必会【第7课-第9课】

第7课 创建计算字段

7.1 计算字段

存储在数据库中的数据一般不是应用程序所需要的格式，这里举几个例子：

• 需要显示公司名，同时还需要现实公司的地址，但这两个信息存储在不同的表列当中。
• 城市，州和邮政编码存储在不同的列中，单是邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
• 列数据是大小写混合的，但是报表程序需要把所有的数据按大写表示出来。
• 物品订单表存储物品的价格和数量，不存储每个物品的总价格，但是为打印发票需要物品的总价格。
• 需要根据表数据进行诸如总数、平均数的计算。

上述情况中，我们需要直接从数据库中检索出转换、计算或格式化后的数据，而不是先检索数据然后再在客户端应用程序中格式化。在这种时候，需要计算字段。

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

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段，通常与计算字段仪器使用。

需要特别注意，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户端来看，计算字段的数据与其他列的数据返回方式是相同的。

7.2 拼接字段

为了说明如何使用计算字段，举一个简单的例子，创建由两列组成的标题。

Vendors表中包含供应商名ven_name和地址信息vend_country，假如要生成一个供应商报表，需要在一列里列出供应商及其位置，那么需要创建一个组合值，解决办法是将这两列拼接起来。

拼接：将不同的值联结在一起构成单个值。

在SQL中的SELECT语句中，可以使用一个特殊的操作符来拼接两个列，不同的DBMS会采用不同的符号，对于MySQL，则必须使用concat函数。

mysql> SELECT CONCAT(vend_name,'(',vend_country,')')    -> FROM Vendors;+----------------------------------------+| CONCAT(vend_name,'(',vend_country,')') |+----------------------------------------+| Bear Emporium(USA)                     || Bears R Us(USA)                        || Doll House Inc.(USA)                   || Fun and Games(England)                 || Furball Inc.(USA)                      || Jouets et ours(France)                 |+----------------------------------------+6 rows in set (0.00 sec)

这样的列实际上是没有名字的，只是值，如果要输出到客户端中，这个列就必须有名字，为了解决这个问题，SQL支持别名（alias），用AS关键字赋予：

mysql> SELECT CONCAT(vend_name,'(',vend_country,')') AS vent_title    -> FROM Vendors;+------------------------+| vent_title             |+------------------------+| Bear Emporium(USA)     || Bears R Us(USA)        || Doll House Inc.(USA)   || Fun and Games(England) || Furball Inc.(USA)      || Jouets et ours(France) |+------------------------+6 rows in set (0.00 sec)

7.3 执行算术计算

计算字段的另一常见用途，是对检索出的数据进行算术计算。举个例子，Orders表包含收到的所有订单，OrderItems表包含每个订单中的各项物品。下面的SQL语句检索订单号20008的所有物品：

mysql> SELECT prod_id,quantity,item_price    -> FROM OrderItems    -> WHERE order_num = 20008;+---------+----------+------------+| prod_id | quantity | item_price |+---------+----------+------------+| RGAN01  |        5 |       4.99 || BR03    |        5 |      11.99 || BNBG01  |       10 |       3.49 || BNBG02  |       10 |       3.49 || BNBG03  |       10 |       3.49 |+---------+----------+------------+5 rows in set (0.00 sec)

item_price列表示订单中美享尚品的单价，quantity则是商品数，那么，可以加入新的一列，计算所有商品的总价：

mysql> SELECT prod_id,quantity,item_price ,quantity*item_price AS expanded_price    -> FROM OrderItems    -> WHERE order_num = 20008;+---------+----------+------------+----------------+| prod_id | quantity | item_price | expanded_price |+---------+----------+------------+----------------+| RGAN01  |        5 |       4.99 |          24.95 || BR03    |        5 |      11.99 |          59.95 || BNBG01  |       10 |       3.49 |          34.90 || BNBG02  |       10 |       3.49 |          34.90 || BNBG03  |       10 |       3.49 |          34.90 |+---------+----------+------------+----------------+5 rows in set (0.00 sec)

第8课 使用数据处理函数

8.1 函数

与大多数其他计算机语言一样，SQL也可以用函数来处理数据。

与几乎所有DBMS都等同地支持SQL语句不通，每一个DBMS都有特定的函数，事实上，只有少数几个函数被所有主要的DBMS等同第支持，下表列出3个常用的函数及其在各个DBMS中的语法：

可以看到，与SQL语句不一样，SQL函数不是可移植的：为特定SQL实现而编写的代码在其他实现中，可能是不正常的。

8.2 使用函数

8.2.1 文本处理函数

例：使用UPPER()函数将文本转换为大写：

mysql> SELECT vend_name,UPPER(vend_name) AS vend_name_upcase    -> FROM Vendors    -> ORDER BY vend_name;+-----------------+------------------+| vend_name       | vend_name_upcase |+-----------------+------------------+| Bear Emporium   | BEAR EMPORIUM    || Bears R Us      | BEARS R US       || Doll House Inc. | DOLL HOUSE INC.  || Fun and Games   | FUN AND GAMES    || Furball Inc.    | FURBALL INC.     || Jouets et ours  | JOUETS ET OURS   |+-----------------+------------------+6 rows in set (0.00 sec)

下表累出常用的文本处理函数：

8.2.2 日期和事件处理函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊格式，日期和时间值以特殊的格式存储，以便能够快速有效地排序或过滤，并且节省物理存储空间。在不同的DBMS中日期和时间函数是很不一致的，可移植性非常的差。

在Mysql中，可以用YEAR()从日期中提取年份：

mysql> SELECT order_num    -> FROM Orders    -> WHERE YEAR(order_date) = 2012;+-----------+| order_num |+-----------+|     20005 ||     20006 ||     20007 ||     20008 ||     20009 |+-----------+5 rows in set (0.00 sec)

关于MySQL的日期时间函数具体可以参考：http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html

8.2.3 数值处理函数

数值处理函数仅仅处理数值数据，这些函数一般主要用于代数、三角或机和运算，是在主要DBMS函数中最一致的函数类型，下表列出常见的数值处理函数：

第9课 汇总数据

9.1 聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此SQL提供了专门的函数，使用这些函数，SQL查询可用于检索数据，这种类型的例子有：

• 确定表中行数；
• 获得表中某些行的和
• 找出表列的最大值、最小值、平均值

上述例子需要的都是汇总表中的数据，而非实际数据本身。为了方便这种类型的检索，SQL给出了5个聚集函数，列于下表：

9.1.1 AVG()函数

AVG()通过对表中行数计数并进而计算列值之和，求得该列的平均值，可用来返回所有列的平均值，也可用来返回特定列或行的平均值。

下例返回Products表中所有产品平均价格：

mysql> SELECT AVG(prod_price) FROM Products;+-----------------+| AVG(prod_price) |+-----------------+|        6.823333 |+-----------------+1 row in set (0.01 sec)

AVG()也可用来确定特定列或行的平均值，下例返回的是特定供应商所提供产品的平均价格：

mysql> SELECT AVG(prod_price) AS avg_price    -> FROM Products    -> WHERE vend_id = 'DLL01'    -> ;+-----------+| avg_price |+-----------+|  3.865000 |+-----------+1 row in set (0.00 sec)

9.1.2 COUNT()函数

COUNT()函数进行计数，可以确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

下例返回Customers表中顾客总数：

mysql> SELECT COUNT(*) FROM Customers;+----------+| COUNT(*) |+----------+|        5 |+----------+1 row in set (0.00 sec)

下例则只对具有电子邮件地址的客户计数：

mysql> SELECT COUNT(cust_email) AS num_cust FROM Customers;+----------+| num_cust |+----------+|        3 |+----------+1 row in set (0.00 sec)

9.1.3 MAX()函数

MAX()返回指定列中的最大值，需要注意的是，MAX()必须指定列名，如下所示：

mysql> SELECT MAX(prod_price) AS max_price    -> FROM Products;+-----------+| max_price |+-----------+|     11.99 |+-----------+1 row in set (0.00 sec)

9.1.4 MIN()函数

MIN()函数返回指定列中的最小值，同样需要指定列名：

mysql> SELECT MIN(prod_price) AS min_price FROM Products;+-----------+| min_price |+-----------+|      3.49 |+-----------+1 row in set (0.00 sec)

9.1.5 SUM()函数

SUM()用来返回指定列值的和（总计）。OrderItems表中包含订单的实际物品，每个物品都有相应的数量，可如下检索所订购物品的总数：

mysql> SELECT SUM(quantity) AS item_ordered    -> FROM OrderItems    -> WHERE order_num = 20005;+--------------+| item_ordered |+--------------+|          200 |+--------------+1 row in set (0.00 sec)

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

mysql> SELECT SUM(quantity*item_price) AS total_price    -> FROM OrderItems    -> WHERE order_num = 20005;+-------------+| total_price |+-------------+|     1648.00 |+-------------+1 row in set (0.00 sec)

9.2 聚集不同值

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

• 对所有行执行计算，指定ALL参数或者不指定参数（因为ALL是默认行为）
• 只包含不同的值，指定DISTINCT函数

下面的例子使用AVG()函数返回特定供应商提供的产品平均价格，使用了DISTINCT函数：

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price    -> FROM Products    -> WHERE vend_id = 'DLL01';+-----------+| avg_price |+-----------+|  4.240000 |+-----------+1 row in set (0.00 sec)

9.3 组合聚集函数

目前为止的所有聚集函数都只涉及单个函数，但是实际上，SELECT语句可以根据需要包含多个聚集函数，例子如下：

mysql> 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;+-----------+-----------+-----------+-----------+| num_items | price_min | price_max | price_avg |+-----------+-----------+-----------+-----------+|         9 |      3.49 |     11.99 |  6.823333 |+-----------+-----------+-----------+-----------+1 row in set (0.00 sec)