PostgreSQL基础(三)

PostgreSQL基础（三）

PostgreSQL语法

视图

视图与模式的区别：

• 模式包含了所有物理表（对应模式中的），模式可以划分表的作用域，这样用户与用户之间就不会冲突。表中的数据是完整的。
• 视图是外模式，它是一个伪表（表中所有行还是存储在原来的表中），使得用户只能访问指定的一个或多个表中的一个或多个行。表中的数据不是完整的。

使用视图的优点：

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

创建一个视图

语法：

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

示例：

testbase=# create view myview as
testbase-# select id, name from company;
CREATE VIEW
testbase=# select * from myview;
 id | name
----+------
  2 | yyy
  3 | zzz
  4 | aaa
  5 | bbb
  6 | qqq
  1 | ccc
(6 行记录)

删除视图

语法：DROP VIEW view_name;

存储过程

语法：

CREATE [or REPLACE] FUNCTION function_name ([arguments])
[RETURNS return_datatype]
AS $variable_name$
	DECLARE
		variable_name variable_datatype;
	BEGIN
		function body;
		RETURN variable_names;
	END;
$variable_name$
LANGUAGE plpgsql;

• function_name：函数名
• arguments：形参列表
• DECLARE：定义变量
• function body：函数体
• RETURN：要返回的变量
• plpgsql：实现该函数所用的语言

示例：

testbase=# CREATE FUNCTION totalcompany() -- 函数名
testbase-# RETURNS INTEGER AS $total$ -- 定义返回值类型（返回类型 返回的谁）
testbase$# DECLARE -- 声明变量
testbase$#      total INTEGER;
testbase$# BEGIN -- 函数体
testbase$#      SELECT count(*) INTO total FROM company; -- into是赋值
testbase$#      RETURN total; -- 返回
testbase$# END;
testbase$# $total$ LANGUAGE plpgsql; -- 语言选择

CREATE FUNCTION

testbase=# select totalcompany();
 totalcompany
--------------
            6
(1 行记录)

触发器

触发器实际上是一个回调函数，他会在指定的事件发生时自动执行。

1. 触发器的调用时机：
   • 在检查约束并尝试 INSERT UPDATE DELETE 之前。（BRFORE）
   • 在检查约束并尝试 INSERT UPDATE DELETE 之后。（AFTER）
2. FOR EACH ROW 与 FOR EACH STATEMENT
   • FOR EACH ROWS：对于操作修改的每一行都会调用触发器。
   • FOR EACH STATEMENT：不管操作修改的有多少行，只会对开始的一行调用触发器。
3. 删除相关联的表的时候，触发器会自动删除。

创建触发器

步骤：

1. 创建一个触发器函数，其中是触发器执行体。
2. 创建一个触发器，搭载触发器函数到触发表中。

语法：

CREATE TRIGGER trigger_name [BEFORE | AFTER | INSTEAD OF] [INSERT|UPDATE|DELETE|TRUNCATE]
ON table_name [FOR EACH ROW | FOR EACH STATEMENT]
[
    -- Trigger logic goes here....
];

示例：

例子说明：在插入数据到 company 表中的时候，将 company.id 插入到记录表中，并配以插入时间。

company表：

testbase=# \d company
               数据表 "public.company"
  栏位   |     类型      | 校对规则 |  可空的  | 预设
---------+---------------+----------+----------+------
 id      | integer       |          | not null |
 name    | text          |          | not null |
 age     | integer       |          | not null |
 address | character(50) |          |          |
 salary  | real          |          |          |

record表：

testbase=# \d record_company;
                     数据表 "public.record_company"
    栏位     |            类型             | 校对规则 |  可空的  | 预设
-------------+-----------------------------+----------+----------+------
 company_id  | integer                     |          | not null |
 insert_time | timestamp without time zone |          | not null |

创建一个存储过程作为触发器体：(触发器函数)

testbase=# create function record_company_function()
testbase-# returns trigger as $record_table$
testbase$# begin
testbase$# insert into record_company values (new.id, current_timestamp);
testbase$# return new;
testbase$# end;
testbase$# $record_table$ language plpgsql;
CREATE FUNCTION

返回类型是触发器类型的！

创建一个触发器：

testbase=# create trigger record_company_trigger after insert on company
testbase-# for each row execute procedure record_company_function();
CREATE TRIGGER

插入数据查看一下：

testbase=# insert into company values(11, 'xyz', 12);
INSERT 0 1
testbase=# select * from record_company;
 company_id |        insert_time
------------+----------------------------
         11 | 2021-04-13 16:22:56.382418
(1 行记录)

创建一个更新触发器

create function modify_table()
returns trigger as $modify$
begin
	new.modify_time = current_timestamp;
	return new;
end;
$modify$ language plpgsql;

create trigger modify_user after update on "user"
for each row execute procedure modify_table();

insert into "user" values(1, '123', '123');
update "user" tb set username = '234' where id=1;

索引

• 索引使用SELECT查询和WHERE子句加速数据输出，但是会减慢使用INSERT和UPDATE语句输入的数据。
• 您可以在不影响数据的情况下创建或删除索引。
• 可以通过使用CREATE INDEX语句创建索引，指定创建索引的索引名称和表或列名称。
• 还可以创建一个唯一索引，类似于唯一约束，该索引防止列或列的组合上有一个索引重复的项。

语法：CREATE INDEX index_name ON table_name (column1, ..., columnN);

可以对多个字段建立索引！

PostgreSQL中有几种索引类型，如B-tree，Hash，GiST，SP-GiST和GIN等。每种索引类型根据不同的查询使用不同的算法。 默认情况下，CREATE INDEX命令使用B树索引。

示例：

testbase=# create index company_index on company (id);
CREATE INDEX

唯一索引

语法：CREATE UNIQUE INDEX index_name ON table_name (columni);

创建唯一索引以获取数据的完整性并提高性能。它不允许向表中插入重复的值，或者在原来表中有相同记录的列上也不能创建索引。

删除索引

语法：DROP INDEX index_name;

在何时避免使用索引

• 应该避免在小表上使用索引。

• 不要为具有频繁，大批量更新或插入操作的表创建索引。

• 索引不应用于包含大量NULL值的列。

• 不要在经常操作(修改)的列上创建索引。

时间相关函数

其他函数

函数	描述
AGE()	减去参数
CURRENT DATE/TIME()	它指定当前日期和时间。
DATE_PART()	获取子字段(相当于提取)
EXTRACT()	获得子字段
ISFINITE()	测试有限的日期，时间和间隔(非+/-无穷大)
JUSTIFY	调整间隔

函数	描述
age(timestamp, timestamp)	当使用第二个参数的时间戳形式调用时，age()减去参数，产生使用年数和月份的类型为“interval”的“符号”结果。
age(timestamp)	当仅使用时间戳作为参数调用时，age()从current_date(午夜)减去。

返回当前时间

函数	描述
CURRENT_DATE	提供当前日期
CURRENT_TIME	提供带时区的值
CURRENT_TIMESTAMP	提供带时区的值
CURRENT_TIME(precision)	可以选择使用precision参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
CURRENT_TIMESTAMP(precision)	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
LOCALTIME	提供没有时区的值。
LOCALTIMESTAMP	提供没有时区的值。
LOCALTIME(precision)	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
LOCALTIMESTAMP(precision)	可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。

事务

事务遵守ACID原则。

三个命令控制事务：

1. BEGIN：开始事务。
2. COMMIT：提交事务（持久化到数据库）
3. ROLLBACK：回滚事务（不执行，还原表）

事务控制命令仅用于DML命令INSERT，UPDATE和DELETE。 创建表或删除它们时不能使用它们，因为这些操作会在数据库中自动提交。

单独使用COMMIT，直接持久化到数据库。

单独使用ROLLBACK，直接回滚，不同判断！

当COMMIT与ROLLBACK同时出现的时候，可以使得其中的事务要么同时成功，要么同时失败。

事务示例：（要么同时成功，要么同时失败回滚）

testbase=# begin; delete from company where id=3; delete from company where xxx=1; commit; rollback;
BEGIN
DELETE 1
错误:  字段 "xxx" 不存在
第1行delete from company where xxx=1;
                               ^
ROLLBACK
警告:  没有事物在运行中
ROLLBACK
testbase=# select * from company;
 id | name | age | address | salary
----+------+-----+---------+--------
  3 | zzz  |  57 |         |
  4 | aaa  |  57 |         |
  5 | bbb  |  58 |         |
  6 | qqq  |  59 |         |
(4 行记录)

锁

锁或独占锁或写锁阻止用户修改行或整个表。 在UPDATE和DELETE修改的行在事务的持续时间内被自动独占锁定。 这将阻止其他用户更改行，直到事务被提交或回退。

用户必须等待其他用户当他们都尝试修改同一行时。 如果他们修改不同的行，不需要等待。 SELECT查询不必等待 （注意在access exclusive模式中select也需要等待，就这一个）。

数据库自动执行锁定。 然而，在某些情况下，必须手动控制锁定。 手动锁定可以通过使用LOCK命令完成。 它允许指定事务的锁类型和范围。

锁必须在事务中执行，从锁住开始到commit或rollback过程中使用锁，commit或rollback释放锁。

语法：

LOCK TABLE
[ONLY] name
IN
lock_mode MODE;

示例：

ROLLBACK
testbase=# begin;
BEGIN
testbase=# lock table company
testbase-# in
testbase-# access exclusive mode;
LOCK TABLE
testbase=# commit;
COMMIT

可从以下两个图片中看出锁的作用：

锁有表锁与行锁：

表锁：

• 访问共享（ACCESS SHARE） - SELECT 命令可在查询中引用的表上获得该锁。一般规则是所有的查询中只有读表才获取此锁。
• 行共享（ROW SHARE） - SELECT FOR UPDATE 和 SELECT FOR SHARE 命令可在目标表上获得该锁（以及查询中所有引用的表的访问共享锁）。
• 行独占（ROW EXCLUSIVE） - UPDATE、INSERT 和 DELETE 命令在目标表上获得该锁（以及查询中所有引用的表的访问共享锁）。 一般规则是所有修改表的查询获得该锁。
• 共享更新独占（SHARE UPDATE EXCLUSIVE） - VACUUM（不含FULL），ANALYZE，CREATE INDEX CONCURRENTLY，和一些 ALTER TABLE 的命令获得该锁。
• 共享（SHARE） - CREATE INDEX 命令在查询中引用的表上获得该锁。
• 共享行独占（SHARE ROW EXCLUSIVE） - 不被任何命令隐式获取。
• 排他（EXCLUSIVE） - 这个锁模式在事务获得此锁时只允许读取操作并行。它不能由任何命令隐式获取。
• 访问独占（ACCESS EXCLUSIVE） - ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER 和 VACUUM FULL 命令在查询中引用的表上获得该锁。此锁模式是 LOCK 命令的默认模式。

行锁：

• 更新（FOR UPDATE） - 这种模式导致 SELECT 读取的行的更新被锁定。这可以防止它们被其他事务锁定，修改或删除。即尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE 的其他事务将被阻塞。删除一行，更新一些列也可以获得到此种锁模式（目前的列集是指那些具有唯一索引，并且可被用作外键 - 但将来这可能会改变）。
• 无键更新（FOR NO KEY UPDATE） - 这种模式与 FOR UPDATE 相似，但是更弱 - 它不会阻塞SELECT FOR KEY SHARE 锁模式。它通过不获取更新锁的 UPDATE 命令获得。
• 共享（FOR SHARE） - 这种模式与无键更新锁类似，除了它可以获取共享锁（非排他）。一个共享锁阻止其他事务在这些行上进行 UPDATE，DELETE，SELECT FOR UPDATE 或 SELECT FOR NO KEY UPDATE 操作，但并不阻止它们进行 SELECT FOR SHARE 或 SELECT FOR KEY SHARE。
• 键共享（FOR KEY SHARE）- 行为类似于共享，但该锁是较弱的：阻止了 SELECT FOR UPDATE，但不阻止 SELECT FOR NO KEY UPDATE。一个键共享锁阻止其他事务进行 DELETE 或任何更改该键值的 UPDATE，但不妨碍任何其他 UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者SELECT FOR KEY SHARE。