【mysql】常用sql及函数

前言

慢慢补充

---

# 线程

查看MySQL线程

show processlise;

杀掉线程

kill 线程id；

mybatis相关

多个子查询，返回对象

其中的单个sql

插入

不存在就插入、存在就更新

insert into user values (1, '张三') on DUPLICATE key update name = '张三1'

mybatis sql块及向sql块传值

示例：http://www.zzvips.com/article/182705.html

<select id="getCountFocusCustomer" resultType="java.lang.Long">
        select count(tmp.customer_id)
        from
        (
            <include refid="focusCustomer_reservationTimeoutSevenDayCustomer_from_sql">
                <property name="col" value="distinct r.customer_id as customer_id"/>
            </include>
            union
            <include refid="focusCustomer_changeReservationTwoOrMore_from_sql">
                <property name="col" value="distinct rec.customer_id"/>
            </include>
        ) as tmp
    </select>
<sql id="focusCustomer_reservationTimeoutSevenDayCustomer_from_sql">
	 select ${col}
	 from reservation r
	 left join customer c on c.id = r.customer_id
	 left join dwd_customer_track dct on dct.customer_id = c.id
	 left join dws_reservation_log drl on drl.reservation_id = r.id
	 <where>
	 dct.first_arrival_time is null
	 and r.check_status != 7
	 and  datediff(concat(r.check_time ,' ' , r.begin_check), if(drl.last_reschedule_time is null, r.create_time, drl.last_reschedule_time)) > 7
	 <if test="consultantIds != null and consultantIds.size() > 0">
	     and c.consultant_id in
	     <foreach item="item" index="index" collection="consultantIds" open="(" separator="," close=")">
	         #{item}
	     </foreach>
	 </if>
	 </where>
	</sql>

select相关

with select 临时表

with c1 as (select * from channel)
select a.*, concat(c1.name, c2.name) as channelName from user a
left join c1 on a.channel_pid = c1.id
left join c2 on a.cahnnel_id = c2.id

select into from()与insert into select()函数

示例：https://blog.csdn.net/weixin_43660536/article/details/118583069
使用场景：一般在处理数据的时候使用

json相关

mysql查询数据从json内容中直接取值
示例：description字段里面存储的是一个json字符串，sql查询的时候直接取它里面的某个值
通过测试可以发现在语法中，$表示的就是这个字符串本体

如果json是数组

select description ->> '$[0].id' as cc from system_config where type = 'automatically_assign_channel_config';

如果json是对象

select description ->> '$.id' as cc from system_config where type = 'automatically_assign_channel_config';

包含相关in

FIND_IN_SET

查询包含指定内容的字段，第一个参数为内容，第二个参数为筛选的字段。
数据

查询示例

---

分组相关group by

GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下：GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

• 属性名：是指按照该字段的值进行分组。
• HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。
• WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。

HAVING

数据

要求：查询分组数量大于2的有哪些？

分组排序

如果想对分组的数据进行控制，可以先排序，然后再分组。
错误示例

select * from a group by id order by createdate desc;

正确示例
示例

select * from (
	select * from a order by createdate desc limit 1000000
) as b 
group by id

为什么添加limit？

其他方式

-- 方式1
select * from (
	select * from a order by createdate desc limit 100000000
) as b 
group by id;

-- 方式2
SELECT
	customer_id,
	SUBSTRING_INDEX(GROUP_CONCAT( id ORDER BY finish_time DESC,id desc ),',',2) id
FROM
	beautiful_center_log GROUP BY customer_id;
	
-- 方式3
SELECT
	DISTINCT customer_id,
	FIRST_VALUE( id ) OVER ( PARTITION BY customer_id ORDER BY finish_time DESC,id desc ) id 
FROM
	beautiful_center_log ;

多行转一行GROUP_CONCAT

https://blog.csdn.net/s1441101265/article/details/102930214
数据

示例
可以看到，通过GROUP_CONCAT可以把同一个分组的数据多行转成一行显示

group_concat中设置内容间隔符

转载自：https://blog.csdn.net/weixin_46775407/article/details/119145946
示例

select group_concat(a.name SEPARATOR '***') as nameStr from user;

mysql中group和case when条件下的聚合函数怎么使用

SELECT
	a.id,
	b.companyid,
	sum(a.amount) as signTotalAmount,
	sum(CASE WHEN a.sign_status = 0 THEN a.amount else 0 END) AS unauditedAmount,
	sum(CASE WHEN a.`status` = 1 THEN a.amount else 0 END) AS unpaidAmount,
	sum(CASE WHEN a.sign_status = 1 and a.status = 2 THEN a.amount else 0 END) AS paidAmount,

	count(DISTINCT(a.payment_user_id)) as customerCount,
	count(CASE WHEN a.sign_status = 1 THEN a.id else null END) AS signNum,
	ROUND(sum( CASE WHEN a.sign_status = 1 THEN a.amount else 0 END)/count(CASE WHEN a.sign_status = 1 THEN a.id else null END),2) AS signAmountAvg,
	a.createby, DATE_FORMAT(a.createdate,'%Y-%m-%d %H:%i:%s') as fmtCreatedate
	FROM
	pmi_transaction_pay_apply a
	LEFT JOIN pmi_account_info b ON a.to_accountid = b.account_id
	WHERE
	a.type = 2 and a.is_delete = 0
GROUP BY
	a.to_accountid
order by
	a.sign_status asc, a.createdate desc

---

排序

自定义排序FIND_IN_SET

ORDER BY FIND_IN_SET(a.userid, (select GROUP_CONCAT(user_id) from md_pmi_empower where isdelete = 0 and status =3 )) desc

自定义排序case when

SELECT id, NAME FROM `paixu`
ORDER BY
	(
		CASE
		WHEN id = 4 THEN
			1
		WHEN id = 1 THEN
			2
		WHEN id = 2 THEN
			3
		WHEN id = 3 THEN
			4
		END
	) ASC

自定义排序FIELD

FIELD这个函数在使用的时候最好还是多了解一下。
提示：可以配合order by FIELD(XX,XX) DESC来使用

排序失效

在使用过程中自定义排序会遇到失效的情况，这时候网上提供的解决方案是FIELD配合in一起使用。
比如：https://blog.csdn.net/hkl_Forever/article/details/120415442
后面自己测了下，发现可以用FIND_IN_SET替代，就是上面的这个。

数据

示例

SELECT id,name FROM `paixu` order by FIELD(id,4,1,2,3) asc;