PostgreSQL psql安装、使用

目录

1.下载

2. 安装

3. 基本命令

3.1 访问远程数据库

 3.2 查看帮助

3.2 显示所有数据库

3.3 选择数据库

3.4 表操作

3.4.1 查看数据库表

3.4.2 创建表 

3.4.3 插入

3.4.4 查询

3.5 退出

---

1.下载

https://www.postgresql.org/download/

下载rpm安装包

2. 安装

安装lib文件

rpm -ivh /home/wjm/pg/postgresql10-libs-10.11-1PGDG.rhel6.x86_64.rpm

[root@localhost packetbeat]# rpm -ivh /home/wjm/pg/postgresql10-libs-10.11-1PGDG.rhel6.x86_64.rpm 
warning: /home/wjm/pg/postgresql10-libs-10.11-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                ########################################### [100%]
   1:postgresql10-libs      ########################################### [100%]

 安装client

rpm -ivh /home/wjm/pg/postgresql10-10.11-1PGDG.rhel6.x86_64.rpm

[root@localhost packetbeat]# rpm -ivh /home/wjm/pg/postgresql10-10.11-1PGDG.rhel6.x86_64.rpm 
warning: /home/wjm/pg/postgresql10-10.11-1PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                ########################################### [100%]
   1:postgresql10           ########################################### [100%]

3. 基本命令

3.1 访问远程数据库

psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

[wjm@localhost ~]$ psql -h 10.47.155.29 -p 5432 dbas_pg_test sndbasusr
Password for user sndbasusr: 
psql (10.11, server 10.7)
Type "help" for help.

dbas_pg_test=> 

 3.2 查看帮助

/?

dbas_pg_test=> \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

3.2 显示所有数据库

\l 显示所有的数据库

dbas_pg_test=> \l
                                 List of databases
     Name     |  Owner   | Encoding | Collate |   Ctype    |   Access privileges   
--------------+----------+----------+---------+------------+-----------------------
 dbas_pg_test | admin    | UTF8     | C       | zh_CN.UTF8 | 
 postgres     | postgres | UTF8     | C       | zh_CN.UTF8 | 
 template0    | postgres | UTF8     | C       | zh_CN.UTF8 | =c/postgres          +
              |          |          |         |            | postgres=CTc/postgres
 template1    | postgres | UTF8     | C       | zh_CN.UTF8 | =c/postgres          +
              |          |          |         |            | postgres=CTc/postgres
(4 rows)

3.3 选择数据库

\c [数据库名]

dbas_pg_test=> \c dbas_pg_test
psql (10.11, server 10.7)
You are now connected to database "dbas_pg_test" as user "sndbasusr".

3.4 表操作

3.4.1 查看数据库表

\d

dbas_pg_test=> \d
                   List of relations
 Schema |             Name              | Type  | Owner 
--------+-------------------------------+-------+-------
 public | pathman_cache_stats           | view  | admin
 public | pathman_concurrent_part_tasks | view  | admin

\d [表名] 

\d people

dbas_pg_test=> \d people
                      Table "public.people"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 name   | character varying(255) |           | not null | 
 sex    | character(255)         |           |          | 

3.4.2 创建表 

注：需要权限

dbas_pg_test=# CREATE TABLE Persons(Id_P int,Name varchar(255),City varchar(255));
CREATE TABLE

dbas_pg_test=# \d Persons
                      Table "public.persons"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id_p   | integer                |           |          | 
 name   | character varying(255) |           |          | 
 city   | character varying(255) |           |          | 

3.4.3 插入

注：不要使用双引号

dbas_pg_test=# INSERT INTO Persons (id_p, name, city) VALUES(1, 'jimmy', 'NJ');
INSERT 0 1

3.4.4 查询

dbas_pg_test=# SELECT * from Persons;
 id_p | name  | city 
------+-------+------
    1 | jimmy | NJ
    2 | tom   | BJ
(2 rows)

3.5 退出

\q