Postgres-SQL语句

这篇文章将基本的SQL语句列一下

SQL语言简介

PostgreSQL是一个关系型数据库管理系统(relational database management system,RDBMS).这表明它是一个使用关系(retional)存储数据的系统

关系是表格(table)在数学层面上的术语.每一个表格都是行(row)的集合.每一个给定表格的行都有着相同名称的列(column),并且每一列都是一个指定的数据类型

多个表格组成了数据库,多个被PostgreSQL服务端管理的数据库则构成了一个叫做集群(cluster)的数据库

创建表格(create)

可以通过指定表格名称,列的名称以及它们的类型来创建一个表格:

CREATE TABLE weather (
    city varchar(80),
    temp_lo int, -- low temperature,最低温度
    temp_hi int, -- high temperature,最高温度
    prcp real, -- precipitation,降水量
    date date
);

两个破折号(--)之后的内容为注释.SQL的关键词和标识符大小写不敏感,除非标识符在双引号中

varchar(80)指定了一个数据类型,它可以存储任意类型的字符串,且长度至多为80个字符
int是一种常见的数值类型
real是一个存储单精度浮点数的类型
date是日期类型

PostgreSQL支持标准SQL类型,例如:int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, and interval.除此之外,它还提供一些其它一些易用的类型

第二个创建表格的示例:

CREATE TABLE cities (
    name varchar(80),
    location point
);

其中,point是一个PostgreSQL定义的数据类型,它是一个二维坐标

删除表格(drop)

如果不再需要一个表格,或者想要重新创建它的话,可以选择删除它:

1	DROP TABLE tablename;

插入(insert)

INSERT语句用来在指定表格中插入一行数据.例如:

1	INSERT INTO weather VALUES ('南京', 25, 30, 0.8, '2018-8-22');

注意值的输入顺序应该完全遵循创建表格时列名的输入顺序.不是数值类型的常数往往应该被包含在单引号(')中

point类型需要一个坐标对作为输入:

1	INSERT INTO cities VALUES ('南京', '(32.0, 118.0)');

上述示例需要记得列名的顺序,还有另外一种可选方案可以精确的选择每一个值的输入顺序:

1 2	INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('南京', 25, 30, 0.8, '2018-8-22');

这样就可以使用不同的顺序插入值,甚至可以忽略掉一些列名

在需要插入大量数据时,可以选择使用COPY语句从文本文件中读取数据.这通常比INSERT更快:

1	COPY weather FROM '/home/user/weather.txt';

查询(select)

SQL语句SELECT用来从一个表格中获取数据:

postgres=# select * from weather;
 city | temp_lo | temp_hi | prcp |    date
------+---------+---------+------+------------
 南京 |      25 |      30 |  0.8 | 2018-08-22
 南京 |      22 |      30 |  0.3 | 2018-08-23
(2 rows)

*是所有列的简称,在weather表中相当于:

1	SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

也可以写表达式,而不仅仅是列的引用:

postgres=# SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
 city | temp_avg |    date
------+----------+------------
 南京 |       27 | 2018-08-22
 南京 |       26 | 2018-08-23
(2 rows)

AS子句用来命名新生成的列

查询也可以添加WHERE子句来选择想要的行.WHERE子句包含一个布尔类型的表达式,只有那些表达式返回真的行才会被返回.WHERE子句允许使用布尔操作符如AND,OR,NOT

例如:查询南京的下雨天

postgres=# SELECT * FROM weather WHERE city='南京' AND prcp>0.0;
 city | temp_lo | temp_hi | prcp |    date    
------+---------+---------+------+------------
 南京 |      25 |      30 |  0.8 | 2018-08-22
 南京 |      22 |      30 |  0.3 | 2018-08-23
(2 rows)

可以对查询的返回结果进行排序:

postgres=# SELECT * FROM weather
postgres-# ORDER BY city;
 city | temp_lo | temp_hi | prcp |    date    
------+---------+---------+------+------------
 南京 |      25 |      30 |  0.8 | 2018-08-22
 南京 |      22 |      30 |  0.3 | 2018-08-23
(2 rows)

如果说单一个排序指标无法满足需求,可以添加第二个指标进行更细化的排序

postgres=# SELECT * FROM weather
postgres-# ORDER BY city,temp_lo;
 city | temp_lo | temp_hi | prcp |    date    
------+---------+---------+------+------------
 南京 |      22 |      30 |  0.3 | 2018-08-23
 南京 |      25 |      30 |  0.8 | 2018-08-22
(2 rows)

还可以从查询结果中去除重复行:

postgres=# SELECT DISTINCT city FROM weather;
 city
------
 南京
(1 row)

多表查询

除了最常见的单表查询外,我们也可以一次性查询多个表,或者对单表的数据进行不同的处理.这种一次性获取多行的查询叫做join查询

示例:查询南京的天气和位置

postgres=# SELECT *
postgres-# FROM weather, cities
postgres-# WHERE city = name;
 city | temp_lo | temp_hi | prcp |    date    | name | location 
------+---------+---------+------+------------+------+----------
 南京 |      25 |      30 |  0.8 | 2018-08-22 | 南京 | (32,83)
 南京 |      22 |      30 |  0.3 | 2018-08-23 | 南京 | (32,83)
(2 rows)

由于不同表间没有同名的字段,解析器能够自动将输入的字段对应于正确的列上,但若两个表间有同名的字段,则需要在字段名前加上表名:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;

建议无论是否有同名字段都加上表名,这是一个好习惯

上面的查询也可以写成这样:

postgres=# SELECT *
postgres-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
 city | temp_lo | temp_hi | prcp |    date    | name | location 
------+---------+---------+------+------------+------+----------
 南京 |      25 |      30 |  0.8 | 2018-08-22 | 南京 | (32,83)
 南京 |      22 |      30 |  0.3 | 2018-08-23 | 南京 | (32,83)
(2 rows)

这叫做内联合查询(INNER JOIN).这种查询不怎么使用,不过也需要理解其背后的含义:PostgreSQL会扫描weather表中的每一行,然后又会扫描cities表中的每一行去寻找匹配ON子句条件的行.如果匹配成功,则将两张表中匹配的行组合并作为结果输出;如果不匹配则丢弃,并继续寻找

还有一种联合查询叫做外联合查询(OUTER JOIN).它与内联合查询不同之处在于如果匹配不成功,不会丢弃而是以空值的方式显示出来.其按空值显示方式分为左联合(LEFT OUTER JOIN),右联合(RIGHT OUTER JOIN)和全联合(FULL OUTER JOIN)

左联合查询对于不匹配的行,将显示左表的值,而右表将以空值代替:

postgres=# SELECT *                                                 
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
 city | temp_lo | temp_hi | prcp |    date    | name | location 
------+---------+---------+------+------------+------+----------
 南京 |      25 |      30 |  0.8 | 2018-08-22 | 南京 | (32,83)
 南京 |      22 |      30 |  0.3 | 2018-08-23 | 南京 | (32,83)
 上海 |      20 |      33 |  0.2 | 2018-08-21 |      | 
(3 rows)

右联合查询与左联合查询刚好相反,显示右表的值,左表以空值代替:

postgres=# SELECT *                                                 
FROM weather RIGHT OUTER JOIN cities ON (weather.city = cities.name);
 city | temp_lo | temp_hi | prcp |    date    | name | location 
------+---------+---------+------+------------+------+----------
 南京 |      22 |      30 |  0.3 | 2018-08-23 | 南京 | (32,83)
 南京 |      25 |      30 |  0.8 | 2018-08-22 | 南京 | (32,83)
      |         |         |      |            | 镇江 | (30,90)
(3 rows)

全联合查询则同时显示左右表的值,不匹配的表以空值代替:

postgres=# SELECT *
FROM weather FULL OUTER JOIN cities ON (weather.city = cities.name);
 city | temp_lo | temp_hi | prcp |    date    | name | location 
------+---------+---------+------+------------+------+----------
 南京 |      25 |      30 |  0.8 | 2018-08-22 | 南京 | (32,83)
 南京 |      22 |      30 |  0.3 | 2018-08-23 | 南京 | (32,83)
 上海 |      20 |      33 |  0.2 | 2018-08-21 |      | 
      |         |         |      |            | 镇江 | (30,90)
(4 rows)

还有一种查询叫做自联合查询(SELF JOIN).它与普通的联合查询并无实质性区别,只不过把同一个表当作两个表罢了

例如:查询温度范围在其它温度范围之间的城市

postgres=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
postgres-# W2.city, W2.temp_lo AS low, W2.temp_hi AS high
postgres-# FROM weather W1, weather W2
postgres-# WHERE W1.temp_lo < W2.temp_lo
postgres-# AND W1.temp_hi > W2.temp_hi;
 city | low | high | city | low | high 
------+-----+------+------+-----+------
 上海 |  20 |   33 | 南京 |  25 |   30
 上海 |  20 |   33 | 南京 |  22 |   30
(2 rows)

为了区分同一张表,需要将它们设为不同的标签:weather W1,weather W2

聚合函数

和其它的关系型数据库一样,PostgreSQL也支持聚合函数(aggregate functions).聚合函数指能从多行输入计算单一的结果的函数.例如,我们可以对一些行计算它们的count,sum,avg,max,min等等

例如:找出最高的每日最低温度

postgres=# SELECT max(temp_lo) FROM weather;
 max 
-----
  25
(1 row)

这样的话我们就可以求出最低温度的那行记录:

postgres=# SELECT * FROM weather WHERE temp_lo=(SELECT max(temp_lo) FROM weather);
 city | temp_lo | temp_hi | prcp |    date    
------+---------+---------+------+------------
 南京 |      25 |      30 |  0.8 | 2018-08-22
(1 row)

聚合函数经常与GROUP BY子句组合使用.例如:找出每个城市的最高每日最低温度

postgres=# SELECT city, max(temp_lo)
postgres-# FROM weather
postgres-# GROUP BY city;
 city | max 
------+-----
 南京 |  25
 上海 |  20
(2 rows)

还有,如果我们想对聚合查询的结果做个过滤的话,则可以使用HAVING子句:

postgres=# SELECT city, max(temp_lo)
postgres-# FROM weather
postgres-# GROUP BY city
postgres-# HAVING max(temp_lo) <= 20;
 city | max 
------+-----
 上海 |  20
(1 row)

最后,如果我们只关心”上”开头的城市,我们可以使用LIKE关键字:

postgres=# SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE '上%' GROUP BY city;
 city | max 
------+-----
 上海 |  20
(1 row)

注意WHERE和HAVING的区别:

WHERE子句一定不能包含聚合函数.因为无法使用聚合函数决定传递给聚合函数的行数据
HAVING子句往往和聚合函数一起使用.虽然严格来说不与聚合函数一起使用也可以,但那样的话一定是使用WHERE更加有效

查询过程如下:

WHERE子句对表中的每一行进行筛选与过滤
筛选后的行将会送给GROUP BY子句进行分组
分组后的行将分别由聚合函数求出结果
聚合函数的结果将传递给HAVING子句,进行进一步的过滤
返回最终的结果

更新(update)

UPDATE命令用于更新存在的行.例如如果想要将温度换成华氏度:

1
2
3

UPDATE weather
SET temp_hi = temp_hi * 1.8 + 32, temp_lo = temp_lo * 1.8 + 32
WHERE date > '1994-11-28';

删除(delete)

DELETE命令用于移除表中的某些行.例如删除城市为南京的记录:

1	DELETE FROM weather WHERE city = '上海';

这样所有关于南京的行都将被删除

注意谨慎使用没有筛选的DELETE命令:

1	DELETE FROM tablename;

没有筛选的话,DELETE将会删除给定表的所有行,并且没有提示确认信息

视图(view)

试想一下,我们经常需要将天气记录和城市位置组合起来查询,但是每次都需要联合查询实在是太累了,这时我们就可以创建一个视图(view):

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

查询视图:

postgres=# select * from myview;
 city | temp_lo | temp_hi | prcp |    date    | location 
------+---------+---------+------+------------+----------
 南京 |      23 |      28 |  0.8 | 2018-08-22 | (32,83)
 南京 |      20 |      28 |  0.3 | 2018-08-23 | (32,83)
(2 rows)

视图的使用与表几乎一样.视图没有真正的存储数据,它所有的数据都是表的行的引用.可以看出,视图与表属于同级结构:

postgres=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | cities  | table | postgres
 public | myview  | view  | postgres
 public | weather | table | postgres
(3 rows)

一个好的数据库设计经常需要用到视图,它在表与查询接口间添加了一个中间层,当表改变时,往往改变视图而无需更改接口.任何使用表的方法几乎都能在视图上使用,甚至在视图上创建视图

外键(foreign key)

外键用于解决这样如这样的情况:我们需要确保当cities表没有匹配的城市的话,没人能够在weather表插入相关城市的行.这叫做维护参照完整性(referential integrity)

示例:

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

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

现在,插入weather表中的行必须是cities表中有的城市.现在尝试插入一个非法的记录:

1
2
3

postgres=# INSERT INTO weather VALUES ('杭州',20,30,0.0,'2018-8-25');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(杭州) is not present in table "cities".

可以看到明显的报错

事务(transaction)

事务是所有数据库系统的基础功能.事务的本质是将多个步骤合为单个的,全或无的操作.步骤之间的中间态是不会被其它并行事务看到的.并且一旦有操作执行失败,那么该事务将不会对数据库有任何影响

事务具有四个特征:原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持续性(Durability)

原子性:事务由多个语句组成,原子性保证每一次事务都被当作一个单独的单元,即要么完全失败,要么完全成功.如果任意一个事务中的语句执行失败,那么整次事务都将失败,且数据库不会发生任何改变.一个原子的系统需要保证任何情况下的原子性,包括断电,报错等等
一致性:一致性确保一次事务只能使数据库从一个有效的状态到另一个有效的状态.任何写入的数据必须符合定义的规则,包括约束,级联,触发器等等.这将防止数据库被非法的事务所损坏
隔离性:事务经常是并发执行(同一时间读,写多个表),隔离性保证其它并发执行的事务不会看到正在执行的事务的任何修改,并且如果发生冲突(比如同时写一个表),那么这些事务就要顺序执行.隔离性的主要目的是并发控制
持续性:持续性保证一旦事务提交,那么数据库将一直是提交后的状态,即使发生了系统错误.这经常指完成后的事务将保存在永久性存储中

在PostgreSQL中,一次事务指使用BEGIN和COMMIT命令包起来的SQL命令.例如:

postgres=# BEGIN;
BEGIN
postgres=# UPDATE weather SET temp_lo = temp_lo - 5 WHERE city = '南京';
UPDATE 1
postgres=# COMMIT;
COMMIT

如果在事务中途,我们决定不想提交了,可以使用ROLLBACK命令代替COMMIT命令,这样所有BEGIN语句后的操作都将被取消

事实上,在PostgreSQL中每一个SQL语句都是在一次事务中执行的.如果不使用BEGIN命令指定,每一个单独的命令都会有一对BEGIN和COMMIT命令包装它.有时,多个被BEGIN和COMMIT命令包装的语句被称为事务块(transaction block)

可以使用SAVEPOINT控制事务块的流程.默认情况下ROLLBACK将事务退回至BEGIN处,而当指定SAVEPOINT后,ROLLBACK也可以选择退回至SAVEPOINT处:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;

ROLLBACK TO是当事务块发生错误时还能重新获得控制的唯一的方法.因此当需要操作复杂事务时应合理使用SAVEPOINT

窗口函数(window function)

窗口函数从一系列行执行一种计算,这和聚集函数十分类似.与之不同的是,聚集函数多行返回单一结果,而窗口函数则每一行都有一个结果

例如:展示一个员工的薪水和其所在部门的平均薪水

1	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)

最开始的三个输出列直接来自于表empsalary,第四列表示对与当前行具有相同depname值的所有表行取得平均值,avg()实际上是聚合函数,只是OVER子句使它当作一个窗口函数处理并通过窗口帧计算

一个窗口函数调用总是包含一个窗口函数名称和紧跟着的OVER子句.这就是窗口函数区别于普通函数和聚合函数的地方.OVER子句决定了有哪些行分离出来并交给窗口函数处理.PARTITION BY子句将具有相同值的行分到相同的组中.对于每一行,窗口函数都会在同一分区的行上做运算

可以使用ORDER BY子句控制窗口函数处理行的顺序:

1	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)

如上,rank()函数按ORDER BY子句的顺序为每一个按PARTITION BY分的组的每一行产生一个数字等级

窗口函数作用于已经过WHERE,GROUP BY,HAVING过滤的虚拟表.随后,窗口函数使用OVER子句划分数据

如果行的顺序不重要,ORDER BY语句可以忽略;如果不需要将行划分为多个部分,则不需要使用PARTITION BY语句

窗口函数还有另外一个重要的概念叫做窗口帧(window frame),一些窗口函数只对窗口函数有效,而非整个部分.默认情况下,如果使用了ORDER BY语句,那么窗口帧包括从划分部分的第一行一直到当前行的所有行,以及接下来依据ORDER BY的相等的行;如果没有使用ORDER BY,那么窗口帧包括整个部分

以工资总额查询示例:

1	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)

如上,由于缺少PARTITION BY,窗口函数不会将表划分为多个部分;由于缺少ORDER BY,窗口帧就是整个部分.也就是说,每个合计都在整个表上执行,这样的话每个输出结果都相同

再试试加上ORDER BY语句:

1	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)

这里salary由从小到大的顺序排,而sum串口构函数的计算则是从第一行到当前行,以及之后的与当前行相同的行(注意salary为4800的行)

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中,因为窗口函数的执行是在处理完过滤,分类等等之后.另外,窗口函数在聚集函数之后执行,这意味着窗口函数的参数可以包括一个聚集函数,反之不行

如果需要在窗口函数执行完进行过滤或分组,可以使用子查询:

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;

当一个查询涉及到多个窗口函数时,可以将每一个分别写到独立的OVER子句中;但如果多个窗口函数要求的窗口函数行为一样时,为了简便,可以将窗口函数单独定义,然后在每个需要使用的窗口函数中引用它:

1
2
3

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

继承(Inheritance)

类似面向对象语言中的继承,使用了继承的表将会拥有其父表的所有字段:

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

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

并且,当查询父表时,子表的数据也将纳入查询范围中:

1
2
3

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

如果只需要查询父表而不涉及到继承层次中位于父表之下的其他表,则需要添加ONLY关键字:

1
2
3

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