前面介绍了 PostgreSQL 基础概念及安装部署、目录结构与配置文件 postgresql.conf 相关的知识点，今天我将详细的为大家介绍 PostgreSQL 常用管理命令 相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！
登录命令
psql是PostgreSQL的一个命令行交互式客户端工具，它具有非常丰富的功能，类似于Oracle的命令行工具sqlplus。
-
-h #数据库所在的IP地址
-
-p #（默认
5432）数据库的监听端口
-
-U #用户名
-
-d #数据库名称
常用命令说明
-
\? #所有命令帮助
-
\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执行结果导入文件中
用户管理
创建账号
创建用户
-
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;
删除账号
-
#撤回在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 用户名;
更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
权限管理
授权
-
#设置只读权限
-
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 用户名;
撤回权限
-
#撤回在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
PostgreSQL 模式SCHEMA 可以看着是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
允许多个用户使用一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
语法
我们可以使用 CREATE 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也会一并删除：
删除数据库
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();
更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
表管理
建表模板语句
-
create table
"t_user" (
-
"id" bigserial not null,
-
"username" varchar (
64) not null,
-
"password" varchar (
64) not null,
-
"create_time" timestamp not null
default current_timestamp,
-
"update_time" timestamp not null
default current_timestamp,
-
constraint t_user_pk primary key (id)
-
);
-
-
comment on column
"t_user".
"id" is
'主键';
-
comment on column
"t_user".
"username" is
'用户名';
-
comment on column
"t_user".
"password" is
'密码';
-
comment on column
"t_user".
"create_time" is
'创建时间';
-
comment on column
"t_user".
"update_time" is
'更新时间';
查询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 index t_user_username on t_user (username);
查看索引
\d t_user
查询SQL
注意：PostgreSQL中的字段大小写敏感，而且只认小写字母，查询时需注意。其他与基本sql大致相同。
to_timestamp() 字符串转时间
-
select * from t_user
-
where create_time >= to_timestamp(
'2023-01-01 00:00:00',
'yyyy-mm-dd hh24:MI:SS');
to_char 时间转字符串
select to_char(create_time, 'yyyy-mm-dd hh24:MI:SS') from t_user;
时间加减
-
-- 当前时间加一天
-
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';
like 模糊查询
SELECT * FROM 表名 WHERE 字段 LIKE ('%关键字%');
substring字符串截取
-
--从第一个位置开始截取，截取
4个字符,返回结果:Post
-
SELECT SUBSTRING (
'PostgreSQL',
1,
4);
-
-- 从第
8个位置开始截取，截取到最后一个字符，返回结果:SQL
-
SELECT SUBSTRING (
'PostgreSQL',
8);
-
--正则表达式截取，截取
'gre'字符串
-
SELECT SUBSTRING (
'PostgreSQL',
'gre');
更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
执行sql脚本
方式一：先登录再执行
\i testdb.sql
方式二：通过psql执行
psql -d testdb -U postgres -f /pathA/xxx.sql
导出数据到SQL文件
pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name -f save_sql.sql database_name
-
--column-inserts #以带有列名的
`INSERT` 命令形式转储数据。
-
-t #只转储指定名称的表。
-
-f #指定输出文件或目录名。
JDBC 连接串常用参数
PostgreSQL JDBC 官方驱动下载地址：https://jdbc.postgresql.org/download/
PostgreSQL JDBC 官方参数说明文档：https://jdbc.postgresql.org/documentation/use/
驱动类：
driver-class-name=org.postgresql.Driver
单机 PostgreSQL 连接串
-
url: jdbc:postgresql:
//10.20.1.231:5432/postgres?
-
binaryTransfer=
false&forceBinary=
false&reWriteBatchedInserts=
true
binaryTransfer=false：控制是否使用二进制协议传输数据，false表示不适用，默认为trueforceBinary=false：控制是否将非 ASCII 字符串强制转换为二进制格式，false表示不强制转换，默认为truereWriteBatchedInserts=true：控制是否将批量插入语句转换成更高效的形式，true表示转换，默认为false
例如：
-
insert into foo (col1, col2, col3) values(
1,
2,
3);
-
insert into foo (col1, col2, col3) values(
4,
5,
6);
会转换成：
insert into foo (col1, col2, col3) values(1,2,3), (4,5,6);
如果使用正确，reWriteBatchedInserts 会提升批量 insert 性能 2-3 倍。
集群PostgreSQL 连接串
集群PostgreSQL，连接串如下：
-
url: jdbc:postgresql:
//10.20.1.231:5432/postgres?
-
binaryTransfer=
false&forceBinary=
false&reWriteBatchedInserts=
true&targetServerType=master&loadBalanceHosts=
true
单机 PostgreSQL 连接串的所有参数。
targetServerType=master：只允许连接到具有所需状态的服务器，可选值有：any：默认，表示连接到任何一个可用的数据库服务器，不区分主从数据库；master：表示连接到主数据库，可读写；slave：表示连接到从数据库，可读，不可写；其他不常用值：primary, master, slave, secondary, preferSlave, preferSecondary and preferPrimary。
loadBalanceHosts=true：控制是否启用主从模式下的负载均衡，true表示启用，开启后依序选择一个 ip1:port 进行连接，默认为false。
更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。
参考文章：https://blog.csdn.net/qq_33204709/article/
details/128772173 https://blog.csdn.net/qq_33204709
/article/details/130622750
扫一扫
9573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
