第章表的存储原理及性创建管理.ppt
- 文档编号:18705006
- 上传时间:2023-10-13
- 格式:PPT
- 页数:45
- 大小:485KB
第章表的存储原理及性创建管理.ppt
《第章表的存储原理及性创建管理.ppt》由会员分享,可在线阅读,更多相关《第章表的存储原理及性创建管理.ppt(45页珍藏版)》请在冰点文库上搜索。
第4章表的存储原理及完整性创建管理,4.1SQLServer表的类型4.2表的存储原理4.3SQLServer数据类型4.4数据表的创建和管理,4.1SQLServer表的类型,4.1.1SQLServer的临时表4.1.2SQLServer的系统表,4.1.1SQLServer的临时表,SQLServer中的数据表分为:
永久表:
创建后一直存储在数据库文件中,直到用户删除为止。
临时表分两种:
局部临时表:
表名用#开头。
只能由创建它的用户使用,在该用户连接断开时,它被自动删除。
全局临时表:
表名用#开头。
当前所有连接用户都可以使用,它在最后一个会话结束时被自动删除。
系统根据表名前有无#符号确定创建的是临时表还是永久表。
4.1.2SQLServer的系统表和系统视图,系统表中的数据组成了SQLServer系统利用的数据字典,系统表记录所有服务器活动的信息,是维护所有存储在其中的所有对象、数据类型、约束、配置选项等可利用资源的相关信息。
一些系统表和系统视图只存在于master数据库,它们包含系统级信息。
还有一些系统表则存在于每一个数据库(包括master数据库),它们包含属于这个特定数据库的对象和资源的相关信息。
注意:
不允许使用SQL语句直接修改系统表中的内容;不允许编写程序直接访问系统表中的信息;如果需要系统表信息,可以通过系统的存储过程和系统提供的函数进行。
4.2表的存储原理,4.2.1内部存储概述4.2.2数据行(记录)结构,4.2.1内部存储概述,表是有关某个特定实例的数据集合,在关系数据库中处于核心地位。
创建一个表,就会有一行或多行插入到用来管理这个表的多个系统表里。
至少要写信息到sysobjects、sysindexes和syscolumns这三个系统表里,当新建的表有外码约束时,相关的信息还会插入到sysrefrences系统表里。
Sysobjects主要记录新表的基本信息,如表名、对象ID以及表的所有者等等。
Syscolumns主要记录新表列的信息,如列名、类型和长度等。
Sysindexes系统表记录包含指向新表所使用的存储空间的指针和有关新表大小的信息。
sysrefrences系统表里记录参照表的外键信息。
例如:
创建一个表,它在系统表中的记录信息如下图所示:
sysobjects,syscolumns,sysindexes,注意:
当一个新表刚创建时,在插入第一行数据之前系统不会立即为其分配存储空间,因此Sysindexes表中指示页地址和预留存储空间的列将都是0值。
如果在该表上定义了PRIMARYKEY或UNIQUE约束,而PRIMARYKEY或UNIQUE约束的背后是由簇集索引支持的,则该表在Sysindexes中对应行的indid值为1。
任何有非簇集索引支持的其他约束在Sysindexes表中都有一行,而且该行的indid值在2到250之间。
4.2.2数据行(记录)结构,1、定长记录在定长记录里,每个字段都有固定的长度,而且字段数也是固定的。
这种记录的字段能够连续存储,因此在给定记录地址的情况下,借助系统目录里有关字段的长度信息就能计算出某个特殊字段的的地址。
2、变长记录对于变长记录来说,一种可能的记录组织方式就是像定长记录一样连续地存放字段,字段之间通过分割符隔开。
这种组织方式需要扫描记录才能定位需要访问的字段。
另一种方法是在记录开始处预留一些空间作为存放一个整数偏移量数组的空间,数组中的第I个整数表示记录的第I个字段的其始地址,当然这个地址是相对于记录的起始地址的。
另外在数组中也存储记录尾部的偏移量,这个偏移量用来识别最后一个字段的结束位置。
1、SQLServer定长记录的存储,首先来看最简单的情况,记录中所有字段都是定长的。
CREATETABLEFixed(col1intNOTNULLcol2char(5)NOTNULLcol3char(3)NULLcol4floatNOTNULL)当这个表被创建以后,就有类似下面一个记录被插入到sysindexes系统表中:
idnameindidfirstminlen2099048Fixed00x00000000000024而其各个字段则会被插入到syscolumns系统表中:
namecolidxtypelengthxoffsetcol115644col2217558col33175313col4462816,1、SQLServer定长记录的存储,当往Fixed表中插入一个记录数据时,例如:
INSERTINTOFixedVALUES(123,ABCD,NULL,45.5)Fixed表在sysindexes系统表里的内容就会发生变化:
idnameindidfirstminlen2099048Fixed00x72000000000024这说明在插入了一记录数据之后,SQLServer就为Fixed表分配了一个数据页。
Fixed表只包含四个定长字段,sysindexes表中minlen字段的值表示记录的最小长度,该长度恰好是syscolumns表中表示字段长度的length的数字之和再加上4个字节。
其中额外的4个字节是用于记录字段数目的两个字节和表示字段中NULL的字节数。
第一个字节是状态位A,它的值是0x10,表示只有位4是1,其他位都是0,因此该记录没有变长字段(如果位5为1说明存在变长字段)。
最后一个字节是NULL位图,其值4意味只有第三位是1,表示第三个字段是NULL。
1、SQLServer定长记录的存储,2、SQLServer变长记录的存储,CREATETABLEVariable(col1char(3)NOTNULLcol2varchar(15)NOTNULLcol3varchar(5)NULLcol4varchar(10)NOTNULLcol5smallintNOTNULL)当这个表被创建以后,就有类似下面一个记录被插入到sysindexes系统表中:
idnameindidfirstminlen18099105Variable00x0000000000009而其各个字段则会被插入到syscolumns系统表中:
namecolidxtypelengthxoffsetcol1117534col2216715-1col331675-1col4416710-1col555227,2、SQLServer变长记录的存储,当往Variable表中插入一个记录数据时,例如:
INSERTINTOVariableVALUES(xyz,ABCDe,NULL,123,999)Variable表在sysindexes系统表里的内容就会发生变化:
idnameindidfirstminlen18099105Variable00x8800000000009定长字段的数据位于记录中由syscolumns的xoffset值指定的字节偏移量所在的位置,即col1起始于字节偏移量4的位置,而col5起始于字节偏移量7的位置。
为了找到变长字段,首先要确定记录中列偏移数组的位置。
在表示总字段数的2个字节(其值是0500)和表示位图的1字节(其值为04)之后就是变长字段数的两个字节,在本例中其值是0300,换算成十进制是3,说明该记录有3个变长字段存在。
紧跟其后的字节就是变长字段偏移数组。
该例变长字段偏移数组用三个2字节来表示3个变长字段在记录中的结束位置。
1900经过字节交换是0x0019,所以第一个变长字段结束于25字节处。
接下来也是0x0019,所以第二个变长字段实际长度为0,表明没有任何东西存储在变长数据区域。
1c00经过字节交换是0x001c,所以第三个变长字段结束于28字节处,而且整个记录也结束于28字节处,换句话说,目前整个记录的实际长度是28个字节长。
4.3SQLServer数据类型,4.3.1数值型数据4.3.2货币型数据4.3.3字符型数据4.3.4日期/时间数据类型,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之间。
4.3.2货币型数据,Money。
它存储的货币值由2个4字节整数构成。
前面的一个4字节表示货币值的整数部分,后面的一个4字节表示货币值的小数部分。
以Money存储的货币值的范围从-263到263-1,可以精确到万分之一货币单位。
Smallmoney。
它存储的货币值由2个2字节整数构成。
前面的一个2字节表示货币值的整数部分,后面的一个2字节表示货币值的小数部分。
以Smallmoney存储的货币值的范围从-214,748.3648到+214,748.3647,也可以精确到万分之一货币单位。
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。
4.3.4日期/时间数据类型,Datetime。
Datetime数据类型范围从1753年1月1日到9999年12月31日,可以精确到千分之一秒。
Datetime数据类型的数据占用8个字节的存储空间。
Smalldatetime。
Smalldatetime数据范围从1900年1月1日到2079年6月6日,可以精确到分。
Smalldatetime数据类型占4个字节的存储空间。
4.4数据表的创建和管理,4.4.1数据表结构的创建4.4.2数据表结构的管理,4.4.1数据表结构的创建,在SQLServer2000中,每个数据库中最多可以创建200万个表,用户创建数据库表时,最多可以定义1024列,也就是可以定义1024个字段。
SQLServer2000提供了两种方法创建数据库表:
第一种方法是利用企业管理器(EnterpriseManager)创建表;另一种方法是利用Transact-SQL语句中的create命令创建表。
1.利用create命令创建表,其语法形式如下:
CREATETABLE(列级完整性约束条件,列级完整性约束条件,),1.利用create命令创建表,参数说明:
(1)是所要定义的基本表的名字。
一个表它可以由一个或多个属性组成。
(2)一般取有实际意义的名字。
(3)可以是前面介绍的数据类型。
(4)在SQLServer2000中有下面几种完整性约束条件:
空值约束(NULLorNOTNULL);主键约束(primarykeyconstraint);唯一性约束(uniqueconstraint);检查约束(checkconstraint);缺省约束(defaultconstraint);外部键约束(foreignkeyconstraint);规则(rule);缺省值(default)。
2关于创建表时运用约束的说明,
(1)空值约束(NULLorNOTNULL)空值NULL约束决定属性值是否允许为空值(NULL)。
NULL表示没有输入任何内容,它不是零和空白,不允许为空值则用NOTNULL表示。
例如:
设置属性teacher允许为空值。
CREATETABLEt(teacherchar(8)NULL),2关于创建表时运用约束的说明,
(2)主键约束(primarykeyconstraint)主健约束要求主健属性取值必须惟一,一个表只能包含一个主健约束。
如果没有在主健约束中指定CLUSTERED或NONCLUSTERED,并且没有为UNIQUE约束指定聚集索引,则将对该主健约束用CLUSTERED。
主键约束SQL的语法形式如下:
CONSTRAINT约束名PRIMARYKEYCLUSTERED|NONCLUSTERED(列名,n),例如:
在执行创建产品信息表的操作时,指定产品编号为主键值。
CREATETABLEproducts(idchar(10)NOTNULL,namechar(20)NOTNULL,pricemoney,quantitysmallintNULL,CONSTRAINTpk_productsPRIMARYKEYCLUSTERED(id)此例的主健名称是pk_products,在products表中关于id建立了一个索引排序。
2关于创建表时运用约束的说明,(3)唯一性约束(uniqueconstraint)唯一性约束用于指定一个或者多个列的组合的值具有唯一性,以防止在列中输入重复的值。
创建唯一性约束SQL语句:
CONSTRAINT约束名UNIQUECLUSTERED|NONCLUSTERED(列名,n)例:
CREATETABLEstudent1(snochar(8),snamechar(16),constraintpk_studentprimarykey(sno),constraintuniq_studentunique(sname),2关于创建表时运用约束的说明,(4)检查约束(checkconstraint)使用检查约束时,应该注意以下几点:
一个列级检查约束只能与限制的字段有关;一个表级检查约束只能与限制的表中字段有关。
一个表中可以定义多个检查约束。
每个CREATETABLE语句中每个字段只能定义一个检查约束。
在多个字段上定义检查约束,则必须将检查约束定义为表级约束。
检查约束中不能包含子查询。
创建检查约束SQL语法格式为:
CONSTRAINTconstraint_nameCHECKNOTFORREPLICATION(logical_expression),例如:
创建一个职工信息表,其中输入性别字段值时,只能接受“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)),2关于创建表时运用约束的说明,(6)外部键约束外部键约束用于强制参照完整性,提供单个字段或者多个字段的参照完整性。
当使用外部键约束时,应该考虑以下几个因素:
外部键约束提供了字段参照完整性。
外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。
外部键约束不能自动创建索引,需要用户手动创建。
一个表中最多可以有31个外部键约束。
在临时表中,不能使用外部键约束。
主键和外部键的数据类型必须严格匹配。
外键约束SQL的语法形式如下:
CONSTRAINT约束名FOREIGNKEY(外键列名)REFERENCES参照表(参照列名),外键约束主要用来维护两个表之间的一致性关系。
外键的建立主要是通过将一个表的主键所在列包含在另一个表中,这些列就是另一个表的外键。
外键的作用不只是对输入自身的数据进行限制,同时也限制了对主键所在表的数据进行修改。
用户无法对主键里的数据进行修改和删除,除非事先删除或修改外键引用的数据。
3数据表结构创建实例,【例4-1】创建数据库“教学管理”的数据表包括学生表、课程表、教师表、开课表和选课表数据表结构如表1-3、表1-4、表1-5、表1-6和表1-7所示,各表的完整性约束如表1-8、表1-9、表1-10、表1-11、表1-12所示。
以学生表创建为例方法一、企业或资源管理器,3数据表结构创建实例,图4-4新建数据表,图4-5利用表设计器创建表结构,3数据表结构创建实例,第一步:
创建表在树状目录中找到要建表的数据库。
在该数据库上单击鼠标右键,在弹出的快捷菜单中选择“新建”“表”命令表设计器的上半部分有一个表格,在这个表格中输入列的属性,表格的每一行对应一列。
对每一列都需要进行设置设置完成后,单击工具栏上的“保存”按钮。
在出现的选择名称对话框中输入表名“学生表”。
单击“确定”按钮退出。
3数据表结构创建实例,第二步:
设置完整性约束展开企业管理器的数据库“教学管理”,单击“表”节点,选定数据表STUDENT,单击右键,在快捷菜单上,单击“设计表”。
设置主键约束(PRIMARYKEY),光标移到需要设置主键的字段学号,单击工具栏上的主键按钮,学号列名左侧出现“钥匙”图标。
如果要设置多属性作为主键,可以通过按住键,用鼠标左键依次单击要选定的列,选定多列后,单击工具栏上的主键按钮。
取消主键设置的方法是,选定主键字段,单击主键按钮即可。
设置主键后,系统自动建立了一个索引。
3数据表结构创建实例,表设计器上右击,出现快捷菜单,在上面单击“属性”,出现属性对话框。
在属性对话框中选择“索引/键”选项,系统设置表学号属性为主键,因此自动在表中建立一个根据学号的值的大小升序排列的索引。
设置检查约束(CHECK),学生表定义了三个CHECK约束,第一个约束是学号,根据约束要求,逻辑表达式为“(snoLIKES0-90-90-90-90-90-9)”,其他两个同上操作。
单击“CHECK约束”选项卡,在“CHECK约束”对话框中新建约束。
3数据表结构创建实例,方法二:
使用SQL命令创建创建课程表的语句如下:
CREATETABLE课程表(课号CHAR(6)NOTNULL,课名VARCHAR(30)NOTNULL,学分INTCHECK(学分=1and学分=5),教材名称VARCHAR(30),编著者CHAR(8),出版社VARCHAR(20),版号VARCHAR(20),定价money,CONSTRAINTPK_课程表_课号PRIMARYKEY(课号),CONSTRAINTCK_课程表_课号CHECK(课号LIKEC0-90-90-90-90-9),3数据表结构创建实例,方法二:
使用SQL命令创建
(2)创建表并包含完整性约束定义,同时定义各个约束名。
CREATETABLECOURSE(cnochar(6)NOTNULL,cnamevarchar(30)NOTNULL,cbnamevarchar(30),cedichar(8)cpupvarchar(20),cisbnvarchar(20),cpricemomey,CONSTRAINTcno_PKPRIMARYKEY(cno),CONSTRAINTcno_CKCHECK(cnoLIKEC0-90-90-90-90-9),4.4.2数据表结构的管理,利用企业管理器修改表结构的过程如图所示。
单击“设计表”命令,将弹出表设计器。
【例4-2】将教学管理数据库中的学生表的属性“sno”的长度改为7,属性“ssex”的长度改为2。
4.4.2数据表结构的管理,使用SQL命令修改表。
语法格式:
ALTERTABLEADD完整性约束|ALTERCOLUMN列名新类型|DROPCOLUMN列名|ADDPRIMARYKEY(列名,)|ADDFOREIGNKEY(列名)REFERENCES表名(列名)|ADDCONSTRAINT约束名.|DROPCONSTRAINT约束名,4.4.2数据表结构的管理,【例4-2】将教学管理数据库中的学生表属性“性别”的长度改为2。
其SQL语句的程序清单如下:
USE教学管理-打开教学管理数据库GOALTERTABLE学生表ALTERCOLUMN性别CHAR
(2),4.4.2数据表结构的管理,2数据表结构的删除可以用企业管理器或SQL语句删除基本表。
方法一:
使用企业管理器选中要删除的数据表,单击右键,在快捷菜单上选择“删除”。
在“除去对象”对话框中,单击“全部除去”。
方法二:
使用SQL命令SQL命令的一般格式为:
DROPTABLE,Thankyouverymuch!
谢谢您的光临!
下一章,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第章表 存储 原理 创建 管理