数据库笔记PostgreSQL里写自定义目录标题
数据库笔记PostgreSQL 13
数据库笔记
对数据库进行查询和修改操作的语言叫作SQL。SQL的含义是结构化查询语言（Structured Query Language）。
SQL语言包含4个部分。 （1）数据定义语言（DDL）：DROP、CREATE、ALTER等语句。 （2）数据操作语言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 （3）数据查询语言（DQL）：SELECT语句。 （4）数据控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK等语句。
二. 数据库运行环境
软件：PostgreSQL 13
连接工具：DBeaver
1. 创建表
1.1 基本语法
创建数据表的语句为CREATE TABLE，语法规则如下：
CREATE TABLE <表名>
(
字段1 数据类型 [列级别约束条件] [默认值],
字段2 数据类型 [列级别约束条件] [默认值],
......
[表级别约束条件]
)
使用CREATE TABLE创建表时，必须指定以下信息： （1）要创建的表名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。 （2）数据表中每一个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。
1.2 使用主键约束
（1）单字段主键
主键由一个字段组成，SQL语句格式分为以下两种情况。 （1）在定义列的同时指定主键，语法规则如下：
字段名 数据类型 PRIMARY KEY
（2）在定义完所有列之后指定主键。
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
【例】定义数据表tb_emp3，其主键为id，SQL语句如下：
CREATE TABLE tb_emp3
(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY(id)
);
上述两个例子执行后的结果是一样的，都会在id字段上设置主键约束。
（2）多字段联合主键 主键由多个字段联合组成，语法规则如下：
PRIMARY KEY [字段1, 字段2,…, 字段n]
1.3 使用外键约束
外键用来在两个表的数据之间建立链接，可以是一列或者多列。一个表可以有一个或多个外键。 外键：表中的一个字段，可以不是本表的主键，但必须对应另外一个表的主键。外键的主要作用是保证数据引用的完整性。定义外键后，不允许删除在另一个表中具有关联关系的行。 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表就是主表。 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表就是从表。 创建外键的语法规则如下：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,…]
REFERENCES <主表名> 主键列1 [ ,主键列2,…]
外键名为定义的外键约束的名称，一个表中不能有相同名称的外键；字段名表示从表需要添加外键约束的字段列；主表名，即被从表外键所依赖的表的名称；主键列表示主表中定义的主键字段或者字段组合。
1.4 使用非空约束
非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。 非空约束的语法规则如下：
字段名 数据类型 not null
1.5 使用唯一性约束
唯一性约束（Unique Constraint）要求添加该约束的列字段的值唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
使用SQL语句也可以轻松创建唯一约束，具体的语法规则如下： （1）在定义完列之后直接指定唯一约束，语法规则如下：
字段名 数据类型 UNIQUE
【例】定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下： （2）在定义完所有列之后指定唯一约束，语法规则如下：
[CONSTRAINT <约束名>] UNIQUE(<字段名>)
UNIQUE和PRIMARY KEY的区别：一个表中可以有多个字段声明为UNIQUE，但只能由一个PRIMARY KEY声明；声明为PRIMAY KEY的列不允许有空值，但是声明为UNIQUE的字段允许有空值（NULL）的存在。
1.6 使用默认约束
默认约束（Default Constraint）指定某列的默认值。例如，男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如下：
字段名 数据类型 DEFAULT 默认值
2. 修改表
2.1 修改表名
PostgreSQL是通过ALTER TABLE语句来实现表名修改的，具体的语法规则如下：
ALTER TABLE <旧表名> RENAME TO <新表名>;
2.2 修改字段的数据类型
对于创建好的字段，用户可以修改它的数据类型。使用SQL语言可以修改字段的数据类型。修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。在PostgreSQL中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> ALTER COLUMN <字段名> TYPE <数据类型>
其中，“表名”指要修改数据类型的字段所在表的名称，“字段名”指需要修改的字段，“数据类型”指修改后字段的新数据类型。
2.3 修改字段名
使用对象浏览修改字段名的方法比较简单，只需要在字段属性对话框中修改即可。下面讲述如何使用SQL语句实现修改字段名。 在PostgreSQL中修改表字段名的语法规则如下：
ALTER TABLE <表名> RENAME <旧字段名> TO <新字段名>;
其中，“旧字段名”指修改前的字段名；“新字段名”指修改后的字段名。
2.4 添加字段
随着业务需求的变化，可能需要在已经存在的表中添加新的字段。 一般情况下，一个完整字段包括字段名、数据类型、完整性约束。使用SQL语句添加字段的语法格式如下：
ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型>
2.5 删除字段
删除字段是将数据表中的某个字段从表中移除。对于不用的字段，可以进行删除操作。 删除字段的语法格式如下：
ALTER TABLE <表名><字段名>;
“字段名”指需要从表中删除的字段的名称。
2.6 删除表的外键约束
对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。 在PostgreSQL中，删除外键的语法格式如下：
ALTER TABLE <表名>CONSTRAINT <外键约束名>
3. 删除表
删除数据表是将数据库中已经存在的表从数据库中删除。注意，删除表的同时，表的定义和表中所有的数据均会被删除，因此，在删除操作前，最好对表中的数据做个备份，以免造成无法挽回的后果。
3.1 删除没有被关联的表
在PostgreSQL中，使用TABLE可以一次删除一个或多个没有被其他表关联的数据表。语法格式如下：
TABLE [IF EXISTS]表1, 表2, ... ,表n;
其中，“表n”是指要删除的表的名称，后面可以同时删除多个表，只需要将要删除的表名依次写在后面，相互之间用逗号隔开即可。如果要删除的数据表不存在，就会提示错误信息。 参数“IF EXISTS”用于在删除前判断删除的表是否存在，加上该参数后，再删除表的时候，如果表不存在，SQL语句可以顺利执行，但是会发出警告信息。
3.2 删除被其他表关联的主表
在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须要删除，可以先删除与之关联的子表，再删除父表。但是这样同时删除了两个表中的数据。有的情况下可能要保留子表，这时若要单独删除父表，只需将关联的表的外键约束条件取消，就可以删除父表了。 两个关联表tb_dept2和tb_emp，其中，tb_emp表为子表，具有名称为fk_emp_dept外键约束；tb_dept2为父表，其主键id被子表tb_emp所关联。 首先，解除关联子表tb_emp的外键约束，SQL语句如下：
ALTER TABLE tb_empCONSTRAINT fk_emp_dept;
语句成功执行后，将取消表tb_emp和tb_dept2之间的关联关系。此时，可以输入删除语句，将原来的父表tb_dept2删除，SQL语句如下：
TABLE tb_dept2;
- 教程列表完成本章学习
数据类型和运算符
1. 整数类型
数值型数据类型主要用来存储数字。PostgreSQL提供多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间越大。PostgreSQL主要提供的整数类型有SMALLINT，INT（INTEGER）和BIGINT。
| 类型名称 | 说明 | 存储空间 |
|---|---|---|
| SMALLINT | 小范围的整数 | 2字节 |
| INT(INTEGER) | 普通大小的整数 | 4字节 |
| BIGINT | 大整数 | 8字节 |
从中可以看到，不同类型整数存储所需的字节数是不同的，占用字节数最小的是MALLINT类型，占用字节最大的是BITINT类型。相应的，占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据类型的取值范围，例如SMALLINT需要2字节（16 bits）来存储，那么MALLINT的最大值为215-1，即32767。其他类型的整数的取值范围计算方法相同。
| 数据类型 | 取值范围 |
|---|---|
| SMALLINT | -23768到32767 |
| INT(INTEGER) | -2147483648到2147483647 |
| BIGINT | -9223372036854775808到9223372036854775807 |
2. 浮点数类型
PostgreSQL中使用浮点数来表示小数。浮点类型有两种：REAL和DOUBLE PRECISION。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| REAL | 6位十进制数字精度 | 4字节 |
| DOUBLE PRECISION | 15位十进制数字精度 | 8字节 |
在大多数系统平台上，REAL类型的范围是1E-37到1E+37，精度至少是6位小数。DOUBLE PRECISION的范围通常是1E-307到1E+308，精度至少是15位数字，太大或者太小的数值都会导致错误。 PostgreSQL也支持SQL标准表示法float和float§，用于声明非精确的数值类型。其中，p声明以二进制位表示的最低可接受精度。在选取real类型的时候，PostgreSQL接受float(1)到float(24)，在选取double precision的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误。没有声明精度的float将被当作double precision。 在PostgreSQL中，在浮点类型中有几个特殊值：Infinity表示正无穷大，-Infinity表示负无穷大，NaN表示不是一个数字。
3. 任意精度类型
在PostgreSQL中，使用NUMERIC（M，N）表示任意精度类型的数值。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。例如，563.186的精度为6、标度为3。 NUMERIC的有效取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。另外，如果用户指定的精度超出精度外，就会进行四舍五入处理。
4. 日期与时间类型
PostgreSQL中有多种表示日期的数据类型，主要有TIME、DATE、TIMESTAMP和INTERVAL。每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入数据库中。
| 类型名称 | 含义 | 日期范围 | 存储需求 |
|---|---|---|---|
| TIME | 只用于一日内的时间 | 00:00:00~24:00:00 | 8字节 |
| DATE | 只用于日期 | 4713 BC~5874897 AD | 4字节 |
| TIMESTAMP | 日期和时间 | 4713 BC~5874897 AD | 8字节 |
提 示： 在格里高利历法里没有零年，所以数字上的1BC是公元零年。 另外，对于TIME和TIMESTAMP类型，默认情况下为without time zone（不带时区），如果需要，可以设置为带时区（with time zone）。
4.1 TIME
TIME类型用于只需要时间信息的值，在存储时需要8字节，格式为HH:MM:SS。其中，HH表示小时；MM表示分钟；SS表示秒。TIME类型的取值范围为00:00:00~24:00:00。
4.2 DATE类型
DATE类型用在仅需要日期值时，没有时间部分，在存储时需要4字节，日期格式为‘YYYY-MM-DD’。其中，YYYY表示年；MM表示月；DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可。 （1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期。例如，输入‘2012-12-31’或者‘20121231’，插入数据库的日期都为2012-12-31。 （2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期。在这里，YY表示两位的年值，包含两位年值的日期会令人模糊，因为不知道世纪。PostgreSQL使用以下规则解释两位年值：‘0069’范围的年值转换为‘20002069’；‘7099’范围的年值转换为‘19701999’。例如，输入‘12-12-31’，插入数据库的日期为2012-12-31；输入‘981231’，插入数据的日期为1998-12-31。 （3）利用CURRENT_DATE或者NOW()插入当前系统日期。
4.3 TIMESTAMP
TIMESTAMP的日期格式为YYYY-MM-DD HH:MM:SS。在存储时需要8字节，因此在插入数据时要保证在合法的取值范围内。
5. 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外还可以进行模式匹配查找。在PostgreSQL中，字符串类型是指CHAR、VARCHAR和TEXT。
5.1 CHARACTER(n)和CHARACTER VARYING(n)
其中，n是一个正整数。CHARACTER(n)和CHARACTER VARYING(n)都可以存储最多n个字符的字符串。试图存储更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断为最大长度。如果要存储的字符串比声明的长度短，类型为character的数值将会用空白填满；而类型为CHARACTER VARYING的数值将只存储短些的字符串。
5.2 TEXT类型
PostgreSQL中的TEXT类型可以存储任何长度的字符串。尽管类型TEXT不是SQL标准，但是许多其他SQL数据库系统中也有。
6. 二进制类型
PostgreSQL支持两类字符型数据：文本字符串和二进制字符串。 PostgreSQL提供了BYTEA类型，用于存储二进制字符串。BYTEA类型存储空间为4字节加上实际的二进制字符串。
7. 布尔类型
PostgreSQL提供了BOOLEAN布尔数据类型。BOOLEAN用1字节来存储，提供了TRUE（真）和FALSE（假）两个值。 另外，用户可以使用其他有效文本值替代TRUE和FALSE。替代TRUE的文本值为’t’、‘true’、‘y’、‘yes’和’1’，替代FALSE的文本值为’f’、‘false’、‘n’、‘no’和’0’。
8. 数组类型
PostgreSQL允许将字段定义成定长或变长的一维或多维数组。数组类型可以是任何基本类型或用户定义类型。
-
声明数组 在PostgreSQL中，一个数组类型是通过在数组元素类型名后面附加方括弧来命名的。例如： 其中，numb字段为一维INT数组，xuehao字段为二维TEXT数组，zuoye字段为二维TEXT数组，并且声明了数组的长度。不过，目前PostgreSQL并不强制数组的长度，所以声明长度和不声明长度是一样的。 另外，对于一维数组，也可以使用SQL标准声明，SQL语句如下：
PAY_BY_QUARTER INT ARRAY[5];
此种声明方式仍然不强制数组的长度。
-
插入数组数值 插入数组元素时，用大括号把数组元素括起来并且用逗号将它们分开。 【例】创建表tmp12，定义数组类型的字段bt，向表中插入一些数组数值。 创建表tmp12，SQL语句如下：
CREATE TABLE tmp12( bt int[]);
插入数据：
tmp12 VALUES('{{1,1,1},{2,2,2},{3,3,3}}');
9. 常见运算符
运算符连接表达式中的各个操作数，用来指明对操作数所进行的运算。常见的运算有数学运算、比较运算、位运算或者逻辑运算。利用运算符可以更加灵活地使用表中的数据。常见的运算符类型有算术运算符、比较运算符、逻辑运算符和位运算符。
9.1 算数运算符
用于各类数值运算，包括加（+）、减（-）、乘（*）、除（/）、求余（或称模运算，%）。
9.2 比较运算符
比较运算符用于比较运算，包括大于（>）、小于（<）、等于（=）、大于等于（>=）、小于等于（<=）、不等于（!=）以及IN、BETWEEN AND、GREATEST、LEAST、LIKE等。
9.3 逻辑运算符
逻辑运算符的求值所得结果均为t（TRUE）、f（FALSE）。这类运算符有逻辑非（NOT）、逻辑与（AND）、逻辑或（OR）。
9.4 位操作运算符
参与位操作运算的操作数，按二进制位进行运算。位操作运算符包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移（>>）6种。
教程
函数表示对输入参数值返回一个具有特定关系的值，PostgreSQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，可以更加灵活地满足不同用户的需求。各类函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。
1. 数学函数
数学函数主要用来处理数值数据，主要的数学函数有绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在有错误产生时，数学函数将会返回空值NULL。
1.1 绝对值函数和返回圆周率的函数
ABS(x)返回x的绝对值。 【例】求2、-3.3和-33的绝对值，输入语句如下：
ABS(2), ABS(-3.3), ABS(-33);
正数的绝对值为其本身，所以2的绝对值为2；负数的绝对值为其相反数，所以-3.3的绝对值为3.3、-33的绝对值为33。 PI()返回圆周率π的值，默认的显示小数位数是6位。 【例】返回圆周率值，输入语句如下：
pi();
返回结果保留了15位有效数字。
1.2 获取整数的函数
CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。 【例】使用CEIL和CEILING函数返回最小整数，输入语句如下：
CEIL(-3.35),CEILING(3.35);
-3.35为负数，不小于-3.35的最小整数为-3，因此返回值为-3；不小于3.35的最小整数为4，因此返回值为4。 FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。 【例】使用FLOOR函数返回最大整数，输入语句如下：
FLOOR(-3.35), FLOOR(3.35);
-3.35为负数，不大于-3.35的最大整数为-4，因此返回值为-4；不大于3.35的最大整数为3，因此返回值为3。
1.3 四舍五入函数
ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。 【例】使用ROUND(x)函数对操作数进行四舍五入操作，输入语句如下：
ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);
ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。 【例】使用ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位，输入语句如下：
ROUND(1.38, 1), ROUND(1.38, 0), ROUND(232.38, -1), ROUND (232.38,-2);
ROUND(1.38, 1)保留小数点后面1位，四舍五入的结果为1.4；ROUND(1.38, 0)保留小数点后面0位，即返回四舍五入后的整数值；ROUND(23.38, -1)和ROUND (232.38,-2)分别保留小数点左边1位和2位。
提示： y值为负数时，保留的小数点左边的相应位数直接保存为0，同时进行四舍五入。
1.4 符号函数
SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。 【例】使用SIGN函数返回参数的符号，输入语句如下：
SIGN(-21),SIGN(0), SIGN(21);
SIGN(-21)返回-1；SIGN(0)返回0；SIGN(21)返回1。
2. 字符串函数
字符串函数主要用来处理数据库中的字符串数据。
2.1 计算字符串字符数和字符串长度
CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。 【例】使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：
CHAR_LENGTH('date'), CHAR_LENGTH('egg');
LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。 【例】使用LENGTH函数计算字符串长度，输入语句如下：
LENGTH('date'), LENGTH('egg');
计算的结果与CHAR_LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占一个字节。
2.2 合并字符串
CONCAT(s1,s2,…)的返回结果为连接参数产生的字符串。若有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。 【例】使用CONCAT函数连接字符串，输入语句如下：
CONCAT('PostgreSQL', '11.2'),CONCAT('Postgre',NULL, 'SQL');
CONCAT(‘PostgreSQL’, ‘11.2’)返回两个字符串连接后的字符串；CONCAT(‘Postgre’,NULL,‘SQL’)中有一个参数为NULL，因此合并的时候忽略不计。 在CONCAT_WS(x,s1,s2,…)中，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。 【例】使用CONCAT_WS函数连接带分隔符的字符串，输入语句如下：
CONCAT_WS('-', '1st','2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
CONCAT_WS(‘-’, ‘1st’,‘2nd’, ‘3rd’)使用分隔符‘-’将3个字符串连接成一个字符串，结果为“1st-2nd-3rd”；CONCAT_WS(‘’, ‘1st’, NULL, ‘3rd’)使用分隔符‘’将两个字符串连接成一个字符串，同时忽略NULL值。
2.3 获取指定长度的字符串
LEFT(s,n)返回字符串s最左边的n个字符。 【例】使用LEFT函数返回字符串中左边的字符，输入语句如下：
LEFT('football', 5);
函数返回字符串“football”从左边开始长度为5的子字符串，结果为“footb”。 RIGHT(s,n)返回字符串s最右边的n个字符。 【例】使用RIGHT函数返回字符串中右边的字符，输入语句如下：
RIGHT('football', 4);
函数返回字符串“football”从右边开始长度为4的子字符串，结果为“ball”。
2.4 填充字符串
LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填充，填充长度为len。假如s1的长度大于len，则返回值被缩短至len字符。 【例】使用LPAD函数对字符串进行填充操作，输入语句如下：
LPAD('hello',4,'??'), LPAD('hello',10,'??');
字符串“hello”长度大于4，不需要填充，因此LPAD(‘hello’,4,‘??’)只返回被缩短的长度为4的子串“hell”；字符串“hello”长度小于10，LPAD(‘hello’,10,‘??’)返回结果为“???hello”，左侧填充‘？’，长度为10。 RPAD(s1,len,s2)返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到与len字符相同的长度。 【例】使用RPAD函数对字符串进行填充操作，输入语句如下：
RPAD('hello',4,'?'), RPAD('hello',10,'?');
字符串“hello”长度大于4，不需要填充，因此RPAD(‘hello’,4,‘??’)只返回被缩短的长度为4的子串“hell”；字符串“hello”长度小于10，RPAD(‘hello’,10,‘??’)返回结果为“hello???”，右侧填充‘？’，长度为10。
2.5 删除空格
LTRIM(s)返回字符串s，字符串左侧空格字符被删除。 【例】使用LTRIM函数删除字符串左边的空格，输入语句如下：
'( book )',CONCAT('(',LTRIM(' book '),')');
LTRIM只删除字符串左边的空格，而右边的空格不会被删除，“ book ”删除左边空格之后的结果为‘book ’。 RTRIM(s)返回字符串s，字符串右侧空格字符被删除。 【例】使用RTRIM函数删除字符串右边的空格，输入语句如下：
'( book )',CONCAT('(', RTRIM (' book '),')');
RTRIM只删除字符串右边的空格，左边的空格不会被删除，“ book ”删除右边空格之后的结果为“ book”。 TRIM(s)删除字符串s两侧的空格。 【例】使用TRIM函数删除指定字符串两端的空格，输入语句如下：
'( book )',CONCAT('(', TRIM(' book '),')');
2.6 删除指定字符串
TRIM(s1s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。 【例】使用TRIM(s1s)函数删除字符串中两端指定的字符，输入语句如下：
TRIM('xy''xyboxyokxyxy') ;
删除字符串“xyboxyokxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，结果为“boxyok”。
2.7 重复生成字符串
REPEAT(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0，则返回一个空字符串；若s或n为NULL，则返回NULL。 【例】使用REPEAT函数重复生成相同的字符串，输入语句如下：
REPEAT('PostgreSQL', 3);
REPEAT(‘PostgreSQL’, 3)函数返回的字符串由3个重复的“PostgreSQL”字符串组成。
2.8 替换函数
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。 【例】使用REPLACE函数进行字符串替代操作，输入语句如下：
REPLACE('xxx.PostgreSQL.com', 'x', 'w');
REPLACE(‘xxx.PostgreSQL.com’, ‘x’, ‘w’)将“xxx.PostgreSQL.com”字符串中的‘x’字符替换为‘w’字符，结果为“www.PostgreSQL.com”。
2.9 获取子串
SUBSTRING(s,n,len)表示从字符串s返回一个长度为len的子字符串，起始于位置n。也可能对n使用一个负值，假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符。 【例】使用SUBSTRING函数获取指定位置处的子字符串，输入语句如下：
SUBSTRING('breakfast',5) AS col1,
SUBSTRING('breakfast',5,3) AS col2,
SUBSTRING('lunch',-3) AS col3;
SUBSTRING(‘breakfast’,5)返回从第5个位置开始到字符串结尾的子字符串，结果为“kfast”；SUBSTRING(‘breakfast’,5,3)返回从第5个位置开始长度为3的子字符串，结果为“kfa”；SUBSTRING(‘lunch’, -3)返回整个字符串。
提示: 如果对len使用的是一个小于1的值，则结果始终为整个字符串。
2.10 匹配子串开始位置
POSITION(str1 IN str)函数的作用是返回子字符串str1在字符串str中的开始位置。 【例】使用POSITION函数查找字符串中指定子字符串的开始位置，输入语句如下：
POSITION('ball'IN 'football');
子字符串“ball”在字符串“football”中从第5个字母位置开始，因此函数返回结果为5。
2.11 字符串逆序
REVERSE(s)将字符串s反转，返回的字符串顺序和s字符串顺序相反。 【例】使用REVERSE函数反转字符串，输入语句如下：
REVERSE('abc');
由结果可以看到，字符串“abc”经过REVERSE函数处理之后所有字符串顺序被反转，结果为“cba”。
3. 日期和时间函数
日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分。许多日期函数可以同时接受数字和字符串类型的两种参数。
3.1 获取当前日期和当前时间
CURRENT_DATE函数的作用是将当前日期按照‘YYYY-MM-DD’格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。 【例】使用日期函数获取系统当前日期，输入语句如下：
CURRENT_DATE;
CURRENT_TIME函数的作用是将当前时间以‘HH:MM:SS’的格式返回，具体格式根据函数用在字符串或是数字语境中而定。 【例】使用时间函数获取系统当前日期，输入语句如下：
CURRENT_TIME;
LOCALTIME函数的作用是将当前时间以‘HH:MM:SS’的格式返回，唯一和CURRENT_TIME函数不同的是返回时不带时区值。 【例】使用时间函数获取系统当前日期，输入语句如下：
LOCALTIME;
CURRENT_TIMESTAMP、LOCALTIMESTAMP和NOW()3个函数的作用相同，返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数是否用在字符串或数字语境中而定。 【例】使用日期时间函数获取当前系统日期和时间，输入语句如下：
CURRENT_TIMESTAMP,LOCALTIMESTAMP,NOW();
3个函数返回的日期和时间是相同的。唯一不同的是，LOCALTIMESTAMP函数的返回值不带时区。
3.2 获取日期指定值
EXTRACT(typedate)函数从日期中提取其部分，而不是执行日期运算。 【例】使用EXTRACT函数从日期中提取一个月中的第几天，输入语句如下：
EXTRACT(DAYTIMESTAMP '2017-09-10 10:18:40');
【例】使用EXTRACT函数从日期中提取月份，输入语句如下：
EXTRACT(MONTHTIMESTAMP '2019-09-10 10:18:40');
【例】使用EXTRACT函数从日期中提取年份，输入语句如下：
EXTRACT(YEARTIMESTAMP '2019-09-10 10:18:40');
【例】使用EXTRACT函数查询指定日期是一年中的第几天，输入语句如下：
EXTRACT(DOYTIMESTAMP '2019-09-10 10:18:40');
【例】使用EXTRACT函数查询指定日期是一周中的星期几，输入语句如下：
EXTRACT(DOWTIMESTAMP '2019-09-10 10:18:40');
2019-09-10是星期日。需要读者注意的是，此函数的星期编号为0~6，星期日的返回结果为0。 【例】使用EXTRACT函数查询指定日期是该年的第几季度（1~4），输入语句如下：
EXTRACT(QUARTERTIMESTAMP '2019-09-10 10:18:40');
从结果可以看出，2019-09-10是该年中的第3季度。
4. 条件判断函数
条件判断函数亦称为控制流程函数，根据满足的条件而执行相应的流程。在PostgreSQL中，进行条件判断的函数为CASE。
4.1 CASE exprv1r1 [WHEN v2r2] [ELSE rn] END
该函数表示，如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn。 【例】使用CASE value WHEN语句执行分支操作，输入语句如下：
CASE 21'one'2'two''more' END;
CASE后面的值为2，与第二条分支语句WHEN后面的值相等，因此返回结果为“two”。
4.2 CASEv1r1 [WHEN v2r2]rn] END
该函数表示，某个vn值为TRUE时，返回对应位置THEN后面的结果；如果所有值都不为TRUE，则返回ELSE后的rn。 【例】使用CASE WHEN语句执行分支操作，输入语句如下：
CASE1<0'true''false' END;
1<0的结果为FALSE，因此函数返回值为ELSE后面的“false”。提 示 一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，具体情况视其所在语境而定。用在字符串语境中，返回结果为字符串；用在数字语境中，返回结果为十进制值、实值或整数值。
5. 改变数据类型的函数
CAST(x , AS type)函数将一个类型的值转换为另一个类型的值。 【例】使用CAST函数进行数据类型的转换，SQL语句如下：
CAST(100 AS CHAR(2));
可以看到，CAST(100 AS CHAR(2))将整数数据100转换为带有2个显示宽度的字符串类型，结果为‘10’。
六. 数据的插入、更新和删除操作
1. 插入数据
1.1 为表的所有字段插入数据
使用基本的INSERT语句插入数据要求指定表名称和插入到新记录中的值，基本语法格式为：
table_name (column_list) VALUES (value_list);
table_name指定要插入数据的表名，column_list指定要插入数据的列，value_list指定每个列应对应插入的数据。注意，使用该语句时字段列和数据值的数量必须相同。
1.2 为表的指定字段插入数据
为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。 【例】在person表中，插入一条新记录，id值为4，name值为Laura，age值为25，SQL语句如下：
person (id,name, age ) VALUES (4,'Laura', 25);
在本例插入语句中没有指定info字段值，查询结果显示info字段在定义时指定默认为NULL，因此系统自动为该字段插入空值。
提示：
要保证每个插入值的类型必须和对应列的数据类型匹配，如果类型不同，就无法插入，并且PostgreSQL会产生错误。
1.3 同时插入多条记录
INSERT语句可以同时向数据表中插入多条记录。插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：
表名 (属性列表)
VALUES (取值列表1),(取值列表2)
...,
( 取值列表n);
其中，“表名”为需要插入数据的表的名称；“属性列表”为可选参数，指定向哪些字段插入数据，如果没有指定字段，那么默认向所有字段插入数据；“取值列表n”参数表示要插入的记录，各个记录之间用逗号隔开。 【例】在person表中，在id、name、age和info字段指定插入值，同时插入3条新记录，SQL语句如下：
person(id,name, age, info)
VALUES (5,'Evans',27, 'secretary'),
(6,'Dale',22, 'cook'),
(7,'Edison',28, 'singer');
使用INSERT同时插入多条记录时，PostgreSQL会返回一些在执行单行插入时没有的额外信息，这些包含数值的字符串的意思分别是： （1）Records表明插入的记录条数。 （2）Duplicates表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。 （3）Warnings表明有问题的数据值，例如发生数据类型转换。
1.4 将查询结果插入到表中
INSERT语句用来给数据表插入记录时指定插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到表中，如果想要从另外一个表中合并个人信息到person表，不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表向一个表中插入多行。基本语法格式如下：
table_name1 (column_list1)
(column_list2)table_name2(condition)
table_name1指定待插入数据的表；column_list1指定待插入表中要插入数据的列；table_name2指定插入数据是从哪个表中查询出来的；column_list2指定数据来源表的查询列，该列表必须和column_list1列表中字段个数相同，数据类型相同；condition指定SELECT语句的查询条件。 【例】从person_old表中查询所有的记录，并将其插入到person表中。 创建一个名为person_old的数据表，其表结构与person结构相同，SQL语句如下： 向person_old表中添加两条记录：
person_old
VALUES (10,'Harry',20, 'student'),
(11,'Beckham',31, 'police');
查询插入的记录：
*person_old;
接下来将person_old表中所有的记录插入person表中，SQL语句如下：
person(id, name, age, info)
id, name, age, infoperson_old;
由结果可以看到，INSERT语句执行后person表中多了两条记录，这两条记录和person_old表中的记录完全相同，数据转移成功。这里的id字段为主键，在插入的时候要保证该字段值的唯一性。
提示： 使用上述方法时，必须保证两个表的字段类型相同，否则系统会报错，不能完成插入操作。
2. 更新数据
表中有数据之后，接下来可以对数据进行更新操作。在PostgreSQL中，使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行，基本语法结构如下：
table_name
SET column_name1 = value1,column_name2=value2,…,column_namen=valuen
(condition);
column_name1,column_name2,…,column_namen为指定更新的字段的名称；value1,value2,…,valuen为相对应的指定字段的更新值；condition指定更新的记录需要满足的条件。更新多个列时，每个“列-值”对之间用逗号隔开，最后一列之后不需要逗号。
3. 删除数据
从数据表中删除数据使用DELETE语句。DELETE语句允许WHERE子句指定删除条件。DELETE语句的基本语法格式如下：
FROM table_name [WHERE <condition>];
table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。 提示： 在执行删除记录操作中并没有任何提示，所有用户在删除记录时要特别小心，最好先用SELECT语句查看需要删除的记录，以确认这些记录是否真的需要删除。
提示： 如果想删除表中的所有记录，还可以使用TABLE语句。TRUNCATE将直接删除原来的表并重新创建一个表，其语法结构为TABLE表名。TRUNCATE直接删除表，而不是删除记录，因此执行速度比DELETE快。
1. 基本查询语句
PostgreSQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：
SELECT
{* | <字段列表>}
[
FROM <表1>,<表2>...
[WHERE <表达式>]
[GROUP BY <condition>]
[HAVING <expression>]
[ORDER BY <expression>]
[LIMIT [<offset>,] <row count>]
]
{* | <字段列表>}：包含星号通配符和选字段列表，‘*’表示查询所有的字段，‘字段列表’表示查询指定的字段，字段列至少包含一个子段名称。如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号。 在FROM <表1>,<表2>…中，表1和表2表示查询数据的来源，可以是单个或者多个。 WHERE子句是可选项，如果选择该项，［查询条件］将限定查询行必须满足的查询条件。 GROUP BY <字段>：该子句告诉PostgreSQL如何显示查询出来的数据，并按照指定的字段分组。 [ORDER BY <字段>]：该子句告诉PostgreSQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。 [LIMIT [,] ]：该子句告诉PostgreSQL每次显示查询出来的数据条数。
2. 单表查询
单表查询是只从一张数据表中查询所需的数据。本节将介绍单表查询中的各种基本查询方式，主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询以及对查询结果进行排序等。
2.1 查询所有字段
-
在SELECT语句中使用星号“”通配符查询所有字段 SELECT查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号（）通配符指定查找所有的列的名称。语法格式如下：
*表名; -
在SELECT语句中指定所有字段 另外一种查询所有字段值的方法是在SELECT语句中指定所有字段。根据前面介绍的SELECT语句格式，SELECT关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在SELECT子句右边。例如，查询fruits表中的所有数据，SQL语句也可以书写如下：
f_id, s_id ,f_name, f_pricefruits;
一般情况下，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询的效率和所使用的应用程序的效率。
2.2 查询指定字段
-
查询单个字段 查询表中的某一个字段，语法格式为：
列名FROM 表名;
【例】查询当前表中f_name列的所有水果名称，SQL语句如下：
f_namefruits;
该语句使用SELECT声明从fruits表中获取名称为f_name字段下的所有水果名称，指定字段的名称紧跟在SELECT关键字之后。输出结果显示了fruits表中f_name字段下的所有数据。
-
查询多个字段 使用SELECT语句，可以获取多个字段下的数据，只需要在关键字SELECT后面指定要查找的字段的名称即可。不同字段名称之间用逗号（，）分隔开，最后一个字段后面不需要加逗号。语法格式如下：
字段名1,字段名2,…,字段名n表名;
【例】从fruits表中获取f_name和f_price两列，SQL语句如下：
f_name, f_pricefruits;
该语句使用SELECT声明从fruits表中获取名称为f_name和f_price两个字段下的所有水果名称和价格，两个字段之间用逗号分隔开。
提示： PostgreSQL中的SQL语句是不区分大小写的，因此SELECT和select的作用相同。许多开发人员习惯将关键字大写、数据列和表名小写。读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。
2.3 查询指定记录
数据库中包含大量的数据，根据特殊要求，可能只需查询表中的指定数据，即对数据进行过滤。在SELECT语句中可通过WHERE子句对数据进行过滤，语法格式为：
字段名1,字段名2,…,字段名n
表名
查询条件
在WHERE子句中，PostgreSQL提供了一系列的条件判断符，具体含义如表8.1所示。表8.1 WHERE条件判断符 【例】查询价格为10.2元的水果名称，SQL语句如下：
f_name, f_pricefruitsf_price = 10.2;
2.4 带IN关键字的查询
IN操作符用来查询满足指定条件范围内的记录。使用IN操作符时，将所有检索条件用括号括起来，检索条件用逗号分隔开，只要满足条件范围内的一个值即为匹配项。 【例】查询s_id为101和102的记录，SQL语句如下：
s_id,f_name, f_pricefruits
s_id IN (101,102) ORDER BY f_name;
相反，可以使用关键字NOT来检索不在条件范围内的记录。 【例】查询所有s_id不等于101也不等于102的记录，SQL语句如下：
s_id,f_name, f_pricefruits
s_id NOT IN (101,102) ORDER BY f_name;
2.5 带BETWEEN AND的范围查询
BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果记录的字段值满足指定的范围查询条件，则这些记录被返回。 【例】查询价格在2.00元到10.20元之间水果的名称和价格，SQL语句如下：
f_name, f_pricefruits
f_price BETWEEN 2.0010.20;
BETWEEN匹配范围中的所有值，包括开始值和结束值。 BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定范围内的值，则这些记录被返回。 【例】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：
f_name, f_pricefruits
f_price NOT BETWEEN 2.0010.20;
2.6 带LIKE的字符匹配查询
通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持通配符，可以和LIKE一起使用的通配符是‘%’。 【例】在fruits表中，查询f_name中包含字母‘g’的记录，SQL语句如下：
f_id, f_namefruits
f_name'%g%';
该语句查询包含字符串中包含字母‘g’的水果名称，只要名字中有字母‘g’，不管前面或后面有多少个字符都满足查询的条件。
2.7 查询空值
数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串，一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。 【例】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
c_id, c_name,c_emailcustomersc_email IS NULL;
可以看到，显示customers表中字段c_email的值为NULL的记录，满足查询条件。 与IS NULL相反的是IS NOT NULL，该关键字查找字段不为空的记录。 【例】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
c_id, c_name,c_emailcustomersc_email IS NOT NULL;
2.8 带AND的多条件查询
使用SELECT查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。PostgreSQL在WHERE子句中使用AND操作符限定只有必须满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。 【例】在fruits表中查询s_id = 101并且f_price大于5的记录价格和名称，SQL语句如下：
s_id, f_price, f_namefruits
s_id = '101'f_price >=5;
前面的语句检索了s_id=101的水果供应商所有价格大于等于5元的水果名称和价格。WHERE子句中的条件分为两部分，AND关键字指示PostgreSQL返回所有同时满足两个条件的行。即使是s_id=101的水果供应商提供的水果，如果价格<5，或者是s_id不等于101的水果供应商里的水果不管其价格为多少，均不是要查询的结果。
提示： 上述例子的WHERE子句中只包含了一个AND语句，把两个过滤条件组合在一起。实际上可以添加多个AND过滤条件，增加条件的同时增加一个AND关键字。
2.9 带OR的多条件查询
与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。 【例】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
s_id,f_name, f_pricefruits
s_id = 101s_id = 102;
在这里，也可以使用IN操作符实现与OR相同的功能，可通过下面的例子进行说明。 【例】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
s_id,f_name, f_pricefruitss_id IN(101,102);
提示： OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
2.10 查询结果去重
有时出于对数据分析的要求，需要消除重复的记录值，如何使查询结果没有重复呢？在SELECT语句中可以使用DISTINCT关键字指示PostgreSQL消除重复的记录值。语法格式为：
DISTINCT 字段名表名;
【例】查询fruits表中s_id字段的值，并返回s_id字段值不得重复，SQL语句如下：
DISTINCT s_idfruits;
2.11 对查询结果排序
-
单列排序 【例】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：
f_namefruits ORDER BY f_name;
该语句查询的结果和前面的语句相同，不同的是，通过指定ORDER BY子句，PostgreSQL对查询的f_name列的数据按字母表的顺序进行了升序排序。
-
多列排序 有时需要根据多列值进行排序，比如显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，只要将需要排序的列之间用逗号隔开即可。 【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：
f_name, f_pricefruits ORDER BY f_name, f_price;
提示： 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中的所有值都是唯一的，就将不再对第二列进行排序。
-
指定排序方向 默认情况下，查询数据按字母升序进行排序（从A到Z），但数据的排序并不仅限于此，还可以使用ORDER BY对查询结果进行降序排序（从Z到A）。降序通过关键字DESC实现，如下面的例子所示。 【例8.22】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：
f_name, f_pricefruits ORDER BY f_price DESC;
提示： 与DESC相反的是ASC（升序排序），将字段列中的数据按字母表顺序升序排序。实际上，在排序的时候ASC是默认的排序方式，所以加不加都可以。 也可以对多列进行不同的顺序排序。 【例】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
f_price, f_namefruits ORDER BY f_price DESC, f_name;
提示： DESC关键字只对其前面的列降序排列，在这里只对f_price排序，而并没有对f_name进行排序，因此，f_price按降序排序，而f_name列仍按升序排序。如果要对多列都进行降序排序，必须要在每一列的列名后面加DESC关键字。
2.12 分组查询
分组查询是对数据按照某个或多个字段进行分组。在PostgreSQL中，使用GROUP BY关键字对数据进行分组，基本语法形式为：
[GROUP BY 字段] [HAVING <条件表达式>]
“字段”表示进行分组时所依据的列名称；“HAVING <条件表达式>”指定GROUP BY分组显示时需要满足的限定条件。
-
创建分组 GROUP BY关键字通常和集合函数一起使用，例如MAX()、MIN()、COUNT()、SUM()、AVG()。例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。 【例8.24】根据s_id对fruits表中的数据进行分组，SQL语句如下：
s_id, COUNT(*) AS Totalfruits GROUP BY s_id; -
使用HAVING过滤分组 GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。 【例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：
s_id,COUNT(f_name)fruits GROUP BY s_id HAVING COUNT(f_name)>1;提示： HAVING关键字与WHERE关键字都是用来过滤数据的，两者有什么区别呢？其中重要的一点是，HAVING在数据分组之后进行过滤，用来选择分组；WHERE在分组之前用来选择记录。另外，WHERE排除的记录不再包括在分组中。
-
某些情况下需要对分组进行排序。在前面的介绍中，ORDER BY用来对查询的记录排序。如果和GROUP BY一起使用可以完成对分组的排序。 【例】根据s_id对fruits表中的数据进行分组，显示水果种类大于1的分组信息，并按照水果的种类排序，SQL语句如下：
s_id,COUNT(f_name)fruits GROUP BY s_id HAVING COUNT(f_name)>1 ORDER BY COUNT(f_name);
2.13 用LIMIT限制查询结果
SELECT将返回所有匹配的行，可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT关键字，基本语法格式如下：
LIMIT行数[位置偏移量,]
“位置偏移量”参数指示PostgreSQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；参数“行数”指示返回的记录条数。 【例】显示fruits表查询结果的前4行。
查询fruits表的前4行：
*fruits LIMIT 4;
由结果可以看到，该语句没有指定返回记录的“位置偏移量”参数，显示结果从第一行开始，“行数”参数为4，因此返回的结果为表中的前4行记录。 如果指定返回记录的开始位置，则返回结果为从“位置偏移量”参数开始的指定行数，“行数”参数指定返回的记录条数。 【例】在fruits表中，使用LIMIT子句，返回从第5个记录开始的行数长度为3的记录，SQL语句如下：
*fruits LIMIT 3 OFFSET 4;
3. 使用集合函数查询
3.1 COUNT函数
COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种： （1）COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。 （2）COUNT（字段名）计算指定列下总的行数，计算时将忽略字段值为空值的行。 【例8.29】查询customers表中总的行数，SQL语句如下：
COUNT(*) AS cust_numcustomers;
COUNT(*)返回customers表中记录的总行数，不管其值是什么。返回的总数的名称为cust_num。 【例】查询customers表中有电子邮箱的顾客的总数，SQL语句如下：
COUNT(c_email) AS email_numcustomers;
提示： 两个例子中不同的数值说明了两种方式在计算总数的时候对待NULL值的方式不同，即指定列的值为空的行被COUNT()函数忽略，但是如果不指定列，而在COUNT()函数中使用星号“*”，则所有记录都不忽略。
【例】在fruits表中，使用COUNT()函数统计不同的s_id的水果种类，然后计算每个分组中的总记录数。
s_id,COUNT(f_name)FROM fruits GROUP BY s_id;
从查询结果可以看到，GROUP BY关键字先按照s_id进行分组，然后计算每个分组中的总记录数。
3.2 SUM函数
SUM()是一个求总和的函数，返回指定列值的总和。 【例】在fruits表中查询s_id=101的水果价格总和，SQL语句如下：
SUM(f_price) AS price_total FROM
fruitss_id=101;
从查询结果可以看到，SUM(f_price)函数返回水果价格之和，WHERE子句指定查询的s_id为101。 SUM()可以与GROUP BY一起使用，计算每个分组的总和。 【例】在fruits表中查询不同s_id的水果价格总和，SQL语句如下：
s_id,SUM(f_price) AS price_total
fruits GROUP BY s_id;
GROUP BY按照s_id进行分组，SUM()函数计算每个分组中的价格总量。
提示： SUM()函数在计算时忽略列值为NULL的行。
3.3 AVG函数
AVG()函数通过计算返回的行数和每一行数据的和求得指定列数据的平均值。 【例】在fruits表中，查询s_id=103的供应商的水果价格平均值，SQL语句如下：
AVG(f_price) AS avg_price
fruitss_id = 103;
【例】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：
s_id,AVG(f_price) AS avg_price
fruits GROUP BY s_id;
GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组的平均值，这种分组求平均值的方法非常有用，例如求不同班级学生成绩的平均值、求不同部门工人的平均工资、求各地的年平均气温等。
提示： AVG()函数使用时，其参数为要计算的列名称，如果要得到多列的多个平均值，就需要在每一列上使用AVG()函数。
3.4 MAX函数
MAX()返回指定列中的最大值。 【例】在fruits表中查找市场上价格最高的水果，SQL语句如下：
MAX(f_price) AS max_pricefruits;
【例】在fruits表中查找不同供应商提供的价格最高的水果，SQL语句如下：
s_id, MAX(f_price) AS max_price
fruits GROUP BY s_id;
GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组中的最大值。 MAX()函数不仅适用于查找数值类型，也可以用于字符类型。 【例】在fruits表中查找f_name的最大值，SQL语句如下：
MAX(f_name)fruits;
MAX()函数可以对字母进行大小判断，并返回最大的字符或者字符串值。
提示： MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型数据进行比较时，按照字符的ASCII码值大小比较，从a到z，a的ASCII码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，‘b’与‘t’比较时，‘t’为最大值；“bcd”与“bca”比较时，“bcd”为最大值。
3.5 MIN函数
MIN()返回查询列中的最小值。 【例】在fruits表中查找市场上水果的最低价格，SQL语句如下：
MIN(f_price) AS min_pricefruits;
MIN()也可以和GROUP BY关键字一起使用，求每个分组中的最小值。 【例】在fruits表中查找不同供应商提供的价格最低的水果，SQL语句如下：
s_id, MIN(f_price) AS min_price
fruits GROUP BY s_id;
GROUP BY关键字根据s_id字段对记录进行分组，然后计算出每个分组中的最小值。 MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可以用于字符类型。
4. 连接查询
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，建立表时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在有相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。
4.1 内连接
内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果列表中。 【例】在fruits表和suppliers表之间使用内连接查询。
fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id字段建立联系。接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name，SQL语句如下：
suppliers.s_id, s_name,f_name, f_price
fruits ,suppliers
fruits.s_id = suppliers.s_id;
在这里，SELECT语句与前面所介绍的一个最大的差别是，SELECT后面指定的列分别属于两个不同的表，（f_name，f_price）在表fruits中，而另外两个在表suppliers中；同时，FROM子句列出了两个表fruits和suppliers；WHERE子句作为过滤条件，指明只有两个表中的s_id字段值相等的时候才符合连接查询的条件。从返回的结果可以看到，显示的记录是由两个表中不同的列值组成的新记录。
注意： 因为fruits表和suppliers表中有相同的字段s_id，所以在比较的时候需要完全限定表名（格式为“表名.列名”），如果只给出s_id，PostgreSQL将不知道指的是哪一个并返回错误信息。 下面的内连接查询语句返回与前面完全相同的结果。 【例】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，SQL语句如下：
suppliers.s_id, s_name,f_name, f_price
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语句如下：
f1.f_id, f1.f_name
fruits AS f1, fruits AS f2
f1.s_id = f2.s_idf2.f_id = 'a1';
此处查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，fruits表第一次出现的别名为f1、第二次出现的别名为f2，使用SELECT语句返回列时明确指出返回以f1为前缀的列的全名，WHERE连接两个表，并按照第二个表的f_id对数据进行过滤，返回所需数据。
4.2 外连接
连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅符合查询条件和连接条件的行，但有时候需要包含没有关联的行中数据，即返回到查询结果集合中不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。外连接分为左外连接和右外连接： （1）LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。 （2）RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
- LEFT JOIN左连接 左连接的结果包括LEFT OUTER JOIN关键字左边连接表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择表字段均为空值。
【例】在customers表和orders表中查询所有客户，包括没有订单的客户，SQL语句如下：
customers.c_id, orders.o_num
customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;
结果显示了5条记录，ID等于10002的客户目前并没有下订单，所以对应的orders表中并没有该客户的订单信息，该条记录只取出了customers表中相应的值，而从orders表中取出的值为空值。
-
RIGHT JOIN右连接 右连接是左连接的反向连接，将返回RIGHT OUTER JOIN关键字右边的表中的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。 【例】在customers表和orders表中查询所有订单，包括没有客户的订单，SQL语句如下：
customers.c_id, orders.o_num customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;
5. 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从PostgreSQL 4.1开始引入。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。查询可以基于一个表或者多个表。子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”“<=”“>”“>=”和“!=”等。
5.1 带ANY、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。 【例】返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要num1大于num2的值就为符合查询条件的结果。
num1tbl1num1 > ANY (num2tbl2);
在子查询中，首先返回的是tbl2表的所有num2列结果，然后将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。
5.2 带ALL关键字的子查询
ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。 ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。 【例】返回tbl1表中比tbl2表中num2列所有值都大的值，SQL语句如下：
num1tbl1num1 > ALL (num2tbl2);
5.3 带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。 【例】查询表suppliers表中是否存在s_id=107的供应商，如果存在就查询fruits表中的记录，SQL语句如下：
*fruits EXISTS
(s_namesupplierss_id = 107);
NOT EXISTS与EXISTS使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOT EXISTS的结果为false，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那么NOT EXISTS返回的结果是true，此时外层语句将进行查询。 【例】查询表suppliers表中是否存在s_id=107的供应商，如果不存在就查询fruits表中的记录，SQL语句如下：
*fruits NOT EXISTS
(s_namesupplierss_id = 107);
提示： EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。
5.4 带IN关键字的子查询
IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。 【例】在customers表中查询c_name=“RedHook”的客户ID（c_id），并根据c_id查询订单号o_num，SQL语句如下：
o_numordersc_id IN
(c_idcustomersc_name='RedHook');
SELECT语句中可以使用NOT IN关键字，其作用与IN正好相反。
【例】与前一个例子语句类似，但是在SELECT语句中使用NOT IN操作符，SQL语句如下：
o_numordersc_id NOT IN
(c_idcustomersc_name='RedHook');
提示： 子查询的功能也可以通过连接查询完成，但是子查询使得PostgreSQL代码更容易阅读和编写。
5.5 带比较运算符的子查询
子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。 【例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：
s_id, f_namefruitss_id =
(s1.s_idsuppliers AS s1s1.s_city = 'Tianjin');
【例】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：
s_id, f_namefruitss_id <>
(s1.s_idsuppliers AS s1s1.s_city = 'Tianjin');
6. 合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。基本语法格式如下：
column,...table1
[ALL]
column,...table2
【例】查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果信息，使用UNION连接查询结果，SQL语句如下：
s_id, f_name, f_pricefruits
f_price < 9.0 UNION
s_id, f_name, f_pricefruits
s_id IN(101,103);
使用UNION ALL包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录，UNION从查询结果集中自动去除了重复的行，如果要返回所有匹配行，而不进行删除，可以使用UNION ALL。
【例】查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果信息，使用UNION ALL连接查询结果，SQL语句如下：
s_id, f_name, f_pricefruits
f_price < 9.0ALL
s_id, f_name, f_pricefruits
s_id IN(101,103);
提示： 使用UNION ALL的功能是不删除重复行，加上ALL关键字语句执行时所需要的资源少，所以尽可能使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用UNION ALL以提高查询效率。
- 教程列表完成本章学习
扫一扫
767
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
