PostgreSQL

一. 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

三. SQL语言

1. DDL

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;

4. 数据类型

四. 高级特性

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. 视图

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 sum (a integer, b integer)
returns INTEGER
language plpgsql
as $$
declare 
	total integer;
BEGIN
	total = a + b;
	return total;
end;
$$

select sum(1,2)

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()