• 0

  • 484

MySQL面试必会

黑猫

我不是黑客

1星期前

大纲

什么是 SQL?

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

常见的 SQL 分为哪几类?

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

DDL可以做哪些操作?

  • 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。
免责声明:文章版权归原作者所有,其内容与观点不代表Unitimes立场,亦不构成任何投资意见或建议。

信息安全

484

相关文章推荐

未登录头像

暂无评论