【pgSql 海量数据库操作记录】-北京尧图网络

一、批量插入数据【测试用】1.1、sql 语法sql 模板DO$$DECLAREiinteger:1;BEGINWHILEi10LOOPINSERTINTOmy_table(col1,col2,col3)VALUES(value1,value2,i);i :i1;ENDLOOP;END$$;案例测试 sqlDO$$DECLAREiinteger:1;BEGINWHILEi100LOOPINSERTINTOcampaign.mc_answer_record(answer_record_id,answer_user_id,answer_user_name,answer_user_mobile,theme_id,theme,answer_category,pass,delete_status,create_user,create_time,update_user,update_time,theme_category_id,score,time_consuming,used_share_times,type,game_type)VALUES(mc_answer_record_seq.nextVal,i1,汤玉祥,18867087968,10630,第51期答题活动,春节民俗,0,0,5001,2023-10-25 19:49:59,NULL,NULL,3902,0,i1,NULL,NULL,NULL);i :i1;ENDLOOP;END$$;二、更改数据库字段类型2.1、sql 语法altertabletable_namealtercolumncolumn_nametype类型;例如altertableintermediatetablealtercolumnphonetypevarchar(100);2.2、新增表字段ALTERTABLEyour_tableADDCOLUMNnew_column datatype;ALTERTABLEyour_tableADDCOLUMNnew_column datatypeDEFAULTdefault_value;-- 新增字段并设置默认值COMMENTONCOLUMNyour_table.your_columnISThis is a comment for the column;-- 字段添加注释-- 示例altertablemc_answer_recordaddcolumnreceive_statuschar(1);COMMENToncolumnmc_answer_record.receive_statusis领取奖品状态0.待领取1.已领取2.3、查询 information_schema.columns 视图来获取表字段的注释信息-- 查询 information_schema.columns 视图来获取表字段的注释信息SELECTcolumn_name,column_commentFROMinformation_schema.columnsWHEREtable_nameyour_table;三、添加索引3.1、单个字段索引CREATEINDEXidx_your_columnONyour_table(your_column);-- 单个字段索引在这个示例中idx_your_column 是索引的名称your_table 是表的名称your_column 是要创建索引的字段。你可以根据自己的需求选择不同的索引类型。PostgreSQL 支持多种类型的索引包括 B-tree、哈希、GiST、SP-GiST、GIN 和 BRIN 等。3.2、复合索引CREATEINDEXidx_your_columnsONyour_table(column1,column2,...);-- 复合索引四、查询排行版4.1、查询前 100 个用户的排行版SELECTanswer_user_id custNum,answer_user_name custName,answer_user_mobile phone,nvl(score,0)score,nvl(time_consuming,0)timeConsuming,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consuming NULLSLAST)ASrankFROM(SELECTanswer_user_id,answer_user_name,answer_user_mobile,score,time_consuming,ROW_NUMBER()OVER(PARTITIONBYanswer_user_idORDERBYscoreDESCNULLSLAST,time_consumingASC)ASrnFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC}) TWHERErn1limit#{num, jdbcTypeNUMERIC}4.2、查询自己的排行名次SELECTanswer_user_id custNum,score score,time_consuming timeConsuming,answer_user_name custName,answer_user_mobile phone,rankFROM(SELECTanswer_user_id,score,time_consuming,answer_user_name,answer_user_mobile,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consuming NULLSLAST)ASrankFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC})WHEREanswer_user_id#{userId,jdbcTypeNUMERIC}ANDrownum14.3、查询排名前 100 的用户SELECTcustNum,custName,phone,score,timeConsuming,rank from(SELECTanswer_user_id custNum,answer_user_name custName,answer_user_mobile phone,nvl(score,0)score,nvl(time_consuming,0)timeConsuming,DENSE_RANK()OVER(ORDERBYscoreDESCNULLSLAST,time_consumingNULLSLAST)ASrankFROM(SELECTanswer_user_id,answer_user_name,answer_user_mobile,score,time_consuming,ROW_NUMBER()OVER(PARTITIONBYanswer_user_idORDERBYscoreDESCNULLSLAST,time_consumingASC)ASrnFROMmc_answer_recordWHEREtheme_id#{campaignId,jdbcTypeNUMERIC})TWHERErn1ORDERBYscoreDESCNULLSLAST,time_consumingASC)where rank![CDATA[]]#{num,jdbcTypeNUMERIC}五、查询时间周期5.1、查询本周一本周日的时间区间SELECTTRUNC(NEXT_DAY(sysdate-8,1)1),TRUNC(NEXT_DAY(sysdate-8,1)7)FROMmc_campaign;5.2、查询当天本周本月开始时间结束时间但是在 sql 中进行了函数操作会导致索引失效建议直接放到代码中处理时间然后 sql 直接拼接处理好些choosewhen testdateType day--本日参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDaWHERETO_CHAR(a.JION_TIME,YYYY-MM-DD)TO_CHAR(now(),YYYY-MM-DD)andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenwhen testdateType week--本周参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHEREJION_TIMEgt;TRUNC(NEXT_DAY(sysdate-8,1)1)ANDJION_TIMElt;TRUNC(NEXT_DAY(sysdate-8,1)7)1andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenwhen testdateType month--本月参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERETO_CHAR(JION_TIME,YYYY-MM)TO_CHAR(now(),YYYY-MM)andCAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/whenotherwise--默认全部参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERECAMPAIGN_ID#{campaignId,jdbcTypeNUMERIC}andCUST_NUM#{userId,jdbcTypeNUMERIC}andJION_STUTSin(1,2)andHOLD_TIMES1/otherwise/choose六、函数操作6.1、两张表关联字符串ids 关联数字型id举例文章表存放的是分类id字符串【2022,2023,2024】这种一篇文章对应多个分类关联文章分类表分类ID-- 每次看执行计划养成良好习惯并且先在生产上执行一下看看速度explainSELECTA.ID,array_to_string(ARRAY_AGG(AC.CATEGORY_NAME),,)AScategoryName,A.ARTICLE_TITLEASarticleTitle,A.CREATE_TIMEAScreateTime,nvl(A.like_num,0)likeNum,nvl(A.collect_num,0)collectNum,nvl(A.read_num,0)readNum,nvl(A.comment_num,0)commentNum,(SELECTCOUNT(1)FROMcampaign.mc_share_record MWHEREA.IDM.busi_idANDM.busi_type1)shareNumFROMcampaign.mc_article ALEFTJOINcampaign.mc_article_category ACONAC.ARTICLE_CATEGORY_IDANY(string_to_array(regexp_replace(A.ARTICLE_CATEGORY_ID,[^\d], ,g), )::INT[])GROUPBYA.IDORDERBYA.CREATE_TIMEDESCNULLSLAST;执行效果图七、查询表字段注释为空脚本selectdistinctg.schemaname 用户名,c.relname 表名,cast(obj_description(relfilenode,pg_class)asvarchar)名称,a.attname 字段,d.description 字段备注,concat_ws(,t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from))as列类型frompg_class cleftjoinpg_attribute aona.attrelidc.oidleftjoinpg_type tona.atttypidt.oidleftjoinpg_description dond.objoida.attrelidandd.objsubida.attnumleftjoinpg_tables gonupper(g.tablename)upper(c.relname)wherea.attnum0andg.schemanamein(campaign,glmall,imauth,mallapp,mallcollect,mallgoodsdb,mallinf,mallmerchantdb,mallorderdb,mallreportdb,workflowdb)andd.descriptionisnulland(c.relnamenotlike%bak%andc.relnamenotlike%0%andc.relnamenotlike%1%andc.relnamenotlike%2%andc.relnamenotlike%3%andc.relnamenotlike%4%andc.relnamenotlike%5%andc.relnamenotlike%6%andc.relnamenotlike%7%andc.relnamenotlike%8%andc.relnamenotlike%9%andc.relnamenotlikeold_%)orderbyg.schemaname,c.relname;八、分类ids关联分类表搂出分类名称SELECTa.category_ids,array_to_string(array_agg(distinctb.category_name),,)ASchinese_namesFROMpms_goods_base_info aleftJOINpms_goods_category bONb.goods_category_idANY(string_to_array(a.category_ids,,)::int[])GROUPBYa.category_ids;效果图

【pgSql 海量数据库操作记录】

相关推荐

FF14 ACT动画跳过插件终极指南：如何快速告别副本冗长动画

开源RPA如何重塑企业自动化成本效益模型：OpenRPA实战指南

STM32嵌入式开发实战：5个让你从新手到专家的核心项目解析

更多资讯

3步搞定3D打印振纹：Klipper 输入整形的完整调参指南

CPU、GPU、NPU、TPU深度解析：AI芯片选型底层逻辑

nhost 仓库中的 safeexec 模块：规避 Windows 下 exec.LookPath 当前目录查找漏洞的实现解析

ESP-IDF SPI Flash 可选特性指南：Auto Suspend、HPM、DPD、32-bit 地址与 OPI Flash 支持解析

Python爬虫气象数据实时发布系统：从采集到可视化全流程

gog YouTube 实战指南：在终端中用 gog 完成 YouTube 数据查询、订阅与播放列表管理

今日更新

基于MATLAB的CRI显色指数计算：从SPD光谱到Ra的完整流程

JSP+Servlet+MySQL博客系统源码部署与优化全攻略

蜂窝调度算法 MATLAB 实现：比例公平、轮询与最大 C/I 对比

本周精选

AI驱动的自动化专利分析平台技术解析

LeetCode-Go 题解：1252. Cells with Odd Values in a Matrix（奇数值单元格计数）双解法剖析

在线答疑系统Java毕设实战：Spring Boot前后端分离与状态流转

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？