앱)sql 쿼리 mysql mybatis 샘플
<select id="select1" resultMap="MasterVO">
SELECT
COUNT(*)
, IFNULL((SELECT CD_NM FROM COMN_CD WHERE CD_GRP = 'CCC_CD' AND CD = CC_CD), '') AS statusCode
, CASE WHEN CD = 'M'
THEN
CASE WHEN USE_CD = '0' THEN 'POC'
ELSE 'MMM'
END
WHEN CD ='T' || CD="TE" THEN 'TEST'
WHEN CD ='N' THEN 'COMMERCIAL'
ELSE ''
END AS typeCodeName
, DATE_FORMAT(CRT_DT, '%Y-%m-%d %H:%i:%s') AS createDatetime
FROM
<where>
<if test="id ! = null and id !=''">
AND ID LIKE CONCAT('%', UPPER(#{id}), '%')
</if>
<if test="cd ! = null and cd !=''">
<choose>
<when test="cd == 'K'.toString()">
AND CD = #{cd}
</when>
<otherwise>
<if test="cd == "M".toString()">
AND CD= #{cd}
</if>
</otherwise>
</choose>
</if>
<if test="registeredDatetimeFrom ! = null and registeredDatetimeFrom !=''">
AND CRT_DT <![CDATA[>=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeFrom}, '000000'), '%Y%m%d%H%i%s')
</if>
<if test="registeredDatatimeTo ! = null and registeredDatetimeTo !=''">
AND CRT_DT <![CDATA[<=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeTo, '235959'), '%Y%m%d%H%i%s')
</if>
<if test="registeredDatetimeFrom != null and registeredDatetimeTo != null">
AND CRT_DT BETWEEN DATE_FORMAT(CONCAT(#{registeredDatetimeFrom}, '00:00:00'), '%Y%m%d%H%i%s') AND DATE_FORMAT(CONCAT(#{registeredDatetimeTo}, '23:59:59'), '%Y%m%d%H%i%s')
</if>
<choose>
<when test="sortName != null and sortNAme != ''">
<if test="sortName == 'Id'.toSTring()">
ORDER BY ID
</if>
</when>
<otherwise>
ORDER BY ...
</otherwise>
</choose>
AND SVC_OPEN_DT BETWEEN DATE_ADD(NOW(), INTERVAL- #{interval} month) and NOW()
AND SVC_FG IS NULL
AND ID1 = SUBSTRING_INDEX(#{ids}, '/', 1)
AND DATE_FORMAT(MDFY_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
ORDER BY DT DESC
<if test="countPerPage ! =0 ">
LIMIT #{startRowIndex}, #{countPerPage}
</if>
</where>
</select>
<select id="select" parameterType="java.util.HashMap">
SELECT
CONCAT('(', REPLACE(REPLACE(CD_NM, '(', ''), ')',''),')' )
, CONCAT(round((SUM(CNT)/TOTALCOUNT * 100),2),'%') AS percentage
, CONCAT('MN', LPAD(CAST(CAST(RIGHT(IFNULL(MAX(MN_ID), 'MN0000'), 4) AS UNSIGNED) + 1 AS CHAR), 4, '0')) AS value
FROM
WHERE
CRT_DT BETWEEN DATE_SUB(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY) AND DATE_ADD(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY)
AND DATE_FORMAT(CRT_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
<include refid="whereclause" />
</select>
<sql id="whereclause">
<if test="(ID != null and ID != '') or (NM != null and NM != '') or (desc !=null and desc != '')">
</if>
<if test='code.equals("K")'>
</if>
<if test="registeredDatetimeFrom != null and !''.equals(registeredDatetimeFrom)">
</if>
</sql>
<select id="" parameterType="String" resultType="VO">
TO_DAYS(NOWS()) - TO_DAYS(END_DD) AS CAL_DD
</select>
<select id="selectBatchDatetime" resultType="String">
SELECT
CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), 'GMT', DATE_FORMAT(NOW(), '%H:%i')) AS batchDt
FROM DUAL
</select>
<select id="xxx" parmeterType="map" resultType="hashmap">
SELECT COUNT(CASE WHEN A.YN='N' THEN 1 END) AS N_CNT, COUNT(CASE WHEN A.YN='Y' THEN 1 END) AS Y_CNT
FROM A;
</select>
<insert id="addMember" parameterType="VO">
INSERT IGNORE INTO MEMBER
(
<trim suffixOverrides=",">
ID
, NM
, CRTR_ID
, CRT_DT
, MDFR_ID
, MDFY_DT
</trim>
)
VALUES
(
<trim suffixOverrides=",">
#{id}
, #{nm}
, #{creatorId}
, SYSDATE()
, #{modifierId}
, SYSDATE()
</trim>
)
</insert>
<insert id="" useGeneratedKeys="true" parameterType="VO">
<selectKey resultType="string" keyProperty="seq" order="BEFORE">
SELECT CONCAT(LPAD(CAST(CAST(RAND()*1000000000 AS UNSIGNED) AS CHAR),8,'0'), "-", DATE_FORMAT(NOW(), '%Y%m%d'))
</selectKey>
INSERT INTO
(
OPRN_HIST_ID
)
VALUES
(
#{seq}
)
<update id="">
INSERT INTO
VALUES
ON DUPLICATE KEY UPDATE
</update>
<delete id="delete" parameterType="map">
DELETE FROM GRP
WHERE USR_ID IN
<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
JOIN TYPE
1. LEFT OUTER JOIN
2. INNTER JOIN
INSERT TYPE(중복키관리)
1. INSERT IGNORE
기존 레코드가 남아 있음
기존 레코드의 auto_increment 값은 변하지 않음
2. REPLACE INTO
기존 레코드가 삭제되고, 신규 레코드가 INSERT됨
따라서 auto_increment의 값이 변경됨
3. ON DUPLICATE UPDATE
INSERT IGNORE의 행동 포함
중복키 오류 발생시, 사용자가 UPDATE될 항목들을 지정할 수 있음
SELECT
COUNT(*)
, IFNULL((SELECT CD_NM FROM COMN_CD WHERE CD_GRP = 'CCC_CD' AND CD = CC_CD), '') AS statusCode
, CASE WHEN CD = 'M'
THEN
CASE WHEN USE_CD = '0' THEN 'POC'
ELSE 'MMM'
END
WHEN CD ='T' || CD="TE" THEN 'TEST'
WHEN CD ='N' THEN 'COMMERCIAL'
ELSE ''
END AS typeCodeName
, DATE_FORMAT(CRT_DT, '%Y-%m-%d %H:%i:%s') AS createDatetime
FROM
<where>
<if test="id ! = null and id !=''">
AND ID LIKE CONCAT('%', UPPER(#{id}), '%')
</if>
<if test="cd ! = null and cd !=''">
<choose>
<when test="cd == 'K'.toString()">
AND CD = #{cd}
</when>
<otherwise>
<if test="cd == "M".toString()">
AND CD= #{cd}
</if>
</otherwise>
</choose>
</if>
<if test="registeredDatetimeFrom ! = null and registeredDatetimeFrom !=''">
AND CRT_DT <![CDATA[>=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeFrom}, '000000'), '%Y%m%d%H%i%s')
</if>
<if test="registeredDatatimeTo ! = null and registeredDatetimeTo !=''">
AND CRT_DT <![CDATA[<=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeTo, '235959'), '%Y%m%d%H%i%s')
</if>
<if test="registeredDatetimeFrom != null and registeredDatetimeTo != null">
AND CRT_DT BETWEEN DATE_FORMAT(CONCAT(#{registeredDatetimeFrom}, '00:00:00'), '%Y%m%d%H%i%s') AND DATE_FORMAT(CONCAT(#{registeredDatetimeTo}, '23:59:59'), '%Y%m%d%H%i%s')
</if>
<choose>
<when test="sortName != null and sortNAme != ''">
<if test="sortName == 'Id'.toSTring()">
ORDER BY ID
</if>
</when>
<otherwise>
ORDER BY ...
</otherwise>
</choose>
AND SVC_OPEN_DT BETWEEN DATE_ADD(NOW(), INTERVAL- #{interval} month) and NOW()
AND SVC_FG IS NULL
AND ID1 = SUBSTRING_INDEX(#{ids}, '/', 1)
AND DATE_FORMAT(MDFY_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
ORDER BY DT DESC
<if test="countPerPage ! =0 ">
LIMIT #{startRowIndex}, #{countPerPage}
</if>
</where>
</select>
<select id="select" parameterType="java.util.HashMap">
SELECT
CONCAT('(', REPLACE(REPLACE(CD_NM, '(', ''), ')',''),')' )
, CONCAT(round((SUM(CNT)/TOTALCOUNT * 100),2),'%') AS percentage
, CONCAT('MN', LPAD(CAST(CAST(RIGHT(IFNULL(MAX(MN_ID), 'MN0000'), 4) AS UNSIGNED) + 1 AS CHAR), 4, '0')) AS value
FROM
WHERE
CRT_DT BETWEEN DATE_SUB(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY) AND DATE_ADD(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY)
AND DATE_FORMAT(CRT_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
<include refid="whereclause" />
</select>
<sql id="whereclause">
<if test="(ID != null and ID != '') or (NM != null and NM != '') or (desc !=null and desc != '')">
</if>
<if test='code.equals("K")'>
</if>
<if test="registeredDatetimeFrom != null and !''.equals(registeredDatetimeFrom)">
</if>
</sql>
<select id="" parameterType="String" resultType="VO">
TO_DAYS(NOWS()) - TO_DAYS(END_DD) AS CAL_DD
</select>
<select id="selectBatchDatetime" resultType="String">
SELECT
CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), 'GMT', DATE_FORMAT(NOW(), '%H:%i')) AS batchDt
FROM DUAL
</select>
<select id="xxx" parmeterType="map" resultType="hashmap">
SELECT COUNT(CASE WHEN A.YN='N' THEN 1 END) AS N_CNT, COUNT(CASE WHEN A.YN='Y' THEN 1 END) AS Y_CNT
FROM A;
</select>
<insert id="addMember" parameterType="VO">
INSERT IGNORE INTO MEMBER
(
<trim suffixOverrides=",">
ID
, NM
, CRTR_ID
, CRT_DT
, MDFR_ID
, MDFY_DT
</trim>
)
VALUES
(
<trim suffixOverrides=",">
#{id}
, #{nm}
, #{creatorId}
, SYSDATE()
, #{modifierId}
, SYSDATE()
</trim>
)
</insert>
<insert id="" useGeneratedKeys="true" parameterType="VO">
<selectKey resultType="string" keyProperty="seq" order="BEFORE">
SELECT CONCAT(LPAD(CAST(CAST(RAND()*1000000000 AS UNSIGNED) AS CHAR),8,'0'), "-", DATE_FORMAT(NOW(), '%Y%m%d'))
</selectKey>
INSERT INTO
(
OPRN_HIST_ID
)
VALUES
(
#{seq}
)
<update id="">
INSERT INTO
VALUES
ON DUPLICATE KEY UPDATE
</update>
<delete id="delete" parameterType="map">
DELETE FROM GRP
WHERE USR_ID IN
<foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
JOIN TYPE
1. LEFT OUTER JOIN
2. INNTER JOIN
INSERT TYPE(중복키관리)
1. INSERT IGNORE
기존 레코드가 남아 있음
기존 레코드의 auto_increment 값은 변하지 않음
2. REPLACE INTO
기존 레코드가 삭제되고, 신규 레코드가 INSERT됨
따라서 auto_increment의 값이 변경됨
3. ON DUPLICATE UPDATE
INSERT IGNORE의 행동 포함
중복키 오류 발생시, 사용자가 UPDATE될 항목들을 지정할 수 있음
'백엔드개발 > 데이터베이스' 카테고리의 다른 글
| 앱)create sql sample (0) | 2018.07.05 |
|---|---|
| 앱)Mysql 내장함수 정리 (0) | 2018.06.22 |
| 오라클 강의(14강~18강) (0) | 2018.06.03 |
| 오라클 강의(11강~13강) (0) | 2018.06.03 |
| 오라클 강의(8강~10강) (0) | 2018.06.03 |