快速入门PostgresSQL

写的不是代码

已于 2022-05-02 12:45:00 修改

阅读量2.5k

点赞数 1

分类专栏：数据库文章标签：数据仓库数据库开发 dba 数据库架构

于 2022-04-25 17:47:40 首次发布

本文链接：https://blog.csdn.net/Melo0705/article/details/124409175

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一：访问数据

1： psql的使用

psql --help 查看命令行使用帮助

eg: psql连接数据库例子：

psql -h 127.0.0.1 -p 12345 -U postgres -d postgres

2: 使用select语句

(1)查看select 语法：

postgres=# \h select

(2) select 常用语法介绍

[1] 别名

postgres=# select 'nihao' as col_alias ----列别名

from table_name as t ---源别名

limit 1;

[2] 字段、函数、表达式、子查询

postgres=# select relname, ---列名

now(), -----函数

upper(relname)||'__digoal', ----表达式

（select 1+2+3）---子查询

from pg_classs limit 2;

[3] 表、视图、物化视图、函数、表达式、子查询

select * from 表；

select * from 视图；

select * from 物化视图；

select * from 函数(参数)；

select * from 函数(参数) as t(列1 类型1，... , 列n 类型n);

select 表达式；

select (子查询) as t;

[4] select where条件、排序、随机排序、分组、限制输出、位移

select relname, reltuples from pg_class where relname = 'pg_class';

select oid, relname from pg_class order by oid limit 1;

select oid, relname from pg_class order by random() limit 1;

select relkind, count(*) from pg_class group y relkind;

select relkind, count(*) from pg_class group y relkind limit 1;

select relkind, count(*) from pg_class group y relkind order by relkind offset 1 limit 1;

3：使用游标

[1] 创建游标、fetch游标、关闭游标

postgres=# begin;

postgres=# declare cur1 cursor for select oid, relname, relkind from pg_class;

postgres=# fetch 2 form cur1;

postgres=# close cur1;

4: 行表达式

postgres=# select * from (values(1, 'test1'), (2, 'test2')) as t(id, info);

5: with

(1) with 语句：
postgres=# with
a as (select * from (values(1,'test1'),(2,'test2'),(3,'test3')) as t(id, info)),
b as (select oid,relname,relkind from pg_class)
select a.*,b.* from a,b where a.id=mod(b.oid::int,3)+1;

6：执行ddl\dml\dcl

[1] 建表

create table tb1(id int, info text, crt_time, timestamp);

[2] 插入（增）

insert into tb1 (id, info, crt_time) values (1, 'test', now());

[3] 批量插入

insert into tb1 (id, info, crt_time) select generate_series(1, 10000), 'test', now(); // 批量插入

insert into tb1 (id, info, crt_time) values (1, 'test', now()), (2, 'test', now()), (3, 'test', now()); // 批量插入

begin;

insert into tb1 (id, info, crt_time) values (1, 'test', now());

insert into tb1 (id, info, crt_time) values (2, 'test', now());

insert into tb1 (id, info, crt_time) values (3, 'test', now());

end; // 批量插入

[4] 更新

update tb1 set info = 'new' where id =1;

[5] 删除

delete from tb1 where id = 1;

truncate(如果要清除全表，建议使用truncate)

[6] drop 表

drop table p;

dorp table p cascade; //drop表时，如果有依赖对象，想一同删除，可以使用cascade关键字

[7] alter table 修改表

alter table tb1 add colcum c1 int; // 添加字段c1，一般我会给默认值alter table tb1 add colcum c1 int default 100;

[8] copy

postgres=# copy tb1 to '/tmp/test.csv' // COPY out

postgres=# copy tb1 from '/tmp/test.csv' // COPY in

7: 选择行

where子句过滤条件

postgres=# select tableoid::regclass, xmin, xmax,cmin,cmax,relname from pg_calss where relname = 'pg_type'; //

8: 使用序列

PostgreSQL允许创建多个序列，每个序列独立自主，有自己的取值空间。序列一旦消耗掉，就无法回退，除非设置它。序列通常用来表示唯一自增值。

postgres=# \h create sequence

[1] 创建序列

postgres=# create sequence seq;

[2] 获取序列

postgres=# select nextval('seq'::regclass);

[3] 读取当前的序列状态

select * from seq;

[4] 读取当前回话，上一次获取的序列值

select * from currval('seq'::regclass);

9: 时间和日期

当前日期: selectcurrent_date;

事务时间: select current_time;

事务timestamp: select now();

语句时间：select clock_timestamp()::time;

select statement_timestamp()::time;

10: 多表协同工作

[1] 子查询

postgres=# select（select * from (values(1),(2)) as t (c1) limit 1), relname, relkind from pg_class;

postgres=# select t.relname from (select * from pg_class limit 1) t， pg_class where t.relname = pg_class.relname;

[2] join

二：高级SQL用法

1：聚集函数

PostgreSQL: Documentation: 9.6: Aggregate Functions

2：子查询

[1] select子查询只能返回一列：

postgres=# select（select * from (values(1),(2), (3)) as t (c1) limit 1), relname, relkind from pg_class;

[2] select子查询只能返回一条记录

postgres=# select t.relname from (select * from pg_class limit 1) t， pg_class where t.relname = pg_class.relname;

3: union all \ union \ except \ intersect

[1] union all 不去重

postgres=# select * from (values (1), (1)) t (id) union all select * from (values (2),(2)) t (id);

[2] union 去重（不包含重复项）

postgres=# select * from (values (1), (1)) t (id) union all select * from (values (2),(2)) t (id);

[3] except 去重（去除相同的项）

postgres=# select * from (values (1), (1), (2)) t (id) except select * from (values (2),(2)) t (id);

[4] intersect 去重（只保留相同的项）

postgres=# select * from (values (1), (1), (2)) t (id) intersept select * from (values (2),(2)) t (id);

4: 自连接

当某个表或者子句的A字段要和B字段进行关联时，可以使用自关联。

postgres=# create table tbl8(c1 int, c2 int, info text);
postgres=# insert into tbl8 select generate_series(1,100), generate_series(2,101),md5(random()::text) ;
postgres=# select t1.* from tbl8 t1, tbl8 t2 where t1.c1=t2.c2 and t2.c2<10 limit 10;

5：内连接（显示join\或者非显示join）

内连接，仅仅输出符合连接条件的记录。

postgres=# select t1.* from tbl_join_1 t1, tbl_join_2 t2 where t1.id=t2.id and t2.id=1;
postgres=# select t1.* from tbl_join_1 t1 join tbl_join_2 t2 on(t1.id=t2.id and t2.id=1);

6: 连接

全面解读PostgreSQL和Greenplum的Hash Join_Greenplum中文社区的博客-CSDN博客

三：数据定义和数据类型

1：数据类型

blog/20170411_05.md at master · digoal/blog · GitHub

2：数据操作

在PostgreSQL中，所有的操作符，索引接口，都是基于函数的，底层都有函数的支撑。

四：事务和锁

1：什么是事务

[1] 事务要满足ACID：

1. 原子性：同一个事务中的所有操作，要么全部成功，要么全部失败。即使数据库恢复，也不能出现同一个事务中对数据库的操作一部分成功一部分失败的情况。

2. 一致性：任何时刻，数据库对用户的视角始终是按事务提交的顺序一致的，即使数据库恢复，也不能出现后提交的事务存在，而先提交的事务不存在的情况。

以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元。

以插入数据为例，a插入1并提交，随后b插入2并提交。当数据库发生恢复时，要么a存在，要么a,b同时存在，绝对不可能出现b存在但是a不存在的情况。

3. 持久化：事务成功提交，表示事务对数据库的修改已经持久化到永久存储，即使断电也不会丢失。除非永久存储的介质损坏。

4. 隔离性：有4种隔离级别，读未提交，读已提交，可重复度，串行化。

a: 读未提交，表示可以读到其他会话未提交的数据。PostgreSQL不支持读未提交（脏读）。

b: 读已提交，表示可以读到其他会话已提交的数据。

c: 可重复读，表示在一个事务中，执行同一条SQL，读到的是同样的数据（即使被读的数据可能已经被其他会话修改并提交）。

d: 串行化，表示并行事务模拟串行执行，违反串行执行规则的事务，将回滚。

2：单用户下事务

单用户情况下的事务，没有并行问题，属于串行事务，仅仅体现一致性、原子性、持久化。

3：多用户下事务

[1] 多用户下如何处理并发事务：

a: 读写不冲突

b: 读到什么数据，取决于事务隔离级别

[2]: PostgreSQL处理事务隔离？

a: 多版本概念

数据插入：在记录的头信息中的xmin字段，记录当前事务ID.

数据删除：在记录的头信息中的xmax字段，记录当前事务ID. 头部informask标记已删除。

更新数据：在被删除的记录的头信息中的xmax字段，记录当前事务ID.头部informask标记已删除。同时插入一条新的记录，在记录的头信息中的xmin字段，记录当前事务ID.

INSERT， xmin = current xid

DELETE， xmax = current xid

UPDATE, old tuple xmax = current id, new tuple xmin = current id

b: 快照

当前数据库中未提交的最小事务xmin，所有小于这个事务号的记录，对用户都可见。

当前数据库中最小的未分配事务号xmax，所有大于这个事务号的记录，对用户都不可见。

在xmin,xmax区间内，仍未提交的事务号list，所有等于这些事务号的记录，对用户都不可见。

查询数据时，根据事务隔离级别、以上快照信息、行头信息中的xmin,xmax值、记录对应的事务提交状态，以及infomask标记信息，判断记录是否可见。

c: HOT

为了降低索引的更新，当更新后的记录在同一个数据块中时，旧的记录使用ctid引用到对应的新记录的offset，如果更新后的记录，索引字段的值未编号，则不需要更新索引。

4：锁

详细的锁的冲突列表

blog/20170412_01.md at master · digoal/blog · GitHub

[1]: 锁对象

源码： src/include/storage/lock.h

[2]: 表级锁

源码： src/include/storage/lockdefs.h

#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6 /* CREATE TRIGGER and many forms of ALTER TABLE, like EXCLUSIVE MODE, but allows ROW SHARE */
#define ExclusiveLock 7 /* REFRESH MATERIALIZED VIEW CONCURRENTLY, blocks ROW SHARE/SELECT...FOR UPDATE */
#define AccessExclusiveLock 8 /* many ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER,
* VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY)
* and unqualified LOCK TABLE */

[3]: 行级锁

blog/20170412_01.md at master · digoal/blog · GitHub

[4]: 用户锁

[5]: 死锁

当发生会话之间相互等待时，出现死锁

[6] 查看锁状态

5：数据恢复粒度

[1] 数据库的时间点恢复PITR

[2] PITR恢复选择时机：

(1): 事务结束时 commit/abort

(2): 或者用户使用pg_create_restore_point()创建还原点

6：数据年龄

[1]: 表的年龄

表的年龄：取决于这张表的最老的记录与当前事务直接经历了多少个事务。

select age(relfrozenxid), relname from pg_class;

7：事务冻结

8：垃圾回收

autovacuum被用于自动的垃圾回收，当表中的垃圾版本超过一定的比例（可配置）后，会自动的触发垃圾回收。

配置postgresql.conf : autovacuum = on; // 开启自动垃圾回收

autovacuum_naptime = 1min; // 垃圾回收线程/进程唤醒时间间隔

autovacuum_vacumm_scale_fator=0.5; //超过垃圾记录占比时，触发垃圾回收机制。

手动垃圾回收： vacumm 表名；

9：flash back query

闪回 blog/20170412_01.md at master · digoal/blog · GitHub

10： analyze

ANALYZE -- 收集与数据库有关的统计信息.

ANALYZE收集数据库中表内容的统计信息，然后把结果保存在系统表pg_statistic里。随后，查询规划器就可以使用这些统计帮助判断查询的最佳规划。

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

参数：

VERBOSE ：显示处理过程的信息。

table_name：要分析的指定表(可以用模式名修饰)的名字。缺省是当前数据库里所有表（不包含外部数据表）。

column_name：要分析的指定字段的名字。缺省是所有字段。

五： SQL实践

1： join

create shcema outerjoin_test;

set search_path = outerjoin_test;

drop table if exists tb1;

drop table if exists tb2;

create table tb1 values (f1 int, f2 int);

create table tb2 values (c1 int, c2 int);

insert into tb1 values(1, 1);

analyze tb1; // 做统计

analyze tb2;

select f1 as join_key , 12345 as my_number from tb1 i1; // int常量12345

select i2.join_key, i2.my_number from (select f1 as join_key, 12345 as my_number from tb1 i1) i2;

select join_key, my_number from (select i2.join_key, i2.my_number from (select f1 as join_key, 12345 as my_number from tb1 i1) i2) i3; // 嵌套子查询

select join_key, my_number from (select i2.join_key, i2.my_number from (select f1 as join_key, 12345 as my_number from tb1 i1) i2) i3 left join (select i2.c1 as join_key from tb2 i4) i5 using(join_key); // 使用子查询做关联的表，使用left join 做 outer join. using 做字段。

select i6.join_key as i6_id, i7.join_key as i7_id, my_number from (values(0), (1)) i6(join_key) left join （select join_key, my_number from (select i2.join_key, i2.my_number from (select f1 as join_key, 12345 as my_number from tb1 i1) i2) i3 left join (select i2.c1 as join_key from tb2 i4) i5 using(join_key)）i7 using(join_key)； // // 使用子查询做表，使用left join 做 outer join. using 做字段。

写的不是代码

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
快速入门PostgresSQL

一：访问数据1： psql的使用psql --help 查看命令行使用帮助eg: psql连接数据库例子：psql -h 127.0.0.1 -p 12345 -U postgres -d postgres2: 使用select语句(1)查看select 语法：postgres=# \h select(2) select 常用语法介绍[1] 别名postgres=# select 'nihao' as col_alias ----列别名...
复制链接

扫一扫