PostGreSQL进阶

周练

已于 2023-03-06 17:17:09 修改

阅读量898

点赞数 5

分类专栏：数据库文章标签：数据库 postgresql

于 2023-03-06 17:10:08 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_49010109/article/details/129366471

版权

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了数据库中的视图概念，作为虚拟表提供数据的便捷访问和安全限制。接着讨论了函数和存储过程，它们是一组SQL语句的集合，用于执行复杂操作。事务管理确保数据库操作的原子性、一致性和隔离性，保证数据完整性。最后，讲解了窗口函数的应用，如row_number(),rank()和dense_rank()，在数据分组和排序中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

进阶用法总结

1.视图

视图是什么？可以把视图看为一个虚拟表，更加方便用户做如下操作：

它以自然和直观的方式构建数据，并使其易于查找。
它限制对数据的访问，使得用户只能看到有限的数据而不是完整的数据。
它归总来自各种表中的数据以生成报告。

语法
CREATE [TEMP | TEMPORARY] VIEW view_name AS 
SELECT column1, column2..... 
FROM table_name 
WHERE [condition];

例：
create view User as
select id, name, age
from user_table;

注意：对表的操作一般都能够应用在视图里。

2.函数

存储过程其实就是函数，函数就是存储过程，由一组 sql 语句组成，实现比较复杂的数据库操作；

存储过程是存储在数据库服务器上的，用户可以像调用 sql 自带函数一样调用存储过程

语法：
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;

参数说明：

function_name：函数名称

[OR REPLACE]：是可选的，它允许您修改/替换现有函数。

RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。return 表示指定返回的变量；returns表示指定返回的数据类型，可以是复合类型。

function_body：function_body包含可执行部分。

plpgsql：它指定实现该函数的语言的名称。

3.事务

TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：

为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

事务的属性:

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

例如：张三给李四转账100元，首先张三账号减100，其次李四账号加100。在这个过程中确保两个操作都要完成，或者出现错误两个都不能转账成功，即原子性。

UPDATE accounts SET balance = balance - 100.00 WHERE name = '张三';
UPDATE accounts SET balance = balance + 100.00 WHERE name = '李四';

pgsql使用BEGIN和COMMIT包裹SQL语句来定义事务。

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = '张三';
UPDATE accounts SET balance = balance + 100.00 WHERE name = '李四';
COMMIT;

BEGIN;--事务开始或者使用 BEGIN TRANSACTION；
ROLLBACK;--事务回滚
COMMIT;--事务确认，或者可以使用END TRANSACTION

SAVEPOINT & ROLLBACK TO

通过SAVEPOINT & ROLLBACK TO对事务进行更细粒度的控制。可以丢弃部分的事务块。

例：
begin;
select * from accounts;

id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|

insert into accounts(user_name, balance) values ('UserC', 2000);

savepoint sv1;--定义事务保存点

insert into accounts(user_name, balance) values ('UserD', 0);

rollback to sv1;--回滚保存点里的内容

commit;

select * from accounts;
id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|
 4|UserC    |2000.0000|

3 row(s) fetched.

回滚点注意事项：

当碰到ROLLBACK TO命令时，事务回滚到回滚点，ROLLBACK TO 和SAVEPOINT之间的SQL语句都将被抛弃。
以经保存的数据不能回滚。
一个回滚点可以被回滚多次。也就是说当通过ROLLBACK TO回滚到某个回滚点后，它依然有效。该ROLLBACK TO语句之后依然可以通过ROLLBACK TO语句回滚到该回滚点。
回滚点耗费资源，不需要时应该及时释放。
释放或回滚到某个回滚点后，那么它之后定义的所有回滚点都会被自动释放。
回滚动作只在事务块内可见，对外不可见。

4.窗口函数

语法
<窗口函数>--此处放置窗口函数
over（partition by 分组列 order by 排序列）--over() 是开窗函数的关键词
order by 并非必要

窗口函数分类：

聚合类：sum() avg() max() min() count()
内置函数：rank() dense_rank() row_number()等等

聚合类和平时用法基本一致,如下:

select *,max(core) over(partition by kmmc,dept order by core desc) as rk  from public.student s ；

name |dept        |kmmc   |core|rk|
---- +------------+-------+----+--+
张三  |软件工程-一班|大数据   |  88|88|
华为  |软件工程-一班|大数据   |  65|88|
王五  |软件工程-二班|计算机网络|  95|95|
小米  |软件工程-二班|计算机网络|  87|95|
李四  |软件工程-二班|计算机网络|  60|95|
张三  |软件工程-一班|计算机网络|  80|80|
华为  |软件工程-一班|计算机网络|  77|80|
华为  |软件工程-一班|计算机网络|  77|80|
老六  |软件工程-一班|计算机网络|  71|80|

row_number()用法是通过kmmc（科目名称）、dept（班级）分组，core（分数）逆序的规则形成rk列，rk列是连续不重复。

select *,row_number() over(partition by kmmc,dept order by core desc) as rk  
from public.student ；

name |dept       |kmmc    |core|rk|
-----+-----------+--------+----+--+
张三  |软件工程-一班|大数据   |  88| 1|
华为  |软件工程-一班|大数据   |  65| 2|
王五  |软件工程-二班|计算机网络|  95| 1|
小米  |软件工程-二班|计算机网络|  87| 2|
李四  |软件工程-二班|计算机网络|  60| 3|
张三  |软件工程-一班|计算机网络|  80| 1|
华为  |软件工程-一班|计算机网络|  77| 2|
华为  |软件工程-一班|计算机网络|  77| 3|
老六  |软件工程-一班|计算机网络|  71| 4|

select * from
(
select *,row_number() over(partition by kmmc,dept order by core desc) as rk  
from public.student 
)s where rk=1；

name |dept       |kmmc    |core|rk|
-----+-----------+--------+----+--+
张三  |软件工程-一班|大数据   |  88| 1|
王五  |软件工程-二班|计算机网络|  95| 1|
张三  |软件工程-一班|计算机网络|  80| 1|

rank()是通过kmmc（科目名称）、dept（班级）分组以及对core（分数）逆序的规则形成rk列，k列是非连续重复。

select *,rank() over(partition by kmmc,dept order by core desc) as rk  from public.student s ；

name |dept       |kmmc    |core|rk|
-----+-----------+--------+----+--+
张三  |软件工程-一班|大数据   |  88| 1|
华为  |软件工程-一班|大数据   |  65| 2|
王五  |软件工程-二班|计算机网络|  95| 1|
小米  |软件工程-二班|计算机网络|  87| 2|
李四  |软件工程-二班|计算机网络|  60| 3|
张三  |软件工程-一班|计算机网络|  80| 1|
华为  |软件工程-一班|计算机网络|  77| 2|
华为  |软件工程-一班|计算机网络|  77| 2|
老六  |软件工程-一班|计算机网络|  71| 4|

dense_rank()是通过kmmc（科目名称）、dept（班级）分组以及对core（分数）逆序的规则形成rk列，k列是连续重复。

select *,dense_rank() over(partition by kmmc,dept order by core desc) as rk  from public.student s ;

name |dept       |kmmc    |core|rk|
---- +-----------+--------+----+--+
张三  |软件工程-一班|大数据   |  88| 1|
华为  |软件工程-一班|大数据   |  65| 2|
王五  |软件工程-二班|计算机网络|  95| 1|
小米  |软件工程-二班|计算机网络|  87| 2|
李四  |软件工程-二班|计算机网络|  60| 3|
张三  |软件工程-一班|计算机网络|  80| 1|
华为  |软件工程-一班|计算机网络|  77| 2|
华为  |软件工程-一班|计算机网络|  77| 2|
老六  |软件工程-一班|计算机网络|  71| 3|

总结：开窗函数多用来：当一个字段是唯一的时候，但是在明细表里面有重复，这个时候使用开窗函数来去除重复值。而row_number()、rank()、dense_rank()这三着的区别可以看上方演示和如下总结：