企业官网建设流程全解析

一、批量插入数据【测试用】

1.1、sql 语法

sql 模板

DO$$DECLAREiinteger:=1;BEGINWHILEi<=10LOOPINSERTINTOmy_table(col1,col2,col3)VALUES('value1','value2',i);i :=i+1;ENDLOOP;END$$;

案例测试 sql

DO$$DECLAREiinteger:=1;BEGINWHILEi<=100LOOPINSERTINTO"campaign"."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,i+1,'汤玉祥','18867087968','10630','第51期答题活动','春节民俗','0','0',5001,'2023-10-25 19:49:59',NULL,NULL,3902,0,i+1,NULL,NULL,NULL);i :=i+1;ENDLOOP;END$$;

二、更改数据库字段类型

2.1、sql 语法

altertabletable_namealtercolumncolumn_nametype类型;例如：altertableintermediatetablealtercolumnphonetypevarchar(100);

2.2、新增表字段

ALTERTABLEyour_tableADDCOLUMNnew_column datatype;ALTERTABLEyour_tableADDCOLUMNnew_column datatypeDEFAULTdefault_value;-- 新增字段并设置默认值COMMENTONCOLUMNyour_table.your_columnIS'This 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_name='your_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,jdbcType=NUMERIC}) TWHERErn=1limit#{num, jdbcType=NUMERIC}

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,jdbcType=NUMERIC})WHEREanswer_user_id=#{userId,jdbcType=NUMERIC}ANDrownum=1

4.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,jdbcType=NUMERIC})TWHERErn=1ORDERBYscoreDESCNULLSLAST,time_consumingASC)where rank<![CDATA[<=]]>#{num,jdbcType=NUMERIC}

五、查询时间，周期

5.1、查询本周一 ～ 本周日的时间区间

SELECTTRUNC(NEXT_DAY(sysdate-8,1)+1),TRUNC(NEXT_DAY(sysdate-8,1)+7)FROMmc_campaign;

5.2、查询当天，本周，本月开始时间 ～ 结束时间

但是在 sql 中进行了函数操作，会导致索引失效，建议直接放到代码中处理时间，然后 sql 直接拼接处理好些！

<choose><when test="dateType == 'day'">--本日参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDaWHERETO_CHAR(a.JION_TIME,'YYYY-MM-DD')=TO_CHAR(now(),'YYYY-MM-DD')andCAMPAIGN_ID=#{campaignId,jdbcType=NUMERIC}andCUST_NUM=#{userId,jdbcType=NUMERIC}andJION_STUTSin('1','2')andHOLD_TIMES='1'</when><when test="dateType == 'week'">--本周参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHEREJION_TIME&gt;=TRUNC(NEXT_DAY(sysdate-8,1)+1)ANDJION_TIME&lt;TRUNC(NEXT_DAY(sysdate-8,1)+7)+1andCAMPAIGN_ID=#{campaignId,jdbcType=NUMERIC}andCUST_NUM=#{userId,jdbcType=NUMERIC}andJION_STUTSin('1','2')andHOLD_TIMES='1'</when><when test="dateType == 'month'">--本月参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERETO_CHAR(JION_TIME,'YYYY-MM')=TO_CHAR(now(),'YYYY-MM')andCAMPAIGN_ID=#{campaignId,jdbcType=NUMERIC}andCUST_NUM=#{userId,jdbcType=NUMERIC}andJION_STUTSin('1','2')andHOLD_TIMES='1'</when><otherwise>--默认全部参与成功的数据SELECTCOUNT(1)FROMMC_CAMPAIGN_JION_RECORDWHERECAMPAIGN_ID=#{campaignId,jdbcType=NUMERIC}andCUST_NUM=#{userId,jdbcType=NUMERIC}andJION_STUTSin('1','2')andHOLD_TIMES='1'</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.ID=M.busi_idANDM.busi_type='1')shareNumFROMcampaign.mc_article ALEFTJOINcampaign.mc_article_category ACONAC.ARTICLE_CATEGORY_ID=ANY(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.attrelid=c.oidleftjoinpg_type tona.atttypid=t.oidleftjoinpg_description dond.objoid=a.attrelidandd.objsubid=a.attnumleftjoinpg_tables gonupper(g.tablename)=upper(c.relname)wherea.attnum>0andg.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.relnamenotlike'old_%')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_id=ANY(string_to_array(a.category_ids,',')::int[])GROUPBYa.category_ids;

效果图：