PgSql进阶

2021-08-19 本文已影响0人 codeduck1

一，约束（唯一，非空，主键，外键，检查，排他约束等等）

# CHECK 约束
CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

# EXCLUSION 约束
# EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。
# USING gist 是用于构建和执行的索引一种类型。
CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);

# 删除约束
ALTER TABLE table_name DROP CONSTRAINT some_name;

二，触发器

PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

下面是关于 PostgreSQL 触发器几个比较重要的点：

PostgreSQL 触发器可以在下面几种情况下触发：
    在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
    在执行操作之后（在检查约束并插入、更新或删除完成之后）。
    更新操作（在对一个视图进行插入、更新、删除时）。

触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

准备两张表company和audit，company插入数据时，也会往审计表audit中插入审计数据

# 创建company和audit表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

# 创建PostgreSQL的一个函数auditlogfunc()
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

# 创建触发器
CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

# 查询所有触发器
SELECT * FROM pg_trigger;

# 查询特定表的触发器
SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

# 删除触发器
drop trigger example_trigger on company;

三，索引

索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。

# 单列索引
CREATE INDEX index_name
ON table_name (column_name);

# 组合索引
CREATE INDEX index_name
ON table_name (column1_name, column2_name);

# 唯一索引
CREATE UNIQUE INDEX index_name
on table_name (column_name);

# 局部索引
CREATE INDEX index_name
on table_name (conditional_expression);

# 隐式索引
# 隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

# 删除索引
DROP INDEX index_name;

四，视图

View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。
PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

# 创建视图
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE FROM COMPANY;

# 查询视图
SELECT * FROM COMPANY_VIEW;

# 删除视图
DROP VIEW COMPANY_VIEW;

五，自增 SERIAL

CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

六，事务 ACID

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

# 开始事务(增删改默认自动提交事务，begin手动开启事务)
BEGIN;
BEGIN TRANSACTION;

# 提交事务
COMMIT;
END TRANSACTION;

# 回滚事务
ROLLBACK;

# 手动回滚示例
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

# 手动提交示例
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

七，PgSql锁

数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

# 语法：
LOCK [ TABLE ]
name
 IN
lock_mode

# 示例：
# LOCK 语句只在事务模式下工作。
BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;
# 上面的消息指示表被锁定，直到事务结束，并且要完成事务，您必须回滚或提交事务。

八，权限

在 PostgreSQL 中，权限分为以下几种：
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE

# 示例：
# 创建一个用户runoob
CREATE USER runoob WITH PASSWORD 'password';

# 授予COMPANY表的权限
GRANT ALL ON COMPANY TO runoob;

# 撤销COMPANY表的权限
REVOKE ALL ON COMPANY FROM runoob;

# 删除用户runoob
DROP USER runoob;