PostgreSQL（二）常用命令整理

已于 2024-10-21 11:52:39 修改
阅读量7.8k 收藏 89
点赞数 6
分类专栏： # PostgreSQL 文章标签： postgresql 数据库 sql
于 2023-01-27 18:44:51 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_33204709/article/details/128772173
版权

目录

一、常用命令

登录命令：

psql -h IP -p 端口 -U 用户名 -d 数据库名 -W
  • \? 所有命令帮助
  • \l 列出所有数据库
  • \d 列出数据库中所有表
  • \dt 列出数据库中所有表
  • \d [table_name] 显示指定表的结构
  • \di 列出数据库中所有 index 索引
  • \dv 列出数据库中所有 view 视图
  • \h sql命令帮助
  • \q 退出连接
  • \c [database_name] 切换到指定的数据库
  • \c 显示当前数据库名称和用户
  • \conninfo 显示客户端的连接信息
  • \du 显示所有用户
  • \dn 显示数据库中的schema
  • \encoding 显示字符集
  • select version(); 显示版本信息
  • \i testdb.sql 执行sql文件
  • \x 扩展展示结果信息，相当于MySQL的\G
  • \o /tmp/test.txt 将下一条sql执行结果导入文件中

查看版本：

select version();

在这里插入图片描述

二、用户

2.1 创建账号

创建用户：

create user 用户名 password '密码';

设置只读权限：

alter user 用户名 set default_transaction_read_only = on;

设置可操作的数据库：

grant all on database 数据库名 to 用户名;

授权可操作的模式和权限：

-- 授权
grant select on all tables in schema public to 用户名;

-- 授权
GRANT ALL ON TABLE public.user TO mydata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.user TO mydata_dml;
GRANT SELECT ON TABLE public.user TO mydata_qry;

2.2 删除账号

撤回在public模式下的权限：

revoke select on all tables in schema public from 用户名;

撤回在information_schema模式下的权限：

revoke select on all tables in schema information_schema from 用户名;

撤回在pg_catalog模式下的权限：

revoke select on all tables in schema pg_catalog from 用户名;

撤回对数据库的操作权限：

revoke all on database 数据库名 from 用户名;

删除用户：

drop user 用户名;

三、权限

3.1 授权

设置只读权限：

alter user 用户名 set default_transaction_read_only = on;

设置可操作的数据库：

grant all on database 数据库名 to 用户名;

设置可操作的模式和权限：

grant select,insert,update,delete on all tables in schema public to 用户名;

3.2 撤回权限

撤回在public模式下的权限：

revoke select on all tables in schema public from 用户名;

撤回在information_schema模式下的权限：

revoke select on all tables in schema information_schema from 用户名;

撤回在pg_catalog模式下的权限：

revoke select on all tables in schema pg_catalog from 用户名;

撤回对数据库的操作权限：

revoke all on database 数据库名 from 用户名;

四、模式 Schema

创建和当前用户同名模式（schema）：

注意：用户名与 schema 同名，且用户具有访问改 schema 的权限，用户连入数据库时，默认即为当前 schema。

create schema AUTHORIZATION CURRENT_USER;

自定义创建模式（schema）：

create schema 模式名称;

注意：如果不创建scheme，并且语句中不写scheme，则默认scheme使用内置的public

查看数据库下的所有（schema）：

select * from information_schema.schemata;

五、数据库

查询所有数据库：

select datname from pg_database;

创建数据库：

create database 数据库名 owner 所属用户 encoding UTF8;

注意：创建完数据库，需要切换到数据库下，创建和当前用户同名scheme，删除数据库后schema也会一并删除：

-- 重新登陆到新数据库下，执行如下语句
create schema AUTHORIZATION CURRENT_USER;

删除数据库：

drop database 数据库名;

注意：删库前需要关闭所有会话，不然会提示：

ERROR:  database "mydb" is being accessed by other users
DETAIL:  There are 8 other sessions using the database.

关闭数据库所有会话：

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='mydb' AND pid<>pg_backend_pid();

六、表

查询schema中所有表：

select table_name from information_schema.tables where table_schema = 'myuser';

创建表：

CREATE TABLE public.t_user (
  "id" BIGSERIAL NOT NULL,
  "username" VARCHAR(64) NOT NULL,
  "password" VARCHAR(64) NOT NULL,
  "create_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null,
  "update_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null
);
-- 注释
COMMENT ON TABLE public.t_user IS '用户表';
COMMENT ON COLUMN public.t_user.id IS '主键';
COMMENT ON COLUMN public.t_user.username IS '用户名';
COMMENT ON COLUMN public.t_user.password IS '密码';
COMMENT ON COLUMN public.t_user.create_time IS '创建时间';
COMMENT ON COLUMN public.t_user.update_time IS '更新时间';
-- 创建自增序列
alter sequence "t_user_ID_seq" restart with 1 increment by 1;
-- 创建主键序列
drop index if exists "t_user_pkey";
alter table "t_user" add constraint "t_user_pkey" primary key ("ID");

根据已有表结构创建表：

create table if not exists 新表 (like 旧表 including indexes including comments including defaults);

删除表：

drop table if exists "t_template" cascade;

查询注释：

SELECT
a.attname as "字段名",
col_description(a.attrelid,a.attnum) as "注释",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as "字段类型"
FROM
pg_class as c,
pg_attribute as a,
pg_type as t
WHERE
c.relname = 't_batch_task'
and a.atttypid = t.oid
and a.attrelid = c.oid
and a.attnum>0;

七、索引

创建索引：

drop index if exists t_user_username;
create index t_user_username on t_user (username);

创建唯一索引：

drop index if exists t_user_username;
create unique index t_user_username on t_user (username);

查看索引：

\d t_user

删除索引：

drop index t_user_username;

更新ID序列：

select setval('t_sys_menu_id_seq',(select max(id) from t_sys_menu));

八、查询SQL

注意：PostgreSQL中的字段大小写敏感，而且只认小写字母，查询时需注意。

其他与基本sql大致相同。

8.1 to_timestamp() 字符串转时间

select * from t_user
where create_time >= to_timestamp('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:MI:SS');

8.2 to_char 时间转字符串

select to_char(create_time, 'yyyy-mm-dd hh24:MI:SS') from t_user;

8.3 时间加减

-- 当前时间加一天
SELECT NOW()::TIMESTAMP + '1 day';
SELECT NOW() + INTERVAL '1 DAY';
SELECT now()::timestamp + ('1' || ' day')::interval
-- 当前时间减一天
SELECT NOW()::TIMESTAMP + '-1 day';
SELECT NOW() - INTERVAL '1 DAY';
SELECT now()::timestamp - ('1' || ' day')::interval
-- 加1年1月1天1时1分1秒
select NOW()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';

8.4 like模糊查询

SELECT * FROM 表名 WHERE 字段 LIKE ('%关键字%');

8.5 substring字符串截取

--从第一个位置开始截取，截取4个字符,返回结果:Post
SELECT SUBSTRING ('PostgreSQL', 1, 4);
-- 从第8个位置开始截取，截取到最后一个字符，返回结果:SQL
SELECT SUBSTRING ('PostgreSQL', 8);
--正则表达式截取，截取'gre'字符串
SELECT SUBSTRING ('PostgreSQL', 'gre');

九、关联更新、关联删除SQL

9.1 关联更新

更新单列数据：

update table1 a set address = (select address from table2 b where a.id=b.id);

更新多列数据：

update table1 a set (address,people_cnt) = (select address,people_cnt from table2 b where a.id=b.id);

9.2 关联删除

示例：

delete from table1 a using table2 where a.id=b.id and address='china';

十、执行sql脚本

方式一：先登录再执行

\i testdb.sql

方式二：通过psql执行

psql -d testdb -U postgres -f /pathA/xxx.sql

十一、导出

11.1 导出数据

写法一：

pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name -f data_sql.sql database_name
  • --column-inserts 以带有列名的 INSERT 命令形式转储数据。
  • -t 只转储指定名称的表。（不指定具体表则导出整个库。）
  • -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的 > 将内容写入到文件。

pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name database_name > data_sql.sql

11.2 导出表结构

写法一：

pg_dump -h localhost -p 5432 -U postgres -s -t table_name -f struct_sql.sql database_name
  • --column-inserts 以带有列名的 INSERT 命令形式转储数据。
  • -t 只转储指定名称的表。（不指定具体表则导出整个库。）
  • -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的 > 将内容写入到文件。

pg_dump -h localhost -p 5432 -U postgres -s -t table_name -f struct_sql.sql database_name

整理完毕，完结撒花~





参考地址：

1.PostgreSql常用命令，https://blog.youkuaiyun.com/weixin_48321825/article/details/121775011

2.Postgresql创建账号及删除账号详细命令，https://blog.youkuaiyun.com/qq_44322586/article/details/123084962

PostgreSQL在Linux环境下的常用命令总结
embelfe_segge的博客
02-15 715
这条查询语句会列出正在使用 mydb 数据库的会话的进程 ID（pid）、用户名（usename）、数据库名（datname）、应用程序名（application_name）、客户端地址（client_addr）、客户端主机名（client_hostname）以及客户端端口（client_port）等信息。在一些较新的 PostgreSQL 版本中，理论上可以尝试使用 ALTER DATABASE 语句来更改数据库名称，但这种方法可能会受到一些限制，比如数据库的使用状态、权限等因素可能会影响其成功与否。
怎样在 PostgreSQL 中优化对大表的索引碎片整理的时机选择？
2401_86074221的博客
07-16 962
在 PostgreSQL 中优化对大表的索引碎片整理的时机选择是一项重要的任务，它需要我们综合考虑数据库的负载情况、业务需求、数据变更频率和系统性能指标等因素。通过合理地选择碎片整理方法和时机，我们可以有效地提高数据库的性能和稳定性，为业务的发展提供有力的支持。就像我们需要定期打扫房间一样，我们也需要定期整理数据库中的索引碎片。只有这样，我们才能保持数据库的整洁和高效，为我们的业务提供更好的服务。希望本文能够对您在 PostgreSQL 数据库管理方面提供一些有益的参考和帮助。🎉相关推荐🍅关注博主🎗️。
常用命令的postgresql
白非马的博客
03-27 2872
一 、运维DBA管理数据库使用命令 1 命令行登录数据库 有两种方式，一是直接在系统shell下执行psql命令；而是先进入psql环境，然后再连接数据库。下面分别给出实例： (1)直接登录 执行命令：psql -h 172.16.35.179 -U username -d dbname ，其中username为数据库用户名，dbname为要连接的数据库名，执行后提示输入密码如下： Pa...
PostgreSQL一些常用命令
热门推荐
u010856284的博客
04-12 6万+
postgresql
PostgreSQL 指令备忘：一文掌握常用数据库操作
最新发布
威哥说编程
03-18 753
本文提供了 PostgreSQL 常用指令的详细备忘，涵盖了数据库管理、表操作、数据操作、索引、视图、权限管理、事务控制等方面。掌握这些指令，将大大提高你在 PostgreSQL 数据库的管理和开发效率。希望这篇文章能帮助你快速了解 PostgreSQL 的各种指令，提升你在实际开发和运维中的操作能力。
postgresql 常用命令大全
08-04
\d [ table ] 列出数据库中的表 或（如果声明了）表 table 的列/字段 \l 列出服务器上所有数据库． \dt 只列出非系统表． ……
PostgreSQL-常用命令
m0_71902491的博客
05-02 2253
【代码】PostgreSQL-psql常用命令。
PostgreSQL命令大全
qqrrjj2011的博客
01-20 5549
以上是PostgreSQL数据库常用命令及使用案例的一部分，更多详细内容请参阅官方文档。对于大型应用和企业级环境，可能还需要涉及更复杂的集群配置、高可用性解决方案、性能调优等方面的知识。请参考官方文档以获取完整信息：https://www.postgresql.org/docs/current/index.html。示例：以用户postgres身份连接到名为mydb的数据库。
postgresql常用命令3
夏林林（Robin）的专栏
05-15 1490
postgresql常用命令  1.createdb 数据库名称   产生数据库 2.dropdb  数据库名称   删除数据库 3.CREATE USER 用户名称   创建用户 4.drop User 用户名称   删除用户 5.SELECT usename FROM pg_user;   查看系统用户信息   \du 7.SELECT version();   查
PostgreSQL常用命令
05-21
PostgreSQL常用命令 psql 国内的资源少啊。
PostgreSQL数据库常用语法总结
Dr.叶子的博客
09-15 3816
目录前言背景讲解1：基础语法讲解2：进阶语法参考来源后语 前言 你好，我是Dr.叶子，用心写最优美的博客，弹最好听的钢琴！ 背景 最近总结项目过程中，涉及到PostgreSQL的运用，链接: Python爬虫实战之：快代理搭建IP代理池（Scrapy进阶版）。为了进一步巩固PostgreSQL数据库的语法知识，所以写了这篇博文总结。 网上的知识太过于零散，博文代码不规范，布局凌乱，所以亲自总结博文，与大家分享！ 讲解1：基础语法 1. 创建 / 删除 数据库： -- 语法 CREATE DATA
PostgreSQL 常用管理命令
01-14
postgresql 管理常用命令总结，汇总方便查询使用，各种命令均已经被测试使用过
psql常用命令
07-06
目前postgresql作为最流行、最先进的开源数据库之一，在企业中现在非常流行，所以掌握postgresql也是IT人员的基本功之一
postgresql数据库 DBA 运维 经常使用的命令
yang_z_1的博客
12-30 1094
** postgresql数据库 DBA 运维 经常使用的命令 ** （持续更新） 最近一直在维护postgresql数据库 ，整理一下经常会用到的命令，sql语句。 postgresql11 常用命令 ** 1.死锁 ** 因为postgresql的本身，常常会导致一部分表被锁 1.查询锁 select w1.pid as 等待进程, w1.mode as 等待锁模式, w2.usename as 等待用户, w2.query as 等待会话, b1.pid as 锁的进程, b1.mode 锁的锁模式,
sqlmap常用命令及语句整理
weixin_43954246的博客
04-14 660
sqlmap常用命令及语句 文章目录常用命令简单的注入语句常用语句 常用命令 -u #注入点 -f #指纹判别数据库类型 -b #获取数据库版本信息 -p #指定可测试的参数(?page=1&id=2 -p "page,id") -D "" #指定数据库名 -T "" #指定表名 -C "" #指定字段 -s "" #保存注入过程到一个文件，还可中断，下次恢复在注入(保存：-s "xx.log" 恢复：-s "xx.log" --resume) --level=(1-5) #要执行的
PostgreSQL 的常用命令
weixin_44594317的博客
11-26 701
PostgreSQL 功能非常强大，本文命令是使用 PostgreSQL 时最常见的操作。
PostgreSQL 的一些常用命令
ray_paul1的博客
01-12 639
的一些常用命令，分为连接数据库、用户和权限管理、表管理、查询数据等多个方面进行讲解，帮助你快速熟悉 PostgreSQL 的操作。
PostgreSQL常用命令汇总
初夏0811的博客
03-06 4万+
20、创建表：create table tbname ([字段名1] [类型1], [字段名2] [类型2], …1 连接数据库：psql -U postgres （psql -U username -d databse_name -h host -W）-U 指定用户 -d 指定数据库 -h 要链接的主机 -W 提示输入密码。24、更改用户密码： \password [username]27、创建用户名称：createuser username;23、SQL命名帮助（用*显示全部命令）：\h [名称]
PostgreSQL常用指令
qq_45722267的博客
06-29 4463
操作PostgreSQL的命令主要包含操作命令，转换命令，查看命令等dbname :数据库名 description ： 关于新创建的数据库相关的说明 options：参数使用 查看一家存在的数据库信息使用 来进入数据库。使用 命令来删除数据库。dbname：要删除的数据库名。 options：参数可选项。使用 命令来查看表格是否创建成功使用 查看表格信息。 会展示表的所有字段信息。使用 列举数据库中表的信息。使用指定参数登录：使用 退出psql上述命令仅仅是在操作系统或者命令行的情况下对post
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不愿放下技术的小赵

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值