Postgresql 常用整理

news/2024/7/9 20:20:19 标签: postgresql, 数据库

文章目录

  • 1. 查询
    • 1.1数据库表
    • 1.2 别名
      • 1.2.1 子表指定别名
      • 1.2.2 查询结果指定别名
    • 1.3 临时表
      • 1.3.1 定义临时表
      • 1.3.2 使用临时表
    • 1.4 子表
    • 1.5 分组
      • 1.5.1 group by
      • 1.5.2 partition by
    • 1.6 分组后合并指定列字段：
    • 1.7 查询条件判断：case when
    • 1.8 大小判断
      • 1.8.1 日期
      • 1.8.2 其他
    • 1.9 空值判断
      • 1.9.1 判断null
      • 1.9.2 判断空串 ‘’
      • 1.9.3 判断null和空串
    • 1.10 时间函数
      • 1.10.1 获取当前日期
      • 1.10.2 获取昨天、上周、上年日期
      • 1.10.3 获取今天、今月、今年的开始日期
      • 1.10.4 从时间戳提取年、月、日、时分秒，周
      • 1.10.5 取年、月、日、时分秒、星期数
      • 1.10.6 获取时间差（获取年、月、日差）
      • 1.10.7 获取日期差
    • 1.11 判断字符串是否包含
    • 1.12 取json字符串指定字段
      • 1.12.1 json和jsonb操作符
      • 1.12.2 操作实例
      • 1.12.3 处理json数组
    • 1.14 拼接字符串
  • 2. 创建函数（以获取日期间隔为例）
    • 2.1 创建方式
    • 2.2 函数使用

1. 查询

1.1数据库表

1.1.1 获取指定数据库表

SELECT table_name FROM
information_schema.TABLES
WHERE
 TABLE_SCHEMA = 'act' -- 数据库名称
AND table_name LIKE '%act_re%';

1.1.2 获取指定数据库表所有列名

select column_name from information_schema.columns where table_name='ppom_object' ORDER BY ORDINAL_POSITION

1.2 别名

1.2.1 子表指定别名

  • 父表根据子表别名获取列，使用as直接加英文方式
select  display_name as alias_display_name

1.2.2 查询结果指定别名

  • 使用as 加英文双引号括住名称
select  display_name as "显示名称"

1.3 临时表

1.3.1 定义临时表

  • 多个临时表，使用一个with，其他逗号分隔，最后一个不需要逗号
with  临时表名1  as (sql查询), 临时表名2 as(sql查询)

1.3.2 使用临时表

select * from 临时表名1 t1, 临时表名2 t2

1.4 子表

  • 外部条件需要取子表列情况下，子表需要有别名（例如temp），通过别名获取
select * from (sql查询) temp where 条件

1.5 分组

1.5.1 group by

  • 只可获取到分组的列（可以有多个）和按分组汇总数量或求和结果
select uid,name, count(*) from table group by uid,name

1.5.2 partition by

  • 支持获取除分组的列以外其他的列，一般可以跟row_number()组合获取分组序号，按序号获取指定排序后的序号列
select * from (
    select row_number() over(partition by object_uid ORDER BY created_time ASC) AS new_index
)temp 
where temp.new_index = 1

1.6 分组后合并指定列字段：

  • 使用array_to_string和array_agg组合
    • array_to_string（数组列，分隔符）
    • array_agg（ expression），加distinct去重，把表达式变成一个数组
SELECT uid, array_to_string(array_agg(distinct display_name),';') AS name FROM tmp GROUP BY uid

1.7 查询条件判断：case when

case when 条件1 then 结果1
     when 条件2 then 结果2
     else 结果3
     end  可对结果添加别名

1.8 大小判断

1.8.1 日期

select date_trunc('day',now()) = date_trunc('day',date('20230908'))

- 日期为date或timestamp，和字符串比较，字符串需要先转date或timestamp
select time1::timestamp > time2::timestamp
select time1::timestmap > to_timestamp('2023-11-15 18:33:41','yyyy-MM-dd hh24:mi:ss');
select to_date('2023-11-15 18:33:41','yyyy-MM-dd hh24:mi:ss');

1.8.2 其他

select a > b
# 大于：> ，小于：< ，等于：= ，不等于  !=  或 <>

1.9 空值判断

1.9.1 判断null

select * from table where uid is null
select * from table where uid is not null

1.9.2 判断空串 ‘’

select * from table where uid = ''
select * from table where uid != '' 或 select * from table where uid <> ''

1.9.3 判断null和空串

select * from table where length(uid) > 0
select * from table where length(uid)  <= 0

1.10 时间函数

  • （整合资料：https://blog.csdn.net/qq_40323256/article/details/123185022）

1.10.1 获取当前日期

select now();
select current_timestamp;
select to_char( now(),'yyyy-mm-dd hh:mi:ss');
select to_char(时间戳字段,'yyyy-mm-dd hh24:mi:ss');
#hh默认是12，可指定：hh12，hh24
select current_date;

1.10.2 获取昨天、上周、上年日期

select to_char( now() - interval '1 day','yyyy-mm-dd');
select to_char( now() - interval '1 week','yyyy-mm-dd hh:mi:ss');
select to_char( now() - interval '1 month','yyyy-mm-dd');
select to_char( now() - interval '1 year','yyyy-mm-dd');

1.10.3 获取今天、今月、今年的开始日期

select date_trunc('year', now())
select date_trunc('month', now())
select date_trunc('day', now())
select date_trunc('hour', now())
select date_trunc('minute', now())
select date_trunc('second', now())

1.10.4 从时间戳提取年、月、日、时分秒，周

select date_part('year', timestamp '2001-02-16 20:38:40')
#或者
select date_part('year', '2001-02-16 20:38:40'::timestamp)
select date_part('month', timestamp '2001-02-16 20:38:40')
select date_part('day', timestamp '2001-02-16 20:38:40')
select date_part('hour', timestamp '2001-02-16 20:38:40')
select date_part('minute', timestamp '2001-02-16 20:38:40')
select date_part('second', timestamp '2001-02-16 20:38:40')
select date_part('week', timestamp '2001-02-16 20:38:40')

1.10.5 取年、月、日、时分秒、星期数

// 从当前时间中提取年份
SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取月份   
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-05-20 16:54:53.644833'); 

// 从当前时间中提取天     
SELECT EXTRACT(DAY FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取小时    
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取分钟  
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取秒
SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-05-20 16:54:53.644833');     

// 从当前时间中提取世纪
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2023-05-20 16:54:53.644833');    

// 从当前时间中提取时间戳，单位：秒
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-05-20 16:54:53.644833');      
//取星期数
SELECT EXTRACT(DOW FROM TIMESTAMP '2023-05-20 16:54:53.644833');

1.10.6 获取时间差（获取年、月、日差）

#只获取年、月、日数据，其他忽略
select date_part('year', endtime::TIMESTAMP-starttime::TIMESTAMP)
select date_part('month', endtime::TIMESTAMP-starttime::TIMESTAMP)
select date_part('day', endtime::TIMESTAMP-starttime::TIMESTAMP)
#获取整体时间差，默认秒
#时间差换算成日
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60/24
#可以外部套一层向下取整floor()、向上取整ceil()、四舍五入round()取整
select floor(date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60/24)
#时间差换算成小时
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60/60
#时间差换算成分钟
select date_part('epoch', endtime::timestamp - starttime::TIMESTAMP)/60

1.10.7 获取日期差

#只取日期部分计算
select Date(endtime) - DATE(starttime)

1.11 判断字符串是否包含

  • position(substring in string)，第一个时目标字符串，第二个是原字符串，如果包含，返回第一次出现位置，根据是否大于0判断
select postion('aa' in 'abcd')
  • strpos(string, substring)，作用与position一致

1.12 取json字符串指定字段

  • （参考资料：https://blog.csdn.net/c_zyer/article/details/130968257）

1.12.1 json和jsonb操作符

在这里插入图片描述

1.12.2 操作实例

select order_json::json->'orderBody' from order -- 对象域
select order_json::json->>'orderBody' from order -- 文本
select order_json::json#>'{orderBody}' from order -- 对象域
select order_json::json#>>'{orderBody}' from order -- 文本

# 列名：column，列值： {"key":"","name":"发起人修改","id":""}
select column::json ->> 'name'

1.12.3 处理json数组

#取数组长度
select json_array_length(name::json)

#取出json数组中的某个字段返回json数组
#列名：column，列值：[{"key":"","name":"xxx","id":""}]
select json_array_elements(column::json) -> 'name'

1.14 拼接字符串

// a11b
select concat('a', 11, NULL, 'b');
// aabb
select  'aa' || 'bb'

2. 创建函数（以获取日期间隔为例）

2.1 创建方式

  • 可以通过sql文件执行函数定义，或者通过sql工具界面操作创建函数的方式，部分工具（以HeidiSQL为例）执行sql在包含主题的 B O D Y BODY BODY处会提示，以及不能使用 符号，可以在其他工具执行（ N a v i c a t e 、 p g A d m i n ）： u n t e r m i n a t e d d o l l a r − q u o t e d s t r i n g a t o r n e a r " 符号，可以在其他工具执行（Navicate、pgAdmin）： unterminated dollar-quoted string at or near " 符号，可以在其他工具执行（Navicate、pgAdmin）：unterminateddollar−quotedstringatornear"BODY$
  • 函数内容
-- 定义函数所处表，名称，public.f_daydelay，
-- 输入参数名称 类型 
-- 返回类型 RETURNS type
-- 创建函数语言选择 LANGUAGE，可以选择（plpgsql，sql，plpython，plperl，...）
-- 启动成本 COST 100（默认值）
-- 波动率 VOLATILE （波动率默认分类），可以执行任何操作

CREATE OR REPLACE FUNCTION public.f_daydelay(
    starttime timestamp with time zone,
    endtime timestamp with time zone)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

--声明函数使用变量名称 类型，函数都需要使用分号结尾
DECLARE
     v_return  varchar;--返回间隔时间 xx日xx时xx分
--函数主体    
BEGIN
  --省略方法
  --返回值     
  RETURN v_return;
    --异常处理
    EXCEPTION 
    WHEN OTHERS THEN
        RETURN SQLERRM;
--主体结束    
END;
$BODY$;

--定义函数授权用户
ALTER FUNCTION public.f_daydelay(timestamp with time zone, timestamp with time zone)
    OWNER TO postgres;

2.2 函数使用

select f_daydelay('2023-05-24 11:38:14.38'::timestmap,'2023-06-20 11:38:14.38'::timestamp);

http://www.niftyadmin.cn/n/5167785.html

相关文章

Elastic Stack 8.11：引入一种新的强大查询语言 ES|QL

Elastic Stack 8.11：引入一种新的强大查询语言 ES|QL

作者&#xff1a;Tyler Perkins, Ninoslav Miskovic, Gilad Gal, Teresa Soler, Shani Sagiv, Jason Burns Elastic Stack 8.11 引入了数据流生命周期、一种配置数据流保留和降采样&#xff08;downsampling&#xff09; 的简单方法&#xff08;技术预览版&#xff09;&#xf…
阅读更多...
Oracle11g for centos7

Oracle11g for centos7

准备工作 x86 centos7 oracle11G 环境搭建 配置好虚拟机&#xff0c;网络通畅&#xff0c;建议最少3G内存。 安装依赖 yum install binutils compat-libstdc-33 glibc* ksh gcc gcc-c libgcc libstdc* libaio libaio-devel libXext libX11 libXau libxcb libXi make sy…
阅读更多...
老师还不会评课？这里有你需要的解决方案

老师还不会评课？这里有你需要的解决方案

优点&#xff1a; 1.课件制作: 老师的PPT设计得很新颖&#xff0c;插入的音乐视频都非常贴合课堂内容&#xff0c;看得出老师非常用心地进行了设计。 2.教师素养&#xff1a;老师的语言丰富、朗读能力很出色、板书设计很工整。 3.教师风格: xx老师上课激情澎湃/非常有亲和力…
阅读更多...
高等数学教材啃书汇总重难点（三）微分中值定理与导数的应用

高等数学教材啃书汇总重难点（三）微分中值定理与导数的应用

本章节包含多个知识点&#xff0c;一些列微分中值定理是考研证明题的重头戏&#xff0c;而洛必达和泰勒展开则是方法论的天花板难度&#xff0c;虽然对于小题的考察难度较低&#xff0c;整体上仍需重点复习 首先是考研大纲包含的内容&#xff1a; 1.理解并会用罗尔(Rolle)定理、…
阅读更多...
倾斜摄影测量实景三维建模ContextCapture Master

倾斜摄影测量实景三维建模ContextCapture Master

目录 专题一 倾斜摄影测量原理及应用领域 专题二 倾斜摄影测量数据采集方法 专题三 CC支持数据类型及导入数据方法 专题四 CC空三计算参数设置及数据处理方法 专题五 CC控制点与连接点添加及应用 专题六 三维重建及应用 专题七 三维模型修复与场景拼接 更多应用 Contex…
阅读更多...
【操作系统】测试一

【操作系统】测试一

文章目录 单选题判断题简答题 单选题 &#xff08; &#xff09;不是基本的操作系统。 A. 批处理操作系统 B. 分时操作系统 C. 实时操作系统 D. 网络操作系统 【 正确答案: D】 操作系统提供给程序员的接口是&#xff08; &#xff09;。 A. 进程 B. 系统调用 C. 库函数 D. B和…
阅读更多...
如何使用 NFTScan NFT API 在 zkSync 网络上开发 Web3 应用

如何使用 NFTScan NFT API 在 zkSync 网络上开发 Web3 应用

zkSync 是由 Matter Labs 创建的&#xff0c;是一个以用户为中心的 zk rollup 平台&#xff0c;它是以太坊的第 2 层扩展解决方案&#xff0c;使用 zk-rollups 作为扩展技术&#xff0c;与 optimistic rollups 一样&#xff0c;zk-rollups 将会汇总以太坊主网上的交易并将交易证…
阅读更多...
Mysql数据备份 — mysqldump

Mysql数据备份 — mysqldump

一 备份类型 - 逻辑备份&#xff08;mysqldump&#xff09;&#xff1a; - 优点&#xff1a; - 恢复简单&#xff0c;可以使用管道将他们输入到mysql。 - 与存储引擎无关&#xff0c;因为是从MySQL服务器中提取数据而生成的&#xff0c;所以消除了底层数据…
阅读更多...
最新文章

Copyright @ 2022~2023