SQL 必知必会

最近阅读了《SQL 必知必会》，本篇文章算作总结和记录

检索

检索数据语法

• DISTINCT:关键词表示去除重复项, 且作用于所有的列

• LIMIT: 限制检索的行数

• OFFSET: 表示起点从哪条数据开始(从 0 开始)

SELECT (DISTINCT) [字段] FROM [表名] LIMIT [数量] OFFSET [数量]

排序

排序数据语法

ORDER BY: 使用ORDER BY子句进行排序, 应该保证它是 SELECT 语句的最后一条子句

按多列排序

-- 先按价格, 再按名称
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name

按列相对位置排序

-- 按照已选列中的相对位置来排序(prod_price是第2, prod_name是第3)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3

指定排序方向

ORDER BY默认采用升序排序ASC(从 A 到 Z), 所以降序需要使用DESC关键字, 且只作用于就近的前面的一个字段

-- 此处只做作用于prod_price降序, prod_name还是升序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name

过滤

过滤数据语法

WHERE: 使用WHERE子句进行数据过滤, WHERE子句在表名(FROM子句)之后给出, 例如

SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price = 3.49

WHERE 操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两值之间
IS NULL	为 NULL 值

WHERE 字段引号

如果 WHERE 后面的字段是字符串需要加上引号, 数字不需要

SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price != 'DLL01'

范围值检查

使用BETWEEN [开始] AND [结束]子句

-- 指定价格从5-10之间的数据
SELECT prod_price, prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;

空值检查

使用IS NULL子句, 0, 空字符串, 空格不属于空值

-- 返回所有没有价格字段的数据
SELECT prod_name FROM products WHERE prod_price IS NULL;

高级过滤

如果要实现更强的数据过滤, 那么就需要组合多个WHERE子句, 使用AND 或者 OR子句来组合

AND 操作符

需要满足所有给定的条件

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR 操作符

只需要满足任一条件

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

混用AND 和 OR

由于AND优先级高于OR, 所以如果混用, 需要使用括号来确定正确顺序

SELECT prod_id, prod_price, prod_name FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

IN 操作符

用来指定条件范围, 每个条件都可以进行匹配, 类似于OR, 性能更好

SELECT prod_id, prod_price, prod_name FROM Products WHERE  vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

NOT 操作符

用于否定其后所跟的任何条件, 等同于除...之外

SELECT prod_id, prod_price, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

通配符过滤

当需要匹配未知的值的时候, 上面的几种方式都不好用了, 此处需要使用LIKE操作符

LIKE 操作符

用来匹配值的一部分的特殊字符

• 百分号通配符(%) : 表示任何字符出现任意次, 不能匹配 NULL; F%y 表示以 F 开头,y 结尾;

-- 匹配所有prod_name以Fish开头的产品, %之后是任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

-- 使用多个通配符, %bean  fish%表示之前和之后可以是任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean  fish%';

• 下划线通配符(_): 只能匹配单个字符, 不能匹配多个字符

-- 此处2个下划线, 匹配2个任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear;';

通配符技巧

1. 不要过度使用通配符(性能问题)
2. 尽量不要把通配符用在搜索模式的开始处(开始处最慢)
3. 注意通配符所处位置

计算字段

数据库中的数据可能不是应用程序所需要的格式, 因此需要做一些处理:

• 不同表列合并
• 格式化字段
• 列大小写混合处理
• 列计算

拼接(Concatenate)

将多个列中的值联结在一起(将一个值附加到另一个值), 构成单个值

在很多 DBMS 中, 都会使用空格来填充多余的长度, 因此我们一般需要去掉空格, mysql 中可以使用函数来实现

• RTRIM(): 去掉右侧空格
• LTRIM(): 去掉左侧空格
• TRIM(): 去掉左右两边空格

-- mysql中使用Concat函数
SELECT Concat(vend_name, ' (', vend_country, ')')

使用别名

在上述拼接的结果中, 为了能够引用到拼接的值, 因此需要使用别名来实现

实际使用中, 当列明包含不合法的字符时, 可以用来重新命名它

SELECT  Concat(TRIM(vend_name), '(', TRIM(vend_country), ')') AS vend_title FROM Vendors ORDER BY vend_name;

执行算术计算

SQL 支持以下的基本算术操作符, 圆括号可用来区分优先顺序

操作符	说明
+	加
-	加
*	乘
÷	除

-- 汇总物品的价格

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

计算测试

一般使用SELECT语句来查看测试、检验函数、计算结果

SELECT 3 * 2;
SELECT TRIM( ' ABC ' )
SELECT Curdate()

函数

DBMS(数据库管理软件)都支持函数, 但是每个 DBMS 实现相同功能的函数并不相同, 所以函数是具有兼容性问题的, 因此是否使用函数, 应明确考虑 SQL 的可移植性

根据函数的功能, 可以分为以下几类:

• 处理文本
• 算术操作
• 处理日期
• 格式化函数
• 返回特殊信息的系统函数

处理文本

常用文本处理函数如下

函数	说明
LEFT()	返回字符串左边字符
LENGTH()	返回字符串长度
LOWER()	字符串转化为小写
LTRIM()	去掉字符串左侧空格
RIGHT()	返回字符串右侧字符
RTRIM()	去掉字符串右侧空格
SUBSTR() / SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的 SOUNDEX 值(类似发音字符和音节)
UPPER()	字符串转化为大写

例如: 当发音相同的两个字段, 在数据库中拼写错误了, 因此通过字符搜索将无法查到

-- 数据库中存储的错误字段名 Michelle Green

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

日期和时间处理

在 DBMS 中, 时间和日期函数的可移植性非常差, SQL Server中用于返回日期的某一部分的函数DATEPART()在Oracle并不支持

数值处理

常用的数值处理函数如下

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

汇总数据

我们经常汇总数据, 而不用把他们实际检索出来, 汇总数据一般使用聚集函数, 在各种 DBMS 的中实现也比较一致

聚集函数

• 确定表列数
• 获得表中列的和
• 找出表列

常用聚集函数如下

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和(会忽略值为 NULL 的行)

MAX()允许用于任意列, 对于文本数据, 用于返回该列排序后的最后一行

MIN()允许用于任意列, 对于文本数据, 用于返回该列排序后的第一行

-- 求列平均值
SELECT AVG(prod_price) AS avg_price FROM Products;

-- 求所有列的行数
SELECT COUNT(*) AS num_cust FROM Customers;

-- 求具体列的行数
SELECT COUNT(cust_email) AS num_cust FROM Customers;

-- 返回指定列最大值
SELECT MAX(prod_price) AS max_price FROM Products;

-- 返回指定列最小值
SELECT MIN(prod_price) AS min_price FROM Products;

-- 返回指定列值之和
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;

聚集不同值

上一小节的 5 个聚集函数, 还可以指定参数

• ALL: 对所有行执行计算(缺省默认值)

• DISTINCT: 只包含不同值, 必须使用列名, 不能用于 COUNT(*)

-- 排除了相同值后来进行平均值计算
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

组合聚集函数

SELECT语句可根据需要包含多个聚集函数

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;

分组数据

使用分组可以将数据分为多个逻辑组, 对每个组进行聚集计算

创建分组语法

GROUP BY: 使用GROUP BY进行分组, 该子句返回结果会先进行分组, 然后对每个组进行结果聚集计算, 而不是整个表

-- 按vend_id排序并分组数据, 征对每个vend_id计算一次num_prods
SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id

输出结果如下:

vend_id	num_prods
BRS01	3
DLL01	4
FNG01	2

过滤分组语法

HAVING: 规定包含哪些分组, 排除哪些分组, 搭配GROUP BY使用; 并且类似于WHERE, 语法使用一致, 都可以用HAVING替代, 但是WHERE用于过滤行, HAVING用于过滤分组

• WHERE 在分组前过滤, HAVING 在数据分组后进行过滤

• WHERE 排除的行不包括在分组中, 从而影响 HAVING 子句中过滤掉的分组

-- 先通过cust_id进行分组, 再计算每个分组的数量, 最后过滤COUNT(*) >= 2(两个以上订单)的分组
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

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

分组排序

一般而言, GROUP BY分组的数据以分组顺序输出, 但DBMS实现可能不一致, 所以保证正确排序, 最好搭配使用ORDER BY, 不要仅依赖GROUP BY排序数据

-- 默认按照了 order_num 排序
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;

-- 如果需要按照数目排序, 需要加上ORDER BY
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3  ORDER BY items, order_num;

输出结果: 先按照items数量排序完成, 在相同数量里面又按照order_num升序排列

order_num	items
2006	3
2009	3
2007	5
2008	5

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的标	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出顺序排序	否

子查询

SQL 允许创建子查询, 嵌套在其他查询中的查询

子查询进行过滤

子查询只能查询单列

假如有三张表, 需要查出订购物品RGAN01的所有顾客

• Customers 表: 客户 ID、顾客信息
• Orders 表: 订单编号、客户 ID、订单日期
• OrderItems 表: 订单编号、物品 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'

计算字段使用子查询

完全限定列名: 排除出现冲突列名导致的歧义性, 使用表.列名来避免歧义

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

联结表

联结的作用

将数据分解为多个表能更有效的存储, 更方便地处理, 并且伸缩性更好, 既然数据存储在多个表中, 所以需要使用联结来关联表

联结的创建

联结两个表时, 实际是将第一个表中的每一行与第二个表中的每一行配对, 因此一般需要使用WHERE子句作为过滤条件

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
Where Vendors.vend_id = Products.vend_id

内联结分类

基于两个表之间的相等测试, 这种联结称为等值联结, 也叫内联结

语法可以使用如下方式:

• 简单等值语法

-- 上面的例子
SELECT [字段] FROM [表一], [联结的表二], [联结的表三]... WHERE [条件]

• INNER JOIN 语法

SELECT [字段] FROM [表一] INNER JOIN [联结的表二] INNER JOIN [联结的表三] ON [条件]

-- 使用联结解决上面的 "子查询过滤" 问题
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
  AND Orders.order_num = OrderItems.order_num
  AND OrderItems.prod_id = 'RGAN01'

高级联结

表别名

使用AS关键词给表取别名, 表别名可以缩短 SQL 语句, 允许在一条SELECT语句中多次使用相同的表

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND O.order_num = OI.order_num
  AND OI.prod_id = 'RGAN01'

注意

Oracle 不支持 AS 关键字

联结主要分为以下几类:

• 内联结(inner join)
• 自联结(self join)
• 自然联结(natural join)
• 外联结(outer join)

上文已讲述了内联结使用, 此处不再赘述

自联结

SELECT C1.cust_id, C1.cust_name, C1.cust_contact
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_name = C2.cust_name
  AND C2.cust_contact = 'Jim Jones'