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 并发和隔离

脏读:一个事务可以读取到其他事务未提交的修改;
不可重复读:一个事务读取某个记录后,再次读取该记录时数据发生了改变(被其他事务修改并提交);
幻读:一个事务按照某个条件查询一些数据后,再次执行相同查询时结果的数量发生的改变(另一个事务增加或删除了某些数据并完成提交);
更新丢失:两个事务同时读取某一条记录,分别进行修改提交,就会造成先修改的事务的修改丢失。
在这里插入图片描述文章来源地址https://uudwc.com/A/4EmY3

2. 索引与优化

3. 视图

4. 存储过程

5. 触发器

原文地址:https://blog.csdn.net/weixin_45844810/article/details/131311724

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请联系站长进行投诉反馈,一经查实,立即删除!

h
上一篇 2023年06月28日 06:18
k8s安装recognize “calico.yaml“: no matches for kind “PodDisruptionBudget“ in version “policy/v1“
下一篇 2023年06月28日 06:18