随着业务的发展, 越来越多的应用系统都从一个大的系统分拆成多个小的系统, 各个系统之间通过一定的通信协议进行数据交换. 这样就会导致一些小的应用系统自己不用去进行数据库的操作, 只需要进行一些 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>
关于性能问题没做研究, 之前看过关于不同更新语句写法的一篇性能的分析, 大家有兴趣可以看下: 批量更新数据两种方法效率对比 https://blog.csdn.net/xu1916659422/article/details/77971696
删除
简单删除
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
来源: https://juejin.im/post/5ad1b61d5188257ddb100d2c