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)
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'
SQL3 插入记录（三）
REPLACE INTO examination_info(exam_id, tag, difficulty, duration, release_time)
VALUES ('9003', 'SQL', 'hard', '90', '2021-01-01 00:00:00')
SQL4?更新记录（一）
UPDATE examination_info
SET tag = 'Python'
WHERE tag = 'PYTHON'
SQL5?更新记录（二）
UPDATE exam_record
SET submit_time = '2099-01-01 00:00:00', score = 0
WHERE DATE(start_time) < '2021-09-01' AND submit_time IS NULL
SQL6?删除记录（一）
DELETE FROM exam_record
WHERE UNIX_TIMESTAMP(submit_time) - UNIX_TIMESTAMP(start_time) < 5 * 60 AND score < 60
SQL7?删除记录（二）
DELETE FROM exam_record
WHERE submit_time IS NULL OR UNIX_TIMESTAMP(submit_time) - UNIX_TIMESTAMP(start_time) < 5 * 60
ORDER BY start_time ASC
LIMIT 3
SQL8?删除记录（三）
TRUNCATE TABLE exam_record
SQL9?创建一张新表
CREATE TABLE IF NOT EXISTS user_info_vip (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
`uid` INT(11) NOT NULL UNIQUE KEY 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 '注册时间'
) CHARSET='utf8'
SQL10?修改表
ALTER TABLE user_info
ADD COLUMN `school` VARCHAR(15) AFTER `level`;
ALTER TABLE user_info
CHANGE COLUMN `job` `profession` VARCHAR(10);
ALTER TABLE user_info
MODIFY COLUMN `achievement` INT(11) DEFAULT 0;
SQL11?删除表
DROP TABLE IF EXISTS exam_record_2011;
DROP TABLE IF EXISTS exam_record_2012;
DROP TABLE IF EXISTS exam_record_2013;
DROP TABLE IF EXISTS exam_record_2014;
SQL12?创建索引
ALTER TABLE examination_info ADD INDEX idx_duration(duration);
ALTER TABLE examination_info ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);
ALTER TABLE examination_info ADD FULLTEXT INDEX full_idx_tag(tag);
SQL13?删除索引
ALTER TABLE examination_info DROP INDEX uniq_idx_exam_id;
ALTER TABLE examination_info DROP INDEX full_idx_tag;
SQL14?SQL类别高难度试卷得分的截断平均值
SELECT 'SQL' AS tag, 'hard' AS difficulty, ROUND(AVG(temp.score), 1) AS clip_avg_score
FROM (
SELECT record.score, RANK() OVER (ORDER BY record.score ASC) AS asc_rank, RANK() OVER (ORDER BY record.score DESC) AS desc_rank
FROM examination_info AS info JOIN exam_record AS record ON info.exam_id = record.exam_id
WHERE info.tag = 'SQL' AND info.difficulty = 'hard' AND record.score IS NOT NULL
) temp
WHERE temp.asc_rank > 1 AND temp.desc_rank > 1
SQL15?统计作答次数
SELECT
COUNT(1) AS total_pv,
SUM(IF(submit_time IS NOT NULL, 1, 0)) AS complete_pv,
COUNT(DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id END) AS complete_exam_cnt
FROM
exam_record
SQL16?得分不小于平均分的最低分
SELECT subquery.score
FROM (
SELECT record.score, record.score - temp.avg_score AS diff
FROM exam_record AS record JOIN examination_info AS info ON record.exam_id = info.exam_id JOIN (
SELECT AVG(score) AS avg_score
FROM exam_record JOIN examination_info ON exam_record.exam_id = examination_info.exam_id
WHERE tag = 'SQL'
) temp
WHERE info.tag = 'SQL'
) subquery
WHERE subquery.diff >= 0
ORDER BY subquery.diff ASC
LIMIT 1
SQL17?平均活跃天数和月活人数
SELECT SUBSTR(temp.active_date, 1, 6) AS month, ROUND(COUNT(1) / COUNT(DISTINCT temp.uid), 2) AS avg_active_days, COUNT(DISTINCT temp.uid) AS mau
FROM (
SELECT uid, DATE_FORMAT(submit_time, "%Y%m%d") AS active_date
FROM exam_record
WHERE submit_time IS NOT NULL AND YEAR(submit_time) = '2021'
GROUP BY uid, active_date
) temp
GROUP BY month
SQL18?月总刷题数和日均刷题数
SELECT
DATE_FORMAT(submit_time, '%Y%m') AS submit_month,
COUNT(1) AS month_q_cnt,
ROUND(COUNT(1) / 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
'2021汇总' AS submit_month,
COUNT(1) AS month_q_cnt,
ROUND(COUNT(1) / 31, 3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time) = '2021'
ORDER BY submit_month ASC
SQL19?未完成试卷数大于1的有效用户
SELECT *
FROM (
SELECT
record.uid,
SUM(IF(record.submit_time IS NULL, 1, 0)) AS incomplete_cnt,
SUM(IF(record.submit_time IS NOT NULL, 1, 0)) AS complete_cnt,
GROUP_CONCAT(DISTINCT CONCAT(DATE(record.start_time), ':', info.tag) ORDER BY DATE(record.start_time), info.tag SEPARATOR ';') AS detail
FROM exam_record AS record JOIN examination_info AS info ON record.exam_id = info.exam_id
WHERE YEAR(record.start_time) = '2021'
GROUP BY record.uid
) temp
WHERE temp.incomplete_cnt > 1 AND temp.incomplete_cnt < 5 AND temp.complete_cnt >= 1
ORDER BY temp.incomplete_cnt DESC
SQL20?月均完成试卷数不小于3的用户爱作答的类别
SELECT info.tag, COUNT(record.start_time) AS tag_cnt
FROM (
SELECT temp.uid
FROM (
SELECT uid, MONTH(submit_time) AS month, COUNT(submit_time) AS cnt
FROM exam_record
WHERE submit_time IS NOT NULL
GROUP BY uid, MONTH(submit_time)
) temp
GROUP BY temp.uid
HAVING AVG(temp.cnt) >= 3
) usr
JOIN exam_record AS record ON usr.uid = record.uid
JOIN examination_info AS info ON record.exam_id = info.exam_id
GROUP BY info.tag
ORDER BY tag_cnt DESC
SQL21?试卷发布当天作答人数和平均分
SELECT info.exam_id, COUNT(DISTINCT record.uid) AS uv, ROUND(AVG(record.score), 1) AS avg_score
FROM examination_info AS info JOIN exam_record AS record ON info.exam_id = record.exam_id AND DATE(info.release_time) = DATE(record.start_time)
JOIN user_info AS usr ON usr.uid = record.uid
WHERE usr.level > 5 AND info.tag = 'SQL'
GROUP BY info.exam_id
ORDER BY uv DESC, avg_score ASC
SQL22?作答试卷得分大于过80的人的用户等级分布
SELECT usr.level, COUNT(DISTINCT record.uid) AS level_cnt
FROM exam_record AS record JOIN examination_info AS info ON record.exam_id = info.exam_id
JOIN user_info AS usr ON record.uid = usr.uid
WHERE info.tag = 'SQL' AND record.score > 80
GROUP BY usr.level
ORDER BY level_cnt DESC, usr.level DESC
SQL23?每个题目和每份试卷被作答的人数和次数
(
SELECT *
FROM (
SELECT exam_id AS tid, COUNT(DISTINCT uid) AS uv, COUNT(1) AS pv
FROM exam_record
GROUP BY tid
ORDER BY uv DESC, pv DESC
) ta
)
UNION ALL
(
SELECT *
FROM (
SELECT question_id AS tid, COUNT(DISTINCT uid) AS uv, COUNT(1) AS pv
FROM practice_record
GROUP BY tid
ORDER BY uv DESC, pv DESC
) tb
)
SQL24?分别满足两个活动的人
SELECT temp.uid, temp.activity
FROM (
SELECT DISTINCT record.uid, 'activity1' AS activity
FROM exam_record AS record
WHERE YEAR(submit_time) = '2021' AND NOT EXISTS (
SELECT 1
FROM exam_record AS record_tmp
WHERE record_tmp.uid = record.uid AND record_tmp.score < 85
)
UNION
SELECT DISTINCT uid, 'activity2' AS activity
FROM examination_info AS info JOIN exam_record AS record ON info.exam_id = record.exam_id
WHERE YEAR(submit_time) = '2021' AND TIMESTAMPDIFF(SECOND, start_time, submit_time) < duration * 60 / 2 AND score > 80 AND difficulty = 'hard'
) temp
ORDER BY temp.uid
SQL25?满足条件的用户的试卷完成数和题目练习数
SELECT master_usr.uid, exam_stat.exam_cnt, IFNULL(prac_stat.question_cnt, 0) AS question_cnt
FROM (
SELECT usr.uid
FROM user_info AS usr JOIN exam_record AS exam_rec ON usr.uid = exam_rec.uid JOIN examination_info AS exam_info ON exam_rec.exam_id = exam_info.exam_id
WHERE exam_info.difficulty = 'hard' AND exam_info.tag = 'SQL' AND usr.level = '7'
GROUP BY usr.uid
HAVING AVG(exam_rec.score) > 80
) master_usr JOIN (
SELECT uid, COUNT(1) AS exam_cnt
FROM exam_record
WHERE YEAR(submit_time) = '2021'
GROUP BY uid
) exam_stat ON master_usr.uid = exam_stat.uid LEFT JOIN (
SELECT uid, COUNT(1) AS question_cnt
FROM practice_record
WHERE YEAR(submit_time) = '2021'
GROUP BY uid
) prac_stat ON master_usr.uid = prac_stat.uid
ORDER BY exam_stat.exam_cnt ASC, prac_stat.question_cnt DESC
SQL26?每个6/7级用户活跃情况
WITH exam_stat AS (
SELECT user_info.uid, DATE(exam_record.start_time) AS act_day_exam
FROM user_info JOIN exam_record ON user_info.uid = exam_record.uid
WHERE user_info.level IN ('6', '7')
GROUP BY user_info.uid, DATE(exam_record.start_time)
), prac_stat AS (
SELECT user_info.uid, DATE(practice_record.submit_time) AS act_day_prac
FROM user_info JOIN practice_record ON user_info.uid = practice_record.uid
WHERE user_info.level IN ('6', '7')
GROUP BY user_info.uid, DATE(practice_record.submit_time)
)
SELECT usr.uid,
IFNULL(act_month.act_month_total, 0) AS act_month_total,
IFNULL(act_day.act_days_2021, 0) AS act_days_2021,
IFNULL(act_day_exam.act_days_2021_exam, 0) AS act_days_2021_exam,
IFNULL(act_day_prac.act_days_2021_question, 0) AS act_days_2021_question
FROM (
SELECT uid
FROM user_info
WHERE `level` IN ('6', '7')
) usr LEFT JOIN (
SELECT tmp.uid, COUNT(DISTINCT tmp.act_month_col) AS act_month_total
FROM (
SELECT uid, DATE_FORMAT(act_day_exam, '%Y%m') AS act_month_col
FROM exam_stat
UNION ALL
SELECT uid, DATE_FORMAT(act_day_prac, '%Y%m') AS act_month_col
FROM prac_stat
) tmp
GROUP BY tmp.uid
) act_month ON usr.uid = act_month.uid LEFT JOIN (
SELECT tmp.uid, COUNT(DISTINCT tmp.act_days_col) AS act_days_2021
FROM (
SELECT uid, DATE(act_day_exam) AS act_days_col
FROM exam_stat
WHERE YEAR(act_day_exam) = '2021'
UNION ALL
SELECT uid, DATE(act_day_prac) AS act_days_col
FROM prac_stat
WHERE YEAR(act_day_prac) = '2021'
) tmp
GROUP BY tmp.uid
) act_day ON usr.uid = act_day.uid LEFT JOIN (
SELECT uid, COUNT(DISTINCT DATE(act_day_exam)) AS act_days_2021_exam
FROM exam_stat
WHERE YEAR(act_day_exam) = '2021'
GROUP BY uid
) act_day_exam ON usr.uid = act_day_exam.uid LEFT JOIN (
SELECT uid, COUNT(DISTINCT DATE(act_day_prac)) AS act_days_2021_question
FROM prac_stat
WHERE YEAR(act_day_prac) = '2021'
GROUP BY uid
) act_day_prac ON usr.uid = act_day_prac.uid
ORDER BY act_month_total DESC, act_days_2021 DESC
|
