SQL 必知必会第 1~10 课

发表于 2018-12-27 更新于 2024-08-05 分类于 SQL 阅读次数：

CH1	基本概念	CH6
CH2	检索数据	CH7
CH3	过滤数据	CH8
CH4	创建拼接字段	CH9
CH5	使用函数	CH10

基本概念的区分

数据库和数据库管理系统

人们通常用数据库这个术语来代表他们使用的数据库软件，这是不正确的，也因此产生了许多混淆。确切地说，数据库软件应称为数据库管理系统（ DBMS）。数据库是通过 DBMS 创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。

模式

表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。描述表的这组信息就是所谓的模式（ schema），模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。

简而言之，模式就是关于数据库和表的布局及特性的信息。

主键（Primary Key）

主键的要求：

任意两行都不具有相同的主键值。
主键不允许为空。
主键列中的值不允许修改或更新。
主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

主键可以定义在表的一列上，也可以一起使用多个列合在一起作为主键。使用多个列作为主键时，只要保证不同记录的主键不完全相同即可，可以有部分值相同。

检索数据

SELECT语句

检索单个列

1 2	SELECT prod_name FROM Products;

SQL语句的分句

必须以分号 ； 分隔。对于单条SQL语句，有些DBMS允许不加分号，但是有些不允许，建议总是加上分号为好。

SQL不区分大小写

SQL不区分大小写，所以 SELECT 和 select 和 Select 效果是一样的。

SQL语句本身不区分大小写，但是数据库名和表名是可能区分大小写的

因为SQL本身只是一个语言，是一个标准，所以SQL本身不区分大小写，但是数据库名和表名是有可能区分大小写的，不同的数据库厂商的设置不一样。

SQL会自动忽略多余的空格

所以以下三种写法是一样的：

SELECT prod_name
FROM Products;

SELECT prod_name FROM Products;

SELECT
prod_name
FROM
Products;

合理分行可以改善程序的可读性。

检索多个列

示例：

1 2	SELECT prod_id, prod_name, prod_price FROM Products;

选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加，如果最后一个列名后加了逗号将出现错误。

检索所有列

示例：

1 2	SELECT * FROM Products;

在实际的应用中，如果不是必须，不要检索所有列，因为这样会降低程序的检索效率。

检索所有列的另外一个用处是，可以检索出你不知道名字的列。

检索结果去重

示例：

1 2	SELECT DISTINCT vend_id FROM Products;

这样，当检索结果中有完全相同的两行的时候，就会去掉重复的。

此原则也适用于同时选择多列。

示例：

1 2	SELECT DISTINCT prod_id, vend_id， prod_price FROM Products;

也就是说 DINSTINCT 关键字同时作用于三行。

检索结果中只选取部分行

不同的数据库管理系统的实现是不一样的。

适用于MySQL、MariaDB、PostgreSQL或者SQLite写法

-- 选取不超过5行的数据
SELECT prod_name
FROM Products
LIMIT 5;

可以使用 OFFSET 指定起始位置。需要注意的是，数据库中的行是从第0行开始的。

-- 返回从第3行起的4行数据
SELECT prod_name
FROM Products
LIMIT 4 OFFSET 3;

在MySQL和MariaDB中，LIMIT 4 OFFSET 3 语句可以进行简化，写成 LIMIT 3,4。需要注意的是逗号前边的对应的是 OFFSET，逗号后边的数对应于 LIMIT。

适用于Oracle的写法

-- 选取前5行
SELECT prod_name
FROM Products
WHERE ROWNUM <= 5;

注释

三种注释方式

-- 注释方式1

/* 注释方式2
   注释方式2 */

# 注释方式3 可能有些产品不支持

排序检索数据

使用 ORDER BY 子句进行排序。默认采用升序排列。

示例：

1
2
3

SELECT prod_name
FROM Products
ORDER BY prod_name;

关于MySQL的 ORDER BY 子句使用方法的官方文档：

MySQL :: MySQL 8.0 Reference Manual :: 3.3.4.4 Sorting Rows

另外，ORDER BY 子句必须是一条语句的最后一条子句，如果它不是最后的子句，将会出现错误信息。
可以通过没有选择的列进行排序

一般情况下我们都是根据 SELECT 选择的列中的列进行排序，但是也可以使用没有选择的列进行排序。

多级排序

可以对两个列进行排序。

示例：

1
2
3

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

先对 prod_price 进行排序，如果 prod_price 中出现了两个相同的值的话，就会通过 pro_name 进行排序。如果 prod_price 中的值都是唯一的，那么就不会按 prod_name 排序。

在 `ORDER BY` 中使用列的相对位置进行排序

示例：

1
2
3

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

这表示依次使用 prod_price 和 prod_name 进行排序。

虽然这种方式可行，但是不建议使用这种方式。因为，如果对SELECT中选择的列进行了修改而忘记了对ORDER BY 子句进行修改的话，那么可能出错。

降序排列

在 ORDER BY 子句中某列名后面加上 DESC，表示针对此列进行降序排列。也就是说，DESC 只作用于它前边紧邻的一个列名。

示例：

1
2
3

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

升序排列和降序排列具体是怎么排的呢？

在对文本性数据进行排序时， A 与 a 相同吗？ a 位于 B 之前，还是 Z之后？这些问题不是理论问题，其答案取决于数据库的设置方式。

在字典（ dictionary）排序顺序中， A 被视为与 a 相同，这是大多数数据库管理系统的默认行为。但是，许多 DBMS 允许数据库管理员在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。

这里的关键问题是，如果确实需要改变这种排序顺序，用简单的 ORDERBY 子句可能做不到。你必须请求数据库管理员的帮助。

自定义排序规则

可以在 GROUP BY 中自定义排序的规则：

示例：

SELECT 柜台号, 季度, 销售额, 同期销售额
FROM 柜台sale
ORDER BY (
    CASE 季度
    WHEN '第一季度' THEN 1
    WHEN '第二季度' THEN 2
    WHEN '第三季度' THEN 3
    WHEN '第四季度' THEN 4
    END
) ASC;

过滤数据

使用 `WHERE` 子句过滤数据

示例：

1
2
3

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

这条语句从 products 表中检索两个列，但不返回所有行，只返回 prod_price 值为 3.49 的行。

SQL过滤与应用过滤

数据也可以在应用层过滤，就是说SQL检索出超过实际需要的数据，发送给客户端，然后由客户端程序再进行筛选。这样做是可行的，但是有几个缺点：

浪费带宽
创建的应用可能不具备可伸缩性
客户端性能问题

`WHERE` 子句的操作符

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

其中有些的效果是一样的，比如 <> 和 !=，!< 和 >=，但是不同的数据库厂商的设置不一定相同，需要参阅相关的文档。比如 Microsoft Access 支持 <> 而不支持 !=。

对比数值和对比字符串的区别

示例：

1
2
3

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

可以看到，WHERE子句中的条件括在了单引号中。单引号用来限定字符串。如果将将值与字符串类型的列进行比较，就需要限定引号。如果用来与数值进行比较的值不用引号。

`BETWEEN` 运算符的使用

示例：

1
2
3

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

空值检查

确定值是否为 NULL，不能简单地检查是否= NULL。 SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。这个 WHERE 子句就是 IS NULL 子句。

示例：

1
2
3

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

不同DBMS特有的操作符

许多 DBMS 扩展了标准的操作符集，提供了更高级的过滤选择。更多信息请参阅相应的 DBMS 文档。

NULL 和非匹配

当你使用 != 运算符过滤不包含指定值的所有行时，你可能希望值为NULL的行也显示出来，但是实际上值为NULL的行并不会显示出来，因为 unknown 有特殊的含义，数据库不知道它们是否匹配。

高级数据过滤

前边的 WHERE 子句都只有一个条件，可以使用 AND 操作符在一个 WHERE 子句中使用多个条件。

`AND` 操作符

使用 AND 操作度之后，会筛选出同时满足所有条件的数据。

示例：

1
2
3

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

可以有多个过滤条件，如果有多个过滤条件，每两个条件之间都要加上 AND 关键字。

`OR` 操作符

使用 OR 操作符之后，一条数据只要满足任意一个条件就会被过滤出来。

示例：

1
2
3

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

同样，使用 OR 操作符也可以有多个过滤条件。

操作符优先级

考虑以下代码：

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

AND 操作符在求值中优先级更高，所以这段代码表示过滤出“ven_id 为 DLL01 的数据，或者 ven_id 为 BRS01 且 prod_price 大于等于 10 的数据。”

如果想要表达的是下面这个意思：

过滤出 ven_id 为 DLL01 或者 BRS01，而且 prod_price 都在 10 以上的数据。

那么就要把代码改成这样：

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

`IN` 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

示例：

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

其实 IN 操作符和 OR 操作符可以执行相同的功能。不过 IN 操作符有以下几个优点：

相比于使用 OR 操作符连接多个条件，IN 操作符的语句更清晰简洁
IN 操作符一般比使用多个 OR 操作符执行得更快
IN 后面可以包含其他 SELECT 语句，能够更动态地建立 WHERE 子句。

`NOT` 操作符

NOT 操作符一般放在条件的前边，用来否定后面的条件。

这段代码

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

和这段代码

SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

表达同样的意思。

补充说明：MariaDB 中的 NOT

MariaDB 允许使用 NOT 子句否定 IN、BETWEEN、EXISTS 子句。大多数DBMS允许使用 NOT 否定任何条件。

使用通配符进行过滤

`LIKE` 操作符

使用 LIKE 操作符来使用通配符（wildcard）进行搜索。用来进行匹配的字符串叫做 模式串。

百分号 `%` 通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。任意次数就意味着可以出现0次，1次或者多次。

这段代码

1
2
3

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

表示找出所有 prod_name 以 Fish 为开头的数据。

通配符可以放在模式串的任意位置。

例如，模式串 %bean bag% 表示匹配任何位置上包含文本 bean bag 的值，不论它之前或者之后出现什么字符串。

需要注意的是，%不能匹配NULL，即 WHERE prod_name LIKE '%' 不会匹配产品名称为 NULL 的行。

下划线 `_` 通配符

_ 通配符匹配任意单个字符。

方括号 `[]` 通配符

方括号用来指定一个字符的集合，匹配这个这个集合中的任意一个字符。例如 LIKE [JM]%' 表示匹配以 J 或 M 开头的数据。

可以在[] 中开头加上 ^ 表示匹配除了这个集合中的字符以外的任意字符。例如 LIKE '[^JM]%' 表示匹配不以 J 或者 M 开头的任意字符。这种功能也可以换一种实现方式，使用 NOT，即 WHERE NOT xxx LIKE '[JM]%'。

使用通配符的技巧

通配符有时候可以很方便，但是这种方便是有代价的，使用通配符进行搜索一般前面介绍的其他搜索要耗费更长的处理时间。最好遵守以下原则：

不要过度使用通配符，如果使用其他操作符能够达到目的，就不要使用通配符
确实需要使用通配符时，尽量不要把它们用在模式串的开头。把通配符放到模式串的开头，搜索起来是最慢的。

创建计算字段

拼接字

SQL Server和Access的写法

1
2
3

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

DB2、Oracle、PostgreSQL、SQLite、Open Office Base的写法

1
2
3

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

MySQL和MariaDB的写法

1
2
3

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

输出结果

Bear Emporium                                (USA        ) 
Bears R Us                                   (USA        ) 
Doll House Inc.                              (USA        ) 
Fun and Games                                (England    ) 
Furball Inc.                                 (USA        ) 
Jouets et ours                               (France     )

去掉字段中多余的空格

输入

1
2
3

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

或

1
2
3

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

输出结果

Bear Emporium (USA) 
Bears R Us (USA) 
Doll House Inc. (USA) 
Fun and Games (England) 
Furball Inc. (USA) 
Jouets et ours (France)

说明：TRIM 函数
大多数 DBMS 都支持 RTRIM() （正如刚才所见，它去掉字符串右边的空格）、 LTRIM() （去掉字符串左边的空格）以及 TRIM() （去掉字符串左右两边的空格）。

别名

使用 AS 关键字为筛选出来的数据赋予一个别名。

1
2
3

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

在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。

别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。

别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

执行算术计算

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

输出：

prod_id        quantity        item_price       expanded_price 
----------     -----------     ------------     ----------------- 
RGAN01         5               4.9900           24.9500 
BR03           5               11.9900          59.9500 
BNBG01         10              3.4900           34.9000 
BNBG02         10              3.4900           34.9000 
BNBG03         10              3.4900           34.9000

其中，expanded_price 为一个计算字段，也就是 quantity*item_price。

SQL支持在 SELECT 语句中直接使用算术运算符 + 、-、*、/。

SELECT 语句为测试、检验函数和计算提供了很好的方法。虽然 SELECT通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和处理表达式，例如 SELECT 3 * 2; 将返回 6， SELECT Trim(‘ abc ‘);将返回 abc ， SELECT Now(); 使用 Now() 函数返回当前日期和时间。现在你明白了，可以根据需要使用 SELECT 语句进行检验。

使用函数处理数据

不同的DBMS中的函数是不同的，但是有一些基本的共同的函数。

函数不仅仅可以在SELECT子句中，还可以出现在WHERE等子句中。

为了代码的可移植，许多 SQL 程序员不赞成使用特定于实现的功能。虽然这样做很有好处，但有的时候并不利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS可以非常有效完成的工作。

如果你要是用函数，记得做好注释，以便之后的修改。

文本处理函数

`UPPER` 函数

输入：

1
2
3

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

输出：

vend_name                       vend_name_upcase 
---------------------------     ---------------------------- 
Bear Emporium                   BEAR EMPORIUM 
Bears R Us                      BEARS R US 
Doll House Inc.                 DOLL HOUSE INC. 
Fun and Games                   FUN AND GAMES

`SOUNDEX()` 函数

输入：

1
2
3

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

输出：

1
2
3

cust_name                      cust_contact 
--------------------------     ---------------------------- 
Kids Place                     Michelle Green

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念，但多数 DBMS 都提供对 SOUNDEX的支持。

聚集函数

分组 GROUP BY

使用 GROUP BY 子句创建分组。

示例

输入：

1
2
3

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

输出：

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

聚集函数针对分组作用，而不是针对所有的行作用。

一些注意事项：

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。
除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

加深理解

使用 GROUP BY 之后，同时使用 COUNT() 函数的话，COUNT() 函数是针对分组来起作用的的，其他一些函数也类似。

过滤分组

使用 HAVING 子句对分组进行过滤。

`HAVING` 和 `WHERE` 的区别

WHERE 也用于数据的过滤，但是 WHERE 是基于行进行过滤的。

HAVING 不仅可以基于行进行过滤，还可以基于分组进行过滤。但是 HAVING 一般只用于过滤分组。
WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。这是一个重要的区别， WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

示例

输入：

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

输出：

1
2
3

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

分组和排序

提示：不要忘记 ORDER BY

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

`SELECT` 子句的顺序

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

基本概念的区分

检索数据

SELECT语句

检索单个列

检索多个列

检索所有列

检索结果去重

检索结果中只选取部分行

注释

排序检索数据

多级排序

在 ORDER BY 中使用列的相对位置进行排序

降序排列

升序排列和降序排列具体是怎么排的呢？

自定义排序规则

过滤数据

使用 WHERE 子句过滤数据

SQL过滤与应用过滤

WHERE 子句的操作符

对比数值和对比字符串的区别

BETWEEN 运算符的使用

空值检查

不同DBMS特有的操作符

NULL 和非匹配

高级数据过滤

AND 操作符

OR 操作符

操作符优先级

IN 操作符

NOT 操作符

使用通配符进行过滤

LIKE 操作符

百分号 % 通配符

下划线 _ 通配符

方括号 [] 通配符

使用通配符的技巧

创建计算字段

拼接字

SQL Server和Access的写法

DB2、Oracle、PostgreSQL、SQLite、Open Office Base的写法

MySQL和MariaDB的写法

输出结果

去掉字段中多余的空格

输入

输出结果

别名

执行算术计算

使用函数处理数据

文本处理函数

UPPER 函数

SOUNDEX() 函数

聚集函数

分组 GROUP BY

示例

加深理解

过滤分组

HAVING 和 WHERE 的区别

示例

分组和排序

SELECT 子句的顺序

在 `ORDER BY` 中使用列的相对位置进行排序

使用 `WHERE` 子句过滤数据

`WHERE` 子句的操作符

`BETWEEN` 运算符的使用

`AND` 操作符

`OR` 操作符

`IN` 操作符

`NOT` 操作符

`LIKE` 操作符

百分号 `%` 通配符

下划线 `_` 通配符

方括号 `[]` 通配符

`UPPER` 函数

`SOUNDEX()` 函数

`HAVING` 和 `WHERE` 的区别

`SELECT` 子句的顺序