数据库:一文掌握 PostgreSQL 的各种指令(PostgreSQL指令备忘) ...

打印 上一主题 下一主题

主题 964|帖子 964|积分 2892

PostgreSQL 备忘清单为您提供了常用的 PostgreSQL 命令和语句。
PostgreSQL 工作

入门

切换和连接
  1. $ sudo -u postgres psql
复制代码
列出全部数据库
  1. postgres=# \l
复制代码
连接到名为 postgres 的数据库
  1. postgres=# \c postgres
复制代码
断开
  1. postgres=# \q
  2. postgres=# \!
复制代码
psql 命令

  参数示例分析[-d] <database>psql -d mydb连接到数据库-Upsql -U john mydb以特定用户身份连接-h -ppsql -h localhost -p 5432 mydb连接到主机/端口-U -h -p -dpsql -U admin -h 192.168.1.5 -p 2506 -d mydb连接长途 PostgreSQL-Wpsql -W mydb逼迫暗码-cpsql -c ‘\c postgres’ -c ‘\dt
’执行 SQL 查询或命令-Hpsql -c “\l
+” -H postgres > database.html生成 HTML 陈诉-lpsql -l列出全部数据库-fpsql mydb -f file.sql从文件执行命令-Vpsql -V打印 psql 版本  得到帮助

:--\hSQL 命令语法帮助\h DELETEDELETE SQL 语句语法\?PostgreSQL 命令列表 在 PostgreSQL 控制台中运行
Recon 观察

显示版本
  1. SHOW SERVER_VERSION;
复制代码
显示体系状态
  1. \conninfo
复制代码
显示情况变量
  1. SHOW ALL;
复制代码
列出用户
  1. SELECT rolname FROM pg_roles;
复制代码
显示当前用户
  1. SELECT current_user;
复制代码
显示当前用户的权限
  1. \du
复制代码
显示当前数据库
  1. SELECT current_database();
复制代码
显示数据库中的全部表
  1. \dt
复制代码
列出函数
  1. \df <schema>
复制代码
Databases 数据库

列出数据库
  1. \l
复制代码
连接到数据库
  1. \c <database_name>
复制代码
显示当前数据库
  1. SELECT current_database();
复制代码
创建数据库
  1. CREATE DATABASE <database_name> WITH OWNER <username>;
复制代码
删除数据库
  1. DROP DATABASE IF EXISTS <database_name>;
复制代码
重命名数据库
  1. ALTER DATABASE <old_name> RENAME TO <new_name>;
复制代码
Tables 表

列出当前数据库中的表
  1. \dt
  2. SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
复制代码
全局列表
  1. \dt
  2. *.*.SELECT * FROM pg_catalog.pg_tables
复制代码
列出表结构
  1. \d <table_name>
  2. \d+ <table_name>
  3. SELECT column_name, data_type, character_maximum_length
  4. FROM INFORMATION_SCHEMA.COLUMNS
  5. WHERE table_name = '<table_name>';
复制代码
创建表
  1. CREATE TABLE <table_name>(
  2.   <column_name> <column_type>,
  3.   <column_name> <column_type>
  4. );
复制代码
创建表,主键自增
  1. CREATE TABLE <table_name> (
  2.   <column_name> SERIAL PRIMARY KEY
  3. );
复制代码
删除表
  1. DROP TABLE IF EXISTS <table_name> CASCADE;
复制代码
Permissions 权限

成为 postgres 用户,如果您有权限错误
  1. sudo su - postgres
  2. psql
复制代码
授予 对数据库的全部权限
  1. GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
复制代码
授予数据库连接权限
  1. GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
复制代码
授予架构权限
  1. GRANT USAGE ON SCHEMA public TO <user_name>;
复制代码
授予函数权限
  1. GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
复制代码
授予在全部表上选择、更新、插入、删除的权限
  1. GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
复制代码
在表上授予权限
  1. GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
复制代码
授予对表的选择权限
  1. GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;
复制代码
Columns 列

添加栏目
  1. ALTER TABLE <table_name> IF EXISTS
  2. ADD <column_name> <data_type> [<constraints>];
复制代码
更新栏
  1. ALTER TABLE <table_name> IF EXISTS
  2. ALTER <column_name> TYPE <data_type> [<constraints>];
复制代码
删除列
  1. ALTER TABLE <table_name> IF EXISTS
  2. DROP <column_name>;
复制代码
将列更新为自增主键
  1. ALTER TABLE <table_name>
  2. ADD COLUMN <column_name> SERIAL PRIMARY KEY;
复制代码
使用自动递增的主键插入表中
  1. INSERT INTO <table_name>
  2. VALUES (DEFAULT, <value1>);
  3. INSERT INTO <table_name> (<column1_name>,<column2_name>)
  4. VALUES ( <value1>,<value2> );
复制代码
Data 数据

选择 全部数据
  1. SELECT * FROM <table_name>;
复制代码
读取一行数据
  1. SELECT * FROM <table_name> LIMIT 1;
复制代码
搜刮数据
  1. SELECT * FROM <table_name> WHERE <column_name> = <value>;
复制代码
插入 数据
  1. INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
复制代码
更新 数据
  1. UPDATE <table_name>
  2. SET <column_1> = <value_1>, <column_2> = <value_2>
  3. WHERE <column_1> = <value>;
复制代码
删除 全部数据
  1. DELETE FROM <table_name>;
复制代码
删除特定数据
  1. DELETE FROM <table_name>
  2. WHERE <column_name> = <value>;
复制代码
Users 用户

  列出角色
  1. SELECT rolname FROM pg_roles;
复制代码
创建用户
  1. CREATE USER <user_name> WITH PASSWORD '<password>';
复制代码
删除用户
  1. DROP USER IF EXISTS <user_name>;
复制代码
更改 用户暗码
  1. ALTER ROLE <user_name> WITH PASSWORD '<password>';
复制代码
Schema

列出 Schemas
  1. \dn
  2. SELECT schema_name FROM information_schema.schemata;
  3. SELECT nspname FROM pg_catalog.pg_namespace;
复制代码
创建架构
  1. CREATE SCHEMA IF NOT EXISTS <schema_name>;
复制代码
删除模式
  1. DROP SCHEMA IF EXISTS <schema_name> CASCADE;
复制代码
PostgreSQL 命令



:--\d <table>形貌表\d+ <table>详细形貌表格\dt
列出当前模式中的表\dt
*.*列出全部模式中的表\dt
<schema>.*列出架构的表\dp列出表访问权限\det[+]列出外部表 查询缓冲区

:--\e [FILE]编辑查询缓冲区(或文件)\ef [FUNC]编辑函数定义\p显示内容\r重置(清除)查询缓冲区\s [FILE]显示汗青记录或保存到文件\w FILE将查询缓冲区写入文件 信息

  :--\l
[+]列出全部数据库\dn[S+]列出架构\di[S+]列出索引\du
[+]列出角色\ds[S+]列出序列\df[antw][S+]列出函数\deu[+]列出用户映射\dv[S+]列表视图\dl列出大对象\dT[S+]列出数据类型\da[S]列出聚合\db[+]列出表空间\dc[S+]列出转化\dC[+]列出演员表\ddp列出默认权限\dd[S]显示对象形貌\dD[S+]列出域\des[+]列出国外服务器\dew[+]列出外部数据包装器\dF[+]列出文本搜刮设置\dFd[+]列出文本搜刮词典\dFp[+]列出文本搜刮解析器\dFt[+]列出文本搜刮模板\dL[S+]列出步伐语言\do[S]列出运算符\dO[S+]列出排序规则\drds列出每个数据库的角色设置\dx[+]列出扩展 S:显示体系对象,+:附加细节
连接

:--\c [DBNAME]连接到新数据库\encoding [ENCODING]显示或设置客户端编码\password [USER]更改暗码\conninfo
显示信息 格式化

:--\a在未对齐和对齐之间切换\C [STRING]设置表格标题,如果没有则取消设置\f [STRING]显示或设置未对齐的字段分隔符\H切换 HTML 输出模式\t [on|off]仅显示行\T [STRING]设置或取消设置 HTML <table> 标签属性\x [on|off]切换扩展输出 输入输出

:--\copy ...导入/导出表 另见: 复制\echo [STRING]打印字符串\i FILE执行文件\o [FILE]将全部结果导出到文件\qecho [STRING]输出流的字符串 变量

:--\prompt [TEXT] NAME设置变量\set [NAME [VALUE]]设置变量 (如果没有参数,则列出全部变量)\unset NAME删除变量 杂项

:--\cd [DIR]更改目录\timing [on|off]切换时间\! [COMMAND]在shell中执行\! ls -l在shell中列出全部 大对象



  • \l
    o_export LOBOID FILE
  • \l
    o_import FILE [COMMENT]
  • \l
    o_list
  • \l
    o_unlink LOBOID
各种各样的

备份

使用 pg_dumpall 备份全部数据库
  1. $ pg_dumpall -U postgres > all.sql
复制代码
使用 pg_dump 备份数据库
  1. $ pg_dump -d mydb -f mydb_backup.sql
复制代码


  •   -a   只转储数据,而不是模式(schema)
  •   -s   只转储模式,不转储数据
  •   -c   在重新创建之前删除数据库
  •   -C   还原前创建数据库
  •   -t   仅转储命名表
  •   -F   格式(c:自定义,d:目录,t:tar)
  使用 pg_dump -? 获取完整的选项列表
规复

使用 psql 规复数据库
  1. $ psql -U user mydb < mydb_backup.sql
复制代码
使用 pg_restore 规复数据库
  1. $ pg_restore -d mydb mydb_backup.sql -c
复制代码


  •   -U   指定数据库用户
  •   -c   在重新创建之前删除数据库
  •   -C   还原前创建数据库
  •   -e   如果碰到错误退出
  •   -F   格式(c:自定义,d:目录,t:tar,p:纯文本sql(默认))
  使用 pg_restore -? 获取完整的选项列表
长途访问

获取 postgresql.conf 的位置
  1. $ psql -U postgres -c 'SHOW config_file'
复制代码
附加到 postgresql.conf
  1. listen_addresses = '*'
复制代码
附加到 pg_hba.conf(与 postgresql.conf 相同的位置)
  1. host  all  all  0.0.0.0/0  md5
  2. host  all  all  ::/0       md5
复制代码
重启 PostgreSQL 服务器
  1. $ sudo systemctl restart postgresql
复制代码
导入/导出 CSV

将表格导出为 CSV 文件
  1. \copy table TO '<path>' CSV
  2. \copy table(col1,col1) TO '<path>' CSV
  3. \copy (SELECT...) TO '<path>' CSV
复制代码
将 CSV 文件导入表格
  1. \copy table FROM '<path>' CSV
  2. \copy table(col1,col1) FROM '<path>' CSV
复制代码
另见:复制
跨版本升级

pg_upgrade 跨版本升级
  1. $ /usr/lib/postgresql/16/bin/pg_upgrade \
  2.   -b /usr/lib/postgresql/15/bin \
  3.   -B /usr/lib/postgresql/16/bin \
  4.   -d /var/lib/postgresql/15/main \
  5.   -D /var/lib/postgresql/16/main \
  6.   -o " -c config_file=/etc/postgresql/15/main/postgresql.conf" \
  7.   -O " -c config_file=/etc/postgresql/16/main/postgresql.conf"
复制代码


  •   -b   旧版本二进制文件目录
  •   -B   新版本二进制文件目录
  •   -d   旧版本数据目录
  •   -D   新版本数据目录
  •   -o   旧版本主设置文件
  •   -O   新版本主设置文件
  •   -c   仅check,不执行升级,可先加-c查抄是否有报错,没有报错再运行升级
  使用 pg_upgrade -? 获取完整的选项列表

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

花瓣小跑

金牌会员
这个人很懒什么都没写!
快速回复 返回顶部 返回列表