PostgreSQl 语言篇

PostgreSQl

SQL语言

概念

pgsql是关系型数据库管理系统。关系本质上是表的数学表达。

表是行的集合。同一个表中每行都有相同的列。每个列都是一个特定的数据类型。每列都有固定的顺序，但是无序的。

表被分组存放到数据库，由单个pgsql服务器实例管理的数据库集合构成一个数据库集群。

---

创建表

先来看一个例子：

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

操作如下图：

划掉的部分不是怕泄密，而是那些地方不重要。

启动pgsql后输入上述命令，就可以创建一张表。psql能够识别分号才是命令的结束，当输入为结束之前，命令提示符也是不一样的。

SQL命令中可以自由使用空格、缩进和换行，上面的命令既可以在一行输入，也可以在多行输入。正如你所见，换行以后并没有结束命令的输入，遇到分号;命令才结束。

--表示单行注释。

SQL不区分大小写，但是双引号内用以保存的字符串是区分大小写的。

varchar(80)表示长度为80个字符的可以存储任意字符的字符串。int表示整数类型。real表示单精度浮点数。date表示日期。正如你所见，变量名和类型都叫date，这是允许的，因为类型并不是SQL关键字。

SQL数据类型

• int

• smallint

• real

• double precision

• char(N)

• varchar(N)

• date

• time

• timestamp

• interval

• 几何类型

• 自定义类型

就像前面说的，类型名称不是SQL关键字，除了SQL标准中的特殊类型。比如下面的point类型：

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

最后，要删除一张表可以使用如下命令：

DROP TABLE tablename;

现在我们为mydb数据库创建了两张表，那么去哪里能看到这两张表呢？

还记得pgsql默认安装的pgAdmin吗？它是图形化应用程序，从这里可以清楚的看到。如果你安装的也是最新的pgAdmin 4的话，那么一定会想哭的，WTF？我的表呢？表在哪里？

不得不说相比于之前的版本，很想骂人。甚至于在很长一段时间内，面对pgAdmin 4笔者都是一脸懵逼的状态。好在天无绝人之路，小编幸得高人指点，终于找到它把数据库表藏到了模式>>public>>表下面。

高人名叫殇莫忆（一听名字就知道是高人），住在https://blog.csdn.net/qq_28289405 。

---

向表中插入一行数据

1. 对号入座。

   类似于C语言对结构体的初始化。我们说过，列在行中的顺序是固定的，我们可以按照列的顺序将所有值依次列出来插入一行。如下：

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

   INSERT是插入命名，INTO后面是数据库名字，VALUES后面是要插入的数据。所有数据用括号括起来，数据之间用逗号隔开，非数值的数据全部要用单引号引起来。

   即使是point这样的SQL标准类型，也要用写在引号中。如下：

   INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
   

   point类型需要坐标对作为输入，即x坐标和y坐标。

2. 韩信点兵。

   第1中方法中，要求数据的顺序和列的顺序不差分毫。这里的方式也可以按列的名字插入数据，类似于golang中按名称初始化结构体。

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

   如你所见，现在数据库名字后面跟了一个括号，里面列出了需要插入数据的列的名字，VALUES后面是与前边对应的数据值。这样你不必记得列在行中的顺序，只需要知道你需要往哪些列中插入数据即可，现在数据的顺序完全有weather后面的字段顺序决定。

   这也意味着你可以省略某些列的初始化，例如不初始化降雨量（prec）：

   INSERT INTO weather (date, city, temp_hi, temp_lo)
       VALUES ('1994-11-29', 'Hayward', 54, 37);
   

   推荐使用这种方式。

3. 一气呵成。

   想象一下如果你有一万条数据要录入，要是靠手一条一条命令敲进去。。。喂，120吗。。。

   

   所以SQL提供了COPY命令从文件中加载大量数据。因为对COPY命令进行了优化，所以它要比INSERT更快。然而阴阳相生，在更快的同时也失去了部分的灵活性。

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

   而且，文件名必须在运行后端进程的机器上可用，而不是客户端，因为后端进程直接读取文件。

---

查询

使用SELECT语句。SELECT包含以下几个部分：

• 查询列表。紧跟在SELECT后面，用逗号隔开的列名，想查询哪些列的数据就把列的名字写在后面，*表示所有列。

• 表。跟在FROM后面，想要查询的表的名字，可以有多个表。

• 查询条件（可选）。查询的约束条件。

普通查询

让我们来看第一个例子，从weather表中查询所有数据。

SELECT * FROM weather;

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字句，对应字段会用默认命名?column?，结果如下：

     city      | ?column? |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)

WHERE条件查询

WHERE后面必须跟bool表达式。查询只返回表达式为true的数据。多个bool表达式之间用AND、OR和NOT连接。

例如，查询"San Francisco"降雨量大于0的数据：

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

ORDER BY排序查询

ORDER BY后跟一个或多个列名称，可以使查询结果按指定的列排序。例如：

按城市名排序：

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

按城市名和最低温排序：

SELECT * FROM weather ORDER BY city, temp_lo;

自所以需要按多个列来排序，因为如果只按一个列来排序时，难保该列中会有同样的值，那么他们之间的顺序依然是任意的。

DISTINCT去除重复的数据

在查询列表中的列名字前加上DISTINCT可以查询该列的数据并去掉重复的数据。

SELECT DISTINCT city FROM weather;

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

当然，在去重的同时，也可以进行排序。去重排序两不误。

SELECT DISTINCT city FROM weather ORDER BY city;

为什么会有去重的同时排序这种操作呢？

原来在早期的pgsql中，DISTINCT命令会自动对结果进行排序，而不需要手动ORDER BY。但这并不是SQL的标准，于是后来pgsql就去掉了DISTINCT命令的排序功能。所以现在才有了去重并排序这样的操作。

---

连表查询

同时访问一个或多个表的多个行。

例如，在weather和cities表中查询字段city和字段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)

问题1：city字段和name字段内容相同，重复输出。

可以指定查询列表，选择输出哪些列来去掉重复。

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

思考：上面的查询语句去掉WHERE字句后，结果是什么？（排列组合）

问题2：语法分析器能够自动识别查询列表中的列属于哪个表，但如果两个表中存在同名字段，需要加上表名显示指定该字段属于哪个表。

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

也许你正奇怪，为什么连表查询没有看到JOIN呢？其实到目前为止，上面讲的查询都是在做INNER JOIN查询。本节的第一条查询命令等同于下面的查询语句：

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

ON字句表示的是查询条件。

五种JOIN

1. JOIN

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

单独使用JOIN等同于INNER JOIN。

2. INNER JOIN

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

只输出符合查询条件的行。

3. LEFT OUTER JOIN

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

列出LEFT OUTER JOIN关键字左边的表中的所有行，以及关键字右边表中符合查询条件的行。如果没有匹配的行则输出空。

     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)

4. RIGHT OUTER JOIN

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

与LEFT OUTER JOIN相反，RIGHT OUTER JOIN会列出关键字右边表的所有行，以及左边表中符合查询条件的行。没有匹配的行输出空。

为了更清晰的分析命令的含义，我们先向cities表中再插入一条数据：

INSERT INTO cities VALUES ('London', '(205, 88)');

然后再执行上面的查询语句。

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      10 |      20 |    3 | 2000-03-01 | San Francisco | (-194,53)
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
               |         |         |      |            | London        | (205,88)

因为cities表中只有一条记录，而且匹配成功，所以结果看起来跟INNER JOIN的结果一样。

5. FULL OUTER JOIN

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

两个表中的所有记录都会列出，相匹配的记录输出到同一行，没有匹配的那边输出空。

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 Hayward       |      37 |      54 |      | 1994-11-30 |               |
 San Francisco |      10 |      20 |    3 | 2000-03-01 | San Francisco | (-194,53)
               |         |         |      |            | London        | (205,88)
(4 行记录)

你可以清楚的看到查询结果中哪些地方是空着的。

关于JOIN就只有这么5种组合方式，其他的组合在语法上就是错误的。切记！

一个表除了可以和其他表JOIN，还可以自己跟自己玩，称为self join。

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)

这里用到了很多重命名技术。查询列表中的AS字句自不必多说。其中w1和w2都是weather表，类似于C语言中声明变量的做法。也就是说查询的数据来自同一张表——weather表。

w1和w2就是weather表中的两条记录，不要把它们看做是表，只是它们来自同一张表而已。满足w1的气温范围包含w2的气温范围时，将这两条记录输出。

这种重命名技术也可以用在其他查询语句中，例如：

SELECT * FROM weather w, cities c WHERE w.city = c.name;

这相当于是给表取了一个别名。

---

聚合（统计，拿不准哪个词合适）函数

• count

• sum

• avg

• max

• min

以上这些函数和C语言中的函数的用方法别无二致。都是函数名后跟括号，括号中是参数，这里参数是列名称。例如，查询weather表中最低温最大的数据：

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

其他函数的使用同max一样，就不一一演示了。大家可以自己试试，练习一下。

现在问题又来了。如果我想输出最低温最大的城市的名字，该如何是好呢？

这个时候是比较麻烦的，需要使用嵌套查询。如下：

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

每个SELECT是一个独立的计算单元，它们之间互不影响。所以我们可以将一个SELECT的输出作为另一个SELECT的输入。

也许你觉得这样的嵌套太麻烦了。那么可不可以在WHERE字句直接写WHERE temp_lo = max(temp_lo)呢？这样是万万不可滴，切记切记！

原因是：聚合函数的输入并不是表中的所有记录，而是由WHERE字句选出来的那些记录。WHERE都还没执行，max又拿什么去计算呢？所以聚合函数不能用在WHERE字句中！

聚合函数与GROUP BY

让咱们先来个例子开开胃。

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

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

GROUP BY的意思就是分组。其后跟着的列名，所有该列的值相同的行都会被分为一组。所有查询操作包括聚合函数都会在每个组上执行，最后返回查询结果 。

GROUP BY和WHERE是相似的。只不过WHERE是将所有满足bool表达式的行作为一个组返回，然后在这个组上执行其他查询操作。而GROUP BY将表中的所有行按某列分成若干组，然后其他查询操作在每个组上都会执行一遍。

####聚合函数与HAVING

通过在HAVING字句中使用聚合函数，可以进一步限制查询出来的结果。例如在上面的查询结果中选出最低温的最大值小于40的记录。

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

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

总结

最后WHERE字句还支持LIKE模式匹配：

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'
    GROUP BY city
    HAVING max(temp_lo) < 40;

那么问题是为什么将模式匹配放在WHERE子中，而不是放到HAVING字句中？

要回答这个问题首先要弄清楚聚合函数、WHERE字句、GROUP字句以及HAVING字句的执行顺序。

它们的执行顺序依次是：

1. WHERE字句
2. GROUP字句
3. 聚合函数
4. HAVING字句

WHERE字句最先执行，选出符合条件的记录。其次是GROUP字句，这也意味着GROUP的分组对象可以是SELECT选出来的部分记录。

聚合函数在GROUP BY函数之后执行，这也是为什么在WHERE字句和GROUP BY字句中不能使用聚合函数的原因。

HAVING字句最后执行，所以其中可以用聚合函数进一步过滤查询结果。HAVING基本就是和聚合函数一起使用，鲜有HAVING字句中不带聚合函数的。

最后我们来回答上面的问题。将模式匹配放在WHERE子中，而不是放到HAVING字句中显然更高效。因为这样只需要对WHERE选出来的记录执行GROUP操作和HAVING过滤，而不是对所有的记录执行。

---

更新

更新数据用UPDATE命令。更新只能修改已有的记录。

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

UPDATE后跟要修改的表名。然后是SET以及后面的表达式，表达式之间用逗号分隔，负责修改数据。最后是可选的查询条件，意味着可以限定修改的范围，也可以全部修改。

不信你看↓↓

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命令删除一行，也就整条记录。

DELETE FROM weather WHERE city = 'Hayward';

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

这样的命令将偷偷删除表中的所有记录，不会有任何提示。所以如果不是脑子抽风尽量碰它。