PuffMeow | PuffMeow

简述

这本书讲的有点抽象，而且翻译不太好。本文只会记录学习中有所收获的地方

去重

DISTINCT 它指示数据库只返回不同的值，可以达到去重的目的。如果使用 DISTINCT 关键字，它必须直接放在列名的前面

不能部分使用 DISTINCT。DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列

分页

LIMIT 和 OFFSET 一般用于做数据分页，但是在数据量大的时候会有性能问题。建议在需要分页的表中使用自动递增的主键，然后使用 id 查询来代替 LIMIT/OFFSET 来做分页

sql

SELECT * FROM table_name WHERE id > 10 LIMIT 10;

LIMIT 指定返回的行数。LIMIT 带的 OFFSET 指定从哪儿开始

假如 Products 表中只有 9 种产品， LIMIT 5 OFFSET 5 只返回 4 行数据

LIMIT x, y 分句表示: 跳过 x 条数据，读取 y 条数据

第一个被检索的行是第 0 行，而不是第 1 行

MySQL、MariaDB 和 SQLite 可以把 LIMIT 4 OFFSET 3 语句简化为 LIMIT 3,4。使用这个语法，逗号之前的值对应 > OFFSET，逗号之后的值对应 LIMIT（反着的，要小心）。

排序

ORDER BY 可以根据某个/多个字段来进行排序

sql

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

对 prod_name 列以字母顺序排序

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句，就是要放到语句的最后

升序降序排序

添加 DESC(DESCENDING) 关键字。ASC 是默认的升序（A-Z 排序）

sql

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字

过滤

使用 WHERE 关键字 + 条件

sql

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误

条件操作符就是 >、 =、 < 、 >= 、 <=、 !<、!= 或 <>、!>、BETWEEN xx AND xx、 IS NULL 这些

AND 、OR、IN 、NOT 运算符

AND 同时满足

sql

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

OR 其中一个满足

sql

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

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配，可以完成 OR 的功能(IN 操作符一般比一组 OR 操作符执行得更快)

sql

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

和 != 功能一样，简单的子句中看不出它的啥优势，但在更复杂的子句中，NOT 是非常有用的。例如，在与 IN 操作符联合使用时，NOT 可以非常简单地找出与条件列表不匹配的行

LIKE 模糊匹配

%百分号通配符

%代表搜索模式中给定位置的 0 个、1 个或多个字符

比如找出所有以词 Fish 起头的产品

sql

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符

搜索模式'%bean bag%'表示匹配任何位置上包含文本 bean bag 的值

%不匹配 NULL，比如子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

下划线通配符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少

使用技巧

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

计算字段

在 SQL 语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。所以在数据库中的计算就很有用

拼接

SQL Server 使用+号。DB2、Oracle、PostgreSQL 和 SQLite 使用||

sql

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

MySQL 或 MariaDB，使用 Concat 函数

sql

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

可以输出比如： test (China) 这样的字段

去除空格

RTRIM() 函数可以去掉值右边的所有空格，LTRIM() 去掉字符串左边的空格以及 TRIM() 去掉字符串左右两边的空格。

别名

SELECT 语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值，为了解决这个问题，SQL 支持列别名

sql

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

函数

函数	语法
提取字符串的组成部分	DB2、Oracle、PostgreSQL 和 SQLite 使用 SUBSTR()；MariaDB、 MySQL 和 SQL Server 使用 SUBSTRING()
数据类型转换	Oracle 使用多个函数，每种类型的转换有一个函数；DB2 和 PostgreSQL 使用 CAST()；MariaDB、MySQL 和 SQL Server 使用 CONVERT()
取当前日期	DB2 和 PostgreSQL 使用 CURRENT_DATE；MariaDB 和 MySQL 使用 CURDATE()；Oracle 使用 SYSDATE；SQL Server 使用 GETDATE()； SQLite 使用 DATE()

常用的一些函数和说明

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
LENGTH()（也使用 DATALENGTH()或 LEN()）	返回字符串的长度
LOWER()	将字符串转换为小写
SUBSTR()或 SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的 SOUNDEX 值。SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法，读音类似的字符串也会被返回
UPPER()	将字符串转换为大写

日期

DB2，MySQL 和 MariaDB 用户可使用名为 YEAR()的函数从日期中提取年份

sql

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

数值处理

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

数据汇总(聚集函数)

与前一章介绍的数据处理函数不同，SQL 的聚集函数在各种主要 SQL 实现中得到了相当一致的支持

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

AVG

sql

SELECT AVG(prod_price) AS avg_price
FROM Products;

avg_price
-------------
6.823333

AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG()函数
AVG()函数忽略列值为 NULL 的行。

COUNT

COUNT()函数有两种使用方式：

使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。

MAX

虽然 MAX()一般用来找出最大的数值或日期值，但许多（并非所有） DBMS 允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。

MAX()函数忽略列值为 NULL 的行

MIN

虽然 MIN()一般用来找出最小的数值或日期值，但许多（并非所有） DBMS 允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MIN()返回该列排序后最前面的行。

MIN()函数忽略列值为 NULL 的行

SUM

SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的 item_price*quantity，得出总的订单金额：

sql

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

total_price
----------
1648.0000

聚集不同的值

下面的例子使用 AVG()函数返回特定供应商提供的产品的平均价格。它与上面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格：

sql

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

avg_price
-----------
4.2400

如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表达式

sql

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;

num_items price_min price_max price_avg
---------- --------------- --------------- ---------
9 3.4900 11.9900 6.823333

分组数据

这一课介绍如何分组数据，以便汇总表内容的子集。这涉及两个新 SELECT 语句子句：GROUP BY 子句和 HAVING 子句

创建分组

sql

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

vend_id num_prods
------- ---------
BRS01 3
DLL01 4
FNG01 2

上面的 SELECT 语句指定了两个列：vend_id 包含产品供应商的 ID， num_prods 为计算字段（用 COUNT(*)函数建立）。GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次

过滤分组

目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是，WHERE 过滤行，而 HAVING 过滤分组

之前学习了 WHERE 子句的条件（包括通配符条件和带多个操作符的子句）。学过的这些有关 WHERE 的所有技术和选项都适用于 HAVING。它们的句法是相同的，只是关键字有差别

过滤 COUNT(*) >= 2（两个以上订单）的那些分组

sql

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

cust_id    orders
---------- -----------
1000000001 2

另一种理解方法，WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤

WHERE 子句过滤所有 prod_price 至少为 4 的行，然后按 vend_id 分组数据，HAVING 子句过滤计数为 2 或 2 以上的分组

sql

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

vend_id num_prods
------- -----------
BRS01 	3
FNG01 	2

分组和排序

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

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据

子查询

在 SELECT 语句中，子查询总是从内向外处理，即从最内部嵌套的子句开始执行

订单物品存储在 OrderItems 表，订单是 Order 表，客户信息是 Customers 表，现在要查出列出订购物品 RGAN01 的所有顾客的信息

sql

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

cust_name             cust_contact
------------          --------------------
Fun4All Denise        L. Stephens
The Toy Store         Kim Howard

作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。上面的 SQL 还可以进行优化

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID 存储在 Orders 表中

sql

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

cust_name           cust_state   orders
------------------- ----------   ------
Fun4All             IN           1
Fun4All             AZ           1
Kids Place          OH           0
The Toy Store       IL           1
Village Toys        MI           2

Orders.cust_id = Customers.cust_id 是完全限定列名，为了防止产生字段歧义的。

连接查询

如果数据存储在多个表中，怎样用一条 SELECT 语句就检索出数据呢？答案是使用连接

创建连接

sql

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

内连接

目前为止使用的连接称为等值连接，这种连接也称为内连接（inner join），下面的 SELECT 语句返回与前面例子完全相同的数据：

sql

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

👆 更推荐用这种写法

连接多个表

sql

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;

再回顾一下上面子查询的写法，看看如何优化

sql

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

连接是 SQL 中一个最重要、最强大的特性，有效地使用连接需要对关系数据库设计有基本的了解

高级连接

自连接 self join：自连接通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理连接远比处理子查询快得多。
自然连接 natural join：每个内连接都是自然连接，无论何时对表进行连接，应该至少有一列不止出现在一个表中
外连接 outer join：可以返回一个表和另一个表没有关联行的那些行
全外连接（full outer join）：它检索两个表中的所有行并关联那些可以关联的行。与左外连接或右外连接包含一个表的不关联的行不同，全外连接包含两个表的不关联的行（MariaDB、MySQL 和 SQLite 不支持）

调整 FROM 或 WHERE 子句中表的顺序，左外连接可以转换为右外连接

带聚集函数的连接

要检索所有顾客及每个顾客所下的订单数

sql

SELECT Customers.cust_id,
 COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
cust_id num_ord
---------- --------
1000000001 2
1000000003 1
1000000004 1
1000000005 1

左外连接，包含那些没有任何订单的顾客

sql

SELECT Customers.cust_id,
 COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
cust_id num_ord
---------- -------
1000000001 2
1000000002 0
1000000003 1
1000000004 1
1000000005 1

组合查询

SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回

sql

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

UNION 会从查询结果集中自动去除重复的行，如果不想去除，则使用 UNION ALL

规则

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合四条 SELECT 语句，将要使用三个 UNION 关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询

为什么使用视图？

重用 SQL 语句
简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节
使用表的一部分而不是整个表
保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害

创建视图

sql

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

这条语句创建一个名为 ProductCustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行 SELECT * FROM ProductCustomers，将列出订购了任意产品的顾客

删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname;。覆盖（或更新）视图，必须先删除它，然后再重新创建。

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

事务

通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性

概念：

事务（transaction）指一组 SQL 语句；
回退（rollback）指撤销指定 SQL 语句的过程；
提交（commit）指将未存储的 SQL 语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

MySQL 和 MariaDB 中

sql

START TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。比如

放置占位符

sql

SAVEPOINT delete1;

回退到占位符

sql

ROLLBACK TO delete1;

完整例子

sql

START TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES(1000000010, 'Toys Emporium');
SAVEPOINT StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1',1000000010);
IF @@ERROR <> 0 ROLLBACK TO StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TO StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TO StartOrder;
COMMIT TRANSACTION

简述 ​

去重 ​

分页 ​

排序 ​

升序降序排序 ​

过滤 ​

AND 、OR、IN 、NOT 运算符 ​

LIKE 模糊匹配 ​

%百分号通配符 ​

下划线通配符 ​

使用技巧 ​

计算字段 ​

拼接 ​

去除空格 ​

别名 ​

函数 ​

日期 ​

数值处理 ​

数据汇总(聚集函数) ​

AVG ​

COUNT ​

MAX ​

MIN ​

SUM ​

聚集不同的值 ​

分组数据 ​

创建分组 ​

过滤分组 ​

分组和排序 ​

子查询 ​

作为计算字段使用子查询 ​

连接查询 ​

创建连接 ​

内连接 ​

连接多个表 ​

高级连接 ​

带聚集函数的连接 ​

组合查询 ​

规则 ​

视图 ​

创建视图 ​

事务 ​

简述

去重

分页

排序