SQLserver基础理论学习

SQLserver基础理论学习：信息体系结构原则1、信息体系结构原则1.1信息管理的3个主要方面：数据库设计和开发、企业数据中心的管理、商业智能分析1.2信息体系结构原则首先必须有详细的信息清单，包括信息的位置、来源、敏感性、当前和未来信息的价值以及当前的拥有者1.3要确保数据能够在未来使用，必须降低当前设计的耦合程度。1.4数据存储目标1.4.1简单性要对复杂性感到敬畏，应不断调整设计，直到整个设计小组都认为它是能够满足需求的最简单解决方案。字典中“困难”的同义词包含“复杂”是有道理的。简单的解决方案通常是优雅而容易理解的。（应让事情尽可能简单，且不能再简单——爱因斯坦）做到简单性的要求：（1）对需求有全面认识（2）掌握大量可供参考的模式和解决方案（3）对技术规则和行业术语有全面认识（4）创造性掌握行业知识，直到何时及如何改善技术系统（5）对用于实现设计的工具和设备以及解决方案的运行环境有深入认识（6）设计人员互相信任、分享想法而不考虑个人得失（7）不断优化设计，直到设计出优雅而简单的设计方案（8）对复杂性抱有健康的敬畏心态1.4.2有用性有用性包括三点：（1）满足组织需求（2）适应性：设计与数据存储的范围和用途相称数据存储设计模型包括：关系DBMS、对象数据库、对象/关系数据库、通用模式、数据仓储数据库的范围包含数据在组织内的使用度（个人、部门、营业单位、公司、企业）以及数据的持续性（当前、当年、永远）（3）数据格式可供应用程序使用以及易于提取数据存储配置包括5种类型：（1）主数据存储：运营数据库或联机数据处理数据库，用于收集第一代交易数据。在组织中必不可少，且是唯一的。主数据库存储需要在方便数据检索和数据更新之间取得平衡。每个运营单位都可能有个主数据存储。（2）缓存数据存储：可选，是主数据存储的只读拷贝，用于提供数据，减轻主数据库的负载。优化它，可提供数据库检索性能。（3）引用数据存储：主要为只读，存储组织中较少变更的数据。类似图书馆的目录区。优化它，也可很高检索性能。（4）数据仓库：从企业多个主数据存储收集大量数据，并使用提取、转化和加载过程，将格式和架构转为通用，简化数据检索。同时存储历史记录，减轻运营数据存储的负载。组织必须有一个通用的数据仓库。主要任务是数据检索和分析。（5）数据集市：数据仓库的子集，包含经过预先汇总的数据。主数据存储的设计方法（1）关系DBMS传统的数据库，它讲类似或相关的数据放到一个表中。适合满足稳定的数据架构需求，这种需求只包含少量的is_a关系。（2）面向对象的DBMSOODBMS必须存储对象，因此判断OODBMS优劣的一个重要标准就是它能否存储复杂的对象，如XML文件、.net文件OODBMS非常适合这样的程序：架构可能发生重大变化、包括复杂数据类型、类之间涉及多个is-a关系、包括复杂的多关联以及要求将数据同应用程序轻松关联起来。有三种主要的面向对象的数据库A对象持久化数据存储（OPDBMS）:相当于一个存储对象的状态仓库B面向对象的数据存储（OODBMS）C对象/关系数据存储（O/RDBMS）（3）通用模式DBMS也称为动态钻石模式，有时被用作关系数据库产品中的面向对象的数据库设计。在应用程序需要动态属性时，这种设计非常有用。1.4.3完整性数据实际上是实体和属性，因此数据完整性由实体完整性和域完整性组成。（1）实体完整性：本质就是规范化（2）域完整新：保证属性只取有效值，包括为空性。（3）引用完整性：外键域的完整性（4）用户定义完整性（5）事务完整性：事务是一个逻辑工作单元，如插入100行。数据库产品的质量是根据其事务对ACID属性的遵循程度衡量的。A（atomicity）原子性：事务必须是原子的，这意味着在事务结束时，耀目所有操作成功，要么都失败。C(consistency)一致性：这意味着在事务执行前数据库处于一致性状态，事务结束后，数据库回到一致性状态。一致性意味着每行和每个值都与描述一致且满足所有的约束。I（isolation）隔离性：每个事务都必须是隔离的，不受其他事务的影响，不管是否有其他事务执行，它都必须使用它开始时的数据集继续执行下去。D（durability）持久性：不管系统是否发生故障，事务的结果都是永久性的。（6）事务缺陷脏读：如果事务能够读取其他商务尚未提交的更新，则被称为脏读。脏读可能导致在读取的数据未提交前，而写入该数据的事务可能被回滚。不可重复读：类似脏读，指事务能看到其他事务已提交的数据更新。如果两次读取得到的结果不同，则存在不可重复读性事务缺陷。虚幻行：危害最小，指事务的更新操作不仅影响结果集的数据值，还导致select语句返回不同记录集。丢失更新：两个用户同时更新一行数据时，第二个覆盖了第一个的更新，导致更新丢失。死锁：多个项目的多个事务争用一个数据资源时容易发生死锁，需要一个事务主动放弃或退出。（7）隔离级别：未提交读、已提交读、可重复读和可序列化，2005还引入了快照隔离，但容易导致更新丢失。1.4.4性能物理磁盘的性能是最严重的瓶颈，因此提高性能的关键之处在于减少执行任务时需要读取的物理页数量。提高性能的技术有：（1）设计：简化设计，不要包含过多的表，过多游标等（2）基于集合的处理：基于集合的优秀查询能够对整个数据集进行处理，且只读取一次所需数据页，而基于游标的解决方案分别对每一行进行处理。（3）索引是查询和数据之间的桥梁，它能减少执行表读取操作时需要读取的物理页数。要更精确滴设计索引，必须了解数据库引擎的查询优化器和索引结果的工作原理。索引有主力提高数据读取的性能，但给写入性能带来负面影响，插入和更新数据时，要确保索引数据同步。（4）分区：将数据分散到多个磁盘中。（5）缓存：预先从磁盘中读取数据，以便数据库操作需求时能够在内存中找到它们。1.4.5可用性恢复计划、冗余性、存档方式以及网络速度都将影响可用性，可用性通常用几个9表示，7个9表示在99.99999%情况下可用，5个9表示在99.999%可用。以此类推。（1）冗余：找出可存在的故障点，并通过过程备用解决方案来避免或降低故障带来的影响。（2）恢复：利用备份文件还原数据。1.4.6可扩展性（1）抽象层：降低数据存储和应用程序之间的耦合程度，一种最主要的方法是使用抽象层。这样可以避免因为缺乏封装导致两者之间的耦合过深。（2）归一化：将类似的尸体组合成一个实体。1.4.7安全性（1）数据中心的物理安全性（2）操作系统的安全性信息安全还包括：（1）限制访问（2）信息所有权（3）审核跟踪1.5优化理论和SQLserver(1)架构设计避免过于复杂精心挑选键处理可选数据实现抽象层（2）查询问题最佳解决方案复杂的业务逻辑查询、子查询、CTE动态生成DDL游标对列表进行逆规范化多赋值标量和游标交叉表包含透视和case表达式的查询导航层次结构UDF或CTE计算累计总计和动态和游标（3）索引（4）并发性：锁定和阻塞问题。nolock并不是很好的解决方式，很危险。可以采用缩短事务的持续时间来实现。（5）高级伸缩性快照隔离分区表索引视图servicebrokerSQLserver基础理论学习：关系数据库建模3.关系数据库建模3.1模拟现实数据建模过程：（1）手机观察结果和需求（2）现实世界的逻辑表示（3）可见实体的识别和设计（4）架构设计（辅助和支持实体）（5）应用程序结构设计3.2可见实体可见实体通常是大多数人能够识别的对象。（1）每个元组（行）都是独立的（2）主键（3）表、行和列3.3识别多个实体（1）多个对象（2）对象之间的关系（3）组织对象（4）一致的查找值（5）复杂对象3.4关系模式关系使用键将实体关联起来，包含如下两个主要属性：基数：在关系的每一端可能存在的对象数可选性：关系是可选的也是强制的（1）辅助实体和外键两个对象彼此相关联时，通常一个实体为主实体，另一个实体为辅助实体。主实体中的一个对象将关联到辅助实体中的多个对象或元组。（2）关系基数指在关系的每一端有多少元组。在关系的主键端只能有一个元组，而在外键端可以有多个元组。（3）关系的可选性：可选关系和强制关系对于数据库的完整性非常重要。（4）绘制数据模型关系图ChenER关系图绘制方法非常简单，用sisio即可。（5）关系种类一对多一对一超类/子类多对多（6）类别实体有时也称为查找表，（7）自反关系在两个相同类型的实体之间建立关系，比如人与人之间的上下级关系，家庭成员之间的关系等。3.5规范化规范化是减少更新异常的工具。（1）实体/属性的设计原则：每个数据库实体必须描述一个“事务”属性和事务之间必须存在一对一的关系每个属性必须描述其所属实体，而不能描述其相关的实体。（2）范式规范化通常以范式的方式定义。每种范式都描述了设计实体和属性时可能出现的错误，并提供了纠正这种错误的规则。因此范式类似于书面语言的语法规则。规范化数据库与平面文件数据库比，优点如下：消除重复存储减少锁的争用缩小文件数据建模人员应该一开始就按照第三范式设计逻辑架构。(3)第一范式符合条件A每个数据单元都是用标量属性表示的，标量值指的是“可以用标尺上的一个点表示”的值B所有数据必须用唯一属性表示，每个属性都必须有唯一的名称和用途。C所有的数据都必须用唯一的元组表示。（4）第二范式确保每个属性是实体属性。每个属性都必须有对应的主键。如果其他属性只依赖于主键的一个属性，而不依赖于主键的其他属性，这被称为部分依赖，违反了第二范式。（5）第三范式检查传递依赖。传递依赖和部分依赖的相似之处在于，他们值得都是属性不完全依赖于主键。当A依赖于B，B依赖于主键时，就发生了传递依赖。同第二范式一样，解决方式是将非依赖属性移到新实体中。*如果实体有很好的主键，每个属性都是标量且都完全依赖于主键，则逻辑设计符合第三方式，大多数数据库设计满足第三范式即可。（6）BCNF范式位于三四范式之间，用于处理实体有两组主键的问题。BCNF规定，在这种情况下，应将实体拆分为两个实体，每个实体包含一个主键。（7）第四范式用于处理复杂的符合主键导致的问题，如果将两个相互独立的属性组合成主键，同时还要有第三个属性，如果没有第三个属性，仅靠着两个属性不能唯一标识实体，则违反了第四范式。（8）第五范式用于设计涉及多个（3个以上）实体的复杂关系，如果设计正确可以设计出三元关系，其中每个关系的基数都可以是一或多。3.6关系代数关系代数有8个关系运算符组成（1）限制：返回符合制定条件的行（2）投影：从数据集中返回指定列（3）积：关系乘法，返回两个数据集的所有可能的数据集合。（4）联合：关系加法和减法，将一个表垂直堆叠到另一个表上，将两个表合并（5）交集：返回两个表中都有的行（6）差集：返回一个数据集中特有的行（7）连接：返回两个表的水平合并结果，合并是根据都有的行来匹配（8）除：返回两个数据集中完全匹配的内容另外sqlserver还开发以下功能：子查询在关系代数中，使用以下术语：表或数据集被称为关系或实体行被称为元组列被称为属性SQLserver基础理论学习：SQLserver2005体系结构4SQLserver2005体系结构4.1数据访问体系结构sqlserver首先是一个数据库服务器（1）客户/服务器数据库模型客户/服务器数据库介绍：客户进程请求服务器进程提供服务，后者处理客户发出的请求。所有的数据库工作都是在数据库服务器中执行的，完成后讲数据集的拷贝发送到客户端，实际的数据和索引不会离开服务器。相对于桌面数据库（acess），它性能更高，更安全可靠。客户/服务器数据库职能数据库服务器职责：A处理数据修改和检索要求B执行数据密集型处理C实施所有的数据库规则和约束D实现数据安全性数据库客户端职责：A以易于识别、引人入胜和适用的格式将数据呈现给用户B提供使用各种工具、数据和报表的界面C将请求提交给服务器（2）N层设计在客户/服务器应用程序中，除了客户端和服务器进程外，还涉及其他进程，经常使用中间层来处理连接、连接池和业务逻辑。使用中间层可以让多个用户使用为数不多的数据库服务器连接，到要考虑用户的身份验证方