mysql花式操作数据小结

1.字段中包含多值

字段为crowd
其值：
1
1,3
2,3,4
3
3,4
我们把所有3删除 然后给他保持原先对队形得到：
1
1
2,4
null
4
操作：

UPDATE sd_o_agreement
SET crowd = trim(
	BOTH ','
	FROM
		REPLACE (
			concat(',', crowd, ','),
			',3,',
			','
		)
)
WHERE
	FIND_IN_SET(3, crowd)

2.基于字段中某个值查询

crowd 1,3,4 字样存储
我们查询所有crowd包含3的记录

SELECT
	*
FROM
	sd_o_agreement
WHERE
	FIND_IN_SET(3, crowd)

3.基于身份证设置性别

身份证字段 idcode 性别字段 sex 1 男 2女
当数据出现0 空 不是 1 也不是2 我们根据身份证修复一下

 性别非1 2 根据身份证生成
 
UPDATE sd_o_agreement
SET sex = CASE
IF (
	length(pidcode) = 18,
	cast(
		substring(pidcode, 17, 1) AS UNSIGNED
	) % 2,

IF (
	length(pidcode) = 15,
	cast(
		substring(pidcode, 15, 1) AS UNSIGNED
	) % 2,
	3
)
)
WHEN 1 THEN
	1
WHEN 0 THEN
	2
ELSE
	0
END
WHERE
	sex NOT IN (1, 2)

4.身份证生成出生日期

身份证有了 但是生日字段却空缺，我们基于身份证做出出生日期

UPDATE sd_o_agreement
SET birth = cast(
	substring(pidcode, 7, 8) AS date
)
WHERE
	ISNULL(birth)

4.增加表字段

增加了开始时间 ，结束时间 都是时间戳类型。
开始时间就用添加时间戳，结束时间戳用1年以后

UPDATE sd_o_agreement
SET starttime = created_at;

UPDATE sd_o_agreement
SET endtime = UNIX_TIMESTAMP(
	DATE_ADD(
		from_unixtime(
			created_at,
			'%Y-%m-%d %H:%i:%s'
		),
		INTERVAL 1 YEAR
	)
)

5.一次搞定多个查询

一个统计方法 要查n多个统计数据，高效查询

        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where .' and mark=0 ';
        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where.' and mark=1';
        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where.' and mark=2';
        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where.' and mark=3';
        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where.' and able=0';
        $count_sql[]='SELECT count(*) as count from sd_o_agreement_serve'.$where.' and able=1';

	$unionSql=implode(' union all ',$count_sql);//综合sql
	$unCount=Db::query($unionSql);

6.关联删除

info id 1 list_id 5
info id 2 list_id 5
info id 3 list_id 5
list-> info 一对多多重关联
下面sql是以info为主表为条件删除，
此法删除的是符合条件的info 以及被符合info关联起来的list
即：select count(*) from info where 条件
以及 join中：info.agreement_list_id = list.id符合条件的list
之和！
特意说明：及时list符合的where条件，但是其join对应的info没有贯通，此类list不会被删除！

DELETE info,
 list
FROM
	sd_o_agreement_list_info AS info
LEFT JOIN sd_o_agreement_list AS list ON info.agreement_list_id = list.id
WHERE
where条件

7.通过关系表一对多查询合并到一条记录

SELECT
	org.*,
	u.`name` AS create_user_name,
	rinfo.*
FROM
	sys_org org
LEFT JOIN sys_user u ON org.create_user_id = u.id
LEFT JOIN (
	SELECT
		r.org_id AS r_org_id,
		GROUP_CONCAT(c.`name`) AS channelNames,
		GROUP_CONCAT(c.id) AS channelIds
	FROM
		sys_org_channel_relation r
	LEFT JOIN sys_channel c ON r.channel_id = c.id
	GROUP BY
		r.org_id
) rinfo ON org.id = rinfo.r_org_id

8.替代like

 SELECT c.*
 FROM sys_channel AS c
 WHERE (INSTR(c.name,'闻') > 0 OR INSTR(c.channel_code,'闻') > 0 ) 
 ORDER BY c.sort ASC
 LIMIT 10;