Mysql中几种sql的常见用法

mysql如何更新替换某一个字段中的特定字符串

UPDATE t_bss_employees
SET mobilephone = REPLACE (mobilephone, "2129", "0000")
WHERE
	id IN (
		SELECT
			a.id
		FROM
			(
				SELECT
					id
				FROM
					t_bss_employees
				WHERE
					mobilephone IN (
						'18121299262',
						'18121299247',
						'18121299206',
						'18121299209'
					)
			) AS a
	)

将mobilephone字段中的2129字符串替换为0000
使用当前表为条件更新当前表。需要在条件处再添加一个（）构建一个虚拟表

求比例的sql

	select a.style, ROUND(
            b.num / a.sum * 100,
            2
        ) as styleRate from (SELECT 
	count(qspc.id) sum 
	,qq.style  style
	from  t_qc_security_plan_comment  qspc 
		inner join t_qc_question qq
		on qq.id = qspc.question_id
		LEFT JOIN t_qc_address qa ON qa.id = qspc.address_id
		LEFT JOIN t_system_organ  so on qa.hospital =  so.id
		WHERE qq.scr_level is not NULL
		and qq.type = 3  and so.tenant_code = 'zzyy'
		GROUP BY style) a left join (SELECT 
	count(qspc.id) num 
	,qq.style  style
	from  t_qc_security_plan_comment  qspc 
		inner join t_qc_question qq
		on qq.id = qspc.question_id
		LEFT JOIN t_qc_address qa ON qa.id = qspc.address_id
		LEFT JOIN t_system_organ  so on qa.hospital =  so.id
		WHERE qq.scr_level is not NULL
		and qq.type = 3  and so.tenant_code = 'zzyy'
		and qspc.is_bad =0
		GROUP BY style) b on a.style = b.style	

计算逻辑是分别求总数和平均数。然后两数相除再用round求精度

希望本文对你有所帮助~~如果对软件测试、接口测试、自动化测试、面试经验交流感兴趣可以加入我们。642830685，免费领取最新软件测试大厂面试资料和Python自动化、接口、框架搭建学习资料！技术大牛解惑答疑，同行一起交流。