sql面试问题汇总

1：sql中delete,drop,truncate区别

delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除。

• delete是DML语句，执行完可以回滚
• truncate 清空整个表的数据，不能回滚
• drop 连表数据和表结构一起删除。
  执行的速度上，drop>truncate>delete

2：UNION与UNION ALL的区别？

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

UNION ALL的效率高于 UNION

3：count(1)、count(*) 与 count(列名) 的区别？

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

4：索引有哪些优缺点？

优点：

唯一索引可以保证数据库表中每一行的数据的唯一性

索引可以加快数据查询速度，减少查询时间

缺点：

创建索引和维护索引要耗费时间

索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间

以表中的数据进行增、删、改的时候，索引也要动态的维护。

5：数据库的三大范式

第一范式：数据表中的每一列（每个字段）都不可以再拆分。

第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。

6：什么是幻读，脏读，不可重复读呢？

事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读

在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

7：mysql的四大特性分别是：原子性，一致性，隔离性和数据持久化

原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

一致性：事务开始前和结束后，数据库的完整性约束没有被破坏

隔离性：个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久化：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响

8：事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

读未提交（Read Uncommitted）

读已提交（Read Committed）

可重复读（Repeatable Read）

串行化（Serializable）

Mysql默认的事务隔离级别是可重复读(Repeatable Read)

9：limit 1000000 加载很慢的话，你是怎么解决的呢？

方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit

select id，name from employee where id>1000000 limit 10.

方案二：在业务允许的情况下限制页数：

建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

方案三：order by + 索引（id为索引）

select id，name from employee order by id limit 1000000，10

方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）

SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

10：MySQL 索引使用有哪些注意事项呢？

可以从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则

索引哪些情况会失效

• 查询条件包含or，可能导致索引失效

• 如何字段类型是字符串，where时一定用引号括起来，否则索引失效

• like通配符可能导致索引失效。

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

• 在索引列上使用mysql的内置函数，索引失效。

• 对索引列运算（如，+、-、*、/），索引失效。

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

• 索引字段上使用is null， is not null，可能导致索引失效。

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

• mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

• 数据量少的不适合加索引

• 更新比较频繁的也不适合加索引

• 区分度低的字段不适合加索引（如性别）

11：数据库事务四大特性的实现思想

原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。

隔离性：通过锁以及MVCC,使事务相互隔离开。

一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

12：字段为什么要求定义为not null？

null值会占用更多的字节，并且null有很多坑的。

13：sql的视图

从SQL的角度来看，视图就是一张表，存在表名、字段列。在SQL语句中，也并不区分实体表和视图。

视图和实体表的区别就在与：是否保存了实际数据。视图本身是一个不含任何数据的虚拟表

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。将频繁使用的查询语句保存成视图，这样就不用每次都重新写了。

创建视图：CREATE VIEW test.viewA AS SELECT * FROM table

视图用途： 简化sql查询，提高开发效率，兼容老的表结构。

视图的常见使用场景：

重用SQL语句；

简化复杂的SQL操作。

14：sql查询结果增加自增列

1、使用mysql中的自定义变量。
先使用set进行设置一个变量，再对变量每次加1即可。

set @row_number=0;
SELECT (@row_number:=@row_number+1) as num,id from xv_sys_config_t;
set @row_number=0;  //使用完后记得归0不然下次执行依旧进行自增。

就会新增num列，并且结果就会实现自增了

num  id
1      1
2      2
3      3

2、使用row_number()函数，行数记录器
select row_number() over (order by id)as num ,id from table

15：主键和外键

主键PRIMARY KEY：表示数据的唯一性 如 PRIMARY KEY (ID_),
外键FOREIGN KEY：用于保证主表和从表间数据的一致性 、完整性。

创建外键示例：
	CONSTRAINT 关键字，
    ACT_FK_MODEL_DEPLOYMENT 起外键名称，
    FOREIGN KEY (DEPLOYMENT_ID_) 外键关键字里面是建立外键的字段，
    REFERENCES ACT_RE_DEPLOYMENT (ID_)依赖于哪个表的主键字段建立的。
 
示例：CONSTRAINT ACT_FK_MODEL_DEPLOYMENT FOREIGN KEY (DEPLOYMENT_ID_) REFERENCES ACT_RE_DEPLOYMENT (ID_),

主表：被当做外键的列在哪个表中是主键，即哪个表时主表。
从表：呢个表中存在外键，哪个表就是从表。
数据删除：删除主表的数据必须先删除从表的数据，当然也可以进行级联删除、级联置空等操作（级联删除即删除主表数据同时删除从表数据，但是需要在创建外键后面加上 on delete cascade）。

比如：学生表中有主键id，外键classid；class表中则id是主键，对应student.classid。
则主表为class表，从表为student表。