提示

可以在 psql 输入命令以及换行符，psql 可以识别命令直到分号才结束。

可以在 SQL 命令中自由使用空白（即空格、制表符和换行符）。 这就意味着可以用不同的对齐方式键入命令，或者将命令全部放在一行中。两个划线（“--”）引入注释。 任何跟在它后面直到行尾的东西都会被忽略。SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写。

数据类型

提示

PostgreSQL 支持标准的 SQL 类型 int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp 和 interval，还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL 中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了 SQL 标准要求支持的特例外。完整的数据类型参考

表操作

创建表-CREATE

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

增加列-ALTER ADD

提示

新列将被默认值所填充（如果没有指定 DEFAULT 子句，则会填充空值）

从 PostgreSQL 11 开始，添加一个具有常量默认值的列不再意味着在执行 ALTER TABLE 语句时需要更新表的每一行。 相反，默认值将在下次访问该行时返回，并在表被重写时应用，从而使得 ALTER TABLE 即使在大表上也非常快。

但是，如果默认值是可变的（例如 clock_timestamp()），则每一行需要被 ALTER TABLE 被执行时计算的值更新。 为避免潜在的长时间的更新操作，特别是如果你想要用大多数非默认值填充列，那么最好添加没有默认值的列，再用 UPDATE 插入正确的值，然后按照下面所述添加任何期望的默认值。

ALTER TABLE weather ADD COLUMN new_line text;

移除列-ALTER DROP

提示

列中的数据将会消失。涉及到该列的表约束也会被移除。然而，如果该列被另一个表的外键所引用，PostgreSQL 不会安静地移除该约束。我们可以通过增加 CASCADE 来授权移除任何依赖于被删除列的所有东西：

ALTER TABLE weather DROP COLUMN new_line;

ALTER TABLE weather DROP COLUMN new_line CASCADE;

增加约束

-- CONSTRAINT后面指定名称，如果不知道系统会自动添加名称
ALTER TABLE weather ADD CONSTRAINT check_city CHECK (city <> '');
ALTER TABLE weather ADD CONSTRAINT u_city UNIQUE (city);

-- 非空约束
-- 注意：该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束。
ALTER TABLE weather ALTER COLUMN city SET NOT NULL;

移除约束

-- 如果指定了约束名，这里直接跟约束名。如果没有指定名称需要去查看一下具体的名称。
ALTER TABLE weather DROP CONSTRAINT check_city;
-- 移除非空约束
ALTER TABLE weather ALTER COLUMN city DROP NOT NULL;

默认值

-- 新增默认值
ALTER TABLE weather ALTER COLUMN des SET DEFAULT '描述';
-- 删除默认值
ALTER TABLE weather ALTER COLUMN des DROP DEFAULT;

修改列的数据类型

-- 只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。
-- 如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。
ALTER TABLE weather ALTER COLUMN des TYPE varchar(100);

重命名列

ALTER TABLE weather RENAME COLUMN des TO description;

重命名表

ALTER TABLE weather RENAME TO demo;

删除表-DROP

DROP TABLE weather;

CRUD

插入数据-INSERT

-- 插入一条数据
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

-- 从文件中插入多条数据，注意各字段之间用 tab 分割的
COPY weather FROM '/home/user/weather.txt';

-- 导出数据到文件
COPY weather TO '/tmp/weather.txt';

点击下载：weather.txt 文件， 更多用法参考COPY (opens new window)

更新数据-UPDATE

UPDATE weather SET prcp=0.32, temp_lo=-20 WHERE city='San Francisco';

简单查询-SELECT

-- 查询所有数据
SELECT * FROM weather;

-- 查询指定字段
SELECT city, date FROM weather;

-- 这里的AS子句是如何给输出列重新命名的（AS子句是可选的，但是这种情况推荐都加上AS）
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

去重-DISTINCT

SELECT DISTINCT city, date FROM weather;

删除数据-DELETE

DELETE FROM weather WHERE city='San Francisco';

查询-SELECT

详细信息查询中文文档 (opens new window)， 数据准备点击下载：study.sql 文件

-- 新建表和新增数据，注意study.sql是通过上面的链接下载的
\i /root/study.sql

连接查询

-- INNER JOIN
SELECT * FROM s INNER JOIN sc ON s.s_no=sc.s_no; -- 两个表的所有字段都会返回
SELECT * FROM s INNER JOIN sc USING (s_no); -- 只返回一个s_no
SELECT * FROM s NATURAL INNER JOIN c; -- 相同的字段只显示一个(只返回一个s_no)
SELECT * FROM s INNER JOIN sc USING (s_no) INNER JOIN c USING(c_no); -- 三个表查询
-- LEFT OUTER JOIN
SELECT * FROM s LEFT OUTER JOIN sc USING (s_no); -- 左边表全部数据都会返回
-- RIGHT OUTER JOIN
SELECT * FROM s RIGHT OUTER JOIN sc USING (s_no); -- 右边表全部数据都会返回
-- FULL OUTER JOIN
SELECT * FROM s FULL OUTER JOIN sc USING (s_no); -- 两边表所有数据都返回

-- 加上一个查询条件， 注意：下面两个结果会不同
-- 这是因为放在ON子句中的一个约束在连接之前被处理，而放在WHERE子句中的一个约束是在连接之后被处理。
-- 这对内连接没有关系，但是对于外连接会带来麻烦。
SELECT * FROM s LEFT OUTER JOIN sc ON s.s_no=sc.s_no AND sc.c_no='1';
SELECT * FROM s LEFT OUTER JOIN sc ON s.s_no=sc.s_no WHERE sc.c_no='1';

表和列别名

-- 表别名
SELECT * FROM s AS s1 WHERE s1.s_no='10001';
-- 列别名
SELECT * FROM s alias (s1_no, s1_name);

子查询

-- 查询比杰克年龄大的学生
SELECT * FROM s WHERE s_age>(SELECT s_age FROM s WHERE s_name='杰克');

表函数

GROUP BY 和 HAVING 子句

SELECT s_dept, count(*) FROM s GROUP BY s_dept;
-- GROP BY后面跟HAVING，在 HAVING 子句中，可以使用聚合函数（如 SUM、AVG、COUNT 等）和比较运算符来定义过滤条件。
SELECT s_dept, count(*) FROM s GROUP BY s_dept HAVING s_dept<>'CS';
SELECT s_dept, count(*) FROM s GROUP BY s_dept HAVING count(*)>1;
-- 加上where条件
SELECT s_dept, count(*) FROM s WHERE s.s_age=21 GROUP BY s_dept HAVING count(*)>1;

GROUPING SETS、CUBE 和 ROLLUP

提示

除了 count 之外，这些函数在没有选择行时返回空值。 特别地，行数的 sum 返回空(null)，而不是预期的零。更多聚集函数 (opens new window)

• count：计算总数
• sum：求和
• min：最小值
• max：最大值
• avg：平均值

-- 使用分组集的概念可以实现更加复杂的分组操作
SELECT s_sex, s_dept, count(*) FROM s GROUP BY GROUPING SETS ((s_sex), (s_dept));
SELECT s_sex, s_dept, count(*) FROM s GROUP BY GROUPING SETS ((s_sex), (s_dept), ());
SELECT s_sex, s_dept, count(*) FROM s GROUP BY CUBE ((s_sex), (s_dept));
SELECT s_sex, s_dept, count(*) FROM s GROUP BY ROLLUP ((s_sex), (s_dept));

窗口函数处理

提示

如果查询包含任何窗口函数，这些函数将在任何分组、聚集和 HAVING 过滤被执行之后被计算。也就是说如果查询使用了任何聚集、GROUP BY 或 HAVING，则窗口函数看到的行是分组行而不是来自于 FROM/WHERE 的原始表行。

当多个窗口函数被使用，所有在窗口定义中有句法上等效的 PARTITION BY 和 ORDER BY 子句的窗口函数被保证在数据上的同一趟扫描中计算。因此它们将会看到相同的排序顺序，即使 ORDER BY 没有唯一地决定一个顺序。但是，对于具有不同 PARTITION BY 或 ORDER BY 定义的函数的计算没有这种保证（在这种情况中，在多个窗口函数计算之间通常要求一个排序步骤，并且并不保证保留行的顺序，即使它的 ORDER BY 把这些行视为等效的）。

目前，窗口函数总是要求排序好的数据，并且这样查询的输出总是被根据窗口函数的 PARTITION BY/ORDER BY 子句的一个或者另一个排序。但是，我们不推荐依赖于此。如果你希望确保结果以特定的方式排序，请显式使用顶层的 ORDER BY 子句。

组合查询

两个查询的结果可以用集合操作并、交、差进行组合。语法是
query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

排序

-- 升序
SELECT * FROM s ORDER BY s_no;
SELECT * FROM s ORDER BY s_no ASC;
-- 倒序
SELECT * FROM s ORDER BY s_no DESC;

-- 先s_sex倒序，然后s_age升序
SELECT * FROM s ORDER BY s_sex DESC, s_age;

LIMIT 和 OFFSET

提示

LIMIT：返回的条数。

OFFSET：从多少条开始返回，默认为 0 从第一条开始返回。

被 OFFSET 子句忽略的行仍然需要在服务器内部计算；因此，一个很大的 OFFSET 的效率可能还是不够高。

-- 从第三条开始，返回3条（返回第二页，每页3条数据）
SELECT * FROM s OFFSET 3 LIMIT 3;

WITH 查询

WITH 官方文档 (opens new window)。

-- 计算从1到100的整数和的查询
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

-- 简单案例
WITH tmp AS (
  SELECT * FROM c WHERE c_credit >= 5
)
SELECT * FROM sc INNER JOIN tmp USING(c_no);

-- 等同于上面
SELECT * FROM sc INNER JOIN c USING(c_no) WHERE c.c_credit>=5;