PostgreSQL7.0手册-教程 -72. 查询语言

第七十二章.　查询语言
内容　
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　检查的行进行分组和聚集计算。

--------------------------------------------------------------------------------