02-PostgreSQL的基本使用

一、数据库操作

①: 登录到数据库

psql -U postgres -d postgres -h 127.0.0.1

②:查看所有数据库

\l

③: 创建数据库

# 创建一个名为 mydb 的数据库
create database mydb;

④:切换数据库

# \c 数据库名
\c mydb

⑤:删除数据库

# 删除前 先确保数据库没有被连接
drop database mydb;

二、数据库表操作

①：数值类型

②：数据表操作

1. 创建表

1. PostgreSQL中三种自增列sequence，serial，identity区别

2. 创建表

create table test(id serial primary key, name varchar(255));

2. 在表中插入数据

insert into test(name) values('Anne');

3. 查看当前数据库下所有的表

\d

4. 查看表结构，相当于desc

\d test(表面)

5. 查询所有数据

三、 Schema

①：介绍

PostgreSQL 模式（Schema）可以理解为是一个表的集合（类似于Oracle的表空间概念）。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

1. 允许多个用户使用一个数据库并且不会互相干扰
2. 将数据库对象组织成逻辑组以便更容易管理
3. 第三方应用的对象可以放在独立的模式中，这样他们就不会与其他对象的名称发生冲突

模式类似于操作系统层的目录，但是模式不能嵌套。

②：创建一个新的Schema

1. 创建前（有一个默认的public）并且之前创建的test表也在其中

2. 创建一个新的Schema

create schema newSchema;

3. 在新的Schema中在创建一张表test

create table newSchema.test(id serial primary key, name varchar(255));

四、如何备份PostgreSQL数据库

如果您在生产环境中使用PostgreSQL,请务必采取预防措施以确保用户的数据不会丢失。

①：必看

1. 登录

2. 注意 要在shell控制台中执行操作而不是数据库控制台

②：单数据库

PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行此命令。

1. 备份

备份格式有几种选择：

• *.bak: 压缩二进制格式

• *.sq1: 明文转储

• *.tar: tarball

#备份数据库
$ pg_dump -U postgres -f /tmp/postgres.sql postgres(导出postgres数据库保存为postgres.sq1)
$ pg_dump -U postgres -f /tmp/postgres.sql-t test postgres(导出postgres数据库中表test的数据）
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres(导出postgres数据库以tar形式压缩保存为postgres.tar)#恢复数据库
$ psql -U postgres -f /tmp/postgres.sql bk01(恢复postgres.sq1数据到bk01数据库）
#pg_restore--从pg_dump创建的备份文件中恢复PostgreSQL数据库，用于恢复由pg_dump转储的任何非纯文本格式中的PostgreSQL数据库。
$ pg_restore-Upostgres-dbk01/tmp/postgres.tar(恢复postgres.tar数据到bk01数据库）

1. 通过运行以下命令将数据库的内容转储到文件中。替换dbname为要备份的数据库的名称。

    # 通过-f 指定文件输出目录pg_dump -f /usr/local/src/mydb.sql mydb;

2. 恢复

1. 删除已经备份的mydb数据库

2. 创建一个新的空数据库（mydb2）

3. 恢复数据库（-f 指定备份的文件路径）

 psql -U postgres -f /usr/local/src/mydb.sql mydb2;

可以看到数据恢复成功

③：所有数据库

五、用户操作

#查看用户
\du#创建用户并设置密码
CREATE USER 'username' WITH PASSWORD 'password' ;
CREATE USER test WITH PASSWORD 'test';#修改用户密码
$ ALTER USER ' username' WITH PASSWORD ' password' ;#数据库授权，赋予指定账户指定数据库所有权限
$ GRANT ALL PRIVILEGES ON DATABASE ' dbname' TO ' username' ;#将数据库mydb权限授权于test
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;#但此时用户还是没有读写权限，需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;#注意，该sq1语句必须在所要操作的数据库里执行
#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test#删除用户
drop user test

①：创建用户并设置密码

1. 注意: 先切换到需要操作的数据库里

2. 查看所有用户

2. 创建用户

create user coke with password 'coke';

②：修改用户密码

1. 修改密码

alter user coke with password 'new_coke';

2. 使用新密码登录

psql -U coke -d mydb2 -h 127.0.0.1;

3. 执行查询sql

新创建的用户没有任何权限，需要进行权限授权

③：数据库授权

1. 数据库授权，赋予指定账户指定数据库所有权限

grant all privileges on database mydb2 to coke;

2. 但此时用户还是没有读写权限，需要继续授权表

grant all privileges on all tables in schema public to coke;

3. 测试（已经有权限了）

④：移除用户数据表权限

revoke all privileges on database mydb2 from coke;

④：移除用户数据库权限

revoke all privileges on database mydb2 from coke;

⑤：删除用户

drop user coke;