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语句了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。