# postgresql学习笔记
**Repository Path**: HaiXiuDeDXianSheng/postgresql-learning-notes
## Basic Information
- **Project Name**: postgresql学习笔记
- **Description**: 用于记录postgresql学习笔记
- **Primary Language**: SQL
- **License**: GPL-3.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 3
- **Forks**: 0
- **Created**: 2023-11-28
- **Last Updated**: 2024-08-19
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
- [1. postgresql 安装](#1-postgresql-安装)
- [2. postgresql 的基本使用](#2-postgresql-的基本使用)
- [2.1. 数据库操作](#21-数据库操作)
- [2.1.1. 命令行登录数据库](#211-命令行登录数据库)
- [2.1.2. 创建数据库](#212-创建数据库)
- [2.1.3. 查看有哪些数据库](#213-查看有哪些数据库)
- [2.1.4. 切换数据库](#214-切换数据库)
- [2.1.5. 删除数据库](#215-删除数据库)
- [2.2. 数据表操作](#22-数据表操作)
- [2.2.1. 数据类型](#221-数据类型)
- [2.2.1.1. 数值类型](#2211-数值类型)
- [2.2.1.2. 字符类型](#2212-字符类型)
- [2.2.1.3. 日期/时间类型](#2213-日期时间类型)
- [2.2.1.4. 其他数据类型](#2214-其他数据类型)
- [2.2.1.5. 自定义数据类型](#2215-自定义数据类型)
- [2.2.2. 创建表格](#222-创建表格)
- [2.2.3. DML 操作](#223-dml-操作)
- [2.2.3.1. INSERT 数据](#2231-insert-数据)
- [2.2.3.2. UPDATE 数据](#2232-update-数据)
- [2.2.3.3. DELETE 数据](#2233-delete-数据)
- [2.3. SCHEMA (模式)](#23-schema-模式)
- [2.3.1. 创建 schema](#231-创建-schema)
- [2.3.2. 查看 schema](#232-查看-schema)
- [2.3.3. 删除 schema](#233-删除-schema)
- [2.4. 视图 View](#24-视图-view)
- [2.4.1. 视图 VS 物化视图](#241-视图-vs-物化视图)
- [2.4.2. 创建视图](#242-创建视图)
- [2.4.3. 创建物化视图](#243-创建物化视图)
- [2.5. 函数](#25-函数)
- [2.5.1. 自定义函数](#251-自定义函数)
- [2.5.1.1. 用 sql 定义函数](#2511-用-sql-定义函数)
- [2.5.1.2. 用 pgsql 定义函数（类似于mysql存储过程）](#2512-用-pgsql-定义函数类似于mysql存储过程)
- [2.6. 存储过程](#26-存储过程)
- [2.7. 触发器](#27-触发器)
- [2.8. CURSORS 游标](#28-cursors-游标)
- [2.9. 备份 postresql 数据库](#29-备份-postresql-数据库)
- [2.9.1. 单数据库备份](#291-单数据库备份)
- [2.9.2. 备份所有数据库](#292-备份所有数据库)
- [2.9.3. 单恢复数据库](#293-单恢复数据库)
- [2.10. 用户操作](#210-用户操作)
- [2.10.1. 创建用户](#2101-创建用户)
- [2.10.2. 修改密码](#2102-修改密码)
- [2.10.3. 数据库授权](#2103-数据库授权)
- [2.10.3.1. 执行实例](#21031-执行实例)
- [2.10.4. 删除用户](#2104-删除用户)
- [2.11. 角色管理](#211-角色管理)
- [2.11.1. 角色属性](#2111-角色属性)
- [2.11.2. pgsql 控制台常用命令](#2112-pgsql-控制台常用命令)
- [3. 常用操作](#3-常用操作)
- [3.1. 使用正则匹配](#31-使用正则匹配)
# 1. postgresql 安装
- 参考资料
- http://www.manongjc.com/detail/56-pcaisabtofkskqf.html
- https://www.bilibili.com/video/BV1p44y1X78F
- 步骤
- 列出可用的 postgresql 模块
>```bash
>dnf module list postgresql
>```
- 安装可用的 pgsql 版本
>```bash
>sudo dnf install @postgresql:16
>```
- 安装 postgresql-contrib,给 pgsql 数据库提供一些额外特性
>```bash
>sudo dnf install postgresql-contrib
>```
- 使用命令初始化 pgsql 数据库
>```
>[hadoop@node01 ~]$ sudo postgresql-setup initdb
>WARNING: using obsoleted argument syntax, try --help
>WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql # 提示要使用这个命令来进行初始化，上面的命令过时了
> * Initializing database in '/var/lib/pgsql/data'
> * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
>```
- 启动 pgsql 服务，并且启用开机启动 pgsql 服务
>````bash
>[hadoop@node01 ~]$ sudo systemctl enable --now postgresql
>Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
>```
- pgsql 安装成功后，会默认创建一个名为 postgresql 的 linux 用户，初始化数据库后，会有名为 postgresql 的数据库，来存储数据库的基本信息，相当于 Mysql 中默认的名为 mysql 的数据库
- pgsql 数据库中会初始化一名 super 用户 postgres, 密码是 psql
- 使用 psql 工具连接 PostgreSQL 数据库服务器并且打印它的版本号，以验证安装过程
>```bash
>[hadoop@node01 ~]$ sudo -u postgres psql -c "SELECT version();"
>[sudo] hadoop 的密码：
> version
>-------------------------------------------------------------------------------------------------------------
> PostgreSQL 16.0 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
>(1 行记录)
>```
- 认情况下，PostgreSQL 服务器仅仅监听本地网络接口：127.0.0.1, 为了允许远程访问你的 PostgreSQL 服务器，打开配置文件
>```bash
>sudo nano /var/lib/pgsql/data/postgresql.conf
>```
- 往下滑动到CONNECTIONS AND AUTHENTICATION部分，并且添加或者编辑下面的行：
>```conf
>#------------------------------------------------------------------------------
># CONNECTIONS AND AUTHENTICATION
>#------------------------------------------------------------------------------
>
># - Connection Settings -
>
>listen_addresses = '*' # what IP address(es) to listen on;
>```
- 保存文件，重启 pgsql 服务
>```bash
>sudo systemctl restart postgresql
>```
- 使用ss工具来验证这个修改
>```
>[hadoop@node01 ~]$ ss -nlt | grep 5432
>LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
>LISTEN 0 128 [::]:5432 [::]:*
>```
- 修改 pg_hba.conf 文件
>```bash
>[hadoop@node01 ~]$ sudo vim /var/lib/pgsql/data/pg_hba.conf
>```
>```conf
># IPv4 local connections:
>host all all 0.0.0.0/0 md5
>host all all 127.0.0.1/32 ident
>```
- 为了远程访问安全，需要开启访问端口和启用防火墙
>```bash
>[hadoop@node01 ~]$ sudo systemctl start firewalld.service
>[hadoop@node01 ~]$ sudo systemctl enable firewalld.service
>Created symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service → /usr/lib/systemd/system/firewalld.service.
>Created symlink /etc/systemd/system/multi-user.target.wants/firewalld.service → /usr/lib/systemd/system/firewalld.service.
>[hadoop@node01 ~]$ sudo systemctl status firewalld
>● firewalld.service - firewalld - dynamic firewall daemon
> Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
> Active: active (running) since Tue 2023-11-28 10:29:22 EST; 50s ago
> Docs: man:firewalld(1)
> Main PID: 25014 (firewalld)
> Tasks: 2 (limit: 49275)
> Memory: 30.5M
> CGroup: /system.slice/firewalld.service
> └─25014 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
>
>11月 28 10:29:22 node01.zhd.org systemd[1]: Starting firewalld - dynamic firewall daemon...
>11月 28 10:29:22 node01.zhd.org systemd[1]: Started firewalld - dynamic firewall daemon.
>11月 28 10:29:23 node01.zhd.org firewalld[25014]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure configuration option. It will be removed in a>
>...skipping...
>● firewalld.service - firewalld - dynamic firewall daemon
> Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
> Active: active (running) since Tue 2023-11-28 10:29:22 EST; 50s ago
> Docs: man:firewalld(1)
> Main PID: 25014 (firewalld)
> Tasks: 2 (limit: 49275)
> Memory: 30.5M
> CGroup: /system.slice/firewalld.service
> └─25014 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
>
>11月 28 10:29:22 node01.zhd.org systemd[1]: Starting firewalld - dynamic firewall daemon...
>11月 28 10:29:22 node01.zhd.org systemd[1]: Started firewalld - dynamic firewall daemon.
>11月 28 10:29:23 node01.zhd.org firewalld[25014]: WARNING: AllowZoneDrifting is enabled. This is considered an insecure configuration option. It will be removed in a>
>~
>```
- 防火墙授权开放端口
>```bash
>[hadoop@node01 ~]$ sudo firewall-cmd --permanent --add-port=5432/tcp # 防火墙开放相关端口
>success
>[hadoop@node01 ~]$ sudo firewall-cmd --reload # 重载防护火墙
>success
>```
- 在外部使用 navicat 进行远程连接
- 需要修改 postgres 用户的密码
>```bash
>[hadoop@node01 ~]$ sudo -u postgres psql
>[sudo] hadoop 的密码：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>postgres=# alter user postgres with password 'psql';
>ALTER ROLE
>```
- navicat 再次连接可以成功
# 2. postgresql 的基本使用
## 2.1. 数据库操作
### 2.1.1. 命令行登录数据库
>```bash
># psql -h 服务器 -U 用户名 -d 数据库 p 端口地址 // -U 是大写
>psq1 -U dbuser -d exampledb -h 127.0.0.1 -p 5432
>```
### 2.1.2. 创建数据库
>```bash
>postgres=# create database zhd_db;
>CREATE DATABASE
>```
### 2.1.3. 查看有哪些数据库
- 在命令控制台输入 \l, 查看数据库中的所有 database
- 在 pgsql 中无法使用 show databases;
>```bash
>postgres=# \l
> 数据库列表
> 名称 | 拥有者 | 字元编码 | Locale Provider | 校对规则 | Ctype | ICU Locale | ICU Rules | 存取权限
>-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
> postgres | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
> template0 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
> template1 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
> zhd_db | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
>(4 行记录)
>```
### 2.1.4. 切换数据库
- 使用\c target_database切换数据库
- mysql 中使用use target_database切换数据库
>```bash
>postgres=# \c zhd_db;
>您现在已经连接到数据库 "zhd_db",用户 "postgres".
>zhd_db=#
>```
### 2.1.5. 删除数据库
>```bash
>postgres=# drop database zhd_db;
>DROP DATABASE
>```
## 2.2. 数据表操作
### 2.2.1. 数据类型
- 参考资料: https://www.runoob.com/postgresql/postgresql-data-type.html
- pgsql 常用的三类数据类型
- 数值数据类型
- 字符串数据类型
- 日期/时间数据类型
#### 2.2.1.1. 数值类型
|名字 |存储长度 | 描述 |范围|
|:-:|:-:|:-:|:-:|
|smallint | 2 字节 | 小范围整数 | -32768 到 +32767|
|integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647|
|bigint | 8 字节 | 大范围整数 |-9223372036854775808 到 +9223372036854775807|
|decimal | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位|
|numeric | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位|
|real | 4 字节 | 可变精度，不精确 | 6 位十进制数字精度|
|double precision | 8 字节 | 可变精度，不精确 | 15 位十进制数字精度|
- postgresql 中使用序列来表示自增长，mysql 中用 auto increment
|名字 |存储长度 | 描述 |范围|
|:-:|:-:|:-:|:-:|
|smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767|
|serial | 4 字节 | 自增整数 |1 到 2147483647|
|bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807|
#### 2.2.1.2. 字符类型
|序号|类型|描述|
|:-:|:-:|:-:|
|1|character varying(n),varchar(n)|变长，有长度限制|
|2|character(n),char(n)|定长，不足补空白|
|3|text|变长，无长度限制|
#### 2.2.1.3. 日期/时间类型
|名字 |存储空间 |描述 |最低值 |最高值 | 分辨率|
|:-:|:-:|:-:|:-:|:-:|:-:|
|timestamp [ (p) ] [ without time zone ] |8 字节 |日期和时间(无时区) |4713 BC |294276 AD | 1 毫秒 / 14 位|
|timestamp [ (p) ] with time zone |8 字节 |日期和时间，有时区 |4713 BC |294276 AD | 1 毫秒 / 14 位|
|date |4 字节 |只用于日期 |4713 BC |5874897 AD | 1 天|
|time [ (p) ] [ without time zone ] |8 字节 |只用于一日内时间 |00:00:00 |24:00:00 | 1 毫秒 / 14 位|
|time [ (p) ] with time zone |12 字节 |只用于一日内时间，带时区 |00:00:00+1459 |24:00:00-1459 | 1 毫秒 / 14 位|
|interval [ fields ] [ (p) ] |12 字节 |时间间隔 |-178000000 年 |178000000 年 | 1 毫秒 / 14 位|
#### 2.2.1.4. 其他数据类型
- 还支持 boolean、货币、枚举、集合、json、数组等数据类型
#### 2.2.1.5. 自定义数据类型
- 在 [创建用户/角色](#253-数据库授权) 后还要进行如下授权
>```bash
>[hadoop@node01 ~]$ psql -U postgres -d postgres
>用户 postgres 的口令：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>postgres=# \du
> 角色列表
> 角色名称 | 属性
>----------+--------------------------------------------
> postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS
> zhd |
>
>postgres=# \c zhd_test_db;
>您现在已经连接到数据库 "zhd_test_db",用户 "postgres".
>
>zhd_test_db=# grant create on schema public to zhd;
>GRANT
>zhd_test_db=# \du
>```
- 执行如下创建自定义数据类型语句
>```sql
>create type sex_type as enum('M','F')
>```
- 使用自定义的数据类型创建一个表
>```sql
>create table custumer(id serial,name varchar(20),sex sex_type);
>```
- 往表里插入数据
>```sql
>insert into custumer (name,sex) values ('zhangsan','M'); -- 可以成功插入数据
>```
- 当尝试使用以下语句插入数据时
>```sql
>insert into custumer (name,sex) values ('lisi','X');
>```
- 改变表字段类型
>```sql
>ALTER TABLE custumer ALTER COLUMN sex sex_type USING sex::sex_type;
>```
### 2.2.2. 创建表格
>```bash
>zhd_db=# create table zhd_test_01(
> id serial primary key,
> name varchar(255)
> ); # id 为主键自增, mysql 是 int auto increment
>CREATE TABLE
>
>zhd_db=# \d # 使用 \d 查看表格是否创建成功
> 关联列表
> 架构模式 | 名称 | 类型 | 拥有者
>----------+--------------------+--------+----------
> public | zhd_test_01 | 数据表 | postgres
> public | zhd_test_01_id_seq | 序列数 | postgres
>(2 行记录)
>
>zhd_db=# \d zhd_test_01 # \d 表名，可以查看表结构
> 数据表 "public.zhd_test_01"
> 栏位 | 类型 | 校对规则 | 可空的 | 预设
>------+------------------------+----------+----------+-----------------------------------------
> id | integer | | not null | nextval('zhd_test_01_id_seq'::regclass)
> name | character varying(255) | | |
>索引：
> "zhd_test_01_pkey" PRIMARY KEY, btree (id)
>
>```
### 2.2.3. DML 操作
#### 2.2.3.1. INSERT 数据
>```bash
>zhd_db=# insert into zhd_test_01(name) values('zhangsan');
>INSERT 0 1
>zhd_db=# select * from zhd_test_01;
> id | name
>----+----------
> 1 | zhangsan
>(1 行记录)
>```
- 插入多行
>```bash
>zhd_db=# insert into zhd_test_01(name) values('lisi'),('wangwu'),('qianliu');
>INSERT 0 3
>```
- 可以看到这里在 insert 之后返回了 0 3 这样的信息
|序号 |输出信息 |描述|
|:-:|:-:|:-:|
|1 |INSERT oid 1|只插入一行并且目标表具有 OID 的返回信息， 那么 oid 是分配给被插入行的 OID。|
|2 |INSERT 0 #|插入多行返回的信息， # 为插入的行数。|
#### 2.2.3.2. UPDATE 数据
>```bash
>zhd_db=# update zhd_test_01 set name='zhangsan_01' where id=1;
>UPDATE 1
>```
#### 2.2.3.3. DELETE 数据
>```bash
>zhd_db=# delete from zhd_test_01 where id=2;
>DELETE 1
>zhd_db=# select * from zhd_test_01;
> id | name
>----+-------------
> 3 | wangwu
> 4 | qianliu
> 1 | zhangsan_01
>(3 行记录)
>```
## 2.3. SCHEMA (模式)
- postgresql 的 schema 可以看成是一个表的集合
- 一个 shcema 可以包含：视图、索引、数据类型、函数、操作符等
- schema 的作用
- **允许多个用户使用同一个数据库并且不会互相干扰**。
- 相当于在 hadoop 体系下一个集群中的不同租户
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
### 2.3.1. 创建 schema
>````bash
>zhd_db=# create schema zhd_schema;
>CREATE SCHEMA
>zhd_db=# create table zhd_schema.mytest01(id int,name varchar(255));
>CREATE TABLE
>```
### 2.3.2. 查看 schema
>```
>zhd_db=# \dn
> 架构模式列表
> 名称 | 拥有者
>------------+-------------------
> public | pg_database_owner
> zhd_schema | postgres # 在不同 schema 中可以创建相同的表名
>(2 行记录)
>
>zhd_db=# select * from information_schema.schemata;
> catalog_name | schema_name | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path
>--------------+--------------------+-------------------+-------------------------------+------------------------------+----------------------------+----------
> zhd_db | public | pg_database_owner | | | |
> zhd_db | zhd_schema | postgres | | | |
> zhd_db | information_schema | postgres | | | |
> zhd_db | pg_catalog | postgres | | | |
> zhd_db | pg_toast | postgres | | | |
>(5 行记录)
>```
### 2.3.3. 删除 schema
- 默认情况下，postgresql 中删除一个 schema, 其中的表也会删除
>```bash
>zhd_db=# drop schema zhd_schema cascade; # cascade 表示级联，删除 schema 的同时会删除其下面的所有对象，包括表、函数、视图等
>注意: 递归删除 表 zhd_schema.mytest01
>DROP SCHEMA
>```
- 删除空的 schema
>```sql
>drop schema zhd_schema -- 该语句不支持删除非空 schema
>```
## 2.4. 视图 View
- 视图实际是一个虚拟的表，是由 sql 拼接出来的虚拟表
- 视图常常用来只展示用户想要的信息，隐藏不必要的数据
- **视图就是每次实时查询底层的表，数据都是实时的**
### 2.4.1. 视图 VS 物化视图
|类型/对比项|物理结构|时效性|有无索引|
|:-:|:-:|:-:|:-:|
|视图|虚拟表|实时|无索引|
|物化视图|物理表|非实时|可以创建索引|
### 2.4.2. 创建视图
### 2.4.3. 创建物化视图
>```sql
>CREATE MATERIALIZED VIEW 'mv_user_tab' AS
>select * from table_name;
>```
## 2.5. 函数
### 2.5.1. 自定义函数
#### 2.5.1.1. 用 sql 定义函数
- 基本语法
>```sql
>CREATE OR REPLACE FUNCTION function_name() RETURNS void as
>' -- 分隔符
> --sql command
>'
>LANGUAGE SQL -- USING the SQL LAANGUAGE
>```
- 例如定义一个两数相加的函数
>```sql
>create or replace function fn_add_ints(int,int)
>returns int as
>$body$ -- 用作分隔符
>select $1 + $2;
>$body$
>language sql
>```
- 返回一条表记录
- 返回多行数据
#### 2.5.1.2. 用 pgsql 定义函数（类似于mysql存储过程）
>```sql
>CREATE OR REPLACE FUNCTION func_name(parameter par_type) RETURNS ret_type AS
>$body$
>BEGIN
>-- statements
>END
>$body$
>LANGUAGE plpgsql
>```
>```sql
>CREATE OR REPLACE FUNCTION fn_get_0_expensive_prods()
>RETURNS TABLE (
> name varchar ,
> supplier varchar.
> price numeric
>) AS
>$body$
>BEGIN
>RETURN QUERY
> SELECT product.name, product.supplier, item.price
> FROM item
> NATURAL JOIN product
> ORDER BY item.price
> DESC
> LIMIT 10;
>END;
>$body$
>LANGUAGE plpgsql
>```
- 这部分可参考 [Mysql存储函数](https://gitee.com/HaiXiuDeDXianSheng/msql-learning-notes#123-%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0)
## 2.6. 存储过程
- 参考 [mysql存储过程](https://gitee.com/HaiXiuDeDXianSheng/msql-learning-notes#122-%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B)
- 之所以写存储过程，是因为它可能无法写成一个函数
>```sql
>CREATE OR REPLACE PROCEDURE procedure_name(parameters)
>AS
>$body$
>DECLARE
>BEGIN
>END;
>$body$
>LANGUAGE PLPGSQL
>```
- 执行存储过程
>```sql
>CALL procedure_name();
>```
## 2.7. 触发器
- 参考 [mysql触发器](https://gitee.com/HaiXiuDeDXianSheng/msql-learning-notes#121-%E8%A7%A6%E5%8F%91%E5%99%A8)
>```sql
>CREATE TRIGGER trigger_name -- 给某个事件加上触发器
>{BEFORE|AFTER} {event}-- Event : insert, update, insert
>ON table_name
>[FOR [EACH]{ROW | STATEMENT}]
>EXECUTE PROCEDURE trigger_function
>```
- 案例
>```sql
>CREATE OR REPLACE FUNCTION fn_log_dist_name_change() -- 自定义一个函数，但是返回类型是触发器
> RETURN TRIGGER
> LANGUAGE PLPGSQL
>AS
>$body$
>BEGIN
> IF NEW.name <> OLD.name THEN
> INSERT INTO distributor_audit(dist_id, name, edit_date) VALUES
> (oLD.id，OLD.name， NOW());
> END IF;
> RAISE NOTICE 'Trigger Name : %'，TG_NAME;
> RAISE NOTICE 'Table Name : %'，TG_TABLE_NAME;
> RAISE NOTICE 'Operation : %'，TG_OP;
> RAISE NOTICE 'when Executed : %'，TG_WHEN;
> RAISE NOTICE 'Row or Statement : %'，TG_LEVEL;
> RAISE NOTICE 'Table Schema : %'，TG_TABLE_SCHEMA;
> RETURN NEW;
>END;
>$body$
>
>CREATE TRIGGER tr_dist_name_changed -- 给某个事件加上触发器
>BEFORE UPDATE
>ON distributor
>FOR EACH ROW
>EXECUTE PROCEDURE fn_log_dist_name_change();
>```
## 2.8. CURSORS 游标
- 游标，实际上就是当前值
## 2.9. 备份 postresql 数据库
- 在生产环境中使用 postgresql，需要有预防措施来确保用户的数据不会丢失
### 2.9.1. 单数据库备份
- postgresql 提供了 pg_dump 程序来简化备份单个数据库的过程，必须以对要备份的1数据库具有读权限的用户身份运行命令
- 通过以下命令将数据库转移到文件中
>```bash
>[hadoop@node01 ~]$ sudo su - postgres # 切换 postgres 用户
>[sudo] hadoop 的密码：
>[postgres@node01 ~]$ ll
>总用量 8
>drwx------. 2 postgres postgres 6 10月 16 04:33 backups
>drwx------. 20 postgres postgres 4096 12月 4 07:36 data
>-rw-------. 1 postgres postgres 977 11月 28 10:21 initdb_postgresql.log
>[postgres@node01 ~]$ pg_dump zhd_db > zhd_db.bak
>```
- 备份格式有几种选择
- **\*.bak**：压缩二进制格式
- **\*.sql**：名文转储
- **\*.tar**：tarball
>```
>使用方法:
> pg_dump [选项]... [数据库名字]
>
> -f, --file=FILENAME 输出文件或目录名
> -F, --format=c|d|t|p 输出文件格式 (定制, 目录, tar
> 明文 (默认值))
> -t,--table=PATTEN 指定表名
>
>
>...
>
>联接选项:
> -d, --dbname=DBNAME 对数据库 DBNAME备份
> -h, --host=主机名 数据库服务器的主机名或套接字目录
> -p, --port=端口号 数据库服务器的端口号
> -U, --username=名字 以指定的数据库用户联接
> -w, --no-password 永远不提示输入口令
> -W, --password 强制口令提示 (自动)
> --role=ROLENAME 在转储前运行SET ROLE
>```
### 2.9.2. 备份所有数据库
- 使用 pg_dumpall 程序备份所有数据库
>```bash
>postgres=# pg_dumpall > pg_backup.bak
>```
### 2.9.3. 单恢复数据库
- 删除数据
>```bash
>[postgres@node01 ~]$ psql
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>postgres=# \l
> 数据库列表
> 名称 | 拥有者 | 字元编码 | Locale Provider | 校对规则 | Ctype | ICU Locale | ICU Rules | 存取权限
>-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
> postgres | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
> template0 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
> template1 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
> zhd_db | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
>(4 行记录)
>
>postgres=# drop database zhd_db;
>DROP DATABASE
>postgres=# \l
> 数据库列表
> 名称 | 拥有者 | 字元编码 | Locale Provider | 校对规则 | Ctype | ICU Locale | ICU Rules | 存取权限
>-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
> postgres | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
> template0 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
> template1 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
> | | | | | | | | postgres=CTc/postgres
>(3 行记录)
>```
- 创建空的数据库
>```bash
>postgres=# create database zhd_test_db;
>CREATE DATABASE
>```
- 恢复数据库
>```bash
>[postgres@node01 ~]$ psql -f ./zhd_db.bak zhd_test_db;
>SET
>SET
>SET
>SET
>SET
> set_config
>------------
>
>(1 行记录)
>
>SET
>SET
>SET
>SET
>SET
>SET
>CREATE TABLE
>ALTER TABLE
>CREATE SEQUENCE
>ALTER SEQUENCE
>ALTER SEQUENCE
>ALTER TABLE
>COPY 3
> setval
>--------
> 4
>(1 行记录)
>
>ALTER TABLE
>```
- 数据库恢复验证
>```bash
>postgres=# \c zhd_test_db
>您现在已经连接到数据库 "zhd_test_db",用户 "postgres".
>zhd_test_db=# \d
> 关联列表
> 架构模式 | 名称 | 类型 | 拥有者
>----------+--------------------+--------+----------
> public | zhd_test_01 | 数据表 | postgres
> public | zhd_test_01_id_seq | 序列数 | postgres
>(2 行记录)
>```
- pg_store 命令
>```
># pg restore --从pg_dump创建的备份文件中恢复ostgresQL数据库,用于恢复由 pg_dump 转储的任何非纯文本格式中的PostgreSQL数据库。(例如 tar 格式)
>$ p_restore -U postgres -d bk01 /tmp/postgres.tar (恢复postgres.tar数据到bk01数据库)
>```
## 2.10. 用户操作
### 2.10.1. 创建用户
>```sql
># 创建用户并设置密码
>CREATE USER 'username' WITH PASSWORD 'password';
>CREATE USER test WITH PASSWORD 'test';
>```
### 2.10.2. 修改密码
>```sql
> ALTER USER 'username' WITH PASSWORD 'password';
>```
### 2.10.3. 数据库授权
- 数据库授权
>```sql
> -- 数据库授权，给予指定账户数据库所有权限
>GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username'
> -- 将数据库 zhd_db 赋权给 test 用户
> GRANT ALL PRIVILEGES ON DATABASE zhd_db TO test_user;
>
>-- 但此时用户还是没有读写权限，需要继续授权表
> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;
>```
- 移除指定账户数据库的所有权限
>```SQL
> REVOKE ALL PRIVILEGE ON DATABASE zhd_db FROM test_user;
>```
- 删除用户
>```sql
>drop user test_user
>```
#### 2.10.3.1. 执行实例
>```bash
>postgres=# create user zhd with password 'zhd';
>CREATE ROLE
>
>postgres=# grant all privileges on database zhd_test_db to zhd;
>GRANT
>postgres=# grant all privileges on all tables in schema public to zhd; # 必须要在所要操作的数据库中执行
>GRANT
>
>postgres=# \du
> 角色列表
> 角色名称 | 属性
>----------+--------------------------------------------
> postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS
> zhd |
>```
- 尝试用新用户登录, 发现不能正常登录
>```bash
>[hadoop@node01 ~]$ psql -U zhd zhd
>psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 对用户"zhd"的对等认证失败
>[hadoop@node01 ~]$ psql -U zhd -w zhd
>psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 对用户"zhd"的对等认证失败
>[hadoop@node01 ~]$ sudo psql -U zhd -w zhd
>psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 对用户"zhd"的对等认证失败
>[hadoop@node01 ~]$ sudo -u postgres psql
>```
- 修改配置文件
>```bash
>[hadoop@node01 ~]$ sudo vim /var/lib/pgsql/data/pg_hba.conf
>```
>```conf
># "local" is for Unix domain socket connections only
>local all all peer # 是用 linux 操作系统的用户来进行登录
>
># 改成如下
>
># "local" is for Unix domain socket connections only
>local all all md5 # 用密码进行登录
>```
- 重启 pgsql
>```bash
>[hadoop@node01 data]$ sudo systemctl restart postgresql
>```
- 使用新用户登录数据库
>```bash
>[hadoop@node01 data]$ psql -U zhd -d zhd_test_db
>用户 zhd 的口令：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>zhd_test_db=>
>```
- 对用户授权 (需要使用管理员账户登录进行授权)
>```bash
>[hadoop@node01 ~]$ psql -U postgres -d postgres # 使用管理员账户进行登录授权
>用户 postgres 的口令：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>postgres=# grant all privileges on database zhd_test_db to zhd; # 授权 zhd 账户对数据库 zhd_test_db 的访问权限
>GRANT
>```
- **管理员账户切换到所要赋予读取权限的数据库，对目标账户进行授权。**
>```bash
>postgres=# \c zhd_test_db; # 切换到目标数据库
>您现在已经连接到数据库 "zhd_test_db",用户 "postgres".
>zhd_test_db=# grant all privileges on all tables in schema public to zhd; # 在目标数据库下进行表权限的授权
>GRANT
>zhd_test_db=# \q
>[hadoop@node01 ~]$ psql -U zhd -d zhd_test_db
>用户 zhd 的口令：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>zhd_test_db=> select * from zhd_test_01;
> id | name
>----+-------------
> 3 | wangwu
> 4 | qianliu
> 1 | zhangsan_01
>(3 行记录)
>```
### 2.10.4. 删除用户
- 例如前面我们创建了用户 zhd，并对其赋予了数据库权限
- 尝试删除数据库
>```bash
>zhd_test_db=> \q
>[hadoop@node01 ~]$ psql -U postgres -d postgres
>用户 postgres 的口令：
>psql (16.0)
>输入 "help" 来获取帮助信息.
>
>postgres=# drop user zhd;
>错误: 无法删除"zhd"因为有其它对象倚赖它
>描述: 数据库 zhd_test_db的权限
>在数据库 zhd_test_db中的1个对象
>```
- 需要把账户上的所有权限回收之后才能删除账户
>```bash
>postgres=# \c zhd_test_db;
>zhd_test_db=# revoke all privileges on database zhd_test_db from zhd;
>REVOKE
>zhd_test_db=# drop user zhd;
>错误: 无法删除"zhd"因为有其它对象倚赖它
>描述: 表 zhd_test_01的权限
>zhd_test_db=# revoke all privileges on all tables in schema public from zhd;
>REVOKE
>zhd_test_db=# drop user zhd;
>DROP ROLE
>```
## 2.11. 角色管理
- pgsql 没有区分用户和角色的概念，CREATE USER 是 CREATE ROLE 的别名，这两个几乎是相同的，唯一的区别是CREATE USER命令创建的用户默认带有一个LOGIN属性，而CREATE ROLE命令创建的用户默认不带LOGIN属性
### 2.11.1. 角色属性
|属性|说明|
|:-:|:-:|
|login|只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名|
|superuser|数据库超级用户|
|createdb|创建数据库权限|
|createrole|允许其创建或删除其他普通的用户角色(超级用户除外)|
|replication|做流复制的时候用到的一个用户属性，一般单独设定。|
|password|在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关|
|inherit|用户组对组员的一个继承标志，成员可以继承用户组的权限特性|
- 指定一个用户有什么角色属性，实际上就是相当于赋予用户什么权限（角色属性）
- 权限是加在角色中的
### 2.11.2. pgsql 控制台常用命令
>```
>\q 退出
>\h 查看命令解释。例如 \h select
>\? 查看psql 命令列表
>\c [database_name] 切换其他数据库
>\d 列出当前数据库所有表格
>\d [table_name] 列出某一张表的结构
>\du 列出所有用户
>\password 设置密码
>```
# 3. 常用操作
- 创建索引
>```SQL
>CREATE INDEX transation_id ON transaction_table(name);
>```
- 清空表数据, truncate table 仅清空数据不删除表结构,drop table 才会删除表结构
>```sql
>truncate table table_name
>```
- postgresql 的几种 join
- cross join: 会产生笛卡尔积
- inner join:
- left outer join: 等同于 left join
- right outer join: 等同于 right join
- similar to, 相当于 mysql 的 like (psql 中也可以使用 like)
>```sql
> SELECT first_name, last_name
> FROM customer
> WHERE first_name SIMILAR TO 'M%';
>```
## 3.1. 使用正则匹配
- 表里面数据长这样
>```
>3 wangwu
>4 qianliu
>1 zhangsan_01
>5 zhangsan01
>6 zhangsan02
>7 zang
>8 zhong
>```
- 正则匹配带有数字结尾的名字
>```sql
>select * from zhd_test_01 where name ~ '([a-z]|[A-Z])*[0-9]$'; -- like、similar to 都可以
>```
- 得到结果
>```
>1 zhangsan_01
>5 zhangsan01
>6 zhangsan02
>```
