postgres数据库之视图、函数、触发器、别名

一 . 视图 

　　视图是一个基于一个或多个表的数据定义的虚拟表。视图是没有数据的，视图里面的数据都是来自实际的表。

• 视图的作用：

1. 简单化
   看到的就是需要的。视图可以简化用户查询数据时的操作，将经常使用的查询定义成视图，以后不必每次查询都带着一堆查询条件。
2. 安全性
   通过视图和权限控制，让用户只能看到和修改他们有权限操作的数据。
3. 逻辑独立
   视图可以屏蔽真实表结构变化带来的影响。当表的结构发生变化时，我们只需要修改视图的定义即可，避免了修改实际使用数据的程序。

 

 

 

1 . 创建视图 

①、建表方式

 

 

 注意创建名称 ，必须是---current-student2 (前面的表叫student2)

 

 结果 : 

 

②、通过sql语句创建视图

CREATE VIEW current_employees AS  
SELECT NAME, ID, SALARY 
FROM EMPLOYEES;

 

 结果 : 

 

2、select 、 insert 、create 

 ①、select 查询表

 

 

 

 ②、insert 插入数据

 

 

 

3、删除视图

 　　注意 : 这个视图一旦删除，就是永久删除的

①、点击视图右键--删除

②、删除sql语句进行删除

DROP VIEW current_employees;

4、总结视图

# 创建视图
create view v_1 as select c1,c2,c3 from table1;
create view v_2 as select c1,d1 from table1,table2 where table1.t2_id=table2.id;

# 删除视图
drop view v_1;
drop view v_2;

# 查询视图
select * from v_1;
select * from v_2;

 

二 . 函数 

　　PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作

• 字符串函数

　　select ltrim('  str'),rtrim('  str   '),trim('  str  ');#去除空格函数
　　select replace('abcdefbc','bc','xx'); #替换所有bc字符为xx
　　select substring('this is a string',2,5);#从第2个字符开始,取5个字符
　　select left('string',3),right('string',2);#获取字符最左/右边的 n 个字符
　　select position('ef' in 'abcdefg'); #返回字符串abc在后面字符串中开始的位置
　　select char_length('date'); #计算字符串字符个数,多字节字符算一个字符，即一个英文字符和一个中文字符都算作一个字符
　　select length('string');#计算字符串的字节长度,使用utf8编码，一个中文是3个字节
　　select concat('postgre','SQL');#连接字符串函数
　　select concat('-','postgre','SQL');#使用 “-” 作为分隔符连接后面的字符串

• 日期和时间函数

　　select current_timestamp,localtimestamp,now(); #返回当前日期和时间,localtimestamp不带时区
　　select extract(day from timestamp '2015-10-07 22:22:22'); #结果 07
　　select extract(month from timestamp '2015-10-07 22:22:22');#结果 10
　　select extract(year from timestamp '2015-10-07 22:22:22');#结果 2015
　　select extract(quarter from timestamp '2015-10-07 22:22:22');#结果 4，季度
　　select extract(DOY from timestamp '2015-10-07 22:22:22');
　　#DOY=day of year 返回指定日期是一年中的第几天
　　#DOW=day of week 返回指定日期是一周中的第几天，范围为0~6

• 日期和时间运算操作

　　select date '2015-9-28' + integer '10'; 指定日期加上间隔天数 结果(2015-10-08)
　　select date '2015-10-07' + interval '3 hour'; 指定日期加上间隔小时 结果(2015-10-07 03:00:00)
　　select  date '2015-10-07' + time '22:00';结果(2015-10-07 22:00:00)
　　select timestamp '2015-10-07 12:10:00'+interval '10 hours';结果(2015-10-07 22:10:00)
　　select date '2015-10-07' -  date '2015-10-01';#日期相减计算间隔天数

• 数据类型转换函数
  select cast(2345 as char(30));#将2345转换为char字符串
• 数学函数

　　select abs(-2),abs(2);    # abs 绝对值函数
　　select pi();              #取圆周率π的值，默认小数位数6位
　　select mod(7,3),mod(8,5); #mod 取余函数
　　select round(1.333),round(1.5),round(2.36,1); #四舍五入函数,第二个参数为保留小数点后位数
　　select sign(-21),sign(0),sign(21);#符号函数,参数值为负/零/正时，返回结果 -1/0/1

• 加密函数

　　select MD5 ('my pwd');#md5加密函数
　　select encode('str','pwd');#使用pwd加密str字符串
　　select decode(encode('str','pwd'),'pwd')#解密由encode加密过的字符串

• 条件函数

　　select case 2 when 1 then 'one' when 2 then 'two' else 'unknown' end;
　　select case when 1<0 then 'true' else 'false' end;

 

三 . 触发器 

　　PostgreSQL触发器是一组动作或数据库回调函数，它们在指定的表上执行指定的数据库事件(即，INSERT，UPDATE，DELETE或TRUNCATE语句)时自动运行。 触发器用于验证输入数据，执行业务规则，保持审计跟踪等。

触发器的重点知识 : 

• PostgreSQL在以下情况下执行/调用触发器：在尝试操作之前(在检查约束并尝试INSERT，UPDATE或DELETE之前)。或者在操作完成后(在检查约束并且INSERT，UPDATE或DELETE完成后)。或者不是操作(在视图中INSERT，UPDATE或DELETE的情况下)

• 对于操作修改的每一行，都会调用一个标记为FOR EACH ROWS的触发器。 另一方面，标记为FOR EACH STATEMENT的触发器只对任何给定的操作执行一次，而不管它修改多少行。

• 您可以为同一事件定义同一类型的多个触发器，但条件是按名称按字母顺序触发。

• 当与它们相关联的表被删除时，触发器被自动删除。

1 . 创建触发器 

　　CREATE TRIGGER语句用于在PostgreSQL表中创建一个新的触发器。 当表发生特定事件(即INSERT，UPDATE和DELETE)时，它被激活。

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

 　　在这里，event_name可以是INSERT，UPDATE，DELETE和TRUNCATE数据库操作上提到的表table_name。 您可以选择在表名后指定FOR EACH ROW。

CREATE  TRIGGER trigger_name AFTER INSERT ON column_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

PostgreSQL触发器的使用 : 

• 验证输入数据。
• 执行业务规则。
• 为不同文件中新插入的行生成唯一值。
• 写入其他文件以进行审计跟踪。
• 从其他文件查询交叉引用目的。
• 访问系统函数。
• 将数据复制到不同的文件以实现数据一致性。

使用触发器的优点 : 

• 它提高了应用程序的开发速度。 因为数据库存储触发器，所以您不必将触发器操作编码到每个数据库应用程序中。
• 全局执法业务规则。定义触发器一次，然后将其重用于使用数据库的任何应用程序。
• 更容易维护 如果业务策略发生变化，则只需更改相应的触发程序，而不是每个应用程序。
• 提高客户/服务器环境的性能。 所有规则在结果返回之前在服务器中运行。

四 . postgres的别名 

　　PostgreSQL别名(Alias)用于为列或表提供临时名称。您可以使用PostgreSQL别名为列或表创建一个临时名称。通常来说，当您执行自联接时，会创建一个临时表。

SELECT column_name AS alias_name  
FROM table_name  
conditions...  ;

参数说明

• column_name: 它指定要进行别名的列的原始名称。
• alias_name: 它指定分配给列的临时名称。
• table_name：它指定表的名称。
• AS：这是可选的。大多数程序员将在对列名进行别名时指定AS关键字，但在别名表名时不指定。

注意：

• 如果alias_name包含空格，则必须将alias_name包含在引号中。
• 在别名列名时，可以使用空格。 但是使用表名时，使用空格通常不是一个好习惯。
• alias_name仅在SQL语句的范围内有效。