PostgreSQL 7.3的中文文档。SQL 语言

黄叶

PostgreSQL 7.3 文档
Prev                Next
Chapter 2. SQL 语言

Table of Contents
2.1. 介绍
2.2. 概念
2.3. 创建新表
2.4. 向表中添加行
2.5. 查询一个表
2.6. 在表之间连接
2.7. 聚集函数
2.8. 更新
2.9. 删除

2.1. 介绍

本章提供一个如何使用 SQL 执行简单操作的概述. 本教程的目地只是给你一个介绍，并非完整的 SQL 教程．都许多关于 SQL 的书，包括 Understanding the New SQLMelton and Simon, 1993A complete guideJimMeltonAlan R.Simon1-55860-245-31993Morgan Kaufmann1993Morgan Kaufmann Publishers, Inc. 和 A Guide to the SQL StandardDate and Darwen, 1997A user's guide to the standard database language SQLFourth EditionC. J.DateHughDarwen0-201-96426-01997Addison-Wesley1997Addison-Wesley Longman, Inc.．而且你还要知道有些 PostgreSQL语言特性是对标准的扩展．

在随后的例子里，我们假设你已经创建了名为 mydb 的数据库，就象在前面的章里面介绍的一样，并且已经启动了 psql．

本手册的例子也可以在PostgreSQL 源代码发布里的目录 src/tutorial/ 中找到． 请参考该目录中的 README 文件获取如何使用 它们的信息．要开始这个教程，按照下面说的进行∶

$ cd ..../src/tutorial
$ psql -s mydb
...

mydb=> \i basics.sql

\i 命令从指定的文件中读取命令． -s 选项把你置于单步模式，它在向服务器发送每个语句之前 暂停．在本节使用的命令都在文件 basics.sql 中．
Prev        Home        Next
访问数据库        Up        概念

PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.2. 概念

PostgreSQL 是一种 关系型数据库管理系统 (RDBMS)． 这意味着它是一种用于管理那些以关系 形式存储的数据的系统．关系实际上是表的 数学称呼．今天，把数据存储在表里的概念已经快成了固有的常识了， 但是还有其它的一些方法用于组织数据库．在类 Unix 操作系统上的 文件和目录就形成了一种层次数据库的例子．更现代的发展是面向对象的数据库．

每个表都是一个命名的行的集合． 每一行由一组相同的命名 列组成． 而且每一列都有一特定的类型． 虽然每列在每行里的位置是固定的，但一定要记住 SQL 并未 对行在表中的顺序做任何保证(但你可以对它们进行明确的排序进行显示)．

表组成数据库，一个由某个 PostgreSQL 服务器管理的数据库集合组成一个数据库集群．
Prev        Home        Next
SQL 语言        Up        创建新表
PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.3. 创建新表

你可以通过声明表的名字和所有字段的名字及其类型来创建表∶

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    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;

Prev        Home        Next
概念        Up        向表中添加行


PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.4. 向表中添加行

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';

这里源文件的文件名必须是后端服务器可访问的， 而不是客户端可访问的，因为后端服务器直接读取文件．你可以在 PostgreSQL 7.3 参考手册 中读到更多有关 COPY 命令的信息．
Prev        Home        Next
创建新表        Up        查询一个表


PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.5. 查询一个表

要从一个表中检索数据就是查询这个表． SQL 的 SELECT 就是做这个用途的． 该语句分为选择列表(列出要返回的字段部分)，表列表(列出从中检索数据 的表的部分)，以及可选的条件(声明任意限制的部分)．比如，要检索 表 weather 的所有行，键入∶

SELECT * 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 子句是如何给输出字段 重新命名的．(它是可选的．)

允许你使用任意布尔操作符(AND，OR， 和 NOT)给查询施加条件．比如，下面的查询检索 旧金山的下雨天的天气∶

SELECT * FROM weather
    WHERE city = 'San Francisco'
    AND prcp > 0.0;

Result:

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

最后再提醒一下，你可以要求选出来的结果按照某种顺序排序， 并且消除重复的行输出：

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

     city
---------------
Hayward
San Francisco
(2 rows)

当然， DISTINCT 和 ORDER BY 可以独立使用．
Prev        Home        Next
向表中添加行        Up        在表之间连接

黄叶

PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.6. 在表之间连接

到目前为止，我们的查询一次只访问了一个表． 查询可以一次访问多个表，或者用某种方式访问一个表，而同时处理该表的 多个行．一个同时访问同一个或者不同表的多个行的查询叫 连接(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表的字段．这种类型的查询叫 外连接(outer join)．(我们在此之前看到的 连接都是内部连接．)这样的命令看起来象这样∶

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) 因为在连接操作符(译注∶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;

你以后会经常碰到这样的缩写的．
Prev        Home        Next
查询一个表        Up        聚集函数



PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.7. 聚集函数

和大多数其它关系数据库产品一样， 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 做模式匹配，在 PostgreSQL 7.3 用户手册 里有解释。

理解聚集和SQL的 WHERE 以及 HAVING 子句之间的关系对我们非常重要． WHERE 和 HAVING 的基本区别如下∶ WHERE 在分组和聚集计算之前选取输入行 (因此，它控制哪些行进入聚集计算)，而 HAVING 在分组和聚集之后选取分组的行．因此，WHERE 子句 不能包含聚集函数；因为试图用聚集函数判断那些行输入给聚集运算是 没有意义的．相反，HAVING 子句总是包含聚集函数． (严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲；同样的条件可以更有效地用于 WHERE 阶段．)

通过观察我们可以发现，我们可以在 WHERE 里应用 城市名称限制，因为它不需要聚集．这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算．
Prev        Home        Next
在表之间连接        Up        更新

黄叶

PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.8. 更新

你可以用 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)

Prev        Home        Next
聚集函数        Up        删除




PostgreSQL 7.3 文档
Prev        Chapter 2. SQL 语言        Next
2.9. 删除

假设你对Hayward的天气不再感兴趣， 那么你可以用下面的方法把那些行从表中删除． 删除是用 DELETE 命令执行的∶

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 将从指定 表中删除所有行，把它清空．做这些之前系统不会请求你确认!
Prev        Home        Next
更新        Up        高级特性