常用场景SQL合集

常用场景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);

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

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

4. 列表按指定字段排序并将null排在最后

# 列表按指定字段排序并将null排在最后
SELECT * FROM t_user ORDER BY name IS NULL, 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'));

新建一个用户并授权

# 创建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'@'%';