sql语句开发笔记

1.循环插入

<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO HIMP_XXX_D
( DIEID,XXX,CREATE_DATE,CREATE_USER_ID)
SELECT A.* FROM (
<foreach collection="list" index="index" item="item" separator="UNION ALL" open="("
close=")">SELECT
#{item.dieid,jdbcType=VARCHAR},#{item.XXX,jdbcType=VARCHAR},
#{item.currentUserId,jdbcType=VARCHAR},#{_currentDate,jdbcType=DATE}, #{item.currentUserId,jdbcType=VARCHAR}
FROM DUAL
</foreach>
) A
</insert>

2.ORACLE新增序列:

   INSERT INTO HIMP_YEAR_INDEX_CONFIG (ID,TYPE_CODE,YEAR,LOCK_FLAG,REMARK,CREATE_TIME,CREATE_USER, UPDATE_TIME,UPDATE_USER )
    VALUES (HIMP_YEAR_INDEX_CONFIG_S.nextval,'1','2018','1','资料来自2017年度广东省通知3',null,'xrcheng',null,'xrcheng');
   COMMIT

3.循环更新：

xml:
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
UPDATE XXX
<set>
<if test="item.rdmrid != null">
RDMRID = #{item.rdmrid,jdbcType=VARCHAR},
</if>
<if test="item.name != null">
NAME = #{item.name,jdbcType=VARCHAR},
</if>
...
dao层：void batchUpdate(List<xx> xxxLists);

4.循环删除：

	<delete id="removeByIds">
UPDATE XXX
SET FLAG=0
WHERE ID in
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</delete>

5.'%'||#{xxx}||'%'的模糊分页查询，查询HAHA表里INPUT_TYPE为A或者B的数据，查询条件为INPUT_TYPE或者keywords值

SELECT A.*,
B.ORG_NAME ORG_CODE_DESP,C.LASTNAME CREATE_USER_NAME,
D.LASTNAME LAST_UPDATE_USER_NAME
FROM (SELECT
T.ID,T.ORG_CODE, T.INPUT_TYPE, T.INPUT_DATE,
T.EXPLAIN_INFO, T.LOCK_FLAG, T.FLAG,T.CREATE_DATE,
T.CREATE_USER_ID,T.LAST_UPDATE_DATE,T.LAST_UPDATE_USER_ID
FROM (
SELECT T1.ID,T1.ORG_CODE, T1.INPUT_TYPE, T1.INPUT_DATE,
T1.EXPLAIN_INFO, T1.LOCK_FLAG, T1.FLAG,T1.CREATE_DATE,
T1.CREATE_USER_ID,T1.LAST_UPDATE_DATE,T1.LAST_UPDATE_USER_ID
FROM HAHA T1
WHERE T1.FLAG = 1
AND T1.INPUT_TYPE='A' OR
T1.INPUT_TYPE='B'
)T
WHERE T.FLAG = 1
<if test="inputDate != null">
AND T.INPUT_DATE LIKE '%'||#{inputDate}||'%'
</if>
<filter
open="AND("
close=")" />
) A
LEFT JOIN HAHA_ORG B ON A.ORG_CODE = B.ORG_CODE
LEFT JOIN HAHA_USER_T C ON A.CREATE_USER_ID = C.ID
LEFT JOIN HAHA_USER_T D ON A.LAST_UPDATE_USER_ID = D.ID
<if test="keywords != null">
WHERE
B.ORG_NAME LIKE '%'||#{keywords}||'%'
OR A.EXPLAIN_INFO LIKE '%'||#{keywords}||'%'
OR C.LASTNAME LIKE '%'||#{keywords}||'%'
</if>
ORDER BY A.LAST_UPDATE_DATE DESC

6.rank()over(order by 列名排序)

rank()over(order by 列名排序)的结果是不连续的，如果有4个人其中有3个是并列第1名，最后的排序结果结果如：1 1 1 4
dense_rank()over(order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2

7.UNION和UNION ALL 

UNION 操作符合并两个或多个 SELECT 语句的结果
注意：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个
SELECT 语句中的列的顺序必须相同
UNION ALL:
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL;
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
SQL UNION ALL 语法:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

7.Oracle字符串截取函数

1.substr 函数：截取字符串
string：表示源字符串，即要截取的字符串。
start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。
length：可选项，表示截取字符串长度
例子：
（1）SELECT SUBSTR('Hello SQL!', 2) FROM dual
--从第2个字符开始，截取到末尾。返回'ello SQL!'
（2）SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual
--从倒数第4个字符开始，截取3个字符。返回'SQL'
2.instr 函数：返回子字符串在源字符串中的位置
语法：INSTR(string，child_string，[start]，[show_time])
string：表示源字符串。
child_string：子字符串，即要查找的字符串。
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错
例子：
（1）SELECT INSTR('city_company_staff', '_', 5) FROM dual
--返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置

8.Oracle批量插入时代码可以不设置UUID，直接在表里边ID主键字段的Default/Expr.添加sys_guid()，自动生成ID

9.修改时判断是新增数据还是更新数据

首先在实体加上count字段

<insert id="saveOrUpdate" parameterType="com.fiberhome.ms.himp.manuallyreport.entity.YearIndexVal">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
SELECT count(*) AS COUNT
FROM XX_VAL
WHERE INDEX_CODE= #{indexCode,jdbcType=VARCHAR}
AND
YICID = #{yicid,jdbcType=VARCHAR}
</selectKey>
<!-- 如果大于0则更新 -->
<if test="count>0">
UPDATE XX_VAL
<set>
<if test="indexValue != null">
INDEX_VALUE = #{indexValue,jdbcType=VARCHAR},
</if>
<if test="indexCode != null">
INDEX_CODE = #{indexCode,jdbcType=VARCHAR},
</if>
<if test="yicid != null">
YICID = #{yicid,jdbcType=VARCHAR},
</if>
<if test="remark != null">
REMARK = #{remark,jdbcType=VARCHAR},
</if>
</set>
WHERE INDEX_CODE = #{indexCode,jdbcType=VARCHAR}
AND
YICID = #{yicid,jdbcType=VARCHAR}
</if>
<!-- 如果等于0则保存 -->
<if test="count==0">
INSERT INTO XX_VAL (
INDEX_CODE,YICID,INDEX_VALUE,
REMARK
) VALUES (
#{indexCode,jdbcType=VARCHAR},#{yicid,jdbcType=VARCHAR},#{indexValue,jdbcType=VARCHAR},
#{remark,jdbcType=VARCHAR}
)
</if>
</insert>

10.instr函数

是字符查找函数，其功能是查找一个字符串在另一个字符串中首次出现的位置。instr函数在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置

语法

instr( string1, string2, start_position,nth_appearance )

参数

• string1：源字符串，要在此字符串中查找。
• string2：要在string1中查找的字符串 。
• start_position：代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
• nth_appearance：代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错

使用：

       位置索引号从1开始。
　　如果String2在String1中没有找到，instr函数返回0。
　　SELECT instr('syranmo','s') FROM dual; -- 返回 1
　　SELECT instr('syranmo','ra') FROM dual; -- 返回 3
　　SELECT instr('syran mo','at',1,2) FROM dual; -- 返回 0

<if test="grpId != null and grpId !=''">
and (instr(CONCAT('.',c.path_code,'.'),concat('.',#{grpId},'.'))>0)
</if>

最后

以上就是高大中心为你收集整理的sql语句开发笔记的全部内容，希望文章能够帮你解决sql语句开发笔记所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。