数据库面试（1）
1、什么是事务？
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行,在关系数据库中,一个事务可以是一条SQL语句、一组SQL语句或整个程序。
（1）原子性
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
（2）一致性
事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。如a+b=10;a发生改变，b也必须改变。
（3）隔离性
事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立。
（4）持久性
事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。
事务的语句
开始事务：BEGIN TRANSACTION（事务）
提交事务：COMMIT TRANSACTION（事务）
回滚事务：ROLLBACK TRANSACTION（事务）
脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
2、drop,delete与truncate的区别
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。
（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉
（3） 一般而言，drop > truncate > delete
（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
3.索引的工作原理及其种类
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
4、数据库范式
范式就是优化数据库存储方式的一些列规范。
1 第一范式（1NF）
所谓第一范式就是指数据库的中的列是不可分割的基本数据项。
不符合第一范式的数据库就不是关系数据库。
2 第二范式（2NF）
第二范式就是在第一范式的基础之上建立起来的，即第二范式必须先满足第一范式。
第二范式就是所有的非主属性都完全函数依赖于主属性。
完全函数依赖就是在，属性集中，不存在X->Y，对于任意X的任意真子集X'，使X'->Y。
举个例子：
表中有数据(Sno,Cno,Grade,Sdept)
主属性是(Sno,Cno)，但是Sdept却函数依赖于Sno,这使得Sdept部分函数依赖于(Sno,Cno)
解决办法就是把该表分成两个表：(Sno,Cno,Grade)和(Sno,Sdept)
3 第三范式（3NF）
第三范式也是在第二范式的基础之上建立起来的。
第三范式就是所有非主属性都不存在传递函数依赖于主属性的情况。
举个例子：
表中有数据(Sno,Cno,Sdept,Sdept_Address)
Sno函数确定Sdept，Sdept函数确定Sdept_Address，所以Sdept_Address传递函数依赖于Sno。
办法就是把该表分成两个表(Sno,Cno,Sdept)和(Sdept,Sdept_Address)
第三范式一定是第二范式。
5、数据库优化的思路
SQL语句优化
1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
数据库结构优化
1）范式优化： 比如消除冗余（节省空间。。） 2）反范式优化：比如适当加冗余等（减少join） 3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)
6、存储过程
存储过程是一组为了完成特定功能的SQL 语句集。，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
MySQL中的存储过程
1)不带参数
create procedure proc() begin select * from student; end;
执行过程如下所示：
call proc();
带in参数的存储过程
create procedure select_emp(in _id int) begin select * from tbl_emp where emp_id=_id; end;
要求：输入两个数，输出它们的结果值
create procedure adder(in a int, in b int, out _sum int) begin if a is null then set a=0; end if; if b is null then set b=0; end if; set _sum=a+b; end;
运行过程如下所示：
set @b=5; call adder(2,@b,@s); select @s as _sum;
out参数必须是变量，inout也是。
存储过程中的控制语句
create procedure proc_if(in _type int) begin declare c varchar(500); if _type=0 then set c='param is 0'; elseif _type=1 then set c='param is 1'; else set c='param is others'; end if; select c; end;
运行如下所示：
set @_type=3; call proc_if(@_type);
参考地址：https://www.cnblogs.com/chenpi/p/5136483.html
7、触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
8、存储过程与触发器的区别
触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。
create procedure adder(in a int, in b int, out _sum int)
begin
if a is null then set a=0;
end if;
if b is null then set b=0;
end if;
set _sum=a+b;
end;
