MySQL入门练习2---牛客SQL进阶挑战题单

题单链接

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 VALUES(
    NULL,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 start_time < '2021-9-1' AND score IS NULL;

SQL6 删除记录（一）

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE,start_time,submit_time)<5 AND score<60

SQL7 删除记录（二）

DELETE FROM exam_record
WHERE submit_time IS NULL OR timestampdiff(minute,start_time,submit_time)<5
ORDER BY start_time
LIMIT 3

SQL8 删除记录（三）

truncate table exam_record

SQL9 创建一张新表

CREATE TABLE IF NOT EXISTS user_info_vip(
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    `level` int COMMENT '用户等级',
    job VARCHAR(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)

SQL10 修改表

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;


增加列在某列之后
ALTER TABLE 增加的表格 ADD 增加列的名称 数据类型 位置(after level 在level 之后)

更换列的名称及数据类型
ALTER TABLE user_info CHANGE 原列名 修改列名 修改数据类型

更改数据类型
ALTER TABLE 表名 MODIFY 修改列名称 数据类型 默认值等

SQL11 删除表

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

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

SQL13 删除索引

DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

SQL14 SQL类别高难度试卷得分的截断平均值

SELECT tag,difficulty,
ROUND((SUM(score)-MAX(score)-MIN(score))/(COUNT(score)-2),1) AS clip_avg_score
FROM examination_info
LEFT JOIN exam_record ON examination_info.exam_id=exam_record.exam_id
WHERE tag='SQL' AND difficulty='hard';

SQL15 统计作答次数

SELECT COUNT(exam_id) AS total_pv,
COUNT(submit_time) AS complete_pv,
COUNT(DISTINCT IF(submit_time is not NULL,exam_id,null)) AS complete_exam_cnt
FROM exam_record

SQL16 得分不小于平均分的最低分

SELECT MIN(score) AS min_score_over_avg
FROM exam_record
JOIN examination_info ON examination_info.exam_id=exam_record.exam_id
WHERE examination_info.tag="SQL" AND score>=
(
    SELECT avg(score) FROM exam_record 
    JOIN examination_info ON examination_info.exam_id=exam_record.exam_id
    WHERE examination_info.tag="SQL"
)

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

SELECT DATE_FORMAT(start_time,"%Y%m") AS month,
ROUND(COUNT(distinct uid,DATE_FORMAT(start_time,"%Y%m%d")) / COUNT(distinct uid) , 2) AS 	avg_active_days	,
COUNT(distinct uid) AS mau
FROM exam_record
WHERE submit_time is not NULL AND YEAR(submit_time) = 2021
GROUP BY month;