MySQL面试题

一、DQL

1. 用一条SQL从这个stuCourse表获得这样结果

 

 

　　分别查出文理科的平均分，再将两个结果左连，只查需要的字段

1 SELECT
2     wen.`学号`,
3     wen.`文-平均分[语文]`,
4     li.`理-平均分[数学、物理]` 
5 FROM
6     ( SELECT `NAME` AS '学号', AVG( score ) AS '文-平均分[语文]' FROM `courses` WHERE course = '语文' GROUP BY `NAME` ) wen
7     LEFT JOIN 
8     ( SELECT `NAME` AS '学号', AVG( score ) AS '理-平均分[数学、物理]' FROM courses WHERE course IN ( '数学', '物理' ) GROUP BY `NAME` ) li 
9     ON wen.学号 = li.学号;

2. MySQL查询优化有哪些？

  1. 尽量不用select *
  2. 减少子查询，使用关联查询（left  join，right  join，inner  join）。
  3. 子查询时减少使用IN，NOT IN，使用使用exists，not exists或者关联查询语句替代。原因：使用IN不会用到索引，内外表进行全表扫描。
  4. or的查询尽量用union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)。
  5. 尽量不用！=，<>操作符，否则引擎将放弃使用索引，而进行全表扫描。
  6. Limit基数大时，使用Between，例如：limit  10000,10改为between  10000 and  10010
  7. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

二、DML

1. truncate drop delete区别

① 从执行速度来说

drop>truncate>delete

② 从原理上来说...哎呀太多了，参考这个博主

、数据库设计

1. 数据库的三大范式

第一范式：列（字段）不可再分。

第二范式：在满足1NF的基础上，确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式：在满足2NF的基础上，确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

 

、索引

1. 什么是索引？

  • 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
  • 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

2. 索引类型？

  • 普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。
  • 唯一索引：唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
  • 组合索引：组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
  • 全文索引：全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

3. 什么时候不要使用索引？

  • 经常增删改的列不建立索引
  • 有大量重复的列不建立索引
  • 表记录太少的不建立索引

4. 使用MySQL的索引应该注意什么？

  • 避免在Where子句中使用!=、<>或OR，否则索引无效将全表扫描。优化器将无法通过索引来确定将要命中的行数因此需要搜索该表的所有行。
  • 避免在Where子句中对字段进行表达式或函数操作，这会导致放弃索引而使用全表扫描。
  • 避免在Where子句中的 = 左边进行函数、算术运行或其他表达式运算，否则系统将可能无法正确使用索引。
  • 复合索引遵循最左前缀原则。
  • 如果MySQL评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
  • 列字段类型是字符串类型，查询时一定要给值加引号，否则索引失效。
  • 模糊LIKE查询，%不能在前。如果需要模糊匹配，可以使用全文索引。
  • 表字段为NULL也是不可以使用索引的。

 

、事务

1. 什么是事务？

多条sql语句，要么全部成功，要么全部失败。

事务的特性：

原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰。

持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

、其他

1. MySQL数据库引擎有哪些

命令行查看：show engines；

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎。

Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些。

Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表。

MERGE：是一组MYISAM表的组合。

2. 说说InnoDB与MyISAM的区别

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
  2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
  3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

 

posted @ 2021-10-21 15:58  E_lian  阅读(49)  评论(0)  编辑  收藏  举报