sql 语句总结

一、查询结果字符拼接

SELECT t.*, CONCAT(IFNULL(d.device_num,''),IFNULL(d1.device_num,'')) AS deviceNum,d.device_name,pr.name AS provinceName,cr.name AS cityName,dr.name AS districtName  FROM simcard t
    LEFT JOIN emergency d ON t.device_id = d.id
    LEFT JOIN battery d1 ON t.device_id = d1.id
    LEFT JOIN region pr ON pr.id=t.province_id
    LEFT JOIN region cr ON cr.id= t.city_id
    LEFT JOIN region dr ON dr.id=t.district_id
   

二、拼接多行数据到一列

select group_concat(device_type_id) AS deviceIds from company_devicetype_relation where company_id=108

 

 

三、字符型数字比较，注意后面加0

<if test="maxPower!=null and maxPower!=''">
          d.voltage+0 <![CDATA[ <= ]]> #{maxPower,jdbcType=INTEGER} and
      </if>

 四、查询当前数据库连接的客户端ip信息

select *, SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

 五、mysql 数据库备份的最小权限操作

1.创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
2、赋予最小权限（查询、锁表、查询视图、触发器、事件）

grant select,lock tables,show view,trigger,event on database.* to 'backup'@'%';

六、创建event

//直接执行sql，每一分钟执行一次，修改时间超过10分钟记录
CREATE    
    EVENT myevent    
    ON SCHEDULE    
      EVERY 1 MINUTE STARTS '2020-10-01 00:00:00'    
    DO    
      update ilock.clientip_info set login_wrong_count=0 where login_time<(select date_sub(now(), interval 10 MINUTE));

//调用存储过程
DELIMITER $$

ALTER DEFINER=`root`@`%` EVENT `do_loginip_checked` ON SCHEDULE EVERY 1 MINUTE STARTS '2020-10-27 11:58:29' ON COMPLETION NOT PRESERVE ENABLE DO call loginip_checked$$

DELIMITER ;

 

七、查询某一些占用的空间大小

select sum(length(response_value)) from history_command

 

八、去除特殊字符

update param_type_back1 set type_code = rtrim(type_code)

update param_type_back1 set type_code= replace(type_code ,'\t','');