目录 一,delete、drop和truncate区别 1.delete: 2.drop: 3.truncate: 二,关于行转列的知识 三.面试题题目+详解 1.建立数据库 2.按要求建立数据表 学生表: 成绩表: 教师表: 课程表
目录
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
3.查询存在" 01 “课程但可能不存在” 02 (不存在时显示为 null )
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
12. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
13. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
前言:Mysql在面试中主要是以题目的方式出现,为此我精选了15题来进行拿捏它!!!
delete、DROP和truncate,详解如下:
1.delete:
功能:delete 用于从表中删除一条或多条数据。
影响:delete 操作是 DML(数据操作语言)语句,它会触发触发器、产生回滚日志,且可以使用 WHERE 子句对要删除的数据进行筛选。
回滚:delete 操作可以通过事务回滚来撤消已删除的行。
返回值:delete 操作在执行时会返回删除的行数。
2.drop:功能:drop 用于删除整个数据库对象(如表、视图、索引等)。
影响:drop 操作是 DDL(数据定义语言)语句,它会永久性地从数据库中删除对象。
回滚:drop 操作不可回滚,一旦执行成功,对象将被永久性删除。
返回值:drop 操作不返回任何值。
3.truncate:功能:truncate 用于删除表中的所有数据。
影响:truncate 操作是 DDL 语句,它会将表完全清空,并且比 DELETE 操作更快,因为它不会触发触发器、不写回滚日志,并且不使用 WHERE 子句进行筛选。
回滚:truncate 操作不可回滚,一旦执行成功,数据将被永久性删除。
返回值:truncate 操作在执行时不返回删除的行数。
总的来说:效率问题是drop>truncate>delete
行转列是一种重塑或重新组织数据的操作,将原先以行的形式存储的数据转换为以列的形式存储。行转列的技术可以使用在各种领域,如数据分析、报表生成等。
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_mysql_course-- ----------------------------DROP TABLE IF EXISTS `t_mysql_course`;CREATE TABLE `t_mysql_course` ( `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号', `cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称', `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号', PRIMARY KEY (`cid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_course-- ----------------------------INSERT INTO `t_mysql_course` VALUES ('01', '语文', '02');INSERT INTO `t_mysql_course` VALUES ('02', '数学', '01');INSERT INTO `t_mysql_course` VALUES ('03', '英语', '03');-- ------------------------------ Table structure for t_mysql_score-- ----------------------------DROP TABLE IF EXISTS `t_mysql_score`;CREATE TABLE `t_mysql_score` ( `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号,外键', `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号,外键', `score` float NULL DEFAULT 0 COMMENT '成绩', INDEX `sid`(`sid`) USING BTREE, INDEX `cid`(`cid`) USING BTREE, CONSTRaiNT `t_mysql_score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `t_mysql_student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `t_mysql_score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_mysql_course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_score-- ----------------------------INSERT INTO `t_mysql_score` VALUES ('01', '01', 80);INSERT INTO `t_mysql_score` VALUES ('01', '02', 90);INSERT INTO `t_mysql_score` VALUES ('01', '03', 99);INSERT INTO `t_mysql_score` VALUES ('02', '01', 70);INSERT INTO `t_mysql_score` VALUES ('02', '02', 60);INSERT INTO `t_mysql_score` VALUES ('02', '03', 80);INSERT INTO `t_mysql_score` VALUES ('03', '01', 80);INSERT INTO `t_mysql_score` VALUES ('03', '02', 80);INSERT INTO `t_mysql_score` VALUES ('03', '03', 80);INSERT INTO `t_mysql_score` VALUES ('04', '01', 50);INSERT INTO `t_mysql_score` VALUES ('04', '02', 30);INSERT INTO `t_mysql_score` VALUES ('04', '03', 20);INSERT INTO `t_mysql_score` VALUES ('05', '01', 76);INSERT INTO `t_mysql_score` VALUES ('05', '02', 87);INSERT INTO `t_mysql_score` VALUES ('06', '01', 31);INSERT INTO `t_mysql_score` VALUES ('06', '03', 34);INSERT INTO `t_mysql_score` VALUES ('07', '02', 89);INSERT INTO `t_mysql_score` VALUES ('07', '03', 98);-- ------------------------------ Table structure for t_mysql_student-- ----------------------------DROP TABLE IF EXISTS `t_mysql_student`;CREATE TABLE `t_mysql_student` ( `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号', `sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生名称', `sage` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生年龄', `ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别', PRIMARY KEY (`sid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_student-- ----------------------------INSERT INTO `t_mysql_student` VALUES ('01', '赵雷', '1990-01-01', '男');INSERT INTO `t_mysql_student` VALUES ('02', '钱电', '1990-12-21', '男');INSERT INTO `t_mysql_student` VALUES ('03', '孙风', '1990-12-20', '男');INSERT INTO `t_mysql_student` VALUES ('04', '李云', '1990-12-06', '男');INSERT INTO `t_mysql_student` VALUES ('05', '周梅', '1991-12-01', '女');INSERT INTO `t_mysql_student` VALUES ('06', '吴兰', '1992-01-01', '女');INSERT INTO `t_mysql_student` VALUES ('07', '郑竹', '1989-01-01', '女');INSERT INTO `t_mysql_student` VALUES ('09', '张三', '2017-12-20', '女');INSERT INTO `t_mysql_student` VALUES ('10', '李四', '2017-12-25', '女');INSERT INTO `t_mysql_student` VALUES ('11', '李四', '2012-06-06', '女');INSERT INTO `t_mysql_student` VALUES ('12', '赵六', '2013-06-13', '女');INSERT INTO `t_mysql_student` VALUES ('13', '孙七', '2014-06-01', '女');-- ------------------------------ Table structure for t_mysql_teacher-- ----------------------------DROP TABLE IF EXISTS `t_mysql_teacher`;CREATE TABLE `t_mysql_teacher` ( `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号', `tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师名称', PRIMARY KEY (`tid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_teacher-- ----------------------------INSERT INTO `t_mysql_teacher` VALUES ('01', '张三');INSERT INTO `t_mysql_teacher` VALUES ('02', '李四');INSERT INTO `t_mysql_teacher` VALUES ('03', '王五');SET FOREIGN_KEY_CHECKS = 1;
-- 1.学生表-t_mysql_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别-- 2.教师表-t_mysql_teacher
-- tid 教师编号,tname 教师名称-- 3.课程表-t_mysql_course
-- cid 课程编号,cname 课程名称,tid 教师名称-- 4.成绩表-t_mysql_score
-- sid 学生编号,cid 课程编号,score 成绩
CREATE TABLE `t_mysql_student` ( `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号', `sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生名称', `sage` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生年龄', `ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别', PRIMARY KEY (`sid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男');insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男');insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男');insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男');insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女');insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女');insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女');insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女');insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女');insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女');insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女');insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');
CREATE TABLE `t_mysql_score` ( `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号,外键', `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号,外键', `score` float NULL DEFAULT 0 COMMENT '成绩', INDEX `sid`(`sid`) USING BTREE, INDEX `cid`(`cid`) USING BTREE, CONSTRAINT `t_mysql_score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `t_mysql_student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `t_mysql_score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_mysql_course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_score-- ----------------------------INSERT INTO `t_mysql_score` VALUES ('01', '01', 80);INSERT INTO `t_mysql_score` VALUES ('01', '02', 90);INSERT INTO `t_mysql_score` VALUES ('01', '03', 99);INSERT INTO `t_mysql_score` VALUES ('02', '01', 70);INSERT INTO `t_mysql_score` VALUES ('02', '02', 60);INSERT INTO `t_mysql_score` VALUES ('02', '03', 80);INSERT INTO `t_mysql_score` VALUES ('03', '01', 80);INSERT INTO `t_mysql_score` VALUES ('03', '02', 80);INSERT INTO `t_mysql_score` VALUES ('03', '03', 80);INSERT INTO `t_mysql_score` VALUES ('04', '01', 50);INSERT INTO `t_mysql_score` VALUES ('04', '02', 30);INSERT INTO `t_mysql_score` VALUES ('04', '03', 20);INSERT INTO `t_mysql_score` VALUES ('05', '01', 76);INSERT INTO `t_mysql_score` VALUES ('05', '02', 87);INSERT INTO `t_mysql_score` VALUES ('06', '01', 31);INSERT INTO `t_mysql_score` VALUES ('06', '03', 34);INSERT INTO `t_mysql_score` VALUES ('07', '02', 89);INSERT INTO `t_mysql_score` VALUES ('07', '03', 98);
CREATE TABLE `t_mysql_teacher` ( `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号', `tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师名称', PRIMARY KEY (`tid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_teacher-- ----------------------------INSERT INTO `t_mysql_teacher` VALUES ('01', '张三');INSERT INTO `t_mysql_teacher` VALUES ('02', '李四');INSERT INTO `t_mysql_teacher` VALUES ('03', '王五');
CREATE TABLE `t_mysql_course` ( `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号', `cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称', `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号', PRIMARY KEY (`cid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of t_mysql_course-- ----------------------------INSERT INTO `t_mysql_course` VALUES ('01', '语文', '02');INSERT INTO `t_mysql_course` VALUES ('02', '数学', '01');INSERT INTO `t_mysql_course` VALUES ('03', '英语', '03');
分析:分别获取01课程(t1)和02的课程(t2)的成绩表的信息
运行结果:
代码:
SELECTst.*,s1.score '01课程',s2.score '02课程'FROM( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,t_mysql_student st WHEREs1.sid = s2.sid AND s1.sid = st.sid AND s1.score > s2.score
分析:获取01课程(t1)和02的课程(t2)的成绩表的信息
运行结果:
代码:
SELECTt3.* ,(CASE WHEN t1.cid='01' THEN t1.score END) 语文,(CASE WHEN t1.cid='01' THEN t1.score END) 数学FROM( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,-- 有01的sid'( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2, -- 有02的sid't_mysql_student t3 WHEREt1.sid = t2.sid AND t1.sid = t3.sid
分析:先得使用左外连接把01课程保存,在将有02课的保存下来。
运行结果:
代码:
SELECTt1.*,t2.cid 数学 ,t2.score 02成绩FROM( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid
分析:先获取到01课程的学生的sdi,再查询02课程的学生信息,使用not in并且不包含01课程的sid
运行结果:
代码:
SELECT* FROMt_mysql_score WHEREsid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) AND cid = '02'
分析:计算各个学生的平均成绩,即AVG(sc.score),在筛选出大于等于 60 分的同学,即HAVING AVG(sc.score)>=60
运行结果:
代码:
SELECTt3.sid,t3.sname,ROUND( AVG( t1.score ), 2 ) FROM( SELECT * FROM t_mysql_score sc ) t1,t_mysql_student t3 WHEREt1.sid = t3.sid GROUP BYt3.sid,t3.sname
分析:根据成绩表中有sid查询学生表sid
运行结果:
代码:
SELECTt3.*FROM( SELECT * FROM t_mysql_score sc) t1,t_mysql_student t3 where t1.sid = t3.sidGROUP BY t3.sid ,t3.sname
分析:通过左外连接连接学生表,成绩表,再将连表查询后的作为一个新表来查询,再根据sid、sname分组(GROUP BY t1.sid,t1.sname)
运行结果:
代码:
SELECTt1.sid,t1.sname,COUNT( t2.cid ),sum( t2.score ) FROM( SELECT * FROM t_mysql_student ) t1LEFT JOIN ( SELECT * FROM t_mysql_score ) t2 ON t1.sid = t2.sid GROUP BYt1.sid,t1.sname
分析:查询t_mysql_teacher表,筛选name姓李的老师,即(WHERE tname like ‘李%’)使用聚合count(*)
运行结果:
代码:
SELECT * FROM t_mysql_teacher sc WHERE sc.tname like '李%';
分析:先根据「张三」老师的编号获取到教的相应课程,即(t.tid=c.tid)再通过课程的编号获取对应课程的成绩信息,即(c.cid=sc.cid) 再通过对应课程的成绩的学生编号获取对应学生的信息
运行结果:
代码:
SELECTs.*,c.cid,c.cname,t.tnameFROMt_mysql_teacher t,t_mysql_course c,t_mysql_student s,t_mysql_score scWHEREt.tid = c.tid ANDc.cid = sc.cid ANDsc.sid = s.sid ANDt.tname = '张三'GROUP BYs.sid,s.sname,c.cid,c.cname,t.tname
分析:根据学生姓名和学生编号分组(s.sid、s.sname),再筛选出选课总数少于课程总数的学生,即(HAVING COUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course ))
运行结果:
代码:
SELECTs.sid,s.sname,COUNT( sc.score ) FROMt_mysql_score sc,t_mysql_student s WHEREsc.sid = s.sid GROUP BYs.sid,s.sname HAVINGCOUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )
分析:先根据张三老师的编号获取到所教课程的编号,再根据所教课程的课程编号在成绩表中获取到所教学生编号,在学生信息表中筛选出不是张三老师的学生
运行结果:
代码:
SELECTs.snameFROMt_mysql_student sWHEREsid NOT IN (SELECTsc.sid FROMt_mysql_score sc,t_mysql_teacher t,t_mysql_course c WHEREt.tid = c.tid AND c.cid = sc.cid AND t.tname = '张三' GROUP BYsc.sid )
分析:先筛选出各学生不及格的记录,再分组得到存在科目不及格的学生对应科目数,最后得出不及格课程数大于等于二的学生信息即平均成绩
运行结果:
代码:
SELECT s.sid,s.sname,count(sc.score) n,ROUND(AVG(sc.score),2)FROMt_mysql_score sc,t_mysql_student sWHERE sc.sid=s.sidand sc.score<60GROUP BYs.sid,s.snameHAVINGn>=2
分析:先查询出01课程成绩小于60分的学生信息,再通过desc进行降序排序
运行结果:
代码:
SELECT s.*,sc.scoreFROMt_mysql_score sc,t_mysql_student sWHERE sc.sid=s.sidand sc.score<60and sc.cid='01'ORDER BYsc.score desc
分析:计算出所有学生的平均成绩和查询出所有学生所有课程的成绩,通过平均成绩从高到低排序
运行结果:
代码:
SELECTs.sid,s.sname,ROUND(AVG(sc.score),2) avgNum,max( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文,max( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学,max( CASE WHEN sc.cid = '03' THEN sc.score END ) 英语FROMt_mysql_score sc,t_mysql_student sWHEREs.sid=sc.sidGROUP BYs.sid,s.snameORDER BY avgNum desc
分析:使用课程表成绩表,按分数进行分组,算及格人数,中等人数,优良人数,优秀人数
运行结果:
代码:
SELECTc.cid,c.cname,MAX(sc.score) 最高分,min(sc.score) 最低分,ROUND(avg(sc.score),2) 平均分,ROUND(sum(if(sc.score >=60,1,0))/count(sc.score)*100,2) 及格率,ROUND(sum(if(sc.score >=70 and sc.score<80,1,0))/count(sc.score)*100,2) 中等率,ROUND(sum(if(sc.score >=80 and sc.score<90,1,0))/count(sc.score)*100,2) 优良率,ROUND(sum(if(sc.score >=90 and sc.score<=100,1,0))/count(sc.score)*100,2) 优秀率fromt_mysql_score sc,t_mysql_course c,t_mysql_student sWHEREsc.cid=c.cid and sc.sid=s.sidGROUP BY c.cid,c.cname
希望这篇博客能够帮助到正在学习工作的你!!!
来源地址:https://blog.csdn.net/m0_73647713/article/details/131617347
--结束END--
本文标题: MySQL的精选15道面试题(学会了就离入职不远了!!!)
本文链接: https://www.lsjlt.com/news/422509.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0