问题描述
该笔记将记录：PostgreSQL 的基本使用方法，以及常见问题的解决办法。
数据库：连接与创建
### 切到到用户，
shell> su - postgres # 通过该用户连接，其是服务运行的默认用户
### 创建数据库
shell> createdb mydb
### 删除数据库
shell> dropdb mydb
### 访问数据库
shell> psql mydb
psql (14.4)
Type "help" for help.
mydb=> # 提示符，暗示当前为普通用户
mydb=# # 提示符，暗示当前为超级用户，不受管控
# 通常为安装用户来连接数据库
mydb=> SELECT version();
mydb=> SELECT current_date;
mydb=> \h
mydb=> \q
表：创建及常用操作
PostgreSQL: Documentation: 14: 2.3. Creating a New Table
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
DROP TABLE tablename;
// ---------------------------------------------------------------------------- // 增
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
COPY weather FROM '/home/user/weather.txt'; # 从扁平化的文件中复制数据
// ---------------------------------------------------------------------------- // 删
DELETE FROM weather WHERE city = 'Hayward';
// ---------------------------------------------------------------------------- // 改
UPDATE weather
SET temp_hi = temp_hi - 2,
WHERE date > '1994-11-28';
// ---------------------------------------------------------------------------- // 查
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather JOIN cities ON city = name;
SELECT *
FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
该数据库的高级特性
该部分我们将记录部分我们从未见过的高级特性，其他数据库常规特性我们将简单概括；
视图（View）
CREATE VIEW myview AS
SELECT name, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
外键（Foreign Key）
CREATE TABLE cities (
name varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(name),
temp_lo int,
temp_hi int,
prcp real,
date date
);
事务（Transaction）
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
PostgreSQL 支持事物部分回滚：ROLLBACK TO
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint; # 添加回滚点
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint; # 撤销到回滚点
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
窗口函数（Window Function）
PostgreSQL: Documentation: 14: 3.5. Window Functions
窗口函数：允许我们将聚合的结果赋给每个行，同时数据并未被聚合；
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
在示例中，虽然针对 depname 的 salary 计算平均数，但是凭据数被赋值给每个记录，而没有合并记录。
OVER：表示使用 Window Functoin
PARTITION BY：表示被分组的列；
ORDER BY：能够用于排序；
// ---------------------------------------------------------------------------- // 通过 ORDER BY 排序
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
Window Frame 是指 Window Function 处理的范围：
// 如果即没有指定 ORDER BY 排序，且没有指定 PARTITION BY，则 Window Frame 为整个表，即针对所有的记录进行处理； SELECT salary, sum(salary) OVER () FROM empsalary; salary | sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows) // 如果已经指定 ORDER BY 排序，但没有指定 PARTITION BY，则 Window Frame 为整个表，即针对所有的记录进行处理，但是行为存在差异； SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary; salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows) 注意，这里的数据是按照顺序进行累加的，并且如果目标字段值相同，则被忽略；
Window Function 别名的使用：
SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Window Function 的应用范围：
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
继承（Inheritance）
在 PostgreSQL 中，允许使用面向对象中继承特性：
// ---------------------------------------------------------------------------- // 这种做法类似于 UNION 特性
CREATE TABLE capitals (
name text,
population real,
elevation int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
elevation int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, elevation FROM capitals
UNION
SELECT name, population, elevation FROM non_capitals;
// ---------------------------------------------------------------------------- // 如果使用继承，便会很简单；
CREATE TABLE cities (
name text,
population real,
elevation int -- (in ft)
);
CREATE TABLE capitals (
state char(2) UNIQUE NOT NULL
) INHERITS (cities);
// 如此，capitals 将具备 cities 的字段；
SELECT name, elevation
FROM cities
WHERE elevation > 500;
// 如下查询，将仅查询所有非 state 且 elevation 超过 500 的城市：
SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500;