database►Postgresql►postgresql book use

postgresql book use

1. 连接数据库

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
psql (9.6.1)
Type "help" for help.
postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
postgres=# \q

说明:

  • 连接PG数据库时,必须先切换到PG启动用户,此时是”su - smallasa”
  • -d 表示需要连接的数据库
  • \q 表示退出PG控制台

2. 创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/createdb mydb

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb
psql (9.6.1)
Type "help" for help.
mydb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
 template1 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
(4 rows)
mydb=# \q

说明:

  • \l 表示显示数据库列表

3. 删除数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/dropdb mydb

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d mydb
psql: FATAL:  database "mydb" does not exist

[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
psql (9.6.1)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
 template1 | smallasa | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/smallasa          +
           |          |          |             |             | smallasa=CTc/smallasa
(3 rows)
postgres=# \q