关于mysql中一些使用技巧
1.对于count()函数
select
count(case when a.connect_status = 1 and a.is_can_connect = 0 then 1 end) as joinTotal,
count(( c.state = '1') OR NULL ) joinUpTotal,//这个后面一定要加or(不论是0/1)
count(( is_remote = '0' AND province = '1114' AND deleted = '0' ) OR NULL) remoteTotal
from table
count(distinct user_operate_id) as number(可以去重统计)
2.对于like模糊查询
<if test="iuConnectedHospitalModel.hospitalName != null and iuConnectedHospitalModel.hospitalName != ''">
and ( hospital_name LIKE CONCAT ('%', #{iuConnectedHospitalModel.hospitalName},'%')
or hospital_name_alias LIKE CONCAT ('%', #{iuConnectedHospitalModel.hospitalName},'%'))
</if>
3.关于外连接,内连接,左连接,右连接的区别
左连接(一般情况都用这个)
select * from tablea LEFT JOIN tableb on tablea.aid=tableb.bid;
以左表为主 (右链接相反)内连接 是取where id都相等的 也就是公共的
4.派生表(临时表)
select count(*) from (select * from list where name="xiao") as t;
from 后面是临时表 一定要起一个名字 一般都是通过分组等条件筛选好的一张临时表
5.更新or添加一起写
<insert id="saveOrUpdateOld">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(*) from hospital_config where hospital_id = #{hospitalConfig.hospitalId} and uuid is null
</selectKey>
<if test="count > 0">
update hospital_config
<set>
<if test="hospitalConfig.commonConfig != null">
common_config = #{hospitalConfig.commonConfig},
</if>
<if test="hospitalConfig.config != null">
config = #{hospitalConfig.config},
</if>
<if test="hospitalConfig.address != null">
address = #{hospitalConfig.address},
</if>
update_time = sysdate(),
update_user =#{hospitalConfig.updateUser},
</set>
where hospital_id = #{hospitalConfig.hospitalId} and uuid is null
</if>
<if test="count==0">
insert into hospital_config (hospital_id,uuid,address, common_config, config, create_time,
update_time,update_user)
values(#{hospitalConfig.hospitalId},#{hospitalConfig.uuid},#{hospitalConfig.address},#{hospitalConfig.commonConfig},#{hospitalConfig.config},sysdate(),sysdate(),#{hospitalConfig.updateUser})
</if>
</insert>
6. distinct 和 group by 的区别
distinct和group by都可以用来去重,
不同之处,distinct针对全部字段去重,而group by可以针对全部字段中的单一字段去重。
两者执行方式不同,distinct主要是对数据两两进行比较,需要遍历整个表。group by分组类似先建立索引再查索引,当数据量较大时,group by速度要优于distinct。
7.在sql中用if实现三元运算符
8.in拼接
<if test="hosIds!=null and hosIds.size()>0">
and hospital_id in
<foreach collection="hosIds" index="index" item="hosId" separator="," close=")" open="(">
#{hosId}
</foreach>
</if>