PostgreSQL的使用

基础命令

initdb 命令

#### 介绍

initdb 用于初始化一个 PostgreSQL 数据库簇。

语法

initdb [选项]... [DATADIR]

选项说明

非普通使用选项

其他选项

注意： 如果没有指定数据目录，将使用环境变量 PGDATA。

示例

# 初始化数据库
initdb -D /path/to/data

pg_ctl 命令

介绍

pg_ctl 是用于初始化、启动、停止或控制 PostgreSQL 服务器的工具。

语法

pg_ctl init[db]   [-D 数据目录] [-s] [-o 选项]
pg_ctl start      [-D 数据目录] [-l 文件名] [-W] [-t 秒数] [-s] [-o 选项] [-p 路径] [-c]
pg_ctl stop       [-D 数据目录] [-m SHUTDOWN-MODE] [-W] [-t 秒数] [-s]
pg_ctl restart    [-D 数据目录] [-m SHUTDOWN-MODE] [-W] [-t 秒数] [-s] [-o 选项] [-c]
pg_ctl reload     [-D 数据目录] [-s]
pg_ctl status     [-D 数据目录]
pg_ctl promote    [-D 数据目录] [-W] [-t 秒数] [-s]
pg_ctl logrotate  [-D 数据目录] [-s]
pg_ctl kill       信号名称 进程号
pg_ctl register   [-D 数据目录] [-N 服务名称] [-U 用户名] [-P 口令] [-S 启动类型] [-e 源] [-W] [-t 秒数] [-s] [-o 选项]
pg_ctl unregister [-N 服务名称]

普通选项

注意： 如果省略了 -D 选项，将使用 PGDATA 环境变量。

启动或重启的选项

停止或重启的选项

关闭模式说明

允许关闭的信号名称

ABRT HUP INT KILL QUIT TERM USR1 USR2

注册或注销的选项

启动类型

示例

# 启动 PostgreSQL
pg_ctl start -D /path/to/data
​
# 注册为系统服务
pg_ctl register -N "PostgreSQL" -D "/path/to/data"

psql 命令

介绍

psql 是 PostgreSQL 的交互式客户端工具。

语法

psql [选项]... [数据库名称 [用户名称]]

通用选项

输入和输出选项

输出格式选项

连接选项

示例

# 连接 PostgreSQL，默认数据库和用户为 postgres
psql -h host -p port -U username -d dbname
​
# 连接 PostgreSQL 并执行 SQL 文件
psql -h host -p port -U username -d dbname -f xxx.sql

数据库操作

查询所有数据库

\\l

创建数据库

语法

CREATE DATABASE 名称
    [ WITH ] [ OWNER [=] 用户名 ]
           [ TEMPLATE [=] 模版 ]
           [ ENCODING [=] 字符集编码 ]
           [ STRATEGY [=] strategy ]
           [ LOCALE [=] 本地化语言 ]
           [ LC_COLLATE [=] 排序规则 ]
           [ LC_CTYPE [=] 字符分类 ]
           [ ICU_LOCALE [=] icu_locale ]
           [ LOCALE_PROVIDER [=] locale_provider ]
           [ COLLATION_VERSION = collation_version ]
           [ TABLESPACE [=] 表空间的名称 ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] 连接限制 ]
           [ IS_TEMPLATE [=] istemplate ]
           [ OID [=] oid ]

示例

-- 本地创建数据库
CREATE DATABASE dbname;
​
-- 远程创建数据库
createdb -h host -p port -U username dbname;

删除数据库

语法

DROP DATABASE [ IF EXISTS ] 名称 [ [ WITH ] ( 选项 [, ...] ) ]

选项

示例

DROP DATABASE IF EXISTS dbname;

修改数据库

语法

ALTER DATABASE 名称 [ [ WITH ] 选项 [ ... ] ]
​
ALTER DATABASE 名称 RENAME TO 新的名称
​
ALTER DATABASE 名称 OWNER TO { 新的属主 | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
​
ALTER DATABASE 名称 SET TABLESPACE 新的表空间
​
ALTER DATABASE 名称 REFRESH COLLATION VERSION
​
ALTER DATABASE 名称 SET 配置参数 { TO | = } { 值 | DEFAULT }
ALTER DATABASE 名称 SET 配置参数 FROM CURRENT
ALTER DATABASE 名称 RESET 配置参数
ALTER DATABASE 名称 RESET ALL

选项

示例

ALTER DATABASE dbname RENAME TO newdbname;

切换数据库

\\c dbname

表操作

创建表

语法

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] 表名 ( [
  { 列名称 数据类型 [ COMPRESSION 压缩方法 ] [ COLLATE 校对规则 ] [ 列约束 [ ... ] ]
    | 表约束
    | LIKE 源表 [ like选项 ... ] }
    [, ... ]
] )
[ INHERITS ( 父表 [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { 列名称 | ( 表达式 ) } [ COLLATE 校对规则 ] [ 操作符类型的名称 ] [, ... ] ) ]
[ USING 方法 ]
[ WITH ( 存储参数 [= 值] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE 表空间的名称 ]

列约束

[ CONSTRAINT 约束名称 ]
{ NOT NULL |
  NULL |
  CHECK ( 表达式 ) [ NO INHERIT ] |
  DEFAULT 默认表达式 |
  GENERATED ALWAYS AS ( 生成表达式 ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( 序列选项 ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] 索引参数 |
  PRIMARY KEY 索引参数 |
  REFERENCES 所引用的表 [ ( 所引用的列 ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE 参考行动 ] [ ON UPDATE 参考行动 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

表约束

[ CONSTRAINT 约束名称 ]
{ CHECK ( 表达式 ) [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( 列名称 [, ... ] ) 索引参数 |
  PRIMARY KEY ( 列名称 [, ... ] ) 索引参数 |
  EXCLUDE [ USING 访问索引的方法 ] ( 排除项 WITH 运算符 [, ... ] ) 索引参数 [ WHERE ( 谓词 ) ] |
  FOREIGN KEY ( 列名称 [, ... ] ) REFERENCES 所引用的表 [ ( 所引用的列 [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE 参考行动 ] [ ON UPDATE 参考行动 ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

示例

CREATE TABLE t_name (
    字段名1 数据类型,
    字段名2 数据类型,
    PRIMARY KEY (字段名1, 字段名2, ...)
);

查看表结构

\\d

删除表

语法

-- 删除一个表
DROP TABLE [ IF EXISTS ] table_name;
​
-- 删除多个表
DROP TABLE [ IF EXISTS ] table1_name, table2_name;

修改表结构

新增一列

ALTER TABLE table_name ADD 字段名 数据类型;

删除一列

ALTER TABLE table_name DROP COLUMN 字段名;

修改某列的数据类型

ALTER TABLE table_name ALTER COLUMN 字段名 TYPE 数据类型;

新增 NOT NULL 约束

ALTER TABLE table_name ALTER 字段名 数据类型 NOT NULL;

新增约束

-- 主键
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2, ...);
​
-- 外键
ALTER TABLE table_name ADD CONSTRAINT MyForeignKey FOREIGN KEY (字段名) REFERENCES 主键表名(主键字段);
​
-- 唯一约束
ALTER TABLE table_name ADD CONSTRAINT MyUnique UNIQUE (column1, column2, ...);
​
-- 检查约束
ALTER TABLE table_name ADD CONSTRAINT MyCheck CHECK (条件);

删除约束

ALTER TABLE table_name DROP CONSTRAINT 约束名称;

数据操作

新增数据

语法

-- 插入一行指定字段的值
INSERT INTO TABLE_NAME (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
​
-- 插入一行全部字段的值
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, ...);
​
-- 插入多行指定字段的值
INSERT INTO TABLE_NAME (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...);
​
-- 插入多行全部字段的值
INSERT INTO TABLE_NAME VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...);

删除数据

语法

-- 删除整张表数据
DELETE FROM TABLE_NAME;
​
-- 删除指定条件的数据
DELETE FROM TABLE_NAME WHERE [condition];

修改数据

语法

UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
WHERE [condition];

查询数据

基础查询

-- 查询所有字段（注意：使用 * 会导致索引失效）
SELECT * FROM table_name;
​
-- 查询指定字段
SELECT column1, column2, ... FROM table_name;

WHERE 子句

SELECT column1, column2, ... FROM table_name WHERE [condition];

LIKE 模糊查询

-- _：单一字符（有且只有一个）
-- 例如：_aaa 匹配 column1 第一位任意，第2、3、4位为 aaa 的所有数据
SELECT * FROM table_name WHERE column1 LIKE '_aaa';
​
-- %：通配符（任意多个字符）
-- 例如：%aaa 匹配 column1 结尾为 aaa 的所有数据
SELECT * FROM table_name WHERE column1 LIKE '%aaa';
​
-- 组合使用：_aaa% 匹配 column1 以 xaaa 开头的数据
SELECT * FROM table_name WHERE column1 LIKE '_aaa%';

WITH 子句（CTE）

介绍

WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。这些语句通常称为通用表表达式（CTE），可以当作一个为查询而存在的临时表。

语法

WITH
   name_for_summary_data AS (
      SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
  SELECT column
  FROM name_for_summary_data)
[ORDER BY columns]

示例

WITH CTE AS (
  SELECT
    column1,
    column2,
    column3,
    column4
  FROM XXX
)
SELECT * FROM CTE;

LIMIT 分页

语法

SELECT column1, column2, ...
FROM table_name
LIMIT [no of rows] | LIMIT [no of rows] OFFSET [row num];

示例

-- 第一页，每页4条
SELECT * FROM table_name LIMIT 4 OFFSET 0;
​
-- 第二页，每页4条
SELECT * FROM table_name LIMIT 4 OFFSET 4;

HAVING 子句

SELECT column1, column2, ...
FROM table_name
WHERE [conditions]
GROUP BY column1, column2, ...
HAVING [conditions]
ORDER BY column1, column2, ...;

AND / OR 条件

SELECT column1, column2, ...
FROM table_name
WHERE [condition1] AND/OR [condition2] AND/OR [conditionN];

ORDER BY 排序

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, ...] [ASC | DESC];

GROUP BY 分组

SELECT column1, column2, ...
FROM table_name
WHERE [conditions]
GROUP BY column1, column2, ...;

AS 别名

SELECT DISTINCT column1 AS COL1, column2 AS COL2, ... FROM table_name;

DISTINCT 去重

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE [condition];

COUNT 计数

SELECT COUNT(1) / COUNT(*) / COUNT(字段)
FROM table_name
WHERE [condition];

子查询

SELECT 子查询

语法

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

示例

SELECT * FROM USER WHERE ID IN (SELECT * FROM USER WHERE AGE > 20);

INSERT 子查询

语法

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

示例

INSERT INTO USER1 SELECT * FROM USER WHERE ID IN (SELECT * FROM USER WHERE AGE > 20);

UPDATE 子查询

语法

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

示例

UPDATE USER username="aaa" WHERE ID IN (SELECT * FROM USER WHERE AGE > 20);

DELETE 子查询

语法

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

示例

DELETE FROM USER WHERE ID IN (SELECT * FROM USER WHERE AGE > 20);

多表联查

内连接（INNER JOIN）

笛卡尔积形式

SELECT column_name1, column_name2
FROM   table1, table2
WHERE  table1.column = table2.column;

JOIN 形式

SELECT column_name1, column_name2
FROM table1 (INNER) JOIN table2 ON table1.column = table2.column;

左连接（LEFT JOIN）

SELECT column_name1, column_name2
FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

右连接（RIGHT JOIN）

SELECT column_name1, column_name2
FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

外连接

左外连接

-- Oracle 风格
SELECT column_name1, column_name2
FROM   table1, table2
WHERE  table1.column = table2.column(+);
​
-- 标准 SQL
SELECT column_name1, column_name2
FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;

右外连接

-- Oracle 风格
SELECT column_name1, column_name2
FROM   table1, table2
WHERE  table1.column(+) = table2.column;
​
-- 标准 SQL
SELECT column_name1, column_name2
FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;

交叉连接（CROSS JOIN）

SELECT column_name1, column_name2
FROM table1 CROSS JOIN table2;

全连接（FULL JOIN）

SELECT column_name1, column_name2
FROM table1 FULL JOIN table2 ON table1.column = table2.column;

完整查询语法

语法

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE [conditions]
GROUP BY column1, column2, ...
HAVING [conditions]
ORDER BY column1, column2, ... [ASC|DESC]
LIMIT [no of rows] OFFSET [row num];

执行顺序

1. FROM 子句：标识出查询的主表及其关联表

2. JOIN 子句：如果有的话，执行联结

3. ON 子句：对 JOIN 的条件进行过滤

4. WHERE 子句：对记录进行过滤

5. GROUP BY 子句：按指定的列分组记录

6. HAVING 子句：对分组的结果进行过滤

7. SELECT 子句：选取特定的列

8. DISTINCT 子句：去除重复数据

9. ORDER BY 子句：按指定的列排序结果

10. LIMIT 子句和 OFFSET 子句：限制结果集数量和偏移量