mysql（下）

前言

       在一个神秘的岛屿上，有一个被称为"数据库之塔"的巨大建筑。据传说，这座塔里隐藏着一份珍贵的宝藏数据，但只有那些能通过MySQL面试的勇士才能找到宝藏的线索。

       主人公小威是一名年轻而聪明的程序员，他对MySQL有着扎实的掌握。当他听说了数据库之塔的传闻后，决定踏上寻宝的冒险之旅......(此处省略一万字)

       在小明离开数据库之塔时，门卫送给了他一本名为《MySQL冒险指南》的书籍，上面写着：“恭喜你成功寻宝，希望这本书能够帮助你在今后的MySQL面试中更加出色！” 小威笑着握住这本书，准备......

正片开始

       前面说到聪明的程序员小威通过数据库之塔时，意外获得了一本《MySQL冒险指南》，之后小威通过数日的阅读从中获取了许多知识，也得到了一些感悟。热心的小威做出了一个伟大的决定！！！那就是把这本书分享给你们（此处应该有掌声🫰）

常见MySQL面试题通关详解

1. 什么是MySQL？它的特点是什么？

       MySQL是一种开源的关系型数据库管理系统，它支持多用户、多线程和多表操作。它具有高性能、可靠性和可扩展性，并且易于使用和管理。总之，MySQL是一款功能强大、性能出色、易于使用和部署的关系型数据库管理系统，适用于各种规模的应用程序和网站。

2. MySQL中的存储引擎有哪些？请列举一些常用的存储引擎。

       MySQL支持多种存储引擎，常见的包括InnoDB、MyISAM、Memory、Archive和CSV等。其中，InnoDB是最常用的存储引擎，它支持事务处理和行级锁定。

3. 在MySQL数据库中MyISAM和InnoDB的区别

在MySQL数据库中，MyISAM和InnoDB是两种不同的存储引擎，它们具有以下区别：

事务支持：MyISAM不支持事务处理，而InnoDB支持事务。事务是一组操作的逻辑单位，可以确保数据的一致性和完整性。

锁定级别：MyISAM使用表级锁定，这意味着当一个操作正在对某个表进行写操作时，其他操作必须等待。而InnoDB使用行级锁定，它可以同时处理多个并发操作，并提供更好的并发性能。

外键约束：MyISAM不支持外键约束，而InnoDB支持。外键约束用于维护表与表之间的关系完整性，以确保引用表中的数据始终存在。

数据缓存：MyISAM将表和索引存储在文件系统中，而InnoDB使用自己的缓冲池来缓存表和索引数据。这使得InnoDB在读取和写入大量数据时具有更好的性能。

崩溃恢复：MyISAM在崩溃后恢复速度较快，因为它具有简单的结构。而InnoDB具有更复杂的结构，因此在崩溃后的恢复过程可能需要更长的时间。

全文搜索：MyISAM支持全文搜索功能，可以快速进行全文索引和搜索操作。而InnoDB在MySQL 5.6版本之前不支持全文搜索，但从MySQL 5.6及以后的版本开始，也提供了全文搜索的支持。

根据具体需求和应用场景，选择适合的存储引擎对于MySQL数据库的性能和功能都非常重要。如果需要事务支持、并发性能好、有外键约束等特性，推荐使用InnoDB。如果对性能要求较高、不需要事务支持和外键约束，并且需要全文搜索功能，那么可以考虑使用MyISAM。

4. Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

SQL标准定义的四个隔离级别为：

read uncommited ：读到未提交数据

read committed：脏读，不可重复读

repeatable read：可重读

serializable ：串行事物

5. CHAR和VARCHAR的区别？

1. CHAR和VARCHAR类型在存储和检索方面有所不同

2. CHAR列长度固定为创建表时声明的长度，长度值范围是1到255

当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。

6. 什么是索引？为什么在数据库中使用索引？

       索引是数据库中用于提高查询效率的数据结构。它类似于书籍中的目录，可以帮助数据库系统快速定位和访问存储在表中的数据。

在数据库中使用索引有以下几个主要原因：

提高查询性能：索引可以加快数据库系统的查询速度，特别是在大型数据库中。通过创建适当的索引，数据库可以直接跳过不需要检查的记录，只需搜索包含所需数据的索引页，从而减少了磁盘I/O操作次数，提高了查询效率。

加速排序和连接操作：索引对于排序和连接操作也非常有用。如果数据库中的表包含大量数据，并且经常需要进行排序或者连接多个表的查询，使用索引可以显著减少这些操作的时间复杂度。

约束数据完整性：索引可以用作唯一性约束，确保表中某个列的值唯一。通过在列上创建唯一索引，系统会自动检查插入或更新操作，防止重复或冲突的数据出现。

优化数据访问路径：索引可以根据查询需求创建多个不同的索引，以改善不同查询的数据访问路径。根据具体的查询模式，使用适当的索引可以使得查询更加高效。

       然而，索引的创建也会带来一些额外的开销。索引需要占用磁盘空间，并且在插入、更新和删除数据时需要维护索引结构，这可能导致写操作的性能稍微降低。因此，在设计数据库时，需要权衡是否创建索引，选择合适的列作为索引，以及根据查询模式和系统需求进行索引的优化和调整。

7. 什么是视图？

 视图 (view) 也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。

       视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

作用：

       视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。

       通过视图，可以消除数据冗余问题。当多个应用程序需要使用相同数据的子集时，可以创建视图来封装这些子集，从而减少数据冗余。此外，由于视图是基于底层表的查询结果生成的，因此视图的数据会随着底层表的数据变化而自动更新，确保数据的一致性。

       总体而言，视图提供了一种灵活且安全的数据访问方式，能够简化复杂查询、提高数据安全性、减少数据冗余，并提供逻辑独立性。这些优点使得视图成为数据库中强大且重要的工具之一。

8. 什么是事务？MySQL如何支持事务处理？

简答：事务是一组数据库操作，要么全部执行成功，要么全部回滚到初始状态。MySQL通过使用ACID（原子性、一致性、隔离性和持久性）属性来支持事务处理。通过使用BEGIN、COMMIT和ROLLBACK语句，可以控制事务的开始、提交和回滚。

详解：

       事务是数据库管理系统中的一个概念，用于管理多个数据库操作作为一个逻辑单位的执行。事务将一组相关的数据库操作（例如插入、更新、删除等）视为一个不可分割的操作序列，要么全部成功执行，要么全部回滚（撤销）到初始状态。

MySQL通过使用ACID属性来支持事务处理：

原子性（Atomicity）：原子性确保事务中的所有操作要么全部执行成功，要么全部取消回滚，不会出现部分操作成功或失败的情况。如果任何一个操作失败，整个事务将被回滚到开始的状态。

一致性（Consistency）：一致性确保在事务开始和结束时，数据库处于一致的状态。这意味着事务操作必须遵循预定义的规则和约束，以保持数据的完整性和有效性。

隔离性（Isolation）：隔离性指在多个并发的事务执行过程中，每个事务都应该与其他事务相互隔离，互不干扰。这样可以防止并发执行的事务之间产生不一致的结果。

持久性（Durability）：持久性确保一旦事务提交成功，其对数据库所做的更改将永久保存，并且在系统崩溃或重启后仍然有效。

MySQL使用InnoDB存储引擎来支持事务处理。InnoDB是一个事务安全的存储引擎，它实现了上述ACID属性。要在MySQL中使用事务，可以通过以下步骤：

启动事务：使用START TRANSACTION语句开始一个新事务。

执行数据库操作：在事务内执行需要进行的数据库操作，如插入、更新、删除等。

提交事务：如果所有操作都成功完成，使用COMMIT语句将事务提交。这将使得所有更改永久保存到数据库中。

回滚事务：如果任何操作失败或出现错误，可以使用ROLLBACK语句将事务回滚到事务开始之前的状态，撤销对数据库的修改。

通过使用事务，MySQL能够确保多个操作按照一致性和可靠性的原则执行，提供了一种可靠的方法来管理和控制数据库中的数据操作序列。

9. 在mysql中什么是连接（JOIN）？

       在MySQL中，连接（JOIN）是指通过共享列或条件将两个或多个表中的行关联起来的操作。连接允许从不同的表中检索相关的数据，并根据一些条件将它们合并到一个结果集中。

连接操作通常使用在SELECT语句中，并且使用ON关键字指定连接条件。连接的类型决定了如何处理没有匹配行的情况。

在MySQL中，常见的连接类型包括：

内连接（INNER JOIN）：内连接返回满足连接条件的行，即只返回两个表中相互匹配的行。

左连接（LEFT JOIN）：左连接返回左表中所有的行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则对应的列将填充为NULL。

右连接（RIGHT JOIN）：右连接返回右表中所有的行，以及左表中与右表匹配的行。如果左表中没有匹配的行，则对应的列将填充为NULL。

全连接（FULL JOIN）：全连接返回两个表中的所有行，无论是否有匹配。如果某个表中没有匹配的行，则对应的列将填充为NULL。

交叉连接（CROSS JOIN）：交叉连接返回两个表的笛卡尔积，即所有可能的组合。它没有连接条件，会生成两个表的所有可能组合。

       连接操作允许在查询结果中获取来自多个表的相关数据，以便进行更复杂的查询和分析。通过连接不同的表，可以根据条件将数据关联起来，并获得更全面和有意义的结果集。

10. 什么是SQL注入？如何防止SQL注入攻击？

       SQL注入是一种常见的安全漏洞，攻击者通过在用户输入中插入恶意SQL代码来获取非法访问权限或破坏数据库。为了防止SQL注入攻击，应该使用参数化查询或预编译语句来过滤用户输入，并对输入进行严格验证和转义。

11.  如何优化MySQL查询性能？

优化MySQL查询性能可以采取多种方法，包括以下几点：

使用合适的索引来加速查询操作。

避免使用全表扫描，只选择需要的列。

优化表结构，合理设计数据库表结构，避免冗余和重复数据。

使用EXPLAIN语句分析查询执行计划，找出潜在的性能问题。

避免频繁的连接和断开数据库连接，使用连接池来管理连接。

配置合适的缓存机制，如查询缓存、结果缓存和对象缓存。

12. 什么是数据库范式？请列举一些常见的数据库范式。

       数据库范式是用于规范化关系型数据库设计的一组规则。它旨在减少数据冗余、提高数据完整性和减少更新异常。范式将数据库表划分为多个更小的关系，并确保每个关系具有清晰的目标和最小的数据重复。

以下是一些常见的数据库范式：

第一范式（1NF）：要求每个列都是原子的，不可再分。每个表中的每个单元格只能包含一个值。

第二范式（2NF）：在1NF的基础上，要求表中的非主键列依赖于整个主键而不是部分主键。

第三范式（3NF）：在2NF的基础上，要求表中的非主键列之间不存在传递依赖关系。即，非主键列不能依赖于其他非主键列。

巴斯-科德范式（BCNF）：在3NF的基础上，要求表中的每个非主键列完全依赖于主键，而不依赖于其他非主键列。

第四范式（4NF）：在BCNF的基础上，要求消除表中的多值依赖。即，表中的任何非主键列都不能依赖于其他非主键列集合的多个值。

第五范式（5NF）：也称为项目-关系范式（PJ/NF），更注重对数据库模式和依赖关系的分析，目标是在表中消除冗余和多值依赖。

每个范式都有其独特的规则和目标，通过逐渐满足更高级别的范式要求，可以提高数据库的数据完整性、减少冗余和更新异常，并确保良好的数据结构设计。但是，需要根据具体业务需求和实际情况来确定使用哪些范式。有时候，在某些情况下牺牲一些范式要求以优化查询性能可能是合理的折衷选择。

13. 如何备份和恢复MySQL数据库？

备份和恢复MySQL数据库可以使用多种方法，包括以下几点：

使用mysqldump命令导出数据库结构和数据。

使用mysqlhotcopy命令进行物理备份。

使用工具，类似Sqlyog、Navicat等导入导出数据

使用LOAD DATA INFILE（OUTFILE）导入导出

14. 在高并发情况下，如何做到安全的修改同一行数据？

       要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案

使用悲观锁

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…

使用乐观锁

乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

15. 在数据库中，TRUNCATE、DELETE和DROP是三种常用的操作，它们的性能特点是什么？

在数据库中，TRUNCATE、DELETE和DROP是三种常用的操作，它们的性能特点如下：

TRUNCATE：TRUNCATE操作用于删除表中的所有数据，但保留表的结构和定义。TRUNCATE比DELETE操作更快，因为它不会逐行删除记录，而是通过释放表的存储空间来实现。TRUNCATE操作是一个DDL（数据定义语言）命令，它会获取并释放表级锁，并将存储空间重新分配给操作系统。

DELETE：DELETE操作用于从表中删除指定的行或满足特定条件的行。DELETE操作相对较慢，因为它需要逐行扫描表并删除匹配的记录。DELETE操作是一个DML（数据操作语言）命令，它会根据所使用的事务隔离级别来获取行级锁。

DROP：DROP操作用于完全删除数据库对象，如表、视图、索引等。DROP操作是最快的操作，因为它直接释放对象的存储空间，并且没有回滚日志和事务管理的开销。DROP操作也是一个DDL命令，它会获取并释放表级锁。

总体而言，TRUNCATE操作是最快的，因为它不需要逐行删除记录，而是通过释放存储空间来完成操作。DELETE操作相对较慢，因为它需要逐行扫描并删除匹配的记录。而DROP操作是最快的，因为它直接释放对象的存储空间。

然而，需要注意的是，这些操作的性能受多种因素影响，如表大小、索引情况、并发访问和系统资源等。在具体场景中，根据需求和实际情况选择适当的操作，并进行性能测试和监测以评估其对系统性能的影响。

       关于视图、索引的详解可进入此篇 MySQL数据库对象与数据备份和还原详解 ，以上问题都是详细的一些解答，可自行概况进行简述🫰

SQL面试题实战

2. 学生管理系统表面试题

1. 表结构要求：

学生表-t_mysql_student：sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

教师表-t_mysql_teacher：tid 教师编号,tname 教师名称

课程表-t_mysql_course：cid 课程编号,cname 课程名称,tid 教师名称

成绩表-t_mysql_score：sid 学生编号,cid 课程编号,score 成绩

2. 表数据

2.1 学生表

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

2.2 教师表

insert into t_mysql_teacher values('01' , '张三');

insert into t_mysql_teacher values('02' , '李四');

insert into t_mysql_teacher values('03' , '王五');

2.3 课程表

insert into t_mysql_course values('01' , '语文' , '02');

insert into t_mysql_course values('02' , '数学' , '01');

insert into t_mysql_course values('03' , '英语' , '03');

2.4 成绩表

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

3. 题目

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

SELECT
    t3.*,
    t1.score 语文,
    t2.score 数学 
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2,
    t_mysql_student t3 
WHERE
    t1.sid = t2.sid 
    AND t1.sid = t3.sid 
    AND t1.score > t2.score;

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

SELECT
    t3.*,
    ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
    ( CASE WHEN t2.cid = '02' THEN t1.score END ) 数学 
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2,
    t_mysql_student t3 
WHERE
    t1.sid = t2.sid 
    AND t1.sid = t3.sid

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

SELECT
    t1.*,
    t2.score 
FROM
    ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1
    LEFT JOIN 
    ( SELECT * FROM t_mysql_score sc2 WHERE sc2.cid = '02' ) t2 
ON t1.sid = t2.sid

04）查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT
    * 
FROM
    t_mysql_score sc 
WHERE
    sc.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) 
    AND sc.cid = '02'

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

SELECT
    s.sid,
    s.sname,
    ROUND( AVG( sc.score ), 2 ) 平均成绩 
FROM
    t_mysql_score sc,
    t_mysql_student s 
WHERE
    sc.sid = s.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    AVG( sc.score ) >= 60

06）查询在t_mysql_score表存在成绩的学生信息

SELECT
    * 
FROM
    t_mysql_student 
WHERE
    sid IN ( SELECT sc.sid FROM t_mysql_score sc GROUP BY sc.sid )

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

SELECT
    s.sid,
    s.sname,
    COUNT( sc.cid ) 选课总数,
    SUM( sc.score ) 总成绩 
FROM
    t_mysql_score sc,
    t_mysql_student s 
WHERE
    sc.sid = s.sid 
GROUP BY
    s.sid,
    s.sname

08）查询「李」姓老师的数量

select COUNT(*) from t_mysql_teacher where tname like '李%'

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

SELECT
    s.* 
FROM
    t_mysql_student s,
    t_mysql_teacher t,
    t_mysql_course c,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
    AND c.cid = sc.cid 
    AND c.tid = t.tid 
    AND t.tname = '张三';

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

SELECT
    s.sid,
    s.sname,
    COUNT( sc.score ) n 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    n < ( SELECT count( 1 ) FROM t_mysql_course )

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

SELECT
    * 
FROM
    t_mysql_student 
WHERE
    sid NOT IN (
SELECT
    sc.sid 
FROM
    t_mysql_score sc,
    t_mysql_course c,
    t_mysql_teacher t 
WHERE
    sc.cid = c.cid 
    AND c.tid = t.tid 
    AND t.tname = '张三' 
    )

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

SELECT
    s.sid,
    s.sname,
    ROUND( AVG( sc.score ), 2 ) 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
    AND sc.score < 60 GROUP BY sc.sid HAVING count( * ) > 1

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

SELECT
    s.*,
    sc.score 
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
    AND sc.cid = '01' 
    AND sc.score < 60 
ORDER BY
sc.score DESC

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

SELECT
    s.*,
    ROUND( AVG( sc.score ), 2 ) descNum,
    IFNULL(max(CASE WHEN sc.cid='01' then sc.score END),0) 语文,
    IFNULL(max(CASE WHEN sc.cid='02' then sc.score END),0) 数学,
    IFNULL(max(CASE WHEN sc.cid='03' then sc.score END),0) 英语
FROM
    t_mysql_student s,
    t_mysql_score sc 
WHERE
    s.sid = sc.sid 
GROUP BY
    s.sid 
ORDER BY
    descNum DESC

15）查询各科成绩最高分、最低分和平均分：

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

SELECT
    sc.cid,
    max( sc.score ) AS 最高分,
    min( sc.score ) AS 最低分,
    ROUND( AVG( sc.score ), 2 ) 平均分,
    count( * ) AS 选修人数,
    CONCAT(ROUND(SUM(if(sc.score>=60,1,0))/COUNT(*)*100,2),'%') 及格率,
    CONCAT(ROUND(SUM(if(sc.score>=70 and sc.score<=80,1,0))/COUNT(*)*100,2),'%') 中等率,
    CONCAT(ROUND(SUM(if(sc.score>=80 and sc.score<=90,1,0))/COUNT(*)*100,2),'%') 优良率,
    CONCAT(ROUND(SUM(if(sc.score>=90,1,0))/COUNT(*)*100,2),'%') 优秀率
FROM
    t_mysql_score sc 
GROUP BY
    sc.cid 
ORDER BY
    count( * ) DESC,
    sc.cid ASC