SQL语法学习记录（三）

牛客-SQL进阶挑战 里的题目，这里只记录1~25题

除了之前的查询，还包括创建表，创建索引等

-- 1. 插入数据
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)


-- 省略列名，默认使用所有的列
-- 自增Id 填充null/default/0
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. 插入，从另一张表中导入数据
-- 自增主键不要复制
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 submit_time like '2020%'

-- 填充0或null，不能用default
INSERT INTO exam_record_before_2021
SELECT null, uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE submit_time like '2020%'


-- 3. repalce into
-- 插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
REPLACE INTO examination_info 
values (null, 9003, "SQL", "hard", 90, "2021-01-01 00:00:00")

-- 7. delete + limit
DELETE FROM exam_record
WHERE submit_time is null or timestampdiff(minute, start_time, submit_time)<5
ORDER BY start_time
LIMIT 3

-- 8. 删除表
-- DROP TABLE　清除数据并且销毁表
-- TRUNCATE TABLE　只清除数据，保留表结构，列，权限，索引，视图，关
-- DELETE TABLE　删除（符合某些条件的）数据，执行后可以撤销
truncate exam_record

-- 9. 创建表
-- PRIMARY KEY -- 可选的约束，主键
-- FOREIGN KEY -- 外键，引用其他表的键值
-- AUTO_INCREMENT -- 自增ID
-- COMMENT comment -- 列注释（评论）
-- DEFAULT default_value -- 默认值
-- UNIQUE -- 唯一性约束，不允许两条记录该列值相同
-- NOT NULL -- 该列非空

CREATE TABLE IF NOT EXISTS user_info_vip(
    id int(11)  primary key auto_increment comment '自增ID',
    uid int(11) unique  not null 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 '注册时间'
)


-- 10. 修改表
ALTER TABLE user_info add school varchar(15) after level;
ALTER TABLE user_info change job profession varchar(10);
ALTER TABLE user_info modify achievement int(11) default 0;

-- 11. 删除多张表
drop table if EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

-- 12. 创建索引
-- 普通索引、唯一索引、全文索引
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);

-- 13. 删除索引
-- 好像不能用delete, 也不能一起删除
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

-- 14. 截断平均值
-- sum-min-max
SELECT tag, difficulty, 
        round((sum(score)-min(score)-max(score))/(count(score)-2), 1) as clip_avg_score
FROM examination_info
LEFT JOIN exam_record using(exam_id)
WHERE tag="SQL" and difficulty="hard"
GROUP BY tag

-- 15. distinct
-- + if
SELECT count(start_time) as total_pv,
        count(submit_time) as complete_pv,
        count(distinct if(score is null, null, exam_id)) as complete_exam_cnt
FROM exam_record


-- + case
SELECT count(start_time) as total_pv,
        count(submit_time) as complete_pv,
        count(distinct case when score is null then null else exam_id end) as complete_exam_cnt
FROM exam_record

-- 16. 得分不小于平均分的最小值
-- where 子句 >
SELECT min(score) as min_score_over_avg
FROM exam_record a
LEFT JOIN examination_info b using(exam_id)
WHERE b.tag = "SQL" and score>=(
    SELECT avg(score) as avg_score
    FROM exam_record a
    LEFT JOIN examination_info b using(exam_id)
    WHERE tag = "SQL"
)

-- 聚合窗口函数 avgSELECT min(score) as min_score_over_avg
FROM (
    SELECT score, avg(score) over(partition by tag) as avg_score
    FROM exam_record a
    LEFT JOIN examination_info b using(exam_id)
    WHERE tag = "SQL"
) t
WHERE score>=avg_score



SELECT month, count(score) as avg_active_days, count(distinct uid) as mau
FROM (
    SELECT month(submit_time) as month, score, uid
    FROM exam_record
) a
GROUP BY month

-- 17. 平均活跃天数和月活人数
-- 注意 同一个人一天活跃多次的情况, 只算一次, 因此要用distinct uid, day
-- 1）不行
SELECT month, round(count(distinct uid)/count(distinct uid), 2) as avg_active_days, count(distinct uid) as mau
# SELECT *
FROM (
    SELECT date_format(submit_time, "%Y%m") as month, submit_time, score, uid
    FROM exam_record
    WHERE submit_time is not null and year(submit_time)=2021
) a
GROUP BY month

-- 2）行
SELECT month, round(count(distinct uid, day)/count(distinct uid), 2) as avg_active_days, count(distinct uid) as mau
# SELECT *
FROM (
    SELECT date_format(submit_time, "%Y%m") as month, date_format(submit_time, "%Y%m%d") as day, submit_time, score, uid
    FROM exam_record
    WHERE submit_time is not null and year(submit_time)=2021
) a
GROUP BY month

-- 18. 计算月平均
-- max(day(last_day(submit_time)))  一个月的天数
SELECT date_format(submit_time, '%Y%m') as submit_month,
        count(question_id) as month_q_cnt, 
        round(count(question_id) / max(day(last_day(submit_time))), 3) as avg_day_q_cnt
FROM practice_record
WHERE year(submit_time) = 2021
GROUP BY submit_month


UNION

SELECT concat(date_format(submit_time, '%Y'), "汇总") as submit_month, 
        count( question_id) as month_q_cnt, 
        round(count( question_id)/31, 3) as avg_day_q_cnt
FROM practice_record
WHERE year(submit_time) = 2021
GROUP BY submit_month

ORDER BY submit_month


-- 19. group_concat
-- group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])
-- 这里需要dictinct, 因为对于同一个uid，day+tag 可能有多个记录，例如tag="SQL"，day="2019-01-01", 但是有"Easy" 和 "Herd" 两个版本
SELECT  uid, 
        count(start_time)-count(submit_time) as incomplete_cnt,
        count(submit_time) as complete_cnt,
        group_concat(distinct concat(date_format(start_time, "%Y-%m-%d"), ':', tag) separator ';') as detail
FROM exam_record as a
LEFT JOIN examination_info as b using(exam_id)
WHERE year(start_time)=2021
GROUP BY uid
HAVING complete_cnt>=1 and incomplete_cnt>1 and incomplete_cnt<5
ORDER BY incomplete_cnt desc


-- 20. 在月平均满足条件下，统计tag
-- 计算月平均 HAVING count(date_format(submit_time, "%Y%m")) / count(distinct date_format(submit_time, "%Y%m")) >=3
SELECT tag, count(*) as tag_cnt
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE uid in (
    SELECT uid
    FROM exam_record
    GROUP BY uid
    HAVING count(date_format(submit_time, "%Y%m")) / count(distinct date_format(submit_time, "%Y%m")) >=3
) 
GROUP BY tag
ORDER BY tag_cnt desc


-- 21.考试记录+考试信息+用户信息
-- 三表联合查询
SELECT exam_id, count(distinct uid) as uv, round(avg(score), 1) as avg_score
FROM exam_record
LEFT JOIN user_info using(uid)
LEFT JOIN examination_info using(exam_id)
WHERE tag='SQL' and level>5
GROUP BY exam_id
ORDER BY uv desc, avg_score

-- 22. 和上一题类似
SELECT level, count(*) as level_cnt
FROM exam_record
LEFT JOIN user_info using(uid)
LEFT JOIN examination_info using(exam_id)
WHERE tag="SQL" and score>80
GROUP BY level
ORDER BY level_cnt desc

-- 23. union 要分别排序
-- union可以使用任何selcet语句，但order by子句只能在最后一次使用
-- 所以为了分开排序，就再套了一个select语句
SELECT * FROM (
    SELECT exam_id as tid, count(distinct uid) as uv, count(*) as pv
    FROM exam_record
    GROUP BY exam_id
    ORDER BY uv desc, pv desc
) a

UNION ALL

SELECT * FROM (
    SELECT question_id as tid, count(distinct uid) as uv, count(*) as pv
    FROM practice_record
    GROUP BY question_id
    ORDER BY uv desc, pv desc
) b

-- 24. 两种类别，用union
-- 注意顺序问题，where, group by, order by
SELECT uid, "activity1" as activity
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE year(start_time)=2021
GROUP BY uid
HAVING min(score)>=85

UNION 

SELECT uid, "activity2" as activity
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE timestampdiff(second, start_time, submit_time) <= (duration*60)/2 
        and score>80 
        and difficulty="hard"
        and year(start_time)=2021
ORDER BY uid


-- 25. 不亏为困难题
-- 关键，先查出符合条件的uid，再与practice_record联合
SELECT uid, exam_cnt, count(question_id) as question_cnt
FROM (
    SELECT uid, count(score) as exam_cnt
    FROM exam_record
    WHERE uid in (
        SELECT uid
        FROM exam_record
        LEFT JOIN examination_info using(exam_id)
        LEFT JOIN user_info using(uid)
        WHERE tag="SQL" and difficulty="hard" and level=7
        GROUP BY uid
        HAVING avg(score)>80
    ) and year(submit_time)=2021
    GROUP BY uid
    HAVING count(score)>=1
) a
LEFT JOIN practice_record b using(uid)
WHERE year(submit_time)=2021 or submit_time is null  # 未做题不能去掉，应为0
GROUP BY uid
ORDER BY exam_cnt, question_cnt desc