PostgreSQL7.0手册-教程 -72. 查询语言
2019-09-08 23:34:02
供稿：网友
第七十二章. 查询语言
内容
SQL 界面
概念
创建新表
向表中加入记录
查询一个表
重新定向 SELECT 查询
联接表
更新
删除
使用聚集函数
Postgres 的查询语言是一个 SQL 下一代标准 SQL3 初稿的一个变种．它有许多扩展，比如可扩展的类型系统，继承，函数和使用(?production) 规则等．这些特性是从最初的 Postgres 查询语言， PostQuel，上继承过来的．本节概要的介绍如何使用 PostgresSQL 进行一些简单操作．这本手册只是试图给你一些我们熟知的SQL的概念，而不是一个SQL的完整的教程．有大量的介绍SQL92的书,包括 Melton and Simon, 1993 和 Date and Darwen, 1997．你同时也要明白有些语言特性并非 ANSI 标准．
SQL 界面
在下面的例子里，我们假定你已经创建了mydb 数据库并且启动了psql．本手册的例子也可以在/usr/local/pgsql/src/tutorial/ 目录下找到．关于如何使用他们，请参阅该目录下的 README 文件．要开始教程，键入下面命令：
% cd /usr/local/pgsql/src/tutorial
% psql -s mydb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type /? for help on slash commands
type /q to quit
type /g or terminate with semicolon to execute query
You are currently connected to the database: postgres
mydb=> /i basics.sql
/i 命令从指定的文件里读入查询．-s 选项让你进入单步模式，这样在把查询送给后端之前会停下来．本节的查询语句都在 basics.sql 文件里．
psql 有一系列 /d 命令用于显示系统信息．使用这些命令去获得更多详细信息； 或在 psql 提示符下键入 /? 查看列表．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
概念
Postgres 里最基本的概念是表（class），表是一个命名的对象实例/记录（instance）的集合．每一实例/记录由一组相同的命名属性/字段（attributes）组成．而且每一属性有一特定的类型．另外，每个实例都有一个唯一的永久对象标识 （OID）．因为 SQL 语法中使用表（tables）的概念，我们这里class和talbes是可以互换的．同样， SQL行(row) 是一个实例(instance) 而 SQL列(columns) 是 属性(attributes)．正如前面所述，一组表组成数据库，一组由某一postmaster 进程管理的数据库构成一个安装或节点．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
创建新表
你可以通过指定表名和表中各字段属性和字段类型的方法创建一个新表：
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
注意：所有关键字和标识都是大小写无关的； SQL92 允许使用双引号将标识括起来实现大小写相关． PostgresSQL支持通常的 SQL 类型 int， float， real， smallint， char(N)，varchar(N)， date， time，和 timestamp，还支持其他的通用类型和丰富的数组类型．我们稍后将看到，Postgres 可以定制任意的用户定义的数据类型．因而类型名并不是语法关键字，除了SQL92 标准要求支持的特例外．所以， Postgres 用于建表的命令 CREATE 和传统的关系型系统的命令几乎一模一样．不过，我们很快就会看到这里的表是关系型模型的扩展．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
向表中加入记录
INSERT 语句用于向表中添加记录：
INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994');
你还可以使用 copy 命令从一个平面（ASCII）文件里向表中添加大数量的记录。这样做通常要快一些，因为数据是做为单个量化事务从目标表中读取或者写入到目标表中去的。例如：
COPY weather FROM '/home/user/weather.txt'
USING DELIMITERS '|';
这里源文件的路径必须是后端服务器机器可以访问的，而不是客户端可访问的路径，因为后端服务器直接从文件中读取数据。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
查询表
我们可以用通常的关系查询选择和影射查询检索 weather 表．一个 SQL select 语句可以用于此事． 该语句分成目标列表（返回的字段）部分和条件（限制条件）部分．例如，我们检索weather表中的所有行，键入：
SELECT * FROM weather;
输出为：
+--------------+---------+---------+------+------------+
|city | temp_lo | temp_hi | prcp | date |
+--------------+---------+---------+------+------------+
|San Francisco | 46 | 50 | 0.25 | 11-27-1994 |
+--------------+---------+---------+------+------------+
|San Francisco | 43 | 57 | 0 | 11-29-1994 |
+--------------+---------+---------+------+------------+
|Hayward | 37 | 54 | | 11-29-1994 |
+--------------+---------+---------+------+------------+
你可以在目标列表中输入任意的表达式．例如：
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
在任何查询条件中条件都可以使用任意布尔操作符（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 | 11-27-1994 |
+--------------+---------+---------+------+------------+
最后，你可以对 select 的结果排序输出或去除重复记录.
SELECT DISTINCT city
FROM weather
ORDER BY city;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
重新定向 SELECT 查询
任何 select 查询可以重定向到一个新表
SELECT * INTO TABLE temp FROM weather;
这句话隐含一个 create 命令，创建一个有着与 select into 命令目标列表一样的字段的新表 temp．这样，我们可以象对其他表一样对生成的表做操作．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
联接表
到此为止，我们一次只能对一个表进行查询．我们可以同时查询多个表，或者同时处理一个表的多个记录．一个同时访问一个或多个表的多个记录的查询叫连接查询．比如，我们想找出所有温度范围在其他记录的温度范围之内的记录．实际上，我们需要拿每个 EMP 记录的 temp_lo 和 temp_hi 字段和所有其他 EMP 记录的相应字段作比较．
注意：这只是一个概念上的模型．实际的联合查询可以一种更有效的方式进行，但却是用户不可见的．
我们可以用下面的查询：
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 |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 37 | 54 | San Francisco | 46 | 50 |
+--------------+-----+------+---------------+-----+------+
注意： 此连接查询的语意是要从查询里声明的表的笛卡儿乘积里面找出符合查询条件的所有记录。对笛卡儿乘积里面资格条件为真的记录， Postgres 计算并返回目标列表的所有值． PostgresSQL 并不对表达式中的重复值赋予任何特殊含义，这就意味着Postgres 有时候会若干次重复计算同一目标列表；这种情况在布尔表达式包含"or"时尤甚，为了避免重复，你应该使用 select distinct 语句．
在本例中，W1 和 W2 都是表 weather 的别名，都具有weather表的所有记录．在大多数数据库术语里 W1 和 W2 被看作是范围变量(range variables.) 一个查询可以包含任意数量的表名和别名．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
更新
你可以使用 update 命令更新现有记录．例如你发现11月28日后所有温度记都偏高2度，你可以用下面命令更新数据库数据：
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '11/28/1994';
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
删除
使用 DELETE 命令删除记录：
DELETE FROM weather WHERE city = 'Hayward';
所有属于 Hayward 的天气记录都将被删除．我们进行下面查询操作时头脑应该很清醒：
DELETE FROM classname;
在不带任何条件时，delete 将简单地把指定表中的所有记录都删除掉，而且做这些之前系统将不给出任何提示！
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
使用聚集函数
与其他大多数查询语言一样， PostgreSQL 支持聚集函数。一个聚集函数从多个输入行计算单一一个结果。例如，存在聚集函数对一个实例的集合进行 count（计数），sum（求和），avg（平均），max （最大），和 min （最小）的计算。
理解聚集和 SQL 的 WHERE 和 HAVING 子句之间的相互作用是非常重要的。在 WHERE 和 HAVING 之间的基本差别是：WHERE 在分组和聚集计算之前选择输入行（因此，它控制哪一行进入聚集计算），而 HAVING 在分组和聚集计算之后选择分组行。因此 WHERE 子句可能不包含聚集函数；因为试图使用一个聚集来判断那些行需要输入到聚集函数中没有什么意义。另一方面，HAVING 子句总是包含聚集函数。（严格的说，你允许写一个不使用聚集的 HAVING 子句，不过是在浪费时间；相同的条件可以在 WHERE 阶段更有效地使用。）
例如，我们可以用下面语句找出最高的最低温度
SELECT max(temp_lo) FROM weather;
如我们想知道这个读数是在哪个城市发生的，我们可以
SELECT city FROM weather WHERE temp_lo = max(temp_lo);
不过这样做是不行的，因为聚集 max() 不能在 WHERE 中使用。不过，正如经常见到的那样，我们可以重新排列查询以实现这样的目标；下面是通过运用子查询实现的方式：
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
这样做是可以的，因为子查询是一个独立的计算，它计算出与外层 select 无关的自身的聚集。
聚集在和 group by 子句联合使用时也是非常有用的。例如，我们可以用下面语句获取在每个城市观察到的最低温度的最高值
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
这个查询为每个城市输出一行。我们可以用 HAVING 过滤这些分组的行：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING min(temp_lo) < 0;
这个查询只给出那些最低温度有零下读数的城市。最后，如果我们只关心以 "P" 开头的城市，我们可以
SELECT city, max(temp_lo)
FROM weather
WHERE city like 'P%'
GROUP BY city
HAVING min(temp_lo) < 0;
请注意我们可以在 WHERE 子句里面给出城市名称的约束，因为这是不需要聚集的。这样比在 HAVING 上加限制更有效，因为我们不必对未通过 WHERE 检查的行进行分组和聚集计算。
--------------------------------------------------------------------------------
