一、SQL语句总结（查询语句）_sql语句表示至少包含所有内容

1 SQL语句的分类

本文总结自《SQL必知必会》、《SQL基础教程》

SQL命令一般分为DQL、DML、DDL几类。

• DQL：数据查询语句，基本就是SELECT查询命令，用于数据查询。
• DML：Data Manipulation Language的简称，即数据操纵语言，主要用于插入、更新、删除数据，所以也分为INSERT、UPDATE、DELETE三种语句。
• DDL：Data Definition Language的简称，即数据定义语言，主要用于创建、删除，以及修改表、索引等数据库对象语言。

2 检索数据

2.1 SELECT语句

使用SELECT检索表数据，至少要给出两组信息

1. 想选择什么
2. 从什么地方

2.2 检索单个列

--输入
SELECT prod_name 
FROM products;
--结果
      prod_name      
---------------------
 8 inch teddy bear
 12 inch teddy bear
 18 inch teddy bear
 Fish bean bag toy
 Bird bean bag toy
 Rabbit bean bag toy
 Raggedy Ann
 King doll
 Queen doll
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

上述语句利用SELECT语句从products表中检索一个名为prod_name的列。SELECT指出需要检索的列， FROM指出从哪个表中检。

该SELECT语句包含了SELECT和FROM两个子句。子句是SQL语句的组成要素。本例中两个自句就是以SELECT或者FROM等作为起始的短语。

2.3 检索多个列

在SELECT后给出多个列名，列名之间以逗号分隔

--输入
SELECT prod_id, prod_name, prod_price 
FROM products;
--结果 
 prod_id |      prod_name      | prod_price 
---------+---------------------+------------
 BR01    | 8 inch teddy bear   |       5.99
 BR02    | 12 inch teddy bear  |       8.99
 BR03    | 18 inch teddy bear  |      11.99
 BNBG01  | Fish bean bag toy   |       3.49
 BNBG02  | Bird bean bag toy   |       3.49
 BNBG03  | Rabbit bean bag toy |       3.49
 RGAN01  | Raggedy Ann         |       4.99
 RYL01   | King doll           |       9.49
 RYL02   | Queen doll          |       9.49
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.4 检索所有列

在列名位置使用通配符 星号（*）。但是使用星号，就无法设定列的显示顺序，会按CREATE TABLE语句的定义对列进行排序。

--输入
SELECT * 
FROM products;
--结果
 prod_id | vend_id |      prod_name      | prod_price |                               prod_desc                               
---------+---------+---------------------+------------+-----------------------------------------------------------------------
 BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket
 BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket
 BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket
 BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it
 BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included
 BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots
 RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll
 RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown
 RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

注意：除非确实需要表中的每一列，否则最好不要使用*****通配符。虽然使用通配符可以带来使用上的便利，但是检索不需要的列通常会降低检索和应用程序的程序。

2.5 检索不同的值

SELECT语句返回所有匹配的行，若不想有重复出现的值，可以使用DISTINCT关键字，它指示数据库返回不同的值。

--输入
SELECT vend_id 
FROM products;
--结果
 vend_id 
---------
 BRS01
 BRS01
 BRS01
 DLL01
 DLL01
 DLL01
 DLL01
 FNG01
 FNG01

--输入
SELECT DISTINCT vend_id 
FROM products;
--结果 
 vend_id 
---------
 BRS01
 FNG01
 DLL01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

注意：不能部分使用DISTINCT

DISTINCT作用于所有的列，不仅仅是跟在其后的那一列。DISTINCT关键字只能用在第一个列名之前。

--输入
SELECT DISTINCT vend_id, prod_price 
FROM products;
--结果 
 vend_id | prod_price 
---------+------------
 BRS01   |      11.99
 BRS01   |       5.99
 BRS01   |       8.99
 FNG01   |       9.49
 DLL01   |       3.49
 DLL01   |       4.99
1
2
3
4
5
6
7
8
9
10
11
12

当指定的所有列相同，才算相同；其中某一类相同，不算相同。

2.6 限制结果

SELECT语句返回指定表中所有匹配行。如果只想返回其中第一行或者一定数量的行，各种数据库的这一SQL实现并不相同。

PostgreSQL中使用LIMIT关键字指定返回行数。

--输入
SELECT prod_name 
FROM products 
LIMIT 5;
--输出
     prod_name      
--------------------
 8 inch teddy bear
 12 inch teddy bear
 18 inch teddy bear
 Fish bean bag toy
 Bird bean bag toy

1
2
3
4
5
6
7
8
9
10
11
12
13

LIMIT 5指示返回结果不超过5行。为了获取后面的数据，需要OFFSET关键字指定从哪里开始以及检索的行数。

--输入
SELECT prod_name 
FROM products 
LIMIT 5 OFFSET 5;
--输出
     prod_name      
---------------------
 Rabbit bean bag toy
 Raggedy Ann
 King doll
 Queen doll
1
2
3
4
5
6
7
8
9
10
11

LIMIT 5 OFFSET 5指示从第5行起的最多5行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始。

2.7 使用注释

-- 这是一种行内注释
# 这是另一种行内注释
/*
这种多行注释
*/
1
2
3
4
5

3 排序检索数据

3.1 排序数据

检索出的数据并不是随机显示的，数据一般以它在底层表中出现的顺序显示。有可能是数据添加顺序，但是如果数据随后进行更新或删除，那么这个顺序会受DBMS重新回收存储空间方式的影响。关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序是有意义的。

为了明确地排序SELECT语句检索出的数据，可以使用ORDER BY子句。

--输入
SELECT prod_name 
FROM products 
ORDER BY prod_name;
--结果
     prod_name      
---------------------
 12 inch teddy bear
 18 inch teddy bear
 8 inch teddy bear
 Bird bean bag toy
 Fish bean bag toy
 King doll
 Queen doll
 Rabbit bean bag toy
 Raggedy Ann
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

上述语句指出了对查询结果以prod_name列以字母序进行排序。

指定一条ORDER BY子句时，应该保证它是SELECT语句中的最后一条子句。

ORDER BY子句中使用的列是为了显示而选择的列，但实际上用非检索的列排序数据也是可以的。

--输入
SELECT prod_name 
FROM products 
ORDER BY vend_id;
--输出
     prod_name      
---------------------
 8 inch teddy bear
 12 inch teddy bear
 18 inch teddy bear
 Fish bean bag toy
 Bird bean bag toy
 Rabbit bean bag toy
 Raggedy Ann
 King doll
 Queen doll
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.2 按多个列排序

有时需要按不止一个列进行排序。例如，显示雇员名单，可能希望先按姓，再按名排序。

要按多个列排序，简单指定列名，列名之间用逗号分开即可。

--输入
SELECT prod_id, prod_price,prod_name
FROM products
ORDER BY prod_price, prod_name;
--结果
 prod_id | prod_price |      prod_name
---------+------------+---------------------
 BNBG02  |       3.49 | Bird bean bag toy
 BNBG01  |       3.49 | Fish bean bag toy
 BNBG03  |       3.49 | Rabbit bean bag toy
 RGAN01  |       4.99 | Raggedy Ann
 BR01    |       5.99 | 8 inch teddy bear
 BR02    |       8.99 | 12 inch teddy bear
 RYL01   |       9.49 | King doll
 RYL02   |       9.49 | Queen doll
 BR03    |      11.99 | 18 inch teddy bear
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

排序的优先级是ORDER BY后列顺序。上述例子中仅在多个行具有相同的prod_price值时，才会对产品按prod_name进行排序。所有prod_price列中所有值都是唯一的，则不会按prod_name进行排序。

3.3 按列位置进行排序

除了能用列名指出排序顺序外，ORDER BY还支持按相对列未知进行排序。

--输入
SELECT prod_id, prod_price, prod_name 
FROM products 
ORDER BY 2, 3;
--结果
 prod_id | prod_price |      prod_name
---------+------------+---------------------
 BNBG02  |       3.49 | Bird bean bag toy
 BNBG01  |       3.49 | Fish bean bag toy
 BNBG03  |       3.49 | Rabbit bean bag toy
 RGAN01  |       4.99 | Raggedy Ann
 BR01    |       5.99 | 8 inch teddy bear
 BR02    |       8.99 | 12 inch teddy bear
 RYL01   |       9.49 | King doll
 RYL02   |       9.49 | Queen doll
 BR03    |      11.99 | 18 inch teddy bear
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT清单中指定的是列的相对位置而不是列名。ORDER BY 2表示按SELECT清单中的第二列prod_name进行排序。ORDER BY 2, 3表示先按prod_price，再按prod_name进行排序。

这种方式虽然不用重新输入列名，但是缺点是

1. 不明确给出列名可能造成错用列名排序；

2. 其次，在对SELECT清单进行更改，但是忘了对ORDER BY子句进行相应的修改，会造成错误；

3. 最后如果进行排序的列不在SELECT清单中，显然不能使用这种方式

   （如果有必要， 可以混合匹配使用实际列名和相对列位置）。

3.4 指定排序方向

数据排序默认是升序排序（从A到Z）。在ORDER BY子句中指定DESC关键字可以进行降序排序。

--输入
SELECT prod_id, prod_price, prod_name 
FROM products
ORDER BY prod_price DESC;
--结果 
prod_id | prod_price |      prod_name
---------+------------+---------------------
 BR03    |      11.99 | 18 inch teddy bear
 RYL01   |       9.49 | King doll
 RYL02   |       9.49 | Queen doll
 BR02    |       8.99 | 12 inch teddy bear
 BR01    |       5.99 | 8 inch teddy bear
 RGAN01  |       4.99 | Raggedy Ann
 BNBG03  |       3.49 | Rabbit bean bag toy
 BNBG02  |       3.49 | Bird bean bag toy
 BNBG01  |       3.49 | Fish bean bag toy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

如果打算给多个列排序

--输入
SELECT prod_id, prod_price, prod_name 
FROM products
ORDER BY prod_price DESC, prod_name;
--结果
prod_id | prod_price |      prod_name
---------+------------+---------------------
 BR03    |      11.99 | 18 inch teddy bear
 RYL01   |       9.49 | King doll
 RYL02   |       9.49 | Queen doll
 BR02    |       8.99 | 12 inch teddy bear
 BR01    |       5.99 | 8 inch teddy bear
 RGAN01  |       4.99 | Raggedy Ann
 BNBG02  |       3.49 | Bird bean bag toy
 BNBG01  |       3.49 | Fish bean bag toy
 BNBG03  |       3.49 | Rabbit bean bag toy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DESC关键字只能应用到直接位于其前面的列名。上例中，prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name仍然以标准的升序排序。

多个列上进行降序排序，必须对每个列指定DESC关键字。

DESC是DESCENDING的缩写， 这两个关键字都可以使用。 与DESC相对的是ASC（或ASCENDING） ， 在升序排序时可以指定它。 但实际上， ASC没有多大用处， 因为升序是默认的（如果既不指定ASC也不指定DESC， 则默认为ASC）。

提示： 区分大小写和排序顺序
在对文本性数据进行排序时， A与a相同吗？ a位于B之前， 还是Z之后？ 这些问题不是理论问题， 其答案取决于数据库的设置方式。
在字典（dictionary） 排序顺序中， A被视为与a相同， 这是大多数数据库管理系统的默认行为。 但是， 许多DBMS允许数据库管理员在需要时
改变这种行为（如果你的数据库包含大量外语字符， 可能必须这样做） 。
这里的关键问题是， 如果确实需要改变这种排序顺序， 用简单的ORDER BY子句可能做不到。 你必须请求数据库管理员的帮助。

4 过滤数据

4.1 使用WHERE子句

检索所需数据需要指定搜索条件（search criteria），也称为过滤条件（filter condition）。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

--输入
SELECT prod_name, prod_price 
FROM products
WHERE prod_price = 3.49;
--结果     
     prod_name      | prod_price
---------------------+------------
 Fish bean bag toy   |       3.49
 Bird bean bag toy   |       3.49
 Rabbit bean bag toy |       3.49
1
2
3
4
5
6
7
8
9
10

返回只满足WHERE子句的结果。

同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则会出现错误。

4.3 WHERE子句操作符

SQL支持的比较运算符

并非所有的DBMS都支持这些操作符。

因此跟NULL比较时不能用比较运算符，SQL提供了专门用来判断是否为NULL的IS NULL运算符。希望选取不是NULL的记录时，需要使用IS NOT NULL运算符

4.2.1 检查单个值

--输入
SELECT prod_name, prod_price 
FROM products 
WHERE prod_price < 10;
--结果
      prod_name      | prod_price
---------------------+------------
 8 inch teddy bear   |       5.99
 12 inch teddy bear  |       8.99
 Fish bean bag toy   |       3.49
 Bird bean bag toy   |       3.49
 Rabbit bean bag toy |       3.49
 Raggedy Ann         |       4.99
 King doll           |       9.49
 Queen doll          |       9.49
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.2.2 不匹配检查

--输入
SELECT vend_id, prod_price 
FROM products  
WHERE vend_id != 'DLL01';
--结果
 vend_id | prod_price
---------+------------
 BRS01   |       5.99
 BRS01   |       8.99
 BRS01   |      11.99
 FNG01   |       9.49
 FNG01   |       9.49
1
2
3
4
5
6
7
8
9
10
11
12

4.2.3 范围值检查

--输入
SELECT prod_name, prod_price 
FROM products 
WHERE prod_price BETWEEN 5 AND 10;
--结果
     prod_name      | prod_price
--------------------+------------
 8 inch teddy bear  |       5.99
 12 inch teddy bear |       8.99
 King doll          |       9.49
 Queen doll         |       9.49
1
2
3
4
5
6
7
8
9
10
11

使用BETWEEN时，必须指定范围的开始值和结束值。这里两个值必须使用AND关键字分隔。匹中值包括开始值和结束值。

4.2.4 空值检查

--输入
SELECT cust_name, cust_email 
FROM customers 
WHERE cust_email IS NULL;
--结果
   cust_name   | cust_email
---------------+------------
 Kids Place    |
 The Toy Store |
1
2
3
4
5
6
7
8
9

5 高级数据过滤

5.1 组合Where子句

SQL允许给出过个WHERE子句。这些子句可以使用AND或OR子句方式的使用。

操作符（operator）：用来联结或改变WHEREB子句中多个子句关键字，也称为**逻辑操作符（logical operator）。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（TRUE）或假（FALSE）**其中之一的值。

SQL还有一种特有的情况。对NULL进行逻辑比较操作时，会产生这时真值是除真假之外的第三种值——不确定（UNKNOWN）。一般的逻辑运算并不存在这第三种值。SQL之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有SQL中的逻辑运算被称为三值逻辑。

5.1.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。

--输入
SELECT prod_id, prod_price ,prod_name 
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
--结果
 prod_id | prod_price |      prod_name
---------+------------+---------------------
 BNBG01  |       3.49 | Fish bean bag toy
 BNBG02  |       3.49 | Bird bean bag toy
 BNBG03  |       3.49 | Rabbit bean bag toy
1
2
3
4
5
6
7
8
9
10

AND用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。可以增加多个过滤条件，每个条件间都要使用AND关键字。

5.1.2 OR操作符

OR WHERE子句用来表示检索匹配仍以给定的条件行，的第一个条件得到满足的情况下，不会计算第二个条件。

--输入
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
--输出
     prod_name      | prod_price
---------------------+------------
 8 inch teddy bear   |       5.99
 12 inch teddy bear  |       8.99
 18 inch teddy bear  |      11.99
 Fish bean bag toy   |       3.49
 Bird bean bag toy   |       3.49
 Rabbit bean bag toy |       3.49
 Raggedy Ann         |       4.99
1
2
3
4
5
6
7
8
9
10
11
12
13
14

5.1.3 求值顺序

WHERE子句可以包含任意数目的AND和OR操作者符，允许两者进行结合进行复杂、高级的过滤。假如需要列出加个为10美元以上，且由DLL01或BRS01制造的所有产品。

--输出
SELECT prod_name, prod_price 
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
AND prod_price > 10;
--结果
     prod_name      | prod_price
---------------------+------------
 18 inch teddy bear  |      11.99
 Fish bean bag toy   |       3.49
 Bird bean bag toy   |       3.49
 Rabbit bean bag toy |       3.49
 Raggedy Ann         |       4.99
1
2
3
4
5
6
7
8
9
10
11
12
13

但是上面的结果中有4行价格小于10美元，没有达到预期效果。

因为在SQL中AND操作符的优先级比OR操作符高。按上述语句写法，SQL理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DDL01制造的所有产品而不管其价格如何。

正确的SQL查询是

--输入
SELECT prod_name, prod_price 
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price > 10;
--结果
     prod_name      | prod_price
--------------------+------------
 18 inch teddy bear |      11.99
1
2
3
4
5
6
7
8
9

圆括号具有比 AND或OR操作符更高的求值顺序。任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作。不要依赖默认的求值顺序。

5.2 IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。

--输入
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;
--输出
      prod_name      | prod_price
---------------------+------------
 12 inch teddy bear  |       8.99
 18 inch teddy bear  |      11.99
 8 inch teddy bear   |       5.99
 Bird bean bag toy   |       3.49
 Fish bean bag toy   |       3.49
 Rabbit bean bag toy |       3.49
 Raggedy Ann         |       4.99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

IN操作符完成了与OR操作符相同的功能。

IN操作符的有点：

• 在有很多合法选项时，IN操作符的语法更加清楚、直观。
• 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
• IN操作符一般比一组OR操作符执行得更快。
• IN操作符最大的优点是可以包含其他SELECT语句，能够更加动态地建立WHERE子句。

5.3 NOT操作符

WHERE子句中的NOT操作符只有一个功能，那就是否其后所跟的任何条件。NOT从不单独使用，总是需要和其他操作符一起使用。NOT关键字可以用在要过滤的列前，而不是列后。

--输入
SELECT prod_name, vend_id 
FROM products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
--输出
     prod_name      | vend_id
--------------------+---------
 12 inch teddy bear | BRS01
 18 inch teddy bear | BRS01
 8 inch teddy bear  | BRS01
 King doll          | FNG01
 Queen doll         | FNG01
1
2
3
4
5
6
7
8
9
10
11
12
13

上述语句中的NOT和 **!=**操作符作用相同。

在简单语句中NOT没有什么优势。但是在更复杂的语句中，可以和IN操作符联合使用，NOT可以非常简单的找出与条件列表不匹配的行。

6 用通配符进行过滤

6.1 LIKE 操作符

前面介绍的所有操作符都是对已知值进行过滤，在过滤中使用的值都是已知值。这种过滤方法并非满足所有的搜索。

通配符 （wildcard）

用来匹配值的一串特殊字符。

搜索模式 （search pattern）

由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是SQL的WHERE子句中有特殊含义的字符。为了在搜索子句中使用统配符，就必须使用LIKE操作符，指示后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

通配符搜索只能用于文本字段（串），非文本数据类型不能使用通配符搜索。

谓词 （predicate）

从技术上讲LIKE是谓词而不是操作符

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false
1
2
3
4

6.1.1 百分号（%）通配符

在搜索串中，**%表示任何字符出现任意次数。%**能匹配0个字符。 %代表搜索模式中给定位置的0个、 1个或多个字符

例如为了找到所有以词Fish开头的产品。

--输入
SELECT prod_name 
FROM products
WHERE prod_name LIKE 'Fish%';
--结果
     prod_name
-------------------
 Fish bean bag toy
1
2
3
4
5
6
7
8

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

--输入
SELECT prod_id, prod_name 
FROM products
WHERE prod_name LIKE '%bean bag%';
--结果 
 prod_id |      prod_name
---------+---------------------
 BNBG01  | Fish bean bag toy
 BNBG02  | Bird bean bag toy
 BNBG03  | Rabbit bean bag toy
1
2
3
4
5
6
7
8
9
10

根据DBMS不同及其配置，搜索可以是区分大小写的。如果区分大小写则’fish%'与Fish bean bag toy就不匹配。

说明： 请注意后面所跟的空格
许多DBMS都用空格来填补字段的内容。 例如， 如果某列有50个字符， 而存储的文本为Fish bean bag toy（17个字符），则为填满该列需要在文本后附加33个空格。

这样做一般对数据及其使用没有影响， 但是可能对上述SQL语句有负面影响。 子句WHERE prod_name LIKE 'F%y’只匹配以F开头、 以y结尾的prod_name。 如果值后面跟空格， 则不是以y结尾， 所以Fish bean bag toy就不会检索出来。

简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配y之后的字符（或空格）。

通配符**%看起来像是可以匹配任何东西， 但有个例外， 这就是NULL**。 子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

6.1.2 下划线（_）通配符

**_的用途与%**一样，但它值匹配单个字符，而不是多个字符。

--输入
SELECT prod_id, prod_name 
FROM products
WHERE prod_name LIKE '__ inch teddy bear';
--结果
 prod_id |     prod_name
---------+--------------------
 BR02    | 12 inch teddy bear
 BR03    | 18 inch teddy bear
1
2
3
4
5
6
7
8
9

对照

--输入
SELECT prod_id, prod_name 
FROM products
WHERE prod_name LIKE '% inch teddy bear';
--输出
prod_id |     prod_name
---------+--------------------
 BR01    | 8 inch teddy bear
 BR02    | 12 inch teddy bear
 BR03    | 18 inch teddy bear
1
2
3
4
5
6
7
8
9
10

6.1.3 方括号（[]）通配符

**方括号（[]）**通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

例如，找出所有名字以J或M开头的联系人，可以使用如下查询。PostgreSQL LIKE操作符不支持这种方式，使用SIMILAR TO就支持。

SELECT cust_contact 
FROM customers
WHERE cust_contact LIKE '[MJ]%';
1
2
3

否定方法

SELECT cust_contact 
FROM Customers
WHERE cust_contact LIKE '[^JM]%'

SELECT cust_contact 
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%';
1
2
3
4
5
6
7

假如我们需要使用到%和_作为正常的字符：使用ESCAPE关键字

SELECT Cno 
FROM Course 
WHERE LIKE 'DB\_Design' ESCAPE '\'
1
2
3

6.2 SIMILAR TO 操作符

SIMILAR TO 根据自己的模式是否匹配给定字符串而返回真或者假。它和 LIKE 非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。SQL 标准的正则表达式是在 LIKE 表示法和普通的正则表达式表示法之间古怪的交叉。

类似 LIKE ，SIMILAR TO 操作符只有在它的模式匹配整个字符串的时候才能成功；这一点和普通的正则表达式的习惯不同，在普通的正则表达式里，模式匹配字符串的任意部分。和 LIKE 类似的地方还有 SIMILAR TO使用 _ 和 % 分别匹配单个字符和任意字符串(这些和 POSIX 正则表达式里的 . 和 .* 兼容)。

除了这些从 LIKE借用的功能之外，SIMILAR TO支持下面这些从 POSIX 正则表达式借用的模式匹配元字符：

• | 表示选择(两个候选之一)
• * 表示重复前面的项零次或更多次
• + 表示重复前面的项一次或更多次
• () 把项组合成一个逻辑项
• [...] 声明一个字符类

请注意没有提供范围重复(? 和 {...})，尽管它们在 POSIX 里有。同时，点(.)不是元字符。

和 LIKE 一样，反斜杠关闭所有这些元字符的特殊含义；当然我们也可以用 ESCAPE 声明另外一个逃逸字符。

一些例子：

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false
1
2
3
4

6.3 使用通配符的技巧

通配符很有用，但是也有代价。通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。

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

7 创建计算字段

7.1 计算字段

存储在数据库表中的数据一般不是应用程序所需的格式。例如

• 需要显示公司名，同时还需要显示公司地址，但这两个信息存储在不同的表列中。
• 城市、州和邮政编码存储在不同观点列中，但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
• 物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量）。但为了打印发票，需要物品的总价格。
• 需要根据表数据进行诸如总数、平均数的计算。

上述例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过后的数据，而不是检索出数据，然后再让客户端应用程序中重新格式化。

**字段（field）**基本上与列的意思相同，经常互换使用。不过数据库一般称为列，而术语字段通常与计算字段一起使用。

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

7.2 拼接字段

举个例子，创建由两组列组成的标题。

vendors表包含了供应商名和地址信息。加入要生成一个供应商表。需要在格式化的名称中列出供应商的地址。

拼接：将值联结在一起构成单个值。PostgreSQL使用的联结符是**||， 有些DBMS使用的连接符是+**。

--输入
SELECT vend_name || '(' ||  vend_country ||  ')' 
FROM vendors
ORDER BY vend_name;
--结果
     ?column?
------------------------
 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
4
5
6
7
8
9
10
11
12
13

上述SELECT语句返回包含四个元素的一个列。

结合成一个计算字段的两个列用空格填充。 许多数据库（不是所有） 保存填充为列宽的文本值， 而实际结果不需要这些空格。 为正确返回格式化的数据， 必须去掉这些空格。 这可以使用SQL的**RTRIM()**函数来完成

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

TRIM函数，大多数DBMS都支持

• RTRIM()（去掉字符串右边的空格）
• LTRIM()（去掉字符串左边的空格）
• TRIM()（去掉字符串左右两边的空格）。

7.3 使用别名

从前面SELECT语句可以看到，新计算列没有名字，只有一个值，不能用于客户端应用中。

可以通过列别名的方式解决这一问题。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。

别名可以使用中文，使用中文时需要用双引号（"）括起来。

SELECT vend_name || '(' ||  vend_country ||  ')' AS vend_title
FROM vendors
ORDER by vend_name;
       vend_title
------------------------
 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
4
5
6
7
8
9
10
11

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

7.4 执行算术计算

计算字段的另一个用途就是对检索出的数据进行算术计算。

SELECT prod_id, quantity, item_price, 
		quantity*item_price AS expended_price
FROM orderitems 
WHERE order_num = 20008;
 prod_id | quantity | item_price | expended_price
---------+----------+------------+----------------
 RGAN01  |        5 |       4.99 |          24.95
 BR03    |        5 |      11.99 |          59.95
 BNBG01  |       10 |       3.49 |          34.90
 BNBG02  |       10 |       3.49 |          34.90
 BNBG03  |       10 |       3.49 |          34.90
1
2
3
4
5
6
7
8
9
10
11

算术运算符

SQL语句中进行运算时，需要特别注意含有NULL的运算。所有包含NULL的计算，结果肯定是NULL。

5 + NULL
10 - NULL
1 * NULL
4 / NULL
NULL /9
NULL / 0
1
2
3
4
5
6

以上极端结果都是NULL。注意NULL不是0；

8 使用数据处理函数

8.1 函数

SQK可以用函数来处理数据。函数一般是在数据上执行，为数据的转换和处理提供方便。

函数带来的问题

每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数名称 和语法可能极不相同。与SQL语句不一样，SQL函数不是可移植的。

8.2 使用函数

目前大多数SQL支持一下类型函数

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

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
 Furball Inc.    | FURBALL INC.
 Jouets et ours  | JOUETS ET OURS
1
2
3
4
5
6
7
8
9
10
11

常用的文本处理函数

8.2.2 日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己特殊的形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并节省物理存储空间。

应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。

SELECT order_num 
FROM orders
WHERE DATE_PART('year', order_date) = 2012;
 order_num
-----------
     20005
     20006
     20007
     20008
     20009
1
2
3
4
5
6
7
8
9
10

DATE_PART()函数， 顾名思义， 此函数返回日期的某一部分。 DATE_PART()函数
有两个参数， 它们分别是返回的成分和从中返回成分的日期。

8.2.3 数值处理函数

数值处理函数仅处理数值数据。一般用于代数、三角或几何运算。

常用的数值函数

9 汇总数据

9.1 聚集数据

我们经常用汇总数据，而不用把他们实际检索出来。

• 确定表中行数（或者满足某个条件或包含某个特定值的行数） ；
• 获得表中某些行的和；
• 找出表列（或所有行或某些特定的行） 的最大值、 最小值、 平均值。

聚合函数（ aggregate function） 对某些行运行的函数， 计算并返回一个值。

9.1.1 AVG()函数

**AVG()**通过对表中行数计算并计算其列值之和，求得该列的平均值。可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

SELECT AVG(prod_price) AS avg_price 
FROM products;
     avg_price
--------------------
 6.8233333333333333
1
2
3
4
5

也可用来返回某些特定行的平均值

SELECT AVG(prod_price) AS avg_price 
FROM products
WHERE vend_id = 'DLL01';
     avg_price
--------------------
 3.8650000000000000
1
2
3
4
5
6

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

9.1.2 COUNT()函数

COUNT()函数进行计数。 可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

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

SELECT COUNT(*) AS num_cust 
FROM CUSTOMERS;
 num_cust
----------
        5
        
 SELECT COUNT(cust_email) AS num_cust 
 FROM CUSTOMERS;
 num_cust
----------
        3
1
2
3
4
5
6
7
8
9
10
11

9.1.3 MAX()函数

MAX()返回指定列中的最大值。

SELECT MAX(prod_price) AS max_price FROM products;
 max_price
-----------
     11.99
1
2
3
4

MAX()一般用来找出最大的数值或日期。但许多DBMS允许它用来返回任意列中的最大值。

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

9.1.4 MIN()函数

MIN()函数的功能与MAX()相反，返回指定列的最小值。

SELECT MIN(prod_price) AS min_price 
FROM products;
 min_price
-----------
      3.49
1
2
3
4
5

9.1.5 SUM()函数

SUM()用来返回指定列值的和（总计）。

可如下检索所订购物品的总数

SELECT SUM(quantity) AS items_ordered 
FROM orderitems
WHERE order_num = 20005;
 items_ordered
---------------
           200
1
2
3
4
5
6

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

SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
 total_price
-------------
     1648.00
1
2
3
4
5
6

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

9.2 聚集不同值

以上5个聚合函数都可以如下使用：

• 对所有行执行计算， 指定ALL参数或不指定参数（因为ALL是默认行为） 。

• 只包含不同的值， 指定DISTINCT参数。

  这里DISTINCT必须写在括号中。这是因为必须要在计算行数之前删除prod_price列中的重复数据。写在括号外的话，就会先计算出数据，然后再删除重复数据，就不会起效果。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
     avg_price
--------------------
 4.2400000000000000
1
2
3
4
5
6

9.3 组合聚合函数

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;
 num_items | price_min | price_max |     price_avg
-----------+-----------+-----------+--------------------
         9 |      3.49 |     11.99 | 6.8233333333333333
1
2
3
4
5
6
7
8

10 数据分组

10.1数据分组

从上一节得知，使用SQL聚合函数可以汇总数据，对行进行计数，计算和平均数，不检索所有数据就能获得最大值和最小值。

目前为止，所有计算都是在表的素有数据或匹配特定的WHERE子句的数据上进行的。

如果要返回每一个供应商提供产品数目怎么办，或者只提供一项产品的供应商的产品，或者返回提供10个以上产品的供应商上的产品。

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

10.2 创建分组

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
 vend_id | num_prods
---------+-----------
 BRS01   |         3
 FNG01   |         2
 DLL01   |         4
1
2
3
4
5
6
7
8

SELECT语句指定了两个列：vend_id包含产品供应商的ID，num_prods作为计算字段。GROUP BY子句指示DBMS按vend_id排序并分组数据。

GROUP BY子句将表进行了分组。在GROUP BY子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式，所以是非常重要的列。当然，GROUP BY子句也和SELECT子句一样，可以通过逗号分隔指定多列。

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

10.3 过滤分组

除了能用GROUP BY分组数据外， SQL还允许过滤分组， 规定包括哪些分组， 排除哪些分组。

WHERE过滤指定的是行而不是分组。 事实上， WHERE没有分组的概念。

HAVING非常类似于WHERE。 事实上， 目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。 唯一的差别是， WHERE过滤行， 而HAVING过滤分组 。WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤 。

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >=2;
  cust_id   | orders
------------+--------
 1000000001 |      2
1
2
3
4
5
6
7

有时在一条语句中同时需要WHERE和HAVING子句 。

列出具有两个以上产品且其价格大于等于4的供应商 。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >=4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
 vend_id | num_prods
---------+-----------
 FNG01   |         2
 BRS01   |         3
1
2
3
4
5
6
7
8
9

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >=2;
 vend_id | num_prods
---------+-----------
 BRS01   |         3
 FNG01   |         2
 DLL01   |         4
1
2
3
4
5
6
7
8
9

HAVING与WHERE非常类似， 如果不指定GROUP BY， 则大多数DBMS会同等对待它们。 不过， 要能区分这一点。 使用HAVING时应该结合GROUP BY子句， 而WHERE子句用于标准的行级过滤。

有些条件既可以写在HAVING子句当中，又可以写在WHERE子句当中。这些条件就是聚合键所对应的条件。聚合键所对应的条件还是应该书写在WHERE子句之中。

有两个原因

• 根本原因是WHERE子句和HAVING子句的作用不同。如前所述，HAVING子句是用来指定“组”的条件的。因此，“行”所对应的条件还是应该写在WHERE子句当中。这样一来，书写出的SELECT语句不但可以分清两者各自的功能，理解起来也更加容易。

  WHERE子句=指定行所对应的条件；HAVING子句=指定组所对应的条件。

• 将条件写在WHERE子句中要比写在HAVING子句中的处理速度更快，返回结果所需的时间更短。