一.PostgreSQL簡介

PostgreSQL使用的是一種客戶端/伺服器的模式。一次PostgreSQL會話由以下相關進程組成：
1.postgres:一個伺服器進程（該進程管理著資料庫文件，接收來自客戶端的連接請求，並代表客戶端對資料庫進行操作）
2.需要執行資料庫操作的客戶端應用（可能是一個字元界面的工具，或是一個圖形化的應用，或是通過訪問資料庫來顯示網頁的web伺服器等）

PostgreSQL可以處理來自客戶端的多個併發請求，這是因為它會為每個請求都fork一個新進程，而這時，客戶端和新的伺服器進程就不再通過原本的postgres進行通訊。

二.PostgreSQL基礎

1.pgAdmin4可視化工具

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子句

//創建一個名為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 表名；

6.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;

7.TRUNCATE TABLE刪除表數據

//語法
TRUNCATE TABLE table_name;

//實例
TRUNCATE TABLE weather;

8.View 視圖

//創建視圖語法
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 事務

事務的特性：

• 原子性：事務作為一個整體，其中的操作要麼全部執行成功，要麼一個失敗整體回滾；
• 一致性：確保資料庫從一個一致狀態轉換到另一個一致狀態；
• 隔離性：多個事務併發執行時，一個事務的執行並不會影響到其他事務的執行；
• 持久性：被提交的修改應永久保存到資料庫中；

開啟事務使用BEGIN關鍵字，結束事務使用END關鍵字，提交事務操作使用COMMIT關鍵字，回滾事務使用ROLLBACK關鍵字。

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

10.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.一些其他零散的博客~