mysql中常用sql（持续补充中）

mysql中常用sql（持续补充中）

时间转字符串：
    date_format(now(), '%Y-%m-%d'); 
    
字符串转时间：
    str_to_date('2016-01-02', '%Y-%m-%d %H');

日期增加40天

update monitor_xx set kpi_time = DATE_ADD(kpi_time,interval 40 day)
    
保留小数点后两位：
    （四舍五入）：convert(4545.1366,decimal);
    （截取）：truncate(4545.1366,2);
    
mysql的模糊查询：
    m.name like CONCAT(CONCAT('%', #{material_name}),'%')
    
防止科学记账法：
    convert(d.decimal_num,DECIMAL(10, 5))

查看表字段属性：
    select column_name,column_comment,data_type from information_schema.columns 
    where table_name='' and table_schema='njwd_cost8'

相同属性行拼接，默认用,隔开：
    select range_type,GROUP_CONCAT(food_id) food_id
    from wd_sys_income_range

使用instr函数能优化like不走索引的性能： 

select * from monitor_device_info_edge where city like '%安%';
select * from monitor_device_info_edge where instr(city,'安')>0
    group by range_type
    
空值替代:
    IFNULL(expression_1,expression_2)     如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果;

获取字符串的长度：
    length(str)同时也可以用于条件不为空的判断
 
去掉两头空格：
    sql语法中没有直接去除两头空格的函数，但有ltrim()去除左空格rtrim()去除右空格。
    合起来用就是sql的trim()函数，即select ltrim(rtrim(UsrName))

查看锁表进程并杀掉：
    SELECT * FROM information_schema.innodb_trx;
    kill 1231447（进程号）

添加表字段
    alter table wd_material_enterprise  add `conf_addr` varchar(200) DEFAULT NULL COMMENT '采购地点'
    alter table wd_enterprise_info  add  `is_conf_addr` int(1) NOT NULL DEFAULT '0' COMMENT '是否配置供应商地址【0：不配，1：配置】'
    
mybatis新增数据之后返回id 
    <insert id="addCgOrder" parameterType="map" useGeneratedKeys="true" keyProperty="id">

查找重复数据，并删除重复数据，保留一条

原方法：
    delete from wd_material_2334  where id in (select b.* from (select t.id from wd_material_2334 t
    GROUP BY  t.enterprise_id,t.dept_id,t.material_id HAVING count(t.id) > 1 ) b) 
    and id not in (select b.* from (select max(t.id) from wd_material_2334 t
    GROUP BY  t.enterprise_id,t.dept_id,t.material_id HAVING count(t.id) > 1 )b)

优化方法：

    delete a from wd_material_636 a
    where a.stock_date = '2019-08-02'
     and exists (
                    select 1
                        from (
                                        select ms2.enterprise_id, ms2.dept_id, ms2.material_id, ms2.stock_date, count(0), max(ms2.id) maxId
                                            from wd_material_636 ms2
                                         where ms2.stock_date = '2019-08-02'
                                         group by ms2.enterprise_id, ms2.dept_id, ms2.material_id, ms2.stock_date
                                        having count(0) > 1
                                 ) tb
                        where a.enterprise_id = tb.enterprise_id
                            and a.dept_id = tb.dept_id
                            and a.material_id = tb.material_id
                            and a.stock_date = tb.stock_date
                            and a.id != tb.maxId
               );

连表更新
    update  wd_material_supplier m, wd_material m1
    set m.supplier_id = 337 
    where m.material_id = m1.id
    and m.enterprise_id = 7596
    and m.supplier_id = 338 

递归查询父类下的所有子类别

    用法：select * from wd_material_style where FIND_IN_SET(id,queryChildrenAreaInfo(255))
    函数设置：CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenAreaInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8
    函数：
    BEGIN
    DECLARE sTemp VARCHAR (4000);
    DECLARE sTempChd VARCHAR (4000);
    SET sTemp ='$';
    SET sTempChd = cast(areaId AS CHAR);
    WHILE sTempChd IS NOT NULL DO
    SET sTemp = CONCAT(sTemp, ',', sTempChd);
    SELECT group_concat(id) INTO sTempChd FROM wd_material_style WHERE FIND_IN_SET(parent_id, sTempChd) > 0;
    END WHILE;
    RETURN sTemp;
    END

批量新增：
insert into wd_material_636 (stock_date,stock_end_time) VALUES
        <foreach collection="list" item="i" separator=",">
            (
            #{i.stock_date},
            #{i.stock_end_time}
            )
        </foreach>

更新时的case when
    update t_student set num = 
    case when num+2<0 then 0
    else num+2 END
    where stuno = 1

连表新增：
    insert into wd_sys_menu_report_user (user_id,menu_id) select 15894,id from wd_sys_menu_report
    
增删查索引
show index from wd_material_2334 
alter table wd_material_636 drop index root_enterprise_id;;
alter table wd_material_2334 add UNIQUE KEY `only` (`stock_date`,`enterprise_id`,`dept_id`,`material_id`) USING BTREE;

查询结果为null的情况下显示为0

SELECT
CASE WHEN count(1) = 0 THEN 0
ELSE number END number
FROM (
        SELECT id number
        FROM wd_other_stock_in
        WHERE id = -1
    ) t

取同条件下的最新数据
    select max(t.id) from wd_material_2334 t
    where enterprise_id = 2335 and stock_date = '2019-09-22'
    GROUP BY t.enterprise_id,t.material_id

String字段的if条件注意点
    <when test="sqfbcz=='是'">改为：<when test='sqfbcz=="是"'>

防止数据重复插入：

insert into student (`name`,`age`) select #{name},#{age} from DUAL
where not exists (select * from student where `name` = #{name} and `age`=#{age})

无则新增，有则修改
insert into student( name,age) values ('zhangsan',2)
on duplicate key update name = values(name),age = values(age)

查询数据库数据使用空间：

select sum(t.data_size)+sum(t.index_size) from 
(select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'sg_iot') t