「PostgreSQL」- 快速入门

  CREATED BY JENKINSBOT

问题描述

该笔记将记录: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;