PostgreSQL中有哪些常用的SQL語句

這期內容當中小編將會給大家帶來有關PostgreSQL中有哪些常用的SQL語句，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

　　創建新的用戶

　　創建一個新的用戶

　　CREATE USER WITH ENCRYPTED PASSWORD '';

　　用戶授權

　　授予 CONNECT 訪問權限

　　GRANT CONNECT ON DATABASE database_name TO username;

　　然后授予模式使用

　　GRANT USAGE ON SCHEMA schema_name TO username;

　　為特定表授予 SELECT 權限

　　GRANT SELECT ON table_name TO username;

　　將 SELECT 授予多個表

　　# 執行格式

　　GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

　　# 例子

　　grant select on all tables in schema public to user1;

　　如果您希望將來自動授予對新表的訪問權限，則必須更改默認值

　　ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username;

　　創建數據庫

　　創建以 utf-8 字符的數據庫，并且以 template0 為模版創建

　　CREATE DATABASE dbname WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8';

　　給指定用戶授指定數據庫所有權限

　　GRANT ALL PRIVILEGES ON DATABASE dbname to username;

　　在執行登陸操作后提示 FATAL： role 'root' is not permitted to log in.

　　alter user "root" login;

　　數據庫備份與恢復

　　備份所有數據庫

　　pg_dumpall > db.out

　　恢復所有數據庫

　　# 執行這個命令的時候連接到哪個數據庫無關緊要，因為pg_dumpall 創建的腳本將會包含恰當的創建和連接數據庫的命令

　　psql -f db.out postgres

　　備份單個數據庫

　　pg_dump -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名) > /data/dum.sql

　　恢復單個數據庫

　　psql -U postgres(用戶名) 數據庫名(缺省時同用戶名) < /data/dum.sql

　　備份單個數據庫并壓縮

　　pg_dump -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名) | gzip > /data/dum.sql.gz

　　恢復單個壓縮數據庫備份

　　gunzip < /data/dum.sql.gz | psql -h localhost -U postgres(用戶名) 數據庫名(缺省時同用戶名)

　　備份單表操作

　　pg_dump -U postgres -h localhost -p 5432 -t staff -f staff.sql yjl(表示數據庫名稱)

　　-U 表示用戶

　　-h 表示主機

　　-p 表示端口號

　　-t 表示表名

　　-f 表示備份后的sql文件的名字

　　-d 表示要恢復數據庫名稱

　　恢復數據單表操作

　　psql -U postgres -h localhost -p 5432 -d product -f staff.sql

　　查詢當前鏈接

　　查詢當前連接數

　　# 統計當前所有連接數

　　select count(1) from pg_stat_activity;

　　# 查詢當前連接數詳細信息

　　select * from pg_stat_activity;

　　查詢最大連接數

　　show max_connections;

　　# 最大連接數也可以在pg配置文件中配置：

　　# 在 postgresql.conf 中設置：

　　max_connections = 500

　　統計數據庫占用磁盤大小

　　統計各數據庫占用磁盤大小

　　SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,

　　CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

　　THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

　　ELSE 'No Access'

　　END AS SIZE

　　FROM pg_catalog.pg_database d

　　ORDER BY

　　CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

　　THEN pg_catalog.pg_database_size(d.datname)

　　ELSE NULL

　　END DESC -- nulls first

　　LIMIT 20

　　統計數據庫中各表占用磁盤大小

　　# 只顯示表名和占用磁盤大小

　　SELECT

　　table_schema || '.' || table_name AS table_full_name,

　　pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size

　　FROM information_schema.tables

　　ORDER BY

　　pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

　　# 詳細顯示各個參數并按數據庫占用大小排序

　　SELECT *, pg_size_pretty(total_bytes) AS total

　　, pg_size_pretty(index_bytes) AS INDEX

　　, pg_size_pretty(toast_bytes) AS toast

　　, pg_size_pretty(table_bytes) AS TABLE

　　FROM (

　　SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (

　　SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME

　　, c.reltuples AS row_estimate

　　, pg_total_relation_size(c.oid) AS total_bytes

　　, pg_indexes_size(c.oid) AS index_bytes

　　, pg_total_relation_size(reltoastrelid) AS toast_bytes

　　FROM pg_class c

　　LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

　　WHERE relkind = 'r'

　　) a

　　) a ORDER BY total_bytes desc;

　　查看 PostgreSQL 正在執行的 SQL

　　SELECT

　　procpid,

　　start,

　　now() - start AS lap,

　　current_query

　　FROM

　　(SELECT

　　backendid,

　　pg_stat_get_backend_pid(S.backendid) AS procpid,

　　pg_stat_get_backend_activity_start(S.backendid) AS start,

　　pg_stat_get_backend_activity(S.backendid) AS current_query

　　FROM

　　(SELECT pg_stat_get_backend_idset() AS backendid) AS S

　　) AS S

　　WHERE

　　current_query <> ''

　　ORDER BY

　　lap DESC;

　　# 參數解釋

　　procpid：進程id

　　start：進程開始時間

　　lap：經過時間

　　current_query：執行中的sql

　　# 通過命令：

　　=# select pg_cancel_backend(線程id);

　　來kill掉指定的SQL語句。(這個函數只能 kill Select 查詢，而updae,delete DML不生效)。

　　# 使用

　　=# select pg_terminate_backend(pid int)

　　可以kill 各種DML(SELECT,UPDATE,DELETE,DROP)操作。

　　雖然可以使用 kill -9 來強制刪除用戶進程，但是不建議這么去做。

　　因為：對于執行 update 的語句來說，kill掉進程，可能會導致 Postgres 進入到 recovery mode；而在 recovery mode 下，會鎖表，不允許鏈接數據庫。

上述就是小編為大家分享的PostgreSQL中有哪些常用的SQL語句了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。