1、求平均数，保留小数用round函数，groupby分组 示例代码：

SELECT
	university,
	round((count(question_id) / count(DISTINCT question_practice_detail.device_id)), 1) AS avg_answer_cnt
FROM
	user_profile
	JOIN question_practice_detail ON user_profile.device_id = question_practice_detail.device_id
GROUP BY
	university

2、Union all :直接将查询结果连接起来，不去重 示例代码：

SELECT
	device_id,
	gender,
	age,
	gpa
FROM
	user_profile
WHERE
	university = '山东大学'
UNION ALL
SELECT
	device_id,
	gender,
	age,
	gpa
FROM
	user_profile
WHERE
	gender = 'male'

3、If函数（适用于两个条件判断的）和case when then 函数（适用与多个条件判断的） 示例代码：

SELECT
	if(age >= 25, "25岁及以上", "25岁以下") AS age_cut,
		count(device_id) AS number
	FROM
		user_profile
	GROUP BY
		age_cut
SELECT
			device_id,
			gender,
			CASE WHEN age < 20 THEN
				"20岁以下"
			WHEN age >= 20
				AND age <= 24 THEN
				"20-24岁"
			WHEN age >= 25 THEN
				"25岁及以上"
			ELSE
				"其他"
			END AS age_cut
		FROM
			user_profile
		GROUP BY
			device_id,
			gender,
			age_cut

4、日期函数 示例代码：

SELECT
	DAY(date) AS day,
	count(question_id) AS question_cnt
FROM
	question_practice_detail
WHERE
	YEAR(date) = "2021"
	AND MONTH(date) = "08"
GROUP BY
	day

5、Substring_index函数 示例代码：

SELECT
	SUBSTRING_INDEX(PROFILE, ',', - 1) AS gender,
	count(device_id) AS NUMBER
FROM
	user_submit
GROUP BY
	gender