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 |