SQL Syntax(Ⅱ)

2017-04-23

继续。

创建计算字段

以下以书中例子呈现相关用法。

拼接字段

SELECT vend_name + '(' + vend_country+ ')'
FROM venders
ORDER BY vender_name;

SELECT vend_name || '(' || vend_country+ ')'
FROM venders
ORDER BY vender_name;

以上两种写法是等价的，还可以用RTRIM()去除字符串右边的空格，用LTRIM()去除字符串左边的空格，用TRIM()去除字符串左右两边的空格。特别地，在MySQL和MariaDB中需要使用Concat()函数来完成相同的功能：

# In MySQL and MariaDB
SELECT Concat(vend_name, '(', vend_country, ')')
FROM venders
ORDER BY vender_name;

为结果使用别名

SELECT vend_name + '(' + vend_country+ ')'
AS vend_title
FROM venders
ORDER BY vender_name;

执行算术计算

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

使用函数处理数据

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER()（Access使用LCASE()）	将字符串转换为小写
SOUNDEX()	返回字符串的SOUNDEX值（Access和PostgreSQL不支持，SQLite需要编译支持）
UPPER()	将字符串转换为大写
DATEPART()	返回日期类型中的年、月或日

关于SOUNDEX()，

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

【实测了一下，Michelle和Michael，Knuth和Kant的SOUNDEX值都是一样的，但是Bitch和Beach是不一样的，前者的SOUNDEX值是B320，后者则是B200……】

汇总数据

聚集函数

常用的函数包括AVG(), COUNT(), MAX(), MIN(), SUM()等，可以使用WHERE过滤，这些函数默认会忽略值为NULL的行，用*则可以不忽略；
聚集不同值

ALL代表对所有行进行运算，是默认行为，不需要指定；

DISTINCT只对不同的行进行操作；
组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       ACG(prod_price) AS price_avg
FROM Products;

分组数据

创建分组

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

/*output:
vend_id		num_prods
-------		---------
BRS01		3
DLL01		4
FNG01		2
*/

其中，GROUP BY后可以包含任意数目的列以进行更细致的数据分组，还可以在子句中嵌套分组。SELECT语句中的每一列都必须在GROUP BY子句中给出。包含NULL值的行会被作为一个分组返回。GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前。

过滤分组

即规定包括哪些组，排除哪些组。

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

这里不能用 WHERE替换HAVING，可以理解为 WHERE 在分组前进行过滤，故排除的行不包括在分组中，而 HAVING 在数据分组后过滤。当然，两个子句并非不能同时存在，如：

SELECT cust_id, COUNT(*) AS orders
FROM Orders
WHERE prod_price >= 4
GROUP BY cust_id
HAVING COUNT(*) >= 2
/*output:
vend_id		num_prods
-------		---------
BRS01		3
DLL01		2
*/

分组和排序

下表列出了 ORDER BY 和 GROUP BY 的区别

ORDER BY	GROUP BY
对产生的输出进行排序	对行分组，但输出可能不是分组的顺序【所以可以在后面再使用ORDER BY对输出进行排序】
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

使用子查询

子查询(Subquery)，即嵌套在其他查询中的查询。在需要多个SQL语句共同完成一个任务且他们之间存在递进关系时，可以使用类似于以下的用法：

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’ 这一句的输出结果传递给外面的 WHERE 查询子句。

本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。

创建计算字段

使用函数处理数据

汇总数据

分组数据

使用子查询

Contents