数据库面试题总结一（范式、视图、索引、授权、事务、触发器、连接方式）

最新推荐文章于 2023-05-12 22:04:59 发布

多多崽

最新推荐文章于 2023-05-12 22:04:59 发布

阅读量611

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/zhsihui429/article/details/86530609

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、范式 2、视图 3、索引 4、授权 5、事务

6、触发器 7、连接方式（自然连接、外连接、内连接）

数据库面试题总结二（varchar / char、delete / drop / truncate、MyISAM / InnoDB、优化方法）

https://blog.csdn.net/zhsihui429/article/details/86571591

1、范式

基础知识：（先搞懂基础，再往下看）

（1）码

超码：一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一地标识一个元组
候选码：最小的超码
主码：用来在一个关系中区分不同元组的候选码
外码：一个关系模式r1可能在它的属性中包括另一个关系模式r2的主码，这个属性在r1上称作参照r2的外码

（2）依赖

完全依赖：设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X

例子：通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB

部分依赖：当主键由两个或两个以上字段构成，而表中的某些信息通过主键的一个字段就能唯一确定

例子：通过AB能得出C，通过A也能得出C，通过B也能得出C，那么说C部分依赖于AB

传递依赖：设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X

例子：通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A

（1）第一范式（1NF）：一个关系模式R的所有属性的域都是原子的。域是原子的即该域的元素被认为是不可分的单元。

（2）第二范式（2NF）：当关系模式R满足第一范式，并且R的所有非主属性都完全依赖于R的每一个主键属性，即它出现在一个候选码中，且没有部分依赖于一个候选码。

（3）第三范式（3NF）：关系模式R满足第一范式，且在R中不存在非主键属性传递函数依赖于主键属性。

（4）BCNF范式：关系模式R满足第一范式，且每个属性都不传递依赖于主键码。即在 3NF 的基础上消除主属性对码的部分依赖与传递函数依赖

※注意：

高一级范式，要满足低一级范式。eg：符合2NF的关系模式，必定符合1NF
一篇有助于理解三大范式的实用的文章：https://baijiahao.baidu.com/s?id=1591955163343123446&wfr=spider&for=

2、视图

虚关系：概念上包含查询结果，实际上并不预先计算并存储，而是在使用虚关系的时候才通过执行查询被计算出来
视图：作为虚关系对用户可见的关系，当视图关系被访问时，元组才通过计算查询结果被创建出来
物化视图：用于定义视图的实际关系改变，视图也跟着修改

create view v as <query_expression>;
-- v为视图名 <query_expression>为任何合法的查询表达式

create view faculty as
select ID, name, dept_name
from instructor;

在查询中，视图名可以出现在关系名可以出现的任何地方

select * from faculty;

当定义一个视图时，数据库系统存储视图的定义本身，而不存储定义该视图的查询表达式的执行结果，这样一旦视图关系出现在查询中，它就被已存储的查询表达式代替，而不会出现视图关系被修改时，视图就会过期这样子的情况
一个视图可能被用到定义另一个视图的表达式中

※除了一些有限的情况之外，一般不允许对视图进行修改（更新、插入或删除），否则可能会带来严重的问题。在对下列条件都满足时，称SQL视图是可更新的（即可执行插入、更新或删除）：

from子句中只有一个数据库关系
select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct声明
任何没有出现在select子句中的属性可以取空值，即这些属性上没有not null约束，也不构成主码的一部分
查询中不含有group by或having子句

可以通过在视图定义的末尾包含with check option子句的方式来定义视图

视图和表的区别：

视图是已经编译好的sql语句，而表不是；视图没有实际的物理记录，是虚拟的内存表，而表是物理存在的。
表是内容，视图是窗口；表是内模式，视图是外模式。
表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，是一些SQL语句的集合，是数据库数据的特定子集。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。视图可以禁止所有用户访问数据库表，而要求用户只能通过视图操作数据，这种方法可以保护用户和应用程序不受某些数据库修改的影响。
视图适合于多表连接浏览时使用，但不适合增、删、改，存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率。而临时表几乎是不会对性能有帮助，是资源消耗者。视图一般随该数据库存放在一起，临时表永远都是在tempdb（数据库）里的。视图是不会自己消失的，除非你删除它
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表，抽象的。
视图的建立和删除只影响视图本身，不影响对应的基本表。

3、索引

索引是一种数据结构，它允许数据库系统高效地找到关系中那些在索引属性上取给定值得元组，而不用扫描关系中的所有元组

create index index_name on A(D); -- 在关系A上的属性D创建索引

两种基本的索引类型：

顺序索引：基于值得顺序排序
散列索引：基于将值平均分布到若干散列桶中，一个值所属的散列桶是由一个散列函数决定的

【强推】一篇深入理解索引的文章：https://www.cnblogs.com/aspwebchh/p/6652855.html

※ 根据上面链接这篇文章总结几个重要的点：

给创建的表加上主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，即平衡树结构，或者说是整个表变成了一个索引，即聚集索引。这就是为什么一个表只能有一个主键，一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。
由于平衡树的结构，使得有索引时查找的速度比没有索引时的速度大大提升。
索引能让数据库查询数据的速度上升，但会使写入数据的速度下降，因为每次写入时需要维持平衡树这个结构，而增删改数据都会改变平衡树各节点中的索引数据内容，即破坏树结构，因此，在每次数据改变时，DBMS（数据库管理系统）必须重新梳理树（索引）的结构以确保它的正确，随之带来不小的性能开销。
如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。而每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。
非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。
有一种例外可以不使用聚集索引就能查询出所需要的数据——覆盖索引查询，即复合索引或者多字段索引查询。

4、授权

包括授权读取、插入、更新、删除数据

用户名public指系统的所有当前用户和将来的用户，因此，对public的授权隐含着对所有当前用户和将来用户的授权

授予权限 grant，包括select、insert、update、delete权限，授予的是某个关系上所以属性的权限列表中的权限

SQL授权机制可以对整个关系或一个关系的指定属性授予权限，但它不允许对一个关系的指定元组授权

grant <权限列表>
on <关系名或试图名>
to <用户/角色列表>;

grant select on department to Amit;
-- 授予数据库用户Amit在department关系上的select权限

SQL允许授予权限来指定权限的接受者可以进一步把权限授予其他用户，在相应的grant命令后面附加with grant option子句

grant select on department to Amit with grant option;

收回权限 revoke

revoke <权限列表>
on <关系名或试图名>
from <用于/角色列表>;

revoke select on department from Amit;

级联收回：从一个用户/角色那里收回权限可能导致其他用户/角色也失去该权限

若不采用级联收回，很可能会有某些用户企图通过相互授权来破坏权限收回规则。例如，若A最初由数据库管理员授予了一种权限，A进而把此权限授予给B，假设B现在把此权限授回给A，如果数据库管理员从A收回权限，看起来好像A保留了通过B获得的权限。然后，注意一旦管理员从A收回权限，在授权图中就不存在从根到A或B的路径了，SQL保证从这两个用户那里都收回了权限。

可用 restrict 限制级联收回，而关键字 casecade 表示需要级联收回，但级联收回是默认行为，所以可省略

revoke select on department from Amit restrict;

但级联收回再许多情况下是不合适的。假定A具有dean角色，他将instructor授给B，后来dean角色从A收回（可能A辞职了），而B继续被雇佣，并且还应被保持instructor角色，为了处理这一情况：

SQL允许权限由一个角色授予，而不是由用户来授予，默认情况下，一个会话所关联的当前角色是空的。一个会话所关联的当前角色可以通过执行set role role_name来设置，指定的角色必须已经授予给用户，否则set role语句执行失败。

如果要在授予权限时将授权人设置为一个会话所关联的当前角色，并且当前角色不为空的话，我们可在授权语句后加上granted by current_role子句。

假设将角色instructor（或其他权限）授给B是用granted by current_role子句实现的，当前角色被设置为dean而不是授权人（用户A），那么，从A处收回角色/权限（包括角色dean）就不会导致收回以角色dean作为授权人所授予的权限，即使A是执行该权限的用户（还有其他除了用户A以外的用户执行dean角色），这样，即使在A的权限被收回后，B仍然能够保持instructor角色。

角色 create role

create role role_name1; -- role_name表示角色名
grant select on A to role_name1; -- A为整个关系或一个关系的指定属性
create role role_name2;
grant role_name1 to role_name2; -- 角色可以授予给其他角色
grant role_name1 to user; -- 角色可以授予给用户

每个数据库用户被授予一组他有权扮演的角色（可能为空）。需要用户用他们自己的用户标识来连接数据库。任何可以授予给用户的权限都可以授予给角色，给用户授予角色就跟给用户授权一样。

一个用户或一个角色的权限包括：

（1）所有直接授予用户/角色的权限

（2）所有授予给用户/角色所拥有角色的权限

举个栗子?：如果一个用户A被授予了角色ddd，则用户A就拥有所有直接授予给A的权限，以及授予给ddd的权限。

视图的授权

创建视图的用户不需要获得该视图上的所有权限，他得到的那些权限不会为他提供超越他已有权限的额外授权

模式的授权

SQL指定了一种基本的授权机制：只有模式的拥有者才能够执行对模式的任何修改，诸如创建或删除关系、增加或删除关系的属性、以及增加或删除索引

SQL提供了一种 references 权限，允许用户在创建关系时声明外码

由于外码约束限制了被参照关系上的删除和更新操作，因此需要对references进行权限授予

grant references (dept_name) on department to A; 
-- 允许用户A创建这样的关系，它能够参照department关系的码dept_name

5、事务

【1】四大特性ACID

原子性(atomicity)：事务的所有操作在数据库中要么全部正确反映出来，要么完全不反应
一致性(consistency)：隔离执行事务时（即在没有其他事务并发执行的情况下）保持数据库的一致性
隔离性(isolation)：尽管多个事务可能并发执行，但系统保证对任何一对事务，每个事务都感觉不到系统中有其他事务在并发执行
持久性(durability)：一个事务成功完成后，它对数据库的改变必须是永久的，即使出现系统故障

【2】事务由查询和（或）更新语句的序列组成

（1）当一条SQL语句被执行，就隐式地开始了一个事务
（2）下列SQL语句之一会结束一个事务（关键词work为可选的）

Commit work：提交当前事务，也就是将该事务所做的更新在数据库中持久保持。在事务被提交后，一个新的事务自动开始
Rollback work：回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新。数据库就恢复到执行该事务第一条语句之前的状态

在事务执行过程中检测到错误时，如果一个事务还没有完成commit work，事务回滚是有用的。但一旦某事务执行了commit work，它的影响就不能用rollback work来撤销了。

一个事务或者在完成所有步骤后提交其行为，或者在不能成功完成其所有动作的情况下回滚其所有动作，通过这种方式数据库提供了对事务具有原子性的抽象，原子性就是不可分割性，要么事务的所有影响被反映到数据库中，要么任何影响都没有（在回滚之后）。

【3】隔离性级别：（从高到低）

（1）可串行化：保证可串行化调度。
（2）可重复读：只允许读取已提交数据，而且在一个事务两次读取一个数据项期间，其他事务不得更新该数据。（mysql默认）
（3）已提交读：只允许读取已提交数据，但不要求可重复读。（oracle默认）
（4）未提交读：允许读取未提交数据

【4】事务的并发访问问题（由隔离性引起）：
（1）脏读：B事务读取到了A事务尚未提交的数据。
（2）不可重复读：一个事务中，两次读取的数据的内容不一致。
（3）幻读/虚读：一个事务中，两次读取的数据的数量不一致。

读数据一致性及并发副作用隔离级别	读数据一致性	脏读	不可重复读	幻读
为提交读(read uncommitted)	最低级别，不读物理上顺坏的数据	是	是	是
已提交读(read committed)	语句级	X	是	是
可重复读(Repeatable red)	事务级	X	X	是
可序列化(Serializable)	最高级别，事务级	X	X	X