MySQL入门练习1

写数据库的题多是一件美事

1.查找最大值

题目链接

# 方法一
SELECT max(gpa)
FROM user_profile 
WHERE university = '复旦大学';
# 方法二
SELECT gpa
FROM user_profile 
WHERE university = "复旦大学"
ORDER BY gpa DESC 
LIMIT 1;

2.计算男生人数以及平均GPA

题目链接

SELECT COUNT(gender) AS male_num,
round(AVG(gpa),1) AS avg_gpa
FROM user_profile
WHERE gender = "male";

3.分组计算练习

题目链接

SELECT gender,university,
COUNT(id) AS user_num,
AVG(active_days_within_30),
AVG(question_cnt)
FROM user_profile GROUP BY university,gender;

4.分组过滤练习

使用HAVING
HAVING子句用于对分组后的结果再进行过滤，
它的功能有点像WHERE子句，但它用于组而不是单个记录。
在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。
HAVING通常与GROUP BY子句一起使用。

例36.查询学生表中人数大于等于3的班号和人数。

SELECT 班号, COUNT(*) 人数
FROM 学生表
GROUP BY 班号
HAVING COUNT(*) >= 3

例37.查询平均成绩大于等于80的学生的学号、选课门数和平均成绩。

SELECT 学号, COUNT(*) 选课门数,
AVG(成绩) 平均成绩 FROM 成绩表
GROUP BY 学号
HAVING AVG(成绩) >= 80

题目链接

SELECT university,
AVG(question_cnt) AS avg_question_cnt,
AVG(answer_cnt) AS avg_answer_cnt
FROM user_profile GROUP BY university
having avg_question_cnt<5 OR avg_answer_cnt<20;

5.分组排序练习

题目链接

SELECT university,
AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt;

6.多表关联查询

JOIN ON详解

1.三种形式JOIN的sql语句示例

/*LEFT JOIN
* 左关联，此时a表为主表，a、b关联后的临时表中a表所有数据均存在*/
SELECT * FROM a LEFT JOIN b ON a.id = b.id
 
/*RIGHT JOIN
* 右关联，此时b表为主表，a、b关联后的临时表中b表所有数据均存在*/
SELECT * FROM a RIGHT JOIN b ON a.id = b.id
 
/*INNER JOIN == JOIN
* 等值关联，返回两个表中关联字段相等的数据行*/
SELECT * FROM a INNER JOIN b ON a.id = b.id

2.ON的作用

ON用来生成关联临时表，生成的临时表会将主表的所有记录保存下来，等待WHERE语句的进一步筛选。

题目链接

##写法一：
SELECT device_id,question_id,result
FROM question_practice_detail
WHERE device_id IN (
    SELECT device_id FROM user_profile
    WHERE university="浙江大学"
)
ORDER BY question_id;

## 写法二
SELECT A.device_id,A.question_id,A.result
FROM question_practice_detail AS A
INNER JOIN user_profile AS B 
ON B.device_id=A.device_id AND B.university="浙江大学"
ORDER BY question_id;

7.统计每个学校的答过题的用户的平均答题数

题目链接

SELECT university,COUNT(question_id) / COUNT(distinct question_practice_detail.device_id)
AS avg_answer_cnt
FROM question_practice_detail
INNER JOIN user_profile 
ON user_profile.device_id=question_practice_detail.device_id
GROUP BY university;

8.统计每个学校各难度的用户平均刷题数

题目链接

SELECT university,difficult_level,
ROUND(COUNT(qpd.question_id) / COUNT(distinct qpd.device_id),4) AS avg_answer_cnt
FROM question_practice_detail AS qpd
LEFT JOIN user_profile AS up ON up.device_id=qpd.device_id
LEFT JOIN question_detail AS qd ON qd.question_id=qpd.question_id
GROUP BY university,difficult_level;

9.统计每个用户的平均刷题数

题目链接

SELECT university,difficult_level,
ROUND(COUNT(qpd.question_id) / COUNT(distinct qpd.device_id) , 4) AS avg_answer_cnt
FROM question_practice_detail AS qpd
INNER JOIN user_profile AS up ON up.device_id=qpd.device_id
INNER JOIN question_detail AS qd ON qd.question_id=qpd.question_id
WHERE university = "山东大学"
GROUP BY difficult_level;

10.计算用户的平均次日留存率

题目链接

SELECT COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM (
    SELECT distinct device_id,date
    FROM question_practice_detail
) AS q1
LEFT JOIN(
    SELECT distinct device_id,date
    FROM question_practice_detail
) AS q2
ON q1.device_id=q2.device_id
AND q2.date=date_add(q1.date,interval 1 day)