PostgreSQL入门之psql基础与建表操作

1.psql基础

psql是PostgreSQL的交互式终端程序，下面对它的基础命令进行简要介绍

## 切换到postgresql的管理员用户
sudo su postgres
## 查看版本
[[email protected] ~]# psql --version
psql (PostgreSQL) 10.12
##查看数据库
[[email protected] lib]# su postgres
bash-4.1$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

## 创建一个新的数据库
bash-4.1$ createdb leedb
bash-4.1$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 leedb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)
## 进入刚才创建的数据库
bash-4.1$ psql leedb
psql (10.12)
Type "help" for help.

##查看帮助
leedb=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
       
## 查看当前系统时间
leedb=# select now();
              now              
-------------------------------
 2020-04-17 22:30:16.937021-04
(1 row)
## 查看版本
leedb=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
## 删除数据库
leedb=# dropdb leedb

2.操作表数据

## 创建表
leedb=# create table posts (title varchar(255),content text);
CREATE TABLE
## 显示表信息
leedb=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | posts | table | postgres
(1 row)
## 修改表名
leedb=# alter table posts rename to leeposts;
ALTER TABLE
leedb=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | leeposts | table | postgres
(1 row)
## 删除表
leedb=# drop table leeposts;
DROP TABLE
leedb=# \dt
Did not find any relations.
leedb=# \q
## 退出
leedb=# \q

创建一个.sql文件以实现上面的建表操作

bash-4.1$ sudo vi db.sql
## 显示文件内容
bash-4.1$ sudo cat db.sql
create table posts (title varchar(255),content text);
## psql leedb
## \i db.sql