一. PostgreSQL 简介
1 资料来源：
中文手册：http://www.postgres.cn/docs/14/index.html
知乎链接：https://www.zhihu.com/column/c_1452567507496689664
视频链接：https://www.bilibili.com/video/BV1uW4y1m7pD/?spm_id_from=pageDriver&vd_source=372365c1ea72bb6075a0ec1b05671b3a
二. PostgreSQL 安装
1. docker 安装
拉取镜像
docker pull postgres
创建容器
docker run -itd --name postgres -e POSTGRES_PASSWORD=root -p 5432:5432 postgres:15
2. 元命令
\l: 列出所有数据库
\c dbName: 连接到指定数据库
\dn: 查看所有模式
\db: 查看表空间
\d: 显示表结构
\d+: 查看表详情，包括size和comment
\di: 查看索引
\ds: 查看序列
\dv: 查看视图
\df: 查看函数
\dg / \du: 查看所有角色和用户
\dp: 查看表的权限分配
三 数据类型
1. 数值型
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| smallint | int2 | 2字节 | -225~215-1 | 有符号的2字节整数 | MySQL中smallint，Oracle中number(5) |
| integer | int,int4 | 4字节 | -231~231-1 | 有符号的4字节整数 | MySQL中int，Oracle中number(38,0) |
| bigint | int8 | 8字节 | -263~263-1 | 有符号的8字节整数 | MySQL中bigint，Oracle中number(38) |
| real | float4 | 4字节 | 单精度浮点数 | MySQL中float，Oracle中binary_float | |
| double | float8 | 8字节 | 双精度浮点数 | MySQL中double，Oracle中binary_double或number | |
| numeric | decimal [(p, s)] | 可选择精度的精确数字 | MySQL中decimal[(p, s)]，Oracle中NUMBER[(p, s)] | ||
| money | 8字节 | -92233720368547758.08~+92233720368547758.07 | 货币金额 |
2. 自增型
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| smallserial | serial2 | 2字节 | 2字节自增整数 | MySQL中自增字段，Oracle中序列 | |
| serial | serial4 | 4字节 | 4字节自增整数 | MySQL中自增字段，Oracle中序列 | |
| bigserial | serial8 | 8字节 | 8字节自增整数 | MySQL中自增字段，Oracle中序列 |
3. 字符型
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| character (n) | char (n)，bpchar(n) | n字节 | 固定长度字符串，不足会被空格填充 | MySQL中char(n) | |
| character varying (n) | varchar (n) | n字节 | 可变长度字符串 | MySQL中varchar(n)，Oracle中varchar(n) | |
| bpchar | char (n)，bpchar(n) | n字节 | 可变不限制长度字符串，不足字符被空格填充 | ||
| text | 可变长度字符串 | MySQL中text，Oracle中clob |
4. boolean型
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| boolean | bool | 1字节 | true/false | MySQL中boolean，Oracle中number(1) |
5. 日期型
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| timestamp [ § ] [without time zone] | 8字节 | 4713BC~294276AD | 无时区的日期和时间 | ||
| timestamp [ § ] [with time zone] | 8字节 | 4713BC~294276AD | 有时区的日期和时间 | ||
| date | 4字节 | 4713BC~5874897AD | 日期 | ||
| time [ § ] [without time zone] | 8字节 | 00:00:00~24:00:00 | 时间 | ||
| time [ § ] [with time zone] | 12字节 | 00:00:00+1559~24:00:00-1559 | 有时区的时间 | ||
| interval [fields] [§] | 16字节 | -178000000years~178000000years | 时间间隔 |
6. 二进制
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| bytea | 二进制数据 | MySQL中blob，Oracle中blob |
7. 位串
| 名称 | 别名 | 存储 | 范围 | 描述 | 与其他数据库对比 |
|---|---|---|---|---|---|
| bit [ (n) ] | 固定长度位串 | MySQL中bit [ (n) ] | |||
| bit varying [ (n) ] | varbit[ (n) ] | 可变长度位串 | MySQL中bit [ (n) ] |
8. 枚举
create type season as enum('spring', 'summer', 'autumn', 'winter');
create table festival
(
id bigserial,
season season
);
insert into festival(season) values ('spring');
select * from festival;
9. 几何类型
10. 网络地址类型
11. xml
12. json
| 名称 | 描述 |
|---|---|
| json | 文本json数据 |
| jsonb | 重新解析的二进制json数据 |
四. SQL语言
1. DDL
添加注释
comment on table tablename is '注释'
comment on column tablename.column is '注释'
2. DML
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
2.1 insert
- 单行
INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');
- 多行
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
INSERT INTO products (product_no, name, price)
SELECT product_no, name, price FROM new_products
WHERE release_date = 'today';
2.2 update
UPDATE products SET price = 10 WHERE price = 5;
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;
2.3 delete
DELETE FROM products WHERE price = 10;
DELETE FROM products;
2.4 数据合并
insert into public.user(id,username,password) values(15,'root','root')
on conflict (id)
do update set username = excluded.username, password = excluded.password
2.5 CTE和通用表表达式
- 插入
with inserts as
(
insert into employees values(1234,'1','1','1','1','2008-03-08','ST_CLERK',1.00,0.1,'120','90')
returning *
)
insert into employees_his select * from inserts;
- 更新
with updates as
(
update employees
set salary = salary + 500
where salary = 2100
returning *
)
select * into employees_his from updates
with updates as
(
update employees
set salary = salary + 500
where salary = 2100
returning *
)
insert into employees_his select * from updates;
3. DQL
3.1 通用表表达式
with tempA as (
select * from public.goods where id < 4
),
tempB as (
select * from public.goods where id >= 4
)
select * from tempA
union all
select * from tempB
3.2 递归查询语句
with recursive cte(n) as
(
select 1
union ALL
select n + 1 from cte where n < 5
)
select * from cte
3.3 窗口函数
3.3.1 定义
窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理
3.3.2 常用的窗口函数
sum
avg
max
min
count
rank
row_number
3.3.3 格式
window_function ( expr ) over (
partition by …
order by …
frame_clause
)
window_function是窗口名称，expr是函数参数，有些函数不需要参数，
over子句包含：三部分
- 分区：partition by
- 排序：order by
- 窗口大小：frame_clause
3.3.4 示例
select department_id, hire_date, salary, sum(salary) over (partition by department_id order by salary)
from public.employees;
select department_id, hire_date, salary, rank() over (partition by department_id order by salary desc)
from public.employees;
select department_id, hire_date, salary, row_number() over (partition by department_id order by salary desc)
from public.employees;
五. 高级特性
1. 事务与并发控制
1.1 acid特性
原子性
一致性
隔离性
持久性
1.2 事务控制语句
begin;
insert into employees values(1234,'1','1','1','1','2008-03-08','ST_CLERK',1.00,0.1,'120','90');
insert into employees values(1235,'1','1','1','1','2008-03-08','ST_CLERK',1.00,0.1,'120','90');
commit; // rollback
1.3 并发和隔离
脏读：一个事务可以读取到其他事务未提交的修改；
不可重复读：一个事务读取某个记录后，再次读取该记录时数据发生了改变（被其他事务修改并提交）；
幻读：一个事务按照某个条件查询一些数据后，再次执行相同查询时结果的数量发生的改变（另一个事务增加或删除了某些数据并完成提交）；
更新丢失：两个事务同时读取某一条记录，分别进行修改提交，就会造成先修改的事务的修改丢失。
2. 索引与优化
3. 视图
create view view_name as (
query statement;
);
drop view view_name;
4. 存储过程
4.1 语法
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
4.2 示例
create or replace function func_timedb(x int, y int)
returns int as $func_timedb$
begin
return x + y;
end
$func_timedb$ language plpgsql;
select * from func_timedb(1,2)
create or replace procedure func_timedb1(x int, y int, out z int)
language plpgsql as $func_timedb1$
begin
z = x + y;
end;
$func_timedb1$
call func_timedb1(1,2,4)
5. 触发器
5.1 语法
CREATE [ CONSTRAINT ] TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]}
ON table_name
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFEREABLE ] { IINITIALLY IMMEDIATE | INITIALLY DEFERED} }
FOR [ EACH ] { ROW | STATEMENT }
[ WHEN { condition }]
EXECUTE PROCEDURE function_name ( arguments )
5.2 示例
create or replace function track_list_user()
returns trigger
as $$
BEGIN
execute 'drop table public.user_history;';
execute 'create table public.user_history as select * from public.user;';
return new;
end
$$
language plpgsql
create trigger trigger_user after insert on public.user
for statement execute function track_list_user()
六. 备份与恢复
# 备份数据库
pg_dump -U myuser -d mydatabase > backup.sql
# 恢复数据库
psql -U myuser -d mydatabase < backup.sql
七、体系架构
7.1 进程和内存
- Postmaster主进程和服务进程
当用户和PG数据库建立连接时，要先与Postmaster进程建立连接，此时客户端进程会发送身份验证消息给Postmaster主进程，Postmaster主进程根据消息进行身份验证，验证通过后，Postmaster主进程会fork出一个会话服务进程为这个用户连接服务。可以通过pg_stat_activity表来查看服务进程的pid
- BgWriter（后台写）进程
BgWriter进程是把共享内存中的脏页写到磁盘上的进程。它的作用有两个：一是定期把脏数据从内存缓冲区刷出到磁盘中，减少查询时的阻塞；二是PG在定期作检查点时需要把所有脏页写出到磁盘，通过BgWriter预先写出一些脏页，可以减少设置检查点（CheckPoint，数据库恢复技术的一种）时要进行的IO操作，使系统的IO负载趋向平稳
- PgArch（归档）进程
类似于Oracle数据库的ARCH归档进程，不同的是ARCH是吧redo log进行归档，PgArch是把WAL日志进行归档。再深入点，WAL日志会被循环使用，也就是说，过去的WAL日志会被新产生的日志覆盖，PgArch进程就是为了在覆盖前把WAL日志备份出来。归档日志的作用是为了数据库能够使用全量备份和备份后产生的归档日志，从而让数据库回到过去的任一时间点。PG从8.X版本开始提供的PITR（Point-In-Time-Recovery）技术，就是运用的归档日志
- PgStat（统计数据收集）进程
PgStat进程是PostgreSQL数据库的统计信息收集器，用来收集数据库运行期间的统计信息，如表的增删改次数，数据块的个数，索引的变化等等。收集统计信息主要是为了让优化器做出正确的判断，选择最佳的执行计划
- AutoVacuum（自动清理进程）进程
在PG数据库中，对数据进行UPDATE或者DELETE操作后，数据库不会立即删除旧版本的数据，而是标记为删除状态。这是因为PG数据库具有多版本的机制，如果这些旧版本的数据正在被另外的事务打开，那么暂时保留他们是很有必要的。当事务提交后，旧版本的数据已经没有价值了，数据库需要清理垃圾数据腾出空间，而清理工作就是AutoVacuum进程进行的
- WalWriter（预写式日志写）进程
预写式日志WAL（Write Ahead Log，也称为Xlog）的中心思想是对数据文件的修改必须是只能发生在这些修改已经记录到日志之后，也就是先写日志后写数据（日志先行）。使用这种机制可以避免数据频繁的写入磁盘，可以减少磁盘I/O。数据库在宕机重启后可以运用这些WAL日志来恢复数据库
- CheckPoint（检查点）进程
检查点是系统设置的事务序列点，设置检查点保证检查点前的日志信息刷到磁盘中
- SysLog进程
- 共享内存
- 本地内存
扫一扫
260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
