SQL命令总结

例题来自牛牛
参考：
https://www.w3school.com.cn/sql/sql_join.asp
https://www.yiibai.com/sql/sql-aggregate-functions.html

SQL语句执行顺序

FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

1. 查询结果去重

法一：distinct关键字

select distinct university from user_profile ;

法二：group by

select university from user_profile group by university;

2. 限制查询行数-limit用法

法一：between and

select device_id from user_profile where id between 1 and 2

法二：limit用法
①取前n行

select device_id from user_profile limit 0,2;
# 或 
select device_id from user_profile limit 2;

②m=0，取从第m+1行到n行

select device_id from user_profile limit 0,2;
或
select device_id from user_profile limit 2 offset 0;

法三：where

select device_id from user_profile where id='1' or id='2';
#或
select device_id from user_profile where id in (1,2);
#或
select device_id from user_profile where id <= 2;

3. 查询后的列重命名-as用法

select device_id as user_infos_example from user_profile limit 0,2;

上一题中的所有写法前面加上as xx即可

as用法：
可用于列、表，仅针对当次查询，不改变原表内容

select  column_1 as 列1, column_2  as 列2 from text as 表;

4. 查找除了xx-不等于的表示方法

① !=

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

②not in ()

select device_id, gender, age, university from user_profile where university not in( '复旦大学');

5. where in（） 和 not in（）

①where in

select device_id, gender, age, university, gpa from user_profile where university in ('北京大学','复旦大学','山东大学');

②not in

select device_id, gender, age, university, gpa from user_profile where university not in ('浙江大学');

6. 操作符混合 and、or

找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学

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

7. 查找最高值、最低值-max（）、min（）

法一：order by

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

法二：max（）
保留一位小数

select round(max(gpa),1) from user_profile where university='复旦大学' ;

8. 计算总数、平均数-count（）、avg（）

计算男生人数以及平均GPA

select count(gender) as male_num,round(avg(gpa),1) as avg_gpa from user_profile where gender='male';
# count（）里面参数可以换，如下

count(*) 和 count(1)和count(列名)区别

9. 分组计算-group by

对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

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

10. 分组过滤-子语句having用法

查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

HAVING子句用于对分组后的结果再进行过滤，它的功能有点像WHERE子句，但它用于组而不是单个记录。聚合函数结果作为筛选条件时，不能用where，而是用having语法

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;

11. 排序-order by

①查找后排序
取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出

# 升序排序
select device_id, gpa, age from user_profile order by gpa, age

# 降序排序
select device_id, gpa, age from user_profile order by gpa desc, age desc

②分组后排序
查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列

select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt;
# 或
select university, avg(question_cnt) from user_profile group by university order by avg(question_cnt);

order by后面可以加聚合函数, group by后面不可
有三类后面可以加聚合函数
1、select
2、order by
3、having

12. ⭐⭐连接查询-join

内连接：
INNER JOIN 与 JOIN 是相同的，至少一个匹配时返回行，没有匹配时不返回该行。
①两张表，用户信息表、回答明细表
查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

# select*的话会有重复的列，device_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 = '浙江大学';

# 或 where in() 子查询
select device_id, question_id,result from question_practice_detail
where device_id in (
    select device_id from user_profile where university = '浙江大学'
);

②每个学校答过题的用户平均答题数量情况

select a.university, count(b.device_id)/count(distinct b.device_id) as avg_answer_cnt
from user_profile as a
inner join question_practice_detail as b on a.device_id = b.device_id
group by a.university ;
# 或
select a.university, count(b.device_id)/count(distinct b.device_id) as avg_answer_cnt
from user_profile as a, question_practice_detail as b
where a.device_id = b.device_id
group by a.university ;

③计算参加了答题的不同学校、不同难度的用户平均答题量

select university, difficult_level, count(a.question_id)/count(distinct a.device_id) as avg_answer_cnt 
from question_practice_detail as a
inner join user_profile as b on a.device_id = b.device_id
inner join question_detail as c on a.question_id = c.question_id
group by university, difficult_level

④查看参加了答题的山东大学的用户在不同难度下的平均答题题目数

select 
    a.university, 
    c.difficult_level, 
    count(b.question_id)/count(distinct b.device_id) as avg_answer_cnt
from
    user_profile a
    join question_practice_detail b on a.device_id = b.device_id
    join question_detail c on b.question_id = c.question_id
where
    a.university = '山东大学'
group by
    c.difficult_level;

13. 联合查询-union用法

查找山东大学或者性别为男生的信息

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';

union用法：
合并两个或多个 SELECT 语句的结果集，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地选取不同的值，如果允许重复的值，使用 UNION ALL。

14. ⭐条件查询-if（）、case when

①将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
本题注意：age为null 也记为 25岁以下

法一：if（x=n，a, b）
若x=n返回a，否则返回b

select if(age >= 25, '25岁及以上', '25岁以下') as age_cut,
count(device_id) as number
from user_profile
group by age_cut

法二：case when

select case
        when age < 25 or age is null then '25岁以下'
        when age >= 25 then '25岁及以上'
    end age_cut,count(*)number
from user_profile
group by age_cut

②查看不同年龄段的用户明细
将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况

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

15. ⭐日期函数-day（）、date（）用法

①计算出2021年8月每天用户练习题目的数量

select
    day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08-%'
group by date;

day（）用法
返回一个整数值，表示指定日期的月份中的第几天(1-31)

②查看用户在某天刷题后第二天还会再来刷题的平均概率

法一：用date_add()构造出第二天来了的字段然后和原表join，用第二天来了的除以第一次来了的

• DATE_ADD(date,INTERVAL expr type)
  expr 参数是时间间隔,type 可以换单位

select count(date2)/count(date1) as avg_ret
from (

        select distinct a.device_id, a.date as date1, b.date as date2
        from question_practice_detail as a
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as b
    on a.device_id=b.device_id and date_add(a.date, interval 1 day)=b.date

) as id_next_date

法二：

• lead（字段名称，行数，默认值）
  用法：
  从后面的1行取timestamp字段
• DATEDIFF(date1,date2) 返回两个日期之间的天数

lead(timestamp, 1, null) over (
    partition by trace_id
    order by timestamp
) toTime,

先按用户分组，再按时间排序，再用lead两两拼接
检查date2和date1的日期差是不是为1，是则为1，否则为0，取avg可得平均概率。

select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
) as id_next_date

16. 文本函数-substring_index()

substring_index(str,delim,count)
count是正数，从左往右数，count是负数，从右往左数
①统计每个性别的用户分别有多少参赛者

select substring_index(profile,',',-1) as gender,count(*)number
from user_submit
group by gender
# 或
SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

②取中间的某个值
统计每个年龄的用户分别有多少参赛者

select substring_index(substring_index(profile,',',-2), ',',1) as age, count(*)number
from user_submit 
group by age;

17. 窗口函数-rank（）

用于组内排名

语法：rank() over (order by 成绩 desc) as ranking

每个学校里gpa最低的同学

select device_id, university, gpa 
from (
    select *,row_number() over (partition by university order by gpa)as a
    from user_profile
)as university_min
where a=1
order by university