PostgreSQL中有哪些常用的SQL語句

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)PostgreSQL中有哪些常用的SQL語句，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

　　創(chuàng)建新的用戶

　　創(chuàng)建一個(gè)新的用戶

　　CREATE USER WITH ENCRYPTED PASSWORD '';

　　用戶授權(quán)

　　授予 CONNECT 訪問權(quán)限

　　GRANT CONNECT ON DATABASE database_name TO username;

　　然后授予模式使用

　　GRANT USAGE ON SCHEMA schema_name TO username;

　　為特定表授予 SELECT 權(quán)限

　　GRANT SELECT ON table_name TO username;

　　將 SELECT 授予多個(gè)表

　　# 執(zhí)行格式

　　GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

　　# 例子

　　grant select on all tables in schema public to user1;

　　如果您希望將來自動(dòng)授予對新表的訪問權(quán)限，則必須更改默認(rèn)值

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

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

　　創(chuàng)建以 utf-8 字符的數(shù)據(jù)庫，并且以 template0 為模版創(chuàng)建

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

　　給指定用戶授指定數(shù)據(jù)庫所有權(quán)限

　　GRANT ALL PRIVILEGES ON DATABASE dbname to username;

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

　　alter user "root" login;

　　數(shù)據(jù)庫備份與恢復(fù)

　　備份所有數(shù)據(jù)庫

　　pg_dumpall > db.out

　　恢復(fù)所有數(shù)據(jù)庫

　　# 執(zhí)行這個(gè)命令的時(shí)候連接到哪個(gè)數(shù)據(jù)庫無關(guān)緊要，因?yàn)閜g_dumpall 創(chuàng)建的腳本將會(huì)包含恰當(dāng)?shù)膭?chuàng)建和連接數(shù)據(jù)庫的命令

　　psql -f db.out postgres

　　備份單個(gè)數(shù)據(jù)庫

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

　　恢復(fù)單個(gè)數(shù)據(jù)庫

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

　　備份單個(gè)數(shù)據(jù)庫并壓縮

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

　　恢復(fù)單個(gè)壓縮數(shù)據(jù)庫備份

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

　　備份單表操作

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

　　-U 表示用戶

　　-h 表示主機(jī)

　　-p 表示端口號

　　-t 表示表名

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

　　-d 表示要恢復(fù)數(shù)據(jù)庫名稱

　　恢復(fù)數(shù)據(jù)單表操作

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

　　查詢當(dāng)前鏈接

　　查詢當(dāng)前連接數(shù)

　　# 統(tǒng)計(jì)當(dāng)前所有連接數(shù)

　　select count(1) from pg_stat_activity;

　　# 查詢當(dāng)前連接數(shù)詳細(xì)信息

　　select * from pg_stat_activity;

　　查詢最大連接數(shù)

　　show max_connections;

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

　　# 在 postgresql.conf 中設(shè)置：

　　max_connections = 500

　　統(tǒng)計(jì)數(shù)據(jù)庫占用磁盤大小

　　統(tǒng)計(jì)各數(shù)據(jù)庫占用磁盤大小

　　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

　　統(tǒng)計(jì)數(shù)據(jù)庫中各表占用磁盤大小

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

　　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;

　　# 詳細(xì)顯示各個(gè)參數(shù)并按數(shù)據(jù)庫占用大小排序

　　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 正在執(zhí)行的 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;

　　# 參數(shù)解釋

　　procpid：進(jìn)程id

　　start：進(jìn)程開始時(shí)間

　　lap：經(jīng)過時(shí)間

　　current_query：執(zhí)行中的sql

　　# 通過命令：

　　=# select pg_cancel_backend(線程id);

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

　　# 使用

　　=# select pg_terminate_backend(pid int)

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

　　雖然可以使用 kill -9 來強(qiáng)制刪除用戶進(jìn)程，但是不建議這么去做。

　　因?yàn)椋簩τ趫?zhí)行 update 的語句來說，kill掉進(jìn)程，可能會(huì)導(dǎo)致 Postgres 進(jìn)入到 recovery mode；而在 recovery mode 下，會(huì)鎖表，不允許鏈接數(shù)據(jù)庫。

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