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

二，关于行转列的知识

行转列是一种重塑或重新组织数据的操作，将原先以行的形式存储的数据转换为以列的形式存储。行转列的技术可以使用在各种领域，如数据分析、报表生成等。

三.面试题题目+详解

1.建立数据库

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;

2.按要求建立数据表

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

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

分析：分别获取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

2.查询同时存在" 01 "课程和" 02 "课程的情况

分析：获取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

3.查询存在" 01 “课程但可能不存在” 02 (不存在时显示为 null )

分析：先得使用左外连接把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

4.查询不存在" 01 “课程但存在” 02 "课程的情况

分析：先获取到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'

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

分析：计算各个学生的平均成绩，即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

6.查询在t_mysql_score表存在成绩的学生信息

分析：根据成绩表中有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

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

分析：通过左外连接连接学生表，成绩表，再将连表查询后的作为一个新表来查询，再根据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

8..查询「李」姓老师的数量

分析：查询t_mysql_teacher表，筛选name姓李的老师,即（WHERE tname like ‘李%’）使用聚合count（*）

运行结果：

代码:

SELECT * FROM t_mysql_teacher sc WHERE sc.tname like '李%';

9.查询学过「张三」老师授课的同学的信息

分析：先根据「张三」老师的编号获取到教的相应课程,即（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

10. 查询没有学全所有课程的同学的信息

分析：根据学生姓名和学生编号分组（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 )

11.查询没学过"张三"老师讲授的任一门课程的学生姓名

分析：先根据张三老师的编号获取到所教课程的编号，再根据所教课程的课程编号在成绩表中获取到所教学生编号，在学生信息表中筛选出不是张三老师的学生

运行结果：

代码:

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 )

12. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

分析：先筛选出各学生不及格的记录，再分组得到存在科目不及格的学生对应科目数，最后得出不及格课程数大于等于二的学生信息即平均成绩

运行结果：

代码:

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

13. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

分析：先查询出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

14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析：计算出所有学生的平均成绩和查询出所有学生所有课程的成绩，通过平均成绩从高到低排序

运行结果：

代码:

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

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

分析：使用课程表成绩表，按分数进行分组，算及格人数，中等人数，优良人数，优秀人数

运行结果：

代码:

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

希望这篇博客能够帮助到正在学习工作的你！！！