PostgreSQL最常用的函数-查询(最全)

太过爱你忘了你带给我的痛 2024-03-31 16:22 98阅读 0赞

**基本**

切换和连接

$ sudo -u postgres psql

列出所有数据库

postgres=\# \\l

连接到名为 postgres 的数据库

postgres=\# \\c postgres

断开

postgres=\# \\q

postgres=\# \\!

**psql** **命令**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 参数 </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 示例 </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> [-d] &lt;database&gt; </td> 
 <td style="vertical-align:top;"> psql -d mydb </td> 
 <td style="vertical-align:top;"> 连接到数据库 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -U </td> 
 <td style="vertical-align:top;"> psql -U john mydb </td> 
 <td style="vertical-align:top;"> 以特定用户身份连接 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -h -p </td> 
 <td style="vertical-align:top;"> psql -h localhost -p 5432 mydb </td> 
 <td style="vertical-align:top;"> 连接到主机/端口 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -U -h -p -d </td> 
 <td style="vertical-align:top;"> psql -U admin -h 192.168.1.5 -p 2506 -d mydb </td> 
 <td style="vertical-align:top;"> 连接远程 PostgreSQL </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -W </td> 
 <td style="vertical-align:top;"> psql -W mydb </td> 
 <td style="vertical-align:top;"> 强制密码 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -c </td> 
 <td style="vertical-align:top;"> psql -c '\c postgres' -c '\dt' </td> 
 <td style="vertical-align:top;"> 执行 SQL 查询或命令 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -H </td> 
 <td style="vertical-align:top;"> psql -c "\l+" -H postgres &gt; database.html </td> 
 <td style="vertical-align:top;"> 生成 HTML 报告 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -l </td> 
 <td style="vertical-align:top;"> psql -l </td> 
 <td style="vertical-align:top;"> 列出所有数据库 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -f </td> 
 <td style="vertical-align:top;"> psql mydb -f file.sql </td> 
 <td style="vertical-align:top;"> 从文件执行命令 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> -V </td> 
 <td style="vertical-align:top;"> psql -V </td> 
 <td style="vertical-align:top;"> 打印 psql 版本 </td> 
 </tr> 
 </tbody> 
</table>

**获得帮助**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \h </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> SQL 命令语法帮助 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \h DELETE </td> 
 <td style="vertical-align:top;"> DELETE SQL 语句语法 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \? </td> 
 <td style="vertical-align:top;"> PostgreSQL 命令列表 </td> 
 </tr> 
 </tbody> 
</table>

在 PostgreSQL 控制台中运行

**PostgreSQL** **工作**

**Recon** **观察**

显示版本

SHOW SERVER\_VERSION;

显示系统状态

\\conninfo

显示环境变量

SHOW ALL;

列出用户

SELECT rolname FROM pg\_roles;

显示当前用户

SELECT current\_user;

显示当前用户的权限

\\du

显示当前数据库

SELECT current\_database();

显示数据库中的所有表

\\dt

列出函数

\\df <schema>

**Databases** **数据库**

列出数据库

\\l

连接到数据库

\\c <database\_name>

显示当前数据库

SELECT current\_database();

创建数据库

CREATE DATABASE <database\_name> WITH OWNER <username>;

删除数据库

DROP DATABASE IF EXISTS <database\_name>;

重命名数据库

ALTER DATABASE <old\_name> RENAME TO <new\_name>;

**Tables** **表**

列出当前数据库中的表

\\dt

SELECT table\_schema,table\_name FROM information\_schema.tables ORDER BY table\_schema,table\_name;

全局列表

\\dt \*.\*.

SELECT \* FROM pg\_catalog.pg\_tables

列表表架构

\\d <table\_name>

\\d+ <table\_name>

SELECT column\_name, data\_type, character\_maximum\_length

FROM INFORMATION\_SCHEMA.COLUMNS

WHERE table\_name = '<table\_name>';

创建表

CREATE TABLE <table\_name>(

<column\_name> <column\_type>,

<column\_name> <column\_type>

);

创建表，主键自增

CREATE TABLE <table\_name> (

<column\_name> SERIAL PRIMARY KEY

);

删除表

DROP TABLE IF EXISTS <table\_name> CASCADE;

**Permissions** **权限**

成为 postgres 用户，如果您有权限错误

sudo su - postgres

psql

授予 对数据库的所有权限

GRANT ALL PRIVILEGES ON DATABASE <db\_name> TO <user\_name>;

授予数据库连接权限

GRANT CONNECT ON DATABASE <db\_name> TO <user\_name>;

授予架构权限

GRANT USAGE ON SCHEMA public TO <user\_name>;

授予函数权限

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user\_name>;

授予在所有表上选择、更新、插入、删除的权限

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user\_name>;

在表上授予权限

GRANT SELECT, UPDATE, INSERT ON <table\_name> TO <user\_name>;

授予对表的选择权限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user\_name>;

**Columns** **列**

添加栏目

ALTER TABLE <table\_name> IF EXISTS

ADD <column\_name> <data\_type> \[<constraints>\];

更新栏

ALTER TABLE <table\_name> IF EXISTS

ALTER <column\_name> TYPE <data\_type> \[<constraints>\];

删除列

ALTER TABLE <table\_name> IF EXISTS

DROP <column\_name>;

将列更新为自增主键

ALTER TABLE <table\_name>

ADD COLUMN <column\_name> SERIAL PRIMARY KEY;

使用自动递增的主键插入表中

INSERT INTO <table\_name>

VALUES (DEFAULT, <value1>);

INSERT INTO <table\_name> (<column1\_name>,<column2\_name>)

VALUES ( <value1>,<value2> );

**Data** **数据**

选择 所有数据

SELECT \* FROM <table\_name>;

读取一行数据

SELECT \* FROM <table\_name> LIMIT 1;

搜索数据

SELECT \* FROM <table\_name> WHERE <column\_name> = <value>;

插入 数据

INSERT INTO <table\_name> VALUES( <value\_1>, <value\_2> );

更新 数据

UPDATE <table\_name>

SET <column\_1> = <value\_1>, <column\_2> = <value\_2>

WHERE <column\_1> = <value>;

删除 所有数据

DELETE FROM <table\_name>;

删除特定数据

DELETE FROM <table\_name>

WHERE <column\_name> = <value>;

**Users** **用户**

列出角色

SELECT rolname FROM pg\_roles;

创建用户

CREATE USER <user\_name> WITH PASSWORD '<password>';

删除用户

DROP USER IF EXISTS <user\_name>;

更改 用户密码

ALTER ROLE <user\_name> WITH PASSWORD '<password>';

**Schema**

列出 Schemas

\\dn

SELECT schema\_name FROM information\_schema.schemata;

SELECT nspname FROM pg\_catalog.pg\_namespace;

创建架构

CREATE SCHEMA IF NOT EXISTS <schema\_name>;

删除模式

DROP SCHEMA IF EXISTS <schema\_name> CASCADE;

**PostgreSQL** **命令**

**表**

<table style="width:410.25pt;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \d &lt;table&gt; </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 描述表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \d+ &lt;table&gt; </td> 
 <td style="vertical-align:top;"> 详细描述表格 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dt </td> 
 <td style="vertical-align:top;"> 列出当前模式中的表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dt *.* </td> 
 <td style="vertical-align:top;"> 列出所有模式中的表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dt &lt;schema&gt;.* </td> 
 <td style="vertical-align:top;"> 列出架构的表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dp </td> 
 <td style="vertical-align:top;"> 列出表访问权限 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \det[+] </td> 
 <td style="vertical-align:top;"> 列出外部表 </td> 
 </tr> 
 </tbody> 
</table>

**查询缓冲区**

<table style="width:413.25pt;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \e [FILE] </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 编辑查询缓冲区(或文件) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \ef [FUNC] </td> 
 <td style="vertical-align:top;"> 编辑函数定义 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \p </td> 
 <td style="vertical-align:top;"> 显示内容 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \r </td> 
 <td style="vertical-align:top;"> 重置(清除)查询缓冲区 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \s [FILE] </td> 
 <td style="vertical-align:top;"> 显示历史记录或保存到文件 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \w FILE </td> 
 <td style="vertical-align:top;"> 将查询缓冲区写入文件 </td> 
 </tr> 
 </tbody> 
</table>

**信息**

<table style="width:411.55pt;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \l[+] </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 列出所有数据库 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dn[S+] </td> 
 <td style="vertical-align:top;"> 列出架构 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \di[S+] </td> 
 <td style="vertical-align:top;"> 列出索引 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \du[+] </td> 
 <td style="vertical-align:top;"> 列出角色 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \ds[S+] </td> 
 <td style="vertical-align:top;"> 列出序列 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \df[antw][S+] </td> 
 <td style="vertical-align:top;"> 列出函数 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \deu[+] </td> 
 <td style="vertical-align:top;"> 列出用户映射 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dv[S+] </td> 
 <td style="vertical-align:top;"> 列表视图 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dl </td> 
 <td style="vertical-align:top;"> 列出大对象 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dT[S+] </td> 
 <td style="vertical-align:top;"> 列出数据类型 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \da[S] </td> 
 <td style="vertical-align:top;"> 列出聚合 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \db[+] </td> 
 <td style="vertical-align:top;"> 列出表空间 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dc[S+] </td> 
 <td style="vertical-align:top;"> 列出转化 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dC[+] </td> 
 <td style="vertical-align:top;"> 列出演员表 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \ddp </td> 
 <td style="vertical-align:top;"> 列出默认权限 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dd[S] </td> 
 <td style="vertical-align:top;"> 显示对象描述 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dD[S+] </td> 
 <td style="vertical-align:top;"> 列出域 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \des[+] </td> 
 <td style="vertical-align:top;"> 列出国外服务器 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dew[+] </td> 
 <td style="vertical-align:top;"> 列出外部数据包装器 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dF[+] </td> 
 <td style="vertical-align:top;"> 列出文本搜索配置 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dFd[+] </td> 
 <td style="vertical-align:top;"> 列出文本搜索词典 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dFp[+] </td> 
 <td style="vertical-align:top;"> 列出文本搜索解析器 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dFt[+] </td> 
 <td style="vertical-align:top;"> 列出文本搜索模板 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dL[S+] </td> 
 <td style="vertical-align:top;"> 列出程序语言 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \do[S] </td> 
 <td style="vertical-align:top;"> 列出运算符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dO[S+] </td> 
 <td style="vertical-align:top;"> 列出排序规则 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \drds </td> 
 <td style="vertical-align:top;"> 列出每个数据库的角色设置 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \dx[+] </td> 
 <td style="vertical-align:top;"> 列出扩展 </td> 
 </tr> 
 </tbody> 
</table>

S：显示系统对象，+：附加细节

**连接**

<table style="width:411.6pt;"> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \c [DBNAME] </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 连接到新数据库 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \encoding [ENCODING] </td> 
 <td style="vertical-align:top;"> 显示或设置客户端编码 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \password [USER] </td> 
 <td style="vertical-align:top;"> 更改密码 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \conninfo </td> 
 <td style="vertical-align:top;"> 显示信息 </td> 
 </tr> 
 </tbody> 
</table>

**格式化**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \a </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> 在未对齐和对齐之间切换 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \C [STRING] </td> 
 <td style="vertical-align:top;width:207.25pt;"> 设置表格标题，如果没有则取消设置 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \f [STRING] </td> 
 <td style="vertical-align:top;width:207.25pt;"> 显示或设置未对齐的字段分隔符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \H </td> 
 <td style="vertical-align:top;width:207.25pt;"> 切换 HTML 输出模式 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \t [on|off] </td> 
 <td style="vertical-align:top;width:207.25pt;"> 仅显示行 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \T [STRING] </td> 
 <td style="vertical-align:top;width:207.25pt;"> 设置或取消设置 HTML &lt;table&gt; 标签属性 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;width:207.25pt;"> \x [on|off] </td> 
 <td style="vertical-align:top;width:207.25pt;"> 切换扩展输出 </td> 
 </tr> 
 </tbody> 
</table>

**输入输出**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \copy ... </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 导入/导出表&nbsp;另见：&nbsp;复制 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \echo [STRING] </td> 
 <td style="vertical-align:top;"> 打印字符串 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \i FILE </td> 
 <td style="vertical-align:top;"> 执行文件 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \o [FILE] </td> 
 <td style="vertical-align:top;"> 将所有结果导出到文件 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \qecho [STRING] </td> 
 <td style="vertical-align:top;"> 输出流的字符串 </td> 
 </tr> 
 </tbody> 
</table>

**变量**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \prompt [TEXT] NAME </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 设置变量 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \set [NAME [VALUE]] </td> 
 <td style="vertical-align:top;"> 设置变量&nbsp;(如果没有参数，则列出所有变量) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \unset NAME </td> 
 <td style="vertical-align:top;"> 删除变量 </td> 
 </tr> 
 </tbody> 
</table>

**杂项**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \cd [DIR] </td> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> 更改目录 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \timing [on|off] </td> 
 <td style="vertical-align:top;"> 切换时间 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \! [COMMAND] </td> 
 <td style="vertical-align:top;"> 在shell中执行 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#e8e8e8;vertical-align:top;"> \! ls -l </td> 
 <td style="vertical-align:top;"> 在shell中列出所有 </td> 
 </tr> 
 </tbody> 
</table>

**大对象**

*  \\lo\_export LOBOID FILE
 *  \\lo\_import FILE \[COMMENT\]
 *  \\lo\_list
 *  \\lo\_unlink LOBOID

**各种各样的**

**备份**

使用 pg\_dumpall 备份所有数据库

$ pg\_dumpall -U postgres > all.sql

使用 pg\_dump 备份数据库

$ pg\_dump -d mydb -f mydb\_backup.sql

*  \-a 只转储数据，而不是模式(schema)
 *  \-s 只转储模式，不转储数据
 *  \-c 在重新创建之前删除数据库
 *  \-C 还原前创建数据库
 *  \-t 仅转储命名表
 *  \-F 格式(c：自定义，d：目录，t：tar)

使用 pg\_dump -? 获取完整的选项列表

**恢复**

使用 psql 恢复数据库