PGSQLMAN CHAPTER2
From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community
[编辑] SQL 语言
目录 |
[编辑] 介绍
本章提供一个如何使用 SQL 执行简单操作的概述。 本教程的目的只是给你一个介绍，并非完整的 SQL 教程。有许多关于 SQL 的书，包括 Understanding the New SQL 和 A Guide to the SQL Standard。而且你还要知道有些 PostgreSQL语言特性是对标准的扩展。
在随后的例子里，我们假设你已经创建了名为 mydb 的数据库，就象在前面的章里面介绍的一样，并且已经启动了 psql。
本手册的例子也可以在PostgreSQL 源代码发布里的目录 src/tutorial/ 中找到。 要使用这些文件，首先进入该目录然后运行 make：
$ cd ..../src/tutorial $ make
这样就创建了那些脚本以及编译了包含用户定义函数和类型的 C 文件。 （你必须使用 GNU make 来做这件事情 — 它在你的系统上可能名字是其它什么东西，经常是 gmake。） 要开始这个教程，按照下面说的进行：
$ cd ..../src/tutorial $ psql -s mydb ...
mydb=> \i basics.sql
\i 命令从指定的文件中读取命令。 -s 选项把你置于单步模式，它在向服务器发送每个语句之前暂停。 在本节使用的命令都在文件 basics.sql 中。
[编辑] 概念
PostgreSQL 是一种关系型数据库管理系统 （RDBMS）。 这意味着它是一种用于管理那些以关系形式存储数据的系统。 关系实际上是表的数学称呼。 今天，把数据存储在表里的概念已经快成了固有的常识了， 但是还有其它的一些方法用于组织数据库。 在类 Unix 操作系统上的文件和目录就形成了一种层次数据库的例子。 更现代的发展是面向对象的数据库。
每个表都是一个命名的行的集合。 每一行由一组相同的命名字段组成。 而且每一字段都有一特定的类型。虽然每字段在每行里的位置是固定的， 但一定要记住 SQL 并未对行在表中的顺序做任何保证（但你可以对它们进行明确的排序进行显示）。
表组成数据库，一个由某个 PostgreSQL 服务器管理的数据库集合组成一个数据库集群。
[编辑] 创建新表
你可以通过声明表的名字和所有字段的名字及其类型来创建表∶
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- 最低气温
temp_hi int, -- 最高气温
prcp real, -- 降水量
date date
);
你可以在 psql 里连换行符一起键入这些东西。 psql 可以识别该命令直到分号才结束。
你可以在 SQL 命令中自由使用空白（也就是空格，tab，和换行符）。 这就意味着你可以用和上面不同的对齐方式键入命令。 两个划线("--") 引入注释。 任何跟在它后面的东西直到该行的结尾都被忽略。 SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写属性（上面没有这么干）。
varchar(80) 声明一个可以存储最长 80 个字符的任意字符串的数据类型。 int 是普通的整数类型。 real 是一种用于存储单精度浮点数的类型。 date 类型应该可以自解释。（没错，类型为 date 的字段名字也是 date。 这么做可能比较方便，也可能容易让人混淆 — 你自己看啦。）
PostgresSQL 支持标准的 SQL 类型 int，smallint， real，double precision， char(N)， varchar(N)，date， time，timestamp 和 interval，还支持其他的通用类型和丰富的几何类型。 PostgreSQL 可以客户化为定制任意的用户定义的数据类型。 因而类型名并不是语法关键字，除了 SQL92 标准要求支持的特例外。
第二个例子将保存城市和它们相关的地理位置：
CREATE TABLE cities (
name varchar(80),
location point
);
类型 point 就是一种 PostgreSQL 特有数据类型的例子。
最后，我们还要提到如果你不再需要某个表，或者你想创建一个不同的表，那么你可以用下面的命令删除它：
DROP TABLE tablename;
[编辑] 向表中添加行
INSERT 用于向表中添加行：
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
请注意所有数据类型都使用了相当明了的输入格式。 那些不是简单数字值的常量必需用单引号(')包围， 就象在例子里一样。 date 类型实际上对可接收的格式相当灵活， 不过在本教程里，我们应该坚持使用这里显示的格式。
point 类型要求一个座标对作为输入，如下：
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
到目前为止使用的语法要求你记住字段的顺序。一个可选的语法允许你明确地列出字段：
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
如果你需要，你可以用另外一个顺序列出字段或者是忽略某些字段， 比如说，我们不知道降水量：
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
许多开发人员认为明确列出字段要比依赖隐含的顺序是更好的风格。
请输入上面显示的所有命令，这样你在随后的各节中才有可用的数据。
你还可以使用 COPY 从文本文件中装载大量数据。 这么干通常更快，因为 COPY 命令就是为这类应用优化的， 只是比 INSERT 少一些灵活性．比如：
COPY weather FROM '/home/user/weather.txt';
这里源文件的文件名必须是后端服务器可访问的， 而不是客户端可访问的，因为后端服务器直接读取文件。 你可以在 COPY 中读到更多有关 COPY 命令的信息。
[编辑] 查询一个表
要从一个表中检索数据就是查询这个表。 SQL 的 SELECT 就是做这个用途的。 该语句分为选择列表（列出要返回的字段部分），表列表（列出从中检索数据的表的部分）， 以及可选的条件（声明任意限制的部分）。比如，要检索表 weather 的所有行，键入：
SELECT * FROM weather;
这里 * 是"所有字段" 的缩写。 [1] 因此同样的结果可以用下面语句获得：
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
而输出应该是：
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows)
你可以在选择列表中写任意表达式，而不仅仅是字段列表。比如，你可以：
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
这样应该得出：
city | temp_avg | date ---------------+----------+------------ San Francisco | 48 | 1994-11-27 San Francisco | 50 | 1994-11-29 Hayward | 45 | 1994-11-29 (3 rows)
请注意这里的 AS 子句是如何给输出字段重新命名的。（AS 子句是可选的。）
一个查询可以使用 WHERE 子句"修饰"，声明需要哪些行。 WHERE 子句包含一个布尔（真值）表达式，只有那些布尔表达式为真的行才会被返回。 允许你在条件中使用常用的布尔操作符（AND，OR， 和 NOT）。 比如，下面的查询检索旧金山的下雨天的天气：
SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0;
结果：
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 (1 row)
你可以要求返回的查询是排好序的：
SELECT * FROM weather
ORDER BY city;
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ Hayward | 37 | 54 | | 1994-11-29 San Francisco | 43 | 57 | 0 | 1994-11-29 San Francisco | 46 | 50 | 0.25 | 1994-11-27
在这个例子里，排序的顺序并非绝对清晰的，因此你可能看到 San Francisco 行随机的排序。 但是如果你使用下面的语句，那么就总是会得到上面的结果
SELECT * FROM weather
ORDER BY city, temp_lo;
你可以要求查询的结果按照某种顺序排序， 并且消除重复的行输出：
SELECT DISTINCT city
FROM weather;
city --------------- Hayward San Francisco (2 rows)
再次声明，结果行的顺序可能是随机的。 你可以组合使用 DISTINCT 和 ORDER BY 来获取一致的结果： [2]
SELECT DISTINCT city
FROM weather
ORDER BY city;
注意
- 虽然 SELECT * 对于把查询的结果翻出来很有用， 我们普遍认为在生产代码中这是很糟糕的风格，因为给表增加一个字段就改变了结果。
- 在一些数据库系统里，包括老版本的 PostgreSQL， DISTINCT 的实现自动对行进行排序，因此 ORDER BY 是多余的。但是这一点并不是 SQL 标准的要求，并且目前的 PostgreSQL 并不保证 DISTINCT 导致数据行被排序。
[编辑] 在表之间连接
到目前为止，我们的查询一次只访问了一个表。 查询可以一次访问多个表，或者用某种方式访问一个表，而同时处理该表的多个行。 一个同时访问同一个或者不同表的多个行的查询叫连接（join）查询。 举例来说，比如你想列出所有天气记录以及这些记录相关的城市。 要实现这个目标，我们需要拿 weather表每行的city 字段和cities表所有行的name字段进行比较， 并选取那些这些数值相匹配的行。
- 注意： 这里只是一个概念上的模型。该连接通常以比实际比较每个可能的行配对更高效的方式执行， 但这些是用户看不到的。
这个任务可以用下面的查询来实现：
SELECT *
FROM weather, cities
WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location ---------------+---------+---------+------+------------+---------------+----------- San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (2 rows)
观察结果集的两个方面：
- 没有城市Hayward的结果行。这是因为在 cities 表里面没有 Hayward的匹配行，所以连接忽略 weather表里的不匹配行。我们稍后将看到如何修补这个毛病。
- 有两个字段包含城市名字。这是正确的， 因为 weather 和 cities 表的字段是接在一起的。不过，实际上我们不想要这些， 因此你将可能希望明确列出输出字段而不是使用 *：
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
- 练习：看看省略 WHERE 子句的语义是什么。
因为这些字段的名字都不一样，所以分析器自动找出它们属于哪个表， 但是在连接查询里使用字段全称是很好的风格：
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
到目前为止，这种类型的连接查询也可以用下面这样的形式写出来：
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);
现在我们将看看如何能把Hayward记录找回来。 我们想让查询干的事是扫描 weather 表， 并且对每一行都找出匹配的 cities 表里面的行。 如果我们没有找到匹配的行，那么我们需要一些"空值"代替cities表的字段。 这种类型的查询叫外连接。 （我们在此之前看到的连接都是内部连接。）这样的命令看起来象这样：
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location ---------------+---------+---------+------+------------+---------------+----------- Hayward | 37 | 54 | | 1994-11-29 | | San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) (3 rows)
这个查询是一个左手边外连接， 因为在连接操作符（译注∶LEFT OUTER JOIN）左手边的表中的行在输出中至少要出现一次， 而在右手边的行将只输出那些与左手边行有对应匹配的行。 如果输出的左手边表的行没有对应匹配的右手边表的行，那么在右手边行的字段将填充空（NULL）。
- 练习：还有右连接和全连接。试着找出来它们能干什么。
我们也可以把一个表和自己连接起来。这叫做自连接。 比如，假设我们想找出那些在其它天气记录的温度范围之外的天气记录。 这样我们就需要拿 weather 表里每行的 temp_lo 和 temp_hi 字段与 weather 表里其它行的 temp_lo 和 temp_hi 字段进行比较。我们可以用下面的查询实现这个目标：
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high ---------------+-----+------+---------------+-----+------ San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)
在这里我们把weather表重新标记为 W1 和 W2 以区分连接的左手边和右手边。 你还可以用这样的别名在其它查询里节约一些敲键，比如：
SELECT *
FROM weather w, cities c
WHERE w.city = c.name;
你以后会经常碰到这样的缩写的。
[编辑] 聚集函数
和大多数其它关系数据库产品一样， PostgreSQL 支持聚集函数。 一个聚集函数从多个输入行中计算出一个结果。 比如，我们有在一个行集合上计算 count（数目）， sum（和），avg（均值）， max（最大值）和min（最小值）的函数。
比如，我们可以用下面的语句找出所有记录中低温中的最高温度
SELECT max(temp_lo) FROM weather; max ----- 46 (1 row)
如果我们想知道该读数发生在哪个城市，我们可以用
SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG
不过这个方法不能运转，因为聚集 max 不能用于 WHERE 子句中。 （存在这个限制是因为 WHERE 子句决定哪些行可以进入聚集阶段；因此它必需在聚集函数之前计算。） 不过，我们通常都可以用其它方法实现我们的目的；这里我们就可以使用子查询：
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city --------------- San Francisco (1 row)
这样做是 OK 的，因为子查询是一次独立的计算，它独立于外层的查询计算出自己的聚集。
聚集同样也常用于 GROUP BY 子句。比如， 我们可以获取每个城市低温的最高值
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)
这样给我们每个城市一个输出。 每个聚集结果都是在匹配该城市的行上面计算的。 我们可以用 HAVING 过滤这些分组：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max ---------+----- Hayward | 37 (1 row)
这样就只给出那些 temp_lo 数值曾经有低于 40 度温度的城市。 最后，如果我们只关心那些名字以 "S" 开头的城市，我们可以用
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'(1)
GROUP BY city
HAVING max(temp_lo) < 40;
- (1)LIKE 做模式匹配，在模式匹配一节里有解释。
理解聚集和SQL的 WHERE 以及 HAVING 子句之间的关系对我们非常重要。 WHERE 和 HAVING 的基本区别如下： WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。 因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。 （严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做很少有用。同样的条件可以更有效地用于 WHERE 阶段。）
在前面的例子里，我们可以在 WHERE 里应用城市名称限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。 这个语法并非象上面那个那么常用，我们在这里写出来是为了让你更容易了解后面的主题。
[编辑] 更新
你可以用 UPDATE 命令更新现有的行。 假设你发现所有 11 月 28 日的温度计数都低了两度，那么你就可以用下面的方式更新数据：
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
看看数据的新状态：
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 Hayward | 35 | 52 | | 1994-11-29 (3 rows)
[编辑] 删除
数据行可以用 DELETE 命令从表中删除。 假设你对Hayward的天气不再感兴趣，那么你可以用下面的方法把那些行从表中删除：
DELETE FROM weather WHERE city = 'Hayward';
所有属于Hayward的天气记录都将被删除。
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 41 | 55 | 0 | 1994-11-29 (2 rows)
我们用下面形式的语句的时候一定要小心
DELETE FROM tablename;
如果没有条件，DELETE 将从指定表中删除所有行，把它清空。做这些之前系统不会请求你确认！
