MySQL面试必会（持续更新）

timevaelesss
1,952 阅读13分钟

大纲

什么是 SQL？

  • Structured Query Language - 结构化查询语言。
  • 用来专门操作关系型数据库的一门语言。

常见的 SQL 分为哪几类？

  • DDL （Data Definition Language）- 操作数据库和表。
  • DML（Data Manipulation Language） - 对表中的数据进行增删改。
  • DQL（Data Query Language）- 查询表中的数据。
  • DCL （Data Control Language） - 定义访问权限和创建用户。

CRUD可以做哪些操作？

  • Create
    • create database hellodb charset utf8;
  • Retrieve
    • use hellodb;
    • select database(); -- 查询当前正在使用哪个数据库
    • show databases;
    • show create database hellodb;
    • information_schema -- 信息数据库，保存的是其他数据库的信息
    • mysql -- 保存的是用户和权限相关的信息
    • performance_schema -- 保存性能相关的数据，监控 mysql 的性能
    • sys -- 记录了DBA所需要的一些信息
  • Update
    • alter database hellodb charset utf8;
  • Delete
    • drop database hellodb;

MySQL 有哪些数据类型？

MySQL 中 char 类型和 varchar类型有什么区别？

  • varchar 是可变长度的，存储字符串时需要多少空间用多少空间。
  • char 是固定长度的，存储字符串时指定了多少长度，创建时就使用多少。

如何查询平均薪资大于 6000 的部门？

  • select dept_name, AVG(salary)
  • from emp
  • where dept_name is not null
  • group by depy_name
  • having AVG(salary) > 6000;

where 和 having 有什么区别？

  • where 是在分组前进行过滤，where 后面不能跟聚合函数。
  • having 是在分组后进行过滤，可以跟聚合函数。

什么是约束？有哪些常见的约束？

  • 约束是指对字段进行限制，来保证插入数据的完整性、有效性、正确性。
  • 主键约束 - primary key
  • 唯一约束 - unique
  • 非空约束 - not null
  • 外键约束 - foregin key

delete 和 truncate 有什么区别，对自增有什么影响？

  • delete 删除表中所有数据，将表中的数据逐条删除。
  • truncate 删除整个表，然后再创建一个结构相同的表。
  • delete 不会影响自增，而 truncate 会使自增重置。

主键约束和唯一约束有什么区别？

  • 主键约束唯一且不能为空
  • 唯一约束唯一但可以为空
  • 一个表中只能有一个主键约束但可以有多个唯一约束

事务是什么？

  • 事务就是一系列 SQL 的集合。
  • 事务中的操作，要么全成功，要么全失败。

事务的四大特性是什么？分别代表什么含义？

  • 原子性 - 每个事务都是一个整体，要么都执行成功，要么都执行失败。
  • 一致性 - 事务执行前后数据库的状态要保持一致，如转账成功后，双方余额都会变化，转账失败后，双方余额都不会变化。
  • 隔离性 - 并发访问数据库时，事务与事务之间不会被互相干扰。
  • 持久性 - 一旦事务执行成功了，对数据的修改是持久的。

事务有哪几种隔离级别？分别解决什么问题？

  • 各个事务之间符合隔离性，相互独立，但是如果多个事务对数据库中的同一批数据进行并发访问的时候，就会引发一些问题，可以通过设置不同的隔离级别来解决对应的问题。
  • 读未提交（read uncommitted）
  • 读已提交（read committed）- 解决脏读问题。
    • 脏读：一个事务读取到另一个事务没有提交的数据。
  • 可重复读（repeatable read）- 解决脏读、不可重复读问题。
    • 不可重复读：一个事务中两次读取的数据不一致。
  • 串行化 - （serializable）- 解决脏读、不可重复读、幻读
    • 幻读：一个事务中，一次查询的结果，无法支撑后续的业务操作。

什么是脏读？

  • 指的是一个事务读取到另一个事务没有提交的数据。
  • 比如事务 A 在对某一行进行修改，还没有提交事务，此时事务 B 能够读到事务 A 修改后的数据，如果事务 A 最终执行了 rollback，那么事务 B 读到的数据就是脏数据。

什么是不可重复读？

  • 指的是一个事务中两次读取的数据不一致。
  • 在一个事务中可能要进行多次查询同一条记录的操作，但这条记录可能在该事务开启期间被其他事务修改了，此时就会造成多次查询的结果是不一致的，这就是不可重复读，带来的问题就是比如每次查询的结果是要给不同的客户端显示，就会造成显示不一致的问题，给用户带来困扰。

什么是幻读？

  • select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，这就是幻读的现象。
  • 一个事务先查询出表中没有该条数据，于是准备插入这条数据，此时事务 B 在 A 插入之前提前插入了这条数据并提交事务了，那么事务 A 在插入该条数据时就会出现数据已存在的现象。

如何给一张表添加外键？

  • 建表原则：在多的一方建立外键，指向一的一方的主键。
  • alter table emp
  • add constraint emp_dept_fk
  • foreign key(dept_id) references department(did);

什么是级联删除？

  • 在删除主表数据的同时，会将与之相关的从表数据删除。
  • on delete cascade

表与表之间存在哪些关系？

  • 一对多，如部门和员工，班级和学生，省和市。
  • 多对多，如用户和角色，角色和权限。
  • 一对一，如身份证和人（可以设计成一张表）。

MyISAM 和 InnoDB 有什么区别？

  • 是否支持行级锁？
    • MyISAM 只支持表级锁。
    • InnoDB 支持行级锁和表级锁，默认行级锁。
  • 是否支持事务和崩溃后的安全恢复？
    • MyISAM 不支持，但执行速度更快，性能更好。
    • InnoDB 提供事务支持，支持回滚、崩溃修复能力。
  • 是否支持 MVCC？
    • MVCC - 应对高并发事务，比单纯的加锁更高效。
    • MyISAM 不支持。
    • InnoDB 在读已提交和可重复读下工作。

在写查询语句时，一般我们需要确定哪些内容？

  • 先确定查几张表 （province & city）
  • 再确定表的连接条件 （p.id = c.pid）
  • 再确定需要用到哪些字段 （p.name, c.name）
  • 最后确认条件信息 （c.name = '芜湖'）
  • 拼凑在一起就得出答案了
    • select p.name, c.name from province p, city c where p.id = c.pid and c.name = '芜湖';

什么是内连接查询？

  • 通过指定的条件，获取两张表的交集。
  • 分为隐式内连接和显式内连接。
    • select p.id, p.name, c.name from province p, city c where p.id = c.pid;
    • select p.id, p.name, c.name from province p inner join city c on p.id = c.pid;

什么是外连接查询？

  • 左外连接: 以左表为基准，匹配右表中的数据，如果匹配上就显式，匹配不上则左表正常显示，右表显示为空。
  • 右外连接：以右表为基准，匹配左表中的数据，如果匹配上就显式，匹配不上则右表正常显示，左表显示为空。
  • 统计每个省下有多少市
    • select p.name, count(c.pid)
    • from province p left join city c
    • on p.id = c.pid
    • group by p.name;

什么是子查询？

  • 一条 select 语句的结果作为另一条 select 的一部分。
  • 分为三类。
    • where 型子查询：子查询的结果作为父查询的比较条件。
    • from 型子查询：子查询的结果作为父查询的一张表。
    • exists 型子查询：子查询作为父查询的 in 筛选条件。
    • 查询是管理员的用户
      • select u.id, u.name from user u where u.id =
      • (select uid from user_role_mapping ur where ur.rid =
      • (select r.id from role r where r.name = '管理员'));

MySQL 分为哪几层？分别作用是什么？

  • Client Connectors 层
    • 负责处理客户端的连接请求，与客户端创建连接。目前 MySQL 几乎支持所有的连接类型，例如常见的 JDBC、Python、Go 等。
  • MySQL Server 层
    • Connection Pool，负责鉴权和存储数据库与客户端创建的连接。
    • Service & Utilities 用来备份恢复、安全管理、做集群管理。
    • SQL Interface，负责接收客户端发送的各种 SQL 语句，比如 DML、DDL 和存储过程等。
    • Parser 解析器会对 SQL 语句进行语法解析生成解析树。
    • Optimizer 查询优化器会根据解析树生成执行计划，并选择合适的索引，然后按照执行计划执行 SQL 语言并与各个存储引擎交互。
    • Caches 缓存包括各个存储引擎的缓存部分，比如：InnoDB 存储的 Buffer Pool、MyISAM 存储引擎的 key buffer 等，Caches 中也会缓存一些权限，也包括一些 Session 级别的缓存。
  • 存储引擎层
    • 存储引擎包括 MyISAM、InnoDB，以及支持归档的 Archive 和内存的 Memory 等。MySQL是插件式的存储引擎，只要正确定义与 MySQL Server 交互的接口，任何引擎都可以访问MySQL，这也是 MySQL 流行的原因之一。
    • 存储引擎底部是物理存储层，是文件的物理存储层，包括二进制日志、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志等。

数据库设计三范式是什么？

  • 指的是数据库的设计规则。
  • 第一范式（1NF）- 列具有原子性，设计列要做到列不可拆分。
  • 第二范式（2NF）- 一张表只能描述一件事情。
  • 第三范式（3NF）- 表中信息如果能被推导出来，就不要设计一个字段来保存它。为的是节省空间。

什么是反三范式？

  • 就是通过增加冗余的数据，来提高数据库读性能。（以空间换时间）
  • 如订单表中也有 name 字段，可以避免连表查询，提高性能。

什么是索引？常见的索引有哪些？ 索引适合添加在哪些字段上？索引有哪些问题？

  • 相当于字典的目录，提高查询速度。
  • 常见的索引：主键索引（primary key）、唯一索引（unique）、普通索引（index）
  • 索引适合添加在 where 查询条件、ASC/DESC排序、GROUP BY 分组后面的字段。
  • 索引被保存在一个索引文件（.ibd）中，虽然能大幅提升查询性能，但如果对数据进行增删改操作，还需要额外维护这个索引文件，当数据库数据量越大，索引占用的磁盘空间也越大，维护所需时间也越长。

什么是视图？视图有什么用？视图和表有什么区别？

  • 视图就是由查询结果形成的一张虚拟的表。操作视图就相当于操作一张只读表。
  • 如果某个查询的结果使用十分频繁，并且查询语法比较复杂，这时就可以根据这条查询语句构建一张视图，方便查询。
  • 视图是建立在表的基础上，视图是只读，视图主要是用来简化查询，一般只用来进行 select 操作。

什么是存储过程？你有用到存储过程吗？

  • Java 里有方法，存储过程就是一种SQL 层面上的方法，可以通过传参的形式调用。
  • 就是一堆 SQL 集合，中间加了一些逻辑控制。
  • 存储过程是经过编译后直接存储在数据库当中的，一旦调用存储过程，就直接在数据库中去执行了，可以减少业务系统与数据库的交互延迟。
  • 在业务中禁止使用存储过程，因为难以调试和扩展，更没有移植性。

什么是触发器？

  • 是一种存储过程，只不过是由事件来触发的。
  • 就是一种钩子函数，在执行 insert、delete、update 语句前后会去执行它。
  • 比如说订单表里记录加 1，那么库存表的库存记录就要减 1。

什么是 JDBC？

  • 是Java 访问数据库的标准规范。
  • 可以通过统一方式操作所有关系型数据库。
  • JDBC 是一套接口，具体的实现类由各数据库厂商提供，具体的实现类封装成 jar 包，这个 jar 包就是数据库驱动。

JDBC是怎么与数据库连接的？

  • 首先获取 Connection 对象，用于和数据库建立连接管道。
  • 然后获取 Statement 对象，负责执行 SQL 语句。
  • 最后获取 ResultSet 对象，用于接收查询的结果。
  • 关闭流时，以上三个对象都要进行关闭。

什么是 SQL注入？怎么防止 SQL 注入？

  • SQL 注入就是用户将 SQL 的代码伪装在参数当中，然后一起发送给服务端，服务端就会带着 SQL 代码一起拼接和解析，进而查出数据库的额外信息。
  • 比如用户在登录我的系统的时候，在用户名后面加上 '#，如果服务端没有防范 SQL 注入，就会把它解析成注释，进而不会去验证密码，直接跳过验证进入系统。
  • 可以通过 preStatement 进行预编译处理，通过占位符的形式设置参数，来防止 SQL 注入。

JDBC 中怎么开启事务？

  • 通过 connection.setAutoCommit(false) 来开启事务。
  • 通过 connection.commit() 来提交事务。
  • 通过 connection.rollback() 来回滚事务。

你工作中使用过哪些数据库连接池？都需要进行哪些配置？

  • Java 为数据库连接池提供了公共的接口 javax.sql.DataSource，各个厂商来实现这个接口，以下三种连接池方式都是实现了 DataSource 接口。
  • DBCP
  • C3P0 - c3p0-config.xml
  • Druid - druid.properties 需要自己加载
  • 都必须配置用户名、密码、连接串、驱动名
  • 还可以配置初始化连接数、最大连接数、最小连接数、最大空闲连接数等。

你用过 DBUtils 吗？查询用过哪些 Handler？

  • DBUtils 是 apache 为了简化连接池操作，而封装的一个工具类。
  • ArrayHandler - 将查询结果的第一条封装为数组。
  • ArrayListHandler - 将查询结果集封装为列表，列表每一项是数组。
  • BeanHandler - 将查询结果的第一条封装为 Bean 对象。
  • BeanListHandler - 将查询结果的每一条都封装为 Bean 对象，并保存到列表中。
  • MapHandler - 将查询结果的第一条封装为 Map 对象。
  • MapListHandler - 将查询结果的每一条都封装为 Map 对象，并保存到列表中
  • ScalarHandler - 统计用

什么是批处理？

  • 就是一次操作中执行多条 SQL 语句，而不是一次只执行一条语句。
  • addBatch 先收集多条 SQL 语句，executeBatch 统一执行这些 SQL 语句。
  • 适合在批量插入或更新数据的场景。

什么是元数据？

  • 包含三部分。
  • 查询结果信息 - UPDATE或 DELETE 受影响的记录数。
  • 数据库和数据表信息 - 在 information_schema 数据库的 TABLE 表里。
  • MySQL服务器信息

xml和 html 有什么区别？

  • xml 的标签可以自定义，html 都是固定的。
  • xml 的语法比 html 更严格。
  • xml 原先是 W3C 用来顶替 html 的，但是 html 太流行了，所以 xml 后来主要用来做配置文件。

xml 如何进行约束？

  • DTD（Data Type Defined）- 只能约束结构
  • Schema - 可以约束结构和取值范围

如何解析 xml？

  • DOM 方式 - 将整个 XML 读取到内存生产一个 DOM 树。占用内存大，可读写。
  • SAX 方式 - 一边扫描一边解析。占用内存少，速度快，只读。
  • XPath - 按照路径的方式。

Java 中一对多怎么表示？

  • 在多的一方 Java 类里添加一的一方的成员变量。

Java 中多对多怎么表示？

  • 先生成一个中间表的 entity 类。

  • 让多对多的双方分别拥有中间表 entity 类的列表。

avatar
文章
阅读
粉丝