PostgreSQL-关系、CRUD和联接

postgres=# \h SELECT  可以列出select相关命令的信息
postgres=# \? 列出以反斜杠开始的psql特有命令的帮助信息
类似与linux的man或者help操作，对我们应用pgsql命令很有帮助。

SQL管理，关系为表（TABLE），属性为列（COLUMN），元祖为行（ROW）。
CRUD是一个助记符，帮助基于数据管理的基本操作： 创建（CREATE），读取（READ），更新（UPDATE），删除（DELETE）。

PostgreSQL是关系型的数据管理系统，所以需要事先进行设计。要先设计好数据库的表，然后插入符合数据库定义的数据。创建表包括为它命名，定义所有列及其类型，以及定义约束信息。每张表都应该指定唯一的标识符列（主键（PRIMARY KEY）），以标识特定的行。

建表

create table countries(country_code char(2) PRIMARY KEY,country_name text UNIQUE);

插入

insert into countries(country_code,country_name) values('us','United States');

删除

delete from countries where country_code='us';

建外键关联表

create table cities(
    name text NOT NULL,
    postal_code varchar(9) CHECK(postal_code <> ''),
    country_code char(2) REFERENCES countries,
    PRIMARY KEY(country_code,postal_code)
);

向表cities中插入数据的时候，country_code需要参考表countries，country_code必须存在于countries中，这称为保持参照完整性。
注：NULL对cities.country_code有效，因为NULL代表一个值空缺。如果不想允许country_code引用为NULL，可以定义为：country_code char(2) REFERENCES countries NOT NULL。

更新表

update cities set postal_code='12345' where name='Portland';

PostgreSQL能够在读取表时对表进行联接。联接本质上是以某种方式联接两个独立的表，并返回一张结果表。

内联接（inner join）

select cities.*,country_name from cities inner join countries on cities.country_code = countries.country_code;

inner join中的inner不是必需的，可以省略。
查询返回单张表，包含表cities的所有列的值，以及countries表中country_name的值。


创建外键关联表，联接有复合主键的cities表

create table venues(
    venue_id serial PRIMARY KEY,
    name varchar(255),
    street_address text,
    type char(7) CHECK(type in('public','private'))DEFAULT 'public',
    postal_code varchar(9),
    country_code char(2),
    FOREIGN KEY(country_code,postal_code) 
    REFERENCES cities(country_code,postal_code) MATCH FULL
);

venues_id设置为自动递增整数。serial这个标示符类似于MySQL的(AUTO_INCREMENT)。
MATCH FULL是一个约束，确保两个值都存在，或两者均为NULL。


复合联接
联接venues表和cities表需要用到两个外键列。
为了减少输入量，可以在表名后面直接加别民，as可以省略。

select v.venue_id,v.name,c.name from venues v inner join cities c on v.postal_code = c.postal_code and v.country_code = c.country_code;

可以选择指定PostgreSQL在插入后返回一些列，方法是让请求以RETURNING语句结尾。

INSERT INTO venues(name,postal_code,country_code) VALUES('Voodoo Donuts','97205','us') RETURNING venue_id;

无需执行另一个查询，就可以得到新插入的venues_id的值。

外联接（outer join）
外联接是合并两张表的一种方式，不论另一张表中是否存在匹配的列值，第一张表的结果总是必须返回。


创建events表

create table events(
    event_id serial PRIMARY KEY,
    title varchar(255),
    starts date,
    ends date,
    venues_id serial REFERENCES venues,
    country_code char(2) REFERENCES countries
);

select e.title,v.name from events e join venues v on e.venues_id = v.venues_id;

只有列值匹配，inner join才会返回一行。如果有空的events.venues_id，则关联不到任何事情。要查询所有的事件，需要使用外连接。
外联接包括左外联接（LEFT OUTER JOIN，简写LEFT JOIN），右外联接（RIGHT OUTER JOIN，简写RIGHT JOIN）。

还有FULL JOIN，是LEFT和RIGHT的联合；保证能得到每张表中的所有值，列匹配时就会联接。

使用索引快速查找
如果从events表选择title作为Christmas Day的行，需要进行全表扫描。如果没有索引，就必须从磁盘读取每一行，才能知道是否是匹配行。
索引是一个特殊的数据结构，目的是避免执行查询时进行全表扫描。


PostgreSQL自动在主键上创建索引，以主键的列值为索引的键，索引的值则指向磁盘上的一行。采用UNIQUE关键字，是强制在表中一列上创建索引的另一种方式。


可以使用CREATE INDEX命令明确地添加一个哈希索引，其中每个值必须是唯一的。

create index events_title on events using hash(title);
select * from events where event_id=2;

对于操作符为小于/大于/等于这样的匹配查询，可以建立B书索引，比简单的hash更灵活。

create index events_starts on events using btree(starts);

select * from events where starts >= '2014-04-01';

这样对日期范围的查询将可以避免全表扫描。当扫描数百万或者数十亿行时，上述查询性能差异会比较大。

索引--优化选择特定的一组列的数据结构。

B-树--一个很好的标准索引；值存储为一个平衡树数据结构；非常灵活。

postgres=# \di    可以列出数据模式中的所有索引。
postgres=# \i  add_event.sql  可以将这个外部文件导入到当前数据库。

注：当对列创建一个FOREIGN KEY约束时，PostgreSQL将自动在目标列创建索引。需要在进行联接的列上创建索引，以便加快基于外键的表联接。