2、PostgreSQL之基本的SQL语言

PostgreSQL之基本的SQL语言

在上一篇文章中，我们已经安装好了PostgreSQL，并且能够通过psql访问数据库，以及远程访问数据库。下面就来介绍一些PostgreSQL的基本操作。

1、创建一个新表

在psql中输入以下命令：

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

小tips：

• SQL语句要以分号“；”结尾。

• 两个划线（“--”）引入注释

2、删除一个表

DROP TABLE tablename;

3、在表中增加行

INSERT语句用于向表中添加行：

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

注意：那些不是简单数字值的常量通常必需用单引号（'）包围

当然你也可以指定出列，然后插入数据

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

我觉得这种写出具体列的写法比较规范。

你还可以使用COPY从文本文件中装载大量数据，举个例子：

COPY weather FROM '/home/user/weather.txt';

当然，这个方法只能在后端进程的机器上使用，也就是在PostgreSQL所安装的机器上。

4、查询一个表

要从一个表中检索数据就是查询这个表。SQL的SELECT语句就是做这个用途的。

语法：

该语句分为选择列表（列出要返回的列）、表列表（列出从中检索数据的表）以及可选的条件（指定任意的限制）。

最简单的查询语句就是：

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)

有SQL基础的应该知道：AS子句是如何给输出列重新命名的（AS子句是可选的）。

WHERE的使用

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

举个例子：

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)

ORDER BY的使用

使用ORDER BY你可以要求返回的查询结果是排好序的。

举个例子：

SELECT * FROM weather
    ORDER BY city, temp_lo;

输出：

     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

DISTINCT的使用

使用DISTINCT可以去除重复行。

举个例子：

SELECT DISTINCT city
    FROM weather;

输出：

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

5、在表之间连接

到目前为止，我们的查询一次只访问一个表。查询可以一次访问多个表，或者用这种方式访问一个表而同时处理该表的多个行。 一个同时访问同一个或者不同表的多个行的查询叫连接查询。（多表查询）

举个例子：

比如你想列出所有天气记录以及相关的城市位置。要实现这个目标，我们需要拿 weather表每行的city列和cities表所有行的name列进行比较， 并选取那些在该值上相匹配的行对。（注意：weather所包含的列有：city、temp_lo、temp_hi、prcp、date。city表中包含列：城市名、城市位置）

那么你可以这么写：

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;

如果在两个表里有重名的列，你需要限定列名来说明你究竟想要哪一个，如：

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

指定列所在的表是一个必备的习惯，当然你也可以给表器别名，这样的话可以更加简易一些。

JOIN ON的写法

上面提到了查询结果集中没有没有城市Hayward的结果行，想要解决这个问题，就要用到连接查询的另一种写法：JOIN ON

可以写成这样：

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

我们想让查询干的事是扫描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)

这个查询是一个左外连接， 因为在连接操作符左部的表中的行在输出中至少要出现一次， 而在右部的表的行只有在能找到匹配的左部表行时才被输出。 如果输出的左部表的行没有对应匹配的右部表的行，那么右部表行的列将填充空值（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)

6、聚合函数

一个聚集函数从多个输入行中计算出一个结果。

举个例子：

找出所有记录中最低温度中的最高温度

SELECT max(temp_lo) FROM weather;

输出：

 max
-----
  46
(1 row)

值得注意的是，聚合函数不能被用于WHERE子句中（这是以为SQL执行顺序的原因）。

GROUP BY的使用

举个例子：

获取每个城市观测到的最低温度的最高值

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

输出：

     city      | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)

HAVING 的使用

每个聚集结果都是在匹配该城市的表行上面计算的。我们可以用HAVING 过滤这些被分组的行：

举个例子：

只给出那些所有temp_lo值曾都低于 40的城市

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | max
---------+-----
 Hayward |  37
(1 row)

WHERE和HAVING的基本区别

WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而HAVING在分组和聚集之后选取分组行。因此，WHERE子句不能包含聚集函数； 因为试图用聚集函数判断哪些行应输入给聚集运算是没有意义的。相反，HAVING子句总是包含聚集函数（严格说来，你可以写不使用聚集的HAVING子句， 但这样做很少有用。同样的条件用在WHERE阶段会更有效）。

7、更新

用UPDATE命令更新现有的行。

假设你发现所有 11 月 28 日以后的温度读数都低了两度，那么你就可以用下面的方式改正数据：

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

8、删除

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

假设你对Hayward的天气不再感兴趣，那么你可以用下面的方法把那些行从表中删除：

DELETE FROM weather WHERE city = 'Hayward';