1
2
3
4
5
6
7

\PostgreSQL\9.5\data\postgresql.conf
listen_address = 'localhost'

\PostgreSQL\9.5\bin\psql.exe –U postgres
\PostgreSQL\9.5\bin\psql.exe –U postgres -d mydb1

\q

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 创建数据库
CREATE DATABASE 数据库名;

-- 创建表
CREATE TABLE 表名
(列名 数据类型 数据约束,
列名 数据类型 数据约束,
...
表约束, 表约束, ...);
-- 数据约束: NOT NULL, DEFUALT 值
-- 表约束: PRIMARY KEY (列名)

-- 删除表
DROP TABLE 表名;

-- 变更表名
ALTER TABLE 旧表名 RENAME TO 新表名

-- 添加表列
ALTER TABLE 表名 ADD COLUMN 列定义;

-- 删除表列
ALTER TABLE 表名 DROP COLUMN 列名;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 插入新数据
INSERT INTO 表名 (列清单) VALUES (值清单);

-- 复制数据
INSERT INTO 新表名 (列清单)
SELECT 列清单
  FROM 源表名

-- 删除数据
DELETE FROM 表名
  WHERE 条件;
-- 整行

-- 更改数据
UPDATE 表名
  SET 列 = 运算式;
      ...,
 WHERE 条件;

-- 事务(transaction)
-- ACID: atomicity, consistency, isolaction, durability
BEGIN TRANSACTION;
DML语句;
...
COMMIT; -- 提交
ROLLBACK; -- 回滚

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- e.g.
CREATE DATABASE shop;

CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id));

BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 列查询
SELECT 运算式, 运算式, ...
  FROM 表名;

-- 全部列
SELECT *
  FROM 表名;

-- 去除重复行
SELECT DISTINCT 运算式, ...
  FROM 表名;

-- 列别名
SELECT 运算式 AS "中文列名",
       运算式 AS 英文列名,
       ...
  FROM 表名;
-- NULL运算均会得到NULL

-- 常数
SELECT 常数 AS 列名,
       ...
  FROM 表名;

-- 行条件
SELECT 运算式, ...
  FROM 表名
 WHERE 条件;

/* 比较运算符
= 相等
<> 不相等
IS NULL 值为NULL
IS NOT NULL 值不为NULL */

/* 逻辑运算符
NOT 非
AND 和
OR 或 */

/* 逻辑值
TRUE
FALSE
UNKNOWN 判断NULL时 */

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 聚合函数: COUNT, SUM, AVG, MAX, MIN

-- 分组
SELECT 表达式, ...
  FROM 表名
 GROUP BY 列名(聚合键), ...
HAVING 条件;

-- 排序
SELECT 表达式, ...
  FROM 表名
 ORDER BY 列名(排序键) ASC, ...
-- 缺省/ASC为升序, DESC为降序

/* 书写顺序: SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY
执行顺序: FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY
WHERE为行指定条件, HAVING为聚合结果指定条件
使用聚合时SELECT只能包含常数, 聚合函数, 聚合键
GROUP BY不能有别名, 结果无序
WHERE中不能使用聚合函数, GROUP BY和HAVING可以 */

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- e.g.
SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC, product_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

/* 视图: 存储SELECT语句的表
避免视图嵌套, 定义视图不能使用ORDER BY, 视图和表需要同时更新 */

-- 创建视图
CREATE VIEW 视图名 (视图列名, ...)
AS
SELECT语句;

-- 删除视图
DROP VIEW 视图名;

-- 子查询: 临时视图
(SELECT 运算式 AS 视图列名, ...
   WHERE 条件
  FROM 表名
   GROUP BY 列名(聚合键), ...
  HAVING 条件) AS 视图名

/* 标量子查询: 只返回值, 可用于表达式
SELECT需要包含常数, 聚合键, 聚合函数以外的运算式时
WHERE需要使用聚合函数时
HAVING使用聚合函数需要作用范围不同时 */

-- 关联子查询: 细分组内比较
    (SELECT 运算式 AS 视图列名, ...
	   FROM 表名 AS 别名
	  WHERE 别名.列名 = 别名.列名) -- 结合条件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- e.g.
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

SELECT product_type, cnt_product
FROM ProductSum;

SELECT product_type, cnt_product
  FROM (SELECT product_type, COUNT(*) AS cnt_product
          FROM Product
         GROUP BY product_type ) AS ProductSum;

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
                            FROM Product);

SELECT product_type, product_name, sale_price
  FROM Product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM Product AS P2
                      WHERE P1.product_type = P2.product_type
                    GROUP BY product_type);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

/* 函数: 输入参数, 输出返回值
数值: ABS, MOD, ROUND
字符串: LENGTH, CONCAT, LOWER, UPPER, REPLACE, SUBSTRING(... FROM ... FOR ...)
日期: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT(... FROM ...)
转换: CAST(... AS ...), COALESCE(NULL, ... )返回第一个不为NULL值 */

/* 谓词: 返回逻辑值的函数
模式匹配: LIKE, 前方一致 '...%', 中间一致 '%...%', 后方一致 '%...'
范围: BETWEEN ... AND ...
指定/排除多个: IN (..., ...), NOT IN(..., ...), 输入参数可为表
判断记录存在: EXISTS, NOT EXISTS */

-- 条件分支
CASE WHEN 条件 THEN 表达式
     ...
     ELSE 表达式
END

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- e.g.
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));

BEGIN TRANSACTION;
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;

SELECT product_name, sale_price
  FROM Product
 WHERE product_id NOT IN (SELECT product_id
                            FROM ShopProduct
                          WHERE shop_id = '000A');

SELECT product_name, sale_price
  FROM Product AS P
 WHERE NOT EXISTS (SELECT *
                     FROM ShopProduct AS SP
                    WHERE SP.shop_id = '000A'
                      AND SP.product_id = P.product_id);

SELECT SUM(CASE WHEN product_type = '衣服'
                THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具'
                THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品'
                THEN sale_price ELSE 0 END) AS sum_price_office
  FROM Product;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

/* 行运算: UNION, INTERSECT, EXCEPT
运算式数量及类型一致, ORDER BY只能加在最后
保留重复行: 行运算+ALL */
SELECT 运算式
  FROM 表名
行运算
SELECT 运算式
  FROM 表名;

/* 列运算
内联结: JOIN 同时存在
外联结: RIGHT/LEFT OUTER JOIN 决定主表
交叉联结: CROSS JOIN 直积 */
SELECT 别名.运算式, ...
  FROM 表名 AS 别名
    INNER JOIN ... ON 条件(联结键)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- e.g.
SELECT product_id, product_name
  FROM Product2
EXCEPT
SELECT product_id, product_name
  FROM Product
ORDER BY product_id;

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

/* 窗口函数 OLAP(OnLine Analytical Processing)
排序: RANK()相同位次跳过, DENSE_RANK相同位次不跳过, ROW_NUMBER唯一连续位次
聚合: SUM累计, AVG移动平均 ROWS BETWEEN ... PRECEDING AND ... FOLLOWING框架 */

-- 多级聚合
GROUP BY 列名, ... WITH ROLLUP -- 相当于如下
GROUP BY ( ), GROUP BY (列名1), GROUP BY(列名1, 列名2), ...

-- 区分聚合与原值
GROUPING (列名) -- 超级分组返回1, 原值NULL返回0
GROUPING(列名1, 列名2, 列名3) -- 相当于如下
GROUPING(列名3) + (GROUPING(列名2) << 1) + (GROUPING(列名3) << 2)

-- 积木 (多维)
GROUP BY 列名, ... WITH CUBE
-- 取得全部组合的聚合结果: 2^(列名数)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- e.g.
SELECT product_name, product_type, sale_price,
    RANK () OVER (ORDER BY sale_price) AS ranking
  FROM Product
 ORDER BY ranking;

SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

SELECT CASE WHEN GROUPING(product_type) = 1
            THEN '商品种类 合计'
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1
            THEN '登记日期 合计'
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);