数据库 考点

基础知识：

数据查询：select

数据定义(DDL)：create    drop     alter

数据操纵(DML)：insert    update     delete

数据控制(DCL)：grant     revoke

 =========================================================================================================================

数据库系统，一般由数据库，数据库管理系统，应用系统，数据库管理员构成。

 

数据库系统的特点：

(1)数据结构化

(2)数据的共享性高，冗余度低，易扩充

(3)数据独立性高

(4)数据由DBMS统一管理和控制

DBMS必须提供几方面的数据控制功能：

数据安全性保护

数据完整性检查

并发控制

数据库恢复

 

两类数据模型：逻辑模型，物理模型

 

数据模型是数据库系统的核心和基础

 

数据模型的组成：数据结构，数据操作和完整性约束

 

数据库系统的三级模式：内模式(存储模式)，外模式(用户模式)，模式(逻辑模式)

 

                         SQL

----------------------------------------------------

视图1        视图2                              外模式

----------------------------------------------------

基本表1      基本表2      基本表3      模式

-----------------------------------------------------

存储文件1            存储文件2           内模式

 

 =================================================================================================================================

视图是从一个或者几个基本表导出 的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此，视图是一个虚表。

 所谓虚表，就是该整体表在硬盘上不存在，其实，我们创建一个视图，实际上是创建了一个经过包装了的Select语句，在这里我们称为A，创建视图，就是我们往硬盘存放了A，我们调用该视图的时候，系统就会自动执行一遍A，然后，我们看见的就是一张表了。（这张表数据的改变时，基表变不变，那要看具体情况了，可以说明的是，我们可以通过虚拟表达到改变基表数据的目的）

 

2-2、视图的优缺点
优点：视图可以限制我们对表中一些数据的访问，增加了数据的安全性；减少了重复写入T-SQL语句。

缺点：复杂的视图一般不能修改内容；能修改的视图，也需要经过变化使该操作应用到基表中。

 

(1)视图隐藏了底层的表结构，简化了数据访问操作
(2)因为隐藏了底层的表结构，所以大大加强了安全性，用户只能看到视图提供的数据
(3)使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性
(4)视图提供了一个用户访问的接口，当底层表改变后，改变视图的语句来进行适应，使已经建立在这个视图上客户端程序不受影响

 

视图可以分为三类:
普通视图(Regular View),索引视图(Indexed View),分割视图(Partitioned View)。

普通视图由一个Select语句所定义，视图仅仅包含其定义和被引用表的metadata.并不实际存储数据。

索引视图可以看作是一个和表(Table)等效的对象！SQL Server中的索引视图和Oracle中的Materialized View是一个概念.想要理解索引视图，必须先理解聚集索引。

聚集索引简单来说理解成主键，数据库中的数据按照主键的顺序物理存储在表中，就像新华字典，默认是按照ABCD….这样的方式进行内容设置。ABCD….就相当于主键.这样就避免了整表扫描从而提高了性能.因此一个表中只能有一个聚集索引。

对于索引视图也是，为一个视图加上了聚集索引后。视图就不仅仅再是select语句和表的metadata了，索引视图会将数据物理存在数据库中，索引视图所存的数据和索引视图中所涉及的底层表保持同步。

分割视图其实从微观实现方式来说，整个视图所返回的数据由几个平行表（既是几个表有相同的表结构，也就是列和数据类型，但存储的行集合不同)进行UNION连接(对于UNION连接如果不了解，请看我之前的博文)所获得的数据集.

 

 =====================================================================================================================

 

关系的三类完整性约束：

(1)实体完整性：主属性不能为空

(2)参照完整性：涉及到外键

(3)用户自定义完整性

任何关系数据库系统都应该支持实体完整性和参照完整性，这是关系模型所要求的

 

SQL功能

数据查询：select

数据定义：create，drop，alter

数据操纵：insert，update，delete

数据控制：grant，revoke

 

数据定义语言(DDL)   数据操纵语言(DML)  数据控制语言(DCL)

 

操作对象：

模式 ：创建模式---------create schema       删除模式--------drop

视图：创建视图----------create view            删除视图--------drop view

索引：创建索引---------create index           删除索引---------drop index

表：创建表----------create table      删除表------drop table     修改表-------alter table

SQL标准通常不提供修改模式定义，修改视图定义和修改索引定义。

 

删除模式：

drop schema +模式名称 +cascade 或者 restrict

cascade 表示在删除模式的同时把该模式中所有的数据库对象全部删除

restrict 表示如果模式中已经定义了下属的数据库对象，则拒绝删除语句的执行。

 

 创建一个模式，就建立了一个数据库的命名空间，一个框架，在这个空间中首先要定义的是该模式包含的数据库基本表

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是否违背了这些

完整性约束条件。如果完整性约束条件涉及到表中多个属性，则必须定义在表级上。

 ========================================================================================================================

创建学生表

create table student

（

      Sno char(9) primary key，

      Sname CHAR(20) unique，

      Ssex char(2)，

      Sage smallint，

      Sdept char(20)

）；

 

创建课程表

create table Course

(

     Cno char(4) primary key，

     Cname char(40)，

     Cpno char(4)，

     Ccredit smallint，

     foreign key Cpno references Course(Cno)

)；

 =====================================================================================================================

 

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它。

一、存储过程与函数的区别：
1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
2.对于存储过程来说可以返回参数(output)，而函数只能返回值或者表对象。
3.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

二、存储过程的优点：
1.执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
2.允许模块化程序设计 – 类似方法的复用
3.提高系统安全性 – 防止SQL注入
4.减少网络流通量 – 只要传输存储过程的名称

 

系统存储过程一般以sp开头，用户自定义的存储过程一般以usp开头

 

1系统存储过程:以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

 

2本地存储过程:用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

 

3临时存储过程:分为两种存储过程：
    一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
    二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

 

4远程存储过程:在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

 

5 扩展存储过程:扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
存储过程的优点：1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

 

2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

 

3.存储过程可以重复使用,可减少数据库开发人员的工作量。

 

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

 

存储过程的缺点：

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

2.可移植性差：由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

================================================================================================================================

 

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：1NF、2NF、3NF、BCNF、4NF和5NF(又称完美范式)。

满足最低要求的范式是第一范式（1NF）。
在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），
其余范式以次类推。

一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式（1NF）无重复的列，

即数据库表的每一列都是不可分割的原子数据项。在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。

 

第二范式（2NF）在1NF的基础上，非码属性必须完全依赖于码[在1NF基础上消除非主属性对主码的部分函数依赖] 。

第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。第二范式（2NF）要求实体的属性完全依赖于主关键字。

 

第三范式（3NF）在1NF基础上，

任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖] 第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。

简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。

 

（BCNF）在1NF基础上，任何非主属性不能对主键子集依赖[在3NF基础上消除对主码子集的依赖] 一般关系型数据库设计中，达到BCNF就可以了！
================================================================================================================================

事务(Transaction) 是指作为单个逻辑工作单元执行的一系列操作。

一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

 

原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。

如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

一致性：事务在完成时，必须使所有的数据都保持一致状态。

在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。

事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。

例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。

隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。

       事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。防止数据丢失

持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

 =============================================================================================================================

 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

       精简来说，索引是一种结构.在SQL Server中，索引和表（这里指的是加了聚集索引的表）的存储结构是一样的,都是B树，B树是一种用于查找的平衡多叉树.
       理解为什么使用B树作为索引和表（有聚集索引）的结构，首先需要理解SQL Server存储数据的原理.

       在SQL SERVER中，存储的单位最小是页(PAGE),页是不可再分的。就像细胞是生物学中不可再分的，或是原子是化学中不可再分的最小单位一样.这意味着,SQL SERVER对于页的读取，要么整个读取，要么完全不读取，没有折中.
       在数据库检索来说，对于磁盘IO扫描是最消耗时间的.因为磁盘扫描涉及很多物理特性，这些是相当消耗时间的。所以B树设计的初衷是为了减少对于磁盘的扫描次数。
如果一个表或索引没有使用B树（对于没有聚集索引的表是使用堆heap存储),那么查找一个数据，需要在整个表包含的数据库页中全盘扫描。这无疑会大大加重IO负担.而在SQL SERVER中使用B树进行存储，则仅仅需要将B树的根节点存入内存，经过几次查找后就可以找到存放所需数据的被叶子节点包含的页！进而避免的全盘扫描从而提高了性能.
 

       优点：大大加快了对源表的执行速度，我们对索引表的检索就可以实现对源表的检索。到底快在哪里？

        举例说明：我们通过T-SQL语句查询源表中的一条记录，在没有索引表的时候，计算机首先把整个源表从外存加载到内存，然后再一一匹配，从外存加载到内存耗时是非常的大；在有索引的时候，计算机首先加载索引表，然后匹配，找到后，取出它的物理地址，此时，再从外存中加载大表中的该记录，这种方法，人看的时候麻烦，但计算机可能就不那么认为（具体还要看源表记录的长度和数量）

       缺点：索引表需要占物理空间；当对源表操作时，也要维护索引表，是维护的任务加重了。

 

 

 ============================================================================================================================

数据库是一个多用户使用的共享资源。

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。

若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。

当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。

加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

在数据库中有两种基本的锁类型：
排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。

当数据对象被加上排它锁时，其他的事务不能对它读取和修改。

加了共享锁的数据对象可以被其他事务读取，但不能修改。

数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。