SQL基础学习2

第8章：使用函数处理数据
SQL函数带来的问题：某些函数不可移植，每个DBMS有其特有的函数。
多数SQL支持的函数处理：文本函数、数值函数、日期和时间函数、系统函数。

将检索值转换为大写，并赋一个别名：

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

常用的文本处理函数：

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

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。使得能对字符串进行发音比较而不是字母比较。
如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX（）在SQLite中就可用，否则多数SQLite实现不支持SOUNDEX（）。

日期和时间处理函数

检索2012年的所有订单：

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2012';

数值处理函数

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

第9章： 汇总数据

仅仅汇总数据，而不是把他们检索出来。
聚集函数：对某些运行的函数，计算并返回一个值。

SQL聚集函数：

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

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

检索产品价格的平均值：

SELECT AVG(prod_price) AS avg_price
FROM Products;

检索特定行或列的平均值：

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

COUNT（）函数对表中行的数目或符合特定条件的行的数目进行计数。
1.使用COUNT（＊）对表中行的数目进行计数，不管表列中包含的是空值还是非空值。
2.使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

返回Customers表中顾客的总数：

SELECT COUNT(*) AS num_cust
FROM Customers;

对具有电子邮件地址的客户计数：

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

MAX()返回指定列中的最大值：

SELECT MAX(prod_price) AS max_price
FROM Products;

对非数值数据使用MAX（），它返回该列排序后的最后一行。

MIN()函数返回指定列中的最小值：

SELECT MIN(prod_price) AS min_price
FROM Products;

用于文本数据时，MIN（）返回该列排序后的最前面一行。

SUM()函数用来返回指定列值的和（总计）。也可用来合计计算值。

返回订单中所有物品数量之和。

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

合计计算值

SELECT SUM(item_price * quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

聚集不同值：

使用DISTINCT，可只包含不同的值，相同值（价格）被排除，所以提高了均价。

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

组合聚集函数
可根据需要包含多个聚集函数：

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;

第10章：分组数据
使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

创建分组：GROUP BY，其必须在ORDER BY之前，WHERE之后。

以下根据供应商ID检索其有多少个产品：

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

过滤分组：HAVING

从订单中检索（仅列出）顾客ID并对有两个以上订单的顾客分组

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

列出具有两个以上产品且其价格大于等于4的供应商：

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

分组和排序：
GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同，理解这一点很重要。它们之间的差别：
ORDER BY:
1。对产生的输出排序
2。任意列都可以使用（甚至非选择的列也可以使用）
3。不一定需要
GROUP BY：
1。对行分组，但输出可能不是分组的顺序
2。只可能使用选择列或表达式列，而且必须使用每个选择列表达式
3。如果与聚集函数一起使用列（或表达式），则必须使用

检索包含三个或更多物品的订单号和订购物品的数目：

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

对于上面的例子按订购物品的数目排序输出：

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

在SELECT语句中必须遵循的顺序：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

第11课：使用子查询

子查询：嵌套在其他查询中的查询。

使用子查询列出订购物品RGAN01的所有顾客：

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

使用子查询检索这些顾客ID的顾客信息：

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

作为计算字段使用子查询：
以下显示Customers表中每个顾客的订单总数：
使用 . 操作符限定列名。

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

第12课：联结表
SQL最强大的功能之一就是能在数据查询的执行中联结表。之所以能够有效连接，是因为关系表及关系数据库的设计。
关系表的设计就是把信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联（所以叫关系数据库）。

创建连接，检索两个表中的指定内容：

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

上面代码中WHERE子句的重要性：
要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导致不正确的数据。

还有一种内联结方法，而最重具体选择哪种语法，请参阅具体的DBMS文档，方法如下，使用关键字INNER JOIN(内联结）和ON：

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

联结多个表：
SQL不限制一条SELECT语句中可以联结的表的数目，但处于性能考虑，DBMS都有限制。

下面这个例子显示订单2007中的物品：

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;

使用联结改进第11课（子查询）中的：返回订购产品RGAN01的顾客列表的例子：

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id =  Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';