SQL进阶篇1-牛客网

一、增删改操作

1.插入记录

1.SQL1 插入记录（一）

insert into exam_record (uid,exam_id,start_time,submit_time,score) 
                 values (1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),
                        (1002,9002,'2021-09-04 07:01:02',null,null)

insert into exam_record VALUES
(
    null,1001,9001, "2021-09-01 22:11:12","2021-09-01 23:01:12",90
),
(
    null,1002,9002, "2021-09-04 07:01:02",null,null
)

2.SQL2 插入记录（二）

insert into exam_record_before_2021(uid,exam_id,start_time,submit_time,score)
select uid,exam_id,start_time,submit_time,score 
from exam_record
where year(submit_time)<'2021'
and score is not null

因为本身exam_record_before_2021创建的时候已经有id=1,所以不能select * from exam_record

3.SQL3 插入记录（三）

replace into examination_info (exam_id,tag,difficulty,duration,release_time)
values (9003,'SQL','hard',90,'2021-01-01 00:00:00')

replace into

1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2. 否则，直接插入新数据。

• 要注意的是：插入数据的表必须有主键或者是唯一索引！否则replace into 会直接插入数据，这将导致表中出现重复的数据。
  insert into会直接插入数据，如果和主键或唯一索引重复，那么就会插入失败。

2.更新记录

1.SQL4 更新记录（一）

update examination_info set tag='Python' where tag='PYTHON'

2.SQL5 更新记录（二）

update exam_record set submit_time='2099-01-01 00:00:00',score=0
                    where start_time<'2021-09-01 00:00:00' and submit_time is null

3.删除记录

1.SQL6 删除记录（一）

delete from  exam_record where score<60 and TIMESTAMPDIFF(minute,start_time,submit_time)<5

• datediff :
  只能精确到天，计算两个时间相差的天数
• TIMESTAMPDIFF(type,expr1,expr2):
  可以计算到天，小时，分钟，秒

2.SQL7 删除记录（二）

delete from exam_record where submit_time is null or 
                        TIMESTAMPDIFF(minute,start_time,submit_time)<5
                        order by start_time
                        limit 3

3.SQL8 删除记录（三）

TRUNCATE TABLE exam_record

2、表与索引操作

1.表的创建、修改与删除

1.SQL9 创建一张新表

Create table user_info_vip (id int(11) primary key auto_increment comment '自增ID',
                           uid int(11) not null unique comment '用户ID',
                           nick_name varchar(64) comment '昵称',
                           achievement int(11) default 0 comment '成就值',
                           level int(11) comment '用户等级',
                           job varchar(32) comment '职业方向',
                           register_time datetime default current_timestamp comment '注册时间'
                           )DEFAULT CHARSET=UTF8; 

2.SQL10 修改表

alter table user_info change  job profession varchar(10);
alter table user_info add school varchar(15) after level;
alter table user_info modify achievement int(11) default 0;

3.SQL11 删除表

DROP TABLE IF EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

2.索引的创建、删除

1.SQL12 创建索引

create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);

2.SQL13 删除索引

drop index  uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;

3、聚合分组查询

1.聚合函数

1.SQL14 SQL类别高难度试卷截断平均分

下面这个方法没有考虑重复的最高分和最低分

select tag,difficulty,round((sum(score)-max(score)->min(score))/(count(score)-2),1) clip_avg_score
from examination_info join exam_record
on examination_info.exam_id=exam_record.exam_id
where tag='SQL' and difficulty='hard'

考虑重复的最高分和最低分

select tag, difficulty, round(avg(score), 1) as clip_avg_score
from(
   select tag, difficulty, score, 
   rank() over(order by score asc) as rk_asc,
   rank() over(order by score desc) as rk_desc
	from examination_info as i
	join exam_record as r
	on i.exam_id = r.exam_id
	where tag = 'SQL' and difficulty = 'hard' and score is not null) as t
where rk_asc <> 1 and rk_desc <> 1
group by tag, difficulty

2.SQL15 统计作答次数

select count(start_time) as total_pv,
        count(submit_time) as complete_pv,
        count(distinct if(score is not null,exam_id,null)) complete_exam_cnt
from exam_record

3.SQL16 统计作答次数

select min(score) as min_score_over_avg
from (
    select score
    from exam_record join examination_info
                      on  exam_record.exam_id=examination_info.exam_id
                      where tag='SQL' and 
                      score >= (select avg(score) from exam_record join examination_info
                      on  exam_record.exam_id=examination_info.exam_id
                      where tag='SQL' and score is not null)) t

select min(score) as min_score_over_avg
from (
    select examination_info.exam_id,tag,score,avg(score) over(partition by tag) avg_score
    from exam_record join examination_info
                      on  exam_record.exam_id=examination_info.exam_id
    where score is not null
                    ) t
where tag='SQL' and score>= avg_score

2.分组查询

1.SQL17 平均活跃天数和月活人数

select DATE_FORMAT(start_time,'%Y%m') month,
            round(count(distinct uid,date(start_time))/count(distinct uid),2) avg_active_days,
            count(distinct uid) mau
    from exam_record
where year(submit_time) =2021
group by month

2.SQL18 月总刷题数和日均刷题数

select DATE_FORMAT(submit_time,'%Y%m') submit_month,
        count(question_id) month_q_cnt,
        round(count(question_id)/day(LAST_DAY(submit_time)),3)
from practice_record
where DATE_FORMAT(submit_time,'%Y')='2021'
group by submit_month
union ALL
select '2021汇总' as submit_month,
        count(question_id) month_q_cnt,
        round(count(id)/31,3) avg_day_q_cnt
from practice_record
where DATE_FORMAT(submit_time,'%Y')='2021'
order by submit_month

3.SQL19 未完成试卷数大于1的有效用户

select uid,
       sum(if(submit_time is null,1,0)) incompiete_cnt,
       sum(if(submit_time is not null,1,0)) compiete_cnt,
        GROUP_CONCAT(distinct concat(substring(start_time,1,10),':',tag) SEPARATOR ';')
from exam_record join examination_info
                  on exam_record.exam_id=examination_info.exam_id
                  where year(start_time)='2021'
group by uid
having incompiete_cnt<5 and incompiete_cnt>1 and compiete_cnt>=1
order by incompiete_cnt desc

• SUBSTRING ( expression, start[, length])
• CONCAT(str1,str2,…)
• GROUP_CANCAT([distinct] str [order by str asc/desc] [separator])

4.多表查询

1.嵌套子查询

1.SQL20 月均完成试卷数不小于3的用户

select tag,count(tag) tag_cnt
from exam_record join examination_info
                  on  exam_record.exam_id=examination_info.exam_id
                  where uid in
                        (select uid
                        from exam_record 
                         where submit_time is not null
                        group by uid
                        having count(submit_time) / COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m")) >= 3)
group by tag
ORDER BY tag_cnt desc

这道题最后实现出来其实也不难，就是逻辑有点绕，月均完成数的计算逻辑题目说的也不是很清，月均完成数这里应该是=完成的题目数/完成题目数的提交月份的非重复计数

2.SQL21 试卷发布当天作答人数和平均分

select  exam_record.exam_id,count(distinct exam_record.uid) uv,round(sum(score)/count(exam_record.uid),1) avg_score
from exam_record join user_info on exam_record.uid=user_info.uid 
                 join examination_info on exam_record.exam_id=examination_info.exam_id
                 where date_format(submit_time,'%Y%m%d')=date_format(release_time,'%Y%m%d')
                      and level>5 and tag='SQL'
                 group by exam_record.exam_id
                 order by uv desc,avg_score

答案是作答完成的人数和其评卷分，但是题目只是说当天作答人数，感觉题目描述不清晰

3.SQL22 作答试卷得分大于过80的人的等级及其分布

select  level,count(level) level_cnt
from exam_record join user_info on exam_record.uid=user_info.uid 
                 join examination_info on exam_record.exam_id=examination_info.exam_id
                 where date_format(submit_time,'%Y%m%d')=date_format(release_time,'%Y%m%d')
                      and score>80 and tag='SQL'
group by level
order by level_cnt desc

2.合并查询

1.SQL23 每个题目和每份试卷被作答的人数和次数

select * from (select exam_id as tid,count( distinct uid) uv,count(uid) pv
from exam_record 
group by exam_id
order by uv desc,pv desc) t1
union ALL
select * from (select question_id as tid,count(distinct uid) uv,count(uid) pv
from practice_record
group by question_id
order by uv desc,pv desc) t2

2.SQL24 分别满足两个活动的人

(select uid,'activity1' activity
from exam_record
where year(submit_time)=2021
group by uid
having min(score)>=85
order by uid) 

union ALL

(select DISTINCT uid,'activity2' activity
 from exam_record join examination_info
                    on exam_record.exam_id=examination_info.exam_id
 where timestampdiff(second,start_time,submit_time)<=duration*30 and difficulty='hard'
      and year(submit_time)=2021 and score>80) 
order by uid

这道题主要是'activity2' activity的用法

3.连接查询

1.SQL25 满足条件的用户的试卷完成数和题目完成数

select user_info.uid,count(distinct exam_record.id) exam_cnt, count(distinct practice_record.id) question_cnt
from user_info  left join practice_record on practice_record.uid=user_info.uid and year(practice_record.submit_time)=2021
                left join  exam_record     on user_info.uid=exam_record.uid and year(exam_record.submit_time)=2021
where   user_info.uid in
        (select  distinct user_info.uid
        from user_info left join exam_record  on user_info.uid=exam_record.uid
                        left  join examination_info on examination_info.exam_id=exam_record.exam_id
        where tag='SQL' and difficulty='hard'  and level=7 
         group by uid
        having avg(score)>80 
        )
group by uid
order by exam_cnt,question_cnt desc

总觉得这个题有点问题，1006完成的不重复题目数不就是2吗
2.SQL26 每个6/7级用户活跃情况

select user_info.uid,
        count(distinct act_month) as act_month_total,
        count(distinct case when year(act_time)=2021 then act_day end) as act_days_2021,
        count(distinct case when year(act_time)=2021 and tag='exam' then act_day end) as act_days_2021_exam,
        count(distinct case when year(act_time)=2021 and tag='question' then act_day end) as act_days_2021_exam
from
(select uid,exam_id as ans_id,start_time as act_time,
        date_format(start_time,'%Y%m') as act_month,
        date_format(start_time,'%Y%m%d') as act_day,
        'exam' as tag from exam_record
union ALL
select uid,question_id as ans_id,submit_time as act_time,
        date_format(submit_time,'%Y%m') as act_month,
        date_format(submit_time,'%Y%m%d') as act_day,
        'question' as tag from practice_record) t
right join user_info on t.uid=user_info.uid
where level in (6,7)
group by uid
order by act_month_total desc,act_days_2021 desc

确实综合性比较强。先将试卷练习表和题目练习表处理之后连接起来，这个过程增加了tag标签，标价这列记录是试卷练习还是题目练习