隨著業務的發展，越來越多的應用系統都從一個大的系統分拆成多個小的系統，各個系統之間通過一定的通訊協議進行資料交換。這樣就會導致一些小的應用系統自己不用去進行資料庫的操作，只需要進行一些rpc呼叫或者快取就可以拿到資料進行展示。我之前參與的一個專案就是這樣的情況，而我也是將近7個多月的時間沒有寫過一行SQL。
近期參與的一個專案的資料大多都市基於資料庫來進行資料互動的，所以免不了的要寫大量的SQL，所以本篇就總結一下一些SQL的基本寫法，以備後用。
建表
CREATE TABLE IF NOT EXISTS `user_test` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增長id',
`user_name` varchar(128) NOT NULL COMMENT '使用者名稱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='使用者表';
複製程式碼查詢
- 簡單的查詢
<select id="queryUserByName" resultMap="userMap" parameterType="java.lang.String">
SELECT * FROM user_test WHERE user_name = #{userName}
</select>
複製程式碼需要注意的是如果這裡不指定parameterType，則預設會識別處理；如果指定了型別，則傳入的值就需要和當前指定的型別保持一致，不然就會出現資料型別轉換異常。
- 簡單分頁查詢
<select id="queryUsersList" resultMap="userMap">
SELECT * FROM user_test WHERE 1=1
<if test="keyword != null and keyword != ''" >
AND user_name LIKE concat('%',#{keyword},'%')
</if>
LIMIT #{currentPage},#{pageSize}
</select>
複製程式碼- left join
app_info表和app_verion表分別儲存的是應用資訊和應用版本資訊。現在要根據appId和versionId查出一個應用的具體資訊【包括資訊資訊和版本資訊】
<select id="getAppDetail" resultMap="appDeatilMap">
select m.id id,
m.app_name appName,
n.version version,
from app_info m
LEFT JOIN app_version n ON m.id = n.app_id
where m.id = #{appId} and n.id = #{versionId}
</select>
複製程式碼- 查詢條件是list
<select id="queryAppByAppNames" resultMap="AppMap" parameterType="java.util.List">
select
a.app_name appName,
b.version version
from starter_info a,starter_version b
where
a.id = b.app_id
and a.id in
(
select id from app_info where app_name in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
)
</select>
複製程式碼更新
- 簡單的更新
<update id="updateApp" parameterType="java.util.List">
UPDATE app_info
SET
app_name = #{appName}
WHERE
app_id = #{appId}
</update>
複製程式碼- 批量更新
有這樣一個需求，把 app_info表中id 為1，2，3的app的app_name改為appName1，appName2，appName3;
使用 case ..when ..then 這樣的語法結構來完成：
case 是當前的條件，when表示條件值，then後面是當前目前更新欄位的值；
下面的說明：當前id=#{item.appId}時,app_name=#{item.appName}
<update id="updateApps" parameterType="java.util.List">
UPDATE app_info set app_name =
<foreach collection="applList" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.appId,jdbcType=INTEGER} then #{item.appName,jdbcType=INTEGER}
</foreach>
where id in
<foreach collection="appList" index="index" item="item" separator="," open="(" close=")">
#{item.appId,jdbcType=INTEGER}
</foreach>
</update>
複製程式碼OK，現在於這樣的需要：
根據應用型別的不同，更新不同的執行環境配置；
{
[
{
"appType":"applet",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1
},
{
"appType":"bs",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1
},
{
"appType":"cs",
"cpu":5,
"memory":4,
"card":3,
"nums":2,
"network":1,
"isInUse":1
},
//有幾個放幾個
]
}
複製程式碼trim屬性說明
- 1.prefix,suffix 表示在trim標籤包裹的部分的前面或者後面新增內容
- 2.如果同時有prefixOverrides,suffixOverrides 表示會用prefix,suffix覆蓋Overrides中的內容。
- 3.如果只有prefixOverrides,suffixOverrides 表示刪除開頭的或結尾的xxxOverides指定的內容。
<update id="updateBatchApp" parameterType="java.util.List">
UPDATE app_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="cpu = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
<if test="item != null">
when app_type =#{item.appType} then #{item.cpu}
</if>
</foreach>
</trim>
<trim prefix="memory = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
<if test="item != null">
when app_type =#{item.appType} then #{item.memory}
</if>
</foreach>
</trim>
<trim prefix="card = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.card}
</foreach>
</trim>
<trim prefix="nums = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.nums}
</foreach>
</trim>
<trim prefix="network = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.network}
</foreach>
</trim>
<trim prefix="is_in_use = case" suffix="end,">
<foreach collection="modelList" item="item" index="index">
when app_type =#{item.appType} then #{item.isInUse}
</foreach>
</trim>
</trim>
where app_id = #{appId}
</update>
複製程式碼關於效能問題沒做研究，之前看過關於不同更新語句寫法的一篇效能的分析，大家有興趣可以看下：批量更新資料兩種方法效率對比
刪除
- 簡單刪除
DELETE FROM app_info where id = #{id}
複製程式碼- 批量刪除
<delete id="deleteApps" parameterType="java.util.List">
DELETE FROM app_info where app_id in
<foreach item="item" collection="appIds" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
複製程式碼時間字串 order by
不知道各位是否遇到過，之前的前輩們在專案中將時間用字串的方式存在DB中，而不是使用DATE,然後有一天你的前輩走了，你的主管說查出來按時間來排序....；呵呵，好！！！
<select id="querySysParamList" resultMap="sysParamDO">
SELECT * FROM app_info WHERE 1=1
<if test="keyword != null and keyword != ''" >
AND app_name LIKE concat('%',#{keyword},'%')
</if>
ORDER BY DATE_FORMAT(update_time,'%H %k %I %r %T %S %w') DESC
</select>
複製程式碼字串轉為日期格式 SELECT DATE_FORMAT('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
把日期轉為字串格式 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');
附：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英語字首的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 數字, 4 位
%y 年, 數字, 2 位
%a 縮寫的星期名字(Sun……Sat)
%d 月份中的天數, 數字(00……31)
%e 月份中的天數, 數字(0……31)
%m 月, 數字(01……12)
%c 月, 數字(1……12)
%b 縮寫的月份名字(Jan……Dec)
%j 一年中的天數(001……366)
%H 小時(00……23)
%k 小時(0……23)
%h 小時(01……12)
%I 小時(01……12)
%l 小時(1……12)
%i 分鐘, 數字(00……59)
%r 時間,12 小時(hh:mm:ss [AP]M)
%T 時間,24 小時(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一個星期中的天數(0=Sunday ……6=Saturday ）
%U 星期(0……52), 這裡星期天是星期的第一天
%u 星期(0……52), 這裡星期一是星期的第一天
%% 一個文字“%”。
複製程式碼先記錄這些，有坑再補！
參考：http://www.runoob.com/sql/sql-tutorial.html