数据库高级知识点汇总

数据库

１.事务

​ (1)什么是事务？

​ 为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。

​ (2)事务的四个特性

• 原子性：事务包含的这一系列操作，要么全部成功，要么全部失败。
• 一致性：事务完成之后，不会将非法的数据写入数据库。
• 隔离性：多个事务可以在一定程度上并发执行。
• 持久性：事务完成之后，数据要永久保存（一般会保存在硬盘上）。

原子性（由DBMS的事务管理子系统来实现）；
一致性（由DBMS的完整性子系统执行测试任务）；
隔离性（由DBMS的并发控制子系统实现）；
持久性（由DBMS的恢复管理子系统实现的）；

​ (3)隔离级别

• 读未提交：一个事务可以读取到另外一个事务尚未提交的数据。该隔离级别可能会产生“脏读”、“不可重复读取”和“幻影读取”问题。

  

• 读已提交：一个事务只能读取到另外一个事务已经提交的数据。该隔离级别解决了“脏读”问题，但是仍然可能会发生“不可重复读取”和“幻影读取”问题。
  

• 可重复读取：在同一个事务当中，多次读取同一份数据，结果一样。该隔离级别解决了“脏读”和“不可重复读取”问题，但是仍然有可能会产生“幻影读取问题”（虚读）。

  

• 序列化：多个同务只能排队执行，即只有一个事务结束之后，另外一个事务才能开始执行。该隔离级别解决了“脏读”，“不可重复读取”和“幻影读取”问题，但是程序性能会下降。所以只有必要的时候（比如在银行系统里面）才会使用。

  总结：

  ​ 隔离级别从低到高依次是"读未提交"、“读已提交”、“可重复读取”和“序列化”，隔离级别越高，性能越低。mysql数据库默认隔离级别是“可重复读取”，oracle是“读已提交”。数据库底层使用的“加锁”的机制来实现不同的隔离级别，包括对整个表加锁，对表中的行加锁。

  ​ mysql数据库开始事务、提交事务、回滚事务

  begin;
  commit;
  rollback;
  

  ​ mysql数据库必须将数据库引擎设置为"innodb"才能支持事务。

２.视图

​ (１)什么是视图？

​ 在已有的表或者视图上创建的虚拟表。

​ (2)创建视图

​ create view 视图名　as select

注：可以对单表或者多表进行查询，数据库会将视图的定义保存下来。

可以对（单表）视图进行一些增删改查操作，这些操作会影响到原始的表。

(3)删除视图

​ drop view 视图名

参考sql:

create  table t_emp(
	id int primary key auto_increment,
	name varchar(50),
	salary int,
	age int
);

create view v_emp as select * from t_emp;
create view v_emp2(name,salary) as select name,salary from t_emp;

insert into v_emp2 values('Jhon',3000);

create table t_dept(
	id int primary key,
	name varchar(50),
	addr varchar(100)
);
insert into t_dept values(100,'财务部','北京');
insert into t_dept values(200,'开发部','上海');

create table t_staff(
	id int primary key auto_increment,
	name varchar(30),
	age int,
	dept_id int
);
insert into t_staff values(null,'张三',33,100);
insert into t_staff values(null,'李四',23,100);
insert into t_staff values(null,'王五',43,200);

create view v_staff_dept(sname,dname,addr) 
as 
select s.name sname,d.name dname,d.addr from t_staff s 
join t_dept d on s.dept_id = d.id;

drop view v_emp;

３.索引

(1)什么是索引？

​ 为了提高查询的速度而在数据库端创建的一种排序的数据结构。

​ 注：索引类似于一本书的目录

(2)如何创建索引？

​ create index 索引名　on 表名(字段列表)

(3)如何查看当前查询是否用到的索引？

(4) 在哪些字段上加索引？

​ 应该将经常作为查询条件的字段加索引，除此以外，还要在分组、过滤、排序及联合查询的字段上加索引。

(5)如何删除索引？

​ drop index 索引名 on 表名

(6)联合索引

​ 所谓联合索引（复合索引），指的是索引字段是多个，比如:

​ 使用联合索引时，要注意“最左匹配原则”。即在使用联合索引的某个字段作为查询条件时，该字段左边的所有字段也要同时作为查询条件，比如：

​ 联合索引包含了三个字段(c1,c2,c3),则：

​ 将c1,c2,c3同时作为查询条件时，会用到索引；

​ 将c1,c2同时作为查询条件时，会用到索引；

​ 将c1作为查询条件时，会用到索引；

​ 将c2作为查询条件时，不会用到索引；

​ 将c3作为查询条件时，不会用到索引；

​ 将c1和c3作为查询条件时，不会用到索引。

(7) 索引原理

　假设t_user表有15条记录，每个磁盘块只能放４条记录，则需要４个磁盘块，对应的"B+"

树结构如下：

(8)主键索引和非主键索引。

​ 数据库自动会为主键字段添加上相应的索引，该索引称之为主键索引。

比如，有一张表(id是主键，除此之外还有name,email,age等字段)，则数据库会为id字段加上主键索引，如果以id作为条件，显然会用到主键索引，但是如果是以name等非主键字段作为查询条件，则不会用到主键索引，所以为了提高查找的速度，可以在非主键字段上加上相应的索引。

​ 主键索引和非主键索引有什么区别？

​ 非主键索引叶子节点存放的是索引字段及主键值，而主键索引叶子节点存放的是完整的记录。使用主键索引查找要比使用非主键索引一般要快一些，因为使用非主键索引，很多时候需要“回表”。

​ 　什么是索引覆盖？

​ 　查找的字段都包含在了索引字段里面，此时，不需要进行“回表”操作，查找的速度会非常快。

面试相关：

​ 为什么数据库不使用B树而是使用B+树？

​ 因为Ｂ树索引块除了可以存放索引字段及指针以外，还可以存放记录，也就是说，Ｂ树的索引的扇出变少（扇出指的是索引块当中指针的个数），相应的由索引块构成的树的高度增加，也就是说，需要进行更多的“i/o”操作。

　为什么数据库很少使用hash作为索引的数据结构？

​ hash索引虽然依据索引字段定位记录很快（比B+树还要快），但是不能进行范围查询。

(9)使用索引需要注意的问题:

​ a. 使用索引之后，虽然加快了查询的速度，但是在进行添加、删除、修改操作时会变慢，因为需要重建索引。在批量添加记录时，建议先临时删除索引，在批量添加成功之后再加上相应的索引。

​ b.不要建过多的索引。

​ 注：索引会占用硬盘空间，一般建议不要超过6个。

​ c.小表不要建索引。

​ 注：小表使用全表扫描更快。

​ d.索引字段不要参与计算。

​ 比如　“select * from t_user where id-1 = 100”　不会用到索引。

​ 注：包括使用函数都不行。

​ e. 尽量在同值少的字段上建索引。

​ 注：　比如性别就不适合作索引。

​

４.存储过程（了解）

​ (1)什么是存储过程?

​ 存储在数据库端的一组为了完成特定功能的sql语句。

​ (2)如何创建存储过程?

​ create procedure 存储过程名([参数])

​ 参数格式 (参数类型 参数名　数据类型)

​ 参数类型有三种：

​ IN: 输入参数，该参数的值必须在调用该存储过程时指定，在存储过程内部使用， 不能返回。

​ 缺省值是IN。

​ OUT：输出参数，该参数值的值可以在存储过程内部修改，并可返回。

​ INOUT：输入输出参数，该参数需要在调用时指定，并且可以返回。

delimiter //
create procedure proc_find1()
begin
	select * from t_emp;
end
//
delimiter ;

注：
	delimiter // 这句的作用是将结束符号设置为"//"

call proc_find1；

delimiter //
create procedure proc_find2(eid int)
begin
	select * from t_emp where id = eid;
end
//
delimiter ;

call proc_find2(1);

delimiter //
create procedure proc_find3(out max_sal int)
begin
	select max(salary) into max_sal from t_emp;
end
//
delimiter ;

call proc_find3(@sal);
select @sal
</