PostgreSQL笔记 (4)

数据库约束是用于规定表中的数据规则，用于保证数据的准确性和可靠性。
约束不可违反，违反约束会终止行为。
约束可以在创建表的时候用建表语句添加，也可以通过ALTER语句添加。
约束可以是列级或表级，列级仅适用于列，表级适用于全表。

常用的约束有：

NOT NULL 非空约束

UNIQUE 唯一约束

PARIMARY KEY 主键约束

FOREIGN KEY 外键约束

CHECK 保证列的值都复核条件

EXCLUSION 排他约束

NOT NULL 非空约束 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80), --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real, --降水量 date date --日期 ); UNIQUE 唯一约束 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80) UNIQUEN, --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real, --降水量 date date --日期 ); PARIMARY KEY 主键约束 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80), --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real, --降水量 date date --日期 ); FOREIGN KEY 外键约束 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80) reference cities(name), --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real, --降水量 date date --日期 ); CHECK 保证列的值都复核条件 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80) reference cities(name), --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real CHECK(real > 0), --降水量 date date --日期 ); EXCLUSION 排他约束 CREATE TABLE myschema.weather( id int PRIMARY KEY NOT NULL, --主键ID city varchar(80) reference cities(name), --城市 temp_lo int, --最低气温 temp_hi int, --最高气温 prcp real, --降水量 date date, --日期 EXCLUDE USING gist --排他约束（USING gist是用于构建和执行的一种索引类型） (city WITH <>, --城市不同，日期相同，否则数据插入失败 date WITH =) ); 2.JOIN表连接

常用的表连接有：

INNER JOIN 内连接

LEFT JOIN 左外连接

RIGHT JOIN 右外连接

FULL JOIN全连接

CROSS JOIN交叉连接

自连接

//内连接 SELECT * FROM weather,cities WHERE weather.city=cities.name; 或 SELECT * FROM weather INNER JOIN cities ON (weather.city=cities.name); //左外连接 SELECT * FROM weather LEFT JOIN cities ON (weather.city=cities.name); //右外连接 SELECT * FROM weather RIGHT JOIN cities ON (weather.city=cities.name); //自连接 SELECT * FROM weather w1,weather w2 WHERE w1.temp_lo > w2.temp_lo AND w1.temp_hi < w2.temp_hi; //交叉连接 SELECT * FROM weahter student s CROSS JOIN result r WHERE s.student_id=r.student_id; //A表的每一行匹配B表的每一行，即A*B行

使用CROSS JOIN交叉查询的时候需要格外注意，因为当量表数据量都较大的时候，可能会产生一个非常庞大的结果表（交叉查询A表和B表，其结果表为A*B的数据量）

3.UNION

UNION有两种使用方式：

UNION 不包含重复的数据

UNION ALL 包含重复的数据

//UNION SELECT * FROM weather201906 where city='ShangHai' UNION SELECT * FROM weather202006 where city='ShangHai' //UNION ALL SELECT * FROM weather201906 where city='ShangHai' UNION ALL SELECT * FROM weather202006 where city='ShangHai' 4.AS别名

AS别名过于简单，不再赘述

5.触发器

触发器是数据库的回调函数，可以在指定的数据库事件发生时，自动执行和调用。
触发器的关键点：

触发器的触发情况：

在执行操作之前(在检查约束并进行增删改之前)

在执行操作之后(在检查约束并进行增删改之后)

更新操作（对一个视图进行增删改时）

触发器的FOR EACH ROW属性是可选的，若选中，则操作修改时每行都调用一次，若是选中FOR EACH STATEMENT，则无论修改多少次，都只执行一次

WHEN子句和触发器操作在引用NEW.column-name和OLD.column-name单标插入、删除和更新的时候，可以访问每一行元素。column-name是与触发器关联的表中的列名称

若存在WHEN子句，则触发器只会执行WHEN条件成立的那一行，若没有WHEN子句，则每一行都会执行

BEFORE和AFTER关键字决定何时执行触发器操作

要修改的表必须都存在同一个库中，作为触发器被附加的表或视图，必须使用tablename，而不能是database.tablename