高性能 SQL 计划 Day 2

2020-06-06 本文已影响0人刘点石

上一次更新竟然是将近一个月之前了，最近因为乱七八糟的事情比较多所以没有更新。上一次的内容主要是《MySQL 必知必会》前九章的内容，今天计划继续。

Ch 10.创建计算字段

一般而言，如果检索结果与程序所需要的数据之间有差异，则需要对检索结果进行处理，这个过程既可以在数据库服务器中完成，又可以在客户机应用程序内实现。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

1.拼接字段

在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。注意：多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;

为了方便客户机对于上述拼接结果的使用，可以使用 AS 关键字对结果进行命名，如下所示：

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

2.算术运算

对检索出的数据进行算术运算：

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

Ch 11.使用数据处理函数

需要注意的是，DMBS 中函数的可移植性比较差，几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。因此，在 SQL 中使用函数时，需要写明注释。

1.使用函数

MySQL 中所支持的函数主要包含以下几类：

文本处理函数

# 文本处理函数
# Left()  返回串左边的字符
# Length()    返回串的长度
# Locate()    找出串的一个子串
# Lower() 将串转换为小写
# LTrim() 去掉串左边的空格
# Right() 返回串右边的字符
# RTrim() 去掉串右边的空格
# SubString() 返回子串的字符
# Upper() 将串转换为大写

#例：
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

日期与时间处理函数

# AddDate()   增加一个日期（天、周等）
# AddTime()   增加一个时间（时、分等）
# CurDate()   返回当前日期
# CurTime()   返回当前时间
# Date()  返回日期时间的日期部分
# DateDiff()  计算两个日期之差
# Date_Add()  高度灵活的日期运算函数
# Date_Format()   返回一个格式化的日期或时间串
# Day()   返回一个日期的天数部分
# DayOfWeek() 对于一个日期，返回对应的星期几
# Hour()  返回一个时间的小时部分
# Minute()    返回一个时间的分钟部分
# Month() 返回一个日期的月份部分
# Now()   返回当前日期和时间
# Second()    返回一个时间的秒部分
# Time()  返回一个日期时间的时间部分
# Year()  返回一个日期的年份部分

#例1，根据订单日期查找

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';
#以上示例喂错误示例，因为 order_date 可能包含具体时间信息，这样的话就无法匹配成功。

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
#以上为正确示例

#例2，查找2005年9月份的订单
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
#或
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数
```
# Abs()   返回一个数的绝对值
# Cos()   返回一个角度的余弦
# Exp()   返回一个数的指数值
# Mod()   返回除操作的余数
# Pi()    返回圆周率
# Rand()  返回一个随机数
# Sin()   返回一个角度的正弦
# Sqrt()  返回一个数的平方根
# Tan()   返回一个角度的正切

#这类函数比较简单
```
Ch 12. 汇总数据

1.聚集函数
```
# 主要的聚集函数
# AVG()   返回某列的平均值
# COUNT() 返回某列的行数
# MAX()   返回某列的最大值
# MIN()   返回某列的最小值
# SUM()   返回某列值之和
```
- AVG() 函数
```
SELECT AVG(prod_price) AS avg_price
FROM products;
```
  注意：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。
- COUNT() 函数
  
  有两种使用方式。
  - 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  - 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
```
SELECT COUNT(*) AS num_cust
FROM customers;

SELECT COUNT(cust_email) AS num_cust
FROM customers;
```
  注意：如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。
- MAX() 函数
  
  MAX()返回指定列中的最大值。MAX()要求指定列名。
```
SELECT MAX(prod_price) AS max_price
FROM products;
```
  虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。
  
  注意：MAX()函数忽略列值为NULL的行。
- MIN() 函数
  
  MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名。
```
SELECT MIN(prod_price) AS min_price
FROM products;
```
  注意： MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。MIN() 函数同样忽略列值为NULL的行。
- SUM() 函数
  
  SUM()用来返回指定列值的和（总计）。
```
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
```
  注意：SUM()函数忽略列值为NULL的行。
2.聚集不同值

以上5个聚集函数都可以如下使用：
- 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
- 只包含不同的值，指定DISTINCT参数。
```
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
```
- 将 DISTINCT 用于COUNT() 函数：必须用于指定列名的场合，不能用于COUNT(*)。
- 将DISTINCT用于MIN()和MAX()函数：虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

组合聚集函数

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;

注意：在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用（以及将来容易排除故障）。

Ch 13.分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

1.创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

使用GROUP BY子句有以下规定：

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值：
```
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
```

2.过滤分组

我们之前所介绍的WHERE子句能够完成对行的过滤，如果要对分组的列进行过滤，需要使用HAVING子句。

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

注意：所学过的有关WHERE的所有这些技术和选项都适用于HAVING。它们的句法是相同的，只是关键字有差别。

HAVING和WHERE的差别：这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

同样可以在一个查询语句中同时使用 WHERE 和 HAVING 子句：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

3.分组与排序

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

上表的第一条尤为重要，我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。

总结：一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

4.SELECT子句顺序

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

Ch 14.使用子查询

所谓子查询（subquery），即嵌套在其他查询中的查询。当不使用联结表查询时，使用简单的子查询可以解决多表查询的问题。下面通过几个例子对子查询进行学习：

1.利用子查询进行过滤

对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

#1.检索包含物品TNT2的所有订单的编号。
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
#输出结果为 20005和20007

#2.检索具有前一步骤列出的订单编号的所有客户的ID。
SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);
#输出结果为10001和10004

#3.检索前一步骤返回的所有客户ID的客户信息。
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001,10004);

#4.可以将上述的WHERE子句转换为子查询进行整合
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 = 'TNT2'));

注意：在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

另外，虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<）等。

2.作为计算字段使用子查询

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中，应该怎样检索？

#1.从customers表中检索客户列表。
#2.对于检索出的每个客户，统计其在orders表中的订单数目。

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

需要注意的是，这里WHERE子句中使用了完全限定列名，当使用相关子查询（设计外部查询的子查询）出现列名歧义的时候，必须使用完全限定列名消歧义，否则会出错。

未完待续...