数据库复习及面试准备

数据库面试一般笔试会出一些编程类问题，面谈就问存储过程，触发器，索引等，再要求高一点，会问到数据并发问题。

数据库的基础知识：

数据模型的三要素：数据结构、数据操作、数据完整性约束

数据库管理系统(Database Management System,DBMS)是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。是系统软件。

数据模型从抽象层次上描述了系统的静态特征、动态行为和约束条件

在数据库设计中，将E－R图转换成关系数据模型的过程属于逻辑设计阶段

数据的物理独立性用户的应用程序与存储在磁盘上数据库中的数据相互独立

候选码：如果任意超码的真子集不能包括超码，则称其为候选码；超码包括候选码；从候选码中选出主码，候选码有多个，主码唯一。

关系运算包括选择、投影、连接以及除法。

投影是选择列，选择是选择行，关系R是的投影是从关系R中选择出若干属性列组成新的关系。

投影：对表进行垂直方向的分割，投影就是"筛选列"。一个数据库表，如仅希望得到其一部分的列的内容（但全部行），就是投影。

选择：对关系进行水平分割，选择就是"筛选行"。选择一般要对一张表选择符合条件的行（但包含所有列）。

连接：两表笛卡尔积的结果比较庞大，实际应用中一般仅选取其中一部分的行，选取两表列之间满足一定条件的行，就是关系之间的连接。

根据连接条件的种类不同，关系之间的连接分为等值连接、大于连接、小于连接、自然连接。

条件是类似于"B列=D列"的"某列=某列"的条件，就是等值连接；

条件是"某列>某列"的，就是大于连接；

条件是"某列<某列"的，就是小于连接。

自然连接是不提出明确的连接条件，但"暗含"着一个条件，就是"列名相同的值也相同"。在自然连接的结果表中，往往还要合并相同列名的列。当对关系R和S进行自然连接时，要求R和S含有一个或者多个共有的属性。

除法：记为R÷S，它是笛卡尔积的逆运算。设关系R和S分别有r列和s列（r>s，且s≠0），那么R÷S的结果有（r-s）个列，并且是满足下列条件的最大的表：其中每行与S中的每行组合成的新行都在R中。注意有时关系之间的除法也有"余数"，可能S×T的结果为R的一部分（最大的一部分），R中的多余部分为"余数"。

集合运算是二目运算，包括并、差、交、广义笛卡尔积四种运算。

并：并集；

交：交集；

差：差集；

广义笛卡儿积：

广义笛卡尔积（Extended Cartesian Product）：两个分别为n目和m目关系R和S的广义笛卡尔积是一个（n+m）列的元组的集合，元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有k1个元组，S有k2个元组，则关系R和关系S的广义笛卡尔积有k1*k2个元组，记作：R×S={tr⌒ts| tr∈R∧ts∈S}或记做R×S={(r1,…,rn ,s1,…,sm)∣((r1,…,rn)∈R∧(s1,…,sm)∈S)。

r,s为R和S中的相应分量。

就是把R表的第一行与S表第一行组合写在一起，作为一行。然后把R表的第一行与S表第二行依此写在一起，作为新一行。以此类推。当S表的每一行都与R表的第一行组合过一次以后，换R表的第二行与S表第一行组合，以此类推，直到R表与S表的每一行都组合过一次，则运算完毕。如果R表有n行，S表有M行，那么笛卡尔积R×S有n×M行。

原文：https://blog.csdn.net/zgcr654321/article/details/82077809

数据库管理系统的工作不包括：为定义的数据库提供操作系统

数据库管理系统(DBMS)的主要功能是定义数据库

1.数据定义：DBMS提供数据定义语言DDL（Data Definition Language），供用户定义数据库的三级模式结构、两级映像以及完整性约束和保密限制等约束。DDL主要用于建立、修改数据库的库结构。DDL所描述的库结构仅仅给出了数据库的框架，数据库的框架信息被存放在数据字典（Data Dictionary）中。  

2.数据操作：DBMS提供数据操作语言DML（Data Manipulation Language），供用户实现对数据的追加、删除、更新、查询等操作。

3.数据库的运行管理：数据库的运行管理功能是DBMS的运行控制、管理功能，包括多用户环境下的并发控制、安全性检查和存取限制控制、完整性检查和执行、运行日志的组织管理、事务的管理和自动恢复，即保证事务的原子性。这些功能保证了数据库系统的正常运行。  

4.数据组织、存储与管理：DBMS要分类组织、存储和管理各种数据，包括数据字典、用户数据、存取路径等，需确定以何种文件结构和存取方式在存储级上组织这些数据，如何实现数据之间的联系。数据组织和存储的基本目标是提高存储空间利用率，选择合适的存取方法提高存取效率。  

5.数据库的保护：数据库中的数据是信息社会的战略资源，随数据的保护至关重要。DBMS对数据库的保护通过4个方面来实现：数据库的恢复、数据库的并发控制、数据库的完整性控制、数据库安全性控制。DBMS的其他保护功能还有系统缓冲区的管理以及数据存储的某些自适应调节机制等。  

6.数据库的维护：这一部分包括数据库的数据载入、转换、转储、数据库的重组合重构以及性能监控等功能，这些功能分别由各个使用程序来完成。  

7.通信：DBMS具有与操作系统的联机处理、分时系统及远程作业输入的相关接口，负责处理数据的传送。对网络环境下的数据库系统，还应该包括DBMS与网络中其他软件系统的通信功能以及数据库之间的互操作功能。

常用的数据模型：

• 非关系模型
  • 层次模型（Hierarchical Model）
  • 网状模型（Network Model）
• 关系模型（Relational Model）
• 面向对象模型（Object Oriented Model）
• 对象关系模型（Object Relational Model）

几个概念的辨析：

• 数据库(DB)：长期存放在计算机内的有组织的可共享的数据集合
• 数据库管理系统(DBMS)：完成数据库的建立、使用和维护功能
• 数据库系统(DBS)
• 数据库管理员(DBA)：除DBMS完成外，还需专门的人员来完成，这些人被称为DBA
• DBS(数据库系统)=DB(数据库)+DBMS(数据库管理系统)

一个完整的关系系统包括: 全相关，部分相关和无关。

外模式

外模式又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行。外模式反映了数据库的用户观。

概念模式

模式又称概念模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的，体现、反映了数据库系统的整体观。

内模式

内模式又称存储模式，对应于物理级，它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义，它是数据库的存储观.

保证数据库物理数据独立性，需要修改的是模式与内模式的映射;

保证数据库逻辑数据独立性，需要修改的是模式与外模式之间的映象

数据库三范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

二 数据库编程问题:

我最常见这一个：

写出一条sql语句输出users表中31到40记录（数据库为SQL Server,以自动增长的ID作为主键，注意ID可能不是连续的）？

代码原链接：https://www.cnblogs.com/zlp520/p/3553576.html

--使用not in 
select top 10 * from users where id not in(select top 30 id from users order by id asc) order by id asc

--使用order by 
select * from (select top 10 * from  (select top 40 * from users order by id asc) as u order by u.id desc) as u1 order by u1.id asc

--使用开窗函数
select * from(select ROW_NUMBER() over(order by id) as num, * from users)as u where u.num between 31 and 40

数据库排序：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

asc 从小到大排列，升序

decs从大到小排列，降序

去掉重复项使用 distinct 关键字

数据类型转换：expression是原数据，length是目标数据的长度

1 cast函数：

CAST ( expression AS data_type [ ( length ) ] )

2 convert函数

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

取得前n个数据使用top关键字,若要查询前百分之几则启用percent关键字：

select top n [PERCENT] 列1,列2,…… from 表名

order by 和 group by 的区别：
1，order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。

2，group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。

聚合函数：

avg() 求平均数函数 获取所得列中的所有不为null的项的平均值。

count()  统计函数 获取到的行数

max() 取得列中的最大值

min   取得列中的最小值

having：having子句仅用于带有 GROUP BY 子句的查询语句中，where子句用于每一行数据的筛选（在变成一个组的某一部分之前），而having子句用于分组的聚合值的筛选。

用法：

group by cid ,Sgender having count(*)>3

select distinct top n *
from t1 join t2 on .....join t3 on
where ....
group by .....having
order by....

sum()  求和函数，获取所得列中的所有不为null的项的总和。

stdev()  标准偏差值函数  用于计算每一个数值与平均值的标准差

var()  方差值函数  VAR函数用于计算方差值

聚合开窗函数：
聚合函数加上 over() 开窗函数就是聚合开窗函数。将统计出来的信息分布到每一行中。结合聚合函数，排序函数使用。
partition by 用于指定分区，比如下例即根据班级分区统计同班的人数

 count(S_no) over(partition by s_Class)

删除：

drop:删除数据库，数据表（表的结构，属性，索引也会被删除），或删除数据表字段

--删除数据库
drop database DB_Name
--删除数据表
use DB_Name
drop table t1_Name,t2_Name
--删除数据表字段(ALTER TABLE 语句用于在已有的表中添加、删除或修改列。)
use DB_Name
alter table t_Name
drop column 字段名

delete:删除数据表中的若干行，不会删除表的属性等

Truncate：删除数据表中的数据，不删除表，相当于清空该表;且没有日志记录

truncate table t_Name

速度：drop>truncate>delete

约束：数据库约束是为了保证数据的完整性而实现的一套机制，它具体的根据各个不同的数据库的实现而有不同的工具。

not null 非空约束

primary key 主键

identity(1,1)标识

default  默认值

foreign key 外键

unique 唯一约束：定义一个唯一约束但是它并不包括NULL值。直接在字段定义后加入UNIQUE即可定义一个唯一约束。

check 检查：通过在定义数据库表里，在字段级或者是在表级加入的检查约束，使其满足特定的要求。使用关键字：CHECK 限制输入到列的值的范围

别名：使用as

筛选：

运算符：

1.比较运算符：=，>,>=,<,<=,!=,<>

2.逻辑运算符：and,or , not

模糊查询:like , % , [] , _ , ^

连接查询：来源于：https://www.cnblogs.com/xiaoyaojinzhazhadehangcheng/articles/8404292.html

内连接：

1. 内连接：在每个表中找出符合条件的共有记录。[x inner join y on...]
第一种写法：（只使用where）

select t.teacher_name, s.student_name from teacher t,student s where t.id = s.teacher_id;

第二种写法：（join .. on.. ）

select t.teacher_name, s.student_name from teacher t join student s on t.id = s.teacher_id;

第三种写法：（inner join .. on.. ）

select t.teacher_name, s.student_name from teacher t inner join student s on t.id = s.teacher_id;

外连接
外连接有三种方式：左连接，右连接和全连接。
2.1 左连接：根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示。[x left [outer] join y on...
第一种写法：（left join .. on ..）

select t.teacher_name, s.student_name from teacher t left join student s on t.id = s.teacher_id;

第二种写法：（left outer join .. on ..）

select t.teacher_name, s.student_name from teacher t left outer join student s on t.id = s.teacher_id;

第三种写法："(+)" 所在位置的另一侧为连接的方向

select t.teacher_name, s.student_name from teacher t, student s where t.id = s.teacher_id(+);

2.2 右连接：根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配，如果找不到匹配的，用null填充。[x right [outer] join y on...]
第一种写法：（）

select t.teacher_name, s.student_name from teacher t right join student s on t.id = s.teacher_id;

第二种写法：

select t.teacher_name, s.student_name from teacher t right outer join student s on t.id = s.teacher_id;

第三种写法："(+)" 所在位置的另一侧为连接的方向

select t.teacher_name, s.student_name from teacher t, student s where t.id(+) = s.teacher_id;

2.3 全连接：返回符合条件的所有表的记录，没有与之匹配的，用null表示（结果是左连接和右连接的并集）
第一种写法：（full join .. on ..）

select t.teacher_name, s.student_name from teacher t full join student s on t.id = s.teacher_id;

第二种写法：（full outer join .. on）

select t.teacher_name, s.student_name from teacher t full outer join student s on t.id = s.teacher_id;

完全外连接

select *
from 表名 as ...
inner join 列名 as ... on 条件

3. 自连接
自连接，连接的两个表都是同一个表，同样可以由内连接，外连接各种组合方式，按实际应用去组合。

SELECT a.*, b.* FROM table_1 a,table_1 b WHERE a.[name] = b.[name]

联合查询：将多个查询结果合并成一个结果集

要求：结果集列数一致;对应列的类型一致

用处:在查询结果处显示汇总

union 合并，消除重复行，按首列升序

union all 合并

except 差集

intersect 交集

select sid from ....
union/union all/except/intersect
select cid from cinfo

connection 连接

command 执行

DataReader 只读只进结果集

DataAdapter 一个封装了上面3个对象的对象

DataSet 数据集临时数据集

 

三.，事务，锁，存储过程，触发器，索引，并发处理

事务：事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

保证一个多操作的事情全部完成，否则回到做之前的状态，只有数据改变才会引发事务（增删改）查询不会。

事务的四大特性：

原子性（Atomicity）    事务中的操作必须完全成功，或全部失败回滚。
一致性（Consistency） 事务执行的结果必须是从数据库从一个一致性状态转换到另一个一致性状态。
隔离性（Isolation）  一个事务的执行不能被其他事务干扰
持久性（Durability）一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

事务的隔离级别：作者：jijs 链接：https://www.jianshu.com/p/aa35c8703d61 来源：简书

事务隔离级别	脏 读	不可重复读	幻 读
读未提及（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）	禁止	允许	允许
可重复读（REPEATABLE_READ）	禁止	禁止	允许
顺序读（SERIALIZABLE）	禁止	禁止	禁止

幻读：幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。

脏读：在一个事务处理过程里读取了另一个未完成提交的事务中的数据，读取数据不一致。

不可重复读：在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

一般情况下，隔离级别越高，并发性越差。

事务的语法：

begin/start transaction  --开始事务
commit transaction  --提交事务
rollback transaction --回滚事务

事务运行的三种模式：
1.自动提交事务
    每条单独的语句都是一个事务，每个语句都隐含一个commit
2.显式事务：需要手动控制
    以begin transaction 开始，以commit 或 rollback 结束。
3.隐性事务：不需要手动操作
    在前一个事务完成时，新事务隐式启动，但每个事务仍以commit或rollback显示结束

锁：自动添加的防止他人误操作，

锁定从数据库系统的角度大致可以分为6种：

共享锁（S）：还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。

排它锁（X）：还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。

更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。

意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。

架构锁：防止修改表结构时，并发访问的锁。

大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

存储过程将一段T-sql 脚本进行封装，以完成一个逻辑操作。

--创建存储过程
create proc/procedure 名称
       参数列表
as

begin
    .....
end

--调用存储过程
exec 名称 参数

存储过程有什么优点和缺点？

　　1. 运行速度：存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。    

　　2.  减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

　　3. 可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。  有些bug，直接改存储过程里的业务逻辑，就搞定了。 

　　4. 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

　　5. 可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

　　缺点  

1. SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

2. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

 　　3. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。     

 　　4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

　　 5. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

触发器

发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

触发器和存储过程的区别:

  触发器与存储过程的区别是运行方式的不同，触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户，应用程序或者触发器来显示地调用并执行。

--创建触发器
CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

以上来自：https://www.cnblogs.com/wangprince2017/p/7827091.html

索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容

聚集索引：与实际存储位置一样，一个表只能有一个聚集索引

非聚集索引：索引顺序与存储顺序不一样

建立索引：手动：右击表建立即可

代码：

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
);

举例：

CREATE INDEX PersonIndex
ON Person (LastName) 

哪些字段适合建立索引?

• 1、表的主键、外键必须有索引；
• 2、数据量超过300的表应该有索引；
• 3、经常与其他表进行连接的表，在连接字段上应该建立索引；
• 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
• 5、索引应该建在选择性高的字段上；
• 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
• 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
• A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
• B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
• C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
• D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
• E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
• 8、频繁进行数据操作的表，不要建立太多的索引；
• 9、删除无用的索引，避免对执行计划造成负面影响；

连接查询