PostgreSQL Essential

2015-04-20 | 振导社会 | 程序设计 SQL

基本应用

SQL操作主要包括SELECT、CREATE、ALTER、REVOKE、DROP、SET和SELECT等，几个通用的基本操作为查询（SELECT）、创建（CREATE）、更改（ALTER）、删除（DROP）……

入门

进行PostgreSQL的任何操作前，须在磁盘上初始化数据库存储空间，这称为数据库簇（database cluster）：

$ initdb -D /usr/local/var/postgres/data

PostgreSQL采用客户端／服务器的工作模式，如果要管理数据库，需要先启动服务进程：

$ pg_ctl start -D /usr/local/var/postgres/data

可以在环境变量中设置代替命令中的-D参数：

export PGDATA=/usr/local/var/postgres/data

通过管理工具PostgreSQL可以操控数据库，流行的三大管理工具是命令行的psql、图形界面的pgAdmin、网页版的PHPPgAdmin，管理工具以客户端的形式接入。psql的接入方式：

$ psql -U user -d exampledb -h 127.0.0.1 -p 5432
$ psql -l #列出所有数据库

创建数据库：

$ createdb 数据库名

删除数据库：

$ dropdb 数据库名

psql的基本管理命令如下：

\h：查看SQL命令的解释，比如\h select；
\?：查看psql命令列表；
\l：列出所有数据库；
\c [database_name]：连接其他数据库；
\d：列出当前数据库的所有表格；
\d [table_name]：列出某一张表格的结构；
\dt：列出所有的表；
\du：列出所有用户；
\e：打开文本编辑器；
\conninfo：列出当前数据库和连接的信息。

数据类型¹

PostgreSQL的数据类型包括：数值类型、货币类型、字符类型、二值类型、日期／时间类型、枚举类型、地理类型、网络地址类型、位字符串类型、文本搜索类型、UUID类型、XML类型、JSON类型、数组、复合类型、范围类型、对象标志类型、pg_lsn类型、伪类型。

查询操作

`GROUP BY` & `HAVING`

经过WHERE语句过滤后，输入表可通过GROUP BY分组，并且利用HAVING排除不满足条件的分组。

嵌套查询

查询sender最新发的消息，结果再按最新优先排序：

-- +------------+-------------+----------+
-- | sender_id  |  created_at | message  |
-- +------------+-------------+----------+
-- |      1     | 2010-06-14  | the msg  |
-- |      1     | 2010-06-15  | the msg  |
-- |      2     | 2010-06-16  | the msg  |
-- |      3     | 2010-06-14  | the msg  |
-- +------------+-------------+----------|

SELECT  *
FROM    (
        SELECT DISTINCT ON (sender_id) *
        FROM messages 
        ORDER BY sender_id, created_at DESC 
        ) q
ORDER BY created_at DESC;

-- 等价于：
WITH q AS (
    SELECT DISTINCT ON (sender_id) *
    FROM messages 
    ORDER BY sender_id, created_at DESC 
) 
SELECT * FROM q ORDER BY created_at DESC;

模式匹配²

用户管理

PostgreSQL只有一种类型的账户，称之为角色（role）。当角色拥有登陆（login）权限时，称之为用户（user）。角色也可作为其它角色的成员，拥有其他角色成员的角色称为组（group）。

PostgreSQL第一次初始化时创建会账户postgres以及同名的数据库postgres，以该用户登录，利用psql或pgAdmin可以创建其它用户。

创建有效期为infinity（也是默认值）的用户：

CREATE ROLE leo LOGIN PASSWORD 'lion!king' CREATEDB VALID UNTIL 'infinity';

创建超级用户：

CREATE ROLE regina LOGIN PASSWORD 'queen!penultimate' SUPERUSER VALID UNTIL '2020-10-20 23:00';

一般情况，组角色拥有其成员角色登录权限以外的其它权限。也可以创建用于登录权限的组。创建组jungle（默认值为INHERIT），并添加用户leo到组：

CREATE ROLE jungle INHERIT;
GRANT jungle TO leo;

通过ALTER分配用户权限：

ALTER ROLE jungle WITH CREATEROLE;

通过REVOKE收回用户权限：

REVOKE ALL ON "tabelname" from jungle;

注意：

PostgreSQL能够使其成员角色不拥有该组的权限；
角色的一些权限不能被其成员继承，比如超级用户。

数据库管理

CREATE DATABASE mydb;

这将以登录用户作为拥有者（owner），创建数据库，该数据库是模版数据库template1的拷贝。模版数据库（template database）可作为创建其它数据库的模版，任何数据库都可作为模版数据库：

UPDATE pg_database SET datistemplate=true WHERE datname='mydb';

标记为模版数据库的数据库不能被删除，可以被拥有CREATEDB权限的用户用于创建新的数据库。template1是默认的模版数据库，编码（encoding）不能修改。如果需要修改编码或者不需要template1中的扩展（extension），可以用模版数据库template0：

CREATE DATABASE mydb TEMPLATE template0;

表管理

查看当前数据库的所有表：

\dt

--             List of relations
--  Schema |     Name     | Type  |  Owner   
-- --------+--------------+-------+----------
--  public | distributors | table | jiyeqian
--  public | user         | table | jiyeqian

如果表名与SQL的关键字相同，需要加上作用域，或者只加双引号：

SELECT * FROM public."user";
SELECT * FROM "user";

如果SQL的关键字与定义的名字相同，需要对名字加双引号，如上删除表格的例子。

创建表

创建表就是创建不同数据类型的字段。除了数据类型外，创建表格的参数还包括：

列／字段约束（column_constraint）：NOT NULL、PRIMARY KEY（可视为UNIQUE和NOT NULL的组合）、DEFAULT
表约束（table_constraint）：PRIMARY KEY
like选项（like_option）：INCLUDING

-- 直接通过CREATE创建
CREATE TABLE "user"
(
  id serial PRIMARY KEY,
  user_id integer NOT NULL, 
  item_id integer,  
  behavior_type smallint, 
  user_geohash varchar(8), 
  item_category integer, 
  time timestamp with time zone
);

-- 依照表格创建新表
CREATE TABLE table2(like table1);

-- 基于查询结果创建表
CREATE TABLE visit_log AS SELECT id AS user_id, time FROM "user" LIMIT 10;

修改表³

-- 增加字段（列）：
ALTER TABLE products ADD COLUMN description text; 
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');  -- 增加字段（列）及约束条件

-- 删除字段：
ALTER TABLE products DROP COLUMN description; 
ALTER TABLE products DROP COLUMN description CASCADE; -- 删除有依赖关系的字段

-- 增加约束条件：
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

-- 删除约束条件：
ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

-- 修改字段默认值：
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

-- 修改字段数据类型：
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

-- 字段重命名：
ALTER TABLE products RENAME COLUMN product_no TO product_number;

-- 表重命名：
ALTER TABLE products RENAME TO items;

数据操作

插入数据：

INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;
INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

更新数据：

UPDATE products SET price = 10 WHERE price = 5;
UPDATE products SET price = price * 1.10;
UPDATE products SET time = to_timestamp(time_str, 'yyyy-mm-dd HH24');
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

删除数据：

DELETE FROM products WHERE price = 10;
DELETE FROM products;

表之间的连接

实用技术

psql运行SQL文件

今有创建表的SQL语句文件create_table_user.sql，内容如下：

CREATE TABLE "user"
(
  id serial PRIMARY KEY,
  user_id integer NOT NULL, 
  item_id integer,  
  behavior_type smallint, 
  user_geohash varchar(8), 
  item_category integer, 
  time varchar(16)
)

在psql命令控制台执行运行sql文件的格式为\i [file.sql]:

\i create_table_user.sql

文件与表单数据的导入与导出

导入前需要先建表，表单字段的数据类型决定了导入数据的类型。将CSV文件导入到表：

COPY "user"(user_id, item_id, behavior_type, user_geohash, item_category, time)
FROM '/abspath/tianchi_mobile_recommend_train_user.csv' WITH CSV HEADER;

将表导出到文件：

COPY "user" TO '/abspath/tianchi_mobile_recommend_train_user.csv' WITH CSV HEADER;

-- 导出查询结果
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

-- 导出结果打包
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

注意：

文件须使用绝对路径。
不要混淆COPY和psql指令\copy。 \copy调用COPY FROM STDIN或者COPY TO STDOUT，然后把数据抓取/存储到一个psql客户端可以访问的文件中。
在SQL标准里没有COPY语句。

基本配置

PostgreSQL通过三个主要被配文件控制其基本操作，这三个文件位于数据文件夹：

postgresql.conf：基本设置，包括：内存分配、新建数据库的默认存放位置、监听IP等；
pg_hba.conf：安全控制，控制访问权限；
pg_ident.conf：将授权的操作系统用户映射为PostgreSQL用户。

当连接到任意数据库时，超级用户可以通过SQL查看这三个文件的位置：

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

可以通过SQL查看postgresql.conf的当前配置：

SELECT name, context, unit, setting, boot_val, reset_val
FROM pg_settings
WHERE name in('listen_addresses','max_connections','shared_buffers','effective_cache_size', 'work_mem', 'maintenance_work_mem')
ORDER BY context,name;

--          name         |  context   | unit |  setting  | boot_val  | reset_val 
-- ----------------------+------------+------+-----------+-----------+-----------
--  listen_addresses     | postmaster |      | localhost | localhost | localhost
--  max_connections      | postmaster |      | 100       | 100       | 100
--  shared_buffers       | postmaster | 8kB  | 16384     | 1024      | 16384
--  effective_cache_size | user       | 8kB  | 524288    | 524288    | 524288
--  maintenance_work_mem | user       | kB   | 65536     | 65536     | 65536
--  work_mem             | user       | kB   | 4096      | 4096      | 4096

context的值为postmaster，表示改变参数需要重启服务才生效；context的值为user，表示改变至少需要重新加载生效。这些设置也能在数据库、用户、会话和函数被覆盖（overridden）。

重新加载不会影响到活动连接：

$ pg_ctl reload -D your_data_directory_here

高级应用

视图

外键

继承

事务

窗函数

`WITH`查询

WITH为大规模查询书提供了写辅助语句的方法。这些语句通常称为通用表表达式（CTE，common table expressions），它们可以被当作临时表，仅供一次查询使用。WITH语句中的辅助语句可以是SELECT、INSERT、UPDATE或DELETE，WITH语句本生也带有SELECT、INSERT、UPDATE或DELETE的主语句。

WITH语句包含的SELECT语句的基本功能是将复杂的查询分割为多部分，比如：

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

以上语句的作用是统计畅销区域每种商品的销量。

RECURSIVE修饰符为WITH语句增加了标准SQL没有的功能。采用RECURSIVE，WITH可以引用它自身的输出。一个简单的例子是计算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 RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

上例中，our_product包含sub_part，sub_part又包含sub_part……，当然sub_part的层次是有限的，也就是包含关系不能构成圈。最终的表是our_product的每个层次sub_part的直接展示。

为了检查是否查询过相同的数据（避免圈），增加path和cycle两个字段：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

通过需要多个字段判断是否有圈：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

注意：

当只需检测一个字段时，不必使用ROW()语法，这样效率更高。
递归查询得到path的是深度优先遍历。

利用WITH语句中修改表的语句，可以将表一分为二，这些修改表的语句通常都带有RETURNING返回项：

CREATE TABLE visit_log_roi AS
WITH moved_rows AS (
    DELETE FROM visit_log
    WHERE
        "time" >= '2014-12-06' AND
        "time" < '2014-12-13'
    RETURNING *
)
SELECT * FROM moved_rows;

聚合函数

聚合函数（aggregate funciton）从一组输入数据计算出一个值，PostgreSQL有内建聚合函数和内建有序集聚合函数。

基本用法⁴

常用的聚合函数是计算行构成集合的包含行的数目、元素和、平均值等。

-- 查询最高气温
SELECT max(temp_lo) FROM weather;

--  max
-- -----
--   46
-- (1 row)

-- 利用子查询，进一步得到最高气温的城市
SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

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

聚合函数通常与分组语句GROUP BY配合使用：

-- 查询每个城市的最高气温
SELECT city, max(temp_lo) FROM weather GROUP BY city;

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

-- 进一步过滤掉不满足条件的结果
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

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

聚合函数配合WHERE和HAVING限制条件：

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

WHERE语句限制进入GROUP BY的对象，HAVING过滤GROUP BY之后的对象，相同功能的限制条件一般在WHERE处效率更高。

自定义聚合函数

查看自定义聚合函数使用命令\da，删除自定义聚合函数使用DROP AGGREGATE。
查看自定义函数（包括自定义聚合函数）使用命令\df，删除自定义函数使用DROP FUNCTION（不能删除自定义的聚合函数）。

值表达式⁵

PL/pgSQL⁶

PL/pgSQL是用于PostgreSQL数据库系统的可装载过程语言（procedural language），它主要实现如下功能：

创建函数与触发过程；
为SQL语言加入控制结构；
进行复杂的计算；
继承所有用户定义的类型、函数和操作；
服务器受信；
易于使用。

PL/Python⁷

PL/Python过程语言使得可以用Python语言写PostgreSQL函数。OSX用brew默认安装的PostgreSQL不支持Python，安装时需要参数--with-python：

$ brew install  postgresql --with-python

使用之前，需要先安装PL/Python插件。如果已经连接了某个数据库，直接创建插件：

CREATE EXTENSION plpythonu;

或者从shell命令行为特定的数据库安装插件：

$ createlang plpythonu dbname

PL/Python函数的基本语法：

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpythonu;

求两个数的较大值的示例：

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

-- 测试
select pymax(14,5);

--  pymax 
-- -------
--     14
-- (1 row)

CREATE FUNCTION pystrip(x text)
  RETURNS text
AS $$
  global x # 
  x = x.strip()  # ok now
  return x
$$ LANGUAGE plpythonu;

参考资料

打赏作者

2016-10-24 » NNML（03）：BP 学习
2016-10-16 » NNML（02）：感知器学习
2016-10-09 » 鲁棒及自适应控制（2）：模型
2016-10-09 » NNML（01）：引言
2016-09-24 » 家用监控设备用于电网的可行性分析
2016-09-19 » 无人机电缆隧道巡检可行性调研报告
2016-09-18 » 鲁棒及自适应控制（1）：概论
2016-09-13 » Gradient-Based Learning Applied to Document Recognition

目录

基本应用

入门

数据类型1

查询操作

GROUP BY & HAVING

嵌套查询

模式匹配2

用户管理

数据库管理

表管理

创建表

修改表3

数据操作

表之间的连接

实用技术

psql运行SQL文件

文件与表单数据的导入与导出

基本配置

高级应用

视图

外键

继承

事务

窗函数

WITH查询

聚合函数

基本用法4

自定义聚合函数

值表达式5

PL/pgSQL6

PL/Python7

参考资料

数据类型¹

`GROUP BY` & `HAVING`

模式匹配²

修改表³

`WITH`查询

基本用法⁴

值表达式⁵

PL/pgSQL⁶

PL/Python⁷