常用postgresql命令

连接数据库

-bash-4.1$ psql -l
                                    List of databases
       Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
------------------+----------+----------+------------+------------+-----------------------
 postgres         | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 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
 yyjs_luxuemei701 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

-bash-4.1$ psql yyjs_luxuemei701  --连接到指定数据库
psql (10.3)
Type "help" for help.

远程连接：
-bash-4.1$ psql -U yyjsdata -d yyjs_luxuemei701 -h 30.12.18.104 -p 35433
Password for user yyjsdata:
psql (10.3)
Type "help" for help.

具体见
https://blog.csdn.net/xuemeilu/article/details/67632883

创建用户

yyjs_luxuemei701=# CREATE USER yyjsdata WITH PASSWORD 'passwd001';
CREATE ROLE
yyjs_luxuemei701=# CREATE USER yyjsopr WITH PASSWORD 'passwd001';
CREATE ROLE

创建表

CREATE TABLE hostmanage(  
   OS_NAME varchar(50),  
   OS_VERSION varchar(50),  
   OS_BRAND varchar(50),  
   CPU_CORE varchar(10),
   OS_MEMORY varchar(10),
   HOST_TYPE varchar(20),
   HOST_STATUS varchar(20),
   HOST_OWER varchar(50),
   IP_ADDR varchar(100)
);

DML操作

--插入
insert into hostmanage values('1','Red Hat','Red Hat 4.4.7-18','Oracle Linux Server release 6.7','2','4G','Linux','start','luxuemei701','30.12.18.104');
insert into hostmanage values('红帽','win 10','microsoft','2','4G','windows','down','luxuemei701','30.12.0.1');

--更新表数据
yyjs_luxuemei701=> update hostmanage set host_name = 'SHB-L0102013';

控制台

--切换数据库：\c [database_name]
--切换用户：\c - [user_name]
--查看所有库: \l  
--查看所有表: \dt
--查询当前用户表: \d
--退出：\q
--当前数据库和连接的信息： \conninfo
--修改数据库用户密码： \password user

--查看当前用户:
yyjs_luxuemei701=> select user;
   user
----------
 yyjsdata
(1 row)

yyjs_luxuemei701=> select * from current_user;
 current_user
--------------
 yyjsdata
(1 row)

--查询当前用户表\d
yyjs_luxuemei701=> \d
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | hostmanage | table | yyjsdata
(1 row)

--查看表结构\d table
yyjs_luxuemei701=> \d hostmanage
                       Table "public.hostmanage"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 os_name     | character varying(50)  |           |          |
 os_version  | character varying(50)  |           |          |
 os_brand    | character varying(50)  |           |          |
 cpu_core    | character varying(10)  |           |          |
 os_memory   | character varying(10)  |           |          |
 host_type   | character varying(20)  |           |          |
 host_status | character varying(20)  |           |          |
 host_ower   | character varying(50)  |           |          |
 ip_addr     | character varying(100) |           |          |

DDL

--新增字段
yyjs_luxuemei701=> alter table hostmanage add column host_name varchar(50);
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
                       Table "public.hostmanage"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | character varying(10)  |           |          |
 os_name     | character varying(50)  |           |          |
 os_version  | character varying(100) |           |          |
 os_brand    | character varying(50)  |           |          |
 cpu_core    | character varying(10)  |           |          |
 os_memory   | character varying(10)  |           |          |
 host_type   | character varying(20)  |           |          |
 host_status | character varying(20)  |           |          |
 host_ower   | character varying(50)  |           |          |
 ip_addr     | character varying(100) |           |          |
 host_name   | character varying(50)  |           |          |

--修改字段名（将HOST_OWER修改为HOST_NAME）
yyjs_luxuemei701=> alter table hostmanage rename column HOST_OWER to HOST_NAME;
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
                       Table "public.hostmanage"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | character varying(10)  |           |          |
 os_name     | character varying(50)  |           |          |
 os_version  | character varying(50)  |           |          |
 os_brand    | character varying(50)  |           |          |
 cpu_core    | character varying(10)  |           |          |
 os_memory   | character varying(10)  |           |          |
 host_type   | character varying(20)  |           |          |
 host_status | character varying(20)  |           |          |
 host_name   | character varying(50)  |           |          |
 ip_addr     | character varying(100) |           |          |


--修改字段类型
yyjs_luxuemei701=> alter table hostmanage alter column os_version type varchar(100);
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
                       Table "public.hostmanage"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | character varying(10)  |           |          |
 os_name     | character varying(50)  |           |          |
 os_version  | character varying(100) |           |          |
 os_brand    | character varying(50)  |           |          |
 cpu_core    | character varying(10)  |           |          |
 os_memory   | character varying(10)  |           |          |
 host_type   | character varying(20)  |           |          |
 host_status | character varying(20)  |           |          |
 host_ower   | character varying(50)  |           |          |
 ip_addr     | character varying(100) |           |          |

修改表名：
alter table hostmanage rename to host;
删除字段：
alter table hostmanage drop column host_name;

授权：

--特殊符号:ALL代表所访问权限，PUBLIC代表所有用户
GRANT ALL ON demo TO demo_role; --赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户

yyjs_luxuemei701=> grant insert,update,select,delete on hostmanage to yyjsopr;
GRANT

--查询授权
yyjs_luxuemei701=> select * from information_schema.role_table_grants;
 grantor  | grantee  |  table_catalog   | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+------------------+--------------+------------+----------------+--------------+----------------
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | INSERT         | YES          | NO
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | SELECT         | YES          | YES
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | UPDATE         | YES          | NO
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | DELETE         | YES          | NO
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | TRUNCATE       | YES          | NO
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | REFERENCES     | YES          | NO
 yyjsdata | yyjsdata | yyjs_luxuemei701 | public       | hostmanage | TRIGGER        | YES          | NO
 yyjsdata | yyjsopr  | yyjs_luxuemei701 | public       | hostmanage | INSERT         | NO           | NO
 yyjsdata | yyjsopr  | yyjs_luxuemei701 | public       | hostmanage | SELECT         | NO           | YES
 yyjsdata | yyjsopr  | yyjs_luxuemei701 | public       | hostmanage | UPDATE         | NO           | NO
 yyjsdata | yyjsopr  | yyjs_luxuemei701 | public       | hostmanage | DELETE         | NO           | NO
 yyjsdata | PUBLIC   | yyjs_luxuemei701 | public       | hostmanage | SELECT         | NO           | YES
(12 rows)

GRANT ALL ON hostmanage TO public;

文章推荐

https://www.cnblogs.com/taosim/articles/4380383.html
点评：非常全的一篇文章，包括postgresql编译，安装，配置，常用命令…