guo第4章表的存储原理及完整性创建管理.ppt

1,计算机学院郭占龙,第4章表的存储原理及完整性创建管理,4.1SQLServer表的类型4.2表的存储原理4.3SQLServer数据类型4.4数据表的创建和管理,2,计算机学院郭占龙,4.1SQLServer表的类型,4.1.1SQLServer的临时表4.1.2SQLServer的系统表,3,计算机学院郭占龙,4.1.1SQLServer的临时表,SQLServer中的数据表分为：永久表：创建后一直存储在数据库文件中，直到用户删除为止。临时表临时表分两种：局部临时表：表名用#开头。只能由创建它的用户使用，在该用户连接断开时，它被自动删除。全局临时表：表名用#开头。当前所有连接用户都可以使用，它在最后一个会话结束时被自动删除。系统根据表名前有无#符号确定创建的是临时表还是永久表。,4,计算机学院郭占龙,4.2.2SQLServer的系统表,一些系统表只存在于master数据库，它们包含系统级信息。在SQLServer2000里共有18个表，在SQLServer2005里对应20个视图。而一些系统表则存在于每一个数据库（包括master数据库），它们包含属于这个特定数据库的对象和资源的相关信息。在SQLServer2000里共有17个表，在SQLServer2005里对应22个视图。注意：不允许使用SQL语句直接修改系统表中的内容；不允许编写程序直接访问系统表中的信息；如果需要系统表信息，可以通过系统的存储过程和系统提供的函数进行。,5,计算机学院郭占龙,4.2表的存储原理,4.2.1内部存储概述4.2.2SQLServer数据记录结构,6,计算机学院郭占龙,4.1.1内部存储概述,表是有关某个特定实例的数据集合，在关系数据库中处于核心地位。创建一个表，就会有一行或多行插入到用来管理这个表的多个系统表里。至少要写信息到sysobjects、sysindexes和syscolumns这三个系统表里，当新建的表有外码约束时，相关的信息还会插入到sysrefrences系统表里。,7,计算机学院郭占龙,Sysobjects主要记录新表的基本信息，如表名、对象ID以及表的所有者等等。Syscolumns主要记录新表列的信息，如列名、类型和长度等。Sysindexes系统表记录包含指向新表所使用的存储空间的指针和有关新表大小的信息。sysrefrences系统表里记录参照表的外键信息。例如：创建一个表，它在系统表中的记录信息如下图所示：,8,计算机学院郭占龙,CREATETABLE课程表(课号CHAR(6)NOTNULL,课名CHAR(20)NOTNULL,教材名称CHAR(20)NULL,编著者CHAR(10)NULL,出版社CHAR(20)NULL,版号CHAR(15)NULL,定价MONEYNULL，PRIMARYKEY(课号),9,计算机学院郭占龙,注意：,当一个新表刚创建时，在插入第一行数据之前系统不会立即为其分配存储空间，因此Sysindexes表中指示页地址和预留存储空间的列将都是0值。如果在该表上定义了PRIMARYKEY或UNIQUE约束，而PRIMARYKEY或UNIQUE约束的背后是由簇集索引支持的，则该表在Sysindexes中对应行的indid值为1。任何有非簇集索引支持的其他约束在Sysindexes表中都有一行，而且该行的indid值在2到250之间。,10,计算机学院郭占龙,4.2.2数据行（记录）结构,在SQLServer中，数据存储的基本单位是页。紧跟着页头的就是存储表的真正数据行区域。单个数据行的最大长度是8060字节。数据行不能跨页存储（文本和图像例外）。页内数据行的多少依赖于表的结构和要存储的数据。如果一个表的所有列都是定长，那么该表在每一页上存储相同数目的行。如果一个表里有变长列，那么该表总是在每一页上存储尽可能多的行。,11,计算机学院郭占龙,4.2.2数据行（记录）结构,1、定长记录在定长记录里，每个字段都有固定的长度，而且字段数也是固定的。这种记录的字段能够连续存储，因此在给定记录地址的情况下，借助系统目录里有关字段的长度信息就能计算出某个特殊字段的的地址。2、变长记录对于变长记录来说，一种可能的记录组织方式就是像定长记录一样连续地存放字段，字段之间通过分割符隔开。这种组织方式需要扫描记录才能定位需要访问的字段。另一种方法是在记录开始处预留一些空间作为存放一个整数偏移量数组的空间，数组中的第I个整数表示记录的第I个字段的其始地址，当然这个地址是相对于记录的起始地址的。另外在数组中也存储记录尾部的偏移量，这个偏移量用来识别最后一个字段的结束位置。,12,计算机学院郭占龙,1、SQLServer定长记录的存储,首先来看最简单的情况，记录中所有字段都是定长的。CREATETABLEFixed(col1intNOTNULLcol2char(5)NOTNULLcol3char(3)NULLcol4floatNOTNULL)当这个表被创建以后，就有类似下面一个记录被插入到sysindexes系统表中：idnameindidfirstminlen2099048Fixed00 x00000000000024而其各个字段则会被插入到syscolumns系统表中：namecolidxtypelengthxoffsetcol115644col2217558col33175313col4462816,13,计算机学院郭占龙,1、SQLServer定长记录的存储,当往Fixed表中插入一个记录数据时，例如：INSERTINTOFixedVALUES(123,ABCD,NULL,45.5)Fixed表在sysindexes系统表里的内容就会发生变化：idnameindidfirstminlen2099048Fixed00 x72000000000024这说明在插入了一记录数据之后，SQLServer就为Fixed表分配了一个数据页。Fixed表只包含四个定长字段，sysindexes表中minlen字段的值表示记录的最小长度，该长度恰好是syscolumns表中表示字段长度的length的数字之和再加上4个字节。其中额外的4个字节是用于记录字段数目的两个字节和表示字段中NULL的字节数。,14,计算机学院郭占龙,第一个字节是状态位A，它的值是0 x10，表示只有位4是1，其他位都是0，因此该记录没有变长字段（如果位5为1说明存在变长字段）。最后一个字节是NULL位图，其值4意味只有第三位是1，表示第三个字段是NULL。,1、SQLServer定长记录的存储,15,计算机学院郭占龙,2、SQLServer变长记录的存储,CREATETABLEVariable(col1char(3)NOTNULLcol2varchar(15)NOTNULLcol3varchar(5)NULLcol4varchar(10)NOTNULLcol5smallintNOTNULL)当这个表被创建以后，就有类似下面一个记录被插入到sysindexes系统表中：idnameindidfirstminlen18099105Variable00 x0000000000009而其各个字段则会被插入到syscolumns系统表中：namecolidxtypelengthxoffsetcol1117534col2216715-1col331675-1col4416710-1col555227,16,计算机学院郭占龙,2、SQLServer变长记录的存储,当往Variable表中插入一个记录数据时，例如：INSERTINTOVariableVALUES(xyz,ABCDe,NULL,123,999)Variable表在sysindexes系统表里的内容就会发生变化：idnameindidfirstminlen18099105Variable00 x8800000000009定长字段的数据位于记录中由syscolumns的xoffset值指定的字节偏移量所在的位置，即col1起始于字节偏移量4的位置，而col5起始于字节偏移量7的位置。,17,计算机学院郭占龙,为了找到变长字段，首先要确定记录中列偏移数组的位置。在表示总字段数的2个字节（其值是0500）和表示位图的1字节（其值为04）之后就是变长字段数的两个字节，在本例中其值是0300，换算成十进制是3，说明该记录有3个变长字段存在。紧跟其后的字节就是变长字段偏移数组。该例变长字段偏移数组用三个2字节来表示3个变长字段在记录中的结束位置。1900经过字节交换是0 x0019，所以第一个变长字段结束于25字节处。接下来也是0 x0019，所以第二个变长字段实际长度为0，表明没有任何东西存储在变长数据区域。1c00经过字节交换是0 x001c，所以第三个变长字段结束于28字节处，而且整个记录也结束于28字节处，换句话说，目前整个记录的实际长度是28个字节长。,18,计算机学院郭占龙,4.3SQLServer数据类型,4.3.1数值型数据4.3.2货币型数据4.3.3字符型数据4.3.4日期/时间数据类型4.3.5二进制数据类型4.3.5双字节数据类型,19,计算机学院郭占龙,4.3.1数值型数据,Bigint。可以存放从-263到263-1范围内的整型数据。以bigint数据类型存储的每个值占用8个字节，共64位，其中63位用于存储数字，1位用于表示正负。Int。也可以写作integer，可以存储从-231到231-1范围内的全部整数。以int数据类型存储的每个值占用4个字节，共32位，其中31位用于存储数字，1位用于表示正负的区别。Smallint。可以存储从-215到215-1范围内的所有整数。以smallint数据类型存储的每个值占用2个字节，共16位，其中15位用于存储数字，1位用于表示正负的区别。Tinyint。可以存储0到255范围内的所有整数。以tinyint数据类型存储的每个值占用1个字节。Decimal和Numeric。在SQLServer中，decimal和numeric型数据的最高精度的可以达到38位，也就是说必须在-1038-1到1038-1之间。格式为：Decimal（n,d）或Numeric（n,d），其中n为总的位数，d为小数位数。float和real。float型数据范围从-1.79E+38到1.79E+38，Real型数据范围从-3.40E+38到3.40E+38。其中float可采用科学记数法表示，格式为：float（n），n必须在153之间。,20,计算机学院郭占龙,4.3.2货币型数据,Money。它存储的货币值由2个4字节整数构成。前面的一个4字节表示货币值的整数部分，后面的一个4字节表示货币值的小数部分。以Money存储的货币值的范围从-263到263-1，可以精确到万分之一货币单位。Smallmoney。它存储的货币值由2个2字节整数构成。前面的一个2字节表示货币值的整数部分，后面的一个2字节表示货币值的小数部分。以Smallmoney存储的货币值的范围从-214,748.3648到+214,748.3647，也可以精确到万分之一货币单位。,21,计算机学院郭占龙,4.3.3字符型数据,Char。利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。利用Char数据类型来定义表列或者定义变量时，应该给定数据的最大长度。如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用单引号（）将字符型常量括起来。Varchar。Varchar数据类型的使用方式与Char数据类型类似。SQLServer利用Varchar数据类型来存储最长可以达到8000字符的变长字符。与Char数据类型不同，Varchar数据类型的存储空间随存储在表列中的每一个数据的字符数的不同而变化。Text。当要存储的字符型数据非常庞大以至于8000字节完全不够用时，Char和Varchar数据类型都失去了作用。这时应该选择Text数据类型。Text数据类型专门用于存储数量庞大的变长字符数据。最大长度可以达到231-1个字符，约2GB。,22,计算机学院郭占龙,4.3.4日期/时间数据类型,Datetime。Datetime数据类型范围从1753年1月1日到9999年12月31日，可以精确到千分之一秒。Datetime数据类型的数据占用8个字节的存储空间。Smalldatetime。Smalldatetime数据范围从1900年1月1日到2079年6月6日，可以精确到分。Smalldatetime数据类型占4个字节的存储空间。,23,计算机学院郭占龙,4.3.5二进制数据类型,所谓二进制数据是一些用十六进制来表示的数据。例如，十进制数据245表示成十六进制数据就应该是F5。（1）binary。具有固定的长度，最大长度可以达到8K字节。（2）varbinary。具有不固定的长度，其最大长度也不得超过8K字节。（3）Image。该数据类型可用于存储字节数超过8K字节的数据，比如MicrosoftWord文档、MicrosoftExcel图表以及图像数据（包括.GIF、.BMP、.JPEG文件）等。,24,计算机学院郭占龙,4.3.5双字节数据类型,（1）Nchar(n)。Nchar(n)是固定长度的双字节数据类型，括号里的n用来定义数据的最大长度。n的取值范围是14000。（2）Nvarchar(n)。Nvarchar(n)数据类型存储可变长度的双字节数据类型，括号里的n用来定义数据的最大长度。n的取值为04000。（3）Ntext(n)。Ntext数据类型存储的是可变长度的双字节字符，Ntext数据类型突破了前2种双字节数据类型不能超过4000字符的规定，最多可以存储多达230-1个双字节字符。,25,计算机学院郭占龙,4.4数据表的创建和管理,4.4.1数据表结构的创建4.4.2数据表结构的管理,26,计算机学院郭占龙,4.4.1数据表结构的创建,在SQLServer2005中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。SQLServer2000提供了两种方法创建数据库表：第一种方法是利用企业管理器（EnterpriseManager）创建表；另一种方法是利用Transact-SQL语句中的create命令创建表。,27,计算机学院郭占龙,1.利用create命令创建表,其语法形式如下：CREATETABLE(列级完整性约束条件,列级完整性约束条件,),28,计算机学院郭占龙,1.利用create命令创建表,参数说明：（1）是所要定义的基本表的名字。一个表它可以由一个或多个属性组成。（2）一般取有实际意义的名字。（3）可以是前面介绍的数据类型。（4）在SQLServer2000中有下面几种完整性约束条件：空值约束（NULLorNOTNULL）；主键约束（primarykeyconstraint）；唯一性约束（uniqueconstraint）；检查约束（checkconstraint）；缺省约束（defaultconstraint）；外部键约束（foreignkeyconstraint）；规则（rule）；缺省值（default）。,29,计算机学院郭占龙,2关于创建表时运用约束的说明,（1）空值约束（NULLorNOTNULL）空值NULL约束决定属性值是否允许为空值（NULL）。NULL表示没有输入任何内容，它不是零和空白，不允许为空值则用NOTNULL表示。例如：设置属性teacher允许为空值。CREATETABLEt(teacherchar(8)NULL),30,计算机学院郭占龙,2关于创建表时运用约束的说明,（2）主键约束（primarykeyconstraint）主健约束要求主健属性取值必须惟一，一个表只能包含一个主健约束。如果没有在主健约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该主健约束用CLUSTERED。主键约束SQL的语法形式如下：CONSTRAINT约束名PRIMARYKEYCLUSTERED|NONCLUSTERED（列名,n）,31,计算机学院郭占龙,例如：在执行创建产品信息表的操作时，指定产品编号为主键值。CREATETABLEproducts(idchar(10)NOTNULL,namechar(20)NOTNULL,pricemoney，quantitysmallintNULL,CONSTRAINTpk_productsPRIMARYKEYCLUSTERED(id)此例的主健名称是pk_products，在products表中关于id建立了一个索引排序。,32,计算机学院郭占龙,2关于创建表时运用约束的说明,（3）唯一性约束（uniqueconstraint）唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。创建唯一性约束SQL语句：CONSTRAINT约束名UNIQUECLUSTERED|NONCLUSTERED（列名,n）例：CREATETABLEstudent1(snochar(8),snamechar(16),constraintpk_studentprimarykey(sno),constraintuniq_studentunique(sname),33,计算机学院郭占龙,2关于创建表时运用约束的说明,（4）检查约束（checkconstraint）使用检查约束时，应该注意以下几点：一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。一个表中可以定义多个检查约束。每个CREATETABLE语句中每个字段只能定义一个检查约束。在多个字段上定义检查约束，则必须将检查约束定义为表级约束。检查约束中不能包含子查询。创建检查约束SQL语法格式为：CONSTRAINTconstraint_nameCHECKNOTFORREPLICATION(logical_expression),34,计算机学院郭占龙,例如：创建一个职工信息表，其中输入性别字段值时，只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似01080798654之类的数据，而不能随意输入其他数据。createtableemployee(Idchar(8),namechar(8)CHECK(NAMELIKEA-C,a-cA-Z,a-zA-ZA-Z),sexchar(2),phonenumint,constraintchk_sexcheck（sexin（F,M）defaultF,Constraintchk_phonenumcheck（phonenumlike(010)8,90-90-90-90-90-90-90-9）),35,计算机学院郭占龙,2关于创建表时运用约束的说明,（6）外部键约束外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。当使用外部键约束时，应该考虑以下几个因素：外部键约束提供了字段参照完整性。外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。外部键约束不能自动创建索引，需要用户手动创建。一个表中最多可以有31个外部键约束。在临时表中，不能使用外部键约束。主键和外部键的数据类型必须严格匹配。外键约束SQL的语法形式如下：CONSTRAINT约束名FOREIGNKEY（外键列名）REFERENCES参照表（参照列名）,36,计算机学院郭占龙,外键约束主要用来维护两个表之间的一致性关系。外键的建立主要是通过将一个表的主键所在列包含在另一个表中，这些列就是另一个表的外键。,外键的作用不只是对输入自身的数据进行限制，同时也限制了对主键所在表的数据进行修改。用户无法对主键里的数据进行修改和删除，除非事先删除或修改外键引用的数据。,37,计算机学院郭占龙,例如：学生表S（SNO，SNAME，）中的SNO是S表的主健，课程表C（CNO，CNAME，）中的CNO是C表的主健，而学习表SC（SNO，CNO，GRADE）中的SNO、CNO是SC表的主健，并且SNO和CNO又分别相对于S表和C表是SC表的外健。则创建SC表的SQL语句如下：CREATETABLESC(SNOCHAR(10)NOTNULL,CNOCHAR(6)NOTNULL,GRADETINYINTDEFAULTNULL,CONSTRAINTPK_SCPRIMARYKEY(SNO,CNO),CONSTRAINTFK_SCFOREIGNKEY(SNO)REFERENCESStudent(Sno),FOREIGNKEY(CNO)REFERENCESCourse(Cno),38,计算机学院郭占龙,3数据表结构创建实例,【例4-1】创建数据库“教学管理”的数据表学生基本资料表，数据表结构如表1-3、表的完整性约束如表1-8、表1-9所示。方法一：使用对象资源管理器创建第一步设置STUDENT数据表的结构，步骤如下：在树形目录中找到要建表的数据库。在该数据库上单击鼠标右键，在弹出的快捷菜单中选择“新建”-“表”命令，如图4-4所示。出现创建数据表结构的窗口，如图4-5所示。,39,计算机学院郭占龙,3数据表结构创建实例,图4-4新建数据表,图4-5利用表设计器创建表结构,40,计算机学院郭占龙,3数据表结构创建实例,表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行设置，其中前三项是必须在建表时给出的。完成后，单击工具栏上的【保存】按钮。在出现的选择名称对话框中输入表名：学生。单击【确定】按钮退出。,41,计算机学院郭占龙,3数据表结构创建实例,第二步设置STUDENT表完整性约束，步骤如下：展开企业管理器的数据库“教学管理”，单击“表”节点，选定数据表学生，单击右键，在快捷菜单上，单击“设计表”，出现如图4-5所示的设计表结构窗口。设置主键约束（PRIMARYKEY），光标移到需要设置主键的字段学号，单击工具栏上的主键按钮，学号列名左侧出现“钥匙”图标。如果要设置多属性作为主键，可以通过按住键，用鼠标左键依次单击要选定的列，选定多列后，单击工具栏上的主键按钮。取消主键设置的方法是，选定主键字段，单击主键按钮即可。设置主键后，系统自动建立了一个索引。,42,计算机学院郭占龙,3数据表结构创建实例,在图4-5所示的表设计器上右击，出现快捷菜单，如图4-6所示。选择“索引/键”选项，如图4-7所示，系统设置表学生.学号属性为主键，因此自动在表中建立一个根据学号的值的大小升序排列的索引。设置检查约束（CHECK），学生表定义了三个CHECK约束，第一个约束是学号学号，只能有7个字符构成，其中第一个必须是S，后面全部是数字，逻辑表达式为“（学号LIKES0-90-90-90-90-90-9）”。单击“CHECK约束”选项卡，在“CHECK约束”对话框中新建约束。见图4-8。,43,计算机学院郭占龙,3数据表结构创建实例,图4-6表设置快捷菜单,图4-7“索引/键”属性对话框,44,计算机学院郭占龙,3数据表结构创建实例,图4-8“CHECK约束”属性对话框,45,计算机学院郭占龙,3数据表结构创建实例,建立学号的约束：单击“添加”，系统自动给定一个约束名，可在“标识-（名称）”处改名为“CK_学生表_学号”，然后在“常规-表达式”行单击，出现约束表达式文本框，然后在约束表达式文本框中输入（学号LIKES0-90-90-90-90-90-9）。建立身份证约束：单击“添加”，系统自动给定一个约束名，同上，改名为“CK_学生表_身份证号”，然后在约束表达式文本框中输入：（身份证号LIKE090909090909090909090909090909090909）。建立移动电话的约束与上类似。,46,计算机学院郭占龙,3数据表结构创建实例,方法二：使用SQL命令创建（1）创建表并包含完整性约束定义。CREATETABLE课程表(课号CHAR(6)NOTNULL,课名VARCHAR(30)NOTNULL,学分INTCHECK(学分=1and学分=13AND年龄70）,51,计算机学院郭占龙,4.4.2数据表结构的管理,2数据表结构的删除可以用企业管理器或SQL语句删除基本表。方法一：使用企业管理器选中要删除的数据表，单击右键，在快捷菜单上选择“删除”。在“除去对象”对话框中，单击“全部除去”。方法二：使用SQL命令SQL命令的一般格式为：DROPTABLE,52,计算机学院郭占龙,Thankyouverymuch!,本章结束！,下一章,