查詢所有列
select * from user_profile诫睬；
查詢用戶的設(shè)備id對應的性別没炒、年齡和學校的數(shù)據(jù)
select device_id,gender,age,university from user_profile;
從用戶信息表中取出學校的去重數(shù)據(jù)
select distinct university from user_profile ;
查看前2個用戶明細設(shè)備ID數(shù)據(jù)
select device_id from user_profile limit 2;
查看后10個用戶明細設(shè)備ID數(shù)據(jù)
select device_id from user_profile limit 10,-1; //11行到last
查看前2個用戶明細設(shè)備ID數(shù)據(jù)药版，并將列名改為 'user_infos_example'
select device_id as user_infos_example from user_profile limit 2;
用戶年齡升序查看信息
select device_id, age from user_profile order by age asc;
用戶年齡降序查看信息
select device_id, age from user_profile order by age desc;
并先按照gpa升序排序，再按照年齡升序排序輸出
select device_id, gpa,age from user_profile order by gpa asc, age asc;
查找2021年8月份所有練習過題目的總用戶數(shù)和練習過題目的總次數(shù)
select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%';
查找所有北京大學的學生
select device_id, university from user_profile where university = '北京大學';
查找24歲以上的用戶
select device_id, gender, age, university from user_profile where age > 24;
查找20歲及以上且23歲及以下的用戶
select device_id, gender, age from user_profile where age >= 20 and age <= 23 ;
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 != '復旦大學';
select device_id, gender,age,university from user_profile where university not in("復旦大學")
查看年齡值不為空的用戶信息
select device_id, gender, age, university from user_profile where age is not null;
查找gpa在3.5以上(不包括3.5)的山東大學用戶 或 gpa在3.8以上(不包括3.8)的復旦大學同學
select device_id, gender, age, university, gpa from user_profile
where (university = '山東大學' and gpa > 3.5) or (university = '復旦大學' and gpa > 3.8);
查找復旦大學gpa最高值
select max(gpa) from user_profile where university = '復旦大學';
查找男性用戶有多少人以及他們的平均gpa
select
count(gender) as male_num,
round(avg(gpa), 1) as avg_gpa
from user_profile where gender="male";
對每個學校不同性別的用戶活躍情況和發(fā)帖數(shù)量進行分析呻此，請分別計算出每個學校每種性別的用戶數(shù)备禀、30天內(nèi)平均活躍天數(shù)和平均發(fā)帖數(shù)量。
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;
where 和 having的區(qū)別
執(zhí)行時機不一樣：where 是分組之前進行限定感论，不滿足where條件绅项，則不參與分組，而having是分組之后對結(jié)果進行過
濾比肄。
可判斷的條件不一樣：where 不能對聚合函數(shù)進行判斷快耿，having 可以。
請取出平均發(fā)貼數(shù)低于5的學蟹技ǎ或平均回帖數(shù)小于20的學校
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;
不同大學的用戶平均發(fā)帖情況掀亥，并期望結(jié)果按照平均發(fā)帖情況進行升序排列
select university, avg(question_cnt) as avg_question_count
from user_profile
group by university
order by avg_question_count asc;
查看所有來自浙江大學的用戶題目回答明細情況
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;
每個學校答過題的用戶平均答題數(shù)量情況
//顯示內(nèi)連接
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
//隱式內(nèi)連接
select university, count(question_id)/count(distinct qpd.device_id)
from user_profile as up, question_practice_detail as qpd
where up.device_id = qpd.device_id
group by university
查看學校為山東大學或者性別為男性的用戶的device_id、gender妥色、age和gpa數(shù)據(jù)
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'
