【牛客刷题-SQL进阶挑战】NO2.表与索引操作

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

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

🚀 SQL9 创建一张新表

📖 原来的用户信息表

🚀 题目描述
现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，
用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。
作为数据分析师，请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。
请写出建表语句将表格中所有限制和说明记录到表里。

备注：
1.后台会通过 SHOWFULLFIELDSFROM user_info_vip 语句，来对比输出结果
2.如果该表已经被其他分析师创建过了，正常返回即可

🍌🍌 答案
createtableifnotexists user_info_vip (
    id int(11)primarykeyauto_incrementcomment'自增ID',
    uid int(11)uniquenotnullcomment'用户ID',
    nick_name varchar(64)comment'昵称',
    achievement int(11)default0comment'成就值',levelint(11)comment'用户等级',
    job varchar(32)comment'职业方向',
    register_time datetimedefaultcurrent_timestampcomment'注册时间');

🚀 SQL10 修改表

📖 用户信息表user_info

🚀 题目描述
现有一张用户信息表user_info，其中包含多年来在平台注册过的用户信息。
请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；
并将表中job列名改为profession，同时varchar字段长度变为10；
achievement的默认值设置为0。

备注：建表时限制的字符集为utf8，MySQL版本为8.*
后台会通过SHOWFULLFIELDSFROM user_info 来对比输出结果。

🚀 建表语句
droptableifexists user_info;CREATETABLEIFNOTEXISTS user_info (
id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
uid intUNIQUENOTNULLCOMMENT'用户ID',`nick_name`varchar(64)COMMENT'昵称',
achievement intCOMMENT'成就值',levelintCOMMENT'用户等级',
job varchar(10)COMMENT'职业方向',
register_time datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间')CHARACTERSET utf8 COLLATE utf8_general_ci;

🍌🍌 答案
ALTERTABLE user_info ADD school varchar(15)afterlevel;ALTERTABLE user_info CHANGE job profession varchar(10);ALTERTABLE user_info modify achievement int(11)DEFAULT0;

🚀 SQL11 删除表

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

现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。
备注:后台会通过SELECT TABLE_NAME FROM information_schema.TABLESWHERE TABLE_NAME LIKE \'exam\_record\_201_\'  来对比输出结果。

🚀 建表语句
droptableifEXISTS exam_record;CREATETABLEIFNOTEXISTS exam_record (
id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
uid intNOTNULLCOMMENT'用户ID',
exam_id intNOTNULLCOMMENT'试卷ID',
start_time datetimeNOTNULLCOMMENT'开始时间',
submit_time datetimeCOMMENT'提交时间',
score tinyintCOMMENT'得分')CHARACTERSET utf8 COLLATE utf8_general_ci;CREATETABLEIFNOTEXISTS exam_record_2010 (LIKE exam_record);CREATETABLEIFNOTEXISTS exam_record_2012 (LIKE exam_record);CREATETABLEIFNOTEXISTS exam_record_2013 (LIKE exam_record);CREATETABLEIFNOTEXISTS exam_record_2014 (LIKE exam_record);CREATETABLEIFNOTEXISTS exam_record_2015 (LIKE exam_record); 

🍌🍌 答案
droptableifexists 
exam_record_2011,
exam_record_2012,
exam_record_2013,
exam_record_2014;

2 🌈 索引的创建、删除

🚀 SQL12 创建索引

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

🚀 题目描述
现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，
需要在examination_info表创建以下索引，规则如下：
在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

🚀 建表语句
droptableifexists examination_info;CREATETABLEIFNOTEXISTS examination_info (
id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
exam_id intUNIQUENOTNULLCOMMENT'试卷ID',
tag varchar(32)COMMENT'类别标签',
difficulty varchar(8)COMMENT'难度',
duration intNOTNULLCOMMENT'时长',
release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_bin;

🍌🍌 答案
-- 唯一索引ALTERTABLE examination_info
ADDUNIQUEINDEX uniq_idx_exam_id(exam_id);-- 全文索引ALTERTABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);-- 普通索引ALTERTABLE examination_info
ADDINDEX idx_duration(duration);

🚀 SQL13 删除索引

🚀 题目描述
请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。
后台会通过 SHOWINDEXFROM examination_info 来对比输出结果。

🚀 建表语句
droptableifexists examination_info;CREATETABLEIFNOTEXISTS examination_info (
id intPRIMARYKEYAUTO_INCREMENTCOMMENT'自增ID',
exam_id intCOMMENT'试卷ID',
tag varchar(32)COMMENT'类别标签',
difficulty varchar(8)COMMENT'难度',
duration intNOTNULLCOMMENT'时长(分钟数)',
release_time datetimeCOMMENT'发布时间')CHARACTERSET utf8 COLLATE utf8_bin;CREATEINDEX idx_duration ON examination_info(duration);CREATEUNIQUEINDEX uniq_idx_exam_id ON examination_info(exam_id);ALTERTABLE examination_info ADD FULLTEXT full_idx_tag(tag);

🍌🍌 答案
dropindex uniq_idx_exam_id on examination_info;dropindex full_idx_tag on examination_info;