刷题网站：

牛客网

---

select device_id as user_infos_example
from user_profile where id <= 2

select device_id, university from user_profile
where university="北京大学"

select device_id, gender, age, university
from user_profile
where age > 24

between and 是左右闭区间[] 

select device_id, gender, age
from user_profile
where age between 20 and 23

单引号！ 

select device_id, gender, age, university 
from user_profile
where university!='复旦大学'

格式化sql语句

select
    device_id,
    gender,
    age,
    university,
    gpa
from
    user_profile
where
    gpa > 3.5 and university = '山东大学'
    or
    gpa > 3.8 and university = '复旦大学'

%表示0个或多个字符 

select
    device_id,
    age,
    university
from
    user_profile
where
    university like '%北京%'

select
    max(gpa)
from
    user_profile
where
    university = '复旦大学'

select
    count(gender) as male_num,
    avg(gpa) as avg_gpa
from
    user_profile
where
    gender = 'male'

有没有人管管牛客网，怎么代码一模一样的，第一次对，第二次不多ToT！

select
    gender,
    university,
    count(*) as user_num,
    avg(active_days_within_30) as avg_active_day,
    avg(question_cnt) as avg_question_cnt
from
    user_profile
group by
    gender, university

加round()默认四舍五入到小数点后一位

select
    device_id,
    age
from
    user_profile
order by
    age asc

select
    device_id,
    gpa,
    age
from
    user_profile
order by
    gpa asc,
    age asc

select
    device_id,
    gpa,
    age
from
    user_profile
order by
    gpa desc,
    age desc

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

select
    university,
    round(avg(question_cnt),3) as avg_question_cnt,
    round(avg(answer_cnt),3) as avg_answer_cnt
from
    user_profile
group by
    university
having
    avg_question_cnt < 5
    or
    avg_answer_cnt < 20

select
    university,
    round(avg(question_cnt), 4) as avg_question_cnt
from
    user_profile
group by
    university
order by
    avg_question_cnt asc

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

select
    up.university,
    round(count(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
    qpd.device_id = up.device_id
group by
    up.university

• INNER JOIN:

  • 只返回两个表中匹配的记录。当连接的表中没有匹配的行时，这些行将不会出现在结果集中。

• LEFT JOIN (或 LEFT OUTER JOIN):

  • 返回左表（第一个表）中的所有记录，即使在右表（第二个表）中没有匹配的记录。如果右表中没有匹配的行，结果集中对应的右表字段将显示为 NULL。

select
    ue.university as university,
    ql.difficult_level as difficult_level,
    round(count(qpl.question_id)/count(distinct(qpl.device_id)),4) as avg_answer_cnt
from
    question_practice_detail as qpl
    left join
    user_profile as ue
    on qpl.device_id = ue.device_id
    left join
    question_detail as ql
    on 
    qpl.question_id = ql.question_id
group by
    ue.university,ql.difficult_level

select
    t1.university as university,
    t3.difficult_level as difficult_level,
    round(
        count(t2.question_id) / count(distinct (t2.device_id)),
        4
    ) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
where
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
group by
    t3.difficult_level

UNION vs UNION ALL

• UNION：合并多个查询的结果集，自动去重，即如果两个查询的结果中有相同的行，最终结果集中只保留一行。
• UNION ALL：合并多个查询的结果集，包括所有的行，不去重，即使有重复的行，也会全部显示。

select
    case
        when age<25 or age is null then "25岁以下"
        else "25岁及以上"
    end 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 between 20 and 24  then '20-24岁'
        when age >= 25 then '25岁及以上'
        else '其他'
    end as age_cut
from
    user_profile;

select
    DAY(date) as day,
    count(*) as question_cnt
from
    question_practice_detail as ql
where
    ql.date like '2021-08-%'
group by
    ql.date