SQL面试题（持续更新）

只是收集的一些基础概念和基本原理，如果有不对的地方希望可以指正~

-数据三大范式：

第一范式：强调的是列的原子性，列不能分成其他几列，第一范式就是无重复的域。

第二范式：首先是在第一范式的基础上，另外包含两部分的内容，一是表必须有主键，二是没有包含在主键中的列必须完全依赖于主键，不能只依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列必须直接依赖于主键，不能存在传递依赖。

 

-数据库优化：（之前总结过的）https://blog.csdn.net/weixin_44129250/article/details/85704817

-数字类型的选择：

float和double之间，尽量选择float

能用数字类型的字段尽量不用字符串类型的字段

对于状态字段可以尝试使用enum来存放

避免使用null字段 很难查询优化而且占额外的索引空间

 

-关于SQL分页优化：

应用场景：1000w条数据，使用limit offset分页时，为什么越往后翻越慢？

涉及到mysql的分页查询优化。

limit关键字的用法：  limit[offset,rows]  offset是返回第一行的偏移量  rows第二个指定返回行的最大数目。初始行的偏移量是0不是1.

最常见的mysql基本分页方式：select * from content order by id desc limit 0,10;

在中小数量的情况下，这样的SQL足够，唯一需要注意的问题是：要确保使用了索引，随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似于：

select  from content order by id desc limit 10000000000,10;

越往后分页，limit语句的偏移量越大，速度也会明显变慢。

优化方式：子查询的分页方式来提高分页效率：

select * from table where id >= (select id from table limit 10000,1)limit 10;

原理：子查询是在索引上完成的。而普通的查询是在数据文件上完成的。索引文件要比数据文件小很多所以操作起来更有效率。

 

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

关系型：oracle\db2\sql server\access\mysql

非关系型：nosql\cloudant\mongodb\redis\hbase

区别：

关系型：

有关系模型 事务的一致性 二维表格模型

优点：容易理解，使用方便，易于维护。

缺点：读写性能差，固定的表结构，高并发读写需求，海量数据的高效率读写。

非关系型：

键值对存储数据，分布式，不支持ACID，是一种数据结构化存储方法。

优点：读写性能比较高，数据没有耦合性，容易扩展，存储数据的格式是key value格式。文档或者图片

缺点：不提供SQL语句支持，学习和使用成本比较高。没有事务处理。

-存储引擎：

数据库中的表有不同的类型，表类型又称为存储引擎。

ISAM：读取操作速度很快，不占用大量内存和存储资源。不支持事务，如果硬盘崩溃数据无法恢复。

MYISAM：提供了索引和字段管理的功能，优化多个并发的读写操作。不支持事务，表锁设计，支持全文索引，缓冲池只缓存索引文件不缓存数据文件。不能恢复数据

INNODB：支持事务，行锁设计，支持外键，高可用，高性能，高扩展。每张表都是按照主键的顺序进行存储，如果没有显示的在表定义的时候指定主键，INNODB存储引擎会为每行生成一个6字节的rowid 并且以此作为主键。

MEMORY：数据都放在内存中，数据库重启或者发生崩溃，表中的数据会全部消失，默认使用哈希索引，不是B+树索引。

关于存储引擎的命令：show engines\G  查看所有支持的存储引擎。

 

-事务：

并发控制的基本单位。是一个操作序列，这些操作要么都执行，要么都不执行。是一个不可分割的工作单位，事务是数据库维护数据一致性的单位。说白了就是几步数据库操作而构成的逻辑执行单元。

ACID性：

原子性：事务是应用中不可再分的最小逻辑执行体。

一致性：事务执行的结果，必须使数据库从一个一致性状态变到另一个一致性状态，一致性是通过原子性来保证的。

隔离性：任意一个事务的内部操作对于其他并发的事务都是隔离的。

持续性：事务一旦提交，对数据做的任何改变都会记录到永久存储器当中，通常是保存进物理数据库。

MySQL的事务处理：

1、begin开始一个事务

2、rollback事务回滚

3、commit事务确认。

或者直接用set来改变mysql的自动提交模式：

mysql默认是自动提交的，也就是你提交一个query 就直接执行，可以通过

set autocommit = 0禁止自动提交。

set autocommit = 1开启自动提交

实现事务处理。

-简述数据库设计中一对多和多对多的应用场景：

一对一：一个学生对应一个学生档案资料。

一对多：一个学生只属于一个班。

多对多：一个学生可以选择多门课程。

 

创建自增ID的数据表：

如何基于数据库实现商城商品计数器？

create table product(

id int primary key auto_increment,

pname varchar(20),

pcount int);

 

--存储过程：

存储过程是一些预编译的SQL语句

是由一些SQL语句组成的代码块，这些SQL语句代码像一个方法一样实现一些功能，然后给这个代码块起一个名字，在用到这个功能的时候调用就可以了。

优点：

执行效率比较高

可以降低网络通信量，提高通信速率。

可以一定程度上确保数据安全

用于替代程序写的SQL语句，实现程序与SQL解耦。

缺点：

程序猿扩展功能不方便。

 

-触发器：

一种特殊的存储过程，事先为某张表绑定一段代码，当表中的某些内容发生改变（增删改）的时候，系统会自动触发代码并且执行。

触发器包含三个要素：

事件类型：insert delete update

触发时间：事件类型的前后 before after

触发对象：表中的每一条记录

 

-函数：

自定义函数创建语法：

创建：create function 函数名称（参数列表）

              returns 返回值类型 函数体

修改：alter function 函数名称 

删除：drop function 函数名称

调用：select 函数名称

SQL常用的函数：

字符串连接：concat('my','sql')

实现字母大小写转换：upper() 

获取随机数 rand()

获取整数的函数 向上取整ceil()  向下取整 floor()

四舍五入函数round()

日期时间函数now()  current_timstamp()当前时间戳

localtime() sysdate()

聚合函数：

avg()  count() min() max() sum() abs() bin() group_concat()返回由属于一组的列值连接组合而成的结果

 

-视图：

视图的一张虚拟的表，只包含使用时动态检索数据的查询，不包含任何列或者数据。

它可以简化复杂的SQL操作，隐藏具体的细节，保护数据，可以使用与表相同的方式来利用它们。

不能被索引，也不能有关联的触发器或者默认值。

创建视图语句：create view viewname as select语句

视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

 

-索引：

对数据库表中一或者多个列进行排序的结构。是帮助MySQL高效获取数据的数据结构，不必扫描整个数据库。

分为普通索引，唯一索引，主键索引，全文索引。

普通索引：最基本的索引，没有任何限制。

唯一索引：索引列的值必须唯一，允许有空值，如果是组合索引，列值的组合必须唯一。

全文索引：全文索引仅可用于MYISAM表。

单列索引，多列索引：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MYSQL只能使用一个索引，

会从多个索引中选择一个限制最为严格的索引。

组合索引（最左前缀）：简单的理解就是只从最左边的开始组合。

优点：提高数据检索的效率，降低数据库的IO成本。

唯一索引可以确保每一行数据的唯一性。

通过索引对数据进行排序，降低了数据排序的成本。

通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能，

缺点：索引实际上也是一张表，会占用一定的存储空间。

更新数据表的数据时，需要同时维护索引表，因此会降低增删改的速度。

 

-例举创建索引但是无法命中索引的几种情况

like查询以%开头。

小表查询。

提示不使用索引，

统计数据不真实。

（感觉索引的知识实在太多了改天还是单独总结一个吧 = =）

 

-主键和外键的区别

主键是能确定一条记录的唯一标识

外键用于与另一张表的关联，是能确定另一张表记录的字段，用于保持数据的一致性。

 

-char varchar

char：定长，存取速度相对快一些。

varchar：不定长，存取速度相对慢一些。

如何进行取舍：对于MYISAM表，尽量使用char。对于那些经常需要修改而容易形成碎片的myisam和isam数据表更是如此，它的缺点就是占用磁盘空间。

对于INNODB表：因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分。所以使用char类型不见得会比使用varchar类型好，事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘io的角度，使用varchar类型反而更有利。

存储很短的信息 用char 

固定长度的。char 更合适

十分频繁改变的column，因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，但是这些对于char来说是不需要的。

 

select语句执行的优先级：

from --on过滤--添加外部行join--where--group by--having--select--distinct--order by --limit

 

MySQL中int类型存储多少个字节：

4个

 

-连表操作：

内连接：只取两张表的共同部分。

左连接：在内连接的基础上保留左表记录。

右连接：在内连接的基础上保留右表记录。

全外连接：在内连接的基础上保留左右两表没有对应关系的记录。

交叉连接：交叉连接中左表的每一行都要与右表的所有行进行组合，交叉连接也称为笛卡尔积

左union右

去重distinct

 

笛卡尔积：

事实上所说的什么时候会出现笛卡尔积，是指一对多关系的时候。

它的指令是cross join

为什么要避免构造广义笛卡尔积？

两个表相连，每张表有100000条数据，如果不加连接条件，就会造成广义笛卡尔积，两张表的数据量相乘，这样的数据量计算机接受不了，所以要在两表连接的时候一定要加上连接条件，并且要想好逻辑。

或者如果是三张表连接，并且是1：n：n的关系，先关联两张表，然后将两张表关联的结果与第三张表进行关联。