SQL 语言

本章仅提供一个如何使用 SQL 执行简单操作的概述。

概念

PostgreSQL 是一种关系型数据库管理系统 （RDBMS）。这意味着它是一种用于管理存储在关系中的数据的系统。关系实际上是表的数学术语。

每个表都是一个命名的行集合。一个给定表的每一行由同一组命名列组成，而且每一列都有一个特定的数据类型。虽然列在每行里的顺序是固定的，但是 SQL 并不对行在表里的顺序做任何保证（我们可以让它显示排序）。

表被分组成数据库，一个由单个 PostgreSQL 服务器实例管理的数据库集合组成一个数据库集簇。

创建表

sql
1CREATE TABLE weather (
2    city            varchar(80),
3    temp_lo         int,           -- 最低温度
4    temp_hi         int,           -- 最高温度
5    prcp            real,          -- 湿度
6    date            date
7);

识别 SQL 语句直到分号为止，前面可以任意增加换行符、注释、逗号、空白符等。

SQL 语句对关键字和标识符大小写不敏感，只有在标识符用双引号“”时才能保留大小写。

上面的 SQL 语句定义了列名和列类型。其中列类型为：

varchar(80)——可以存储 80 个任意字符串的数据。
int——普通的整数类型。
real——用来存储单精度浮点数的类型。
date——日期类型。

PostgreSQL 支持标准的 SQL 类型int、smallint、real、double precision、char(*N*)、varchar(*N*)、date、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。

再来创建一个保存城市和他们相关的地址位置的数据表：

sql
1CREATE TABLE cities (
2    name            varchar(80),
3    location        point
4);

类型point就是一种 PostgreSQL 特有数据类型的例子。

删除表

sql
1DROP TABLE tablename;

增加行

**（不推荐使用）**隐式顺序插入数据

sql
1INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

所有数据类型都有相当明了的输入格式。那些不是简单数字值的常量通常必须用单引号‘包围。

point类型要求一个坐标对作为输入，如下：

sql
1INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

上面使用的语法要求记住列的顺序才能正确插入行。

**（推荐使用）**还有一种可选的语法允许明确地列出列名：

sql
1INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
2    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

如果有需要，还可以忽略某些列，比如说我们不知道prcp，就可以不用写：

sql
1INSERT INTO weather (date, city, temp_hi, temp_lo)
2    VALUES ('1994-11-29', 'Hayward', 54, 37);

许多开发人员认为明确列出列要比依赖隐含的顺序是更好的风格。

查询表

要从一个表中检索数据就是查询这个表。

SQL 的SELECT语句就是做这个用途的。该语句分为 select 列表（列出要返回的列）、table 列表（列出从中检索数据的表）以及可选的条件（指定任意的限制）。

比如，要检索表weather的所有行，键入：

sql
1SELECT * FROM weather;

这里*是“所有列”的缩写。

效果类似于下面的查询：

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

查询结果为：

bash
1     city      | temp_lo | temp_hi | prcp |    date
2---------------+---------+---------+------+------------
3 San Francisco |      46 |      50 | 0.25 | 1994-11-27
4 San Francisco |      43 |      57 |    0 | 1994-11-29
5 Hayward       |      37 |      54 |      | 1994-11-29
6(3 rows)

在 select 列表中，可以写任意表达式。比如，可以写：

sqlite
1SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

这样就可以得到：

bash
1     city      | temp_avg |    date
2---------------+----------+------------
3 San Francisco |       48 | 1994-11-27
4 San Francisco |       50 | 1994-11-29
5 Hayward       |       45 | 1994-11-29
6(3 rows)

其中 AS 子句是给列重新命名的。

一个查询可以使用 where 子句指定需要哪些行。where 子句包含一个布尔值（真值）表达式，只有那些让布尔表达式为 true 的行才会被返回。

在条件中可以使用常用的布尔操作符（AND、OR 和 NOT）。比如，下面的查询检索旧金山的下雨天的天气。

sql
1SELECT * FROM weather WHERE city='San Francisco' AND prcp > 0;

bash
1     city      | temp_lo | temp_hi | prcp |    date
2---------------+---------+---------+------+------------
3 San Francisco |      46 |      50 | 0.25 | 1994-11-27
4(1 row)

排序 ORDER BY

返回的查询结果还可以排序：

sql
1SELECT * FROM weather ORDER BY city;

bash
1     city      | temp_lo | temp_hi | prcp |    date
2---------------+---------+---------+------+------------
3 Hayward       |      37 |      54 |      | 1994-11-29
4 San Francisco |      43 |      57 |    0 | 1994-11-29
5 San Francisco |      46 |      50 | 0.25 | 1994-11-27

此时旧金山的数据还是随机排序，可以再增加一个排序条件对它进行排序。

sql
1SELECT * FROM weather ORDER BY city , prcp; -- 默认升序
2SELECT * FROM weather ORDER BY city , prcp ASC; -- 等同于上面的 SQL
3SELECT * FROM weather ORDER BY city , prcp DESC; -- 降序

取消重复行 DISTINCT

在查询时，还能够消除重复的行：

sql
1SELECT DISTINCT city FROM weather ORDER BY city;;

bash
1     city
2---------------
3 Hayward
4 San Francisco
5(2 rows)

注意：虽然SELECT *对于即席查询很有用，但普遍认为在生产代码中这是一种很糟糕的风格，因为给表增加一个列就能改变原有的结果。

LIMIT 限制行数

sql
1SELECT city FROM weather LIMIT 1;

OFFSET

假设现在我仅需要除第一个外的所有行，可以用OFFSET关键字

sql
1SELECT * FROM person ORDER BY id OFFSET 1;

OFFSET 跟 LIMIT 一起用筛选出除了第一个以外的所有行中的一个：

sql
1SELECT * FROM person ORDER BY id OFFSET 1 LIMIT 1;

IN

in 用来从一组数据中筛选数据，类似于 or 的语法糖

sql
1SELECT * FROM person WHERE first_name IN ('Leo','Qiu');

等同于

sql
1SELECT * FROM person WHERE first_name ='Leo' OR first_name ='Qiu';

BETWEEN

区间查询：

sql
1SELECT * FROM person WHERE date_of_birth BETWEEN DATE('1992-01-01') AND DATE('1992-12-31');

LIKE

字符串模糊查询：

sql
1SELECT * FROM person WHERE email LIKE '%@gmail.com';

通配符%表示任意字符

COALESCE

如果有一些数据列为 null，我们在获取的时候不想让他返回 null ，而是我们指定的内容，可以使用COALESCE

sql
1SELECT COALESCE(email,'not provider')  FROM person;

NULLIF

在做查询运算时，经常会遇到除以 0 的操作，在程序中，这是不允许的，所以遇到这种情况，需要使用 NULLIF 结合 COALESCE 做判断查询：

NULLIF(arg1,arg2)接受两个参数，当 arg1 等于 arg2 时，则返回 null，否则返回 arg1。

基于这一点，我们可以用以下语句解决除以 0 的问题：

SELECT COALESCE(10 / NULLIF(age,0),0)  FROM person;

现在即使 age 为 0，10/0 的除法结果也是 0。

时间转换

时间戳转化成 DATE：

sql
1SELECT NOW()::DATE;
2-- 2023-09-12

时间戳转化为 TIME：

sql
1SELECT NOW()::TIME;
2-- 15:22:53.885641

提取年份：

sql
1SELECT EXTRACT(YEAR FROM NOW());

提取月份：

sql
1SELECT EXTRACT(MONTH FROM NOW());

从生日得到年龄

sql
1SELECT AGE(NOW(),date_of_birth) AS age FROM person;
2-- 61 years 2 mons 20 days 15:33:07.57824

从生日得到年龄（到年）

sql
1SELECT EXTRACT(YEAR FROM AGE(NOW(),date_of_birth)) FROM person;
2-- 61

连接表

查询可以一次访问一个表，也可以访问多个表，或者可以访问一个表而同时处理该表的多个行。

一个同时同一个或者不同表的多个行的查询叫连接查询。

在我们的数据中，如果我们想要同时列出所有的天气记录以及城市位置，就需要拿 weather 表的 city 列与 cities 表的 name 列进行比较，并且选取那些在该值上相匹配的行。

sql
1SELECT * FROM weather, cities WHERE name = city;

bash
1San Francisco	46	50	0.25	1994-11-27	San Francisco	(-194,53)
2San Francisco	43	57	0	1994-11-29	San Francisco	(-194,53)

此时并没有 Hayward 的行，这是因为 cities 表里面没有 Hayward 的匹配行，所以连接忽略 weather 表里的不匹配行。

有两个列包含城市的名字，这是正确的，但我们不想要这些，因此明确地列出输出列而不是使用*会更好：

sql
1SELECT city, temp_lo, temp_hi, prcp, date, location
2    FROM weather, cities
3    WHERE city = name;

因为这些列的名字不一样，所以 sql 会自动找出它们属于哪个表。如果在两个表中有重名的列，那么需要限定列名来说明究竟想要哪一个，如：

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

人们广泛认为在一个连接查询中限定所有列名是一种好的风格，这样即使未来向其中一个表里添加重名列也不会导致查询失败。

到目前为止，这种类型的连接查询也可以这样写：

sql
1SELECT * FROM weather JOIN cities ON weather.city = cities."name";

现在我们将看看如何将 Hayward 记录找回来。我们想让查询干的事情是扫描 weather 表，并且每一行都能找出匹配的 cities 表行。如果没有找到匹配的行，那么我们需要一些空值来替代 cities 表的列。

这种类型的查询叫外连接查询。（之前的查询都是内连接查询）。

外连接和内连接的差别：

内连接是两表的列之间取交集。

外连接是两表之间取并集。

sql
1SELECT * FROM weather LEFT OUTER JOIN cities ON weather.city = cities."name";

这个查询是左外连接，意味着在连接操作符左边的表的行在输出中至少要出现一次，而右边的表的行只有在找到匹配的左边的表才能被输出。

如果输出的左边的表的行没有对应匹配的右边的表的行，那么右边表行的列将填充为 null。

还有右外连接和全外连接。

右外连接：

sql
1SELECT * FROM weather RIGHT OUTER JOIN cities ON weather.city = cities."name";

右外连接跟左外连接的差别是指定右边的表的行在输出中至少要输出一次。

全外连接：

sql
1SELECT * FROM weather FULL OUTER JOIN cities ON weather.city = cities."name";

全外连接是只要左表（weather）和右表（cities）其中一个表中存在匹配，则返回行。

我们也可以把一个表和自己连接起来，这叫做自连接。比如，假设我们想找出那些在其它天气记录的温度范围之外的天气记录。这样我们就需要拿 weather表里每行的temp_lo和temp_hi列与weather表里其它行的temp_lo和temp_hi列进行比较。

自连接：

sql
1SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
2    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
3    FROM weather W1, weather W2
4    WHERE W1.temp_lo < W2.temp_lo
5    AND W1.temp_hi > W2.temp_hi;
6
7     city      | low | high |     city      | low | high
8---------------+-----+------+---------------+-----+------
9 San Francisco |  43 |   57 | San Francisco |  46 |   50
10 Hayward       |  37 |   54 | San Francisco |  46 |   50
11(2 rows)

在这里我们把 weather 表重新标记为W1和W2以区分连接的左部和右部。你还可以用这样的别名在其它查询里节约一些敲键，比如：

sql
1SELECT * FROM weather w,cities c WHERE w.city=c.name;

一张图读懂各种连接

聚集函数

一个聚集函数从多个输入行中计算出一个结果。比如，我们有在一个行集合上计算count（计数）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函数。

我们可以从下面的语句中找出所有记录中最低温度的最高温度：

sql
1SELECT MAX(temp_lo) FROM weather;

bash
1 max
2-----
3  46
4(1 row)

如果想知道该度数发生在哪个城市，我们不可以用：

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     错误

因为聚合函数 max 不能被用于 where 子句中（原因是 where 子句决定哪些行可以被聚集函数计算，因此它显然要在聚集函数之前就已经执行）。

我们可以用其他方法来实现我们的目的，这里我们可以使用子查询：

sql
1SELECT city FROM weather WHERE temp_lo = (SELECT MAX(temp_lo) FROM weather);

bash
1     city
2---------------
3 San Francisco
4(1 row)

这样做是可以的，因为子查询是一次独立的计算，它独立于外层的查询计算出自己的聚集。

GROUP BY

聚集函数经常和 GROUP BY 子句组合。比如我们可以获取每个城市观测到的最低温度的最高值：

首先我们要 GROUP BY city
根据分组的城市获取最低温度的最高值

sql
1SELECT city,MAX(temp_lo) FROM weather GROUP BY city;

bash
1     city      | max
2---------------+-----
3 Hayward       |  37
4 San Francisco |  46
5(2 rows)

每个聚集的结果都是在匹配该城市的表行上面计算的，我们得到了每个城市的输出。

这时候可以用 HAVING过滤这些被分组的行：

sql
1SELECT city,MAX(temp_lo) FROM weather GROUP BY city HAVING MAX(temp_lo) < 40;

bash
1  city   | max
2---------+-----
3 Hayward |  37
4(1 row)

这样 HAVING 就替代 WHERE 筛选出小于 40 的城市。

如果我们还想要筛选出以“s”开头的城市，我们可以用：

sql
1SELECT city, max(temp_lo)
2    FROM weather
3    WHERE city LIKE 'S%'            -- (1)
4    GROUP BY city
5    HAVING max(temp_lo) < 40;

WHERE和HAVING的基本区别如下:

WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）。
HAVING在分组和聚集之后选取分组行。
WHERE子句不能包含聚集函数。
HAVING子句总是包含聚集函数。

更新行

我们可以使用 UPDATE 更新现有的行。例如，将所有的 11 月 28 日之后的温度都减少 2 度：

sql
1UPDATE weather
2    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
3    WHERE date > '1994-11-28';

删除

数据行可以使用 DELETE 命令从表中删除。

下面的命令将所有 Hayward 的天气都删除：

sql
1DELETE FROM weather WHERE city='Hayward';

还可以使用下面的语句将表中所有的行都删除：

sql
1DELETE FROM tablename;

ON CONFLICT

当 unique 约束或者 primary 约束时，执行 SQL 语句会出现一些错误，比如插入相同 ID 时，就会产生冲突。

出现这些冲突后，我们不希望程序报错，而是让它什么也不要做。

ON CONFLICT就是用来处理此类问题的。

使用以下语句可以让程序 SQL 在插入数据并出现冲突时，什么也不做：

sql
1INSERT INTO person(id,first_name,last_name,gender,date_of_birth)VALUES(1,'Qiu','Yanxi','MALE',DATE '1992-09-22') ON CONFLICT DO NOTHING;