SQL必知必会学习笔记2
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在运行时关联指定的每个表，已处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。
