列出所有库
\l
postgres@0:users> \l
+-----------+----------+----------+------------+------------+-----------------------+
| Name | Owner | Encoding | Collate | Ctype | Access privileges |
|-----------+----------+----------+------------+------------+-----------------------|
| 123456 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
| auths | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
| mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
| postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
| template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres |
| | | | | | postgres=CTc/postgres |
| template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres |
| | | | | | postgres=CTc/postgres |
| users | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
| zero_0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | <null> |
+-----------+----------+----------+------------+------------+-----------------------+
列出某个database 的所有 表(tables)
先use 某个库名
然后 \dt
postgres@0:users> \dt
+--------+-------------------------------+-------+----------+
| Schema | Name | Type | Owner |
|--------+-------------------------------+-------+----------|
| public | sys_authorities | table | postgres |
| public | sys_authority_sys_base_menus | table | postgres |
| public | sys_authority_sys_users | table | postgres |
| public | sys_base_menu_btns | table | postgres |
| public | sys_base_menu_parameters | table | postgres |
| public | sys_base_menu_sys_authorities | table | postgres |
| public | sys_base_menus | table | postgres |
| public | sys_user_sys_authorities | table | postgres |
| public | sys_users | table | postgres |
+--------+-------------------------------+-------+----------+
SELECT 9
Time: 0.012s
列出 某个 表(table) 的 所有属性 (columns)
\d+ 表名
postgres@0:users> \d+ sys_users
+--------------+--------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
| Column | Type | Modifiers | Storage | Stats target | Description |
|--------------+--------------------------+-----------------------------------------------------------------+----------+--------------+-------------|
| active_color | text | default '#1890ff'::text | extended | <null> | <null> |
| authority_id | text | default '888'::text | extended | <null> | <null> |
| base_color | text | default '#fff'::text | extended | <null> | <null> |
| created_at | timestamp with time zone | | plain | <null> | <null> |
| email | text | | extended | <null> | <null> |
| header_img | text | default 'https://qmplusimg.henrongyi.top/gva_header.jpg'::text | extended | <null> | <null> |
| id | bigint | not null default nextval('sys_users_id_seq'::regclass) | plain | <null> | <null> |
| nick_name | text | default '系统用户'::text | extended | <null> | <null> |
| password | text | | extended | <null> | <null> |
| phone | text | | extended | <null> | <null> |
| side_mode | text | default 'dark'::text | extended | <null> | <null> |
| uuid | text | | extended | <null> | <null> |
| updated_at | timestamp with time zone | | plain | <null> | <null> |
| username | text | | extended | <null> | <null> |
+--------------+--------------------------+-----------------------------------------------------------------+----------+--------------+-------------+
Indexes:
"sys_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "sys_authorities" CONSTRAINT "fk_sys_users_authority" FOREIGN KEY (sys_user_id) REFERENCES sys_users(id)
TABLE "sys_authority_sys_users" CONSTRAINT "fk_sys_authority_sys_users_sys_user_orm" FOREIGN KEY (sys_user_orm_id) REFERENCES sys_users(id)
Has OIDs: no
Time: 0.013s
从远程数据库 里面 导出 sql 到本地 存储 为sql 文件
用 pg_dump命令
如 pg_dump -U postgres -h 39.99.242.255 -p 15432 zero_0 > ~/zero0.sql
如图有详细解释
导入sql 文件 到本地 数据库里
使用 psql命令
如psql -d zero_0 -U postgres -h 0.0.0.0 -f ~/.zero0.sql
如图有详细解释
执行命令行命令 创建 数据 库
pg 不像 mysql 可以 create database if not exists users; 一行命令就能创建数据库
他弄起来 比较麻烦
得 echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql -d zero_0 -U postgres -h 0.0.0.0 才行
这里的 mydb 就是 你要创建的 db名
本质上就是利用 psql 命令 执行 sql 语句 创建 命令 (在命令行里不是在 pgsql 里面)