表的存储原理及完整性创建管理.ppt

《表的存储原理及完整性创建管理.ppt》由会员分享，可在线阅读，更多相关《表的存储原理及完整性创建管理.ppt（53页珍藏版）》请在三一文库上搜索。

1、优秀精品课件文档资料,SQL Server,第4章 表的存储原理及完整性创建管理,第4章 表的存储原理及完整性创建管理,4.1 表的存储原理 4.2 SQL Server表的类型 4.3 SQL Server数据类型 4.4 数据表的创建和管理,4.1 表的存储原理,4.1.1 内部存储概述 4.1.2 数据行（记录）结构,4.1.1 内部存储概述,表是有关某个特定实例的数据集合，在关系数据库中处于核心地位。 创建一个表，就会有一行或多行插入到用来管理这个表的多个系统表里。例如要写信息到 sysobjects、sysindexes和syscolumns等系统表里。 通过查询sys.tables

2、、sys.objects、sys.indexes、和sys.columns等系统视图可以查看相关信息。,Sysobjects主要记录新对象的基本信息，如对象名、对象ID等等。 Syscolumns主要记录新表列的信息，如列名、类型和长度等。 Sysindexes系统表记录索引等相关信息。,注意：,当一个新表刚创建时，在插入第一行数据之前系统不会立即为其分配存储空间，因此Sysindexes表中指示页地址和预留存储空间的列将都是0值。 如果在该表上定义了PRIMARY KEY或UNIQUE约束，而PRIMARY KEY或UNIQUE约束的背后是由簇集索引支持的，则该表在Sysindexes中对应

3、行的index_id值为1。 任何有非簇集索引支持的其他约束在Sysindexes表中都有一行，而且该行的index_id值在2到250之间。,4.1.2 数据行（记录）结构,在 SQL Server 中，数据存储的基本单位是页。 紧跟着页头的就是存储表的真正数据行区域。单个数据行的最大长度是8060字节。 数据行不能跨页存储（文本和图像例外）。页内数据行的多少依赖于表的结构和要存储的数据。 如果一个表的所有列都是定长，那么该表在每一页上存储相同数目的行。 如果一个表里有变长列，那么该表总是在每一页上存储尽可能多的行。,4.1.2 数据行（记录）结构,1、定长记录 在定长记录里，每个字段都有固

4、定的长度，而且字段数也是固定的。这种记录的字段能够连续存储，因此在给定记录地址的情况下，借助系统目录里有关字段的长度信息就能计算出某个特殊字段的地址。 2、变长记录 对于变长记录来说，一种可能的记录组织方式就是像定长记录一样连续地存放字段，字段之间通过分割符隔开。这种组织方式需要扫描记录才能定位需要访问的字段。 另一种方法是在记录开始处预留一些空间作为存放一个整数偏移量数组的空间，数组中的第I个整数表示记录的第I个字段的其始地址，当然这个地址是相对于记录的起始地址的。另外在数组中也存储记录尾部的偏移量，这个偏移量用来识别最后一个字段的结束位置。,1、 SQL Server定长记录的存储,首先来

5、看最简单的情况，记录中所有字段都是定长的。 CREATE TABLE Fixed ( col1 int NOT NULL, col2 char(5) NOT NULL, col3 char(3) NULL, col4 float NOT NULL ) 当这个表被创建以后，就有一个记录被插入到systables系统表中; 而其各个字段则会被插入到syscolumns系统表中。,第一个字节是状态位A，它的值是0x10，表示只有位4是1，其他位都是0，因此该记录没有变长字段（如果位5为1说明存在变长字段）。 最后一个字节是NULL位图，其值4意味只有第三位是1，表示第三个字段是NULL。,1、 SQ

6、L Server定长记录的存储,2、 SQL Server变长记录的存储,CREATE TABLE Variable ( col1 char(3) NOT NULL, col2 varchar(15) NOT NULL, col3 varchar(5) NULL, col4 varchar(10) NOT NULL, col5 smallint NOT NULL ) 当这个表被创建以后，就有一个记录被插入到systables系统表中; 而其各个字段则会被插入到syscolumns系统表中。,2、 SQL Server变长记录的存储,往Variable表中插入一个记录数据时，例如： INSERT

7、 INTO Variable VALUES(xyz,ABCDe,NULL, 123,999),为了找到变长字段，首先要确定记录中列偏移数组的位置。在表示总字段数的2个字节（其值是0500）和表示位图的1字节（其值为04）之后就是变长字段数的两个字节，在本例中其值是0300，换算成十进制是3，说明该记录有3个变长字段存在。紧跟其后的字节就是变长字段偏移数组。该例变长字段偏移数组用三个2字节来表示3个变长字段在记录中的结束位置。1900经过字节交换是0x0019，所以第一个变长字段结束于25字节处。接下来也是0x0019，所以第二个变长字段实际长度为0，表明没有任何东西存储在变长数据区域。1c00

8、经过字节交换是0x001c，所以第三个变长字段结束于28字节处，而且整个记录也结束于28字节处，换句话说，目前整个记录的实际长度是28个字节长。,4.2 SQL Server表的类型,4.2.1 SQL Server的临时表 4.2.2 SQL Server的系统表,4.2 SQL Server表的类型,SQL Server中的数据表分为： 永久表：创建后一直存储在数据库文件中，直到用户删除为止。 临时表：本地临时表和全局临时表 系统根据表名前有无#符号确定创建的是临时表还是永久表。,4.2.1 SQL Server的临时表,临时表分两种： 本地临时表：表名用#开头。只能由创建它的用户使用，在

9、该用户连接断开时，它被自动删除。 全局临时表：表名用#开头。当前所有连接用户都可以使用，它在最后一个会话结束时被自动删除。,4.2.2 SQL Server的系统表,系统表中的数据组成了SQL Server系统利用的数据字典，系统表记录所有服务器活动的信息，是维护所有存储在其中的所有对象、数据类型、约束、配置选项等可利用资源的相关信息。 一些系统表只存在于master数据库，它们包含系统级信息。 而一些系统表则存在于每一个数据库（包括master数据库），它们包含属于这个特定数据库的对象和资源的相关信息。 注意：不允许使用SQL语句直接修改系统表中的内容；如果需要系统表信息，可以通过系统的存储

10、过程和系统提供的函数进行。,4.3 SQL Server数据类型,4.3.1 数值型数据 4.3.2 货币型数据 4.3.3 字符型数据 4.3.4 日期/时间数据类型 4.3.5 二进制数据类型 4.3.5 双字节数据类型,4.3.1 数值型数据,Bigint。可以存放从-263到263-1范围内的整型数据。以bigint数据类型存储的每个值占用8个字节，共64位，其中63位用于存储数字，1位用于表示正负。 Int。也可以写作integer，可以存储从-231到231-1范围内的全部整数。以int数据类型存储的每个值占用4个字节，共32位，其中31位用于存储数字，1位用于表示正负的区别。 S

11、mallint。可以存储从-215到215- 1范围内的所有整数。以smallint数据类型存储的每个值占用2个字节，共16位，其中15位用于存储数字，1位用于表示正负的区别。 Tinyint。可以存储0到255范围内的所有整数。以tinyint数据类型存储的每个值占用1个字节。 Decimal和Numeric。在SQL Server中，decimal和numeric型数据的最高精度的可以达到38位，也就是说必须在-1038-1到1038-1之间。格式为：Decimal（n,d）或Numeric（n,d），其中n为总的位数，d为小数位数。 float和real。float型数据范围从-1.79

12、E+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.3

13、648到+214,748.3647，也可以精确到万分之一货币单位。,4.3.3 字符型数据,Char。利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。利用Char数据类型来定义表列或者定义变量时，应该给定数据的最大长度。如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用单引号（）将字符型常量括起来。 Varchar。Varchar数据类型的使用方式与Char数据类型类似。SQL S

14、erver 利用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数据

15、类型的数据占用8个字节的存储空间。 Smalldatetime。Smalldatetime数据范围从1900年1月1日到2079年6月6日，可以精确到分。Smalldatetime数据类型占4个字节的存储空间。,4.3.5 二进制数据类型,所谓二进制数据是一些用十六进制来表示的数据。例如，十进制数据245表示成十六进制数据就应该是F5。 （1）binary。具有固定的长度，最大长度可以达到8K字节。 （2）varbinary。具有不固定的长度，其最大长度也不得超过8K字节。 （3）Image。该数据类型可用于存储字节数超过8K字节的数据，比如Microsoft Word文档、Microsoft

16、 Excel图表以及图像数据（包括.GIF、.BMP、.JPEG文件）等。,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个双字节字符。,4.4 数据表的创建和管理,4.

17、4.1 数据表结构的创建 4.4.2 数据表结构的管理,4.4.1 数据表结构的创建,在SQL Server 中，每个数据库中最多可以创建 20 亿个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。 SQL Server 提供了两种方法创建数据库表： 第一种方法是利用SSMS创建表； 另一种方法是利用Transact-SQL语句中的create命令创建表。,1. 利用create命令创建表,其语法形式如下： CREATE TABLE ( 列级完整性约束条件 , 列级完整性约束条件 , ),1. 利用create命令创建表,参数说明： （1）是所要定义的基本表的名字

18、。一个表它可以由一个或多个属性组成。 （2）一般取有实际意义的名字。 （3）可以是前面介绍的数据类型。 （4）在SQL Server 中有下面几种完整性约束条件： 空值约束（NULL or NOT NULL）；主键约束（primary key constraint）；唯一性约束（unique constraint）；检查约束（check constraint）；缺省约束（default constraint）；外部键约束（foreign key constraint）；规则（rule）；缺省值（default）。,2关于创建表时运用约束的说明,（1）空值约束（NULL or NOT NULL）

19、 空值NULL约束决定属性值是否允许为空值（NULL）。NULL表示没有输入任何内容，它不是零和空白，不允许为空值则用NOT NULL表示。 例如：设置属性teacher允许为空值。 CREATE TABLE t ( teacher char(8) NULL ),2关于创建表时运用约束的说明,（2）主键约束（primary key constraint） 主健约束要求主健属性取值必须惟一，一个表只能包含一个主健约束。如果没有在主健约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该主健约束用CLUSTERED。 主键约束SQL的语法形式如下：

20、 CONSTRAINT 约束名 PRIMARY KEY CLUSTERED|NONCLUSTERED（列名,n）,例如：在执行创建产品信息表的操作时，指定产品编号为主键值。 CREATE TABLE products ( id char(10) NOT NULL, name char(20) NOT NULL, price money， quantity smallint NULL, CONSTRAINT pk_products PRIMARY KEY CLUSTERED (id) ) 此例的主健名称是pk_products，在products表中关于id建立了一个索引排序。,2关于创建表时运

21、用约束的说明,（3）唯一性约束（unique constraint） 唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。 创建唯一性约束SQL语句： CONSTRAINT 约束名 UNIQUE CLUSTERED|NONCLUSTERED（列名,n） 例： CREATE TABLE student1 (sno char(8), sname char(16), constraint pk_student primary key(sno), constraint uniq_student unique(sname),2关于创建表时运用约束的说明,（4）检查约束（che

22、ck constraint） 使用检查约束时，应该注意以下几点： 一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。 一个表中可以定义多个检查约束。 在多个字段上定义检查约束，则必须将检查约束定义为表级约束。 检查约束中不能包含子查询。 创建检查约束SQL语法格式为： CONSTRAINT constraint_name CHECK (logical_expression),例如：创建一个职工信息表，其中输入性别字段值时，只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似01080798654之类的数据，而不能随意输入其他数据。 c

23、reate table employee( Id char(8), ename char(4) check(ename LIKE A-C,a-cA-Z,a-zA-ZA-Z), sex char(2) DEFAULT (F), phonenum char(15), constraint chk_sex check(sex in (F,M) , Constraint chk_phonenum check(phonenum like (010)8,90-90-90-90-90-90-90-9),2关于创建表时运用约束的说明,（5）外部键约束 外部键约束用于强制参照完整性，提供单个字段或者多个字段的参

24、照完整性。当使用外部键约束时，应该考虑以下几个因素： 外部键约束提供了字段参照完整性。 外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。 外部键约束不能自动创建索引，需要用户手动创建。 一个表中最多可以有31个外部键约束。 在临时表中，不能使用外部键约束。 主键和外部键的数据类型必须严格匹配。 外键约束SQL的语法形式如下： CONSTRAINT 约束名 FOREIGN KEY （外键列名）REFERENCES 参照表（参照列名）,外键约束主要用来维护两个表之间的一致性关系。 外键的建立主要是通过将一个表的主键所在列包含在另一个表中，这些列就是另一个

25、表的外键。,外键的作用不只是对输入自身的数据进行限制，同时也限制了对主键所在表的数据进行修改。 用户无法对主键里的数据进行修改和删除，除非事先删除或修改外键引用的数据。,例如：学生表S（SNO，SNAME，）中的SNO是S表的主健，课程表C（CNO，CNAME，）中的CNO是C表的主健，而学习表SC（SNO，CNO，GRADE）中的SNO、CNO是SC表的主健，并且SNO和CNO又分别相对于S表和C表是SC表的外健。则创建SC表的SQL语句如下： CREATE TABLE SC ( SNO CHAR(10) NOT NULL, CNO CHAR(6) NOT NULL, GRADE TINYI

26、NT DEFAULT NULL, CONSTRAINT PK_SC PRIMARY KEY ( SNO,CNO ), CONSTRAINT FK_SC FOREIGN KEY ( SNO ) REFERENCES Student(Sno), FOREIGN KEY ( CNO ) REFERENCES Course(Cno) ),3数据表结构创建实例,【例4-1】创建数据库“教学管理”的数据表学生基本资料表STUDENT，数据表结构如表0-3、表的完整性约束如表0-8、表0-9所示。 方法一：使用SSMS创建 第一步设置STUDENT数据表的结构，步骤如下： 在树形目录中找到要建表的数据库。

27、展开该数据库，在“表”上单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，出现创建数据表结构的窗口。,3数据表结构创建实例,图 利用表设计器创建表结构,3数据表结构创建实例, 表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行设置，其中前三项是必须在建表时给出的。 完成后，单击工具栏上的【保存】按钮。在出现的选择名称对话框中输入表名：STUDENT。 单击【确定】按钮退出。,3数据表结构创建实例,第二步设置STUDENT表完整性约束，步骤如下： 展开SSMS的数据库“教学管理”，单击“表”节点，选定数据表STUDENT，单击右键，在快捷菜单上，单

28、击“设计表”，出现设计表结构窗口。 设置主键约束（PRIMARY KEY），光标移到需要设置主键的字段sno，单击工具栏上的主键按钮，sno列名左侧出现“钥匙”图标。 如果要设置多属性作为主键，可以通过按住键，用鼠标左键依次单击要选定的列，选定多列后，单击工具栏上的主键按钮。取消主键设置的方法是，选定主键字段，单击主键按钮即可。 设置主键后，系统自动建立了一个索引。,3数据表结构创建实例, 在表设计器上右击，出现快捷菜单，在上面单击“属性”，出现属性对话框，在“表”选项中，可以输入表的名称、所有者、表文件组等信息。 在属性对话框中选择“索引/键”选项，系统设置表student.sno属性为主键

29、，因此自动在表中建立一个根据sno的值的大小升序排列的索引。由于主键要求属性取值惟一，所以复选框“创建UNIQUE”被选上。 设置检查约束（CHECK），STUDENT表定义了三个CHECK约束，第一个约束是学号sno，只能有7个字符构成，其中第一个必须是S，后面全部是数字，逻辑表达式为“（sno LIKE S0-90-90-90-90-90-9）”。 单击“CHECK约束”选项卡，在“CHECK约束”对话框中新建约束。,3数据表结构创建实例,建立学号的约束：单击【新建】，系统自动给定一个约束名，可在“约束名”处改名为“CK_SNO”，然后在约束表达式文本框中输入（sno LIKE S0-90

30、-90-90-90-90-9）。 建立身份证约束：单击【新建】，系统自动给定一个约束名，然后在约束表达式文本框中输入（sssn LIKE 0-90-90-90-90-90-9 0-90-90-90-90-90-9 0-90-90-90-90-90-9），最后在“约束名”处改名为“CK_SSSN”。,3数据表结构创建实例,方法二：使用SQL命令创建 （1）创建表并包含完整性约束定义，但不带约束名。 创建课程表COURSE的语句如下： CREATE TABLE COURSE ( cno char(6) NOT NULL, cname varchar(30) NOT NULL, cbname var

31、char(30), cedi char(8) cpup varchar(20), cisbn varchar(20), cprice momey, PRIMARY KEY(cno), CHECK(cno LIKE C0-9 0-9 0-9 0-9 0-9), ),3数据表结构创建实例,方法二：使用SQL命令创建 （2）创建表并包含完整性约束定义，同时定义各个约束名。 CREATE TABLE COURSE ( cno char(6) NOT NULL, cname varchar(30) NOT NULL, cbname varchar(30), cedi char(8) cpup varch

32、ar(20), cisbn varchar(20), cprice momey, CONSTRAINT cno_PK PRIMARY KEY(cno), CONSTRAINT cno_CK CHECK(cno LIKE C0-9 0-9 0-9 0-9 0-9),4.4.2 数据表结构的管理,1修改数据表结构 表结构创建以后，在使用的过程中经常会发现原来创建的表可能存在结构、约束等方面的问题，在这种情况下，需要对原表进行修改。如果用创建一个新表的方法替换原表，将造成表中数据的丢失，而通过修改表则可以在保留表中原有数据的基础上修改表结构，打开、关闭或删除已有约束，或增加新的约束等。 修改表结构有

33、两种方法：一种是利用SSMS，一种是使用SQL命令。,4.4.2 数据表结构的管理,利用SSMS修改表结构的过程。单击“设计表”命令，将弹出表设计器。 【例4-2】将教学管理数据库中的学生表STUDENT的属性“sno”的长度改为7，属性“ssex”的长度改为2。,4.4.2 数据表结构的管理,使用SQL命令修改表。 语法格式： ALTER TABLE ADD完整性约束 |ALTER COLUMN 列名 新类型 |DROP COLUMN 列名 |ADD PRIMARY KEY（列名,） |ADD FOREIGN KEY(列名) REFERENCES 表名（列名） |ADD CONSTRAINT

34、 约束名. |DROP CONSTRAINT 约束名,4.4.2 数据表结构的管理,【例4-2】将教学管理数据库中的学生表STUDENT的属性“sno”的长度改为7，属性“ssex”的长度改为2。 其SQL语句的程序清单如下： USE 教学管理 -打开教学管理数据库 GO ALTER TABLE STUDENT -修改STUDENT表 ALTER COLUMN sno char(7) NOT NULL -修改属性列sno ALTER TABLE STUDENT ALTER COLUMN ssex char(2) -修改属性列ssex 注意：跟属性列有关的约束和索引删除后，指定的属性才能被修改。所以上述第一个修改的属性sno有主键约束，有其他表和它的外键约束，因此直接运行该命令一般不能成功。,4.4.2 数据表结构的管理,2数据表结构的删除 可以用SSMS或SQL语句删除基本表。 方法一：使用SSMS 选中要删除的数据表，单击右键，在快捷菜单上选择“删除”。 方法二：使用SQL命令 SQL命令的一般格式为： DROP TABLE ,4.4.2 数据表结构的管理,3参看数据表定义信息 sp_help student,Thank You !,