函数"/>
mysql中常用的函数
1. find_in_set()
有个文章表里面有个type字段,他存储的是文章类型,有 1头条,2推荐,3热点,4图文
现在有篇文章他既是 头条,又是热点,还是图文,
type中以 1,3,4的格式存储.
我们如何用sql查找所有type中有4图文标准的文章呢??
这就要我们的find_in_set出马的时候到了.
以下为引用的内容:
select * from article where FIND_IN_SET('4',type)-------------------------------------------------------------------------------------------
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
结果为2 因为b 在strlist集合中放在2的位置 (索引从1开始 )
--------------------------------------------------------
select FIND_IN_SET('1','1'); 返回 就是1 这时候的strlist集合有点特殊 只有一个字符串 其实就是要求前一个字符串 一定要在后一个字符串集合中 才返回 大于0的数
select FIND_IN_SET('2','1,2');返回2
select FIND_IN_SET('6','1'); 返回0
2. case when then else end
1. 用做多条件的排序
select * from newcloud_access_card order by
case
when card_status='0' and type ='1' then 0
when card_status='0' and type ='0' then 1
when card_status='1' and type ='1' then 2
when card_status='1' and type ='0' then 3
when card_status='2' and type ='1' then 4
when card_status='2' and type ='0' then 5else 6
end;2. 重命名
select ui.user_id,ui.user_name,
case ui.user_type
when 'company_legal_person' then '企业法人'
when 'gov_leader' then '政府领导'
when 'gov_grid_worker' then '网格员'
when 'gov' then '政府用户'
when 'company' then '企业用户'
when 'company_safer' then '企业安全员'
when 'company_safe_manager' then '企业安全管理员'
when 'company_charger' then '企业安全负责人'
when 'gov_fulltime_worker' then '专职人员'
else '其他' end as user_type,
ui.mobile from user_info ui
left join login_user_relation lur on lur.user_id = ui.user_id
left join login_info li on li.login_id=lur.login_id
where lur.status = '1' and ui.status = '1' and li.status='1'
and uipany_id = 'iksbe634ajogetpn'3. 数据统计
sum(1) == count(1) sum(0) 相当于不统计
SELECT SUM(CASE WHEN status = '0' OR status = '2' THEN 1 ELSE 0 END) AS '待维保',
SUM(CASE WHEN create_date BETWEEN '2018-09-5 0:41:18' and '2019-07-23 23:41:18' THEN 1 ELSE 0 END) AS '维保总数',
SUM(CASE WHEN status = '1' and update_date BETWEEN '2018-09-5 0:41:18' and '2019-07-23 23:41:18' THEN 1 ELSE 0 END) AS '已维保'
FROM newcloud_fire_process where type='4' and unit_id='DccBRhlrSiu9gMV7fmvizw'
3. concat方法(用作模糊查询)
<select id="getGroupsList" resultType="Map">SELECTid,name,avatar,"1" as typeFROM im_chat_groupWHERE id IN (SELECT cg.chat_group_idFROM im_chat_group_user cgWHERE cg.user_id = #{userId})<if test="name!=null and name.trim()!=''">and name like concat('%',concat(#{name},'%'))</if> </select>
4. (id1,id2,id3,id4) 实现批量修改
<update id="batchOptionByIds" parameterType="map">update im_user<set><if test="password != null">password = #{password},</if><if test="status != null">status = #{status},</if></set>where id in<foreach item="id" collection="ids" open="(" separator="," close=")">#{id}</foreach> </update>
5. IFNULL
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
SELECT IFNULL(NULL, "RUNOOB"); RUNOOBSELECT IFNULL("Hello", "RUNOOB"); Hello
.htm
更多推荐
mysql中常用的函数
发布评论