1.查询数据时,将实体类变量与数据库中对应sql idcolum USER_NAME as userName, USER_AGE as userAge /sql Select include refid ”colum”/ from userinfo2.将插入后的数据id映射到实体类上insert id insertUser selectKey keyProperty”id” resultType “int”order “BEFORE” (select nvl(max(ID),0) 1 as ID from user t) /selectKey insert into user(name) values( #{name} ) /insert3.for循环添加Mapper.Java中int inserUser( Param(userList) ListUser userList);Mapper.XML中insert idinsertUser parameterTypejava.util.List insert all foreach collection userList index index item item into user values( #{item.userId} ) /foreach select * from dual /insert4. 模糊查询Mapper.Java中ListUser getUserInfo(Param(name) String name);Mapper.XML中select idgetUserInfo bind nameuserName value% name %/ select * from user where name like #{userName} /select5.多嵌套查询resultMap id “getUser” type “对应的实体类” result column “数据库的名称” property “实体类的名称”/ collection propertyfeatIds selectgetFeatId column{type type, userName userName}/ resultMap/ select id “getUserInfo” resultMap “getUser”/ select id “getFeatId” resultType “对应的实体类”/6.大于号小于号大于号gt;小于号lt;7. Oracle对应的数据类型jdbcTypeNUMERIC(数字)jdbcTypeVARCHAR (字符串)8.时间格式to_date(#{date},yyyy-MM-dd hh24:mi:ss)9.Oracle中添加当前时间Sysdate可以在对应的字段下直接写列如:INSERT INTO USER(username, age, time)VALUES (张三,18,sysdate)10.Trim标签属性prefix表示以xxx开头prefixOverrides表示若标签中间内容以xxx开头就删除suffix表示以xxx结尾suffixOverrides表示若标签中间内容以xxx结尾就删除列如:UPDATE USERNAME trime prefix set sufficOverrdes ,if teststate ! nullstate #{state} ,/ifif testage! nullage #{age} ,/ifwhere id #{id}这句话就是代表这段代码以 set 开头 , 如果结尾是逗号 就删掉.11.If-Else标签choose (when,otherwise)例如:choosewhen testparams!null //when 代表如果right JOIN/whenotherwise // otherwise代表否则LEFT JOIN/otherwise/choose12.if标签中大于号小于号大于gt大于等于gte小于lt小于等于lte例如:if text age gt 1 and age lt 3/13.in多参查询Mapper.Java中ListUser getUser(Parm(userIds) ListInteger userIds);Mapper.XML中select * from userwhere userid inforeach collectionuserIds itemitem indexindex open( separator, close)#{item}/foreach14.查询多个字符串类型and regexp_like(t.userName, 张三|李四|王五)15.字符的拼接!--两表查询拼接字段,截取“/”第八个之前的数据-- select idgetSys001JiancetjInfo resultTypecom.qc.jy.pojo.Sys001JiancetjInfo SELECT t2.*, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.jctp),/), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.jctp),/), /, 1, 8) - 1) AS pathjctb, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.JCBG),/), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.JCBG),/), /, 1, 8) - 1) AS pathjcbg, SUBSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.JCSP),/), 1, INSTR(CONCAT(CONCAT(CONCAT(CONCAT(t1.PATH,t1.gcbm),/),t2.JCSP),/), /, 1, 8) - 1) AS pathjcsp FROM SYS_001_Wenjiantjinfo t1 JOIN SyS_001_JIANCETJINFO t2 ON t1.gcbm t2.gcbm where if testfeatid !null and featid! FEATID #{featid} /if /where /select16.使用insert-select进行添加数据insert idaddPipingNetworkSketch insert into SYS_002_GUANWANGCAOTU(FROMID,TOID,GDTYPE,GDCZ,GDKJ,REMARK,FROMX,FROMY,TOX,TOY,UPLOADPERSON,ID,FEATID,STYLE,WIDTH,SSQZ,SFJS,LXFS,DWMC) SELECT #{fromId},#{toId},#{gdType},#{gdcz},#{gdkj},#{remark},#{fromX},#{fromY},#{toX},#{toY},#{uploadPerson}, MAX(ID) 1 , #{featId} , #{style}, #{width}, #{ssqz}, #{sfjs}, #{lxfs}, #{dwmc} FROM SYS_002_GUANWANGCAOTU /insert17.foreach标签遍历集合或者数组一般都是用于批量修改或者批量添加foreach collection 集合名或者数组名 item 元素 separator 标签分隔符 open 以什么开始 close 以什么结束 #{元素} /foreach18.Oracle行专列select * from (select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t) tpivot(max(CUR_VAL) for PID_NAME in(流量 as flow,液位 as YW,cod as COD,氨氮 as NH3,ph as PH,总磷 AS TP))转之前数据结构select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t转之后数据结构select * from (select PUMPID,PID_NAME,to_number(CUR_VAL) CUR_VAL from jc_bz_data_time t) tpivot(max(CUR_VAL) for PID_NAME in(流量 as flow,液位 as YW,cod as COD,氨氮 as NH3,ph as PH,总磷 AS TP))19.针对数据库中的表进行自增序列的设置和触发器的创建create sequence SEQUENCE_NAME start with 1000 increment by 1 nomaxvalue; create or replace trigger TRIGGER_NAME before insert on TABLE_NAME for each row when (new.FIELD_NAME is null) begin select SEQUENCE_NAME.nextval into:new.FIELD_NAME from dual; end;1.create sequence SEQUENCE_NAME start with 1000 increment by 1 nomaxvalue;创建一个名为SEQUENCE_NAME的序列起始值为1000每次递增1没有最大值限制。2.create or replace trigger TRIGGER_NAME before insert on TABLE_NAME for each row when (new.FIELD_NAME is null)创建一个名为TRIGGER_NAME的触发器它在每次向TABLE_NAME表中插入新行之前触发且当新行的FIELD_NAME字段为空时才触发。3.begin select SEQUENCE_NAME.nextval into:new.FIELD_NAME from dual; end;定义了触发器的动作即在插入新行时从上面创建的序列SEQUENCE_NAME中获取下一个值并将其赋给新行的FIELD_NAME字段。总结这段代码的作用是为指定的表创建一个自增序列并创建一个触发器使得在向表中插入新行时如果某个字段为空就会自动从序列中获取下一个值并填充到该字段中。这种方式通常用于为表的主键字段生成唯一的递增值。20.保证分组后获取第一条数据with cte_table2 as ( select t1.*, row_number() over(partition by t1.psyh order by t1.psyh) as rank from psh_info t1 where t1.psfqJDGY00008S and t1.pshdj IN (一级排水户, 二级排水户) ) select * from cte_table2 where rank1;这个SQL查询使用了一个公共表表达式CTE它的作用是为了计算每个psyh分组中的记录并对每个分组中的记录进行排名。具体来说这个查询的意思如下首先使用公共表表达式CTEcte_table2从表psh_info中选择符合条件psfqJDGY00008S并且pshdj为一级排水户或二级排水户的记录。在cte_table2中利用ROW_NUMBER()窗口函数对每个psyh分组内的记录按照psyh的顺序进行排名并将排名结果存储在rank列中。最后从cte_table2中选择所有rank为1的记录即每个psyh分组中排名第一的记录。总的来说这个SQL查询的目的是找出每个psyh分组中排名第一的记录并将它们返回。21SQL 解析子查询select t.*, row_number() over(partition by t.EQUIPID order by t.SAMPLE_TIME desc nulls last) rn from JC_YW_DATA_HIS t这个部分是一个子查询它从表JC_YW_DATA_HIS中选择所有列t.*以及一个额外的列rn。row_number() over(partition by t.EQUIPID order by t.SAMPLE_TIME desc nulls last)是一个窗口函数它为每个EQUIPID的记录生成一个行号rn。partition by t.EQUIPID根据EQUIPID列将结果集分区。每个EQUIPID的记录会单独分组。order by t.SAMPLE_TIME desc nulls last在每个分区内记录根据SAMPLE_TIME列的值降序排列nulls last表示将SAMPLE_TIME为NULL的记录排在最后。结果是每个EQUIPID的记录都有了一个行号最新的记录行号为 1第二新的记录行号为 2以此类推。外层查询select * from (...) c where rn 2外层查询从子查询中选择所有列*并且只保留行号rn等于 2 的记录。这意味着只选择每个EQUIPID的第二条最新记录。总结综上所述这个 SQL 查询的作用是从JC_YW_DATA_HIS表中按照EQUIPID分组并为每个组内的记录按SAMPLE_TIME降序排列。选择每个EQUIPID的第二条最新记录即SAMPLE_TIME的第二大值。22.获取时间倒叙且空值排在最后order by DATA_TIME desc nulls last23.查询时根据时间是否添加时分秒1.不添加时分秒if teststartTime!null and startTime! bind namestartTime valuestartTime 00:00:00/ and CREATETIME to_date(#{startTime},yyyy-mm-dd hh24:mi:ss) /if if testendTime!null and endTime! bind nameendTime valueendTime 23:59:59/ and CREATETIME lt; to_date(#{endTime},yyyy-mm-dd hh24:mi:ss) /if2.添加时分秒if teststartTime!null and startTime! bind valuestartTime namestartTime/!--用bind标签加了 00:00:00就不需要加时分秒-- and t1.sample_time to_date(#{startTime}, yyyy-mm-dd hh24:mi:ss) /if if testendTime!null and endTime! bind nameendTime valueendTime/ and t1.sample_time lt;to_date(#{endTime},yyyy-mm-dd hh24:mi:ss) /if24.Oracle只显示年月日1.TO_CHAR(your_date_column, YYYY-MM-DD)2.TRUNC(your_date_column)25.SQL 代码利用了SUM和DECODE函数对数据进行了行转列也称为“行专列”或“透视”操作。具体来说它将多行数据的某些特定值汇总到单个结果集中以便更方便地查看和分析。select idgetSeWageFromData resultTypecom.qc.web.ks.monitor.pojo.SeWageFromData select snfwid, YLSBID, STATION_ID, SSPQID, wscid, SCYID, name wscname, scyname name, sjcll, sum(decode(zbname, 瞬时流速, DATA, 0))*3.6*TO_CHAR(SYSDATE, HH24) DATACount, sum(decode(zbname, 瞬时流速, DATA, 0)) DATA1, sum(decode(zbname, 化学需氧量, DATA, 0)) DATA3, sum(decode(zbname, 氨氮, DATA, 0)) DATA4, sum(decode(zbname, 总氮, DATA, 0)) DATA6, sum(decode(zbname, 总磷, DATA, 0)) DATA5, sum(decode(zbname, 氢离子浓度指数, DATA, 0)) DATA2, sum(decode(zbname, 化学需氧量, DATA, 0)) DATA8, sum(decode(zbname, 水温, DATA, 0)) DATA7, sum(decode(zbname, 累计流量, DATA, 0)) FLOWTOTAL, max(MONITORTIME) MONITORTIME, max(lat) lat, max(lon) lon from jc_wsc_data_view a where if testname!null and name! bind namename value%name%/ and a.name like #{name} /if /where group by wscid, SCYID, name,scyname,sjcll,STATION_ID, SSPQID,snfwid,YLSBID order by MONITORTIME desc /selectselect idgetSeWageFromData resultTypecom.qc.web.ks.monitor.pojo.SeWageFromData select snfwid, YLSBID, STATION_ID, SSPQID, wscid, SCYID, name wscname, scyname name, sjcll, sum(decode(zbname, 瞬时流速, DATA, 0)) * 3.6 * TO_CHAR(SYSDATE, HH24) AS DATACount, sum(decode(zbname, 瞬时流速, DATA, 0)) AS DATA1, sum(decode(zbname, 化学需氧量, DATA, 0)) AS DATA3, sum(decode(zbname, 氨氮, DATA, 0)) AS DATA4, sum(decode(zbname, 总氮, DATA, 0)) AS DATA6, sum(decode(zbname, 总磷, DATA, 0)) AS DATA5, sum(decode(zbname, 氢离子浓度指数, DATA, 0)) AS DATA2, sum(decode(zbname, 化学需氧量, DATA, 0)) AS DATA8, sum(decode(zbname, 水温, DATA, 0)) AS DATA7, sum(CASE WHEN zbname IN (累计流量, 流量) THEN DATA ELSE 0 END) AS FLOWTOTAL, max(MONITORTIME) AS MONITORTIME, max(lat) AS lat, max(lon) AS lon from jc_wsc_data_view a where if testname!null and name! bind namename value%name%/ and a.name like #{name} /if /where group by wscid, SCYID, name, scyname, sjcll, STATION_ID, SSPQID, snfwid, YLSBID order by MONITORTIME desc /select注意这是查询实时数据的内容可以这样进行行专列。26.oracle中如果原表没有进行建立分区是无法再去修改建立分区的需要建立新的表建立分区把原表数据导入。以下是在oracle建立分区表举例CREATE TABLE jc_ll_data_hour2_fenqu ( llid VARCHAR2(32), ll NUMBER, recordTime DATE !--CONSTRAINT pk_jc_ll_data_hour2 PRIMARY KEY (llid, recordTime)-- !--CONSTRAINT pk_jc_ll_data_hour2 PRIMARY KEY (llid, recordTime) 是定义一个主键约束的 SQL 语句。它指定了 llid 和 recordTime 这两列的组合作为表的主键。主键约束确保 唯一性每一行的数据在这两列的组合下是唯一的没有重复。 非空性这两列的值不能为 NULL。-- PARTITION BY RANGE (recordTime) INTERVAL (INTERVAL 1 MONTH) -- 自动分区间隔设置为一个月 ( PARTITION p_start VALUES LESS THAN (TO_DATE(2023-04-01, YYYY-MM-DD)) );27.oracle中根据四个小时进行分组可以求平均值。SELECTTRUNC(data_time, HH24) - MOD(TO_NUMBER(TO_CHAR(data_time, HH24)), 4) * INTERVAL 1 HOUR AS four_hour_periodFROMjc_bzSZ_data_hourGROUP BYTRUNC(data_time, HH24) - MOD(TO_NUMBER(TO_CHAR(data_time, HH24)), 4) * INTERVAL 1 HOURORDER BYfour_hour_period;28 .Oracle遇到锁怎么解决--查锁select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where t1.session_idt2.sidorder by t2.logon_time;--解锁alter system kill session 40667ALTER SYSTEM KILL SESSION SID,SERIAL#;29.如果一个字段样式 01,02,03 统计出列表出现各个次数SELECTSUM(count_1) AS total_count_1,SUM(count_2) AS total_count_2,SUM(count_3) AS total_count_3,SUM(count_4) AS total_count_4FROM (SELECTREGEXP_COUNT(qxjb, 1) AS count_1,REGEXP_COUNT(qxjb, 2) AS count_2,REGEXP_COUNT(qxjb, 3) AS count_3,REGEXP_COUNT(qxjb, 4) AS count_4FROM Sys_001_XiufutjinfoWHERE gcbm GD-20240717141327564-31337)注意如果还要分组按不同的字段统计可以这样SELECTgcbm,SUM( CASE WHEN TRIM(qxjb) 1 THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), 1, )) END) AS count_of_1,SUM(CASE WHEN TRIM(qxjb) 2 THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), 2, )) END ) AS count_of_2,SUM( CASE WHEN TRIM(qxjb) 3 THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), 3, )) END) AS count_of_3,SUM( CASE WHEN TRIM(qxjb) 4 THEN 1 ELSE LENGTH(TRIM(qxjb)) - LENGTH(REPLACE(TRIM(qxjb), 4, )) END ) AS count_of_4FROMsys_001_xiufutjinfoGROUP BYgcbm30.当出现一对多可以把一个字段拼起来SELECTt1.sim_num,GROUP_CONCAT(t2.name SEPARATOR , ) AS name_listFROMinspection_user_carlink_rivers_lakes t1LEFT JOINrivers_lakes t2 ON t1.river_id t2.idGROUP BYt1.sim_num;31.Mysql优化32.Oracle数据库锁表问题数据库被锁定查锁--查锁select t2.username,t2.sid,t2.serial#,t2.logon_timefrom v$locked_object t1,v$session t2where t1.session_idt2.sidorder by t2.logon_time;--解锁alter system kill session 40667ALTER SYSTEM KILL SESSION SID,SERIAL#;32.获取当前本周时间-- 本周一00:00:00;本周日23:59:59SELECT TRUNC(SYSDATE, IW) AS week_start, TRUNC(SYSDATE, IW) 6 INTERVAL 23:59:59 HOUR TO SECOND AS week_end FROM dual;33.Oracle创建触发器id自增CREATE OR REPLACE TRIGGER MENUSIDBEFORE INSERT ON SYS_MENUSREFERENCING OLD AS OLD NEW AS NEWFOR EACH ROWBEGINSELECT COALESCE(MAX(id), 0) 1 INTO :new.id FROM SYS_MENUS;END;34.原javasql// 先安全处理输入 String lon (longitude null || longitude.trim().isEmpty()) ? : longitude.trim(); String lat (latitude null || latitude.trim().isEmpty()) ? : latitude.trim(); String type (siteType null || siteType.trim().isEmpty()) ? : siteType.trim(); String sampLoc (samplingLocation null || samplingLocation.trim().isEmpty()) ? : samplingLocation.trim(); // 再拼接 SQL String sql UPDATE KS_SAMPLING_SITE SET LON COALESCE(NULLIF( lon , ), LON), LAT COALESCE(NULLIF( lat , ), LAT), TYPE COALESCE(NULLIF( type , ), LAT), SAMPLINGLOCATION COALESCE(NULLIF( sampLoc , ), SAMPLINGLOCATION) WHERE NO siteID AND PARENTID stationID ; LON COALESCE(CAST(NULLIF( lon , ) AS NUMBER), LON), LAT COALESCE(CAST(NULLIF( lat , ) AS NUMBER), LAT), 35.Oracle行专列多个值SELECT * FROM (SELECT pid, pipediameter, repairValue, reviewValue FROM WX_REPAIRPOINT WHERE delflag 0 and afterRepairTime to_date(#{startTime}, yyyy-mm-dd hh24:mi:ss) and afterRepairTime lt;to_date(#{endTime}, yyyy-mm-dd hh24:mi:ss)) PIVOT (SUM(repairValue) AS repair_sum,SUM(reviewValue) AS review_sum FOR pipediameter IN (DN600以上 AS sixs, DN600以下(含) AS sixx))36.Oracle自定义排序函数--DECODEselect * from (select PARENTAREAID,PARENTAREANAME,AREAID,AREANAME from sys_area_treewhere AREAID not in (select AREAID from sys_area_tree t where length(to_char(parentareaid)) 3 )and length(to_char(parentareaid)) 2 and PARENTAREANAME!中环union allselect PARENTAREAID,PARENTAREANAME,AREAID,AREANAME from sys_area_tree twhere length(to_char(parentareaid)) 3and areaid not in (select areaid from sys_area_tree t where parentareaid 1029)union allselect * from sys_area_tree t where parentareaid 1029union allselect * from sys_area_tree t where parentareaid 10034)order by DECODE(PARENTAREAID,15,1,16,2,17,3,18,4,13,5,14,6,11,7,21,8,10,9,20,10,19,11,12,12,22,13,23,14,9999) ,AREAID37.oracle自增函数-NEXTVAL函数insert idaddShjProblemFiles INSERT ALL foreach collectionshjProblemFiles itemitem INTO SHJ_PROBLEM_FILES (ID, PROBLEM_ID, FILE_NAME, FILE_PATH, UPLOAD_TIME, DEL_FLAG) VALUES ( SEQ_SHJ_PROBLEM_FILES.NEXTVAL, #{item.problemId}, #{item.fileName}, #{item.filePath}, #{item.uploadTime}, #{item.delFlag} ) /foreach SELECT 1 FROM DUAL /insert