mysql之插入，清理，更新的多表组合操作

最新推荐文章于 2025-05-01 23:24:31 发布

筱F

最新推荐文章于 2025-05-01 23:24:31 发布

阅读量184

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/FYW_1121/article/details/108165134

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍MySQL中处理数据的高级技巧，包括使用复杂SQL语句删除重复数据、通过模糊查询将数据从一个表插入到另一个表、更新表字段以及利用正则表达式进行数据清理。这些技巧对于数据库管理员和开发人员来说非常实用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql之删除单表内的重复数据

DELETE FROM
	plat_position
WHERE
	position_id IN (
		SELECT
			tp.position_id
		FROM
			(
				SELECT
					ta.position_id
				FROM
					plat_position AS ta
				WHERE
					ta.position_id <> (
						SELECT
							max(tb.position_id)
						FROM
							plat_position AS tb
						WHERE
							ta.remark = tb.remark
						AND ta.phone = tb.phone
						AND ta.salary = tb.salary
						AND ta.userid = tb.userid
						AND ta.models = tb.models
						AND ta.license_type = tb.license_type
						AND ta.conductor = tb.conductor
						AND ta.age = tb.age
					)
			) tp
	);

mysql之将A表内的模糊查询的数据插入到B表

insert into plat_position_vehicle(
	position_id,
	driver_name,
	phone,
	province_code,
	city_code,
	area_code,
	create_time,
	update_time,
	remark,
	photo,
	user_id,
	isJob) 
select position_id,
	driver_name,
	phone,
	province_code,
	city_code,
	area_code,
	create_time,
	update_time,
	remark,
	photo,
	userid,
	is_job from (
SELECT position_id,driver_name,phone,province_code,
city_code,area_code,create_time,update_time,remark,photo,userid,is_job 
from plat_position 
where  remark regexp '油罐车|挖掘机|铲车|塔吊|吊车|叉车|推土机|搅拌车|装载机|旋挖|平地机|路拌机|泵车' ) temp"

mysql之将A表的某字段与B表某字段Like查询后，把A表的某字段的value数据插入到B表

UPDATE  plat_position_vehicle v,
(SELECT value,name,type from plat_dict where  type = 'vehicle_type') t  
SET v.vehicle_type = t.value where v.vehicle_type is NULL  
and v.remark  like concat("%" ,t.name ,"%")

mysql之用正则表达式匹配某字段进行查询删除

delete  from  plat_position 
where remark 
regexp '油罐车|挖掘机|铲车|塔吊|吊车|叉车|推土机|搅拌车|装载机|旋挖|平地机|路拌机|泵车'