PostgreSQL最常用的函数

迷南。 2024-03-27 09:45 95阅读 0赞

**基本的**

切换和连接

$ sudo -u postgres psql

列出所有数据库

postgres=\# \\l

连接到名为 postgres 的数据库

postgres=\# \\c postgres

断开

postgres=\# \\q

postgres=\# \\!

**psql 命令**

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

SELECT nspname FROMpg\_catalog.pg\_namespace;

创建架构

CREATE SCHEMA IF NOT EXISTS <schema\_name>;

删除模式

DROP SCHEMA IF EXISTS <schema\_name> CASCADE;

**PostgreSQL 命令**

**表**

**查询缓冲区**

**信息**

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

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

**连接**

**格式化**

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

**输入输出**

**变量**

**杂项**

**大对象**

*  \\lo\_exportLOBOID FILE

*  \\lo\_importFILE \[COMMENT\]

*  \\lo\_list

*  \\lo\_unlinkLOBOID

**各种各样的**

**备份**

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

$ pg\_dumpall -U postgres> all.sql