Install PostgreSQL on Mac OS

安装

➜  brew install postgres
==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.4.4.yosemite.bottle.tar.
######################################################################## 100.0%
==> Pouring postgre<kbd>b</kbd>sql-9.4.4.yosemite.bottle.tar.gz
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.4/static/upgrading.html


To have launchd start postgresql at login:
    ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
Then to load postgresql now:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Or, if you don't want/need launchctl, you can just run:
    postgres -D /usr/local/var/postgres
==> /usr/local/Cellar/postgresql/9.4.4/bin/initdb /usr/local/var/postgres
==> Summary
🍺  /usr/local/Cellar/postgresql/9.4.4: 3014 files, 40M

创建

initdb /usr/local/var/postgres
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

配置

vi .zshrc

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin
alias postgres.server="sudo -u postgres pg_ctl -D /Library/PostgreSQL/9.4/data"

启动停止服务

postgres.server start
postgres.server stop

创建数据库

# 创建默认名为user的数据库
createdb
# 创建指定名为play的数据库
createdb play
# 用superuser创建数据库
createdb -U postgres databasename

删除数据库

dropdb -U postgres databasename

创建用户赋予权限

createuser -U postgres username
psql database -c "GRANT ALL ON ALL TABLES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to user;"

命令行

# user的命令行
psql
# play的命令行
psql play
# 查看所有数据库
psql -l

# 以某个用户连接某个数据库
psql -U username -d databasename

# 查看版本号

select version()
PostgreSQL 9.5.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit
select postgis_full_version()
POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.2" LIBJSON="0.11" RASTER

alter table cities add id serial primary key;

PostgreSQL 高级特性

视图

1
2
3
4
5
6
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;

外键

事务

BEGIN, COMMIT, ROLLBACK TO, SAVEPOINT

事务是所有数据库系统的一个基本概念。一次事务的要点就是把多个步骤捆绑成一个单一的、 不成功则成仁的操作。其它并发的事务是看不到在这些步骤之间的中间状态的,并且如果发生了一些问题, 导致该事务无法完成,那么所有这些步骤都完全不会影响数据库。

事务被认为是原子的: 从其它事务的角度来看,它要么是全部发生,要么完全不发生。

一个事务型数据库保证一个事务所做的所有更新在事务发出完成响应之前都记录到永久的存储中(也就是磁盘)。

事务型数据库的另外一个重要的性质和原子更新的概念关系密切:当多个事务并发地运行的时候, 每个事务都不应看到其它事务所做的未完成的变化。

一个打开的事务所做的更新在它完成之前是无法被其它事务看到的,而到提交的时候所有更新同时可见。

1
2
3
4
5
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- 等等
COMMIT;

如果在该事务的过程中,我们决定不做提交(可能是我们刚发现 Alice 的余额是负数), 那么我们可以发出ROLLBACK而不是COMMIT命令, 那么到目前为止我们的所有更新都会被取消。

PostgreSQL 实际上把每个 SQL 语句当做在一个事务中执行来看待。 如果你没有发出BEGIN命令,那么每个独立的语句都被一个隐含的BEGIN 和(如果成功的话)COMMIT包围。一组包围在BEGIN和COMMIT 之间的语句有时候被称做事务块。

我们可以通过使用保存点的方法,在一个事务里更加精细地控制其中的语句。 保存点允许你选择性地抛弃事务中的某些部分,而提交剩下的部分。在用SAVEPOINT 定义了一个保存点后,如果需要,你可以使用ROLLBACK TO回滚到该保存点。 则该事务在定义保存点到 ROLLBACK TO 之间的所有数据库更改都被抛弃, 但是在保存点之前的修改将被保留。

窗口函数

OVER
聚合函数+over()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
算出每个部门的平均工资

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) FROM empsalary;
rank:每个部门人员的权重,按照over里的方式排序

SELECT depname, empno, salary, enroll_date, pos
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;

子查询:查询rank<3的行

继承

INHERITS

1
2
3
4
5
6
7
8
9
CREATE TABLE cities (
name text,
population real,
altitude int -- (单位是英尺)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

capitals继承了其父表 cities的所有字段,州首府有一个额外的字段 state显示其所处的州

词法结构

标识符和关键字

SQL 标识符和关键字必须以一个字母(a-z 以及带变音符的字母和非拉丁字母)或下划线开头, 随后的字符可以是字母、下划线、数字(0-9)、 美元符号($)

常量

1
CREATE FUNCTION empsalary(int) RETURNS empsalary                                                                                                                                                                                                                           AS $$ SELECT * FROM empsalary WHERE empno = $1 $$                                                                                                                                                                                                                               LANGUAGE SQL;

调用函数

位置表示法

1
2
3
4
5
6
7
 CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)                                                                                                                                                                                   RETURNS text                                                                                                                                                                                                                                                                    AS                                                                                                                                                                                                                                                                              $$                                                                                                                                                                                                                                                                                SELECT CASE                                                                                                                                                                                                                                                                            WHEN $3 THEN UPPER($1 || ' ' || $2)                                                                                                                                                                                                                                             ELSE LOWER($1 || ' ' || $1)                                                                                                                                                                                                                                                     END;                                                                                                                                                                                                                                                                   $$                                                                                                                                                                                                                                                                              LANGUAGE SQL IMMUTABLE STRICT;

select concat_lower_or_upper('a', 'b');
concat_lower_or_upper
-----------------------
a a
(1 row)

名称表示法

1
2
3
4
5
SELECT concat_lower_or_upper(a := 'Hello', b := 'World', uppercase := true);
concat_lower_or_upper
-----------------------
HELLO WORLD
(1 row)

混合表示法

1
2
3
4
5
SELECT concat_lower_or_upper('Hello', 'World', uppercase := true);
concat_lower_or_upper
-----------------------
HELLO WORLD
(1 row)

数据定义

缺省值

1
2
3
4
CREATE TABLE products (
product_no integer DEFAULT nextval('products_product_no_seq'),
...
);

缩写

1
2
3
4
CREATE TABLE products (
product_no SERIAL,
...
);

约束

  • 检查约束
    • CHECK (price > 0) 字段约束
    • CONSTRAINT positive_price CHECK (price > 0) 命名约束
    • CHECK (price > discounted_price) 表约束
    • CHECK (discounted_price > 0 AND price > discounted_price)
  • 非空约束
    • NOT NULL
  • 唯一约束
    • UNIQUE
    • UNIQUE (a, b, c)
  • 主键
    • PRIMARY KEY (与 UNIQUE NOT NULL 等价)
    • PRIMARY KEY (a, b, c)
  • 外键
    • REFERENCES products (product_no) 如果缺少字段列表的话,就会引用被引用表的主键
    • FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
    • ON DELETE RESTRICT 限制删除:禁止删除被引用的行
    • ON DELETE CASCADE 级联删除:在删除一个被引用的行的时候,所有引用它的行也会被自动删除
    • SET NULL 删除后的设置为 NULL
    • SET DEFAULT 删除后设置为 默认值DEFAULT
  • 排除约束
    • EXCLUDE

修改表

  • 增加字段
    • ALTER TABLE products ADD COLUMN description text;
  • 删除字段
    • 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 ALTER COLUMN product_no SET NOT NULL;
  • 删除约束
    • ALTER TABLE products DROP CONSTRAINT some_name;
    • ALTER TABLE products DROP CONSTRAINT some_name CASCADE;
    • 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;

架构schema

自定义schema

用途:

  • 允许多个用户使用一个数据库而不会干扰其它用户。
  • 把数据库对象组织成逻辑组,让它们更便于管理。
  • 第三方的应用可以放在不同的模式中,这样它们就不会和其它对象的名字冲突。
1
2
3
4
5
6
7
8
database.schema.table
CREATE SCHEMA myschema;
CREATE TABLE myschema.mytable (
...
);
DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;
CREATE SCHEMA schemaname AUTHORIZATION username;

The Public Schema

CREATE TABLE products ( … );
CREATE TABLE public.products ( … );

The Schema Search Path

SHOW search_path;
SET search_path TO myschema,public;
DROP TABLE mytable;

Schemas and Privileges

1
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

不允许所有用户有CREATE的权限,第一个”public”是schema,第二个”PUBLIC”意思是”所有用户”

The System Catalog Schema

pg_catalog 它包含系统表和所有内置数据类型、函数、操作符

数据操作

1
2
3
4
5
6
7
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;
DELETE FROM products WHERE price = 10;

查询

表连接

  • CROSS JOIN (卡笛尔积 Cartesian product)
  • INNER JOIN ON
  • INNER JOIN USING
  • NATURAL INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

子查询

子查询的结果(派生表)必须包围在圆括弧里并且必须赋予一个别名

表函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

LATERAL Subqueries

1
2
3
4
5
6
7
8
9
10
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

The GROUP BY and HAVING Clauses

1
2
3
4
5
6
SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;

GROUPING SETS, CUBE, and ROLLUP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
=> SELECT * FROM items_sold;
brand | size | sales
-------+------+-------
Foo | L | 10
Foo | M | 20
Bar | M | 15
Bar | L | 5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
(5 rows)

Combining Queries

对两个查询的结果进行集合操作(并、交、差)

  • query1 UNION [ALL] query2
  • query1 INTERSECT [ALL] query2
  • query1 EXCEPT [ALL] query2

Sorting Rows

1
2
3
4
SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
[, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

LIMIT and OFFSET

1
2
3
4
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]

数据类型

PostgreSQL有着丰富的内置数据类型可用。 用户还可以使用CREATE TYPE命令增加新的数据类型。

名字 别名 描述
bigint int8 有符号8字节整数
bigserial serial8 自增8字节整数
bit [ (n) ] 定长位串
bit varying [ (n) ] varbit 可变长位串
boolean bool 逻辑布尔值(真/假)
box 平面上的矩形
bytea 二进制数据(“字节数组”)
character varying [ (n) ] varchar [ (n) ] 可变长字符串
character [ (n) ] char [ (n) ] 定长字符串
cidr IPv4 或 IPv6 网络地址
circle 平面上的圆
date 日历日期(年, 月, 日)
double precision float8 双精度浮点数(8字节)
inet IPv4 或 IPv6 主机地址
integer int, int4 有符号 4 字节整数
interval [ fields ] [ (p) ] 时间间隔
line 平面上的无限长直线
lseg 平面上的线段
macaddr MAC (Media Access Control)地址
money 货币金额
numeric [ (p, s) ] decimal [ (p, s) ] 可选精度的准确数值数据类型
path 平面上的几何路径
point 平面上的点
polygon 平面上的封闭几何路径
real float4 单精度浮点数(4 字节)
smallint int2 有符号 2 字节整数
smallserial serial2 自增 2 字节整数
serial serial4 自增 4 字节整数
text 可变长字符串
time [ (p) ] [ without time zone ] 一天中的时刻(无时区)
time [ (p) ] with time zone timetz 一天中的时刻,含时区
timestamp [ (p) ] [ without time zone ] 日期与时刻(无时区)
timestamp [ (p) ] with time zone timestamptz 日期与时刻,含时区
tsquery 文本检索查询
tsvector 文本检索文档
txid_snapshot 用户级别的事务ID快照
uuid 通用唯一标识符
xml XML 数据
json JSON 数据

如果你要求精确的计算(比如计算货币金额),应使用numeric(decimal)类型

并发控制

  • MVCC (Multiversion Concurrency Control)
  • Table- and row-level locking

transaction isolation

Isolation Level Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly
Read uncommitted Allowed, but not in PG Possible Possible Possible
Read committed Not possible Possible Possible Possible
Repeatable read Not possible Not possible Allowed, but not in PG Possible
Serializable Not possible Not possible Not possible Not possible

PostgreSQL 中默认的隔离级别是Read committed

默认函数

random()
rank()

如果我的文章对你有很大帮助 那么不妨?
0%