PostgreSQL数据库的sql语法(整理版)

时间:2024-03-04 13:14:05

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 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为空,则返回右边的0

select 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,为空则返回0

select 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=名字              以指定的数据库用户联接
  • -d, --database=数据库            数据库名                         
  • -f, --file=FILENAME                输出文件或目录名

6. 总结

【参考资料】

https://pg.sjk66.com/postgresql/create-table.html PostgreSQL 创建表 CREATE TABLE

https://www.alibabacloud.com/help/zh/doc-detail/52951.htm PostgreSQL UPSERT的功能与用法

https://blog.csdn.net/u011402596/article/details/38510547 postgresql的show databases、show tables、describe table操作

https://yanbin.blog/postgresql-unnest-batch-crud-merge/ PostgreSQL 批量插入, 更新和合并操作

https://blog.csdn.net/qq_43639296/article/details/90667860 postgresql中类似IFNULL用法

https://www.cnblogs.com/Paul-watermelon/p/10401344.html PostgreSQL入门教程(命令行)