SQL数据库基础知识及postgre学习笔记

杜Sean

已于 2023-09-15 11:38:34 修改

阅读量575

点赞数

文章标签：数据库 sql mysql postgresql

于 2023-09-15 11:29:28 首次发布

本文链接：https://blog.csdn.net/qq_46631497/article/details/132898645

版权

一、postgre的数据表基本操作

注：不同数据库会有各自不同的语法，但为了数据库的可迁移性，一般使用通用的SQL语法

1. 创建表

 CREATE TABLE tb_emp3
 (
 id INT,
 name VARCHAR(25),
 deptId INT,
 salary FLOAT,
 PRIMARY KEY(id)
 );

主键约束

主键不能为空, 且值不能重复
与MySQL略有不同,

主键由一个字段组成，SQL语句格式分为以下两种情况。

（1）在定义列的同时指定主键，语法规则如下：
```
 字段名 数据类型 PRIMARY KEY
```
（2）在定义完所有列之后指定主键。
```
 [CONSTRAINT <约束名>] PRIMARY KEY [字段名]
```
可以一样用列级别的约束：'id int primary key'；也可以使用表级别的约束：'[CONSTRAINT <约束名>] PRIMARY KEY [字段名]'（后面多了一个约束名）

外键约束

外键约束保证数据引用的完整性，外键定义后，不能删除另一个表有关联关系的记录
我们通常将拥有外键的表称之为子表，比如学生表是子表, 班级表是主表；如果一定要删除主表某条数据, 则必须这条数据相关联的所有子表数据。

创建外键的语法规则如下：

 [CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,…]
 REFERENCES <主表名> 主键列1 [ ,主键列2,…]

非空约束

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。非空约束的语法规则如下

 字段名 数据类型 not null

唯一性约束

唯一性约束（Unique Constraint）要求添加该约束的列字段的值唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

使用SQL语句也可以轻松创建唯一约束，具体的语法规则如下：（1）在定义完列之后直接指定唯一约束，语法规则如下：

 字段名 数据类型 UNIQUE

默认约束

默认约束（Default Constraint）指定某列的默认值。例如，男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。默认约束的语法规则如下：

 字段名 数据类型 DEFAULT 默认值

2. 修改表

修改表名

 ALTER TABLE <旧表名> RENAME  TO  <新表名>;
 alter table a rename to b；

修改字段数据类型、字段名

修改数据类型

ALTER TABLE <表名> ALTER COLUMN <字段名> TYPE <数据类型>
-- alter table a alter column ID type INT；

 ALTER TABLE <表名> RENAME <旧字段名> TO  <新字段名>;
 -- alter table a rename ID to No；

添加字段

ALTER TABLE <表名> ADD  COLUMN  <新字段名> <数据类型>
-- alter table a add column b int；

删除字段

ALTER TABLE <表名> DROP <字段名>; -- alter table a drop name;
删除约束

 ALTER TABLE <表名> DROP CONSTRAINT  <外键约束名>
 -- alter table a drop constrain FK_B_A;

3. 删除表

删除表的同时，表的定义和表中所有的数据均会被删除，因此，在删除操作前，最好对表中的数据做个备份，以免造成无法挽回的后果。

3.1 删除没有被关联的表

在PostgreSQL中，使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表。语法格式如下：

 DROP 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_emp DROP CONSTRAINT fk_emp_dept;

语句成功执行后，将取消表tb_emp和tb_dept2之间的关联关系。此时，可以输入删除语句，将原来的父表tb_dept2删除，SQL语句如下：

 DROP TABLE tb_dept2;

二、数据的查询操作

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 * FROM 表名;
```
在SELECT语句中指定所有字段另外一种查询所有字段值的方法是在SELECT语句中指定所有字段。根据前面介绍的SELECT语句格式，SELECT关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在SELECT子句右边。例如，查询fruits表中的所有数据，SQL语句也可以书写如下：

SELECT f_id, s_id ,f_name, f_price FROM fruits;

一般情况下，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询的效率和所使用的应用程序的效率。

2.2 查询指定字段

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

SELECT 列名FROM 表名;

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

 SELECT f_name FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f_name字段下的所有水果名称，指定字段的名称紧跟在SELECT关键字之后。输出结果显示了fruits表中f_name字段下的所有数据。

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

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

【例】从fruits表中获取f_name和f_price两列，SQL语句如下：

 SELECT f_name, f_price FROM fruits;

该语句使用SELECT声明从fruits表中获取名称为f_name和f_price两个字段下的所有水果名称和价格，两个字段之间用逗号分隔开。

提示： PostgreSQL中的SQL语句是不区分大小写的，因此SELECT和select的作用相同。许多开发人员习惯将关键字大写、数据列和表名小写。读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。

2.3 查询指定记录

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

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

在WHERE子句中，PostgreSQL提供了一系列的条件判断符，具体含义如表8.1所示。表8.1　WHERE条件判断符【例】查询价格为10.2元的水果名称，SQL语句如下：

 SELECT f_name, f_price FROM fruits WHERE f_price = 10.2;

2.4 带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;

2.5 带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;

BETWEEN匹配范围中的所有值，包括开始值和结束值。 BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定范围内的值，则这些记录被返回。【例】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：

 SELECT f_name, f_price FROM fruits
 WHERE f_price NOT BETWEEN 2.00 AND 10.20;

2.6 带LIKE的字符匹配查询

通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符，SQL语句中支持通配符，可以和LIKE一起使用的通配符是‘%’。【例】在fruits表中，查询f_name中包含字母‘g’的记录，SQL语句如下：

 SELECT f_id, f_name FROM fruits
 WHERE f_name LIKE '%g%';

该语句查询包含字符串中包含字母‘g’的水果名称，只要名字中有字母‘g’，不管前面或后面有多少个字符都满足查询的条件。

2.7 查询空值

数据表创建的时候，设计者可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串，一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。【例】查询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的值为NULL的记录，满足查询条件。与IS NULL相反的是IS NOT NULL，该关键字查找字段不为空的记录。【例】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：

 SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;

2.8 带AND的多条件查询

使用SELECT查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。PostgreSQL在WHERE子句中使用AND操作符限定只有必须满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。【例】在fruits表中查询s_id = 101并且f_price大于5的记录价格和名称，SQL语句如下：

 SELECT s_id, f_price, f_name FROM fruits
 WHERE s_id = '101' AND 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语句如下：

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

提示： OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

2.10 查询结果去重

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

 SELECT DISTINCT 字段名 FROM 表名;

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

 SELECT DISTINCT s_id FROM fruits;

2.11 对查询结果排序

单列排序【例】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：

SELECT f_name FROM fruits ORDER BY f_name;

该语句查询的结果和前面的语句相同，不同的是，通过指定ORDER BY子句，PostgreSQL对查询的f_name列的数据按字母表的顺序进行了升序排序。

多列排序有时需要根据多列值进行排序，比如显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，只要将需要排序的列之间用逗号隔开即可。【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：
```
SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
```

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

指定排序方向默认情况下，查询数据按字母升序进行排序（从A到Z），但数据的排序并不仅限于此，还可以使用ORDER BY对查询结果进行降序排序（从Z到A）。降序通过关键字DESC实现，如下面的例子所示。【例8.22】查询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关键字。

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语句如下：
```
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
```
使用HAVING过滤分组 GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。【例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：
```
SELECT s_id,COUNT(f_name) FROM fruits
  GROUP BY s_id HAVING COUNT(f_name)>1;
```
提示： HAVING关键字与WHERE关键字都是用来过滤数据的，两者有什么区别呢？其中重要的一点是，HAVING在数据分组之后进行过滤，用来选择分组；WHERE在分组之前用来选择记录。另外，WHERE排除的记录不再包括在分组中。
某些情况下需要对分组进行排序。在前面的介绍中，ORDER BY用来对查询的记录排序。如果和GROUP BY一起使用可以完成对分组的排序。【例】根据s_id对fruits表中的数据进行分组，显示水果种类大于1的分组信息，并按照水果的种类排序，SQL语句如下：

SELECT s_id,COUNT(f_name) FROM 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行：

 SELECT * From fruits LIMIT 4;

由结果可以看到，该语句没有指定返回记录的“位置偏移量”参数，显示结果从第一行开始，“行数”参数为4，因此返回的结果为表中的前4行记录。如果指定返回记录的开始位置，则返回结果为从“位置偏移量”参数开始的指定行数，“行数”参数指定返回的记录条数。【例】在fruits表中，使用LIMIT子句，返回从第5个记录开始的行数长度为3的记录，SQL语句如下：

 SELECT * From fruits LIMIT 3 OFFSET 4;

3. 函数

1. 数学函数

数学函数主要用来处理数值数据，主要的数学函数有绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在有错误产生时，数学函数将会返回空值NULL。

1.1 绝对值函数和返回圆周率的函数

ABS(x)返回x的绝对值。【例】求2、-3.3和-33的绝对值，输入语句如下：

 SELECT ABS(2), ABS(-3.3), ABS(-33);

正数的绝对值为其本身，所以2的绝对值为2；负数的绝对值为其相反数，所以-3.3的绝对值为3.3、-33的绝对值为33。 PI()返回圆周率π的值，默认的显示小数位数是6位。【例】返回圆周率值，输入语句如下：

 SELECT pi();

返回结果保留了15位有效数字。

1.2 获取整数的函数

CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。【例】使用CEIL和CEILING函数返回最小整数，输入语句如下：

 SELECT  CEIL(-3.35),CEILING(3.35);

-3.35为负数，不小于-3.35的最小整数为-3，因此返回值为-3；不小于3.35的最小整数为4，因此返回值为4。 FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。【例】使用FLOOR函数返回最大整数，输入语句如下：

 SELECT FLOOR(-3.35), FLOOR(3.35);

-3.35为负数，不大于-3.35的最大整数为-4，因此返回值为-4；不大于3.35的最大整数为3，因此返回值为3。

1.3 四舍五入函数

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。【例】使用ROUND(x)函数对操作数进行四舍五入操作，输入语句如下：

 SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);

ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。【例】使用ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位，输入语句如下：

 SELECT 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函数返回参数的符号，输入语句如下：

 SELECT SIGN(-21),SIGN(0), SIGN(21);

SIGN(-21)返回-1；SIGN(0)返回0；SIGN(21)返回1。

2. 字符串函数

字符串函数主要用来处理数据库中的字符串数据。

2.1 计算字符串字符数和字符串长度

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。【例】使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：

 SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');

LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。【例】使用LENGTH函数计算字符串长度，输入语句如下：

 SELECT LENGTH('date'), LENGTH('egg');

计算的结果与CHAR_LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占一个字节。

2.2 合并字符串

CONCAT(s1,s2,…)的返回结果为连接参数产生的字符串。若有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。【例】使用CONCAT函数连接字符串，输入语句如下：

 SELECT 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函数连接带分隔符的字符串，输入语句如下：

 SELECT 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函数返回字符串中左边的字符，输入语句如下：

 SELECT LEFT('football', 5);

函数返回字符串“football”从左边开始长度为5的子字符串，结果为“footb”。 RIGHT(s,n)返回字符串s最右边的n个字符。【例】使用RIGHT函数返回字符串中右边的字符，输入语句如下：

 SELECT RIGHT('football', 4);

函数返回字符串“football”从右边开始长度为4的子字符串，结果为“ball”。

2.4 填充字符串

LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填充，填充长度为len。假如s1的长度大于len，则返回值被缩短至len字符。【例】使用LPAD函数对字符串进行填充操作，输入语句如下：

 SELECT 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函数对字符串进行填充操作，输入语句如下：

 SELECT 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函数删除字符串左边的空格，输入语句如下：

 SELECT '(  book  )',CONCAT('(',LTRIM('  book  '),')');

LTRIM只删除字符串左边的空格，而右边的空格不会被删除，“ book ”删除左边空格之后的结果为‘book ’。 RTRIM(s)返回字符串s，字符串右侧空格字符被删除。【例】使用RTRIM函数删除字符串右边的空格，输入语句如下：

 SELECT '(  book  )',CONCAT('(', RTRIM ('  book  '),')');

RTRIM只删除字符串右边的空格，左边的空格不会被删除，“ book ”删除右边空格之后的结果为“ book”。 TRIM(s)删除字符串s两侧的空格。【例】使用TRIM函数删除指定字符串两端的空格，输入语句如下：

 SELECT '(  book  )',CONCAT('(', TRIM('  book  '),')');

2.6 删除指定字符串

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。【例】使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符，输入语句如下：

 SELECT TRIM('xy' FROM 'xyboxyokxyxy') ;

删除字符串“xyboxyokxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，结果为“boxyok”。

2.7 重复生成字符串

REPEAT(s,n)返回一个由重复的字符串s组成的字符串，n表示重复生成的次数。若n<=0，则返回一个空字符串；若s或n为NULL，则返回NULL。【例】使用REPEAT函数重复生成相同的字符串，输入语句如下：

 SELECT REPEAT('PostgreSQL', 3);

REPEAT('PostgreSQL', 3)函数返回的字符串由3个重复的“PostgreSQL”字符串组成。

2.8 替换函数

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。【例】使用REPLACE函数进行字符串替代操作，输入语句如下：

 SELECT 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函数获取指定位置处的子字符串，输入语句如下：

SELECT 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函数查找字符串中指定子字符串的开始位置，输入语句如下：

 SELECT POSITION('ball'IN 'football');

子字符串“ball”在字符串“football”中从第5个字母位置开始，因此函数返回结果为5。

2.11 字符串逆序

REVERSE(s)将字符串s反转，返回的字符串顺序和s字符串顺序相反。【例】使用REVERSE函数反转字符串，输入语句如下：

 SELECT REVERSE('abc');

由结果可以看到，字符串“abc”经过REVERSE函数处理之后所有字符串顺序被反转，结果为“cba”。

3. 日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分。许多日期函数可以同时接受数字和字符串类型的两种参数。

3.1 获取当前日期和当前时间

CURRENT_DATE函数的作用是将当前日期按照‘YYYY-MM-DD’格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。【例】使用日期函数获取系统当前日期，输入语句如下：

 SELECT CURRENT_DATE;

CURRENT_TIME函数的作用是将当前时间以‘HH:MM:SS’的格式返回，具体格式根据函数用在字符串或是数字语境中而定。【例】使用时间函数获取系统当前日期，输入语句如下：

 SELECT CURRENT_TIME;

LOCALTIME函数的作用是将当前时间以‘HH:MM:SS’的格式返回，唯一和CURRENT_TIME函数不同的是返回时不带时区值。【例】使用时间函数获取系统当前日期，输入语句如下：

 SELECT LOCALTIME;

CURRENT_TIMESTAMP、LOCALTIMESTAMP和NOW()3个函数的作用相同，返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数是否用在字符串或数字语境中而定。【例】使用日期时间函数获取当前系统日期和时间，输入语句如下：

 SELECT CURRENT_TIMESTAMP,LOCALTIMESTAMP,NOW();

3个函数返回的日期和时间是相同的。唯一不同的是，LOCALTIMESTAMP函数的返回值不带时区。

3.2 获取日期指定值

EXTRACT(type FROM date)函数从日期中提取其部分，而不是执行日期运算。【例】使用EXTRACT函数从日期中提取一个月中的第几天，输入语句如下：

 SELECT EXTRACT(DAY FROM TIMESTAMP '2017-09-10 10:18:40');

【例】使用EXTRACT函数从日期中提取月份，输入语句如下：

 SELECT EXTRACT(MONTH FROM TIMESTAMP '2019-09-10 10:18:40');

【例】使用EXTRACT函数从日期中提取年份，输入语句如下：

 SELECT EXTRACT(YEAR FROM TIMESTAMP '2019-09-10 10:18:40');

【例】使用EXTRACT函数查询指定日期是一年中的第几天，输入语句如下：

 SELECT EXTRACT(DOY FROM TIMESTAMP '2019-09-10 10:18:40');

【例】使用EXTRACT函数查询指定日期是一周中的星期几，输入语句如下：

 SELECT EXTRACT(DOW FROM TIMESTAMP '2019-09-10 10:18:40');

2019-09-10是星期日。需要读者注意的是，此函数的星期编号为0~6，星期日的返回结果为0。【例】使用EXTRACT函数查询指定日期是该年的第几季度（1~4），输入语句如下：

 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2019-09-10 10:18:40');

从结果可以看出，2019-09-10是该年中的第3季度。

4. 条件判断函数

条件判断函数亦称为控制流程函数，根据满足的条件而执行相应的流程。在PostgreSQL中，进行条件判断的函数为CASE。

4.1 CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

该函数表示，如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn。【例】使用CASE value WHEN语句执行分支操作，输入语句如下：

 SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

CASE后面的值为2，与第二条分支语句WHEN后面的值相等，因此返回结果为“two”。

4.2 CASE WHEN v1 THEN r1 [WHEN v2 THEN r2] ELSE rn] END

该函数表示，某个vn值为TRUE时，返回对应位置THEN后面的结果；如果所有值都不为TRUE，则返回ELSE后的rn。【例】使用CASE WHEN语句执行分支操作，输入语句如下：

SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;

1<0的结果为FALSE，因此函数返回值为ELSE后面的“false”。提　示一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，具体情况视其所在语境而定。用在字符串语境中，返回结果为字符串；用在数字语境中，返回结果为十进制值、实值或整数值。

5. 改变数据类型的函数

CAST(x , AS type)函数将一个类型的值转换为另一个类型的值。【例】使用CAST函数进行数据类型的转换，SQL语句如下：

 SELECT CAST(100 AS CHAR(2));

可以看到，CAST(100 AS CHAR(2))将整数数据100转换为带有2个显示宽度的字符串类型，结果为‘10’。

6、coalesce函数

在做统计的时候，这个函数作为条件可以兼顾到一些特殊情况。

用途：（1）：将控制替换成其他值；

（2）：返回第一个非空值

表达式：

 COALESCE  (expression_1, expression_2, ...,expression_n)

依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

SQL实例:

select coalesce(success_cnt, 1) from tableA

当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。

select coalesce(success_cnt,period,1) from tableA;

当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

4. 连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，包括内连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，建立表时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在有相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询。

4.1 内连接

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

fruits表和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）在表fruits中，而另外两个在表suppliers中；同时，FROM子句列出了两个表fruits和suppliers；WHERE子句作为过滤条件，指明只有两个表中的s_id字段值相等的时候才符合连接查询的条件。从返回的结果可以看到，显示的记录是由两个表中不同的列值组成的新记录。

注意：因为fruits表和suppliers表中有相同的字段s_id，所以在比较的时候需要完全限定表名（格式为“表名.列名”），如果只给出s_id，PostgreSQL将不知道指的是哪一个并返回错误信息。下面的内连接查询语句返回与前面完全相同的结果。【例】在fruits表和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';

此处查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，fruits表第一次出现的别名为f1、第二次出现的别名为f2，使用SELECT语句返回列时明确指出返回以f1为前缀的列的全名，WHERE连接两个表，并按照第二个表的f_id对数据进行过滤，返回所需数据。

4.2 外连接

连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅符合查询条件和连接条件的行，但有时候需要包含没有关联的行中数据，即返回到查询结果集合中不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。外连接分为左外连接和右外连接：（1）LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。（2）RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

LEFT JOIN左连接左连接的结果包括LEFT OUTER JOIN关键字左边连接表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择表字段均为空值。

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

 SELECT customers.c_id, orders.o_num
 FROM 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语句如下：
```
SELECT customers.c_id, orders.o_num
  FROM 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的值就为符合查询条件的结果。

 SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

在子查询中，首先返回的是tbl2表的所有num2列结果，然后将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。

5.2 带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。 ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。【例】返回tbl1表中比tbl2表中num2列所有值都大的值，SQL语句如下：

 SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

5.3 带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。【例】查询表suppliers表中是否存在s_id=107的供应商，如果存在就查询fruits表中的记录，SQL语句如下：

 SELECT * FROM fruits WHERE 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的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

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

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代码更容易阅读和编写。

5.5 带比较运算符的子查询

子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。【例】在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，然后在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');

6. 合并查询结果

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

 SELECT column,... FROM table1
 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 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以提高查询效率。

杜Sean

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫