SQL笔记

查询重复数据

1
2
3
4
5
6
7
8
SELECT *
FROM tableName
WHERE user_name IN (
	SELECT user_name
	FROM tableName
	GROUP BY user_name
	HAVING COUNT(user_name) > 1
);

统计数量和占比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
	token.client_id AS 'clientId',
	client.client_explain AS 'explain',
	COUNT(token.client_id) AS 'sum',
	CONCAT(
		CONVERT (
			COUNT(token.client_id) * 100 / (
				SELECT
					COUNT(1)
				FROM
					oauth_client_token
			),
			DECIMAL (18, 2)
		),
		'%'
	) AS 'proportion'
FROM
	oauth_client_token AS token
LEFT JOIN oauth_client_client AS client ON client.client_id = token.client_id
GROUP BY
	token.client_id
ORDER BY
	sum DESC;

删除表B中表A不存在的记录

1
2
3
4
5
6
DELETE FROM tableB
WHERE NOT EXISTS (
	SELECT 1
	FROM tableA A
	WHERE tableB.did = A.id
);

数据库事件状态查询

1
show variables like '%scheduler%';

数据库事件开启执行

1
SET GLOBAL event_scheduler = 1;