SQL必知必会学习笔记2

七、创建计算字段

1.计算字段

  存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或者格式化过的数据，而不是检索出数据然后再在客户端应用程序中更新格式化。

2.拼接字段

  将两个列拼接起来，需要使用操作符(+)或者(||)。

SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name；

  以上语句是拼接一个name(country)的字段。如果需要去除拼接后填充的空格，可以使用RTRIM函数来完成。

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name；

  RTRIM()函数去掉是右边所有空格，LTRIM()去掉的是左边的括号，以及TRIM()去掉两边的空格。

使用别名

  可以使用别名来表示计算字段，使用AS关键字赋予。别名也称为导出列。

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name；

3.执行算术计算

操作符	说明
+	加
-	减
*	乘
/	除

  实例如下：

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

八、使用函数处理数据

1.使用函数

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

• 处理文本字符串
• 数值计算操作
• 处理日期和时间，并从中提取特定成分。
• 返回DBMS使用的特殊信息(如用户登录信息)的系统函数。

文本处理函数

函 数	说 明
LEFT()	返回字符串左边的字符
LENGTH()	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RTRIM()	去掉字符串右边的空格
RIGHT()	返回字符串右边的字符
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	字符串转换为大写

  SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式算法。可以进行发音匹配。举例，Customers表中有一个顾客Kids Place，其联系名为Michelle Green。如果这是错误输入，此联系名实际上应该是Michael Green，该怎么办？显然按照正确的联系名进行搜索不会返回数据。使用SOUNDEX函数进行搜索，匹配发音类似于Michael Green的联系名：

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

日期和时间处理函数

  SQL中检索2012年所有的订单，可以如下进行：

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

数值处理函数

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

九、汇总数据

1.聚集函数

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

AVG()函数

• 只能使用单个列
• AVG()函数忽略值为NULL的行。

COUNT()

  两种使用方式：

• 使用COUNT(*)对于表中行的数目进行技术，不管表列中包含的是(NULL)还是非空值。

SELECT COUNT(*) 
AS num_cust
FORM Customers

• 使用COUNT(column)对特定列中具有值得进行计数，忽略NULL值。统计有值的个数。

SELECT COUNT(cust_email) 
AS num_cust
FORM Customers

MAX()和MIN()

  这两个将忽略NULL值。

SUM()函数

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005；

  返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

2.聚集不同值

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

• 对所有执行计算，指定ALL参数或不指定参数。
• 只包含不同的值，指定DISTINCT。

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

警告：DISTINCT不能用于COUNT(*)

3.组合聚集函数

  在一个语句中使用多个聚集函数：

SELECT COUNT(*) AS num_item, 
MIN(prod_price) AS price_max, 
AVG(prod_price) AS price_avg,
FROM PRoducts;

十、分组数据

1.数据分组

  如果要返回每个供应商提供的产品数目，该怎么办？这个时候就需要分组，使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。使用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子句中指定相同的表达式。不能使用别名。
• 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型。
• 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
• 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
• GROUP BY可以基于相对位置进行分组，GROUP BY 2,1。

2.过滤分组

  要想对分组进行过滤，不能使用WHERE子句，WHERE过滤指定的是行而不是分组。需要使用HAVING子句

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

  WHERE和HAVING配合使用

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

  ORDER BY和GROUP BY配合使用

SELECT order_num, COUNT(*) 
AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

十一、使用子查询

1.子查询

  我们迄今为止所看到所有的SELECT语句都是简单查询，子查询就是简单查询嵌套简单查询的方式进行查询，举例如下：

SELECT cust_id
FROM Orders
WHERE order_num IN
(SELECT order_num 
FROM OrderItems
WHERE prod_id = 'RGAN01')

  子查询都是从内到外进行处理。

2.使用完全限定列名

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

  查询涉及多个表的时候要使用完全限定列名。 WHERE Orders.cust_id = Customers.cust_id

十二、联结表

1.联结

  SQL最强大的功能之一就是能在数据查询中执行联结(join)表。先来了解一下基础知识。

关系表

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

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

  相同数据出现多次决不是一件好事，这是关系数据库设计的基础，关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值相互关联。

  这个例子中可以建立两个表：一个存储供应商信息，一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键，可以是供应商ID或任何其他唯一值。PRODUCT表只存储产品信息，除了存储供应闪ID外，不存储其他有关供应商的信息。

为什么使用联结

  关系表带来了有效存储，跟方便的处理，但如果数据存储在多个表中，怎样用一条SELECT语句就检索出数据呢？答案就是联结。

2.创建联结

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

  从两个表中获取数据，就是联结，用WHERE语句进行正确的联结，vend_id进行匹配。要使用完全限定名。

  笛卡尔积 由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目僵尸第一个表中函数乘以第二个表中的行数。返回笛卡尔积的联结，也成叉联结。

内联结

  前面的例子是等值联结，这种联结也成为内联结，可以使用不同的语法来指明它：

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

联结多个表

SELECT vend_name, prod_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;

警告：性能考虑：DBMS在运行时关联指定的每个表，已处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。