北京专业建设_黄页88客服_新媒体营销推广方案_新华传媒b2b商务平台

本文主要介绍psql命令行连接Postgresql服务器的常用命令

1.查看数据库:  \l  

2.列出表：\d

3.创建数据库：CREATE DATABASE pg_test;

postgres=# CREATE DATABASE pg_test;
CREATE DATABASE

4.连接创建的数据库：\c pg_test;

postgres=# \c pg_test;
You are now connected to database "pg_test" as user "postgres"

5.常用psql连接数据库的语法：psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

psql -h 127.0.0.1 -p 5432 pg_test postgres

 6. \d 表名  显示表的结构定义，例如： \d student

pg_test-# \d studentTable "public.student"Column |  Type  | Collation | Nullable |           Default
--------+--------+-----------+----------+------------------------------id     | bigint |           | not null | generated always as identityname   | "char" |           |          |

7.\d+显示更详细信息，例如：\d+ student

pg_test-# \d+ studentTable "public.student"Column |  Type  | Collation | Nullable |           Default            | Storage | Stats target | Description
--------+--------+-----------+----------+------------------------------+---------+--------------+-------------id     | bigint |           | not null | generated always as identity | plain   |              |name   | "char" |           |          |                              | plain   |              |
Access method: heap

8.列出所有schema

pg_test-# \dnList of schemasName  |  Owner
--------+----------public | postgres
(1 row)

9.列出数据库中所有角色和用户，使用命令：\du 或者 \dg

pg_test-# \dgList of rolesRole name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------odoo12    | Create role, Create DB                                    +| {}| Password valid until 2027-11-13 00:00:00+08                |postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}pg_test-# \duList of rolesRole name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------odoo12    | Create role, Create DB                                    +| {}| Password valid until 2027-11-13 00:00:00+08                |postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {

10. \dp 或 \z 用于列出表的权限分配情况

pg_test-# \zAccess privilegesSchema |      Name      |   Type   | Access privileges | Column privileges | Policies
--------+----------------+----------+-------------------+-------------------+----------public | student        | table    |                   |                   |public | student_id_seq | sequence |                   |                   |
(2 rows)pg_test-# \dpAccess privilegesSchema |      Name      |   Type   | Access privileges | Column privileges | Policies
--------+----------------+----------+-------------------+-------------------+----------public | student        | table    |                   |                   |public | student_id_seq | sequence |                   |                   |
(2 rows)

11.使用\encoding 显示客户端的字符编码，如：\encoding gbk; \encoding utf8;

12.\x命令将表中的数据拆分成单行展示，类似于mysql中\G;

13. \i a.sql 执行a.sql脚本