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;
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 行记录)
触发器
触发器实际上是一个回调函数,他会在指定的事件发生时自动执行。
- 触发器的调用时机:
- 在检查约束并尝试
INSERT UPDATE DELETE
之前。(BRFORE
) - 在检查约束并尝试
INSERT UPDATE DELETE
之后。(AFTER
)
- 在检查约束并尝试
FOR EACH ROW
与FOR EACH STATEMENT
FOR EACH ROWS
:对于操作修改的每一行都会调用触发器。FOR EACH STATEMENT
:不管操作修改的有多少行,只会对开始的一行调用触发器。
- 删除相关联的表的时候,触发器会自动删除。
创建触发器
步骤:
- 创建一个触发器函数,其中是触发器执行体。
- 创建一个触发器,搭载触发器函数到触发表中。
语法:
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 行记录)
索引
- 索引使用
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原则。
三个命令控制事务:
BEGIN
:开始事务。COMMIT
:提交事务(持久化到数据库)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
可从以下两个图片中看出锁的作用: