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 的表。
使用模式的优势：
- 允许多个用户使用一个数据库并且不会互相干扰
- 将数据库对象组织成逻辑组以便更容易管理
- 第三方应用的对象可以放在独立的模式中，这样他们就不会与其他对象的名称发生冲突
模式类似于操作系统层的目录，但是模式不能嵌套。
②：创建一个新的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;
