PostgreSQL数据库的sql语法（整理版）
1. 概述
PostgreSQL是⼀个免费的关系型数据库服务器（ORDBMS）
2. 登录数据库
以⽤户的名义登录数据库，这时使⽤的是psql命令
psql -h 127.0.0.1 -U dbuser -p 5832 -d database
上⾯的命令的参数含义如下：
-h 指定服务器
-p 指定端⼝
-U 指定⽤户
-d 指定数据库
输⼊上⾯的命令以后，系统会提⽰输⼊dbuser⽤户的密码。输⼊正确，就可以登录控制台了。
3. 控制台命令
操作命令
设置密码
\password dbuser
退出控制台\q
查看SQL命令的解释\h command ，如\h select
查看psql命令列表\?
列出所有数据库\l
进⼊其他数据库\c [database_name]
列出当前数据库的所有表格\d
列出某⼀张表的结构\d [table_name]
列出所有⽤户\du
打开⽂本编辑器\e
列出当前数据库和连接的信息\conninfo
4. PSQL数据库操作命令
操作命令
创建数据库create database [db_name];
删除数据库drop database [db_name];
查询所有数据库select datname from PG_DATABASE; / select * from PG_DATABASE;
查询某⼀数据库的所有
表
select table_name FROM information_schema.tables where table_schema = 'public';
创建表create table if not exists ke_p_role (
id serial primary key, #唯⼀值，递增
name varchar (64) not null, #字符串64位
seq smallint not null, #最⼩int型
description varchar(128) not null # 字符串128位 )
插⼊表数据insert into ke_p_role (id, name, seq, description) values ('1', 'Administrator', '1', 'Have all permissions'), ('2', 'Devs', '2', 'Own add or delete'), ('3', 'Tourist', '3', 'Only viewer')
insert into ke_p_role values ('1', 'Administrator', '1', 'Have all permissions'), ('2', 'Devs', '2', 'Own add or
insert into ke_p_role values ('1', 'Administrator', '1', 'Have all permissions'), ('2', 'Devs', '2', 'Own add or
delete'), ('3', 'Tourist', '3', 'Only viewer')
插⼊并返回插⼊的数据insert into ke_p_role values ('1', 'Administrator', '1', 'Have all permissions') returning *;
根据字段，不存在则插⼊，存在则更新create table if not exists ke_topic_rank (
cluster varchar(64),
topic varchar(128),
tkey varchar(128),
tvalue bigint,
primary key (cluster, topic, tkey)
)
insert into ke_topic_rank values ('test1','test2','test3', 7) on conflict (cluster,topic,tkey) do update set tvalue=excluded.tvalue;
查询记录select * from user_tbl;
查询记录，带limit和
offset偏移量
select * from ke_consumer_group_summary where cluster='cluster1' limit 2 offset 0；去重查询select distinct on(name) * from t_ai_project;
递归查询with recursive summary as (
(select topic, diffval, timespan from ke_logsize where diffval > 0 order by topic asc, timespan desc limit 1)
union all
select u.* from summary s, lateral( select topic,diffval,timespan from ke_logsize where diffval > 0 and topic > s.topic order by topic asc, timespan desc limit 1) u
)
select topic, diffval, timespan, '1' as rownum, '1' as rank from summary;
查询，coalesce判断
是否为空
如果a.logsize为空，则返回右边的0select coalesce(sum(a.logsize),0) from (select logsize from ke_logsize where cluster='cluster1' and topic in ('phone') and tm='20200615' order by timespan desc limit 1) a
查询，ifnull，为空则返回0select ifnull(lag,0) from ke_consumer_bscreen where cluster='cluster1' and tm='20200713' and "group"='consumer' and topic='topic1' order by timespan desc limit 1
更新数据 update user_tbl set name = '李四' where name = '张三';
删除数据 delete from user_tbl where name = '李四' ;
添加表字段 alter table user_tbl add email varchar(40);
更新表字段 alter table user_tbl alter column signupdate set not null;
重命名表字段 alter table user_tbl rename column signupdate to signup;
删除表字段 alter table user_tbl drop column email;
重命名表名 alter table user_tbl rename to backup_tbl;
删除表名 drop table if exists backup_tbl;
清空表数据 truncate table ke_alarm_config;
查询postgres当前的
连接数
select * from pg_stat_activity;
postgres的最⼤连接
数
show max_connections;
注意：mysql的insert into values后⾯的值带有双引号，但postgresql带有的是单引号，如：insert into ke_topic_rank values
('test1','test2','test3', 7)
5. 备份与还原
这⾥使⽤的是postgresql11。root⽤户执⾏命令。
备份数据库ranger命令：
/usr/pgsql-11/bin/pg_dump --format=d -n public --verbose --host=192.168.1.212 -j 8 --port=5832 --username=postgres --file=/root/ranger ranger
还原数据库ranger命令：
/usr/pgsql-11/bin/pg_restore --format=d -n public --verbose --host=192.168.1.212 -j 8 --port=5832 --username=postgres --dbname=rangertest /root/ranger
注：
-F, --format=c|d|t|p 输出⽂件格式 (定制, ⽬录, tar明⽂ (默认值))；
-n, --schema=SCHEMA 只转储指定名称的模式；
-v, --verbose 详细模式；
-j, --jobs=NUM 执⾏多个并⾏任务进⾏备份转储⼯作（只适⽤--format=d）；
-h, --host=主机名 数据库服务器的主机名或套接字⽬录；
-p, --port=端⼝号 数据库服务器的端⼝号
-U, --username=名字 以指定的数据库⽤户联接
-f, --file=FILENAME 输出⽂件或⽬录名
ranger 数据库名（-d, --dataname=数据库不能与-f, --file=FILENAME同时使⽤）
/root/ranger 输出⽂件或⽬录名（-d, --dataname=数据库不能与-f, --file=FILENAME同时使⽤） 单独备份数据库ranger的表x_policy_export_audit命令（只备份数据）：
/usr/pgsql-11/bin/pg_dump -h 192.168.1.218 -p 5832 -U postgres -d ranger -t x_policy_export_audit -a > audit.sql
单独还原数据库ranger的表x_policy_export_audit命令：
/usr/pgsql-11/bin/psql -h 192.168.1.214 -p 5832 -U postgres -d rangertest -f audit.sql
注：
-h, --host=主机名 数据库服务器的主机名或套接字⽬录；
-p, --port=端⼝号 数据库服务器的端⼝号
-U, --username=名字 以指定的数据库⽤户联接drop table if exists user
-d, --database=数据库 数据库名
-f, --file=FILENAME 输出⽂件或⽬录名
6. 总结
【参考资料】
PostgreSQL 创建表 CREATE TABLE
PostgreSQL UPSERT的功能与⽤法
postgresql的show databases、show tables、describe table操作
PostgreSQL 批量插⼊, 更新和合并操作
postgresql中类似IFNULL⽤法
PostgreSQL⼊门教程（命令⾏）
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论