PostgreSQL筆記

yleizzz發表於2023-03-02
SQL筆記

一.PostgreSQL簡介

PostgreSQL資料庫一種關係型資料庫。是當前世界上最先進的開源關係型資料庫。

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

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

二.PostgreSQL基礎

1.pgAdmin4視覺化工具

pgAdmin4是專用於操作PostreSQL資料庫的視覺化工具，類似於操作MySQL的Navicat。

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

具體的安裝步驟在此省略，因為網上有很多介紹安裝步驟的部落格,自行百度即可~

2.資料型別

數值型別

名字 儲存長度 描述 範圍
smallint 2位元組 小範圍整數 -32768 到 +32767
integer 4位元組 常用整數 -2147483648 到 +2147483647
bigint 8位元組 大範圍整數 9223372036854775808 到 +9223372036854775807
decimal 可變長 使用者指定精度，精確 小數點前 131072 位；小數點後 16383 位
numeric 可變長 使用者指定精度，精確 小數點前 131072 位；小數點後 16383 位
real 4位元組 可變精度，不精確 6 位十進位制數字精度
double precision 8位元組 可變精度，不精確 15 位十進位制數字精度
smallserial 2位元組 自增的小範圍整數 1 到 32767
serial 4位元組 自增整數 1 到 2147483647
bigserial 8位元組 自增的大範圍整數 1 到 9223372036854775807

貨幣型別

名字 儲存長度 描述 範圍
money 8位元組 貨幣金額 -92233720368547758.08 到 +92233720368547758.07

雖然numeric、int、bigint都可以轉換為money，但是轉換過程中會損失一部分精度，會對結果造成誤差，因此並不推薦這種做法

字元型別

名字 儲存長度 描述 範圍
character varying(n),varchar(n) 變長，有長度限制
character(n),char(n) 定長，不補足空白
text 變長，無長度限制

日期型別

名字 儲存長度 描述 最低值 最高值 解析度
timestamp [(p)] [without time zone] 8位元組 日期和時間（無時區） 4713 BC 294276 AD 1 毫秒 / 14 位
timestamp [(p)] with time zone 8位元組 日期和時間，有無時區 4713 BC 294276 AD 1 毫秒 / 14 位
date 4位元組 只用於日期 4713 BC 5874897 AD 1 天
time[(p)] [without time zone] 8位元組 只用於一天內時間 00:00:00 24:00:00 1 毫秒 / 14 位
time[(p)] without time zone 12位元組 只用於一天內時間，有時區 00:00:00+1459 24:00:00-1459 1 毫秒 / 14 位
interval [fields] [(p)] 12位元組 時間間隔 -178000000 年 178000000 年 1 毫秒 / 14 位

布林型別

名字 儲存長度 描述 範圍
boolean 1位元組 true或false

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.運算子

運算子一般分為：

  • 算數運算子
  • 比較運算子
  • 邏輯運算子
  • 按位運算子

算數運算子

運算子 描述 例項
+ 加 省略
- 減 省略
* 乘 省略
/ 除 省略
% 餘 省略
^ 指數 2^3 = 8
|/ 平方根 |/25.0 = 5
||/ 立方根 ||/27.0 = 3
! 階乘 5! = 120
!! 階乘（字首操作符） !!5 = 120

比較運算子

運算子 描述 例項
= 等於 省略
!= 不等於 省略
<> 不等於 省略
< 小於 省略
> 大於 省略
<= 小於等於 省略
>= 大於等於 省略

邏輯運算子

運算子 描述 例項
AND 且 省略
NOT 非 NOT EXISTS，NOT IN，NOT BETWEEN
OR 或 省略

按位運算子

運算子 描述 例項
& 按位與運算子 省略
| 按位或運算子 省略
# 按位異或運算子 省略
~ 取反運算子 省略
<< 二進位制左移運算子 省略
>> 二進位制右移運算子 省略

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標識欄位（和其他主流資料庫都不同，算是它的一個特色吧）

偽型別 儲存大小 範圍
SMALLSERIAL 2位元組 1 到 32,767
SERIAL 4位元組 1 到 2,147,483,647
BIGSERIAL 8位元組 1 到 922,337,2036,854,775,807 
//使用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.常用函式

都說了是常用函式了，所以只列出來常用的那部分了哈，可不是我懶~~~

函式 返回值型別 描述 例子 結果
string | | string text 字串連線 'Hello' | | 'World' 'HelloWorld'
char_length(string) int 返回字串中字元個數 char_length('HelloWorld') 10
convert(string using conversion_name) text 轉換字串編碼 convert(convert('PostgreSQL' using iso_8859_1_to_utf8)) 'PostgreSQL'
lower(string) text 字串轉換成小寫 lower("TOM") 'tom'
upper(string) text 把字串轉化為大寫。 upper('tom') TOM
overlay(string placing string from int [for int]) text 替換子字串 overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas
substring(string [from int] [for int]) text 抽取子字串 substring('Thomas' from 2 for 3) hom
trim([leading丨trailing 丨 both] [characters] from string) text 從字串string的開頭/結尾/兩邊/ 刪除只包含characters(預設是一個空白)的最長的字串 trim(both 'x' from 'xTomxx') Tom
convert(string text, [src_encoding name,] dest_encoding name) text 字串轉碼 convert( 'text_in_utf8', 'UTF8', 'LATIN1') 以ISO 8859-1編碼表示的text_in_utf8
initcap(text) text 單詞首字母大寫 initcap('hi thomas') Hi Thomas
length(string text) int string中字元的數目 length('jose') 4
md5(string text) text MD5加密字串 md5('abc')
replace(string text, from text, to text) text 替換指定字串 replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef
substr(string, from [, count]) text 抽取子字串。 substr('alphabet', 3, 2) ph
to_char(timestamp, text) text 將時間戳轉換為字串 to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text 將時間間隔轉換為字串 to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text) text 整型轉換為字串 to_char(125, '999')
to_char(double precision, text) text 雙精度轉換為字串 to_char(125.8::real, '999D9')
to_char(numeric, text) text 數字轉換為字串 to_char(-125.8, '999D99S')
to_date(text, text) date 字串轉換為日期 to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 轉換字串為數字 to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text) timestamp 轉換為指定的時間格式 time zone convert string to time stamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision) timestamp 把UNIX紀元轉換成時間戳 to_timestamp(1284352323)

參考文章和手冊

1.PostgreSQL菜鳥手冊
2.PostgreSQL中文手冊
3.一些其他零散的部落格~

相關文章