MySQL的面试题讲解看完肯定对你有帮助！！(精选24道)

2023-09-16 12:20

短信预约 -IT技能 免费直播动态提醒

一、理论方面

1.InnoDB存储引擎和MyISAM的区别

InnoDB和MyISAM是MySQL数据库常见的两种存储引擎，它们在功能和性能方面有一些重要区别：

        1.事务支持：InnoDB是一个支持事务处理的存储引擎，它使用了ACID（原子性、一致性、隔离性和持久性）特性来确保数据的完整性。而MyISAM不支持事务，它执行的是自动提交模式，即每个SQL语句都被视为一个独立的事务。

        2.锁定粒度：InnoDB使用行级锁定（row-level locking），这意味着在并发操作时只锁定所需的行，从而提高了多用户并发处理的效率。MyISAM使用表级锁定（table-level locking），当一个用户在对某个表执行写操作时，其他用户不能同时对该表进行写操作，这可能导致并发性能下降。

        3.外键支持：InnoDB支持外键约束，可以确保关联表之间的数据完整性。MyISAM不支持外键约束，只能通过应用程序层面来维护数据一致性。

        4.崩溃恢复：InnoDB具有崩溃恢复能力，它会在数据库发生异常崩溃时恢复数据的一致性。MyISAM在崩溃发生时不提供数据恢复功能，可能导致数据损坏或不一致。

        5.全文索引：MyISAM支持全文索引，可以进行高效的全文搜索。而InnoDB在MySQL 5.6版本之前不支持全文索引，但在之后的版本中添加了全文搜索功能。

总体上说，如果你需要事务支持、并发性能较好、数据一致性要求较高或需要使用外键约束，那么选用InnoDB存储引擎会更合适。而如果你对并发性能要求不高、只需简单的读写操作并且对数据一致性要求不高，那么使用MyISAM存储引擎可能会更简单和高效。但是需要注意的是，根据具体的应用场景和需求，选择正确的存储引擎是非常重要的。

2.DELETE、DROP、TRUNCATE三者的区别

DELETE、DROP和TRUNCATE是SQL语句中用于删除数据和对象的操作，它们之间有一些重要的区别：

        1.DELETE：DELETE语句用于从表中删除满足条件的数据行。它是一个事务操作，可以使用WHERE子句指定删除的条件。DELETE语句执行会触发表上的触发器，并且可以配合使用ROLLBACK命令撤销删除操作。DELETE语句只删除表中的数据，而不会删除表本身。

         2.DROP：DROP语句用于删除数据库对象，可以是表、索引、视图、存储过程等。DROP TABLE语句可以删除整个表及其相关的索引、触发器、约束等。DROP语句是一个DDL（数据定义语言）操作，它会立即删除对象，且无法恢复。DROP语句一般不回滚，一旦执行成功，相关对象将永久删除。

        3.TRUNCATE：TRUNCATE语句用于从表中删除所有数据，但保留表的结构。它是一个DDL操作，比DELETE更快，因为它仅仅是删除数据页的引用，而不是逐行删除数据。TRUNCATE语句不会触发表上的触发器，也无法撤销。TRUNCATE语句在执行成功后，表的计数器会被重置，自动增量插入的计数器也会被重置。

总结起来，DELETE用于删除表中的特定行数据，DROP用于删除数据库对象，TRUNCATE用于删除表中的所有数据。DELETE是一个DML（数据操作语言）操作，DROP和TRUNCATE是DDL操作。DELETE语句可以撤销，而DROP和TRUNCATE删除的数据或对象无法恢复。根据需要选择合适的语句来执行删除操作。

3.行转列的使用

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

举个例子：

科目/学员 001 002 003 ....
科目一 90 89 99 ...
科目二 90 0 100 ...
从上面这样变成下面这样
学员/科目 科目一 科目二
001 90 90
002 89 0
003 99 100
... ... ...
后面的方式是不是更为简便，这就是“行转列”。

那么在MySQL是怎么使用的呢？我们一起来看看👇👇👇

SELECTt1.sid,t1.cid  '"01"课程',t2.cid  '"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--以上是利用别名的方式，但是不符合规范，最好采用下面的方式SELECTt1.sid,(CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',(CASE WHEN t2.cid = '02' THEN t2.score END ) '"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.什么是主键和外键？

主键是标识数据库表中唯一记录的列，用于确保数据的唯一性。外键是用于建立表与表之间关系的列，它引用了另一个表的主键。

5.什么是事务？

在数据库中，事务是一组操作的逻辑单元，要么全部执行，要么全部回滚。事务具有ACID属性：原子性、一致性、隔离性和持久性。

原子性(Atomicity)：事务是一个原子性质的操作单元，事务里面的对数据库的操作要么都执行，要么都不执行。

一致性(Consistency)：在事务开始之前和完成之后，数据都必须保持一致状态，必须保证数据库的完整性。也就是说，数据必须符合数据库的规则。

隔离性(Isolation）:一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(Durability）:持久性也成为永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久的。

6.什么是SQL注入？

SQL注入是一种常见的安全漏洞，攻击者通过在用户输入中插入恶意的SQL代码，来执行未经授权的数据库操作。为了防止SQL注入攻击，应该使用参数化查询或预编译语句来过滤和转义用户输入。

7.连表查询的使用

在MySQL中，使用JOIN语句可以实现连表查询，它允许你从多个表中获取相关联的数据。以下是常见的连表查询语句：

内连接（INNER JOIN）：返回两个或多个表中满足连接条件的行。
SELECT *FROM table1JOIN table2 ON table1.column = table2.column;
左连接（LEFT JOIN）：返回左表中的所有行，以及右表中满足连接条件的行。
SELECT *FROM table1LEFT JOIN table2 ON table1.column = table2.column;
右连接（RIGHT JOIN）：返回右表中的所有行，以及左表中满足连接条件的行。
SELECT *FROM table1RIGHT JOIN table2 ON table1.column = table2.column;
全连接（FULL OUTER JOIN）：返回两个表中的所有行，无论是否满足连接条件。
SELECT *FROM table1FULL OUTER JOIN table2 ON table1.column = table2.column;
自连接（SELF JOIN）：将表视为两个独立的实体，通过连接条件关联同一个表中的不同行。
SELECT *FROM table1 AS t1JOIN table1 AS t2 ON t1.column = t2.column;
连接条件可以基于一列或多列的相等性，你可以根据实际需求调整连接条件。使用连表查询可以从多个表中获取相关的数据，以便进行更复杂的数据分析和处理。在编写连表查询时，请确保表之间的连接条件是正确的，并仔细考虑查询的性能和效率。

8.聚合函数的使用及注意事项

在MySQL中，聚合函数用于对数据进行聚合计算，返回一个单一的结果。以下是一些常见的聚合函数及其使用方法：

COUNT：计算给定列或表中的行数。
SELECT COUNT(column_name) FROM table_name;SELECT COUNT(*) FROM table_name; -- 计算表中的总行数
SUM：计算给定列的总和。
SELECT SUM(column_name) FROM table_name;
AVG：计算给定列的平均值。
SELECT AVG(column_name) FROM table_name;
MAX：返回给定列中的最大值。
SELECT MAX(column_name) FROM table_name;
MIN：返回给定列中的最小值。
SELECT MIN(column_name) FROM table_name;
这些聚合函数可以与其他SQL语句（如SELECT、WHERE、GROUP BY等）一起使用，以实现对数据的聚合计算和筛选。在使用聚合函数时，可以根据需要进行分组（使用GROUP BY子句）或过滤（使用HAVING子句）。此外，聚合函数还可以嵌套使用，以进行更复杂的计算。

注意事项:

聚合函数通常忽略NULL值，除非使用特定的修饰符（如COUNT(*)）来计算行数。如果要使用多个聚合函数，可以将它们作为同一SELECT语句的不同部分进行计算。

9.GROUP BY子句使用及注意事项

在MySQL中，GROUP BY子句用于将结果集按照一个或多个列进行分组，并对每个组进行聚合计算。以下是GROUP BY子句的使用及注意事项：

使用格式：
SELECT column1, column2, ..., aggregate_function(column)FROM table_nameGROUP BY column1, column2, ...;
注意事项:

分组列：GROUP BY子句中指定的列将用于分组数据。你可以指定一个或多个列作为分组条件，列的顺序会影响分组的结果。注意，SELECT列表中的非聚合列（未使用聚合函数的列）必须出现在GROUP BY子句中。
聚合函数：在SELECT列表中使用聚合函数进行计算，如SUM、AVG、COUNT等。聚合函数会对每个分组的数据进行计算，并返回结果集中的一个值。
结果集：GROUP BY子句将返回每个分组的结果集。它会将每个分组的聚合计算结果与分组列一起展示。

10.HAVING子句使用及注意事项

在MySQL中，HAVING子句与GROUP BY子句一起使用，用于对分组后的结果进行筛选。它允许在分组计算后对分组结果进行过滤，并返回满足特定条件的分组。

使用格式：
SELECT column1, column2, ..., aggregate_function(column)FROM table_nameGROUP BY column1, column2, ...HAVING condition;
注意事项:

HAVING条件：HAVING子句用于指定条件，过滤满足特定条件的分组。你可以在HAVING子句中使用聚合函数、列和运算符来构造条件表达式。HAVING条件将在分组计算后进行筛选，仅返回满足条件的分组。
聚合函数：在SELECT列表中使用聚合函数进行计算，如SUM、AVG、COUNT等。HAVING子句中可以使用这些聚合函数。
HAVING vs WHERE：HAVING子句用于筛选分组结果集，而WHERE子句用于筛选行。因此，HAVING子句只能在包含GROUP BY子句的查询中使用。

以下是一个示例，展示了如何使用HAVING子句筛选出销售额超过1000的部门：
SELECT department, SUM(sales) AS total_salesFROM sales_tableGROUP BY departmentHAVING total_sales > 1000;
在上述查询中，首先按部门进行了分组，并计算每个部门的销售总额。然后，HAVING子句筛选出总销售额超过1000的部门。

使用HAVING子句时，注意事项:

HAVING子句只能在包含GROUP BY子句的查询中使用。
HAVING子句可以使用聚合函数、列和运算符来构造条件表达式。
可以使用AND、OR、NOT等逻辑运算符连接多个条件，并构建复杂的筛选条件。
HAVING子句中的条件可以引用SELECT列表中的别名。
注意HAVING条件的位置和顺序，它应该出现在GROUP BY子句之后。

通过使用HAVING子句，你可以对分组计算后的结果进行进一步的筛选和条件过滤，以得到满足特定条件的分组。这对于数据分析和统计非常有用。

二、技术方面(MySQL面试真题)

数据库脚本

一、表结构要求：

-- 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 成绩

二、表数据：

-- 学生表
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' , '女');

-- 教师表
insert into t_mysql_teacher values('01' , '张三');
insert into t_mysql_teacher values('02' , '李四');
insert into t_mysql_teacher values('03' , '王五');

-- 课程表
insert into t_mysql_course values('01' , '语文' , '02');
insert into t_mysql_course values('02' , '数学' , '01');
insert into t_mysql_course values('03' , '英语' , '03');

-- 成绩表
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);

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

SQl语句编写：

SELECTt3.*,( CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',( CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"课程' FROM( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,t_mysql_student t3 WHEREt1.sid = t2.sid AND t1.score > t2.score AND t1.sid = t3.sid

crud操作结果：

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

SQl语句编写：

SELECTt3.*,( CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',( CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"课程' FROM( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,t_mysql_student t3 WHEREt1.sid = t2.sid AND t1.sid = t3.sid

crud操作结果：

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

SQl语句编写：

SELECTt1.sid,(CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',(CASE WHEN t2.cid = '02' THEN t2.score END ) '"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

crud操作结果：

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

SQl语句编写：

SELECT* FROMt_mysql_score WHEREsid IN ( SELECT sid FROM t_mysql_student WHERE sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) ) AND cid = '02'

crud操作结果：

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

SQl语句编写：

SELECTstu.sid,stu.sname,ROUND( AVG( sc.score ), 2 ) '平均成绩'FROMt_mysql_score sc,t_mysql_student stu WHEREsc.sid = stu.sid GROUP BYsid,sname HAVINGAVG( sc.score ) >= 60

crud操作结果：

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

SQl语句编写：
SELECT* FROMt_mysql_student WHEREsid IN ( SELECT sid FROM t_mysql_score GROUP BY sid )
crud操作结果：

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

SQl语句编写：

SELECT*FROM( SELECT * FROM t_mysql_student ) t1LEFT JOIN ( SELECT sid, COUNT( cid ) '选课总数', sum( score ) '所有课程的总成绩' FROM t_mysql_score GROUP BY sid ) t2 ON t1.sid = t2.sid

crud操作结果：

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

SQl语句编写：
SELECT COUNT(*) '李姓老师的数量' from t_mysql_teacher where tname LIKE '李%'
crud操作结果：

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

SQl语句编写：

SELECT* FROMt_mysql_student WHEREsid IN (SELECTsid FROMt_mysql_score WHEREcid =  ( SELECT cid FROM t_mysql_course WHERE tid = ( SELECT tid FROM t_mysql_teacher WHERE tname = '张三' ) ) )

crud操作结果：

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

SQl语句编写：

SELECTs.* FROMt_mysql_student sLEFT JOIN ( SELECT sid, COUNT( DISTINCT cid ) AS course_count FROM t_mysql_score GROUP BY sid ) sc ON s.sid = sc.sid WHEREsc.course_count < ( SELECT COUNT( * ) FROM t_mysql_course );

crud操作结果：

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

SQl语句编写：

SELECT* FROMt_mysql_student WHEREsid NOT IN (SELECTs.sid FROMt_mysql_score sc,t_mysql_teacher t,t_mysql_course c,t_mysql_student s WHEREc.cid = sc.cid AND c.tid = t.tid AND sc.sid = s.sid AND t.tname = '张三' )

crud操作结果：

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

SQl语句编写：

SELECTs.sid,s.sname,COUNT( * ) 不及格课程数,ROUND( AVG( sc.score ), 2 ) 平均成绩 FROMt_mysql_score sc,t_mysql_student s WHEREsc.sid = s.sid AND sc.score < 60 GROUP BY s.sid, s.sname HAVING 不及格课程数 >=2

crud操作结果：

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

SQl语句编写：

SELECTs.*,sc.score FROMt_mysql_score sc,t_mysql_student s WHEREsc.sid = s.sid AND sc.score < 60 AND sc.cid = '01' ORDER BYsc.score DESC

crud操作结果：

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

SQl语句编写：

SELECTs.sid,s.sname,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 ) 英语,ROUND( AVG( sc.score ), 2 ) 平均成绩 FROMt_mysql_student s,t_mysql_score sc WHEREs.sid = sc.sid GROUP BYs.sid,s.sname ORDER BY平均成绩 DESC

crud操作结果：

查询各科成绩最高分、最低分和平均分
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SQl语句编写：

SELECTc.cid,c.cname,max( sc.score ) 最高分,min( sc.score ) 最低分,ROUND( AVG( sc.score ), 2 ) 平均分,CONCAT(ROUND(sum( IF ( sc.score >= 60, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 及格率,CONCAT(ROUND(sum( IF ( sc.score >= 70 AND sc.score < 80, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 中等率,CONCAT(ROUND(sum( IF ( sc.score >= 80 AND sc.score < 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优良率,CONCAT(ROUND(sum( IF ( sc.score >= 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优秀率,COUNT( sc.score ) 选修人数 FROMt_mysql_score sc,t_mysql_course c WHEREsc.cid = c.cid GROUP BYsc.cid ORDER BY选修人数 DESC,c.cid ASC;

crud操作结果：