PostgreSQL筆記

来源:https://www.cnblogs.com/LML97/archive/2020/07/27/13331695.html

一.PostgreSQL簡介 PostgreSQL資料庫一種關係型資料庫。是當前世界上最先進的開源關係型資料庫。 PostgreSQL使用的是一種客戶端/伺服器的模式。一次PostgreSQL會話由以下相關進程組成: 1.postgres:一個伺服器進程(該進程管理著資料庫文件,接收來自客戶端的連接 ...


一.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

雖然numericintbigint都可以轉換為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.一些其他零散的博客~


您的分享是我們最大的動力!

更多相關文章
  • 本文更新於2020-05-04,使用MySQL 5.7,操作系統為Deepin 15.4。 忘記roo密碼 使用mysqld_safe --skip-grant-tables跳過許可權系統啟動服務。 不使用賬號密碼,直接使用mysql登錄。 使用UPDATE mysql.user SET authen ...
  • 在.net開發中我們經常會用到SQL Server,微軟的SQL Server其實還算不錯,而且其Developer(開發者版)也免費對用戶使用,但是在安裝的過程中,尤其是在沒有之前版本的全新安裝時,往往會出現很多問題,在本文中我將詳細再現整個安裝流程,並對出現的問題給出解決辦法。 ...
  • DECLARE @tbl nvarchar(265) SELECT @tbl = '' SELECT o.name,i.index_id, i.name, i.type_desc, substring(ikey.cols, 3, len(ikey.cols))AS key_cols, substri ...
  • SQL語句執行順序 From 表連接 on連接條件,形成新的虛擬表 where 篩選條件 group by 生成新的結果集合 group by 分組列表 having 分組後篩選 select 選出顯示的 order by 排序 limit 分頁/個數限制 子查詢順序 除了exists,先執行子查詢 ...
  • select c.unique_compiles as 將從該缺失索引組受益的編譯和重新編譯數, c.user_seeks as 建索引後可能的使用查找次數, c.user_scans as 建索引後可能的使用掃描次數, c.statement as 表名, c.equality_columns, ...
  • 為了更方便的操作elasticsearch,我們需要安裝kibana kibana提供了一個開發工具,可以很方便的操作elasticsearch 打開kibana官網下載頁面 https://www.elastic.co/cn/downloads/kibana 安裝kibana 修改kibana相關 ...
  • 最近登錄到Elasticsearch官網發現Elasticsearch已經更新到7.8的版本了,但是自己公司使用的還是6.4版本,查了下,發現7.X的版本與6.X的版本差別還挺大的,所以準備在自己的個人伺服器上搭建一個7.6的版本,提前瞭解一下新特性,為以後公司遷移到ES7.X提前做好準備,這裡把自 ...
  • 該系列上一篇文章《Elasticsearch必知必會的乾貨知識一:ES索引文檔的CRUD》 講瞭如何進行index的增刪改查,本篇則側重講解說明如何對index進行創建、更改、遷移、查詢配置信息等。 僅創建索引:PUT index PUT /index 添加欄位設置(mappings):PUT in ...
一周排行
  • 比如要拆分“呵呵呵90909086676喝喝999”,下麵當type=0返回的是中文字元串“呵呵呵,喝喝”,type=1返回的是數字字元串“90909086676,999”, private string GetStrings(string str,int type=0) { IList<strin ...
  • Swagger一個優秀的Api介面文檔生成工具。Swagger可以可以動態生成Api介面文檔,有效的降低前後端人員關於Api介面的溝通成本,促進項目高效開發。 1、使用NuGet安裝最新的包:Swashbuckle.AspNetCore。 2、編輯項目文件(NetCoreTemplate.Web.c ...
  • 2020 年 7 月 30 日, 由.NET基金會和微軟 將舉辦一個線上和為期一天的活動,包括 微軟 .NET 團隊的演講者以及社區的演講者。本次線上大會 專註.NET框架構建微服務,演講者分享構建和部署雲原生應用程式的最佳實踐、模式、提示和技巧。有關更多信息和隨時瞭解情況:https://focu... ...
  • #abp框架Excel導出——基於vue #1.技術棧 ##1.1 前端採用vue,官方提供 UI套件用的是iview ##1.2 後臺是abp——aspnetboilerplate 即abp v1,https://github.com/aspnetboilerplate/aspnetboilerp ...
  • 前言 本文的文字及圖片來源於網路,僅供學習、交流使用,不具有任何商業用途,版權歸原作者所有,如有問題請及時聯繫我們以作處理。 作者:碧茂大數據 PS:如有需要Python學習資料的小伙伴可以加下方的群去找免費管理員領取 input()輸入 Python提供了 input() 內置函數從標準輸入讀入一 ...
  • 從12年到20年,python以肉眼可見的趨勢超過了java,成為了當今It界人人皆知的編程語言。 python為什麼這麼火? 網路編程語言搜索指數 適合初學者 Python具有語法簡單、語句清晰的特點,這就讓初學者在學習階段可以把精力集中在編程對象和思維方法上。 大佬都在用 Google,YouT ...
  • 在社會上存在一種普遍的對培訓機構的學生一種歧視的現象,具體表現在,比如:當你去公司面試的時候,一旦你說了你是培訓機構出來的,那麼基本上你就涼了,那麼你瞞著不說,然後又通過了面試成功入職,但是以後一旦在公司被髮現有培訓經歷,可能會面臨被降薪,甚至被辭退,培訓機構出來的學生,在用人單位眼裡就是能力低下的 ...
  • from typing import List# 這道題看了大佬寫的代碼,經過自己的理解寫出來了。# 從最外圍的四周找有沒有為O的,如果有的話就進入深搜函數,然後深搜遍歷# 判斷上下左右的位置是否為Oclass Solution: def solve(self, board: List[List[s ...
  • import requests; import re; import os; # 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, li ...
  • import requests; import re; import os; import parsel; 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537. ...