MySQL常见面试题

Java常见面试题_2022最新版

• Java常见面试题_2022最新版持续更新中…

---

---

MySQL官网

MySQL官网

---

一、基本概念了解

什么是数据库

数据库（DataBase）是用来组织、存储和管理数据的仓库。

关系型数据库和非关系型数据库

• 关系型数据库：关系型数据库是一个结构化的数据库，创建在关系模型（二维表格模型）基础上，一般面向于记录。
  • MySQL，Oracle，SQL Server
• 非关系型数据库：NoSQL（NoSQL = Not Only SQL ），意思是“不仅仅是 SQL”，是非关系型数据库的总称。
  • Redis（键值型），MongoDB（文档型），HBase（列存储），Neo4J（图形）

什么是SQL

SQL（英文全称：Structured Query Language）是结构化查询语言，专门用来访问和处理数据库的编程语言。

• SQL 是一门数据库编程语言
• SQL 语言只能在关系型数据库中使用

---

二、常见面试题

数据库SQL语言有哪些

• DQL 数据查询语言
  • SELECT
• DML 数据操纵语言
  • INSERT
  • UPDATE
  • DELETE
• DDL 数据定义语言
  • CREATE
  • DROP
  • ALTER
• DCL 数据控制语言
  • GRANT
  • REVOKE

MySQL常用的搜索引擎

• InnoDB（常用，唯一对事务支持的搜索引擎）
• MyISAM（常用，查询多的时候性能好，ISAM升级版）
• ISAM（不常用）
• MEMORY（不常用）

MySQL，Oracle 默认端口号

• MySQL：3306
• Oracle：1521

SQL中常用的数据类型

• 整型
  • tinyint
  • smallint
  • int
  • bigint
• 浮点型
  • float
  • double
  • decimal
• 字符型
  • char
  • varchar
  • text
  • blob
• 日期型
  • date
  • time
  • datetime
  • timestrap

SQL 中的日期和时间类型

• year
  • 年份
  • YYYY
• date
  • 日历日期（年月日）
  • YYYY-MM-DD
• time
  • 时分秒
  • HH-MM-SS
• datetime
  • 年月日时分秒
  • YYYY-MM-DD HH:MM:SS
• timestamp
  • 混合日期和时间，可作时间戳
  • YYYY-MM-DD HH:MM:SS
  • 可设置默认值
  • timestamp 的数值是与时区相关
    • 以UTC（Universal Time Coordinated）系统协调时区（全时间统一公用的时间）进行存储，以系统当前所在时区进行显示

SQL 中 datetime 和 timestamp 的区别

varchar 与 char 的区别?

• char 是一种固定长度的类型，不足的部分用隐藏空格填充，空间的使用会有浪费
• varchar 则是一种可变长度的类型，节省空间
• char 查询效率比 varchar 高
• 一般定长的数据选用 char 类型，比如身份证号，手机号，电话等，长度变化很大的可以使用 varchar 类型

关联查询，分组，排序，分页，数据库聚合函数

• 关联查询
  • 内联 [inner] join on
  • 外联
    • 左联 left join on
    • 右联 right join on
• 分组 group by
• 排序 order by
  • 默认升序 asc
  • 降序 desc
• 分页 limit
  • limit num1（跳过的条数），num2（每页的条数）
• 聚合函数
  • max（最大值）
  • min（最小值）
  • sum（和）
  • avg（平均数）
  • count（统计个数）

MySQL中都使用过什么函数

• 数字函数
  • abs(x) 绝对值
  • ceil(x) 返回大于或等于x的最大整数
  • round(x) 返回四舍五入的整数
  • floor(x) 返回等于或小于x的最大整数
  • mod(x) 获取 a / b 的余数（如果id需要是偶数或者奇数时就可以使用mod）
• 字符串函数
• 日期函数
• MySQL高级函数

表与表之间的关系

• 一对一（不常用）
  • 一个人 => 一个身份证号
  • 一个人 => 一个国籍
  • 一个学生 => 一个学号
• 一对多/多对一
  • 从班级表的角度看：一个班级 => 多个学生
  • 从学生表的角度看：多个学生 => 一个班级
  • 一对多或者多对一的表关系中，我们会在多的那张表中去维护两张表之间的关系
• 多对多
  • 从学生表的角度看：一个学生 => 多个课程
  • 从课程表的角度看：一个课程 => 多个学生
  • 所以多对多这种关系，我们一般会有一个中间表去维护两张表之间的关系

having 与 where 的区别

• having 是在分组后对数据进行过滤
• where 是在分组前对数据进行过滤
• having 后面可以使用聚合函数
• where 后面不可以使用聚合

查询过程中的书写顺序

select 字段 from 表名 where 查询条件(分组前) group by（含聚合）having 查询条件(分组后排序) order by(排序) limit 分页；
1

查询过程中的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
1

drop，delete，truncate 的区别

• drop 属于DDL语句，直接删掉表、库（删除表、库数据及表结构）无法回滚；
• truncate 是表截断命令，可以将表头和表格数据截断，相当于快速删除全表数据，不能用于删除几行数据，删除数据之后自增的 id 主键从 1 开始。属于DDL语句，不支持事务，不能回退
• delete 可以删除表格中的行或者全部的内容，属于DML语句，支持事务，可以进行事务回滚。

union 与 union all 的区别

• union 和 union all用于将两个或多个select语句的结果作为一个整体显示出来，作用是将多个结果合并在一起显示出来。
• union all：对重复的数不会去重。（对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不进行排序）
• union：对重复的数据会去重。（对两个结果集进行并集操作, 不包括重复行, 同时进行默认规则的排序, 相当于distinct去重处理）

内连接与外连接的区别

• 内连接：[inner] join on 只显示两表共有的数据(交集数据)

  #查找 emp 中拥有部门的所有员工信息(也包含部门信息)
  SELECT * FROM emp e INNER JOIN dept d ON e.dNo = d.deptNo;
  1
  2

• 外连接：

  • 左连接：left join on 显示 join 左边的表的所有数据（不管两个表是否匹配），对于不匹配的部分都用 NULL 表示
  • 右连接：right join on 显示 join 右边的表的所有数据（不管两个表是否匹配），对于不匹配的部分都用 NULL 表示

以谁为主谁中的所有信息都出现

SELECT * FROM emp e LEFT JOIN dept d ON e.dNo = d.deptNo;
SELECT * FROM emp e RIGHT JOIN dept d ON e.dNo = d.deptNo;
1
2

数据库的五大约束

• 主键约束（Primay Key Coustraint） 唯一性，非空性(唯一且非空)
• 唯一约束（Unique Constraint） 唯一性（可以为空，但是只能有一个）
• 外键约束（Foreign Key Counstraint）需要建立两表间的关系，数据库中InnoDB存储引擎支持外键约束，MyISAM不支持外键约束
• 默认约束（Default Coustraint） 该数据的默认值
• 非空约束（Not Null Counstraint） 该字段不能为空
• 检查约束（Check Counstraint）对该列格式和范围的限制，自定义约束，自己决定限制条件

网上有的说数据库的 5 大约束，有的是 6 大约束，MySQL中没有检查约束 的

/*Oracle*/
CREATE TABLE student(
   id int primary key auto_increment,/*主键约束*/
   name varchar(50) not null,/*非空约束*/
   sex varchar(10) default '男',/*默认约束*/
   age number(9),
   check(age>=18 and age<=70)/*检查约束*/
);
/*MySQL*/
CREATE DATABASE IF NOT EXISTS company;
USE company;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS emp;
CREATE TABLE dept(
  did INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',/*主键约束*/
  dname VARCHAR(50) UNIQUE COMMENT '部门名称'/*唯一约束*/
);
CREATE TABLE emp(
  uid INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
  ename VARCHAR(50) NOT NULL COMMENT '员工姓名',/*非空约束*/
  etype VARCHAR(50) DEFAULT '普通员工' NOT NULL COMMENT '员工类型', 
  dept_id INT COMMENT '部门ID',/*默认约束*/
  FOREIGN KEY(dept_id) REFERENCES dept(did)/*外键约束*/
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

什么是事务

事务是数据库操作最小的逻辑工作单元，它包含了一个或多个业务操作，这些操作要么都执行要么都不执行，是不可分割的，一起向系统去提交的。事务常常用来确保数据的一致性。

项目中如何使用事务：

项目中我们通过 @Transactional 注解，这个注解是Spring框架提供的事务管理（声明式事务管理）
通常我们在业务层中，若一个方法中涉及到多个增删改查操作，此时需要在该方法上通过 @Transactional 注解开启事务管理；也可以在业务层接口上添加@Transactional

数据库中如何操作事务

• 开启事务
  • START TRANSACTION
• MySQL数据库中事务默认是开启的
  • 查看事务状态
    • show variables like 'autocommit';
      • on为开启
  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1开启自动提交
• 提交事务
  • COMMIT
• 回滚事务
  • ROLLBACK

事务的四大特性

• 原子性：
  • 事务是数据库的最小的工作单元，事务中包含的各操作要么都做，要么都不做。
• 一致性：
  • 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。当事务执行失败时，所有被该事务影响的数据都应该恢复到事务执行前的状态。
• 持久性：
  • 一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。
• 隔离性：
  • 在事务执行过程中对数据的修改，在事务提交之前对其他事务不可见。接下来的其它操作或故障不应该对其执行结果有任何影响

事务的隔离级别

• READ UNCOMMITTED（读未提交数据）
  • 特点：安全性最差，不能防止任何的并发读问题，但性能最高，不能推荐使用
• READ COMMITTED（读已提交数据）
  • Oracle 默认的隔离级别
  • 特点：安全性比"读未提交"较好，可以防止脏读，但不能防止不可重复度，也不能防止幻读，性能比"读未提交" 差。
• REPEATABLE READ（可重复读）
  • MySQL 默认隔离级别
  • 特点：安全性比"读已提交"较好,可以防止脏读、不可重复读，但不能防止幻读，性能比 “读已提交” 差。
  • 由于 mysql 默认的隔离级别不允许出现脏读和不可重复读，因此要想在 mysql 中演示这两个现象，必须要设置 mysql 的隔离级别为最低级别，也就是 “读未提交”
• SERIALIZABLE（串行化）
  • 特点 : 安全性最好，可以防止脏读，不可重复读，幻读，性能最差，也不推荐使用

什么是视图，游标是什么

• 视图：
  • 是一种虚拟的表，具有和物理表相同的功能，表示一张表的部分数据,或者多张表的综合数据.其结构和数据是建立在对表的查询基础上。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。
  • 视图不存储数据，其中的所有数据均来自于真实表中，实际的数据来源可以来源自多张表，所以若实际的数据发生改变了，则视图也随之发生改变.视图可以嵌套
  • 视图并不能提高查询效率
  • 执行视图就是执行对应写好的SQL语句
• 游标：
  • 是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行， 从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要

创建视图：

create view 视图名 as select查询语句...
create view 视图名(列名1,列名2...) as select查询语句...
1
2

使用视图：

select * from 视图名
1

删除视图：

drop view 视图名
1

用途：

• 筛选表中的行
• 防止未经许可的用户访问敏感数据
• 降低数据库的复杂程度
• 将多个物理数据库/表抽象为一个逻辑数据库/表

什么是简单视图，什么是复杂视图

• 简单视图：一张表中抽出的字段信息创建的是简单视图
• 复杂视图：多张表中抽出的字段信息创建的是复杂视图

数据库如何查询优化

• 给关键字建立索引
  • 经常使用某一个条件，就可以再这个条件上添加索引，比如：
    • 经常通过 username 去查找，就可以再 username 上添加索引
• 使用存储过程,它使 SQL 变得更加灵活和高效
• 备份数据库和清理垃圾数据
  • 短信发送验证码（4-6 位数字），校验功能；有个表会专门存储我们的验证码定时清理（晚上），SSM,springboot 框架都有
• SQL 语句语法的优化
  • 数据库中查询尽量不要写 * 号，查什么字段写什么字段
  • 明确要查询的 sql 中只有一条记录，或者要求我们只有 1 条记录
    • select * from emp limit 1;
  • 尽量不要使用（不建议）使用 or 关键字，容易引起索引失效
• 建立缓存（redis，memcache）
  • 把我们经常使用的数据，不会修改的数据，存放到缓存中，这样会减少数据库的压力

MySQL 和 Oracle 的区别

• MySQL 开源，Oracle 收费
• 端口号
  • MySQL：3306
  • Oracle：1521
• 数据类型
  • MySQL （tinyint，int，bigint，varchar，data(日期)，datetime(时间)，timestamp）
  • Oracle （number，varcher2，nvvarchar2，date(年月日，时分秒)）
• 分页
  • MySQL （limit）
  • Oracle （rownum 伪类）
• 对事务的支持
  • MySQL
    • InnoDB存储引擎支持事务
    • 默认自动提交
  • Oracle

数据库三大范式

• 第一范式
  • 属性不可再分
• 第二范式
  • 在一范式的基础上, 要求数据库表中的每个例或行必须可以被惟一地区分， 通常需要为表加上一个列， 以存储各个实例的惟一标识。 这个惟一属性列被称为主关键字或主键。
• 第三范式
  • 在二范式的基础上, 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1). 每一列只有一个值. 2). 每一行都能区分. 3). 每一个表都不包含其他表已经包含的非主关键字信息

数据库常用对象

• 表
• 字段
• 索引
• 视图
• 存储过程
• 触发器
• 缺省值
• …

MySQL里如何实现远程连接

• 授权法
  • 使用 GRANT 命令，添加新的用户，授予远程访问权限

    grant all privileges on *.* to root @"%" identified by "root";
    flush privileges; # 强制刷新
    1
    2

• 改表法
  • 在 localhost 登入mysql后，更改 mysql 数据库里的 user 表里的 host 项，将 localhost 改称 %

    update user set Host = '%' where Host = 'localhost' and User='root';
    flush privileges;  # 强制刷新
    1
    2

使用过哪些数据连接池？数据库连接池有什么用？

• Druid （我们用的）
• HikariCP （SpringBoot 2.x 默认支持）
• c3p0
• dbcp
• 维护的数据库连接的缓存，以便在将来需要对数据库发出请求时可以重用连接。 连接池用于提高在数据库上执行命令的性能。 保护数据库连接总数，避免连接过载。

---

索引

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
在数据中，索引相当于字典的目录一下，通过索引可以快速的检索数据。

描述下创建索引的几种方式

MySQL 索引的建立对于 MySQL 的高效运行时很重要的，索引可以大大提高 MySQL 的检索速度。
创建索引时，需要确保该索引时应用在 SQL 查询语句的条件（一般作为 where 子句的条件）
创建索引的字段我们建议是唯一，不为空，经常被查询的字段。

• 普通索引：
  • 创建索引
    • CREATE INDEX indexName ON tableName (username);
  • 修改表结构（添加索引）
    • ALTER TABLE tableName ADD INDEX indexName (columnName);
  • 删除索引
    • DROP INDEX indexName ON tableName;
  • 创建表时直接指定

    CREATE TABLE tableName(
           ID INT NOT NULL,
           username VARCHAR(16) NOT NULL,
           INDEX [indexName] (username)
        );
    1
    2
    3
    4
    5

• 唯一索引：
  • 创建索引
    • CREATE UNIQUE INDEX indexName ON tableName(username);
  • 修改表结构
    • ALTER table tableName ADD UNIQUE [indexName] (username);
  • 创建表的时候直接指定

    CREATE TABLE tableName(
           ID INT NOT NULL,
           username VARCHAR(16) NOT NULL,
           UNIQUE [indexName] (username)
        );
    1
    2
    3
    4
    5

索引的分类

• 按字段分类
  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引
  • 全文索引
• 按物理存储分类
  • 聚集索引
  • 非聚集索引
• 按照个数分类
  • 单列索引
  • 联合索引

怎么检查一个SQL是否使用了索引进行检索

explain

谈谈数据库索引的优缺点

索引会大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT，UPDATE，DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存（更新）一下索引文件。

• 优点：
  • （B-Tree 平衡二叉树的结构）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 缺点：
  • 增加了数据库的存储空间
  • 减慢了数据录入的速度

什么样情况，我们考虑给字段添加索引

• 数据量庞大

• 该字段经常作为查询条件

• 该字段很少的DML操作（insert，delete，update），因为操作之后，索引需要重新排序

  建议

  • 建议不要随意添加索引，因为索引是需要维护的，太多的话会降低系统的性能。
  • 建议通过主键查询（默认添加索引）
  • 建议通过unique约束的字段进行查询（默认添加索引）

索引失效的场景、什么情况下会造成索引失效

• 不满足最左匹配原则
• 使用了 select *
• 索引列上有计算
• 索引列上用了函数
• 字段类型不同
• like 左边包含 %
• 列对比
• 使用 or 关键字
• not in 和 not exists
• order by 的坑

---

- [x] 持续更新中…