[数据库] SQL 语法之进阶篇

一、创建计算字段

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


1.1 计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。

  • 需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。

  • 物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量即可）。但为打印发票，需要物品的总价格。

  • 需要根据表数据进行诸如总数、平均数的计算。

存储在表中的数据可能不是应用程序所需要的，我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。这就是计算字段可以派上用场的地方了。与前面介绍的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。


1.2 拼接字段

为了说明如何使用计算字段，我们来举一个简单例子，创建由两列组成的标题。在 SQL 中的 SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS，此操作符可用加号（+）或两个竖杠（II）表示。在 MySQL 和 MariaDB 中，必须使用特殊的函数。

下面是 MySQL 和 MariaDB 中使用的语句：

SELECT Concat(vend_name, ' ', vend_country) FROM Vendors;

使用 Concat 函数拼接 vend_name，一个空格 ' ' 和 vend_country。


使用别名

SELECT 语句可以很好地拼接地址字段，但这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如 果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

为了解决这个问题，SQL 支持列别名。别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。请看下面的 SELECT 语句：

SELECT Concat(vend_name, ' ', vend_country) AS vend_title FROM Vendors;

这里的计算字段之后跟了文本 AS vend_title，它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。现在列名为 vend_title，任何客户端应用都可以按名称引用这个列，就像它是一个实际的表列一样。


1.3 执行算数计算

计算字段的另一常见用途是对检索出的数据进行算术计算。例如，可以汇总 2008 年这一年订单物品的价格（单价乘以订购数量）：

SELECT prod_id, quantity, item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

这里的计算字段之后跟了文本 AS vend_title，它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。现在列名为 vend_title，任何客户端应用都可以按名称引用这个列，就像它是一个实际的表列一样。

SQL 支持基本的加减乘除这4种算术操作符。


二、使用数据处理函数

下面介绍什么是函数，DBMS 支持何种函数，以及如何使用这些函数；还将讲解为什么 SQL 函数的使用可能会带来问题。

2.1 函数

与大多数其他计算机语言一样，SQL 也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。

函数带来的问题

与几乎所有 DBMS 都等同地支持 SQL 语句（如 SELECT）不同，每一个 DBMS 都有特定的函数。事实上，只有少数几个函数被所有主要的 DBMS 等同地支持。虽然所有类型的函数一般都可以在每个 DBMS 中使用，但各个函数的名称和语法可能极其不同。这就表示为特定 SQL 实现编写的代码在其他实现中可能不正常。


8.2 使用函数

大多数 SQL 实现支持以下类型的函数。

  • 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
  • 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
  • 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
  • 返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数。

1. 文本处理函数

下面例子使用的是 UPPER() 函数：

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors;

UPPER() 将文本转换为大写。

下表列出了一些常用的文本处理函数。

函 数 说 明
LEFT()（或使用子字符串函数） 返回字符串左边的字符
LENGTH() （也使用 DATALENGTH() 或 LEN()） 返回字符串的长度
LOWER() （Access 使用 LCASE()） 将字符串转换为小写
LTRIM() 去掉字符串左边的空格
RIGHT() （或使用子字符串函数） 返回字符串右边的字符
RTRIM() 去掉字符串右边的空格
SOUNDEX() 返回字符串的SOUNDEX值
UPPER() （Access 使用 UCASE()） 将字符串转换为大写

2. 日期和时间处理函数

MySQL 和 MariaDB 用户可使用名为 YEAR() 的函数从日期中提取年份：

SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;

DBMS 提供的功能远不止简单的日期成分提取。大多数 DBMS 具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是，可以看到，不同 DBMS 的日期-时间处理函数可能不同。关于具体 DBMS 支持的日期-时间处理函数，请参阅相应的文档。


3. 数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数使用那么频繁。具有讽刺意味的是，在主要 DBMS 的函数中，数值函数是最一致、最统一的函数。下表列出了一些常用的数值处理函数。

函 数 说 明
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

三、汇总数据

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


3.1 聚集函数

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

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
  • 获得表中某些行的和；
  • 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

为方便这种类型的检索，SQL 给出了 5 个聚集函数，见下表。这些函数能进行上述检索。与前一章介绍的数据处理函数不同，SQL 的聚集函数在各种主要 SQL 实现中得到了相当一致的支持。

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

1. AVG() 函数

AVG() 通过对表中行数计数并计算其列值之和，求得该列的平均值。下面的例子使用 AVG() 返回 Products 表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

此 SELECT 语句返回值 avg_price，它包含 Products 表中所有产品的平均价格。


2. COUNT() 函数

C0UNT() 函数进行计数。可利用 C0UNT() 确定表中行的数目或符合特定条件的行的数目。C0UNT() 函数有两种使用方式：

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

在此例子中，利用 C0UNT(*) 对所有行计数，不管行中各列有什么值。计数值在 num_cust 中返回。


3.2 聚集不同值

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

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

下面的例子使用 AVG() 函数返回特定供应商提供的产品的平均价格。它与上面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格：

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

使用了 DISTINCT 后，会排除相同的价格。


3.3 组合聚集函数

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

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;

这里用单条 SELECT 语句执行了 4 个聚集计算，返回 4 个值（Products 表中物品的数目，产品价格的最高值、最低值以及平均值）。


四、分组数据

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


4.1 数据分组

如果要返回每个供应商提供的产品数目，该怎么办？或者返回只提供一项产品的供应商的产品，或者返回提供 10 个以上产品的供应商的产品， 怎么办？这就是分组大显身手的时候了。使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。


4.2 创建分组

分组是使用 SELECT 语句的 GROUP BY 子句建立的。理解分组的最好办法是看一个例子：

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

输出如下：

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |
+---------+-----------+

上面的 SELECT 语句指定了两个列：vend_id 包含产品供应商的 ID，num_prods 为计算字段，GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods —次。


4.3 过滤分组

除了能用 GROUP BY 分组数据外，SQL 还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。

SQL 为此提供了另一个子句，就是 HAVING 子句。HAVING 非常类似于 WHERE。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是，WHERE 过滤行，而 HAVING 过滤分组。

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

这条 SELECT 语句的前三行类似于上面的语句。最后一行增加了 HAVING 子句，它过滤 COUNT(*) >= 2 （两个以上订单）的那些分组。


五、联结表

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


5.1 联结

SQL 最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习 SQL 的极为重要的部分。


1. 关系表

有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。

现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的理由是：

  • 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
  • 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；
  • 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。

关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors 表包含所有供应商信息，每个供应商占一行，具有唯一的标识。

Products 表只存储产品信息，除了存储供应商 ID （Vendors 表的主键）外，它不存储其他有关供应商的信息。Vendors 表的主键将 Vendors 表与 Products 表关联，利用供应商 ID 能从 Vendors 表中找出相应供应商的详细信息。


2. 为什么使用联结

如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。如果数据存储在多个表中，怎样用一条 SELECT 语句就检索出数据呢？

答案是使用联结。简单说，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。


5.2 创建联结

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可。请看下面的例子:

SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

SELECT 语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列（prod_name 和 prod_price）在一个表中，而第三列（vend_name）在另一个表中。

这条语句的 FROM 子句列出了两个表：Vendors 和 Products。它们就是这条 SELECT 语句联结的两个表的名字。这两个表用 WHERE 子句正确地联结，WHERE 子句指示 DBMS 将 Vendors 表中的 vend_id 与 Products 表中的 vend_id 匹配起来。

可以看到，要匹配的两列指定为 Vendors.vend_id 和 Products.vend_id。这里需要这种完全限定列名，如果只给出 vend_id，DBMS 就不知道指的是哪一个（每个表中有一个）。


5.3 联结多个表

SQL 不限制一条 SELECT 语句中可以联结的表的数目。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。例如：

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;

这个例子显示订单 20007 中的物品。订单物品存储在 OrderItems 表中。每个产品按其产品 ID 存储，它引用 Products 表中的产品。这些产品通过供应商 ID 联结到 Vendors 表中相应的供应商，供应商 ID 存储在每个产品的记录中。这里的 FROM 子句列出三个表，WHERE 子句定义这两个联结条件， 而第三个联结条件用来过滤出订单 20007 中的物品。


六、创建高级联结

本课讲解另外一些联结（包括它们的含义和使用方法），介绍如何使用表别名，如何对被联结的表使用聚集函数。


6.1 使用表别名

请看下面的 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';

可以看到，FROM 子句中的三个表全都有别名。Customers AS C 使用 C 作为 Customers 的别名，如此等等。这样，就可以使用省略的 C 而不用全名 Customers。在这个例子中，表别名只用于 WHERE 子句。其实它不仅能用于 WHERE 子句，还可以用于 SELECT 的列表、ORDER BY 子句以及其他语句部分。


6.2 使用不同类型的联结

迄今为止，我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：自联结（self-join）、自然联结（natura join）和外联结（outer join）。由于篇幅原因，这里不再过多介绍。


七、使用子查询

下面介绍什么是子查询，如何使用它们。


7.1 子查询

SQL 还允许创建子查询（subquery），即嵌套在其他查询中的查询。为什么要这样做呢？理解这个概念的最好方法是考察几个例子。


7.2 利用子查询进行过滤

订单存储在两个表中。每个订单包含订单编号、客户 ID、订单日期，在 Orders 表中存储为一行。各订单的物品存储在相关的 Orderltems 表中。Orders 表不存储顾客信息，只存储顾客 ID。顾客的实际信息存储在 Customers 表中。

现在，假如需要列出订购物品 RGAN01 的所有顾客，应该怎样检索？下面列出具体的步骤。

  1. 检索包含物品 RGAN01 的所有订单的编号。

  2. 检索具有前一步骤列出的订单编号的所有顾客的 ID。

  3. 检索前一步骤返回的所有顾客 ID 的顾客信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句。也可以使用子查询来把 3 个查询组合成一条语句。使用子查询的语句如下所示：

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

为了执行上述 SELECT 语句，DBMS 实际上必须执行三条 SELECT 语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的 WHERE 子句。外面的子查询返回顾客 ID 列表，此顾客 ID 列表用于最外层查询的 WHERE 子句。最外层查询返回所需的数据。


八、组合查询

本课讲述如何利用 UNION 操作符将多条 SELECT 语句组合成一个结果集。


8.1 组合查询

多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句。但是，SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：

  • 在一个查询中从不同的表返回结构数据；

  • 对一个表执行多个查询，按一个查询返回数据。


8.2 创建组合查询

可用 UNION 操作符来组合数条 SQL 查询。利用 UNION，可给出多条 SELECT 语句，将它们的结果组合成一个结果集。如下所示：

SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI') UNION SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All';

这条语句由前面的两条 SELECT 语句组成，之间用 UNION 关键字分隔。UNION 指示 DBMS 执行这两条 SELECT 语句，并把输出组合成一个查询结果集。


参考：

《SQL 必知必会》


posted @ 2019-09-27 21:04  fengMisaka  阅读(957)  评论(0)  编辑  收藏  举报