SQL(入门实战06)

最新推荐文章于 2024-09-14 23:32:22 发布

林园857

最新推荐文章于 2024-09-14 23:32:22 发布

阅读量2.5k

点赞数 4

分类专栏： mysql 文章标签：数据库开发

本文链接：https://blog.csdn.net/weixin_44464850/article/details/124429666

版权

mysql 专栏收录该内容

24 篇文章 8 订阅

订阅专栏

本文介绍了如何从exam_record数据表中计算SQL类别高难度试卷的得分截断平均值，即去掉一个最大值和一个最小值后的平均分。通过关联exam_record和examination_info表，筛选出SQL高难度试卷，然后利用SQL查询计算得分的截断平均值，展示了解题的两种不同方法。

摘要由CSDN通过智能技术生成

1.创建一张新表

现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。

原来的用户信息表：

在这里插入图片描述

作为数据分析师，请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。

你应该返回的输出如下表格所示，请写出建表语句将表格中所有限制和说明记录到表里。

在这里插入图片描述

备注：
1.后台会通过 SHOW FULL FIELDS FROM user_info_vip 语句，来对比输出结果

2.如果该表已经被其他分析师创建过了，正常返回即可

示例1

drop table if EXISTS user_info_vip;

输出
id|int|None|NO|PRI|None|auto_increment|select,insert,update,references|自增ID
uid|int|None|NO|UNI|None||select,insert,update,references|用户ID
nick_name|varchar(64)|utf8_general_ci|YES||None||select,insert,update,references|昵称
achievement|int|None|YES||0||select,insert,update,references|成就值
level|int|None|YES||None||select,insert,update,references|用户等级
job|varchar(32)|utf8_general_ci|YES||None||select,insert,update,references|职业方向
register_time|datetime|None|YES||CURRENT_TIMESTAMP|DEFAULT_GENERATED|select,insert,update,references|注册时间

题解

create table 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'注册时间'
)

拓展

创建表
CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

 从另一张表复制表结构创建表： 
 CREATE TABLE tb_name LIKE tb_name_old

 从另一张表的查询结果创建表：
 CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

修改表
     ADD COLUMN <列名> <类型>  -- 增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
     | MODIFY COLUMN <列名> <类型> -- 修改列类型
     | DROP COLUMN <列名> -- 删除列
     | RENAME TO <新表名> -- 修改表名
     | CHARACTER SET <字符集名> -- 修改字符集
     | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。
 
 
细节：
自增ID：AUTO_INCREMENT；
设置主键：PRIMARY KEY；
唯一性约束：UNIQUE
非空约束：NOT NULL
设置默认值：DEFAULT 0
当前时间戳：CURRENT_TIMESTAMP
评论/注释：COMMENT
如果该表已创建过，正常返回：IF NOT EXISTS

2.修改表

现有一张用户信息表user_info，其中包含多年来在平台注册过的用户信息。

用户信息表user_info：

在这里插入图片描述

请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，同时varchar字段长度变为10；achievement的默认值设置为0。

输出结果示例：

在这里插入图片描述

备注：建表时限制的字符集为utf8，MySQL版本为8.*

后台会通过SHOW FULL FIELDS FROM user_info 来对比输出结果。

示例：

drop table if exists user_info;
CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

题解

--字段level的后面增加一列最多可保存15个汉字的字段school
alter table user_info add school varchar(15) after level;

--将表中job列名改为profession，同时varchar字段长度变为10 
alter table user_info change column job profession varchar(10);

--achievement的默认值设置为0
alter table user_info modify achievement int(11) default 0;

3.删除表

现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录。一般每年都会为exam_record表建立一张备份表exam_record_{YEAR}，{YEAR}为对应年份。

现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。

备注:后台会通过SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE ‘exam_record_201_’ 来对比输出结果。

示例1

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_2010 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2012 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2013 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2014 (LIKE exam_record); 
CREATE TABLE IF NOT EXISTS exam_record_2015 (LIKE exam_record);

输出

exam_record_2010
exam_record_2015

题解

drop table if exists exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014;

4.创建索引

现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：

在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

根据题意，将返回如下结果：

在这里插入图片描述

备注：后台会通过 SHOW INDEX FROM examination_info 语句来对比输出结果

示例1

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

输出

examination_info|0|PRIMARY|1|id|A|0|None|None||BTREE|||YES|None
examination_info|0|exam_id|1|exam_id|A|0|None|None||BTREE|||YES|None
examination_info|0|uniq_idx_exam_id|1|exam_id|A|0|None|None||BTREE|||YES|None
examination_info|1|idx_duration|1|duration|A|0|None|None||BTREE|||YES|None
examination_info|1|full_idx_tag|1|tag|None|0|None|None|YES|FULLTEXT|||YES|None

题解

--在duration列创建普通索引idx_duration
create index idx_duration on examination_info(duration);

--在exam_id列创建唯一性索引uniq_idx_exam_id
create unique index uniq_idx_exam_id on examination_info(exam_id);

--在tag列创建全文索引full_idx_tag
create fulltext index full_idx_tag on examination_info(tag);

拓展

create 方式创建索引
CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引  


alter方式创建索引：
ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

drop方式删除索引：
DROP INDEX <索引名> ON <表名>

alter方式删除索引：
ALTER TABLE <表名> DROP INDEX <索引名>


索引的使用：

索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，
在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；

索引不包含有NULL值的列
一个查询只使用一次索引，where中如果使用了索引，
order by就不会使用like做字段比较时只有前缀确定时才会使用索引
在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引

5.删除索引

请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

后台会通过 SHOW INDEX FROM examination_info 来对比输出结果。

示例1：

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长(分钟数)',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
ALTER TABLE examination_info ADD FULLTEXT full_idx_tag(tag);

输出

examination_info|0|PRIMARY|1|id|A|0|None|None||BTREE|||YES|None
examination_info|1|idx_duration|1|duration|A|0|None|None||BTREE|||YES|None

题解

--删除唯一索引uniq_idx_exam_id
drop index uniq_idx_exam_id on examination_info;
--删除全文索引full_idx_tag
drop index full_idx_tag on examination_info;

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

牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据：examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

在这里插入图片描述

示例数据：exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

在这里插入图片描述

根据输入你的查询结果如下：

在这里插入图片描述

从examination_info表可知，试卷9001为高难度SQL试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为81.6666667，保留一位小数后为81.7

示例1

drop table if exists examination_info;
CREATE TABLE  examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'medium', 80, '2020-08-02 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9001, '2021-09-02 12:01:01', null, null),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1002, 9001, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9001, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 50);

题解

思路1：
1.关联作答记录和试卷信息：join examination_info 
2.筛选SQL高难度试卷：where tag="SQL" and difficulty="hard"
3.计算截断平均值：(和-最大值-最小值) / (总个数-2) 

方式一：
写法1：
select 
distinct info.tag,
info.difficulty,
round((sum(record.score)-max(record.score)-min(record.score))
/(count(record.score)-2),1) as clip_avg_score
from examination_info info
inner join exam_record record
on info.exam_id=record.exam_id and record.score
where info.difficulty='hard' and info.tag='SQL'
group by info.exam_id

写法二：using()  using等价于join操作中的on
select tag, difficulty,
    round((sum(score) - max(score) - min(score)) 
	/(count(score) - 2), 1) as clip_avg_score
from exam_record
join examination_info using(exam_id)
where tag="SQL" and difficulty="hard"

思路二：
1.关联作答记录和试卷信息：join examination_info 
2.筛选SQL高难度试卷：where tag="SQL" and difficulty="hard"
3.row_number() over ( ORDER BY score ASC ) AS rk_asc 获取最高
 row_number() over ( ORDER BY score DESC ) AS rk_desc 获取最低
4.where 	rk_asc <> 1 AND rk_desc <> 1 去除最高和最低分数
5.根据tag,difficulty 分组

SELECT
	tag,
	difficulty,
	round( avg( score ), 1 ) AS clip_avg_score 
FROM
	(
	SELECT
		tag,
		difficulty,
		score,
		row_number() over ( ORDER BY score ASC ) AS rk_asc,
		row_number() 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