PostgreSQL学习记录——第四天

view

view是PostgreSQL中非常重要的概念，它可以将多个表的关系组合在一起，并提供一个统一的视图，使得用户可以访问到所需要的数据。

view并不是一个真正的表，只是一个通过查询语句定义的虚拟表，因此，它并不占用磁盘空间，也不存储数据。

可以包含一个表中的所有行也可以是指定的一个或多个表选定行

view可以允许用户实现以下几点

  • 用户或用户组认为更自然的更直观的查找结构数据的方式
  • 限制数据访问，用户只能访问到被允许的优先数据而不是完整的表
  • 汇总数据，将多个表的数据汇总到一个视图中，方便用户查看
  • 简化复杂的查询，通过view可以简化复杂的查询，使得查询更加简单

PG的视图是只读，无法进行任何修改操作的，只能查询。

创建view

基本语法如下：

1
2
3
4
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

可以在SELECT语句中包含多个表，与正常SQL的查询语句一样。

导入表的操作和前文一样

然后进行如下操作

1
2
3
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;
1
SELECT * FROM COMPANY_VIEW;

最后结果如下

1
2
3
4
5
6
7
8
9
10
id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

删除view

1
DROP VIEW view_name;

事务

终于来到了面试必考环节之一（真的被ACID弄得很烦了，时不时就会被拉出来鞭尸）

基本概念这里就不说了，和MySQL一样。

只说几点不同的

  • PG完全支持ACID，而MySQL只支持部分ACID特性
  • PG的事务默认是关闭自动提交，而MySQL默认是开启自动提交
  • PG的事务支持回滚，而MySQL不支持
  • PG的隔离等级默认为提交读，而MySQL的隔离等级默认为可重复读
  • PG的锁机制更加细致，而MySQL的锁机制更加粗糙

事务的控制是通过下面几个命令完成的

BEGIN TRANSACTION开始事务

COMMIT提交事务

ROLLBACK回滚事务

事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用，他们不能在创建表或删除表时使用。

BEGIN TRANSACTION

事务可以通过命令开始事务并持续下去，直到遇到下一个COMMIT或ROLLBACK命令。

下面是一个简单的语法

1
2
3
4
5
BEGIN;

或者

BEGIN TRANSACTION;

COMMIT

将事务更改的内容提交到数据库。

1
2
3
4
5
COMMIT;

或者

END TRANSACTION;

ROLLBACK

撤销还 未保存 的事务，即回滚

1
ROLLBACK;

然后是例子，还是用前面的表

1
2
3
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
1
2
3
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

锁

关于这部分我都不想说了，S锁X锁，不是考锁和隔离级别的关系就是考锁的类型不然就是考死锁。还有悲观锁乐观锁，这个就遇到过一次，不过看面经好像考察次数确实不是很多。

概念部分太熟悉了直接跳过

基本语法如下

1
2
3
4
LOCK [ TABLE ]
name
 IN
lock_mode

name是当前要锁定表的名称，如果在表名前指定就是锁当前表，如果为指定则锁表及其子表

lock_name锁定模式指定和哪个锁冲突，不指定的话会使用限制最大的访问独占模式。

下面是PG支持的锁定模式

  1. ACCESS SHARE (AccessShareLock)
    • 用于SELECT查询，防止表被删除或结构被修改。
    • 仅与ACCESS EXCLUSIVE冲突。
  2. ROW SHARE (RowShareLock)
    • 用于SELECT … FOR UPDATE和SELECT … FOR SHARE，允许读取和共享行级锁。
    • 与EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  3. ROW EXCLUSIVE (RowExclusiveLock)
    • 用于INSERT、UPDATE、DELETE操作，允许行级修改。
    • 与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  4. SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
    • 用于VACUUM操作，允许更新但不允许删除。
    • 与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、5 EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  5. SHARE (ShareLock)
    • 用于CREATE INDEX操作，允许共享读取但不允许写入。
    • 与ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  6. SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)
    • 用于某些DDL操作，允许共享读取但限制写入。
    • 与ROW EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  7. EXCLUSIVE (ExclusiveLock)
    • 用于某些DDL操作，允许独占访问。
    • 与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE冲突。
  8. ACCESS EXCLUSIVE (AccessExclusiveLock)
    • 用于ALTER TABLE、DROP TABLE等操作，完全独占访问。
    • 与所有其他锁定模式冲突。

PG中LOCK语句只在事务模式下才能使用

1
2
BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

语句的结果是

1
LOCK TABLE

子查询

基本一样，不再多说

权限

除了root用户，其他用户在创建数据库、表、视图等对象时，需要有相应的权限。

权限的控制是通过GRANT和REVOKE命令完成的。

GRANT

1
2
3
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege是权限类型，包括SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE等。

object是要授予权限的对象，包括数据库、表、视图等。

PUBLIC是所有用户的公共权限，GROUP是用户组的权限，username是具体的用户名。

REVOKE

1
2
3
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP group | username }

REVOKE命令的语法和GRANT命令的语法基本相同，只不过是将GRANT命令变成了REVOKE命令。

好咯，到这里基本的和稍微进阶一点的操作就都学完了。接下来准备开始学习GO语言和前端三件套了💖。

学习记录 > PostgreSQL
#学习记录 #PostgreSQL
PostgreSQL学习记录——第四天
https://www.lx02918.ltd/2024/08/07/postgresql-study-fourth-day/
作者
Seth
发布于
2024年8月7日
许可协议