mysql相关

查询指定时间相近的记录

SELECT * abs(UNIX_TIMESTAMP(t.create_time)-UNIX_TIMESTAMP('2020-06-04 10:10:39')) as min from t_video_history t WHERE t.ip = '10.0.5.124' GROUP BY min asc limit 1

查询一个月之前的数据
select * from t_video_history  t where  date_format(t.create_time,'%Y-%m-%d') <= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m-%d')


批量修改
<update id="batchUpdate"
        parameterType="com.develop.common.domain.FileDO"
        useGeneratedKeys="true" keyProperty="id">
    <foreach collection="list" item="item" separator=";">
        update
        sys_file
        set
        <trim prefix="" suffix=" " suffixOverrides=",">
            <if test="item.score != null">
                score=#{item.score},
            </if>
            <if test="item.des != null">
                des=#{item.des},
            </if>
        </trim>
        where id = #{item.id}
    </foreach>
</update>```	

替换字段中的部分字符串
UPDATE tb_test_example  te set te.example_name = replace (te.example_name,'接口','模拟接口')

清空表数据
TRUNCATE TABLE tablename

删除指定时间段数据
DELETE FROM t_video_history WHERE create_time BETWEEN '2020-06-13 00:00:01' AND '2020-06-20 23:59:59'

mysql查询查询树结构,通过树结构查询一个自己昵称和父级节点的昵称
SELECT
	t1.id,
	t1.parent_id,
	t1.NAME,
	t1.`level`,
	t1.des,
	t1.create_time,
	t1.update_time,
	t1.create_by,
	t1.update_by,
	t2.`name` AS parentName 
FROM
	t_job_type t1
	LEFT JOIN t_job_type t2 ON t1.parent_id = t2.id
	
**mysql查询某字段为空 或者不为空的语句**
1.不为空
select * from table where id <> "";
select * from table where id != "";
select * from table where is not null;
2.为空
select * from table where id ="";
select * from table where isNull(id);
select * from table where id is null;
https://www.cnblogs.com/sqldata/p/10909868.html

同一张表内，查询包含自身的子级

select id from t_resource_type WHERE
(id=#{typeId}
or parent_id=#{typeId}
or parent_id IN(select id from t_resource_type where parent_id=#{typeId})
or parent_id IN (select id from t_resource_type where parent_id in(select id from t_resource_type where parent_id=#{typeId}))) and is_del=1

模糊查询 
SELECT * FROM `sys_dict_data` WHERE locate("1",dict_label)>0

指定时间段内注册的用户
where t.name = '张三' 
and  t.createtime = between now()  - interval  1 day and  now() ;

**IP 地址存储**
将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据
MySQL 提供了两个方法来处理 ip 地址
inet_aton 把 ip 转为无符号整型 (4-8 位)
inet_ntoa 把整型的 ip 转为地址
插入数据前，先用 inet_aton 把 ip 地址转为整型，可以节省空间，显示数据时，使用 inet_ntoa 把整型的 ip 地址转为地址显示即可。

SELECT
* 
FROM
	ipv4_plan_address 
WHERE
	inet_aton( ip_address ) > inet_aton('192.168.1.3') 
	AND inet_aton( ip_address ) < inet_aton('192.168.1.20');