面试过程中的一些SQL Q&A

有关SQL在面试过程中的一些问题,SQL/数据库是任何与计算机科学相关的学术或学位课程的重要组成部分,这也是事实。

因此,大多数工程师确实对SQL概念有基本的了解,但是他们需要专注于面试官最有可能提出问题的SQL领域。因此,我们想吸引您的重点是使您在SQL面试中取得成功的相关和必要条件。

除了面试外,前提需要对SQL架构有一个整体的认证,《MySQL实战45讲》的基础篇架构、日志、事务、索引、锁基本概念,SQL实践篇的常见问题。

SQL面试的主要问题1

主键、普通索引、唯一索引、外键区别?

以Mysql为例,Mysql中的表记录信息是以及索引存储的,同时索引是以B+树数据结构组织存储的,需要注意索引大小、维护开销;

  1. 主键:单张表只能有一个,不能为NULL,通常是int或bigint类型且自增的,InnoDB中的主键是聚簇索引(行内容在索引节点上),如果没有显示声明Mysql也会自动创建一个主键索引;不同行的主键索引不能相同,主键可以作为其他表的外键关联,表示表的一个行记录;
  2. 普通索引:单张表可以指定多个普通索引,普通索引的列允许重复的元素(注意可能不符合最佳原则),允许为NULL,可以是任意数据类型(数值、日期、字符串)
  3. 唯一索引:单张表中可以有多个唯一索引,唯一索引的列不同行的数据都是唯一的,唯一约束可能为NULL(多行相同);
  4. 外键:
    • 外键是表中的一个字段,该字段是另一个表的主键,用于标识另一个表的独立一行的记录;
    • 一个表可以有多个外键;
    • 外键不会创建聚簇或非聚簇索引,需要自己手动加索引;

JOIN的连接类型与差异?

JOIN关键字用于关联多张表的结果数据,返回包含连接表中至少一个匹配项的行,连接类型有内连接、左连接、右连接、完全连接、交叉连接几类:

  1. 内连接(简单连接):SELECT * FROM tb1 INNER JOIN tb2 USING(c);
    • 两个表使用指定列连接,仅返回两个表都存在匹配的记录
    • 没有指定INNER JOIN也是按这个模式执行
  2. 左连接(也叫左外连接): SELECT * FROM tb1 LEFT JOIN tb2 USING(c);
    • 两个表使用指定列连接,返回左表匹配的记录,即使右表连接的条件不满足;
    • 右表若不满足连接条件,返回为NULL;
  3. 右连接(也叫右外连接): SELECT * FROM tb1 RIGHT JOIN tb2 USING(c);
    • 返回右表和正确匹配左表的行记录,与LEFT JOIN相反的操作;
    • 引擎内部将右连接,转成左连接操作;
  4. 全连接(也叫全外连接): SELECT * FROM tb1 FULL JOIN tb2 USING(c)
    • 返回左、右表匹配查询结果的组合,即等同于执行UNION两个查询的结果;
    • 基于ON tb1.c=tb2.c的查询会报错;
  5. 交叉连接: SELECT * FROM tb1 CROSS JOIN tb2;
    • 如果不带WHERE条件,则返回两个表的笛卡尔积效果;
    • WHERE条件,返回两个表的INNER JOIN效果;
  6. 自连接:SELECT * FROM tb1, tb2 WHERE tb1.c=tb2.c;
    • 类似于INNER JOIN效果,连接部分需要在WHERE条件中指定,使用SELECT * FROM tb1,tb2 USING(c)类似的SQL会解析错误;
  • Tips:
    • 不加条件的内连接、自连接、全连接、交叉连接返回结果一致,左连接和右连接不加条件会报错;
    • USING(c)可以用类似ON tb1.x=tb2.y模式替换,两者差别是USING(c)将c列公共出来了;
    • 尽量采用INNERT JOINLEFT JOIN表示,语义简单明了;

设计规范化问题?

满足遵循第三范式即可,但设计数据库表字段最大程度的减少数据冗余;

  • 第一范式(1NF)
    • 表的属性(列)不能包含多个值,应当是保证原子值;
    • EG: 比如用户有多个手机号码,我们通过,分隔符连接(不符合字段的原子性),即不符合1NF,我们应该新增一条用户记录与该手机号对应;
    • 1NF的问题: 为了保证字段原子值(用户名、地址、手机号),用户基础数据有冗余(用户名、地址等信息)
  • 第二范式(2NF):
    • 表符合第1NF,并且表的非素数属性(与对象易于关联的属性,比如人和年龄、姓名、性别等),不依赖于其他候选键的子集
    • EG:比如{教师ID, 年龄, 课程},其中年龄是非素数属性,但依赖{教师ID, 课程}候选键,因此不符合第二范式,我们需要将非质数属性拆分成独立的教师表来满足第二范式;
  • 第三范式(3NF)
    • 表符合第2NF,并且表中不能含多个功能依赖,不能存在功能依赖传递
    • EG:比如{教师ID, 姓名, 年龄, 户籍ID, 省份, 城市},其中{姓名, 年龄}依赖教师ID,{省份, 城市}依赖户籍ID,{姓名, 年龄, 户籍ID}依赖教师ID,存在多个功能的依赖,同时存在功能传递依赖,因此不符合第三范式;我们可以拆成{教师ID, 姓名, 年龄, 户籍ID}和{教师ID, 户籍ID, 省份, 城市}两个表来符合第三范式
    • 简单的理解,第三范式对应OOP的的单一职责,即一个表不应该包含多个不同实体对象的属性信息;
  • BCNF
    • 表满足第3NF,并且每个每个表的主属性是唯一的
    • EG:按上述例子,后一个表中的{教师ID, 户籍ID, 省份, 城市},应该改为{户籍ID, 省份, 城市}来满足BCNF

SuperKey和候选键的区域?

  • 超级键是列的组合,可以唯一标识表中的任何行。主键也是超级键。
  • 候选键是一个超级键,包含可唯一标识行的最少列的超级键,通常就是主键。

SQL中不同类型的语句?

  • DML(数据操作语句):CRUD相关,比如SELECT、UPDATE、INSERT、DELETE、CALL、EXPLAIN、LOCK TABLES
  • DDL(数据定义语句):创建、更改数据库表结构,处理数据库对象的设计和存储,比如CREATE、ALTER、DROP、TRUNCATE、RENAME
  • DCL(数据控制语句):比如GRANT、REVOKE
  • TCL(事务性控制语句):比如BEGIN/START TRANSACTION、COMMIT、SAVEPOINT、ROLLBACK、SET TRANSATION

COMMIT在SQL事务中的作用?

做事务的提交工作,将所有事务中的SQL结果永久存储在数据库中,事务中的操作内容,

数据库的事务的特性?

  • ACID
    • 原子性:每个事务的操作要么全部成功,要么全部失败,不会出现部分成功或失败的情况;
    • 一致性:每个事务在操作前后,对数据库整体是一致的,即不会破坏数据库数据整体的一致;
    • 隔离性:多个事务执行期间彼此看到的数据视图版本是相互是独立和隔离的(MVCC),Mysql存在不同的隔离级别,默认是RR;
    • 持久化:事务提交后,数据会持久化写入磁盘,即使是中间宕机、断电等情况发生;(基于Mysql的Binlog和RedoLog以及二阶段事务提交保证)

DELETE、DROP、TRUNCATE的差别?

  • 语法差异:
    • DELETE FROM TABLEDELETE * FROM TABLEDELETE FROM TABLE WHERE ...
    • DROP TABLE tb1
    • TRUNCATE TABLE tb1
  • DELETE删除:
    • DELETE是一个DML,而DROP和TRUNCATE属于DDL;
    • DELETE语义是按条件删除行记录;DROP语义是丢弃表,表会被移除;TRUNCATE TABLE是清空表,同时会重置表的主键ID标识;
    • DELETE删除行记录,在事务中操作的话,可以回滚
    • DELETE删除不会重置主键ID,但TRUNCATE TABLE
    • DELETE会触发触发器的执行
    • DELETE在事务中执行过程是将删除记录移到ROLLBACK空间,回滚的话会取回数据;
    • 仅在按条件删除记录时候使用
  • DROP,丢弃表:
    • 从数据库将表移除
    • 无法事务回滚
    • 不会触发触发器执行
    • 仅在不需要表时候使用
  • TRUNCATE TABLE,清空表:
    • 清空表,但相关表结构、索引等记录都在
    • 无法事务回滚
    • 会重置自增主键为1,类似一个全新的表
    • 操作比DELETE快,因为少了很多日志记录操作
    • 如果表有外键的话,无法使用
    • 不会触发触发器的执行
    • 无法执行WHERE条件
    • 仅在需要清空表时候可以使用这个

什么是数据库索引以及有哪些类型?

  • 数据库索引是一种数据存储结构,用于加速数据的检索性能,从数据结构类型区分,Mysql中的索引有Hash、B+树类型
  • InnoDB引擎,按功能划分可以分为聚簇索引非聚簇索引,非聚簇索引又包含普通索引、唯一索引、关联索引,索引支持前缀索引类型;
    • 聚簇索引:一张表的数据记录都聚簇在该索引上,主键索引为聚簇索引,一张表仅有一个聚簇索引;
    • 非聚簇索引:在非主键上创建的索引,称为非聚簇索引,也可以称为普通索引;在多个列上创建的索引称为关联索引,关联索引遵循最左前缀原则;唯一索引指通过索引可以确定在表中的唯一值记录;
  • 查询过程中,如果没有做到索引覆盖,则需要回表操作,同时Mysql数据库还会基于索引下推来进行查询记录的加速优化!

什么叫子查询,子查询的目的?

子查询也叫嵌套查询,

25个SQL性能面试问答


  1. sql-interview-questions-answers-experienced ↩︎