做网站推广/seo搜索引擎优化论文
本文主要向大家介绍了MySQL数据库之MYSQL的动态SQL各类语法讲解 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。
if语法
parameterType="AccountsAdjustAccountSet002Dto">
SELECT COUNT(1) FROM BOK_BOOKSET
WHERE STATUS='E'
AND BOOKSET_NAME = #{booksetName}
AND BOOKSET_ID != #{booksetId}
select
NVL(sum(PRIN_AMT),0) PRIN_AMT
from
SEC_ORG_DEAL
where
STATUS IN ('A','E')
AND ENTRUST_ID = #{entrustId}
AND WORKFLOW_ID != #{workflowId}
choose, when, otherwise语法
SELECT ASSET_ID assetId,
SEC_ID secId,
ISIN_ID isinId,
SEC_ABBR secAbbr,
SEC_NAME secName,
INT_RULE_ID intRuleId,
RESET_RULE_ID resetRuleId,
UPDATE_TIME updateTime
FROM
SEC_INFO_TMP T
SEC_INFO_HIS T
SEC_INFO T
where, set语法，解决首尾问题
select
SUBJECT_NO id,
SUBJECT_ID object,
SUBJECT_NAME text,
'0'||SUBJECT_LEVEL as col
from
BOK_SUBJECT
and BOOKSET_ID = #{booksetId}
and STATUS = 'E'
update WFL_AGENT
START_DATE = #{startDate,jdbcType=DATE},
AGENT_TYPE = #{agentType,jdbcType=VARCHAR},
AGENT_ID = #{agentId,jdbcType=VARCHAR},
WORKFLOW_ID = #{workflowId,jdbcType=VARCHAR}
where USER_ID = #{userId,jdbcType=VARCHAR}
and START_DATE = #{startDateToUpdate,jdbcType=DATE}
最强大的foreach语句 collection是参数，一般是LIST 或者数组 item是迭代的元素
用于批量insert
insert into SJYZ_PROJ_ASSET_INFO (
PROJ_ASSET_INFO_ASSET_ID
,PROJ_ASSET_INFO_ASSET_NAME
,PROJ_ASSET_INFO_FINANCING_SIZE
,PROJ_ASSET_INFO_CCY
,INV_RATE_QUO_UPDATE_TIME
)
values
(
#{INFO.PROJ_ASSET_INFO_ASSET_ID,jdbcType=VARCHAR}
,#{INFO.PROJ_ASSET_INFO_ASSET_NAME,jdbcType=VARCHAR}
,#{INFO.PROJ_ASSET_INFO_FINANCING_SIZE,jdbcType=DECIMAL}
,#{INFO.PROJ_ASSET_INFO_CCY,jdbcType=VARCHAR}
,#{INFO.INV_RATE_QUO_UPDATE_TIME,jdbcType=TIMESTAMP}
)
用于查询，多个参数实现IN
SELECT NULL AS hisNo,
T.WORKFLOW_ID workflowId,
T.ASSET_ID AS assetId,
T.SEC_ID AS secId,
T.SEC_ABBR secAbbr,
T.SEC_TYPE secType
FROM SEC_INFO_TMP T
WHERE
T.CREATE_USER = #{loginUserId}
AND T.SEC_ID LIKE '%'||#{assetId}||'%'
0">
AND T.COUPON_SPECIES IN
open="(" separator="," close=")">
'${item}'
SELECT ASSET_ID
FROM PROJ_ASSET_INFO
WHERE STATUS IN ('A','P')
0">
AND ASSET_ID IN
open="(" separator="," close=")">
#{item}
include语法 静态包含
SELECT distinct
secInfo.hisNo,
secInfo.workflowId,
secInfo.assetId,
secInfo.secId,
secInfo.secAbbr,
secInfo.secType,
secInfo.vdate
secInfo ORDER BY secInfo.assetId, secInfo.workflowId DESC
本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！