一.PostgreSQL简介

PostgreSQL数据库一种关系型数据库。是当前世界上最先进的开源关系型数据库。

PostgreSQL使用的是一种客户端/服务器的模式。一次PostgreSQL会话由以下相关进程组成：
1.postgres:一个服务器进程（该进程管理着数据库文件，接收来自客户端的连接请求，并代表客户端对数据库进行操作）
2.需要执行数据库操作的客户端应用（可能是一个字符界面的工具，或是一个图形化的应用，或是通过访问数据库来显示网页的web服务器等）

PostgreSQL可以处理来自客户端的多个并发请求，这是因为它会为每个请求都fork一个新进程，而这时，客户端和新的服务器进程就不再通过原本的postgres进行通讯。

二.PostgreSQL基础

1.pgAdmin4可视化工具

pgAdmin4是专用于操作PostreSQL数据库的可视化工具，类似于操作MySQL的Navicat。

pgAdmin4下载地址：https://www.pgadmin.org/

具体的安装步骤在此省略，因为网上有很多介绍安装步骤的博客,自行百度即可~

2.数据类型

数值类型

货币类型

虽然numeric、int、bigint都可以转换为money，但是转换过程中会损失一部分精度，会对结果造成误差，因此并不推荐这种做法

字符类型

日期类型

布尔类型

boolean类型除了true和false之外，还有第三种值：unknown，在数据库中用NULL表示

除了以上列举的数据类型，PostgreSQL还支持其他多种数据类型，如网络地址类型、几何类型、文本搜索类型等，如果想了解请自行百度~

3.创建数据库

创建数据库有三种方式：

• 使用SQL语句创建(CREATE DATABASE)
• 使用命令创建(createdb)
• 使用可视化工具创建（pgAdmin4）

//CREATE DATABASE创建数据库
CREATE DATABASE dbname;

//createdb命令创建数据库
$ createdb dbname

当响应为CREATE DATABASE时，就说明成功了。
若是响应为createdb: command not found时，则说明没有安装好。

4.删除数据库

删除数据库有三种方式：

• 使用SQL语句删除（DROP DATABASE）
• 使用命令删除（dropdb）
• 使用可视化工具删除（pgAdmin4）

//使用SQL语句删除数据库
DROP DATABASE dbname;

//使用命令删除数据库
dropdb dbname

5.创建表 & 删除表 & 修改表

//使用CREATE TABLE语法创建表
CREATE TABLE weather(
  id        int,             --主键ID    
  city      varchar(80),     --城市 
  temp_lo   int,             --最低气温       
  temp_hi   int,             --最高气温
  prcp      real,            --降水量
  date      date,            --日期
  PRIMARY KEY()                               
);

//使用DROP TABLE删除表
DROP TABLE 表名

//使用ALTER修改表
ALTER TABLE table_name ADD column_name datatype;                           --添加新的列
ALTER TABLE table_name DROP column_name;                                   --删除表中的列
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;             --修改列的数据类型
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;               --添加非空约束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;                 --删除约束
ALTER TABLR table_name ADD CONSTRAINT primarykey_name PRIMARY KEY(column); --添加主键
ALTER TABLR table_name DROP CONSTRAINT primarykey_name;                    --删除主键

• real类型用于存储单精度浮点数

在我们创建表的时候，是可以将双划线后面的注释一起键入的，因为双划线后面的内容直到该行末尾都会被忽略；
SQL是对关键字和标识符大小写不敏感的语言；

6.SCHEMA模式

PostgreSQL模式可以看作是一个表的集合。
一个模式可以包含：函数、视图、索引、操作符、据类型等。
不同模式中使用相同的对象名不会出现冲突。

模式的优点：

• 允许多个用户使用一个数据库，但不会出现冲突
• 将数据库对象组织逻辑组更容易管理
• 第三方应用的对象可以放在独立的模式种，可以避免与其他对象名发生冲突

//使用命令创建模式
create schema schmeaname;
create table myschema.weather(            --在myschema模式中创建一个名为weather的表
  id        int,             --主键ID    
  city      varchar(80),     --城市 
  temp_lo   int,             --最低气温       
  temp_hi   int,             --最高气温
  prcp      real,            --降水量
  date      date,            --日期
  PRIMARY KEY(id)                               
);


//使用SQL语句创建模式
CREATE SCHEMA schemaname;
CREATE TABLE myschema.weather(            --在myschema模式中创建一个名为weather的表
  id        int   PRIMARY KEY   NOT NULL, --主键ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低气温       
  temp_hi   int,                          --最高气温
  prcp      real,                         --降水量
  date      date                          --日期                              
);


//删除模式
DROP SCHEMA myschema;            --myschema模式中的对象已经被删除干净
DROP SCHEMA myschema CASCADE;    --删除myschema模式及其模式下的对象  

7.INSERT INTO插入

//向表中插入新记录
INSERT INTO weather VALUES(1,'ShangHai',46,52,0.73,'2020-7-16'),(2,'BeiJing',48,58,0.52,'2020-7-16');

上面这种写法是标准的写法，它要求我们记住所有字段的顺序和类型。
但是在PostgreSQL中，还有一种可以忽略字段顺序的INSERT写法，但是这种写法不推荐，因为很容易造成错误，所以这里不再赘述，有兴趣可以自己百度~

最后介绍一种使用COPY为表装载大量数据的方法：

COPY weather FROM '/home/user/weather.txt';      --源文件的文件名必须是后端服务器可以访问的，因为后端服务器会直接读取文件

8.SELECT查询

//普通查询
SELECT * FROM weather;

//支持AS别名
SELECT w.city AS 城市, w.temp_lo AS 最低气温, w.temp_hi AS 最高气温, w.prcp AS 降水量, w.date AS 日期 FROM weather;

//支持WHERE条件查询
SELECT * FROM weather w WHERE w.city='ShangHai' and prcp>0.0;

//支持排序
SELECT * FROM weather ORDER BY city,temp_lo;

//支持去重
SELECT DISTINCT city FROM weather ORDER BY city;

在一些数据库（包括老版本的PostgreSQL），都支持DISTINCT默认对查询结果排序，但是现阶段不保证结果排序，所以为了保证排序正确，最好使用ORDER BY关键字

9.UPDATE更新

//使用UPDATE语句来更改指定的表记录
UPDATE weather SET temp_lo=50, temp_hi=61 WHERE city='BeiJing'; 

10.DELETE删除

//使用DELETE语句删除指定表记录
DELETE FROM weather WHERE id=1;

DELETE FROM weather;      --使用这种删除语句的时候必须格外小心，因为这会删除表中所有的记录

11.运算符

运算符一般分为：

• 算数运算符
• 比较运算符
• 逻辑运算符
• 按位运算符

算数运算符

比较运算符

逻辑运算符

按位运算符

12.表达式

表达式是由一个或多个值、函数、运算符组成的。

//布尔表达式：
SELECT * FROM weather WHERE id=1000;

//数字表达式
SELECT sum(temp_lo,temp_hi) FROM weather WHERE id=20;

//日期表达式
SELECT CURRENT_TIMESTAMP;

13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查询、HAVING、ORDER BY、GROUP BY、LIMIT

懒得分开写了，反正也简单，直接写一起省事儿了~

//支持去重
SELECT DISTINCT city FROM weather ORDER BY city;

//支持WHERE条件查询
SELECT * FROM weather w WHERE w.city='ShangHai';

//支持AND连接多个查询条件
SELECT * FROM weather w WHERE w.city='ShangHai' and prcp>0.0;

//支持OR连接多个查询条件
SELECT * FROM weather w WHERE w.city='ShangHai' OR prcp>0.0;

//支持NOT NULL判断条件
SELECT * FROM weather w WHERE date NOT NULL;

//支持LIKE模糊查询
SELECT * FROM weather w WHERE city LIKE'%Hai';

//支持IN条件判断
SELECT * FROM weather w WHERE id in(1,2,3,4,5);

//支持NOT IN条件判断
SELECT * FROM weather w WHERE id NOT IN(1,2,3,4,5);

//支持BETWEEN AND区间筛选
SELECT * FROM weather w WHERE id BETWEEN 1 AND 10;

//支持子查询
SELECT * FROM weather w WHERE w.city exists(SELECT * FROM cities c WHERE c.id exists(1,3,7,10));

//支持HAVING结果筛选
SELECT SUM(w.temp_hi) FROM weather w WHERE w.city exists(SELECT * FROM cities c WHERE c.id exists(1,3,7,10)) HAVING w.temp_hi>60;

//支持ORDER BY排序
SELECT * FROM weather w ORDER BY w.id ASC;      --默认ASC升序，DESC降序

//支持GROUP BY分组
SELECT city,SUM(temp_hi) FROM weather GROUP BY city;

//支持LIMIT分页
SELECT * FROM weather LIMIT 0,5;

14.WITH子句

WITH子句可以将大型、复杂二点查询语句分解为诸多简单的查询语句；
WITH子句中可以使用SELECT、UPDATE、INSERT、DELETE语句；
WITH子句最大的优点，就是可以调用自身，从而实现递归。
WITH子句可以多次调用。

//创建一个名为getCities的WITH子句
WITH getCities AS(
      SELECT * FROM cities WHERE id=2;
      union all
      SELECT * FROM cities,getCities WHERE getCities .parent_id=cities.id;
)
//使用getCities
SELECT * FROM getCities ORDER BY id;

//使用WITH和RETURNING关键字做被删数据备份
WITH backups AS(
      DELETE FROM weather WHERE id >100
      RETURNING *                        --RETURNING关键字的作用：返回DML操作的数据（增、删、改操作）
)
INSERT INTO weather1 VALUES(SELECT * FROM backups);

三.PostgreSQL高级

1.约束

数据库约束是用于规定表中的数据规则，用于保证数据的准确性和可靠性。
约束不可违反，违反约束会终止行为。
约束可以在创建表的时候用建表语句添加，也可以通过ALTER语句添加。
约束可以是列级或表级，列级仅适用于列，表级适用于全表。

常用的约束有：

• NOT NULL 非空约束
• UNIQUE 唯一约束
• PARIMARY KEY 主键约束
• FOREIGN KEY 外键约束
• CHECK 保证列的值都复核条件
• EXCLUSION 排他约束

NOT NULL 非空约束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主键ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低气温       
  temp_hi   int,                          --最高气温
  prcp      real,                         --降水量
  date      date                          --日期                              
);

UNIQUE 唯一约束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主键ID    
  city      varchar(80)         UNIQUEN,  --城市 
  temp_lo   int,                          --最低气温       
  temp_hi   int,                          --最高气温
  prcp      real,                         --降水量
  date      date                          --日期                              
);

PARIMARY KEY 主键约束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY   NOT NULL, --主键ID    
  city      varchar(80),                  --城市 
  temp_lo   int,                          --最低气温       
  temp_hi   int,                          --最高气温
  prcp      real,                         --降水量
  date      date                          --日期                              
);

FOREIGN KEY 外键约束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主键ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低气温       
  temp_hi   int,                                --最高气温
  prcp      real,                               --降水量
  date      date                                --日期                              
);

CHECK 保证列的值都复核条件

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主键ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低气温       
  temp_hi   int,                                --最高气温
  prcp      real               CHECK(real > 0), --降水量
  date      date                                --日期                              
);

EXCLUSION 排他约束

CREATE TABLE myschema.weather(            
  id        int   PRIMARY KEY         NOT NULL, --主键ID    
  city      varchar(80) reference cities(name), --城市 
  temp_lo   int,                                --最低气温       
  temp_hi   int,                                --最高气温
  prcp      real,                               --降水量
  date      date,                               --日期                              
  EXCLUDE USING gist  --排他约束（USING gist是用于构建和执行的一种索引类型）
  (city WITH <>,      --城市不同，日期相同，否则数据插入失败
  date WITH =)
);

2.JOIN表连接

常用的表连接有：

• INNER JOIN 内连接
• LEFT JOIN 左外连接
• RIGHT JOIN 右外连接
• FULL JOIN全连接
• CROSS JOIN交叉连接
• 自连接

//内连接
SELECT * FROM weather,cities WHERE weather.city=cities.name;
或
SELECT * FROM weather INNER JOIN cities ON (weather.city=cities.name);

//左外连接
SELECT * FROM weather LEFT JOIN cities ON (weather.city=cities.name);

//右外连接
SELECT * FROM weather RIGHT JOIN cities ON (weather.city=cities.name);

//自连接
SELECT * FROM weather w1,weather w2 WHERE w1.temp_lo > w2.temp_lo AND w1.temp_hi < w2.temp_hi;

//交叉连接
SELECT * FROM weahter student s CROSS JOIN result r WHERE s.student_id=r.student_id; //A表的每一行匹配B表的每一行，即A*B行

使用CROSS JOIN交叉查询的时候需要格外注意，因为当量表数据量都较大的时候，可能会产生一个非常庞大的结果表（交叉查询A表和B表，其结果表为A*B的数据量）

3.UNION

UNION有两种使用方式：

• UNION 不包含重复的数据
• UNION ALL 包含重复的数据

//UNION
SELECT * FROM weather201906 where city='ShangHai'
UNION
SELECT * FROM weather202006 where city='ShangHai'

//UNION ALL
SELECT * FROM weather201906 where city='ShangHai'
UNION ALL
SELECT * FROM weather202006 where city='ShangHai'

4.AS别名

AS别名过于简单，不再赘述

5.触发器

触发器是数据库的回调函数，可以在指定的数据库事件发生时，自动执行和调用。
触发器的关键点：

• 触发器的触发情况：

1. 在执行操作之前(在检查约束并进行增删改之前)
2. 在执行操作之后(在检查约束并进行增删改之后)
3. 更新操作（对一个视图进行增删改时）

• 触发器的FOR EACH ROW属性是可选的，若选中，则操作修改时每行都调用一次，若是选中FOR EACH STATEMENT，则无论修改多少次，都只执行一次
• WHEN子句和触发器操作在引用NEW.column-name和OLD.column-name单标插入、删除和更新的时候，可以访问每一行元素。column-name是与触发器关联的表中的列名称
• 若存在WHEN子句，则触发器只会执行WHEN条件成立的那一行，若没有WHEN子句，则每一行都会执行
• BEFORE和AFTER关键字决定何时执行触发器操作
• 要修改的表必须都存在同一个库中，作为触发器被附加的表或视图，必须使用tablename，而不能是database.tablename
• 当创建约束触发器时会执行约束选项，通过约束选项我们可以设置触发器的触发时间，当约束触发器实现的约束被违反的时候，它会抛出异常

//创建触发器的语法
CTEATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
  --触发器逻辑
];

//创建触发器实例
CREATE TRIGGER weather_trigger AFTER INSERT ON weather FOR EACH ROW 
BEGIN
   DECLARE s1 VARCHAR(40) character set utf8;
   DECLARE s2 VARCHAR(20) character set utf8;
   SET s2 = "is created";
   SET s1 = CONCAT(NEW.name,s2);
   INSERT INTO logs(log) VALUES(s1);   
END $
DELIMITER;

//列出当前数据库中所有的触发器
SELECT * FROM pg_trigger;

//列出特定表的触发器
SELECT tgname FROM pg_trigger,pg_class WHERE tgreid=pg_class.oid AND relname='company';

//删除触发器
DROP TRIGGER 触发器名 ON 表名；

虽然本文介绍了触发器，但是日常开发中并不推荐使用触发器，原因如下：
1. 触发器隐藏在数据库中，数量不多还行，大数量的触发器对于日常开发和维护，都是一个极大地挑战，尤其是对于那些不是自己写的触发器而言；
2. 触发器会占用数据库性能；
3. 在数据库迁移时，会非常麻烦；
简而言之，触发器就像是实体外键一样，非常鸡肋……

6.INDEX索引

索引是一种特殊的加速数据检索的特殊表查询，它是一个指向表中数据的指针。索引就像是一本书的目录。
索引可以加速SELECT语句和WHERE查询数据的速度，但是会降低UPDATE和INSERT语句的速度。
索引的创建使用的是CREATE INDEX语句，需要指定表和要添加索引的列（一或多），并指定索引是升序或降序排列。
索引是唯一的。

常用的索引类型：

• 单列索引
• 组合索引
• 唯一索引
• 局部索引
• 隐式索引

单列索引

//语法
CREATE INDEX index_name ON table_name (column_name); --在一列上添加索引

组合索引

//语法
CREATE INDEX index_name ON table_name (column_name1,column_name2); --在多列上添加索引

单列索引和多列索引必须使用在WHERE子句过滤条件非常频繁的列上

唯一索引

//语法
CREATE UNIQUE INDEX index_name ON table_name (column_name);  --唯一索引可以保证数据的完整性

局部索引

//语法
CREATE INDEX index_name ON table_name (conditional_expression);  --局部索引是在表的子集上创建的索引

隐式索引

隐式索引是在创建对象的时候，由数据库自动创建的索引，如主键约束和唯一约束。

DROP INDEX删除索引

DROP INDEX index_name;

索引不适合使用的场景：
1. 索引不应该创建在较小的表上；
2. 索引不能创建在有频繁更新和插入的表上；
3. 索引不能创建在有大量NULL值得列上；
4. 索引不能创建在进程操作的列上

7.TRUNCATE TABLE删除表数据

TRUNCATE TABLE会删除表数据，但是会保留表结构。
虽然DROP TABLE也能够删除表数据，但是会同时删除表结构。
虽然DELETE的效果相同，但是TRUNCATE TABLE更快，可以立即释放表空间，而且不需要后续使用VACUUM操作，因为它不需要检索数据。
（VACUUM是PGSQL用于释放、再利用被更新、删除操作占据的表空间）

//语法
TRUNCATE TABLE table_name;

//实例
TRUNCATE TABLE weather;

8.View 视图

视图并不是一张真正的表，而是通过名称存储在数据库中的SQL语句。
视图是一个预定义的SQL查询形式存在的表组合。
视图可以包括一个表所有的行（或一行或多行）。
视图可以通过一个或多个表查询（取决于SQL查询中有几张表）。
视图是一个虚拟表，可以允许我们限制数据访问或汇总各表中的数据。
视图是只读的，不能进行增删改操作。

//创建视图语法
CREATE [TEMP|TEMPORARY] VIEW view_name AS
SELECT * FROM table_name WHERE [condition];

//实例
CREATE VIEW WEATHER_VIEW AS
SELECT city,date FROM weather;

//使用视图
SELECT * FROM WEATHER_VIEW;

//删除视图
DROP VIEW WEATHER_VIEW;

9.TRANSACTION 事务

事务的简介：
TRANSACTION事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列组成。
事务通常包含了读写操作。
当事务被提交到了DBMS（数据库管理系统），DBMS需要确保所有的操作都独立运行、互不干扰，但同时又是一个整体，如果一个操作失败，那么久整体回滚。

事务的特性：

• 原子性：事务作为一个整体，其中的操作要么全部执行成功，要么一个失败整体回滚；
• 一致性：确保数据库从一个一致状态转换到另一个一致状态；
• 隔离性：多个事务并发执行时，一个事务的执行并不会影响到其他事务的执行；
• 持久性：被提交的修改应永久保存到数据库中；

开启事务使用BEGIN关键字，结束事务使用END关键字，提交事务操作使用COMMIT关键字，回滚事务使用ROLLBACK关键字。

BEGIN ;
      [事务操作1];
      [事务操作2];
COMMIT;

10.LOCK 锁

LOCK锁主要是为了保证数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在高并发的数据库中。

数据库中两种基本锁：

• 排他锁(Exclusive Locks):其他事务不可以读取和修改；
• 共享锁(Share Locks)：其他事务可以读取，但是不能修改；

//LOCK语法
LOCK [TABLE]
name            --被锁表名 
  IN
lock_mode;      --锁定模式

死锁

死锁可能发生在两个事务彼此等待对方结束的时候。虽然可以回滚结束它，但是不方便。最好可以指定锁顺序避免死锁发生。

11.子查询

有主流数据库使用经验的，对子查询应该非常熟悉了，这里不再赘述~

12.SERIAL自增长

一开始我还以为PostgreSQL没有自带的自增长呢，原来它的自增长是SERIAL标识字段（和其他主流数据库都不同，算是它的一个特色吧）

//使用SERIAL自增标识
CREATE TABLE weather(
      id       SERIAL      PRIMARY KEY,
      temp_lo  INT,
      temp_hi  INT,
      date     DATE 
)

虽然这个标识挺方便的，但是还是建议使用序列作为自增长~

13.SEQUENCE序列

PG的序列语法和Oracle的基本一致，但是细节上有区别，例如序列的高速缓存，Oracle的可以设置为0，但是PG的最低设置为1

//创建序列
CREATE SEQUENCE complaint_opr.seq_complaint_comment_id
    INCREMENT 1
    START 500
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

14.GANT权限

这个主流数据库都有的，所以应该非常熟悉了

//应用在序列的时候
CREATE SEQUENCE cp_opr.seq_cp_comment_id
    INCREMENT 1
    START 500
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE cp_opr.seq_cp_comment_id
    OWNER TO cp_opr;

GRANT ALL ON SEQUENCE cp_opr.seq_cp_comment_id TO cp_opr;

GRANT SELECT, UPDATE ON SEQUENCE cp_opr.seq_cp_comment_id TO tp_cp_opr;



//应用在建表的时候
--建表语句省略~
GRANT INSERT, SELECT, UPDATE ON TABLE cp_opr.t_cp_parameter TO tp_cp_opr;

GRANT ALL ON TABLE cp_opr.t_cp_parameter TO complaint_opr;

COMMENT ON TABLE cp_opr.t_cp_parameter
    IS '简单类别定义表';

15.常用函数

都说了是常用函数了，所以只列出来常用的那部分了哈，可不是我懒~~~

参考文章和手册

1.PostgreSQL菜鸟手册
2.PostgreSQL中文手册
3.一些其他零散的博客~