PG 高级特性
外键: 数据插入的完整性检验!
CREATE TABLE cities_two ( city varchar(80) primary key, location point ); create table weather_two ( --外键指向 cities_two city varchar(80) references cities_two(city), temp_lo int, temp_hi int, prcp real, date date ); -- 如果 cities_two 没有数据Berkeley 会报错! INSERT INTO weather_two VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
事务:它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作
begin; updata accounts SET balance = balance - 100.00 where name = 'Alice'; -- 设置保存点 savepoint my_savepoint; update accounts set balance = balance + 100.00 where name = 'Bob'; -- 回退到保存点 rollback TO my_savepoint; update accounts set balance = balance + 100.00 where name = 'Wally'; commit
视图:在查询上创建一个视图,这会给该查询一个名字,我们可以像使用一个普通表一样来使用它:
create view myview AS select city, temp_lo, temp_hi, prcp, date, location from weather, cities where city = name
继承性:表之间可以有继承关系!
create table cities ( name text, population real, altitude int -- (in ft) ); --增加了stute 字段, --capitals的行从它的父亲cities继承了所有列(name、population和altitude) create table capitals ( state char(2) ) inherits (cities); select name, altitude from cities where altitude > 500;