国内av免费在线,久久精品五月天导

PostgreSQL 常用命令速查表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        連接服務(wù)器
            查看幫助
            查看連接
            查看版本
            配置參數(shù)
            退出客戶端
        角色、用戶和組
            創(chuàng)建角色
            查看角色
            修改密碼
            設(shè)置密碼失效時(shí)間
            用戶授權(quán)
            查看權(quán)限
            撤銷權(quán)限
            設(shè)置當(dāng)前角色
            刪除角色
        數(shù)據(jù)庫和模式
            查看數(shù)據(jù)庫
            創(chuàng)建數(shù)據(jù)庫
            修改數(shù)據(jù)庫
            刪除數(shù)據(jù)庫
            查看模式
            創(chuàng)建模式
            修改模式
            模式搜索路徑
            刪除模式
        管理數(shù)據(jù)表
            創(chuàng)建表
            查看所有表
            查看表結(jié)構(gòu)
            增加字段
            修改字段
            重命名字段
            刪除字段
            增加約束
            刪除約束
            重命名表
            刪除表
        索引
            創(chuàng)建索引
            查看索引
            維護(hù)索引
            刪除索引
        管理表空間
            創(chuàng)建表空間
            查看表空間
            修改表空間
            刪除表空間
        備份與還原
            使用 pg_dump 執(zhí)行邏輯備份
            使用 psql/pg_restore 執(zhí)行還原
            備份/還原整個(gè)數(shù)據(jù)庫集群
            使用 COPY 導(dǎo)入/導(dǎo)出表數(shù)據(jù)
        查詢語句
            單表查詢
            查詢條件
            排序顯示
            限定數(shù)量
            分組操作
            多表連接
            子查詢
            集合運(yùn)算
            通用表表達(dá)式
        DML 語句
            插入數(shù)據(jù)
            更新數(shù)據(jù)
            刪除數(shù)據(jù)
            合并數(shù)據(jù)
        事務(wù)控制
            開始事務(wù)
            提交事務(wù)
            回滾事務(wù)
            事務(wù)保存點(diǎn)
            隔離級(jí)別
        視圖
            創(chuàng)建視圖
            查看所有視圖
            查看視圖定義
            修改視圖
            刪除視圖
        存儲(chǔ)過程/函數(shù)
            創(chuàng)建存儲(chǔ)過程/函數(shù)
            調(diào)用存儲(chǔ)過程/函數(shù)
            重命名存儲(chǔ)過程/函數(shù)
            刪除存儲(chǔ)過程/函數(shù)
        觸發(fā)器
            創(chuàng)建觸發(fā)器
            查看觸發(fā)器
            修改觸發(fā)器
            啟用/禁用觸發(fā)器
            刪除觸發(fā)器

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。本文為大家精心整理了 PostgreSQL 數(shù)據(jù)庫中最常用的語句和命令，點(diǎn)擊收藏以備不時(shí)之需！如果需要 PDF 版本，可以點(diǎn)此下載。

連接服務(wù)器

使用 psql 客戶端工具連接 PostgreSQL 服務(wù)器的命令行如下：

psql -h hostname -p port -U username -W dbname

其中，hostname 表示服務(wù)器主機(jī)名，默認(rèn)為本機(jī)；port 表示 PostgreSQL 實(shí)例服務(wù)端口，默認(rèn)為 5432；username 表示用戶名，默認(rèn)為當(dāng)前操作系統(tǒng)用戶；-W 提示輸入密碼，默認(rèn)行為；dbname 表示要連接的數(shù)據(jù)庫，默認(rèn)和用戶名相同。例如：

[tony@sqlhost ~]> psql -h 192.168.56.104 -p 5432 -U tony hrdb
Password for user tony:
psql (12.4)
Type "help" for help.

hrdb=>

以上命令使用 tony 用戶通過 5432 端口連接到服務(wù)器 192.168.56.104 上的 hrdb 數(shù)據(jù)庫。
查看幫助

在 psql 提示符中輸入help獲取使用幫助。

hrdb=> help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

使用\?命令可以獲取 psql 命令相關(guān)的幫助信息：

hrdb=> \?
General
\copyright             show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose            show most recent error message at maximum verbosity
\g [FILE] or ;         execute query (and send results to file or |pipe)
\gdesc                 describe result of query, without executing it
\gexec                 execute query, then execute each value in its result
\gset [PREFIX]         execute query and store results in psql variables
\gx [FILE]             as \g, but forces expanded output mode
\q                     quit psql
\watch [SEC]           execute query every SEC seconds

Help
\? [commands]          show help on backslash commands
\? options             show help on psql command-line options
\? variables           show help on special variables
\h [NAME]              help on syntax of SQL commands, * for all commands
...

使用\h命令可以獲取 SQL 命令相關(guān)的幫助信息：

hrdb=> \h
Available help:
ABORT                            CREATE FOREIGN DATA WRAPPER      DROP ROUTINE
ALTER AGGREGATE                  CREATE FOREIGN TABLE             DROP RULE
ALTER COLLATION                  CREATE FUNCTION                  DROP SCHEMA
ALTER CONVERSION                 CREATE GROUP                     DROP SEQUENCE
ALTER DATABASE                   CREATE INDEX                     DROP SERVER
ALTER DEFAULT PRIVILEGES         CREATE LANGUAGE                  DROP STATISTICS
...

hrdb=> \h insert
Command:     INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

URL: https://www.postgresql.org/docs/12/sql-insert.html

查看連接

使用 psql 中的\conninfo命令可以查看當(dāng)前連接信息。

hrdb=> \conninfo
You are connected to database "hrdb" as user "tony" on host "192.168.56.104" at port "5432".

1
2

查詢系統(tǒng)視圖 pg_catalog.pg_stat_activity 可以列出所有的服務(wù)器進(jìn)程。

psql 中的 SQL 命令以;或者\(yùn)g結(jié)束。其中，client backend 是客戶端的連接進(jìn)程。

如果想要取消正在運(yùn)行中的查詢，可以執(zhí)行以下語句：

SELECT pg_cancel_backend(pid);

其中，pid 是執(zhí)行語句的后臺(tái)進(jìn)程 id，通過上文中的查詢可以獲得。

如果想要強(qiáng)制終止某個(gè)后臺(tái)連接進(jìn)程，可以執(zhí)行以下語句：

SELECT pg_terminate_backend(pid);

其中，pid 是執(zhí)行語句的后臺(tái)進(jìn)程 id，通過上文中的查詢可以獲得。

??關(guān)于如何查看和終止 PostgreSQL 連接會(huì)話的詳細(xì)內(nèi)容，可以參考這篇文章。

查看版本

例如，以下語句可以查看 PostgreSQL 服務(wù)器的版本：

hrdb=> SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

配置參數(shù)

PostgreSQL 運(yùn)行時(shí)的參數(shù)設(shè)置可以通過SHOW語句進(jìn)行查看：

SHOW name
SHOW ALL

例如：

hrdb=> show shared_buffers;
shared_buffers
----------------
256MB
(1 row)

運(yùn)行時(shí)的參數(shù)可以通過SET語句、修改 postgresql.conf 配置文件、設(shè)置 PGOPTIONS 環(huán)境變量（使用 libpq 或者基于 libpq應(yīng)用連接）或者啟動(dòng)服務(wù)時(shí)的命令行參數(shù)進(jìn)行設(shè)置。

使用 SET 語句設(shè)置參數(shù)的命令如下：

SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }

如果通過配置文件修改了參數(shù)，可以使用以下語句重新加載配置，而不需要重啟服務(wù)：

SELECT pg_reload_conf();

??關(guān)于 PostgreSQL 服務(wù)器參數(shù)的配置和優(yōu)化，可以參考這篇文章。

退出客戶端

使用exit、quit或者\(yùn)q命令退出 psql 客戶端：

postgres=# \q
[root@sqlhost ~]#

??關(guān)于 PostgreSQL 客戶端工具 psql 的詳細(xì)使用，可以參考這篇文章。

角色、用戶和組

PostgreSQL 通過角色的概念來控制數(shù)據(jù)庫的訪問權(quán)限。角色又包含了兩種概念，具有登錄權(quán)限的角色稱為用戶，包含其他成員（也是角色）的角色稱為組（group）。因此，一個(gè)角色可以是一個(gè)用戶，也可以是一個(gè)組，或者兩者都是。

??PostgreSQL 角色與用戶管理的詳細(xì)內(nèi)容可以參考這篇文章。

創(chuàng)建角色

使用CREATE ROLE語句創(chuàng)建一個(gè)角色：

CREATE ROLE role_name [ [ WITH ] option [ ... ] ]

其中，role_name 表示角色名、用戶名或者組名；如果指定了 LOGIN 屬性表示創(chuàng)建用戶，PASSWORD 屬性用于指定用戶密碼。例如：

CREATE ROLE tony LOGIN PASSWORD 'Pswd#123';

CREATE GROUP tony LOGIN PASSWORD 'Pswd#123';

CREATE USER tony PASSWORD 'Pswd#123';

以上語句創(chuàng)建了一個(gè)角色 tony，并且設(shè)置了登錄密碼。

??CREATE USER 和 CREATE GROUP 都是 CREATE ROLE 的同義詞，只是 CREATE USER 默認(rèn)指定了 LOGIN 選項(xiàng)。

一個(gè)角色被創(chuàng)建之后，可以通過ALTER ROLE語句修改它的屬性。例如，以下命令為用戶 tony 設(shè)置了創(chuàng)建數(shù)據(jù)庫的權(quán)限：

ALTER ROLE tony createdb;

ALTER USER tony createdb;

查看角色

PostgreSQL 中的角色信息存儲(chǔ)在系統(tǒng)視圖 pg_catalog.pg_roles 中：

SELECT rolname, rolsuper, rolcanlogin
FROM pg_catalog.pg_roles;

rolname                  |rolsuper|rolcanlogin|
-------------------------|--------|-----------|
pg_monitor               |false   |false      |
pg_read_all_settings     |false   |false      |
pg_read_all_stats        |false   |false      |
pg_stat_scan_tables      |false   |false      |
pg_read_server_files     |false   |false      |
pg_write_server_files    |false   |false      |
pg_execute_server_program|false   |false      |
pg_signal_backend        |false   |false      |
postgres                 |true    |true       |
monitor_system_stats     |false   |false      |
tony                     |false   |true       |

查詢結(jié)果中大部分都是系統(tǒng)創(chuàng)建的角色。另外，也可以通過 pg_catalog.pg_user 查看用戶信息，或者使用 psql 中的\du命令列出角色。

user 或者 current_user 函數(shù)可以用于查看當(dāng)前的用戶：

SELECT current_user;

current_user|
------------|
postgres |

修改密碼

使用ALTER ROLE語句修改角色的密碼：

ALTER ROLE tony PASSWORD 'Pswd123@';

ALTER USER tony PASSWORD 'Pswd123@';

另外，也可以使用 psql 中的\password [USERNAME]命令修改用戶的密碼。
設(shè)置密碼失效時(shí)間

使用ALTER ROLE語句設(shè)置密碼的失效時(shí)間：

ALTER ROLE tony VALID UNTIL '2020-12-12 00:00:00';

ALTER USER tony VALID UNTIL 'infinity';

第一個(gè)語句將 tony 的密碼失效時(shí)間設(shè)置為 2020 年 12 月 12 日零點(diǎn)；第二個(gè)語句取消 tony 的密碼失效時(shí)間，意味著永遠(yuǎn)有效。
用戶授權(quán)

PostgreSQL 使用GRANT語句進(jìn)行數(shù)據(jù)庫對(duì)象的授權(quán)操作。以表為例，基本的授權(quán)語法如下：

GRANT privilege_list | ALL
ON [ TABLE ] table_name
TO role_name;

其中，privilege_list 權(quán)限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等，ALL 表示表上的所有權(quán)限。例如，以下語句將 employees、departments 和 jobs 表上的增刪改查權(quán)限授予了 tony 用戶：

GRANT SELECT, INSERT, UPDATE, DELETE
ON employees, departments, jobs
TO tony;

另一種授權(quán)方式是將某個(gè)組角色的成員資格授予其他用戶，使得這些用戶可以自動(dòng)獲得該角色的權(quán)限。例如：

GRANT monitor_system_stats TO tony;

ALTER GROUP monitor_system_stats ADD USER tony;

以上語句將用戶 tony 添加為角色 monitor_system_stats 的成員。
查看權(quán)限

系統(tǒng)視圖 information_schema.role_table_grants 或者 information_schema.table_privileges 包含了授予用戶的表和視圖權(quán)限：

SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'tony';

table_catalog|table_schema|table_name       |privilege_type|
-------------|------------|-----------------|--------------|
hrdb         |public      |employees        |INSERT        |
hrdb         |public      |employees        |SELECT        |
hrdb         |public      |employees        |UPDATE        |
hrdb         |public      |employees        |DELETE        |
...

其他對(duì)象的權(quán)限可以通過 information_schema 視圖 role_column_grants（column_privileges）、role_routine_grants（routine_privileges）、role_udt_grants（udt_privileges）、role_usage_grants（usage_privileges）等進(jìn)行查看。
撤銷權(quán)限

PostgreSQL 使用REVOKE語句撤銷數(shù)據(jù)庫對(duì)象上的權(quán)限。同樣以表為例，基本的撤銷授權(quán)語句如下：

REVOKE privilege_list | ALL
ON TABLE table_name
FROM role_name;

其中的參數(shù)與GRANT語句相同。以下語句撤銷了用戶 tony 對(duì) employees、departments 和 jobs 表的增刪改查權(quán)限：

REVOKE SELECT, INSERT, UPDATE, DELETE
ON employees, departments, jobs
FROM tony;

同樣，可以使用 REVOKE 語句撤銷某個(gè)用的成員資格。例如以下語句撤銷了用戶 tony 的 monitor_system_stats 成員資格：

REVOKE monitor_system_stats
FROM tony;

ALTER GROUP monitor_system_stats DROP USER tony;

設(shè)置當(dāng)前角色

SET ROLE語句可以設(shè)置當(dāng)前會(huì)話的用戶 ID。例如：

SELECT session_user, current_user;
session_user|current_user|
------------|------------|
postgres |postgres |

SET ROLE tony;

SELECT session_user, current_user;
session_user|current_user|
------------|------------|
postgres |tony |

以上語句將當(dāng)前用戶設(shè)置為 tony。此時(shí)，該會(huì)話只擁有 tony 用戶的權(quán)限，不再擁有超級(jí)用戶權(quán)限?？梢允褂靡韵抡Z句要恢復(fù)初始狀態(tài)的會(huì)話權(quán)限：

SET ROLE NONE;

RESET ROLE;

刪除角色

刪除角色可以使用DROP ROLE語句。例如以下語句刪除了用戶 tony：

DROP ROLE IF EXISTS tony;

另外，DROP USER和DROP GROUP語句都是 DROP ROLE 的同義詞。
數(shù)據(jù)庫和模式

PostgreSQL 中的數(shù)據(jù)庫（Database）由一組相關(guān)的對(duì)象組成，例如表、索引、視圖、存儲(chǔ)過程等。數(shù)據(jù)庫中的對(duì)象使用模式（Schema）進(jìn)行組織。因此，一個(gè)數(shù)據(jù)庫由多個(gè)模式組成，一個(gè)模式由許多對(duì)象組成。

??PostgreSQL 數(shù)據(jù)庫和模式管理的詳細(xì)內(nèi)容可以參考這篇文章。

查看數(shù)據(jù)庫

使用 psql 工具的\l命令可以查看當(dāng)前集群中的數(shù)據(jù)庫：

也可以通過 pg_catalog.pg_database 查看所有的數(shù)據(jù)庫：

SELECT datname
FROM pg_catalog.pg_database;
datname |
---------|
postgres |
template1|
template0|
pagila   |
ds2      |
hrdb     |

創(chuàng)建數(shù)據(jù)庫

PostgreSQL 使用CREATE DATABASE語句創(chuàng)建數(shù)據(jù)庫：

CREATE DATABASE db_name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

其中，db_name 是數(shù)據(jù)庫的名稱，OWNER 用于指定數(shù)據(jù)庫的擁有者，TEMPLATE 是創(chuàng)建數(shù)據(jù)庫時(shí)使用的模板（默認(rèn)使用 template1 數(shù)據(jù)庫），ENCODING 用于設(shè)置數(shù)據(jù)庫的字符集編碼。

例如，以下語句創(chuàng)建了一個(gè)名為 testdb 的數(shù)據(jù)庫：

CREATE DATABASE testdb;

修改數(shù)據(jù)庫

PostgreSQL 使用ALTER DATABASE語句修改數(shù)據(jù)庫的屬性和配置：

ALTER DATABASE db_name RENAME TO new_name

ALTER DATABASE db_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE db_name SET TABLESPACE new_tablespace

ALTER DATABASE db_name [ [ WITH ] option [ ... ] ]

option:
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

例如，以下語句將 testdb 的名稱修改為 newdb：

ALTER DATABASE testdb RENAME TO newdb;

除了修改常見的屬性之外，ALTER DATABASE語句還可以用于修改運(yùn)行時(shí)配置變量的會(huì)話默認(rèn)值：

ALTER DATABASE db_name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE db_name RESET configuration_parameter

例如，以下語句將會(huì)默認(rèn)禁用數(shù)據(jù)庫 newdb 中的索引掃描：

ALTER DATABASE newdb SET enable_indexscan TO off;

刪除數(shù)據(jù)庫

PostgreSQL 使用DROP DATABASE語句刪除數(shù)據(jù)庫，該數(shù)據(jù)庫中的所有對(duì)象以及與該數(shù)據(jù)庫相關(guān)的數(shù)據(jù)目錄也會(huì)被刪除：

DROP DATABASE [IF EXISTS] db_name;

我們將 newdb 數(shù)據(jù)庫和相關(guān)的數(shù)據(jù)刪除：

DROP DATABASE newdb;

如果存在任何目標(biāo)為該數(shù)據(jù)庫的連接，無法執(zhí)行刪除操作。
查看模式

創(chuàng)建了數(shù)據(jù)庫之后，還需要?jiǎng)?chuàng)建模式才能夠存儲(chǔ)數(shù)據(jù)庫對(duì)象。通常在創(chuàng)建一個(gè)新的數(shù)據(jù)庫時(shí)，默認(rèn)會(huì)創(chuàng)建一個(gè)公共模式 public。我們再次創(chuàng)建一個(gè)數(shù)據(jù)庫 testdb：

CREATE DATABASE testdb;

postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".

其中，\c用于連接數(shù)據(jù)庫。然后使用\dn查看當(dāng)前數(shù)據(jù)庫中的模式：

testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)

另外，也可以系統(tǒng)視圖 pg_catalog.pg_namespace 查詢所有的模式：

SELECT *
FROM pg_catalog.pg_namespace;
oid |nspname           |nspowner|nspacl                             |
-----|------------------|--------|-----------------------------------|
   99|pg_toast          |      10|NULL                               |
12314|pg_temp_1         |      10|NULL                               |
12315|pg_toast_temp_1   |      10|NULL                               |
   11|pg_catalog        |      10|{postgres=UC/postgres,=U/postgres} |
2200|public            |      10|{postgres=UC/postgres,=UC/postgres}|
13887|information_schema|      10|{postgres=UC/postgres,=U/postgres} |

查詢結(jié)果還返回了系統(tǒng)提供的其他模式。
創(chuàng)建模式

PostgreSQL 使用CREATE SCHEMA語句創(chuàng)建一個(gè)新的模式：

CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_name ]

其中，schema_name 是模式名，role_name 是模式的擁有者，默認(rèn)為執(zhí)行該語句的用戶。例如：

CREATE SCHEMA app AUTHORIZATION tony;

該語句創(chuàng)建了一個(gè)新的模式 app，擁有者為 tony。
修改模式

如果需要修改已有模式的屬性，可以使用ALTER SCHEMA語句：

ALTER SCHEMA schema_name RENAME TO new_name
ALTER SCHEMA schema_name OWNER TO new_owner

例如，以下語句將模式 app 重命名為 sale：

ALTER SCHEMA app RENAME TO sale;

模式搜索路徑

當(dāng)我們訪問數(shù)據(jù)表時(shí)，完整的對(duì)象名應(yīng)該是 database.schema.table。例如：

SELECT count(*) FROM hrdb.public.employees;

為了方便書寫，常?？梢灾苯邮褂帽砻M(jìn)行訪問，此時(shí) PostgreSQL 按照預(yù)定義的搜索路徑查找不同模式下的對(duì)象。搜索路徑由不同的模式名稱組成，可以使用 SHOW 語句查看：

show search_path;
search_path
-----------------
"$user", public
(1 row)

默認(rèn)的搜索路徑是與當(dāng)前用戶同名的模式，加上 public 模式。不僅僅是對(duì)象查找，其他語句也會(huì)使用搜索路徑。例如 CREATE TABLE 默認(rèn)會(huì)在 “$user” 模式下創(chuàng)建表，如果該模式不存在，則在 public 模式下創(chuàng)建。

可以使用 SET 命令修改搜索路徑，例如：

SET search_path TO sale,public;

刪除模式

使用DROP SCHEMA語句刪除模式：

DROP SCHEMA [ IF EXISTS ] schema_name [ CASCADE | RESTRICT ]

如果模式中存在對(duì)象，需要使用 CASCADE 級(jí)聯(lián)刪除；否則，無法刪除模式。例如，以下語句將會(huì)刪除模式 sale：

DROP SCHEMA sale;

管理數(shù)據(jù)表

??關(guān)于 PostgreSQL 表管理的詳細(xì)內(nèi)容可以參考這篇文章。

創(chuàng)建表

PostgreSQL 使用CREATE TABLE語句創(chuàng)建表：

CREATE TABLE [ IF NOT EXISTS ] table_name
(
column_name data_type column_constraint,
column_name data_type,
...,
table_constraint
);

其中，table_name 指定了新表的名稱；括號(hào)內(nèi)是字段的定義，column_name 是字段的名稱，data_type 是它的類型，column_constraint 是可選的字段約束；多個(gè)字段使用逗號(hào)進(jìn)行分隔；最后，table_constraint 是可選的表級(jí)約束。

以下語句用于創(chuàng)建部門表和員工表：

CREATE TABLE departments
    ( department_id    INTEGER NOT NULL PRIMARY KEY
    , department_name CHARACTER VARYING(30) NOT NULL
    ) ;

CREATE TABLE employees
    ( employee_id    INTEGER NOT NULL
    , first_name     CHARACTER VARYING(20)
    , last_name      CHARACTER VARYING(25) NOT NULL
    , email          CHARACTER VARYING(25) NOT NULL
    , phone_number   CHARACTER VARYING(20)
    , hire_date      DATE NOT NULL
    , salary         NUMERIC(8,2)
    , commission_pct NUMERIC(2,2)
    , manager_id     INTEGER
    , department_id INTEGER
   , CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
   , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments(department_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees(employee_id)
    ) ;

除了自定義表的結(jié)構(gòu)之外，PostgreSQL 還提供了另一個(gè)創(chuàng)建表的方法，就是通過一個(gè)查詢的結(jié)果創(chuàng)建新表：

CREATE TABLE [ IF NOT EXISTS ] table_name
AS query
[ WITH [ NO ] DATA ];

例如，我們可以基于 employees 復(fù)制出一個(gè)新的表：

CREATE TABLE emp1
AS
SELECT *
FROM employees;

emp1 只會(huì)復(fù)制 employees 的字段類型和名稱，以及查詢的結(jié)果，不會(huì)復(fù)制任何約束和索引。
查看所有表

在 psql 中使用\d命令可以查看當(dāng)前數(shù)據(jù)庫中的所有表：

也可以通過 information_schema.tables 查看表的信息：

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

查看表結(jié)構(gòu)

PostgreSQL 可以使用 psql 工具的\d命令查看表結(jié)構(gòu)：

增加字段

增加字段使用ALTER TABLE ... ADD COLUMN語句：

ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;

添加字段與創(chuàng)建表時(shí)的字段選項(xiàng)相同，包含字段名稱、字段類型以及可選的約束。以下語句為 employees 表增加一個(gè)字段：

ALTER TABLE employees ADD COLUMN description varchar(100);

修改字段

修改字段使用ALTER TABLE ... ALTER COLUMN語句。以下語句為字段 description 設(shè)置了一個(gè)默認(rèn)值：

ALTER TABLE employees ALTER COLUMN description SET DEFAULT 'No description';

以下語句將字段 description 的數(shù)據(jù)類型修改為 text：

ALTER TABLE employees ALTER COLUMN description TYPE text;

重命名字段

使用ALTER TABLE ... RENAME COLUMN語句為字段指定一個(gè)新的名稱，以下語句將 description 字段重命名為 notes：

ALTER TABLE employees
RENAME COLUMN description TO notes;

刪除字段

刪除字段使用ALTER TABLE ... DROP COLUMN語句，以下語句用于刪除 notes 字段：

ALTER TABLE employees DROP COLUMN IF EXISTS notes;

增加約束

為表增加約束可以使用ALTER TABLE語句：

ALTER TABLE table_name ADD table_constraint;

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT expression;

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

刪除約束

刪除約束同樣可以使用ALTER TABLE語句：

ALTER TABLE table_name DROP CONSTRAINT IF EXISTS constraint_name [ RESTRICT | CASCADE ];

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

重命名表

重命名表可以使用ALTER TABLE ... DROP COLUMN語句：

ALTER TABLE [ IF EXISTS ] table_name
RENAME TO new_name;

刪除表

刪除表可以使用DROP TABLE語句：

DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

如果存在依賴于 table_name 的視圖或外鍵約束，需要指定 CASCADE 選項(xiàng)，執(zhí)行級(jí)聯(lián)刪除。
索引

索引（Index）可以用于提高數(shù)據(jù)庫的查詢性能；但是索引也需要進(jìn)行讀寫，同時(shí)還會(huì)占用更多的存儲(chǔ)空間；因此了解并適當(dāng)利用索引對(duì)于數(shù)據(jù)庫的優(yōu)化至關(guān)重要。

??關(guān)于 PostgreSQL 索引和優(yōu)化的詳細(xì)內(nèi)容可以參考這篇文章。

創(chuàng)建索引

PostgreSQL 為主鍵和唯一約束自動(dòng)創(chuàng)建相應(yīng)的索引，另外我們也可以手動(dòng)創(chuàng)建索引。創(chuàng)建索引的命令如下：

CREATE INDEX index_name ON table_name
[USING method]
(column_name [ASC | DESC] [NULLS FIRST | NULLS LAST], ...);

其中 index_name 是索引的名稱，table_name 是表的名稱；method 表示索引的類型，例如 btree、hash、gist、spgist、gin 或者 brin。默認(rèn)為 btree；column_name 是字段名，ASC 表示升序排序（默認(rèn)值），DESC 表示降序索引；NULLS FIRST 和 NULLS LAST 表示索引中空值的排列順序，升序索引時(shí)默認(rèn)為 NULLS LAST，降序索引時(shí)默認(rèn)為 NULLS FIRST。
查看索引

PostgreSQL 提供了一個(gè)關(guān)于索引的視圖 pg_catalog.pg_indexes，可以用于查看索引的信息：

SELECT * FROM pg_catalog.pg_indexes;

psql 工具的\di命令也可以用于查看數(shù)據(jù)庫中的索引列表。
維護(hù)索引

PostgreSQL 提供了一些修改和重建索引的方法：

ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX index_name SET TABLESPACE tablespace_name;

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } index_name;

其中，兩個(gè) ALTER INDEX 語句分別用于重命名索引和移動(dòng)索引到其他表空間；REINDEX 語句用于重建索引數(shù)據(jù)，支持不同級(jí)別的索引重建。
刪除索引

刪除索引使用以下命令：

DROP INDEX IF EXISTS index_name [ CASCADE | RESTRICT ];

CASCADE 表示級(jí)聯(lián)刪除其他依賴該索引的對(duì)象；RESTRICT（默認(rèn)值）表示如果存在依賴于該索引的對(duì)象，將會(huì)拒絕刪除操作。
管理表空間

在 PostgreSQL 中，表空間（tablespace）表示數(shù)據(jù)文件的存放目錄，這些數(shù)據(jù)文件代表了數(shù)據(jù)庫的對(duì)象，例如表或索引。當(dāng)我們訪問表時(shí)，系統(tǒng)通過它所在的表空間定位到對(duì)應(yīng)數(shù)據(jù)文件所在的位置。

??PostgreSQL 表空間管理的詳細(xì)內(nèi)容可以參考這篇文章。

創(chuàng)建表空間

創(chuàng)建新的表空間使用CREATE TABLESPACE語句：

CREATE TABLESPACE tablespace_name
OWNER user_name
LOCATION 'directory';

表空間的名稱不能以 ‘pg_’ 開頭，它們是系統(tǒng)表空間的保留名稱；LOCATION 參數(shù)必須指定絕對(duì)路徑名，指定的目錄必須是一個(gè)已經(jīng)存在的空目錄，PostgreSQL 操作系統(tǒng)用戶（postgres）必須是該目錄的擁有者，以便能夠進(jìn)行文件的讀寫。

??PostgreSQL 支持在CREATE DATABASE、CREATE TABLE、CREATE INDEX 以及 ADD CONSTRAINT 語句中指定 tablespace_name 選項(xiàng)，覆蓋默認(rèn)的表空間（pg_default）；也可以使用相應(yīng)的 ALTER … 語句將對(duì)象從一個(gè)表空間移到另一個(gè)表空間。

查看表空間

系統(tǒng)視圖 pg_catalog.pg_tablespace 中包含了所有表空間的信息：

SELECT * FROM pg_catalog.pg_tablespace;

另外，psql 工具的\db命令也可以列出所有表空間。
修改表空間

如果需要修改表空間的定義，可以使用ALTER TABLESPACE語句：

ALTER TABLESPACE tablespace_name RENAME TO new_name;

ALTER TABLESPACE tablespace_name OWNER TO new_owner;

ALTER TABLESPACE tablespace_name SET ( tablespace_option = value [, ... ] );
ALTER TABLESPACE tablespace_name RESET ( tablespace_option [, ... ] );

第一個(gè)語句用于表空間的重命名；第二個(gè)語句用于修改表空間的擁有者；最后兩個(gè)語句用于設(shè)置表空間的參數(shù)。PostgreSQL 支持設(shè)置的表空間參數(shù)包括 seq_page_cost、random_page_cost 以及 effective_io_concurrency，它們用于查詢計(jì)劃器選擇執(zhí)行計(jì)劃時(shí)的代價(jià)評(píng)估。
刪除表空間

對(duì)于不再需要的表空間，可以使用DROP TABLESPACE語句進(jìn)行刪除：

DROP TABLESPACE [ IF EXISTS ] tablespace_name;

IF EXISTS可以避免刪除不存在的表空間時(shí)產(chǎn)生錯(cuò)誤信息。刪除表空間時(shí)，同時(shí)會(huì)刪除文件系統(tǒng)中對(duì)應(yīng)的表空間子目錄。
備份與還原

??關(guān)于 PostgreSQL 備份與恢復(fù)的詳細(xì)介紹可以參考這篇文章。

使用 pg_dump 執(zhí)行邏輯備份

pg_dump 是 PostgreSQL 邏輯備份工具，用于導(dǎo)出創(chuàng)建數(shù)據(jù)庫（CREATE DATABASE）和插入數(shù)據(jù)的文本文件或者其他格式文件。

使用 pg_dump 文本格式備份指定數(shù)據(jù)庫的命令如下：

pg_dump db_name > file_name.sql

其中，db_name 表示數(shù)據(jù)庫名；file_name 表示備份文件名。

pg_dump 也可以選擇導(dǎo)出指定的表：

pg_dump -t 'table_name*' db_name > file_name.sql

以上命令表示導(dǎo)出數(shù)據(jù)庫 db_name 中名字以 table_name 開頭的表。

使用其他格式備份數(shù)據(jù)庫的語法如下：

pg_dump -Fc db_name -f file_name.dmp

pg_dump -Fd db_name -f file_dir

pg_dump -Ft db_name -f file_name.tar

使用 psql/pg_restore 執(zhí)行還原

對(duì)于 sql 文件格式的備份，可以使用 psql 還原數(shù)據(jù)庫：

psql newdb -f file_name.sql

pg_dump 和 psql 支持的讀寫管道功能使得我們可以直接將數(shù)據(jù)庫從一個(gè)服務(wù)器導(dǎo)出到另一個(gè)服務(wù)器，例如：

pg_dump -h host1 db_name | psql -h host2 db_name

其他格式的備份需要pg_restore 工具進(jìn)行還原：

pg_restore -d newdb file_name.dmp

pg_restore -d newdb file_dir

pg_restore -d newdb file_name.tar

備份/還原整個(gè)數(shù)據(jù)庫集群

PostgreSQL 提供了導(dǎo)出數(shù)據(jù)庫集群的 pg_dumpall 工具。

pg_dumpall -f cluster.sql

pg_dumpall 導(dǎo)出 sql 文件格式的備份，還原時(shí)直接使用 psql 導(dǎo)入相關(guān)文件即可。

psql -f cluster.sql postgres

使用 COPY 導(dǎo)入/導(dǎo)出表數(shù)據(jù)

使用COPY命令可以導(dǎo)出單個(gè)表中的數(shù)據(jù)或查詢結(jié)果集：

COPY table_name
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]

查詢語句
單表查詢

??PostgreSQL 簡單查詢可以參考這篇文章。

查詢單個(gè)表中的字段：

SELECT column1, column2, ...
FROM table_name;

查詢所有字段：

SELECT * FROM table_name;

排除查詢結(jié)果中的重復(fù)數(shù)據(jù)：

SELECT DISTINCT column1, column2, ...
FROM table_name;

查詢條件

??關(guān)于 PostgreSQL 查詢條件的詳細(xì)介紹可以參考這篇文章。

使用WHERE指定查詢條件：

SELECT column1, column2, ...
FROM table
WHERE conditions;

常用的查詢條件包括：=、!=、<>、<、<=、>、>=、BETWEEN、IN、EXISTS、LIKE、AND、OR、NOT、IS [NOT] NULL 等。

模糊匹配

使用LIKE運(yùn)算符進(jìn)行簡單的字符串模式匹配：

expr LIKE pattern [ESCAPE escape_character]

其中，pattern 用于指定一個(gè)匹配模式，百分號(hào)（%）匹配任意多個(gè)字符，下劃線（_）匹配任意單個(gè)字符；escape_character 指定轉(zhuǎn)義字符。例如：

SELECT first_name,
       last_name
FROM employees
WHERE last_name LIKE 'Kin%';
first_name | last_name
------------+-----------
Steven     | King
Janette    | King
(2 rows)

另外，NOT LIKE運(yùn)算符匹配與 LIKE 相反的結(jié)果。PostgreSQL 同時(shí)還提供了不區(qū)分大小寫的[NOT] ILIKE運(yùn)算符。

復(fù)雜條件

WHERE 子句可以包含多個(gè)條件，使用邏輯運(yùn)算符（AND、OR、NOT）將它們進(jìn)行組合，并根據(jù)最終的邏輯值進(jìn)行過濾。對(duì)于AND運(yùn)算符，只有當(dāng)它兩邊的結(jié)果都為真時(shí)，最終結(jié)果才為真；否則最終結(jié)果為假，不返回結(jié)果。OR邏輯或運(yùn)算符只要有一個(gè)條件為真，結(jié)果就為真。當(dāng)我們組合 AND 和 OR 運(yùn)算符時(shí)，AND 運(yùn)算符優(yōu)先級(jí)更高。
排序顯示

??關(guān)于 PostgreSQL 的排序操作可以參考這篇文章。

指定排序字段的方式如下：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC, ...;

PostgreSQL 支持使用 NULLS FIRST（空值排在最前）和 NULLS LAST（空值排在最后）指定空值的排序位置；升序排序時(shí)默認(rèn)為 NULLS LAST，降序排序時(shí)默認(rèn)為 NULLS FIRST。
限定數(shù)量

??關(guān)于 PostgreSQL Top-N 和分頁查詢的實(shí)現(xiàn)可以參考這篇文章。

PostgreSQL 支持 SQL 標(biāo)準(zhǔn)的 FETCH 和 OFFSET 子句，以及擴(kuò)展的 LIMIT 語法限制返回結(jié)果的數(shù)量：

SELECT column1, column2, ...
FROM table_name
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows] { ROW | ROWS } ONLY];

SELECT column1, column2, ...
FROM table_name
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[LIMIT { num_rows| ALL } ]
[OFFSET m {ROW | ROWS}];

分組操作

??關(guān)于 PostgreSQL 分組匯總的具體介紹可以參考這篇文章。

指定分組和過濾：

SELECT column1, column2, agg_func()
FROM table_name
GROUP BY column1, column2
HAVING conditions;

常用的聚合函數(shù)：AVG、COUNT、MIN、MAX、SUM、STRING_AGG 等。PostgreSQL 除了支持基本的分組操作之外，還支持 3 種高級(jí)的分組選項(xiàng)：GROUPING SETS、ROLLUP 以及 CUBE。
多表連接

??關(guān)于 PostgreSQL 連接查詢這篇文章。

連接查詢用于從多個(gè)表中查詢關(guān)聯(lián)數(shù)據(jù)：

SELECT t1.column1, t2.column2, ...
FROM table1 AS t1
[INNER | LEFT | RIGHT | FULL | CROSS] JOIN table2 AS t2
ON conditions;

子查詢

??關(guān)于 PostgreSQL 子查詢的介紹可以參考這篇文章。

FROM 子句中的子查詢被稱為派生表：

SELECT column1, column2, ...
FROM (subquery) AS table_alias;

WHERE 條件中的子查詢可以與 IN、ALL、ANY 等操作符一起使用。通過增加LATERAL關(guān)鍵字，橫向子查詢可以引用左側(cè)表中的列：

SELECT table1.colum1, t2.col1, ...
FROM table1
JOIN LATERAL (
       SELECT ...
         FROM table2
        WHERE table1.col1 = table1.column1) t2;

EXISTS 操作符與關(guān)聯(lián)子查詢：

SELECT table1.column1, table1.column12, ...
FROM table1
WHERE EXISTS ( SELECT 1
FROM table2
WHERE table2.col1 = table1.col1);

集合運(yùn)算

??關(guān)于 PostgreSQL 集合運(yùn)算符的使用可以參考這篇文章。

集合運(yùn)算包括并集、交集和差集：

SELECT column1, column2
FROM table1
UNION [DISTINCT | ALL]
SELECT col1, col2
FROM table2;

SELECT column1, column2
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT col1, col2
FROM table2;

SELECT column1, column2
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT col1, col2
FROM table2;

其中 ALL 表示保留結(jié)果集中的重復(fù)記錄；DISTINCT（默認(rèn)值）表示去除查詢結(jié)果中的重復(fù)記錄。相同的集合操作符按照從左至右的順序執(zhí)行，INTERSECT 的優(yōu)先級(jí)高于 UNION 和 EXCEPT，使用括號(hào)可以修改集合操作的執(zhí)行順序。
通用表表達(dá)式

??關(guān)于PostgreSQL 通用表表達(dá)式的詳細(xì)內(nèi)容可以參考這篇文章。

通用表表達(dá)式類似于派生表或者語句級(jí)別的視圖，但是可讀性和性能更好：

WITH cte_name (col1, col2, ...) AS (
cte_query_definition
)
sql_statement;

遞歸 CTE 允許在它的定義中進(jìn)行自引用，以下查詢生成 1 到 10 的數(shù)字序列：

WITH RECURSIVE cte(n) AS (
    SELECT 1 AS n -- 初始查詢
    UNION ALL
    SELECT n+1 FROM cte WHERE n < 10) -- 遞歸查詢
SELECT * FROM cte;

DML 語句

??關(guān)于 PostgreSQL 數(shù)據(jù)修改語句的使用可以參考這篇文章。

插入數(shù)據(jù)

插入數(shù)據(jù)使用INSERT語句：

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...);

一次插入多條記錄：

INSERT INTO table_name(column1, column2, ...)
VALUES (val11,val12,...), (val21,val22,...), (val31,val32,...);

插入查詢語句的結(jié)果：

INSERT INTO table_name(column1, column2, ...)
SELECT ...;

PostgreSQL 對(duì) SQL 進(jìn)行了擴(kuò)展，可以在 INSERT 語句之后使用 RETURNING 返回插入的數(shù)據(jù)值：

INSERT INTO table_name(column1, column2, ...)
...
RETUNRING ...;

更新數(shù)據(jù)

PostgreSQL 使用UPDATE語句更新表中已有的數(shù)據(jù)：

UPDATE table_name
   SET column1 = value1,
       column2 = value2,
       ...
WHERE conditions;

其中，WHERE 決定了需要更新的數(shù)據(jù)行，只有滿足條件的數(shù)據(jù)才會(huì)更新；如果省略 WHERE 條件，將會(huì)更新表中的所有數(shù)據(jù)。

另外，PostgreSQL 還支持通過關(guān)聯(lián)其他表中的數(shù)據(jù)進(jìn)行更新：

UPDATE table1
   SET column1 = table2.salary,
       column2 = value2,
       ...
FROM table2
WHERE conditions;

PostgreSQL 同樣對(duì) UPDATE 語句進(jìn)行了擴(kuò)展，支持使用 RETURNING 返回更新后的數(shù)據(jù)值：

UPDATE table_name
SET ...
WHERE conditions
RETURNING ...;

刪除數(shù)據(jù)

刪除數(shù)據(jù)可以使用DELETE語句：

DELETE FROM table_name
WHERE conditions;

同樣，只有滿足WHERE條件的數(shù)據(jù)才會(huì)被刪除；如果省略，將會(huì)刪除表中所有的數(shù)據(jù)。

PostgreSQL 同樣支持通過關(guān)聯(lián)其他表進(jìn)行數(shù)據(jù)刪除：

DELETE
FROM table1
USING table2
WHERE conditions;

DELETE 語句也可以使用 RETURNING 返回被刪除的數(shù)據(jù)：

DELETE FROM table_name
WHERE conditions
RETURNING ...;

另外，TRUNCATE TABLE語句用于快速清除表中的全部數(shù)據(jù)：

TRUNCATE TABLE table_name;

合并數(shù)據(jù)

PostgreSQL 可以通過INSERT INTO ON CONFLICT語句實(shí)現(xiàn)數(shù)據(jù)合并（MERGE）的功能：

INSERT INTO table_name(column1, column2, ...)
...
ON CONFLICT [conflict_target]
{DO NOTHING | DO UPDATE SET ... [WHERE contidions]};

其中，conflict_target 是判斷數(shù)據(jù)是否存在沖突的條件：

( { index_column_name | ( index_expression ) } )，基于某個(gè)具有索引的字段或者表達(dá)式進(jìn)行判斷；
ON CONSTRAINT constraint_name，基于某個(gè)唯一約束進(jìn)行判斷。

事務(wù)控制

??關(guān)于 PostgreSQL 數(shù)據(jù)庫事務(wù)和事務(wù)控制語句可以參考這篇文章。

開始事務(wù)

默認(rèn)情況下，PostgreSQL 執(zhí)行任何語句都會(huì)自動(dòng)開始一個(gè)事務(wù)并提交該事務(wù)。如果有需要，可以使用BEGIN語句手動(dòng)開始一個(gè)事務(wù)：

BEGIN;

另外，也可以使用BEGIN WORK或者BEGIN TRANSACTION開始事務(wù)。
提交事務(wù)

PostgreSQL 使用COMMIT語句提交已經(jīng)打開的事務(wù)：

COMMIT;

另外，也可以使用COMMIT WORK或者COMMIT TRANSACTION提交事務(wù)。
回滾事務(wù)

ROLLBACK用于回滾當(dāng)前事務(wù)：

ROLLBACK;

另外，也可以使用ROLLBACK WORK或者ROLLBACK TRANSACTION回滾事務(wù)。
事務(wù)保存點(diǎn)

事務(wù)保存點(diǎn)可以用于回滾部分事務(wù)，SAVEPOINT語句用于在事務(wù)中定義保存點(diǎn)：

BEGIN;
...
SAVEPOINT savepoint_name;
...
ROLLBACK TO SAVEPOINT savepoint_name;
...
COMMIT;

其中，ROLLBACK TO 用于回退到保存點(diǎn)時(shí)的狀態(tài)。另外，RELEASE SAVEPOINT savepoint_name可以釋放保存點(diǎn)。
隔離級(jí)別

使用 SHOW 命令可以查看當(dāng)前的隔離級(jí)別：

SHOW transaction_isolation;

如果需要修改當(dāng)前事務(wù)的隔離級(jí)別，可以在事務(wù)的最開始執(zhí)行SET TRANSACTION命令：

BEGIN;
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };
...

視圖

??關(guān)于 PostgreSQL 視圖的概念和使用，可以參考這篇文章。

創(chuàng)建視圖

PostgreSQL 使用CREATE VIEW語句創(chuàng)建視圖：

CREATE [OR REPLACE] VIEW view_name
AS
select-statement
WITH CHECK OPTION;

WITH CHECK OPTION 選項(xiàng)可以阻止通過視圖修改或者插入視圖范圍之外的基礎(chǔ)表數(shù)據(jù)。
查看所有視圖

PostgreSQL 系統(tǒng)表 information_schema.views 中存儲(chǔ)了關(guān)于視圖的所有信息：

SELECT *
FROM information_schema.views;

psql 命令\dv也可以列出當(dāng)前數(shù)據(jù)庫中的所有視圖。
查看視圖定義

查看視圖的定義：

SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'public'
AND table_name = 'employees_it';

SELECT employees.employee_id,
    employees.first_name,
    employees.last_name,
    employees.email,
    employees.phone_number,
    employees.hire_date,
    employees.job_id,
    employees.manager_id,
    employees.department_id
   FROM employees
WHERE (employees.department_id = 60);

修改視圖

PostgreSQL 使用ALTER VIEW語句修改視圖的信息。例如重命名視圖：

ALTER VIEW IF EXISTS view_name RENAME TO new_name

刪除視圖

PostgreSQL 使用DROP VIEW語句刪除視圖：

DROP VIEW [IF EXISTS] view_name;

存儲(chǔ)過程/函數(shù)

??關(guān)于 PostgreSQL 存儲(chǔ)過程/函數(shù)的定義和使用，可以參考這篇文章。

創(chuàng)建存儲(chǔ)過程/函數(shù)

使用CREATE PROCEDURE語句創(chuàng)建PL/pgSQL 存儲(chǔ)過程：

CREATE [ OR REPLACE ] PROCEDURE
procedure_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS

D E C L A R E d e c l a r a t i o n s B E G I N s t a t e m e n t s; . . . E N D;

LANGUAGE plpgsql;

使用CREATE FUNCTION語句創(chuàng)建PL/pgSQL 函數(shù)：

CREATE [ OR REPLACE ] FUNCTION
function_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
RETURNS rettype
AS

D E C L A R E d e c l a r a t i o n s B E G I N s t a t e m e n t s; . . . E N D;

LANGUAGE plpgsql;

調(diào)用存儲(chǔ)過程/函數(shù)

調(diào)用存儲(chǔ)過程使用CALL語句：

CALL procedure_name( argument1, ... );

存儲(chǔ)函數(shù)可以像可以像內(nèi)置函數(shù)一樣在 SQL 語句中進(jìn)行調(diào)用：

SELECT function_name( argument1, ... );

重命名存儲(chǔ)過程/函數(shù)

使用ALTER PROCEDURE和ALTER FUNCTION語句修改存儲(chǔ)過程/函數(shù)的屬性，例如修改名稱：

ALTER PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;

ALTER FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name;

刪除存儲(chǔ)過程/函數(shù)

刪除存儲(chǔ)過程/函數(shù)使用DROP語句：

DROP PROCEDURE IF EXISTS procedure_name [ CASCADE | RESTRICT ];

DROP FUNCTION IF EXISTS function_name [ CASCADE | RESTRICT ];

觸發(fā)器

??關(guān)于 PostgreSQL 觸發(fā)器的詳細(xì)介紹，可以參考這篇文章。

創(chuàng)建觸發(fā)器

PostgreSQL 觸發(fā)器的創(chuàng)建分為兩步：首先，使用CREATE FUNCTION語句創(chuàng)建一個(gè)觸發(fā)器函數(shù)；然后，使用CREATE TRIGGER語句將該函數(shù)與表進(jìn)行關(guān)聯(lián)。

CREATE [ OR REPLACE ] FUNCTION trigger_function ()
RETURNS trigger
AS

D E C L A R E d e c l a r a t i o n s B E G I N s t a t e m e n t s; . . . E N D;

LANGUAGE plpgsql;

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON table_name
   [FOR [EACH] {ROW | STATEMENT}]
   [WHEN ( condition ) ]
   EXECUTE FUNCTION trigger_function;

其中，event 可以是 INSERT、UPDATE、DELETE 或者 TRUNCATE，UPDATE 支持特定字段（UPDATE OF col1, clo2）的更新操作；觸發(fā)器可以在事件之前（BEFORE）或者之后（AFTER）觸發(fā)，INSTEAD OF 只能用于替代視圖上的 INSERT、UPDATE 或者 DELETE 操作；FOR EACH ROW 表示行級(jí)觸發(fā)器，F(xiàn)OR EACH STATEMENT 表示語句級(jí)觸發(fā)器；WHEN 用于指定一個(gè)額外的觸發(fā)條件，滿足條件才會(huì)真正支持觸發(fā)器函數(shù)。
查看觸發(fā)器

視圖 information_schema.triggers 中存儲(chǔ)了關(guān)于觸發(fā)器的信息：

SELECT *
FROM information_schema.triggers;

修改觸發(fā)器

PostgreSQL 使用ALTER TRIGGER語句修改觸發(fā)器，目前只支持修改觸發(fā)器的名稱：

ALTER TRIGGER trigger_name ON table_name RENAME TO new_name;

啟用/禁用觸發(fā)器

PostgreSQL 支持觸發(fā)器的禁用和啟用：

ALTER TABLE table_name
{ENABLE | DISABLE} TRIGGER {trigger_name | ALL | USER};

刪除觸發(fā)器

刪除觸發(fā)器的語句如下：

DROP TRIGGER [IF EXISTS] trigger_name
ON table_name [RESTRICT | CASCADE];

在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

數(shù)據(jù)庫