SQL必知必会(二) 计算字段、函数、汇总数据、分组数据、子查询

七、创建计算字段

计算字段（field）

基本上与列的意思一样，经常互换使用。 在数据中完成需要转换和格式化工作，会比客户端快很多。

拼接(concatenate)字段

Access和SQL server使用+号，DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用 ||

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

SELECT vend_name || '(' || vend_country || ')' FROM Vendors
ORDER BY vend_name;

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

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' FROM Vendors
ORDER BY vend_name;

RTRIM去掉右边的所有空格，LTRIM去掉左边所有空格，TRIM去掉左右两边所有空格

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' 
AS vend_title  --AS后面的就是别名
FROM Vendors
ORDER BY vend_name;

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' 
AS vend_title
FROM Vendors
ORDER BY vend_name;

执行算术计算

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

SELECT prod_id ,quantity,item_price,
quantity * item_price AS expanded_price  --会额外列出expanded_price表示总价
FROM OrderItems
WHERE order_num = 20008;

Tips：

SELECT 3*2 --返回6
SELECT Trim('abc') --返回abc
SELECT Now() --返回当年日期和时间

八、使用函数处理数据

函数

不同数据库使用函数不同，移植性差。需要程序员不赞成使用特定于实现的功能。

使用函数

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

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

文本处理函数

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

SELECT cust_name ,cust_contact FROM Customers
WHERE cust_contact = 'Micheal Green'

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

日期和时间处理函数

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

SELECT order_num FROM Orders
WHERE to_number(to_char(order_date,'YYYY'))= 2012;
--Oracle版本

SELECT order_num FROM Orders
WHERE YEAR(order_date) = 2012;
--MySQL版本

九、汇总数据

聚集函数

SELECT AVG(prod_price) AS avg_price
FROM Products;

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

SELECT COUNT(*) AS num_cust
FROM Customers;   

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

COUNT(*)对表中行的数目进行计数，不管是空值还是非空 ,COUNT(colum)忽略空值

SELECT MAX(prod_price) AS max_price
FROM Products;

SELECT MIN(prod_price) AS max_price
FROM Products;

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

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

聚集不同值

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

DISTINCT不能用于COUNT(*)

组合聚集函数

SELECT COUNT(*) AS num_items,
        MAX(prod_price) AS max_price,
        MIN(prod_price) AS max_price,
        AVG(prod_price) AS avg_price
FROM Products;

十、分组数据

数据分组

SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = 'DLL01';

创建分组

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

GROUP BY 语句必须出现在WHERE字句之后，ORDER BY 子句之前

过滤分组

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

HAVING非常类似WHERE语句，唯一的差别是WHERE过滤行，HAVING 过滤分组

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

分组和排序

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;

虽然GROUP BYU 分组后的数据有时确实是以分组顺序输出的，但是不总是这样，加上ORDER_BY保险

十一、使用子查询

利用子查询进行过滤

1. 检索包含物品RGAN01的所有订单的编号
2. 检索具有前一步骤列出的订单编号的所有顾客ID
3. 检索前一步操作返回的所有顾客ID的顾客信息

SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

SELECT cust_id FROM Orders WHERE order_num IN(20007,20008);

组合后：

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

SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

SELECT cust_id FROM Orders WHERE order_num IN(20007,20008);

SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN('10000004,1000005');

组合后:

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  cust_name , 
        cust_state ,
        (SELECT COUNT(*)
         FROM Orders
         WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;