PostgreSQL数据库连接和数据库管理
PostgreSQL数据库连接和数据库管理
首先我们需要通过multipass启动安装了PostgreSQL的虚拟机，然后我们就可以开启PostgreSQL数据库之旅了。
multipass list
multipass shell <虚拟机名>
例：
C:\Users\Administrator>multipass list
Name State IPv4 Image
mysql-vm Stopped -- Ubuntu 20.04 LTS
pgvm Running N/A Ubuntu 20.04 LTS
C:\Users\Administrator>multipass shell pgvm
Welcome to Ubuntu 20.04.4 LTS (GNU/Linux 5.4.0-125-generic x86_64)
* Documentation: https://help.ubuntu.com
* Management: https://landscape.canonical.com
* Support: https://ubuntu.com/advantage
System information as of Thu Sep 15 21:25:06 CST 2022
System load: 0.48 Processes: 106
Usage of /: 29.2% of 4.67GB Users logged in: 0
Memory usage: 20% IPv4 address for enp0s3: 10.0.2.15
Swap usage: 0%
0 updates can be applied immediately.
The list of available updates is more than a week old.
To check for new updates run: sudo apt update
New release '22.04.1 LTS' available.
Run 'do-release-upgrade' to upgrade to it.
To run a command as administrator (user "root"), use "sudo <command>".
See "man sudo_root" for details.
ubuntu@pgvm:~$
PostgreSQL数据库连接
连接PostgreSQL命令
可以通过如下命令利用自带的psql工具连接和断开PostgreSQL服务器。
--连接数据库
shell> psql -h <主机名> -p <端口号> -U <用户名> <数据库名>
例：
ubuntu@pg-vm:~$ psql -U postgres -h localhost -d postgres
Password for user postgres: --》输入之前设置的密码：pass
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
断开PostgreSQL命令
对于PostgreSQL，通常使用\q推出连接。 但是有时候和MySQL一样，也可以通过输入exit或者quit或者Control+D也能够退出PostgreSQL连接。
例：
--首选 \q
postgres=# \q
postgres=# exit
postgres=# quit
Control+D
PostgreSQL数据库基本操作
执行查询
和MySQL数据库相类似，连接了数据库以后就可以直接执行查询。
例：
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
(END)
psql命令行工具
psql是PostgreSQL自带的命令行工具，功能全面，是PostgreSQL数据库最重要的命令行工具之一。 通过psql工具可以和PostgreSQL数据库服务器进行SQL命令行交互。另外，psql工具也提供了大量强大的元命令（以反斜杠“\”开头的命令）。
通过在psql命令行键入help可以获得使用帮助的内容。
例：
postgres=# 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
psql工具元命令可以帮助我们更有效的利用数据库，下面是一些常用的元命令。
例1：列出所有的数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
例2：列出所有的 schema
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
例3：列出所有的数据库用户和角色
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
例4：获得连接信息
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
注：通过在psql命令行键入 ? 可以获得更多元命令的帮助。
数据库操作
PostgreSQL数据库操作相关的常用命令如下：
查看数据库列表：/l
create database <数据库名>;
drop database <数据库名>;
切换数据库：\c <数据库名>
CREATE DATABASE创建数据库
可以通过CREATE DATABASE创建一个新的PostgreSQL数据库。
例1：创建新数据库
postgres=# create database mydb1;
CREATE DATABASE
例2：创建指定owner的数据库
postgres=# create database mydb2 owner user1 ;
CREATE DATABASE
查看已经存在的数据库
使用 \l 命令可以 查看已经存在的数据库。
例：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
mydb1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
mydb2 | user1 | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
ALTER DATABASE修改数据库
通过ALTER DATABASE命令更改一个数据库的属性。
例：修改数据库的所有者
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
mydb1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
mydb2 | user1 | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres=#
postgres=#
postgres=# alter database mydb1 owner to user1;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
mydb1 | user1 | UTF8 | C.UTF-8 | C.UTF-8 |
mydb2 | user1 | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
\c 切换数据库
可以通过 \c <数据库名>命令切换数据库。
postgres=# \c mydb1
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "mydb1" as user "postgres".
mydb1=# \conninfo
You are connected to database "mydb1" as user "postgres" on host "localhost" (address "::1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
mydb1=#
查看数据库的版本
postgres=# SELECT version();
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit