PostgreSQL 高级特性入门篇

PostgreSQL

高级特性

视图

所谓视图，就是一条查询语句的别名。视图建立在表之上，你可以认为视图就是一张新的表。

创建视图的命令如下：

CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

这里的myview就是一个视图。之后你可以对视图，也就是myview进行查询等操作。

SELECT * FROM myview;

 city | temp_lo | temp_hi | prcp | date | location ---------------+---------+---------+------+------------+-----------
 San Francisco | 46 | 48 | 0.25 | 1994-11-27 | (-194,53)
 San Francisco | 10 | 18 | 3 | 2000-03-01 | (-194,53)
(2 行记录)

视图可以封装表的细节，对构建一致接口十分有用。

视图也是一张表，因此在视图上再建视图也是OK的。

---

外键

意义：保持数据的参照完整性。

语法：

CREATE TABLE cities ( city varchar(80) primary key, location point );

CREATE TABLE weather ( city varchar(80) references cities(city), temp_lo int, temp_hi int, prcp real, date date );

在尝试之前，别忘了先用DROP命令删除原来的表，否则会报表已存在的错误。

这，这就完了？？

这里只是入个门。花开两朵，后边再说。客官莫要惊慌。

---

事务

事务就是一组操作，它们要么一起成功，要么一起失败。你可以把事务看作是同生共死的兄弟。

事务，也就是这一组操作之间的状态对其他并发事务是不可见的，意味着事务是不能并发的，是原子的。如果事务被打断，那么这个事务的所有操作都不会生效，也不会影响数据库。

举个栗子：

假设现在我要通过银行向你转100块钱。虽然我只在手机上点了一下转账，但是银行那帮哥们儿可要忙了。它们要去修改数据库。首先把数据库中我的账户余额减去100，然后把你的账户余额加上100。就是下面这两条SQL语句：

UPDATE accounts SET balance = balance - 100.00 WHERE name = '我';
UPDATE accounts SET balance = balance + 100.00 WHERE name = '你';

如果银行那帮哥们儿正改着数据库，突然机器故障了。导致我这边余额减少了100，你那边余额没有增加。反正我已经转账了，你没收到那不关我事，那你肯定不乐意了。

再如果执行第一条语句是机器就故障了，执行第二条语句机器又好了。我这边钱没少，你那边多了100块，我俩是高兴了，可银行不乐意了。银行赔了钱说不定还会报警把你抓起来，说你是黑客。

所以，这一类的操作有个特点：要么一起成功，要么一起失败。而事务就是用来保证这一点的。

现在还有一个问题。数据库操作是成功了，突然断电了，内存中的数据有消失了，刚转过去的钱又回来了。我肯定不能再转一遍，银行也不可能补给你，那你只能凉凉了。

所以支持事务的数据库还应该保证：事务在报告完成之前，所有的更新都要持久化。

假如说现在银行做活动，凡是余额不少于100元的客户都会随机送出一份礼品。现在你的账上没有钱，等着我给你转账。如果在我给你转账的途中，银行查看了你的账户，发现没有钱。那你的礼品也就泡汤了，那你铁定是不乐意啊。

所以事务的第三个特点是原子性。在事务的操作结束之前，它所操作的数据对其他并发事务是不可见的，也就是说别人不能访问。

废话说完了，咱们战场上见功夫。

pgsql用BEGIN和COMMIT包裹SQL语句来定义事务。

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = '我';
UPDATE accounts SET balance = balance + 100.00 WHERE name = '你';
COMMIT;

BEGIN和COMMIT之间的SQL语句并不是立刻执行，而是会先入队，等待输入了COMMIT回车之后，这些SQL语句才会依次执行。

BEGIN和COMMIT之间的这一组SQL语句就叫做事务块。

如果想放弃事务的执行，就输入ROLLBACK而不是COMMIT。ROLLBACK和BEGIN之间的所有SQL语句都不会执行。

pgsql实际上将每条SQL语句都当做了一个事务来处理。即便不显示的使用BEGIN和COMMIT，每条SQL语句都会隐式的包含在BEGIN和COMMIT（如果成功的话，语句执行失败当然是ROLLBACK啦）之间。

SAVEPOINT&ROLLBACK TO

通过SAVEPOINT和ROLLBACK TO是对事务块更细粒度的控制。可以部分的丢弃事务块，而不是整个丢弃或全部执行。

一个SAVEPOINT定义了一个回滚点，例如：

SAVEPOINT my_savepoint;

my_savepoint是回滚点的名字，回滚点的名字自己随便取。

一个ROLLBACK TO回滚到指定的回滚点，如下：

ROLLBACK TO my_savepoint;

几点说明：

1. 当碰到ROLLBACK TO命令时，事务回滚到回滚点，ROLLBACK TO 和SAVEPOINT之间的SQL语句都将被抛弃。

2. 以经保存的数据不能回滚。

3. 一个回滚点可以被回滚多次。也就是说当通过ROLLBACK TO回滚到某个回滚点后，它依然有效。该ROLLBACK TO语句之后依然可以通过ROLLBACK TO语句回滚到该回滚点。

4. 回滚点耗费资源，不需要时应该及时释放。

5. 释放或回滚到某个回滚点后，那么它之后定义的所有回滚点都会被自动释放。

6. 回滚动作只在事务块内可见，对外不可见。

再举个例子：

我本来是向你转账，结果银行那帮哥们儿错把钱转到你女朋友账上了。正所谓羊入虎口，钱一旦到了你女朋友账上再想要回来就好比是与虎谋皮。于是你赶紧给银行打电话，说哥们你们搞错了，钱应该转到我账户上。银行那帮哥们儿这才幡然醒悟，于是一顿操作猛如虎，总算是把钱转到你的账上了，好在虚惊一场。

这如虎般的操作就是回滚操作。

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = '我';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = '你女朋友';
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = '你';
COMMIT;

说了这么多，本节也该结束了。本节所说的例子都是瞎掰的，不可尽信。倒不是我不可能给你转钱，主要是你不可能有女朋友。

---

窗口函数

窗口函数和前面讲的聚合函数时一样的。但是窗口函数要更好用，聚合函数只有一行输出，而窗口函数可以有任意行输出。

例子

avg

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
                               ———————————————————————————————————————

depname    | empno | salary | avg 
-----------+-------+--------+-----------------------
 develop   | 11 | 5200 | 5020.0000000000000000
 develop   | 7 | 4200 | 5020.0000000000000000
 develop   | 9 | 4500 | 5020.0000000000000000
 develop   | 8 | 6000 | 5020.0000000000000000
 develop   | 10 | 5200 | 5020.0000000000000000
 personnel | 5 | 3500 | 3700.0000000000000000
 personnel | 2 | 3900 | 3700.0000000000000000
 sales     | 3 | 4800 | 4866.6666666666666667
 sales     | 1 | 5000 | 4866.6666666666666667
 sales     | 4 | 4800 | 4866.6666666666666667
(10 rows)

上面的SELECT语句中画横线的部分对应着输出结果中的第四列。从结果也能看出来，求的是和当前行有相同depname的所有行的salary的平均值。也就是同一个部门的平均工资。

实际上这里的avg就是聚合函数，只是后面紧跟的OVER子句让它变成了窗口函数。PARTITION BY是按某列进行分组，这里是按depname分组，所以结果就是计算同一个部门的平均工资。

1. 窗口函数之后总是紧跟一个OVER子句，这正是它与普通函数和非窗口的聚合函数的区别；

2. OVER子句决定如何分割表中的行，也就是窗口函数要处理的数据集，称为窗口；

3. PARTITION BY子句将所有行进行分组，所有与PARTITION BY后跟的表达式有相同值的行都被分到同一组；

4. 窗口函数的输入就是和当前行分到同一组的所有行。

rank

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   | 8 | 6000 | 1
 develop   | 10 | 5200 | 2
 develop   | 11 | 5200 | 2
 develop   | 9 | 4500 | 4
 develop   | 7 | 4200 | 5
 personnel | 2 | 3900 | 1
 personnel | 5 | 3500 | 2
 sales     | 1 | 5000 | 1
 sales     | 4 | 4800 | 2
 sales     | 3 | 4800 | 2
(10 rows)

1. rank函数用来计算排名。

2. OVER表示rank是窗口函数。

3. PARTITION BY depname将行按depname分组，在每个分组内用rank函数计算排名。

4. ORDER BY salary DESC将分组内的行按salary从高到低（倒序）排序。

5. 正是有ORDER BY字句的存在，所以rank函数才没有带入任何参数，排名规则有ORDER BY代劳了。

6. 注意工资相同的行的排名也是一样的，但是相同行的数量却会累加到下一个不同的行。这就是5200的工资排名都是2，4500的工资排名却跳到4的原因。第二个工资为5200的行隐含了排名3。

数据来源

前面曾说聚合函数的输入是经过WHERE筛选过的。窗口函数就更狠了，它的数据来源要经过WHERE，GROUP BY，HAVING三重过滤。

一个查询可以使用多个窗口函数操作由不同的OVER字句切分的数据。

窗口框架

对于每一行，所有与该行在同一个分组内的行的集合，称为该行的窗口框架。

有些函数只作用于窗口框架，而不是整个分组。

使用ORDER BY时，窗口框架包括从开始行到当前行之间的所有行，以及其后和当前行的值（ORDER BY指定的列的值）相同的所有行。这时窗口框架包含的行集合是分组包含的行集合的子集。

不使用OREDER BY默认窗口框架包括分组内的所有行。这时窗口框架和分组就是一样的。

例如：

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary | sum 
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

OVER子句也可以是空。没有了ORDER BY意味着窗口框架就是整个分组；没有了PARTITION BY意味着分组就是整张表（因为没有WHERE子句等进行筛选）。

所以结果中的sum都是一样的，是所有工资的和。

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary | sum 
--------+-------
   3500 | 3500
   3900 | 7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

现在有了ORDER BY情况就大不一样的。首先分组还是整张表，但是窗口框架变成了第一行至当前行之间的行。每次求和都是当前行到第一行之间的工资的和，所以每次计算sum都不一样。

需要注意的是：工资相同的行，它们属于同一个窗口框架，所以求和时它们都会参与计算。

窗口函数何时执行

窗口函数的执行在WHERE、GROUP BY、HAVING以及聚合函数之后。这意味着两点：

1. 窗口函数不能用于WHERE、GROUP BY以及HAVING子句中；
2. 聚合函数可以作为窗口函数的参数，反之不行。

事实上，窗口函数只能用于SELECT的查询列表以及ORDER BY子句中。

同聚合函数一样，如果需要在窗口函数执行之后再进行过滤（WHERE）或分组（GROUP BY）操作，必须使用嵌套的SELECT子句。

SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary) AS ss WHERE pos < 3;

上面的查询语句解释如下：

• 外层的SELECT从某张表中查询一些数据，条件是pos字段小于3；

• 内层的子SELECT返回一张表，并重命名为ss，这就是外层SELECT要查的表；

• 内层子SELECT从表empsalary中查询数据，以及计算每条记录的排名并将排名重命名为pos，也就是外层SELECT语句的WHERE子句中的pos。

现在不难知道，上面的查询语句是在查找rank小于3的行。

开篇我就说OVER字句后面的是窗口函数要操作的数据集，叫做窗口。窗口也是可以重命名的，这在多个窗口函数要处理同一个窗口时格外有用，可以使查询语句清晰简练。

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

最后还是来个表情包压压惊。

---

继承

继承是面向对象数据库的概览。那么pgsql是否支持继承呢？

当然是支持的，否则提它做什么呢。

怎么玩呢？只需要在创建表的时候用INHERITS关键字指定从哪个表继承就OK了：

CREATE TABLE cities ( name text, population real, altitude int -- (in ft) );

CREATE TABLE capitals ( state char(2) ) INHERITS (cities);

1. 表capitals从表cities继承。
2. 和C++中的继承一样，表capitals拥有表cities的所有列，但state字段只属于表capitals。
3. pgsql支持多重继承，即一个表可以继承自多个表。
4. 当查询“父表”时，所有“子表”也会被查询。

查询之前我们先向表中插入一些数据：

INSERT INTO cities VALUES('Las Vegas', 100000, 2174);
INSERT INTO cities VALUES('Mariposa', 121000, 1953);
INSERT INTO capitals VALUES('Madison', 2105000, 845);

从cities中查询海拔（altitude)高于500的城市：

SELECT name, altitude FROM cities WHERE altitude > 500;

 name | altitude -----------+----------
 Las Vegas | 2174
 Mariposa | 1953
 Madison | 845
(3 rows)

从结果中可以看到，表capitals中的数据也被查询出来了。这就好比连坐，一人犯罪，凡是有关系的都要入狱盘查问罪。

不管怎么说连坐毕竟太残酷。如果不想问罪于子孙，那么ONLY可以免去连坐之罪。ONLY关键字意味着只访问”父表”，而不包括从该表继承的表。

SELECT name, altitude FROM ONLY cities WHERE altitude > 500;

 name | altitude -----------+----------
 Las Vegas | 2174
 Mariposa | 1953
(2 rows)

可以看到现在capitals表中的数据已经没有了，说明并没有查询这个表。

ONLY关键字还可以用于UPDATE和DELETE语句。