PostgreSQL中有哪些常用的SQL語句

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

創(chuàng)新互聯(lián)公司是網(wǎng)站建設技術企業(yè)，為成都企業(yè)提供專業(yè)的成都網(wǎng)站設計、做網(wǎng)站，網(wǎng)站設計，網(wǎng)站制作，網(wǎng)站改版等技術服務。擁有10余年豐富建站經(jīng)驗和眾多成功案例，為您定制適合企業(yè)的網(wǎng)站。10余年品質(zhì)，值得信賴！

創(chuàng)建新的用戶

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

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 授予多個表

# 執(zhí)行格式

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;

創(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ù)庫所有權限

GRANT ALL PRIVILEGES ON DATABASE dbname to username;

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

alter user "root" login;

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

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

pg_dumpall > db.out

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

# 執(zhí)行這個命令的時候連接到哪個數(shù)據(jù)庫無關緊要，因為pg_dumpall 創(chuàng)建的腳本將會包含恰當?shù)膭?chuàng)建和連接數(shù)據(jù)庫的命令

psql -f db.out postgres

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

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

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

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

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

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

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

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

備份單表操作

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

-U 表示用戶

-h 表示主機

-p 表示端口號

-t 表示表名

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

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

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

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

查詢當前鏈接

查詢當前連接數(shù)

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

select count(1) from pg_stat_activity;

# 查詢當前連接數(shù)詳細信息

select * from pg_stat_activity;

查詢最大連接數(shù)

show max_connections;

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

# 在 postgresql.conf 中設置：

max_connections = 500

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

統(tǒng)計各數(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)計數(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;

# 詳細顯示各個參數(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：進程id

start：進程開始時間

lap：經(jīng)過時間

current_query：執(zhí)行中的sql

# 通過命令：

=# select pg_cancel_backend(線程id);

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

# 使用

=# select pg_terminate_backend(pid int)

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

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

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

上述就是小編為大家分享的PostgreSQL中有哪些常用的SQL語句了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

當前標題：PostgreSQL中有哪些常用的SQL語句
標題URL：http://muchs.cn/article36/ihgcsg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導航、域名注冊、定制網(wǎng)站、網(wǎng)站內(nèi)鏈、軟件開發(fā)、建站公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)