MMYSQL刷题

SELECT DISTINCT university FROM user_profile;

SELECT university
FROM user_profile
GROUP BY university;

SELECT device_id user_infos_example from user_profile order by id limit 2;
SELECT device_id user_infos_example from user_profile limit 2;
//1.as 写不写都可
//2.别名加不加引号（单双）都可//加引号：别名就是引号内的内容。//不加引号：别名如果为小写，会解析为大写，别名实际为大写。
//以上两点在调用别名时要注意，易报错：找不到对应的列（大小写对应的是不同的列）

select device_id,gender,age,university,gpa from user_profile where(gpa>3.5 and university = "山东大学") or (gpa>3.8 and university="复旦大学") ORDER BY device_id ASC;

SELECT device_id, gender, age, university,gpa from user_profile where gpa > 3.8 and university = '复旦大学' 
UNION
SELECT device_id, gender, age, university,gpa from user_profile where gpa > 3.5 and university = '山东大学'
order by device_id asc;

select gpa from user_profile where university = "复旦大学"
order by gpa desc limit 1;

select id,name,phone_number
from contacts
where phone_number REGEXP "^[1-9][0-9]{2}-?[0-9]{3}-?[0-9]{4}$";

select 
gender,university,count(device_id) as user_num,
avg(active_days_within_30) as avg_active_days,
avg(question_cnt) as avg_question_cnt
from user_profile
group by gender,university;

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;

select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id and up.university='浙江大学'
order by question_id

select device_id, question_id, result
from question_practice_detail
where device_id in (select device_id from user_profilewhere university='浙江大学'# 筛选出所有浙江大学的设备号 如果在这个列表中说明是浙江大学的同学
)
order by question_id

select university,
count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
#这个学校的答题总数 / 这个学校的人数
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id
group by university
order by university asc;

SELECT 
u.university, 
qd.difficult_level, 
ROUND(COUNT(qpd.id) / COUNT(DISTINCT u.device_id), 4) avg_answer_cnt
#这个count其实代表的是一个学校的一个难度的题目数(group的结果)
#后面的count代表的是这个学校参加答题的人数(设备数)
FROM user_profile u
JOIN question_practice_detail qpd ON u.device_id = qpd.device_id
JOIN question_detail qd ON qpd.question_id = qd.question_id
GROUP BY u.university, qd.difficult_level
ORDER BY u.university, qd.difficult_level;

select 
case 
when age<25 or age is null then "25岁以下"
else "25岁及以上"
end as age_cut,
count(*) as number 
from user_profile
group by age_cut
order by
case
when age_cut = "25岁以下" then 1
else 2
end
asc;

select device_id,gender,
case
when age is null then "其他"
when age < 20 then "20岁以下"
when age between 20 and 24 then "20-24岁"
when age >= 25 then "25岁及以上"
end as age_cut
from user_profile;

SELECT ROUND(SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) , 4) AS avg_ret
FROM (SELECT DISTINCT device_id, date FROM question_practice_detail) p1 #第一天每个用户的唯一刷题记录
LEFT JOIN (SELECT DISTINCT device_id, date FROM question_practice_detail) p2 #第二天每个用户的唯一刷题记录
ON p1.device_id = p2.device_id  #是同一个用户 AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY); #第二个日期是第一个日期+1

select
substring_index(substring_index(profile,',',3),',',-1) as age,
#正数只保留前n个 负数只保留后n个 
count(*) as number
from user_submit
group by age;

SELECT device_id, university, gpa
FROM user_profile
WHERE (university, gpa) IN (SELECT university, MIN(gpa)FROM user_profileGROUP BY university
)
ORDER BY university ASC;
#只要用了 GROUP BY，SELECT 的字段要么被聚合（如 MIN），要么出现在 GROUP BY 里，否则就会报错。

select 
up.device_id,
up.university,
up.gpa
from user_profile up
join(select university,min(gpa) as min_gpafrom user_profile group by university
) as sub
on up.university = sub.university
and up.gpa = sub.min_gpa
order by university;

select
up.device_id,
up.university,
count(qpd.question_id) as question_cnt,
sum(case when qpd.result = "right" then 1else 0end
) as right_question_cnt
from user_profile up
left join 
question_practice_detail qpd
on up.device_id = qpd.device_id
AND qpd.date BETWEEN '2021-08-01' AND DATE_ADD('2021-08-01', INTERVAL 30 DAY)
where up.university = "复旦大学"
group by up.device_id,
up.university
order by up.device_id asc;

-- 统计浙江大学用户在不同难度题目的答题正确率，并按正确率升序输出SELECT qd.difficult_level,ROUND(SUM(CASE WHEN qpd.result = 'right' THEN 1 ELSE 0 END) / COUNT(qpd.question_id), 4) AS correct_rate
FROM user_profile up
JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
JOIN question_detail qd ON qpd.question_id = qd.question_id
WHERE up.university = '浙江大学'
GROUP BY qd.difficult_level
ORDER BY correct_rate ASC;

selectcount(distinct device_id) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08%"

select *,
sum(profit) over (order by profit_date) cumulative_profit
from daily_profits
order by profit_date
#OVER 就是给每一行**“开一扇窗户”，让你在旁边再算一个值（如累计、排名、移动平均），但不破坏原始行数**。

select *,abs(value) as absolute_value,ceil(value) as ceiling_value, #向上取整floor(value) as floor_value, #向下取整round(value,1) as rounded_value
from numbers
order by id ASC