PostgreSQL11 | 查询数据_postgresql 查询

上一篇总结了原著的第七章有关pgsql的插入、更新与删除的用法，本篇将总结pgsql的查询功能以及相关的sql语句。

查询数据

数据库管理系统的一个重要功能就是提供数据查询。数据查询不是简单返回数据库中存储的数据，而是应该根据需要对数据进行筛选，以及决定数据将以什么样的格式显示。PostgreSql提供了功能强大、灵活的语句来实现这些操作，本章将介绍如何使用 SELECT 语句查询数据表中的一列或多列数据、使用集合函数显示查询结果、连接查询、子查询以及使用正则表达式进行查询等。

目录

查询数据

基本查询语句

单表查询

查询所有字段

查询指定字段

查询指定记录

带 IN 关键字的查询

带BETWEEN AND 的范围查询

带 LIKE 的字符匹配查询

查询空值

带AND的多条件查询

带OR的多条件查询

查询结果不重复

对查询结果排序

分组查询

用LIMIT 限制查询结果的数量

使用集合函数查

COUNT()函数

SUM()函数

AVG()函数

MAX()函数

MIN()函数

连接查询

内连接查询

外连接查询

复合条件连接查询

子查询

带ANY、SOME 关键字的子查询

带 ALL 关键字的子查询

带 EXISTS 关键字的子查询

带 IN 关键字的子查询

带比较运算符的子查询

合并查询结果

为表和字段取别名

为表取别名

为字段取别名

使用正则表达式查询

查询以特定字符或字符串开头的记录

查询以特定字符或字符串结尾的记录

用符号"."来替代字符串中的任意一个字符

使用"*"和"+"来匹配多个字符

匹配指定字符中的任意一个

匹配指定字符以外的字符

使用{M}或者{MN}来指定字符串连续出现的次数

PostgreSQL 11 新特性——psql 新增 gdesc 选项

---

基本查询语句

PostgreSOL从数据表中查询数据的基本语句为SELECT 语句。SELECT语句的基本格式是：

SELECT
{*|<字段列表>}
[    
    FROM <表 1>,<表 2>...
    [WHERE<表达式>]
    [GROUP BY <group by definition>]]
    [HAVING <expression> [{<operator> <expression>}...]]
    [ORDER BY <order by definition>]
    [LIMIT [<offset>,] [<row count>]
]
SELECT[字段 1,字段 2,..,字段 n]
FROM[表或视图1
WHERE[查询条件];

• {*<字段列表>}:包含星号通配符和选字段列表，‘*’表示查询所有的字段，‘字段列表’表示查询指定的字段，字段列至少包含一个子段名称。如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。
• 在 FROM <表 1>,<表 2>...中，表 1和表 2表示查询数据的来源，可以是单个或者多个。
• WHERE 子句是可选项，如果选择该项，[查询条件]将限定查询行必须满足的查询条件。
• GROUP BY<字段>：该子句告诉 PostgreSOL 如何显示查询出来的数据，并按照指定的字段分组。
• [ORDER BY<字段 >]：该子句告诉 PostgreSQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序 (ASC) 和降序 (DESC)。
• [LIMIT[<offset>,]<row count>]：该子句告诉 PostgreSQL 每次显示查询出来的数据条数。

下面创建数据表 fruits，该表中包含了本章中需要用到的数据。

create table fruits
(
	f_id char(10) not null,
	s_id int not null,
	f_name char(225) not null,
	f_price decimal(8,2) not null,
	primary key(f_id)
);

为了演示如何使用 SELECT 语句，需要插入数据。请读者插入如下数据(建议直接复制节约时间)：

insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('12',104,'lemon',6.4),
('b2',104,'berry',8.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx',3.6);

使用 SELECT 语句查询 f id 和 f name 字段的数据，SQL 语句如下：

select f_id,f_name from fruits;

  

SELECT 语句决定了要查询的列值，在这里查询 f_id 和 f_name 两个字段的值；FROM子句指定了数据的来源，这里指定数据表 fruits。因此，返回结果为 fruits 表中 f_id和f_name 两个字段下的所有数据，其显示顺序为添加到表中的顺序。

单表查询

单表查询是只从一张数据表中查询所需的数据。本节将介绍单表查询中的各种基本查询方式，主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询以及对查询结果进行排序等。

查询所有字段

1.在SELECT 语句中使用星号“*”通配符查询所有字段

SELECT 查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号(*)通配符指定查找所有的列的名称。语法格式如下:

SELECT * FROM 表名;

例：从 fruits 表中检索所有字段的数据，SQL语句如下:

select * from fruits;

2.在 SELECT 语句中指定所有字段

另外一种查询所有字段值的方法是在 SELECT 语句中指定所有字段。根据前面介绍的SELECT 语句格式，SELECT 关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在 SELECT 子句右边。有时候，可能表中的字段比较多，不一定能记住所有字段的名称，因此该方法不太方便，不建议使用。例如，查询 fuits 表中的所有数据，SQL 语句也可以书写如下:

select f_id,s_id,f_name,f_price from fruits;

查询指定字段

(1).查询单个字段

查询表中的某一个字段，语法格式为：

SELECT 列名 FROM 表名;

例：查询当前表中 f_name 列的所有水果名称，SQL 语句如下：

select f_name from fruits;

(2).查询多个字段

使用SELECT 语句，可以获取多个字段下的数据，只需要在关键字 SELECT 后面指定要查找的字段的名称即可。不同字段名称之间用逗号 ( , ) 分隔开，最后一个字段后面不需要加逗号。语法格式如下:

SELECT 字段名 1,字段名 2,...,字段名 n FROM 表名;

例：从fruits 表中获取 f name和f price 两列，SOL 语句如下：

select f_name,f_price from fruits;

查询指定记录

数据库中包含大量的数据，根据特殊要求，可能只需查询表中的指定数据，即对数据进行过滤。在 SELECT 语句中可通过 WHERE 子对数据进行过滤，语法格式为:

SELECT 字段名 1,字段名 2,...,字段名 n
FROM 表名
WHERE查询条件

在WHERE 子句中，PostgreSQL 提供了一系列的条件判断符，如表所示。

例：查询价格为 10.2 元的水果名称，SQL 语句如下：

select f_name,f_price from fruits where f_price = 10.2;

相等还可以用来比较字符串，比如下面的例子。

例：查找名称为“apple”的水果价格，SQL语句如下：

select f_name,f_price from fruits where f_name = 'apple';

例：查询价格小于 10 的水果名称，SQL 语句如下：

select f_name,f_price from fruits where f_price < 10;

 

带 IN 关键字的查询

IN 操作符用来查询满足指定条件范围内的记录。使用 IN 操作符时，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

例：查询 s_id 为 101 和102 的记录，SQL 语句如下：

select s_id,f_name,f_price from fruits where s_id in (101,102) order by f_name;

相反，可以使用关键字 NOT 来检索不在条件范围内的记录

例：查询所有 s id 不等于 101 也不等于 102 的记录，SQL 语句如下：

select s_id,f_name,f_price from fruits where s_id not in (101,102) order by f_name;

带BETWEEN AND 的范围查询

BETWEEN AND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果记录的字段值满足指定的范围查询条件，则这些记录被返回。

例：查询价格在 2.00 元到 10.20 元之间水果的名称和价格，SQL 语句如下:

select f_name,f_price from fruits
where f_price between 2.00 and 10.20;

例：查询价格在 2.00 元到 10.20 元之外的水果名称和价格，SQL 语句如下：

select f_name,f_price from fruits
where f_price not between 2.00 and 10.20;

 

带 LIKE 的字符匹配查询

在前面的检索操作中讲述了如何查询多个字段的记录、如何进行比较查询或者是查询一个条件范围内的记录，如果要查找所有的包含字符“ge”的水果名称，该如何查找呢?简单的比较操作在这里已经行不通了，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。执行这个任务的关键字是 LIKE。
通配符是一种在 SOL的 WHERE 条件子句中拥有特殊意思的字符，SQL 语句中支持通配符，可以和 LIKE 一起使用的通配符是“%’

例：查找所有以“b’字母开头的水果，SOL 语句如下：

select f_name,f_price from fruits
where f_name like 'b%';

例： 在 fuits 表中，查询f_name 中包含字母'g'的记录，SQL语句如下：

select f_id,f_name from fruits
where f_name like '%g%';

查询空值

数据表创建的时候，设计者可以指定某列中是否可以包含空值 (NULL)。空值不同于0，也不同于空字符串，一般表示数据未知、不适用或将在以后添加数据。在 SELECT 语句中使用 IS NULL 子句，可以查询某字段内容为空记录。
下面在数据库中创建数据表 customers，该表中包含了本章中需要用到的数据。

create table customers
(
	c_id char(10) primary key,
	c_name varchar(225) not null,
	c_email varchar(50) null
);

为了演示，需要插入数据，可执行以下语句：

insert into customers(c_id,c_name,c_email)
values('10001','RedHook','LMing@163.com'),
('10002','Stars','Jerry@hotmail.com'),
('10003','RedHook',NULL),
('10004','JOTO','sam@hotmail.com');

例：查询 customers 表中 c_email 为空的记录的c_id、c_name 和c_email 字段值，SQL 语句如下：

select c_id,c_name,c_email from customers
where c_email is null;

例：查询 customers 表中 c_email 不为空的记录的 c_id、c_name和c_email字段值，SOL语句如下：

select c_id,c_name,c_email from customers
where c_email is not null;

带AND的多条件查询

使用 SELECT 查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确PostgreSQL在WHERE子句中使用AND操作符限定只有必须满足所有查询条件的记录才会被返回。可以使用 AND连接两个甚至多个查询条件，多个条件表达式之间用 AND 分开。

例：在 fuits 表中查询 s_id = 101 并且f_price 大于 5 的记录价格和名称，SQL语句如下：

select s_id,f_price,f_name from fruits
where s_id = '101' and f_price >= 5; 

例：在 fruits 表中查询 s_id = 101 或者 102、f_price 大于等于5 并且f_name=apple'的记录的价格和名称，SOL 语句如下: 

select f_id,f_price,f_name from fruits
where s_id in ('101','102') and f_price >= 5 and f_name='apple'; 

带OR的多条件查询

与AND 相反，在 WHERE 声明中使用 OR 操作符，表示只需要满足其中一个条件的记录即可返回。OR 也可以连接两个甚至多个查询条件，多个条件表达式之间用 OR 分开。

例：查询 s_id=101 或者 s_id=102 的水果供应商的 f_price和f_name，SQL语句如下：

select s_id,f_name,f_price from fruits
where s_id= 101 or s_id = 102; 

在这里，也可以使用 IN 操作符实现与 OR 相同的功能，可通过下面的例子进行说明。

例：查询 s_id=101 或者 s_id=102 的水果供应商的 f_price 和 f_name，SQL语句如下：

select s_id,f_name,f_price from fruits
where s_id in (101,102); 

查询结果不重复

从前面的例子可以看到，SELECT查询返回所有匹配的行，假如查询 firuits表中所有的s_id，其结果如图所示。

可以看到查询结果返回了 16 条记录，其中有一些重复的 s_id 值。有时出于对数据分析的要求，需要消除重复的记录值，如何使查询结果没有重复呢？在 SELECT 语句中可以使用 DISTINCT 关键字指示 PostgreSQL 消除重复的记录值。语法格式为：

SELECT DISTINCT 字段名 FROM 表名;

例：查询 fruits 表中 s_id 字段的值，并返回 s_id 字段值不得重复，SQL 语句如下：

select distinct s_id FROM fruits;

对查询结果排序

从前面的查询结果，读者会发现有些字段的值是没有任何顺序的。在 PostgreSQL 中可以通过在 SELECT 语句中使用 ORDER BY 子句对查询的结果进行排序。

(1).单列排序

例如，查询 f_name 字段，语句执行后的结果如图所示。

可以看到，查询的数据并没有以一种特定的顺序显示。如果没有对它们进行排序，就将根据它们插入到数据表中的顺序来显示。
下面使用 ORDER BY 子句对指定的列数据进行排序。

例：查询 fruits 表的 f_name 字段值，并对其进行排序，SOL语句如下：

select f_name FROM fruits order by f_name;

(2).多列排序

有时需要根据多列值进行排序，比如显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，只要将需要排序的列之间用逗号隔开即可。

例：查询 fruits 表中的 f_name 和f price 字段，先按f_name 排序，再按f_price 排序，SOL语句如下:

select f_name,f_price FROM fruits order by f_name,f_price;

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中的所有值都是唯一的，就将不再对第二列进行排序

(3).指定排序方向

默认情况下，查询数据按字母升序进行排序(从A 到 Z)，但数据的排序并不仅限于此还可以使用ORDER BY 对查询结果进行降序排序(从Z到A)。降序通过关键字DESC实现如下面的例子所示。

例：查询 fruits 表中的 f_name 和f_price 字段，对结果按f_price 降序方式排序，SQL语句如下：

select f_name,f_price FROM fruits order by f_price DESC;

与 DESC 相反的是 ASC(升序排序)，将字段列中的数据按字母表顺序升序排序实际上，在排序的时候 ASC 是默认的排序方式，所以加不加都可以。

也可以对多列进行不同的顺序排序

例：查询 fruits 表，先按 f_price 降序排序，再按f_name 字段升序排序，SQL语句如下:

select f_price,f_name FROM fruits order by f_price DESC,f_name;

DESC 关键字只对其前面的列降序排列，在这里只对 f_price 排序，而并没有对f_name 进行排序，因此，f_price 按降序排序，而f_name 列仍按升序排序。如果要对多列都进行降序排序，必须要在每一列的列名后面加 DESC 关键字。

分组查询

分组查询是对数据按照某个或多个字段进行分组。在 PostgreSQL 中，使用 GROUP BY关键字对数据进行分组，基本语法形式为:

[GROUP BY 字段][HAVING <条件表达式>]

“字段”表示进行分组时所依据的列名称；“HAVING <条件表达式>”指定 GROUP BY分组显示时需要满足的限定条件。

(1).创建分组

GROUP BY 关键字通常和集合函数一起使用，例如MAX()、MIN()、COUNT()、SUM()、AVG()。例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到 COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。

例：根据s_id 对 fruits 表中的数据进行分组，SOL 语句如下：

select s_id,count(*) as Total FROM fruits group by s_id;

(2). 使用 HAVING 过滤分组

GROUP BY 可以和 HAVING 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
例：根据 s id 对 fruits 表中的数据进行分组，并显示水果种类大于1的分组信息,SOL 语句如下：

select s_id,count(f_name) FROM fruits group by s_id having count(f_name)>1;

HAVING 关键字与 WHERE 关键字都是用来过滤数据的，两者有什么区别呢?其中重要的一点是，HAVING 在数据分组之后进行过滤，用来选择分组；WHERE在分组之前用来选择记录。另外，WHERE 排除的记录不再包括在分组中。

(3).GROUP BY和ORDER BY 一起使用

某些情况下需要对分组进行排序。在前面的介绍中，ORDER BY 用来对查询的记录排序。如果和GROUP BY一起使用可以完成对分组的排序
例：根据 s_id 对 fits 表中的数据进行分组，显示水果种类大于1的分组信息，并按照水果的种类排序，SOL 语句如下:

select s_id,count(f_name) FROM fruits group by s_id having count(f_name)>1 order by count(f_name);

用LIMIT 限制查询结果的数量

SELECT 将返回所有匹配的行，可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT 关键字，基本语法格式如下:

LIMIT行数[位置偏移量,]

“位置偏移量”参数指示 PostgreSQL 从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始(第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推)；参数“行数”指示返回的记录条数。

例：显示 fruits 表查询结果的前 4 行
在查询前，先查询 fruits 表中数据的顺序:

select * from fruits;

查询 fruits 表的前4行：

select * from fruits limit 4;

例：在 fruits 表中，使用 LIMIT 子，返回从第 5 个记录开始的行数长度为3的记录（从第五个开始包含第五条数据在内共向下取三条数据），SQL 语句如下:

select * from fruits limit 3 offset 4;

使用集合函数查

有时候并不需要返回实际表中的数据，而只是对数据进行总结，PostgreSQL 提供一些查询功能，可以对获取的数据进行分析和报告。这些函数的功能有计算数据表中总共有的记录行数，计算某个字段列下数据的总和，以及计算表中某个字段下的最大值、最小值或者平均值.本章将介绍这些函数以及如何使用它们。这些聚合函数的名称和作用如表所示。

COUNT()函数

COUNTO函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种:

1. COUNT(*) 计算表中总的行数，不管某列是否有数值或者为空值。
2. COUNT(字段名)计算指定列下总的行数，计算时将忽略字段值为空值的行。

例：查询 customers 表中总的行数，SQL 语句如下:

先看一下表中的数据有那些

 执行sql语句

select count(*) as cust_num from customers;

COUNT(*)返回 customers 表中记录的总行数，不管其值是什么。返回的总数的名称为cust num。

例：查询 customers 表中有电子邮箱的顾客的总数，SOL语句如下:

select count(c_email) as email_num from customers;

两个例子中不同的数值说明了两种方式在计算总数的时候对待 NULL值的方式不同，即指定列的值为空的行被 COUNTO函数忽略，但是如果不指定列，而在COUNTO函数中使用星号“*”，则所有记录都不忽略。

前面介绍分组查询的时候介绍了 COUNTO函数与 GROUP BY 关键字一起使用，用来计算不同分组中的记录总数。
例：在 fruits 表中，使用 COUNT()函数统计不同的 s_id 的水果种类，然后计算每个分组中的总记录数。

先看一下表中的数据

然后执行语句

select s_id,count(f_name) from fruits group by s_id;

SUM()函数

SUM()是一个求总和的函数，返回指定列值的总和。

例：在 fruits 表中查询 s_id=101 的水果价格总和，SQL 语句如下:

select sum(f_price) as price_total from fruits where s_id=101;

从查询结果可以看到，SUM(f_price)函数返回水果价格之和，WHERE 子句指定查询的s_id为101。
SUM()可以与GROUP BY一起使用，计算每个分组的总和。

例：在fruits 表中查询不同s_id 的水果价格总和，SOL语句如下:

select s_id,sum(f_price) as price_total from fruits group by s_id;

AVG()函数

AVG()函数通过计算返回的行数和每一行数据的和求得指定列数据的平均值

例：在 fruits 表中，查询 s_id=103 的供应商的水果价格平均值，SOL语句如下：

select avg(f_price) as avg_price from fruits where s_id=103;

该例中的查询语句增加了一个 WHERE 子句,并且添加了查询过滤条件,只查询s_id= 103的记录中的 f_price，因此通过 AVG()函数计算的结果只是指定的供应商水果的价格平均值,而不是所有市场上水果价格的平均值。
AVG()可以与 GROUP BY 一起使用，计算每个分组的平均值。

例：在 fuits 表中，查询每一个供应商的水果价格的平均值，SQL 语句如下:

select s_id,avg(f_price) as avg_price from fruits group by s_id;

AVG()函数使用时，其参数为要计算的列名称，如果要得到多列的多个平均值，就需要在每一列上使用 AVG()函数。

MAX()函数

MAX()返回指定列中的最大值。
例：在 fruits 表中查找市场上价格最高的水果，SQL 语句如下:

select max(f_price) as max_price from fruits;

MAX()也可以和 GROUP BY 关键字一起使用，求每个分组中的最大值。

例：在 uits 表中查找不同供应商提供的价格最高的水果，SOL语句如下：

select s_id,max(f_price) as max_price from fruits group by s_id;

MAX()函数不仅适用于查找数值类型，也可以用于字符类型
例：在 fruits 表中查找 f_name 的最大值，SQL 语句如下:

select max(f_name) from fruits;

 MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，按照字符的ASCII码值大小比较，从a到z，a的 ASCII 码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，“b’与“t”比较时，“t”为最大值;“bcd”与“bca”比较时“bcd”为最大值。

MIN()函数

MIN()返回查询列中的最小值
例：在 fruits 表中查找市场上水果的最低价格，SQL 语句如下:

select min(f_price) as min_price from fruits;

MIN()也可以和 GROUP BY 关键字一起使用，求每个分组中的最小值。

例：在 fruits 表中查找不同供应商提供的价格最低的水果，SQL 语句如下:

select s_id,min(f_price) as min_price from fruits group by s_id;

连接查询

连接是关系数据库模型的主要特点。

连接查询是关系数据库中最主要的查询,包括内连接外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，建立表时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。

当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在有相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。

关于三种表间连接方式可以看下面分享的链接来简单学习三种连接的效果

sql的内连接、左连接、右连接基础概念

内连接查询

内连接 (INNER JOIN)使用比较运算符进表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果列表中。

例：在 fruits 表和 suppliers 表之间使用内连接查询
查询之前，在数据库中创建数据表 suppliers，SQL 语句如下:

create table suppliers
(
	s_id int primary key,
	s_name varchar(50) not null,
	s_city varchar(50) not null
);

为了演示，需要插入数据，可执行以下语句:

insert into suppliers(s_id,s_name,s_city)
values(101,'FastFruit Inc','Tianjin'),
(102,'LT Supplies','shanghai'),
(103,'ACME','beijing'),
(104,'FNK Inc','zhengzhou'),
(105,'Good Set','xinjiang'),
(106,'Just Eat Ours', 'yunnan'),
(107,'JOTO meoukou', 'guangdong');

firuits 表和 suppliers 表中都有相同数据类型的字段s_id，两个表通过s_id 字段建立联系，接下来从 fruits 表中查询 f_name、f_price 字段，从 suppliers 表中查询 s_id、s_name，SQL语句如下:

select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers 
where fruits.s_id=suppliers.s_id;

在这里，SELECT 语句与前面所介绍的一个最大的差别是，SELECT 后面指定的列分别属千两个不同的表，(f_name，f_price)在表 firuits 中，而另外两个在表 suppliers 中；同时，FROM句列出了两个表 fruits 和 suppliers；WHERE 子句作为过滤条件，指明只有两个表中的s_id字段值相等的时候才符合连接查询的条件。从返回的结果可以看到，显示的记录是由两个表中不同的列值组成的新记录。

因为 fruits 表和 suppliers 表中有相同的字段 s_id,所以在比较的时候需要完全限定表名(格式为“表名.列名”)，如果只给出 s_id，PostgreSQL将不知道指的是哪一个并返回错误信息

下面的内连接查询语句返回与前面完全相同的结果
例：在fuits 表和suppliers 表之间使用INNER JOIN语法进行内连接查询，SQL语句如下:

select suppliers.s_id,s_name,f_name,f_price 
from fruits inner join suppliers on fruits.s_id=suppliers.s_id;

在这里的查询语句中，两个表之间的关系通过INNER JOIN 指定。在使用这种语法时，连接的条件使用 ON 子句给出，而不是 WHERE，ON 和 WHERE 后面指定的条件相同。

使用 WHERE子句定义连接条件比较简单明了,而INNER JOIN 语法是ANSI SQL的标准规范，使用INNER JOIN 连接语法能够确保不会忘记连接条件，而且在某些时候会影响查询的性能。

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表

例：查询 f_id='a1' 的水果供应商提供的其他水果种类，SQL 语句如下:

select f1.f_id,f1.f_name from fruits as f1,fruits as f2 
where f1.s_id=f2.s_id and f2.f_id='a1';

外连接查询

连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅符合查询条件和连接条件的行，但有时候需要包含没有关联的行中数据，即返回到查询结果集合中不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接) 中的所有数据行。外连接分为左外连接和右外连接:

1. LEFTJOIN(左连接): 返回包括左表中的所有记录和右表中连接字段相等的记录
2. RIGHTJOIN(右连接): 返回包括表中的所有记录和左表中连接字段相等的记录

(1).LEFT JOIN 左连接

左连接的结果包括 LEFT OUTERJOIN 关键字左边连接表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择表字段均为空值。
在做外连接查询的案例之前，在数据库中创建数据表 orders，SQL语句如下:

create table orders
(
	o_num int null,
	o_date date not null,
	c_id varchar(50) not null
);

为了演示，需要插入数据，可执行以下语句:

insert into orders(o_num,o_date,c_id)
values
(30001,'2008-09-01 00:00:00','10001'),
(30002,'2008-09-12 00:00:00','10003'),
(30003,'2008-09-30 00:00:00','10004'),
(NULL,'2008-10-03 00:00:00','10002'),
(30004,'2008-10-03 00:00:00','NULL'),
(30005,'2008-10-08 00:00:00','10001');

例：在 customers 表和 orders 表中查询所有客户，包括没有订单的客户，SQL语句如下：

select customers.c_id,orders.o_num
from customers left outer join orders
on customers.c_id=orders.c_id;

 

 

(2).RIGHT JOIN 右连接

右连接是左连接的反向连接，将返回 RIGHT OUTER JOIN 关键字右边的表中的所有行如果右表的某行在左表中没有匹配行，左表将返回空值。

例：在 customers 表和 orders 表中查询所有订单，包括没有客户的订单，SQL 语句如下：

select customers.c_id,orders.o_num
from customers right outer join orders
on customers.c_id=orders.c_id;

 

复合条件连接查询

复合条件连接查询是在连接查询的过程中通过添加过滤条件来限制查询的结果,使查询的结果更加准确。
例：在 customers 表和 orders 表中，使用INNER JOIN语法查询 customers 表中ID为10001的客户的订单信息，SOL 语句如下:

select customers.c_id,orders.o_num
from customers inner join orders
on customers.c_id=orders.c_id and customers.c_id='10001';

使用连接查询，也可以对查询的结果进行排序。
例：在 fruits 表和 suppliers 表之间使用INNER JOIN 语法进行内连接查询，并对查询结果排序，SOL 语句如下:

select suppliers.s_id,s_name,f_name,f_price 
FROM fruits INNER JOIN suppliers 
ON fruits.s_id = suppliers.s_id ORDER BY fruits.s_id;

 

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 PostgreSQL4.1 开始引入。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。

查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<” “<=” “>” “>=”和“!=”等。本节将介绍如何在 SELECT语句中嵌套子查询。

带ANY、SOME 关键字的子查询

ANY和 SOME 关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
下面定义两个表 tb1和 tb2:

CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

分别向两个表中插入数据:

INSERT INTO tbl1 values(1),(5),(13),(27);
INSERT INTO tbl2 values(6),(14),(11),(20);

ANY 关键字接在一个比较操作符的后面，表示与子查询返回的任何值比较为 TRUE，则返回TRUE。

例：返回 tbl2 表的所有 num2 列，然后将 tbl1 中的 num1 的值与之进行比较，只要 num1 大于num2 的值就为符合查询条件的结果。

select num1 from tbl1 where num1 > any(select num2 from tbl2);

带 ALL 关键字的子查询

ALL 关键字与ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。例如，修改前面的例子，用 ALL 操作符替换 ANY 操作符。
ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为 TRUE，则返回TRUE。

例：返回 tbl1 表中比 tbl2 表中 num2 列所有值都大的值，SOL语句如下

select num1 from tbl1 where num1 > all(select num2 from tbl2);

带 EXISTS 关键字的子查询

EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询:如果子查询没有返回任何行，那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。

例：查询表 suppliers 表中是否存在s_id=107 的供应商，如果存在就查询 fruits 表中的记录，SOL 语句如下:

select * from fruits where exists
(select s_name from suppliers where s_id=107);

 

 

EXISTS 关键字可以和条件表达式一起使用。

例：查询表 suppliers 表中是否存在 s_id=107 的供应商，如果存在就查询 fruits 表中f_price 大于10.20的记录，SQL语句如下:

select * from fruits where f_price>10.20 and exists
(select s_name from suppliers where s_id=107);

 

NOT EXISTS 与 EXISTS 使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOT EXISTS 的结果为 false，此时外层查询语句将不进行查询: 如果子查询没有返回任何行，那么 NOT EXISTS 返回的结果是 true，此时外层语句将进行查询。

例：查询表 suppliers 表中是否存在 s_id=107 的供应商，如果不存在就查询 fruits表中的记录，SQL 语句如下:

select * from fruits where not exists
(select s_name from suppliers where s_id=107);

EXISTS和NOT EXISTS 的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

带 IN 关键字的子查询

IN 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

例：在 customers 表中查询 c_name=“RedHook” 的客户ID (c_id)，并根据c_id查询订单号 o_num，SQL 语句如下:

select o_num from orders where c_id in
(select c_id from customers where c_name='RedHook');

查询结果的o_num 有3 个值，分别为 30001、30002 和 30005。上述查询过程可以分步执行，首先内层子查询查出 customers 表中符合条件的客户 ID (c_id)，单独执行内查询:

select c_id from customers where c_name='RedHook';

可以看到，符合条件的c_id 列的值有两个(10001 和 10003)，然后执行外层查询，在 orders表中查询c_id 等于 10001 或 10003 的o_num。套子查询语句可以写为如下形式:

select o_num from orders where c_id in ('10001','10003');

这个例子说明在处理 SELECT 语句的时候，PostgreSOL 实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层子查询的结果作为外部查询的比较条件。

SELECT 语句中可以使用 NOT IN 关键字，其作用与IN 正好相反。

例：与前一个例子语句类似，但是在 SELECT 语中使用 NOT IN 操作符，SQL语句如下:

select o_num from orders where c_id not in
(select c_id from customers where c_name='RedHook');

子查询的功能也可以通过连接查询完成，但是子查询使得 PostgreSQL 代码更容易阅读和编写。

带比较运算符的子查询

在前面介绍的带 ANY、ALL 关键字的子查询时使用了>比较运算符，子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。

例：在 suppliers 表中查询 s_city 等于“Tianjin”的供应商 s_id，然后在fruits 表中查询所有该供应商提供的水果的种类，SQL 语句如下:

select s_id,f_name from fruits where s_id=
(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');

该套查询首先在 suppliers 表中查找 s_city 等于 Tianjin 的供应商的 s_id，单独执行子查询查看 s_id 的值，执行下面的操作过程:

select s1.s_id from suppliers as s1 where s1.s_city='Tianjin'

 然后在外层查询时，在 firuits 表中查找 s_id 等于 101 的供应商提供的水果的种类，SQL 语句如下:

select s_id,f_name from fruits where s_id=
(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');

结果表明，“Tianjin”地区的供应商提供的水果种类有 3 种，分别为“apple”、“blackberry”、“cherry”

例：在 suppliers 表中查询 s_city 等于“Tianjin”的供应商s_id，然后在 fruits 表中查询所有非该供应商提供的水果的种类，SQL语句如下:

select s_id,f_name from fruits where s_id<>
(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');

 

合并查询结果

利用 UNION 关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或UNION ALL 关键字分隔。UNION 不使用关键字 ALL，执行的时候删除重复的记录，所有返回的行都是唯一的:使用关键字 ALL 的作用是不删除重复行也不对结果进行自动排序。基本语法格式如下:

SELECT column,... FROM tablel
UNION [ALL]
SELECT column,.. FROM table2

例：查询所有价格小于 9 的水果的信息，查询 s_id 等于 101 和 103 所有的水果信息，使用 UNION 连接查询结果，SQL 语句如下:

select s_id,f_name,f_price from fruits 
where f_price<9.0 union
select s_id,f_name,f_price from fruits
where s_id in (101,103);

 如前所述，UNION 将多个 SELECT 语句的结果组合成一个结果集合。可以分开查看每个SELECT 语句的结果。第 1个 SELECT 语句如下:

select s_id,f_name,f_price from fruits where f_price < 9.0;

第2个SELECT 语句如下:

select s_id,f_name,f_price from fruits where s_id in (101,103);

由分开查询的结果可以看到,第1条 SELECT语查询价格小于9的水果,第2条SELECT语句查询供应商 101 和 103 提供的水果。使用UNION 将两条 SELECT 语句分隔开，执行完毕之后把输出结果组合成单个的结果集，并删除重复的记录。

使用 UNION ALL 包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录，UNION 从查询结果集中自动去除了重复的行，如果要返回所有匹配行，而不进行删除，可以使用 UNION ALL。

例：查询所有价格小于9的水果的信息，查询 s_id 等于 101 和 103 所有的水果信息，使用 UNION ALL 连接查询结果，SQL 语句如下:

select s_id,f_name,f_price from fruits 
where f_price<9.0 union all
select s_id,f_name,f_price from fruits
where s_id in (101,103);

使用 UNION ALL 的功能是不删除重复行，加上 ALL 关键字语句执行时所需要的资源少，所以尽可能使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用 UNION ALL以提高查询效率。

为表和字段取别名

在前面介绍分组查询、集合函数查询和嵌套子查询的章节中，有的地方使用 AS 关键字为查询结果中的某一列指定了一个特定的名字。在内连接查询时，则对相同的表 fruits 分别指定两个不同的名字，这里可以为字段或者表取一个别名，在查询时使用别名替代其指定的内容本节将介绍如何为字段和表创建别名以及如何使用别名。

为表取别名

当表名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名替代表原来的名称。为表取别名的基本语法格式为:

表名 [AS] 表别名

“表名”为数据库中存储的数据表的名称，“表别名”为查询时指定的表的新名称，AS 关键字为可选参数。

例：为 orders 表取别名 ，查询 30001 订单的下单日期，SOL 语句如下:

select * from orders as o where o.o_num=30001;

例：为 customers 和 orders 表分别取别名，并进行连接查询，SOL 语句如下：

select c.c_id,o.o_num from customers as c
left outer join orders as o on c.c_id=o.c_id;

由结果可以看到，PostgreSQL 可以同时为多个表取别名，而且表别名可以放在不同的位置，如WHERE 子句、SELECT 列表、ON 子句以及 ORDER BY 子句等。在前面介绍内连接查询时指出自连接是一种特殊的内连接,在连接查询中的两个表都是同个表，其查询语句如下:

select f1.f_id,f1.f_name from fruits as f1,fruits as f2
where f1.s_id=f2.s_id and f2.f_id='a1';

在这里，如果不使用表别名，PostgreSQL 将不知道引用的是哪个 fruits 表实例，这是表别名一个非常有用的地方。

在为表取别名时，要保证不能与数据库中其他表的名称冲突。

为字段取别名

在本章和前面各章节的例子中可以看到，使用 SELECT 语句显示查询结果时，PostgreSQL会显示每个 SELECT 后面指定的输出列。在有些情况下，显示的列的名称会很长或者名称不够直观，PostgreSQL 可以指定列别名，替换字段或表达式。为字段取别名的基本语法格式为:

列名 [AS] 列别名

“列名”为表中字段定义的名称，“列别名”为字段新的名称，AS 关键字为可选参数。

例：查询 fruits 表,为 f_name 取别名 fruit_name, f_price 取别名 fruit_price，为 fruits表取别名 f，查询表中f_price<8 的水果名称，SQL 语句如下:

select f1.f_name as fruit_name,f1.f_price as fruit_price 
from fruits as f1 where f1.f_price<8;

也可以为 SELECT 子句中计算字段取别名，例如对使用 COUNT 聚合函数或者 CONCA等系统函数执行的结果字段取别名。

例：查询 suppliers 表中字段 s_name 和 s_city，使用 CONCAT函数连接这两值，并取列别名为 suppliers_title。

如果没有对连接后的值取别名，其显示列名称将会不够直观，SQL 语句如下:

select concat(rtrim(s_name),'(',rtrim(s_city),')')
from suppliers order by s_name;

  

CONCAT函数用来将两个或多个字符串连接成一个;RTRIM用来将字符串右侧空格字符被删除

由结果可以看到，显示结果的列名称为 SELECT 子句后面的计算字段，实际上计算之后的列是没有名字的，这样的结果让人很不容易理解，如果为字段取一个别名，将会使结果清晰，SQL 语句如下：

select concat(rtrim(s_name),'(',rtrim(s_city),')')
as suppliers_title from suppliers order by s_name;

 

使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊的字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

PostgreSQL中正则表达式的操作符使用方法如下:

1. ~匹配正则表达式，区分大小写。
2. ~*匹配正则表达式，不区分大小写。
3. !~ 不匹配正则表达式，区分大小写相关
4. !~* 不匹配正则表达式，不区分大小写。

查询以特定字符或字符串开头的记录

字符 '^' 匹配以特定字符或者字符串开头的文本

例：在 fuits 表中，查询 f_name 字段以字母 ‘b’ 开头的记录，SQL语句如下:

select * from fruits where f_name~'^b';

例：在 fruits 表中，查询 f_name 字段以“be”开头的记录，SQL语句如下:

select * from fruits where f_name~'^be';

查询以特定字符或字符串结尾的记录

本节介绍如何查询特定字符结尾或字符串结尾的记录。

例：在 fruits 表中，查询 f_name 字段以字母“t’结尾的记录，SQL 语句如下:

select * from fruits where f_name~'t';

例：在 fruits 表中，查询 f_name 字段以字符串“rry”结尾的记录，SQL语句如下:

select * from fruits where f_name~'rry';

 

用符号"."来替代字符串中的任意一个字符

字符“.”匹配任意一个字符。

例：在 fuits 表中，查询 f_name 字段值包含字母 ‘a’ 与 ‘g’ 且两个字母之间只有一个字母的记录，SQL 语句如下:

select * from fruits where f_name~'a.g';

使用"*"和"+"来匹配多个字符

星号“*”匹配前面的字符任意多次，包括0次。加号“+”匹配前面的字符至少一次。

例：在 fuits 表中，查询 f_name 字段值以字母 ‘b’ 开头，且 ‘b’ 后面出现字母 ‘a’ 的记录，SQL 语句如下:

select * from fruits where f_name~'^ba*';

星号“*”可以匹配任意多个字符，blackberry 和 berry 中字母 b 后面并没有出现字母a，但是也满足匹配条件。

例 8.69]在 fruits 表中，查询 f_name 字段值以字母 “b” 开头，且 “b” 后面出现字母 “a”至少一次的记录，SQL 语句如下:

select * from fruits where f_name~'^ba+';

 

匹配指定字符中的任意一个

方括号“[ ]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

例：在 fruits 表中，查找 f_name 字段中包含字母 ‘o’ 或者 ‘t’ 的记录，SQL语句如下:

select * from fruits where f_name~'[ot]';

 

匹配指定字符以外的字符

“[字符集合]”匹配不在指定集合中的任何字符

例：在 fruits 表中，查询 f_id 字段不包含字母a到e 或数1到2的字符的记录SQL 语句如下:

select * from fruits where f_name!~'[a-e1-2]';

 

使用{M}或者{MN}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配 n 次前面的字符。“字符串{n,m}”表示匹配前面的字符串不少于 n 次，不多于 m 次。例如，a{2,}表示字母 a 连续出现至少 2 次,也可以大于2次; a{2,4}表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

例：在 fruits 表中，查询 f_name 字段值出现字母 ‘x’ 至少2次的记录，SQL语法如下:

select * from fruits where f_name~'x{2,}';

例：在 fruits 表中，查询 f_name 字段值出现字符串“ba”最少1次、最多3次的记录，SQL 语句如下:

select * from fruits where f_name~'ba{1,3}';

可以看到，在 f_name 字段中，ba 在 xbabay 中出现了 2 次、在 banana 中出现了 1次、在xbababa 中出现了 3 次，都满足匹配条件的记录。

PostgreSQL 11 新特性——psql 新增 gdesc 选项

有关PostgreSQL 11 新特性-psl 新增 gdesc 选项的相关内容请通过原教材《postgresql11从入门到精通》（清华大学出版社）第220页开始了解,综合案例—数据表查询操作在原书220页，常见问题及解答在原书228页，经典习题在原书229页，该部分作为自由了解范围请购买原著自行学习，感谢理解。

作者的话（Alvin）：

本章算是这本书内容最多，篇幅最长的一章，仅博客上展现的就已经高达2万多字，当然也是最重要的一章，而且内容难度也相较之前的几个章节要更难，尤其是子查询和合并查询那部分，语句长，不过只要细心的跟着每一步走我相信一定可以轻松学会，为了怕大家看不明白过程，本章基本没简略原书的步骤，甚至还比原书要多加一些步骤，sql内容我也全部按原书sql示例演示没有修改内容，原书代码已经全部运行过，没有出现错误的情况，如果想了解本章后续的内容可以购买原书。

本文根据原书《PostgreSql11 从入门到精通》（清华大学出版社）第8章总结整理，为提问与解答可以帮助更多人，本博客模拟GitHub的issue方案，所以私信已关，有问题请在评论区直接指正与提问，允许转发、复制或引用本文章，必须遵守开源法则注释来源与作者，感谢您的阅读