User-Profile-Image
hankin
  • 5
  • Java
  • Kotlin
  • Spring
  • Web
  • SQL
  • MegaData
  • More
  • Experience
  • Enamiĝu al vi
  • 分类
    • Zuul
    • Zookeeper
    • XML
    • WebSocket
    • Web Notes
    • Web
    • Vue
    • Thymeleaf
    • SQL Server
    • SQL Notes
    • SQL
    • SpringSecurity
    • SpringMVC
    • SpringJPA
    • SpringCloud
    • SpringBoot
    • Spring Notes
    • Spring
    • Servlet
    • Ribbon
    • Redis
    • RabbitMQ
    • Python
    • PostgreSQL
    • OAuth2
    • NOSQL
    • Netty
    • MySQL
    • MyBatis
    • More
    • MinIO
    • MegaData
    • Maven
    • LoadBalancer
    • Kotlin Notes
    • Kotlin
    • Kafka
    • jQuery
    • JavaScript
    • Java Notes
    • Java
    • Hystrix
    • Git
    • Gateway
    • Freemarker
    • Feign
    • Eureka
    • ElasticSearch
    • Docker
    • Consul
    • Ajax
    • ActiveMQ
  • 页面
    • 归档
    • 摘要
    • 杂图
    • 问题随笔
  • 友链
    • Spring Cloud Alibaba
    • Spring Cloud Alibaba - 指南
    • Spring Cloud
    • Nacos
    • Docker
    • ElasticSearch
    • Kotlin中文版
    • Kotlin易百
    • KotlinWeb3
    • KotlinNhooo
    • 前端开源搜索
    • Ktorm ORM
    • Ktorm-KSP
    • Ebean ORM
    • Maven
    • 江南一点雨
    • 江南国际站
    • 设计模式
    • 熊猫大佬
    • java学习
    • kotlin函数查询
    • Istio 服务网格
    • istio
    • Ktor 异步 Web 框架
    • PostGis
    • kuangstudy
    • 源码地图
    • it教程吧
    • Arthas-JVM调优
    • Electron
    • bugstack虫洞栈
    • github大佬宝典
    • Sa-Token
    • 前端技术胖
    • bennyhuo-Kt大佬
    • Rickiyang博客
    • 李大辉大佬博客
    • KOIN
    • SQLDelight
    • Exposed-Kt-ORM
    • Javalin—Web 框架
    • http4k—HTTP包
    • 爱威尔大佬
    • 小土豆
    • 小胖哥安全框架
    • 负雪明烛刷题
    • Kotlin-FP-Arrow
    • Lua参考手册
    • 美团文章
    • Java 全栈知识体系
    • 尼恩架构师学习
    • 现代 JavaScript 教程
    • GO相关文档
    • Go学习导航
    • GoCN社区
    • GO极客兔兔-案例
    • 讯飞星火GPT
    • Hollis博客
    • PostgreSQL德哥
    • 优质博客推荐
    • 半兽人大佬
    • 系列教程
    • PostgreSQL文章
    • 云原生资料库
    • 并发博客大佬
Help?

Please contact us on our email for need any support

Support
    首页   ›   SQL   ›   PostgreSQL   ›   正文
PostgreSQL

PostgreSQL—常用指令

2022-04-03 16:12:20
706  0 1
参考目录 隐藏
1) 连接PostgreSQL 数据库
2) 切换新数据库连接
3) 列出有效数据库
4) 列出当前数据库所有表
5) 查看表结构
6) 列出有效schema
7) 列出有效函数
8) 列出有效试图
9) 列出用户及其角色
10) 执行前面命令
11) 命令历史
12) 从文件执行psql命令
13) 获取命令帮助
14) 开启查询执行时间
15) 退出psql
16) 创建数据库命令
17) 删除数据库
18) 重命名一个表
19) 删除一个表
20) 表里添加字段
21) 删除表中的字段
22) 重命名一个字段
23) 给一个字段设置缺省值
24) 去除缺省值
25) 表中插入数据
26) 修改表中的某行某列的数据
27) 修改字段类型
28) 删除表中某行数据
29) 删空整个表
30) 创建表
31) 切换用户
32) 创建用户
33) 创建用户时设定用户属性
34) 修改用户属性
35) 设置访问权限
36) 撤销用户访问权限
37) 用户组
38) 删除用户和组
39) 数据库连接数查询
40) 查询当前所有连接的状态
41) 查看数据库剩余连接数
42) 查看为超级用户保留的连接数
43) 关闭空闲连接
44) 查看目前所有的连接的进程 id、应用名称、状态。
45) 当前总共正在使用的连接数
46) 显示系统允许的最大连接数
47) 显示系统保留的用户数
48) 按照用户分组查看
49) 结束连接的进程
50) 杀死所有 idle 的进程

阅读完需:约 6 分钟

连接PostgreSQL 数据库

psql -d database -U  user -W

psql -d postgres -U postgres -W
用户 postgres 的口令:
psql (10.15)

postgres=#

如果数据库非本机,则需要增加-h选项:

psql -h host -d database -U user -W

使用SSL方式连接,使用下面命令:

psql -U user -h host "dbname=db sslmode=require"

切换新数据库连接

连接数据库之后,当前用户可能需要切换至其他数据库。切换之后则前面的连接关闭,如果忽略user参数则默认为当前用户。

\c dbname username

postgres=# \c TestDb
口令:
您现在已经连接到数据库 "TestDb",用户 "postgres".
TestDb=#

列出有效数据库

列出当前数据库服务器上的所有数据库,使用\l命令:

\l

列出当前数据库所有表

\dt

查看表结构

\d table_name

列出有效schema

\dn

列出有效函数

\df 

列出有效试图

\dv

列出用户及其角色

\du

执行前面命令

\g

命令历史

\s 
保存命令历史到文件,需要指定文件名称:
\s filename

从文件执行psql命令

\i filename
\i 'e:\\init.sql'

获取命令帮助

\?
获取特定PostgreSQL语句帮助
\h ALTER TABLE

开启查询执行时间

postgres=# \timing
启用计时功能.
postgres=# select count(*) from tbl_partition;
 count
-------
     0
(1 行记录)

时间:0.461 ms
postgres=#

可以再次使用相同命令关闭计时功能

退出psql

\q

创建数据库命令

create database [数据库名]; 

删除数据库

drop database [数据库名];  

重命名一个表

alter table [表名A] rename to [表名B]; 

删除一个表

drop table [表名]; 

表里添加字段

alter table [表名] add column [字段名] [类型]; 

删除表中的字段

alter table [表名] drop column [字段名]; 

重命名一个字段

alter table [表名] rename column [字段名A] to [字段名B]; 

给一个字段设置缺省值

alter table [表名] alter column [字段名] set default [新的默认值];

去除缺省值

alter table [表名] alter column [字段名] drop default; 

表中插入数据

insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 

修改表中的某行某列的数据

update [表名] set [目标字段名]=[目标值] where [该行特征]; 

修改字段类型

ALTER TABLE '表名' ALTER COLUMN '列名' type 新类型;

删除表中某行数据 

delete from [表名] where [该行特征]; 

删空整个表 

delete from [表名];

创建表

create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 

切换用户

su username

创建用户

  • 方式1:在系统命令行中使用createuser命令中创建
createuser username 
  • 方式2:在PostgresSQL命令行中使用CREATE ROLE指令创建
CREATE ROLE rolename;
  • 方式3:在PostgresSQL命令行中使用CREATE USER指令创建
CREATE USER username;

CREATE USER和CREATE ROLE的区别在于,CREATE USER指令创建的用户默认是有登录权限的,而CREATE ROLE没有。

  • \du 指令显示用户和用户的用户属性 

创建用户时设定用户属性

  • 基本语法格式
CREATE ROLE role_name WITH optional_permissions;
  • 示例:在创建用户时设定登录权限。
CREATE ROLE username WITH LOGIN;
  • 可以通过\h CREATE ROLE指令查看全部可设置的管理权限

修改用户属性

  • 修改权限的命令格式
ALTER ROLE username WITH attribute_options;
  • 例如:可通过以下方式禁止用户登录
ALTER ROLE username WITH NOLOGIN;

设置访问权限

  • 语法格式如下:
GRANT permission_type ON table_name TO role_name;
  • 实例:
GRANT UPDATE ON demo TO demo_role; --赋予demo_role demo表的update权限GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --赋予demo_role所有表的SELECT权限
  • 特殊符号:ALL代表所访问权限,PUBLIC代表所有用户
GRANT ALL ON demo TO demo_role; --赋给用户所有权限GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户
  • \z或\dp指令显示用户访问权限。
  • \h GRANT显示所有可设置的访问权限

撤销用户访问权限

  • 语法格式如下:
REVOKE permission_type ON table_name FROM user_name;

其中permission_type和table_name含义与GRANT指令中相同。

用户组

在postgres中用户实际上是role,同时组也是role。 包含其他role的role就是组。

  • 创建组示例:
CREATE ROLE temporary_users;GRANT temporary_users TO demo_role;GRANT temporary_users TO test_user;
  • 切换ROLE
SET ROLE role_name; --切换到role_name用户RESET ROLE; --切换回最初的role
  • INHERIT权限:该属性使组成员拥有组的所有权限
ALTER ROLE test_user INHERIT;

删除用户和组

删除用户和组很简单:

DROP ROLE role_name;DROP ROLE IF EXISTS role_name;

删除组role只会删除组的role本身,组的成员并不会被删除

数据库连接数查询

查询当前所有连接的状态

select datname,pid,application_name,state from pg_stat_activity;

查看数据库剩余连接数

select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;

查看为超级用户保留的连接数

show superuser_reserved_connections;

关闭空闲连接

select datname,pid,application_name,state from pg_stat_activity;

查看目前所有的连接的进程 id、应用名称、状态。

select pg_terminate_backend(pid) from pg_stat_activity;

当前总共正在使用的连接数

select count(1) from pg_stat_activity;

显示系统允许的最大连接数

show max_connections;

显示系统保留的用户数

show superuser_reserved_connections ;

按照用户分组查看

select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

结束连接的进程

SELECT pg_terminate_backend(15278);

杀死所有 idle 的进程

select pg_terminate_backend(pid) from pg_stat_activity where state=’idle’;

如本文“对您有用”,欢迎随意打赏作者,让我们坚持创作!

1 打赏
Enamiĝu al vi
不要为明天忧虑.因为明天自有明天的忧虑.一天的难处一天当就够了。
543文章 68评论 297点赞 608197浏览

随机文章
Nginx—安装运行Lua脚本
3年前
SpringSecurity—OAuth 2(六)刷新Token
5年前
Spring Boot 整合 Freemarker
5年前
PostgreSQL—集群方案
3年前
RBAC用户角色权限设计方案
5年前
博客统计
  • 日志总数:543 篇
  • 评论数目:68 条
  • 建站日期:2020-03-06
  • 运行天数:1947 天
  • 标签总数:23 个
  • 最后更新:2024-12-20
Copyright © 2025 网站备案号: 浙ICP备20017730号 身体没有灵魂是死的,信心没有行为也是死的。
主页
页面
  • 归档
  • 摘要
  • 杂图
  • 问题随笔
博主
Enamiĝu al vi
Enamiĝu al vi 管理员
To be, or not to be
543 文章 68 评论 608197 浏览
测试
测试
看板娘
赞赏作者

请通过微信、支付宝 APP 扫一扫

感谢您对作者的支持!

 支付宝 微信支付