PostgreSQL（二）常用命令整理

目录

一、常用命令

登录命令：

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 创建账号

创建用户：

createuser 用户名 password '密码';

设置只读权限：

alteruser 用户名 set default_transaction_read_only =on;

设置可操作的数据库：

grantallondatabase 数据库名 to 用户名;

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

-- 授权grantselectonalltablesinschemapublicto 用户名;

-- 授权GRANTALLONTABLEpublic.userTO mydata;GRANTSELECT,UPDATE,INSERT,DELETEONTABLEpublic.userTO mydata_dml;GRANTSELECTONTABLEpublic.userTO mydata_qry;

2.2 删除账号

撤回在public模式下的权限：

revokeselectonalltablesinschemapublicfrom 用户名;

撤回在information_schema模式下的权限：

revokeselectonalltablesinschema information_schema from 用户名;

撤回在pg_catalog模式下的权限：

revokeselectonalltablesinschema pg_catalog from 用户名;

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

revokeallondatabase 数据库名 from 用户名;

删除用户：

dropuser 用户名;

三、权限

3.1 授权

设置只读权限：

alteruser 用户名 set default_transaction_read_only =on;

设置可操作的数据库：

grantallondatabase 数据库名 to 用户名;

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

grantselect,insert,update,deleteonalltablesinschemapublicto 用户名;

2.2 撤回权限

撤回在public模式下的权限：

revokeselectonalltablesinschemapublicfrom 用户名;

撤回在information_schema模式下的权限：

revokeselectonalltablesinschema information_schema from 用户名;

撤回在pg_catalog模式下的权限：

revokeselectonalltablesinschema pg_catalog from 用户名;

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

revokeallondatabase 数据库名 from 用户名;

四、模式 Schema

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

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

createschemaAUTHORIZATIONCURRENT_USER;

自定义创建模式（schema）：

createschema 模式名称;

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

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

select*from information_schema.schemata;

五、数据库

查询所有数据库：

select datname from pg_database;

创建数据库：

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

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

-- 重新登陆到新数据库下，执行如下语句createschemaAUTHORIZATIONCURRENT_USER;

删除数据库：

dropdatabase 数据库名;

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

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.tableswhere table_schema ='myuser';

创建表：

CREATETABLEpublic.t_user ("id" BIGSERIAL NOTNULL,"username"VARCHAR(64)NOTNULL,"password"VARCHAR(64)NOTNULL,"create_time"TIMESTAMP(0)defaultCURRENT_TIMESTAMPnotnull,"update_time"TIMESTAMP(0)defaultCURRENT_TIMESTAMPnotnull);-- 注释COMMENTONTABLEpublic.t_user IS'用户表';COMMENTONCOLUMNpublic.t_user.id IS'主键';COMMENTONCOLUMNpublic.t_user.username IS'用户名';COMMENTONCOLUMNpublic.t_user.password IS'密码';COMMENTONCOLUMNpublic.t_user.create_time IS'创建时间';COMMENTONCOLUMNpublic.t_user.update_time IS'更新时间';-- 创建自增序列alter sequence "t_user_ID_seq" restart with1 increment by1;-- 创建主键序列dropindexifexists"t_user_pkey";altertable"t_user"addconstraint"t_user_pkey"primarykey("ID");

根据已有表结构创建表：

createtableifnotexists 新表 (like 旧表 including indexes including comments including defaults);

删除表：

droptableifexists"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;

七、索引

创建索引：

dropindexifexists t_user_username;createindex t_user_username on t_user (username);

创建唯一索引：

dropindexifexists t_user_username;createuniqueindex t_user_username on t_user (username);

查看索引：

\d t_user

删除索引：

dropindex t_user_username;

八、查询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 时间加减

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

8.4 like模糊查询

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

8.5 substring字符串截取

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

九、执行sql脚本

方式一：先登录再执行

\i testdb.sql

方式二：通过psql执行

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

十、导出

10.1 导出数据

写法一：

pg_dump -h localhost -p5432-U postgres --column-inserts -t table_name -f data_sql.sql database_name

• --column-inserts 以带有列名的 INSERT 命令形式转储数据。
• -t 只转储指定名称的表。（不指定具体表则导出整个库。）
• -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的

>

将内容写入到文件。

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

10.2 导出表结构

写法一：

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

• --column-inserts 以带有列名的 INSERT 命令形式转储数据。
• -t 只转储指定名称的表。（不指定具体表则导出整个库。）
• -f 指定输出文件或目录名。

写法二：

直接使用 Linux 的

>

将内容写入到文件。

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

整理完毕，完结撒花~

参考地址：

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

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