mysql中的select经验分享 疑难杂症

1.中文字段排序

SELECT * 
FROM tablename 
ORDER BY convert(name using gb2312) ASC

2.如果name字段中含有A字符串,替换成B

UPDATE tablename SET name=replace(name, 'A', 'B'); 

3.将字段name改为name字段的值+A字符串

UPDATE tablename SET name=concat('A');

4.查询以英文逗号隔开的type字段里面是否有A值

SELECT * 
FROM tablename 
WHERE FIND_IN_SET('A',type)

5.time日期格式直接比较时间戳

SELECT * 
FROM tablename 
WHERE unix_timestamp(time)>1568799225

6.按照in的顺序查询出对应的值

SELECT 
* 
FROM tablename 
WHERE 
id IN (7，2，1，5) 
ORDER BY field(id,7，2，1，5 )

7.查询左表中以逗号隔开的a字段存在于右表中的值

t1表：

id	title	userid
10	数学	2,3
20	英语	1,4

t2表：

id	realname
1	张三
2	李四
3	王五
4	赵六

SELECT 
t1.id,t1.title,GROUP_CONCAT(t2.realname) AS realname 
FROM t1 
LEFT JOIN t2 
ON FIND_IN_SET(t2.id,t1.userid) 
GROUP BY t1.id;

结果：

id	title	realname
10	数学	李四,王五
20	英语	张三,赵六

8.查询右表中某字段包含左表某字段的值

t1表：

id	title	userid
10	数学	2,3
20	英语	1,4

t2表：

id	realname
1	张三
2	李四
3	王五
4	赵六

SELECT 
t2.id,t1.realname,t1.title 
FROM t2 
LEFT JOIN t1 
ON FIND_IN_SET(t2.id,t1.userid);

结果：

id	title	realname
1	张三	英语
2	李四	数学
3	王五	数学
4	赵六	英语

9.where中使用case判断处理，为了解决列别名无法作为where条件

t1表：

id	realname	relateId	relateTableId	deleted
1	张三	1	2	0
2	李四	3	3	0
3	王五	2	3	0
4	赵六	2	2	1

t2表：

id	title	status
1	语文	1
2	数学	2
3	英语	1

t3表：

id	title	status
1	体育	1
2	美术	2
3	音乐	1

SELECT
	t1.id,
	t1.realname,
	( CASE 
	WHEN t1.relateTableId = 2 THEN t2.title 
	WHEN t1.relateTableId = 3 THEN t3.title 
	END ) AS title
	FROM t1
	LEFT JOIN t2 ON t1.relateId = t2.id
	LEFT JOIN t3 ON t1.relateId = t3.id
	WHERE 
	CASE
	WHEN t1.relateTableId = 2 THEN t2.`status` IN ( '1', '2' ) 
	WHEN t1.relateTableId = 3 THEN t3.`status` IN ( '1', '2') 
	END
	AND
	t1.deleted = 0 
	ORDER BY t1.id asc

结果：

id	realname	title
1	张三	语文
2	李四	音乐
3	王五	美术

10.中文字段值根据不同值另改为不同数字值后排序

t1表：

id	name	score
1	张三	良好
2	李四	良好
3	王五	优秀
4	赵六	及格
5	贾七	优秀

SELECT
	id,
	`name`,
	`score`,
CASE
		`score` 
		WHEN '优秀' THEN
		3 
		WHEN '良好' THEN
		2 
		WHEN '及格' THEN
		1 ELSE 0 
	END score_num 
FROM
	t1 
ORDER BY
	score_num DESC

结果：

id	name	score	score_num
5	贾七	优秀	3
3	王五	优秀	3
2	李四	良好	2
1	张三	良好	2
4	赵六	及格	1