SQL Server 2005 数据库应用与开发教程

2020年6月9日,第1页,第2章数据库对象的建立和维护,SQLServer2005数据库应用与开发教程清华大学出版社,教学目标教学重点教学过程,2020年6月9日,第2页,教学目标,理解和掌握数据库及数据库对象的概念和特点掌握如何在对象资源管理器中创建数据库掌握如何在对象资源管理器中创建数据库对象,2020年6月9日,第3页,教学重点,数据库的概念及创建系统提供的数据类型使用对象资源管理器创建表如何修改表结构使用对象资源管理器创建索引使用对象资源管理器创建视图,2020年6月9日,第4页,教学过程,数据库表索引视图,2020年6月9日,第5页,2.1数据库,SQLServer2005的数据库是所涉及的对象以及数据的集合。它不仅反映数据本身的内容，而且反映对象以及数据之间的联系。本节主要介绍SQLServer2005的数据库的基本概念及创建、删除、修改数据库的基本操作。,2020年6月9日,第6页,2.1.1数据库的基本概念,1.逻辑存储结构数据库的逻辑存储结构是指数据库是由哪些性质的信息组成的。SQLServer的数据库是由表、视图、索引等各种不同的对象所组成。,2020年6月9日,第7页,2020年6月9日,第8页,2.逻辑存储结构数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的。一个数据库文件至少应包含一个数据库文件和一个事物日志文件。SQLServer2005中每个数据库包括主数据库文件、辅助数据库文件和事物日志文件,2020年6月9日,第9页,2020年6月9日,第10页,一个数据库可以有一个或多个数据库文件，一个数据库文件只属于一个数据库。只能有一个主数据库文件。采用多个数据库文件来存储数据的优点：数据库文件可以不断扩充，而不受操作系统文件大小的限制；可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取，提高了数据处理的效率。对于服务器型的计算机尤为有用。,2020年6月9日,第11页,事务日志文件事务日志文件是用来记录数据库更新情况的文件，扩展名为.ldf。例如使用INSERT、UPDATE、DELETE、等对数据库进行更的操作都会记录在此文件中而如SELECT等对数据库内容不会有影响的操作则不会记录在案。一个数据库可以有一个或多个事务日志文件,2020年6月9日,第12页,SQLServer中采用“Write-Ahead（提前写）”方式的事务，即对数据库的修改先写入事务日志中，再写入数据库。其具体操作是，系统先将更改操作写入事务日志中，再更改存储在计算机缓存中的数据，为了提高执行效率，此更改不会立即写到硬盘中的数据库，而是由系统以固定的时间间隔执行CHECKPOINT命令，将更改过的数据批量写入硬盘。,2020年6月9日,第13页,SQLServer有个特点，它在执行数据更改时会设置一个开始点和一个结束点，如果尚未到达结束点就因某种原因使操作中断，则在SQLServer重新启动时会自动恢复已修改的数据，使其返回未被修改的状态。当数据库破坏时，可以用事务日志恢复数据库内容。,2020年6月9日,第14页,2.1.2数据库的创建,在对象资源管理器中，可以使用图形工具创建数据库，具体步骤略。注意：一个数据库最多可以创建32,767个文件组，文件组不能独立于数据库文件而建立，文件组是管理数据库中一组数据文件的管理机制。文件组中只能包含数据文件，不能包含日志文件。,2020年6月9日,第15页,2.1.3数据库的修改,使用对象资源管理器进入到数据库的属性对话框对数据库进行修改,2020年6月9日,第16页,2.2表,表是数据库中最重要的对象，包含着数据库中的所有数据。在SQLServer2005中一个数据库最多可以存储20亿个表。表由行和列组成，行表示唯一的一个记录，每行最多可以存储8192个字节。列表示数据库的一个属性，每个表最多可以存储1024列。,2020年6月9日,第17页,在考虑数据库的空间分配时，需要了解如下规则：所有数据库都包含一个主数据库文件与一个或多个事务日志文件，此外，还可以包含零个或多个辅助数据库文件。实际的文件都有两个名称：操作系统管理的物理文件名和数据库管理系统管理的逻辑文件名。数据库文件和事务日志文件的默认存放位置为：ProgramFilesMicrosoftSQLServerMSSQLData文件夹。在SQLServer2005中，数据的存储单位是页（Page）。一页是一块8KB（81024字节）的连续磁盘空间，页是存储数据的最小单位。页的大小决定了数据库表的一行数据的最大大小。在SQLServer中，不允许表中的一行数据存储在不同页上，即行不能跨页存储。在SQLServer2005中，一行数据的大小（即各列所占空间的和）不能超过8060字节。,2020年6月9日,第18页,2.2.1表的数据类型,(1)字符型：char、nchar、varchar、nvarchar、text、ntext；(2)数型int、bigint、smallint、tinyint、decimal/numeric、float、real；(3)货币类型：money、smallmoney；(4)时间类型：datetime、smalldatetime；(5)二进制类型：binary、varbinary、image；(6)其他类型：bit、XML、Timestamp、Uniqueidentifier，crusor、sql_variant；,整数类型,数据类型范围存储bigint-263(-9,223,372,036,854,775,808)到263-1(9,223,372,036,854,775,807)8字节int-231(-2,147,483,648)到231-1(2,147,483,647)4字节smallint-215(-32,768)到215-1(32,767)2字节tinyint0到2551字节,字符数据类型,CHAR（n）与VARCHAR（n）：n的取值为1到8000；CHAR（n），若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。VARCHAR（n），VARCHAR数据类型具有变动长度的特性，因为VARCHAR数据类型的存储长度为实际数值长度，若输入数据的字符数小于n，则系统不会在其后添加空格来填满设定好的空间。CHAR数据类型长度固定，它比VARCHAR类型的处理速度快。,3.2.11新数据类型,SQL_VARIANT可以存储除文本、图形数据（TEXT、NTEXT、IMAGE）和TIMESTAMP类型数据外的其它任何合法的SQLServer数据sql_variant的最大长度可以是8016个字节。这包括基类型信息和基类型值。实际基类型值的最大长度是8,000个字节。TABLETABLE数据类型用于存储对表或视图处理后的结果集。这一新类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。,2020年6月9日,第22页,2.2.2创建表,创建一个数据库表主要是对表中的列属性进行定义，注意，同一个表中不允许出现重名列，每一个列名可以长达128个字符，可以包含中文、英文字母、“”号、“￥”货币符号、下划线和符号。在对象资源管理器中，可以使用图形工具创建数据库，具体步骤略。,2020年6月9日,第23页,2.2.3创建约束,约束是SQLserver提供的自动保持数据库完整性的一种方法在SQLserver2005中提供了六种约束：主键约束、唯一性约束、检查约束、默认约束、外键约束和NULL约束。,2020年6月9日,第24页,1.主键约束,主键是最重要的约束类型表中定义主键的列只能包含唯一的值一个表中只能有一个主键且不允许用NULL来定义主键的值可以指定多个列的组合作为主键，这多个列中每一个列都不能出现NULL值，此时一个列中可以出现重复值。但所有列的组合值必须是唯一的。Text和Image数据类型不能被指定为主键。,2020年6月9日,第25页,2.唯一性约束,唯一性约束确保输入到在约束中定义的一个或几个列中只能是唯一值表中定义主键的列只能包含唯一的值一个表中可以定义多个唯一性约束，唯一性约束可以为NULL值当向表中的列添加唯一性约束后，SQLServer2005将自动检查此列中的数值，以保证数据的唯一性,2020年6月9日,第26页,3.检查约束,检查约束即是对表中的数据设置检查条件，来保证数据的完整性一个表中可以定义多个检查约束。,2020年6月9日,第27页,默认约束指用户在进行插入操作时，没有显示为列提供的数据，那么系统将默认值赋给该列，默认值约束所提供的默认值可以为常量、函数、系统函数、空值等，对于表中，每一列只能定义一个默认约束，对于具有IDENTITY属性和timestamp数据类型的字段，不能使用默认约束。同时定义的默认值不允许高过对应字段的允许长度。,4.默认约束,2020年6月9日,第28页,5.外键约束,外键约束是用于强制参照完整性，用来保证相关联的表中的主键或外键的数据保持一致。当一个表中的一列或多列的组合与其他表中定义的主键或唯一性约束相同时，可以将这些列或列的组合定义为外键，并设定它与哪个表中的哪些列相关联。其中包含外键的表，称为从表，包含外键所引用的主键或唯一键的表称为主表一个表中最多可以有31个外键约束,2020年6月9日,第29页,6.空值约束,空值约束即是否允许该字段的值为NULL，表示“不知道”、“不清楚”或“没有数据”的意思。主键不允许为空值。,2020年6月9日,第30页,2.2.6修改、删除表,(1)修改列的数据类型。(2)修改列的数据长度，选择数据类型时，将自动定义长度。只能增加或减少具有binary、char、nchar、varbinary、varchar或nvarchar数据类型的数据长度，其他的数据类型无法更改，如果新指定的数据长度小于原有的数据长度，则列中超过新的数据长度的值将被截断，且无任何警告。无法更改主键约束列的数据长度。(3)修改列的空值性，默认情况下，列允许为空值。,2020年6月9日,第31页,2.3索引,提供以一列或多列的值为基础迅速查找/存取表的行的能力用来快速访问数据库表中的特定信息,索引的结构,索引的B-树结构,2020年6月9日,第33页,2.3.1索引的优点,索引能够大大提高SQL语句的执行速度在执行查询时，SQL能对查询进行优化通过创建唯一索引，可以保证表中的数据不重复缺点存储索引要占用磁盘空间。数据修改需要更长的时间，因为索引也要更新。创建索引要花时间。,2020年6月9日,第34页,2.3.2索引分类,按索引组织方式的不同，可以将索引分为聚集索引和非聚集索引聚集索引1）使用表中的一列或多列来排序记录，然后再重新存储在磁盘上，表的物理行顺序和聚集索引中行的顺序一致。2）一个表只能有一个聚集索引。3）聚集索引一般创建在表中经常搜索的列或按顺序访问的列上。,聚集索引,2020年6月9日,第36页,非聚集索引1）存储指向表数据的指针，称为行定位器2）聚集索引的表中的数据由存储指针的索引页构成3）一个表中可以存储多达249个非聚集索引,非聚集索引,2020年6月9日,第38页,2.3.3索引的创建,在创建索引时，首先要考虑一些设计准则(1)要使得索引最有效，必须使索引与用户访问数据的形式匹配。(2)要保证索引的更新与数据库的更新同步。(3)一般而言，存取表的最常用的方法是通过主键来进行，因此应该在主键上建立索引，同时在连接中频繁使用的外键，也要建立索引。同时，对于经常搜索的列和安排序频繁检索的列，也应当建立索引。(4)只有两个或若干个较少值得列和行数较少的表不要创建索引。(5)对表进行大批量的插入和更新时，应先删除索引，待插入和更新完成后，再重新建立。,2.3.3索引的创建,注意：数据类型为TEXT、NTEXT、IMAGE或BIT的列不能作为索引的列。由于索引的宽度不能超过900个字节，因此数据类型为CHAR、VARCHAR、BINARY和VARBINARY的列的列宽度超过了900字节，或数据类型为NCHAR、NVARCHAR的列的列宽度超过了450个字节时也不能作为索引的列。,2020年6月9日,第40页,2.4视图,是从一个或多个表或视图中导出的虚表由若干个字段（列）和一些记录组成，可以通过视图来插入、更改和删除数据视图在数据库中存储的是视图的定义，而不是查询的数据。视图中的数据在视图所引用的表中，而不是实际地以视图结构存储在数据库中,2020年6月9日,第41页,视图的优点1、视图是作为一个数据库对象存在数据库中的，便于管理和维护，且视图象表一样还可以用在查询语句中，从而简化了检索数据的操作。2、可以定制允许用户查看哪些数据，让用户通过视图来访问表中特定字段和记录3、可以针对不同的用户定义不同的视图，在用户视图上不包括机密数据字段，从而自动提供对机密数据的保护。4、可以使用视图将数据导出到其它的应用程序5、允许用户以不同的方式查看数据，即使在他们同时使用相同的数据也可如此。,2020年6月9日,第42页,2.4.1视图的分类,视图分为标准视图、索引视图和分区视图。标准视图是视图的标准形式，标准视图组合了一个或多个表中的数据，用户可以通过标准视图对数据库进行查询、修改、插入和删除数据等操作。索引视图是通过计算并存储的视图，索引视图可以提高某些类型查询的性能，适合同一时间对多行的查询，还可以对其创建一个惟一的聚集索引。分区视图是用户可以把来自不同表的两个或多个查询结果组合成单一的结果集，在用户看来是一个单独的表。,2020年6月9日,第43页,2.4.2视图的创建,建立视图是还要注意以下几点：建立视图时必须遵循标识符命名规则一个视图最多可以引用1024个字段视图可以在其他视图上建立。SQLServer允许视图最多嵌套32层。即使删除了一个视图所依赖的表或视图，这个视图的定义仍然保留在数据库中。不能在视图上定义全文索引。不能在视图上绑定规则、默认值和触发器。不能建立临时视图，也不能在一个临时表上建立视图。只能在当前数据库中创建视图，但是视图所引用的表或视图可以是其他数据库中的，甚至可以是其他服务器上的。,2020年6月9日,第44页,2.4.5通过视图更改记录,通过视图可以对创建视图的基表进行修改，如插入新的记录、更新新的记录及删除记录等。使用视图修改数据时，需要注意：插入视图中的列值个数、数据类型应该和视图定义中的列数、基础表对应列的数据类型保持一致。修改视图中的数据时，不能同时修改两个或多个基表，可以对基于两个或多个基表或视图进行修改，但每次只能修改都只能影响到一个基表。不能修改通过计算得到的字段。,2020年6月9日,第45页,2.5如何估计数据库的大小,在设计数据库时，可能需要估计填入数据后数据库的大小。估计数据库的大小可以帮助您确定执行下列操作所需的硬件配置：获得应用程序所需的性能。保证有足够的物理磁盘空间用于存储数据和索引。估计数据库的大小还可以帮助您确定是否需要修改数据库设计。例如，您可能会发现估计的数据库大小太大，无法在您的单位中实现，因此需要进行更多的规范化处理。相反，也可能估计大小比需要的更小。这就允许您降低数据库的规范化以提高查询性能。若要估计数据库的大小，请分别估计每个表的大小，然后将各个值累加起来即可。表的大小取决于表是否有索引，如果有索引，还取决于索引的类型。,2020年6月9日,第46页,估计表的大小,可以使用下列步骤估计在表中存储数据所需的空间：按照估计堆的大小或估计聚集索引的大小中的说明计算堆或聚集索引所需的空间。对于每个非聚集索引，按照估计非聚集索引的大小中的说明计算其所需的空间。对步骤1和步骤2中计算的值求和。,2020年6月9日,第47页,估计堆的大小,可以使用以下步骤估计在堆中存储数据所需的空间量：1。指定表中显示的行数：Num_Rows=表中的行数指定固定长度和可变长度列的数量，并计算存储所需的空间：计算每组列在数据行中所占据的空间。列的大小取决于数据类型和长度说明。有关详细信息，请参阅数据类型（数据库引擎）。Num_Cols=总列数（固定长度和可变长度）Fixed_Data_Size=所有固定长度列的总字节大小Num_Variable_Cols=可变长度列的数量,2020年6月9日,第48页,Max_Var_Size=所有可变长度列的最大字节大小保留行中称为空位图的部分以管理列的为空性。计算大小：Null_Bitmap=2+(Num_Cols+7)/8)只应使用该表达式的整数部分。而去掉其余部分。,2020年6月9日,第49页,2。计算可变长度数据的大小：如果表中有可变长度列，请确定在行