目錄
一.PostgreSQL簡介
二.PostgreSQL基礎
1.pgAdmin4可視化工具
2.資料型別
3.創建資料庫
4.洗掉資料庫
5.創建表 & 洗掉表 & 修改表
6.SCHEMA模式
7.INSERT INTO插入
8.SELECT查詢
9.UPDATE更新
10.DELETE洗掉
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
3.創建資料庫
4.洗掉資料庫
5.創建表 & 洗掉表 & 修改表
6.SCHEMA模式
7.INSERT INTO插入
8.SELECT查詢
9.UPDATE更新
10.DELETE洗掉
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
5.創建表 & 洗掉表 & 修改表
6.SCHEMA模式
7.INSERT INTO插入
8.SELECT查詢
9.UPDATE更新
10.DELETE洗掉
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
7.INSERT INTO插入
8.SELECT查詢
9.UPDATE更新
10.DELETE洗掉
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
9.UPDATE更新
10.DELETE洗掉
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
11.運算子
12.運算式
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
13.DISTINCT、WHERE、AND、OR、NOT NULL、LIKE、IN、NOT IN、BETWEEN、子查詢、HAVING、ORDER BY、GROUP BY、LIMIT
14.WITH子句
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
三.PostgreSQL高級
1.約束
2.JOIN表連接
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
3.UNION
4.AS別名
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
5.觸發器
6.INDEX索引
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
7.TRUNCATE TABLE洗掉表資料
8.VIEW 視圖
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
9.TRANSACTION 事務
10.LOCK 鎖
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
11.子查詢
12.SERIAL自增長
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
13.SEQUENCE序列
14.GANT權限
15.常用函式
四.補充
五.參考
一.PostgreSQL簡介
PostgreSQL資料庫一種關系型資料庫，是當前世界上最先進的開源關系型資料庫，
15.常用函式
四.補充
五.參考
一.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.觸發器
觸發器是資料庫的回呼函式，可以在指定的資料庫事件發生時，自動執行和呼叫，
觸發器的關鍵點：
- 觸發器的觸發情況：
- 在執行操作之前(在檢查約束并進行增刪改之前)
- 在執行操作之后(在檢查約束并進行增刪改之后)
- 更新操作（對一個視圖進行增刪改時）
- 觸發器的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索引
索引是一種特殊的加速資料檢索的特殊表查詢，它是一個指向表中資料的指標，索引就像是一本書的目錄， 索引可以加速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;
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.CAST(...AS)
--CAST(...AS)的作用是型別轉換，等同于::
select cast(b.c_no as numeric) from T_C_BASE b --使用CAST(...AS)進行型別轉換
select b.c_no::numeric from T_C_BASE b --使用::進行型別轉換
select b.* from T_COMPLAIN_BASE b where b.c_no::varchar = '09070007' --使用::進行型別轉換
2.ANY & SOME & ALL
ANY、SOME、ALL是一種運算子，其中ANY等價于SOME，
ANY、SOME、ALL左邊是一個值，右邊是一個集合，
ANY、SOME:該值大于或小于集合中其中一個值，等式成立，
ALL：該值大于或小于集合中所有的值，等式成立，
select b.PARENT_ID from T_B_TEAM b where b.PARENT_ID = SOME(VALUES(303),(657),(786)) --SOME
select b.PARENT_ID from T_B_TEAM b where b.PARENT_ID = ANY('{303,567,1234,676}'::integer[]) --ANY
select b.PARENT_ID from T_BRANCH_TEAM b where b.PARENT_ID = All('{303,567,1234,676}'::integer[])--ALL
3.string_to_array & string_agg & array_to_string & array_agg
| 函式名 | 作用 | 補充 |
|---|---|---|
| string_to_array | 將字串轉換為陣列 | |
| string_agg | 將資料轉換為字串 | 一般搭配string_to_array使用 |
| array_to_string | 將陣列轉換為字串 | |
| array_agg | 將資料轉換為陣列 | 一般搭配array_to_string使用 |
五.參考
1.PostgreSQL菜鳥手冊
2.PostgreSQL中文手冊
3.一些其他零散的博客~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1163.html
標籤：PostgreSQL
上一篇：PostgreSQL中的位圖索引掃描（bitmap index scan）
下一篇：PostgreSQL執行計劃概述