【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个让你从新手到专家的核心项目解析

更多资讯

冷江电工证哪里考，电子证书怎么拿？实操考试心里没底怕挂科

麻城电工证在哪里报名的真实经历：外省证怎么转过来

西安哪里办电工证便宜官方回应怕考不过白交培训费

容城县电工证考证报名怎么考？工地太忙根本没时间复习考试，全国通用证怎么拿？

电工证年审超过怎么应对？通过率揭秘助你一次通关

贵阳市电工证快速办理多少钱考不过白交钱？选对机构是关键

今日更新

高压电工证哪里可以挂多久拿证全攻略：实操考试心里没底怕挂科别慌

电工证的理论题目在哪考，外省证怎么转过来

工地太忙根本没时间复习考试，职业学校的电工证值不值得考？

本周精选

安监局电工证分数查询怎么查才能知道含金量高不高

新疆电工作业干活累吗良心建议助你避开考不过的坑

中牟县电工证怎么报名外省证能转吗

本月热门

面向复杂光照与角度的宠物面部识别鲁棒性增强策略

基于DRV8818与STM32的双极步进电机驱动方案实战解析

DRV8818PWPR+PIC18F45K80双极步进电机控制方案实战

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