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