常用场景SQL合集（持续更新）

1. 查询存在多条的数据

# 查询存在多条的数据(不考虑性能)
SELECT * FROM t_user WHERE name IN (SELECT name FROM t_user GROUP BY name HAVING COUNT(name) > 1)) ORDER BY name;

2. 删除同名重复的数据保留一条

# 删除同名重复的数据保留一条(不考虑性能)
DELETE FROM t_user WHERE name IN (SELECT name FROM(SELECT name FROM t_user GROUP BY name HAVING COUNT(name) > 1) a)
AND id NOT IN(SELECT id FROM(SELECT MIN(id) id FROM t_user GROUP BY name HAVING COUNT(name) > 1) b);

# 删除不在任意学校的用户（必须使用临时表包一层）
DELETE FROM t_user WHERE t_sc_id NOT IN (SELECT id FROM (SELECT id FROM t_school) b);

3. 根据一个表关联更新另一个表

# 根据一个表关联更新另一个表
UPDATE t_user a, t_depart b SET a.depart_name = b.depart_name WHERE a.depart_id = b.id;

4. MySQL列表按指定字段排序并将null排在最后/前，经实践性能IS NULL优于ISNULL()优于IFNULL()

# 列表按指定字段ASC小到大排序并将null排在最后（ASC默认null排最前，可以省略）
SELECT * FROM t_user ORDER BY name IS NULL ASC;
# 或
SELECT * FROM t_user ORDER BY ISNULL(name);
# 列表按指定字段DESC大到小排序并将null排在最前（DESC默认null排最后）
SELECT * FROM t_user ORDER BY name IS NULL DESC;
# 或
SELECT * FROM t_user ORDER BY ISNULL(name) DESC;

5. 按自定义字段内容顺序排序 ORDER BY FIELD(field, field_v1, field_v2, …)

SELECT * FROM t_user ORDER BY FIELD(status_code, '2', '1', '3'), create_time DESC;

6. 行行比较查询 WHERE (field1, field2) IN ((field1_v1, field2_v1), (field1_v1, field2_v2), (field1_v2, field2_v1), …)

SELECT * FROM t_user WHERE (depart_id, status_code) IN (('001', '1'), ('001', '2'), ('002', '3'), ('003', '2'), ('003', '3'));
# 相当于
SELECT * FROM t_user WHERE (depart_id = '001' AND status_code IN ('1', '2')) OR (depart_id = '003' AND status_code IN ('3')) OR (depart_id = '003' AND status_code IN ('2', '3'));

7. 默认情况下，MySQL是不区分大小写的，但是有时候需要区分大小写查询

# 解决方案1：修改数据库默认区分大小写。

# 解决方案2：查询时指定binary。
SELECT * FROM t_user WHERE name = '张三' AND binary code = 'A';

# 解决方案3：修改表单个字段为 utf8_bin 区分大小写。
ALTER TABLE t_user MODIFY COLUMN f_code varchar(2) COLLATE utf8_bin NULL DEFAULT '' COMMENT '字母code' AFTER f_name;

8. 根据多个表关联更新另一个表

# 更新状态为 4 的用户公司/家庭/学校三项的最后一次操作时间
UPDATE t_user, (
    SELECT COALESCE(a.u_no,b.u_no,c.u_no) u_no, GREATEST(a.tm,b.tm,c.tm) tm FROM
        (SELECT a.u_no u_no,MAX(a.opt_time) tm FROM t_office a WHERE a.u_no 
            IN (SELECT u_no FROM t_user t WHERE t.u_status = '4') GROUP BY a.u_no) a,
	(SELECT b.u_no u_no,MAX(b.opt_time) tm FROM t_home b WHERE b.u_no 
            IN (SELECT u_no FROM t_user t WHERE t.u_status = '4') GROUP BY b.u_no) b,
	(SELECT c.u_no u_no,MAX(c.opt_time) tm FROM t_school c WHERE c.u_no 
            IN (SELECT u_no FROM t_user t WHERE t.u_status = '4') GROUP BY c.u_no) c
    WHERE a.u_no = b.u_no AND a.u_no = c.u_no
) d SET last_opt_time = d.tm WHERE u_status = '4' AND u_no = d.u_no;

9. 查询某个字段值中包含指定字符的个数

# 查询用户标签中存在“|”的个数
SELECT LENGTH(u_tag) - LENGTH(REPLACE(u_tag,'|','')) c FROM t_user;

10. 单个字段逗号分隔的数据条件查询过滤

SELECT * FROM t_user WHERE FIND_IN_SET('A', u_tag);

11. 强制使用/忽略指定索引查询数据，PRIMARY 主键索引
    格式：SELECT ... FROM ... FORCE|IGNORE INDEX(PRIMARY,idx_xxx,...) WHERE ...;

# 强制使用主键索引或idx_no_code索引查询数据
SELECT id, u_no, name, u_tag, u_code, u_status FROM t_user FORCE INDEX(PRIMARY, idx_no_code) 
    WHERE u_no = 'S001' AND u_code = 'C' AND u_status = '4' ORDER BY u_code;
    
# 忽略idx_no索引查询数据
SELECT id, u_no, name, u_tag, u_code, u_status FROM t_user IGNORE INDEX(idx_no) 
    WHERE u_no LIKE 'S%' AND u_status = '4';

12. 查询分组后某个字段值最大的记录（查询每一个对象值最大的记录）

原始数据：
id	uid	uname	score	remark
1	1	张三	80	80分
2	1	张三	90	90分
3	2	李四	85	85分
4	3	王五	70	80分
5	1	张三	95	95分
6	2	李四	80	80分
7	2	李四	65	65分
8	4	老六	66	66分

期望结果：
id	uid	uname	score	remark
3	2	李四	85	85分
4	3	王五	70	80分
5	1	张三	95	95分
8	4	老六	66	66分

-- 使用right join
SELECT a.* FROM student a RIGHT JOIN 
    (SELECT uid,MAX(score) score FROM student GROUP BY uid) b
ON a.uid = b.uid AND a.score = b.score;

-- 使用left jion
SELECT a.* FROM (SELECT uid,MAX(score) score FROM student GROUP BY uid) b 
    LEFT JOIN student a
ON a.uid = b.uid AND a.score = b.score;

-- 使用left jion
SELECT a.* FROM student a LEFT JOIN 
    (SELECT uid,MAX(score) score FROM student GROUP BY uid) b
ON a.uid = b.uid AND a.score = b.score WHERE b.score IS NOT NULL;

13. 查询超大数字不使用科学计数法
    使用 CONVERT(id, SIGNED) 或 CAST(id AS SIGNED)

-- 查询id,id+1不使用科学计数
SELECT id, id + 1, CONVERT(id, SIGNED) + 1, CAST(id AS SIGNED) + 1 FROM student WHERE id = '1425725467610370050';

结果：
id			id + 1			CONVERT(id, SIGNED) + 1	CAST(id AS SIGNED) + 1
1425725467610370050 	1.42572546761037e18 	1425725467610370051 	1425725467610370051

14. 随机查询10条数据，使用RAND()函数排序，注意性能问题

-- 随机查询10条商品信息
SELECT * FROM product ORDER BY RAND() LIMIT 10;

新建一个用户并授权

# 创建admin用户，设置密码admin123，所有IP可访问
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin123';
# 设置密码为admin666888
SET PASSWORD FOR 'admin'@'%' = PASSWORD('admin666888');
# 授予admin显示数据库的权限
GRANT SHOW DATABASES ON *.* TO 'admin'@'%';
# 授予admin对admin_db库所有表的所有操作权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON admin_db.* TO 'admin'@'%';
# 授予admin对admin_db库所有操作权限
GRANT GRANT OPTION ON admin_db.* TO 'admin'@'%';