《《表的建立与维护》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《表的建立与维护》PPT课件.ppt（44页珍藏版）》请在金锄头文库上搜索。
1、第3章 表的建立与维护主讲教师：卫琳3.1.1 什么是表什么是表l表类型：表类型：u普通表：普通表：u分区表：分区表：u临时表：临时表：u系统表：系统表：l表需要包含的列，每列的数据类型、精度表需要包含的列，每列的数据类型、精度l表中哪些列允许空值或不允许空值表中哪些列允许空值或不允许空值l表中是否设置主键，在何处设置主键表中是否设置主键，在何处设置主键l表中是否设置约束、默认值、规则表中是否设置约束、默认值、规则l表中是否设置外键以及在何处设置表中是否设置外键以及在何处设置l表中是否设置索引、在何处设置以及设置什么样的索引表中是否设置索引、在何处设置以及设置什么样的索引l一个单词一个单词l一
2、个字母一个字母l一个数字一个数字l一个日期一个日期l一个一个NULL值，它表示该单元中没有数据值，它表示该单元中没有数据一个单元可以包含以下内容中的一个：一个单元可以包含以下内容中的一个：实体与记录实体与记录行(记录)列列设计数据库实际上就是设计数据库中的表。在设计表时，要使用尽可能少的表数量，在达到设计要求的前提下，使每个表中包含的列的数量尽可能地少。合理的表结构，可以大大提高整个数据库数据的查询效率。为了提高数据库的效率，设计出高质量的存储数据的表，在设计表时，应该从整体上考虑下面7个因素。在设计表时应该考虑的因素在设计表时应该考虑的因素1. 1. 因素一：考虑表将要存储哪些数据对象，因素
3、一：考虑表将要存储哪些数据对象，绘制出绘制出ERER图图ER图是描述数据库中所有实体以及实体之间关系的图形，是辅助设计关系模型的工具。表就是关系模型，也对应着模型中的实体，是存储数据的对象。在设计表时，应该综合考虑这些问题：数据库中将要包含哪些数据？数据库中应该包含多少表？每一个表将要包含哪些数据？表和表之间是否存在关系？如果存在关系，那么存在什么样的关系？2024年8月11日第6页2. 2. 2. 2. 因素二：考虑表中将要包含的列以及这些列的数因素二：考虑表中将要包含的列以及这些列的数因素二：考虑表中将要包含的列以及这些列的数因素二：考虑表中将要包含的列以及这些列的数据类型、精度等属性。据
4、类型、精度等属性。据类型、精度等属性。据类型、精度等属性。确定了表之后，就要确定表的内容。每一个表包含多个列，每一个列都有一个数据类型，数字数据类型的列还需要确定列的精度和约度，这些都是设计表时必不可少的因素。数字列应该使用数字数据类型，字符列应该使用字符数据类型，日期列应该使用日期数据类型。对于数字列，需要认真考虑其精度和约度，对于字符列，应该考虑其是否使用定长字符列和字符长度。考虑这些因素的目标是：使得表中的列的数量尽可能地少。如果列的数量过多，应该考虑将该表分解成两个表或多个表。2024年8月11日第7页3. 3. 3. 3. 因素三：考虑列的属性，例如哪些列允许空值，因素三：考虑列的属
5、性，例如哪些列允许空值，因素三：考虑列的属性，例如哪些列允许空值，因素三：考虑列的属性，例如哪些列允许空值，哪些列不允许空值哪些列不允许空值哪些列不允许空值哪些列不允许空值列允许空值，表示该列可以不包含任何的数据，空值既不是数字0，也不是空字符，而是表示未知。如果允许列包含空值，表示可以不为该列输入具体的数据；如果不允许列包含空值，在输入数据时必须为该列提供数据。例如，在包含订单的orders表中，订单代码、产品代码、客户代码等列不允许空置，但是订单描述列则可以包含空值。考虑这个因素的目标是，尽可能地不使用允许空值的列，因为空值列有可能带来意想不到的查询效果。如果不得不允许某些列为空，那么应该
6、使得这些列的数量最少。2024年8月11日第8页4. 4. 因素四：考虑表是否使用主键，如果使用因素四：考虑表是否使用主键，如果使用则在何处使用主键则在何处使用主键主键是唯一确定每一行数据的方式，是一种数据完整性对象。主键往往是一个列或多个列的组合。一个表中最多只能有一个主键。一般地，应该为每一个表都指定主键，借此可以确定行数据的唯一性。2024年8月11日第9页5. 5. 因素五：考虑是否使用约束、默认值、规因素五：考虑是否使用约束、默认值、规则，以及在何处使用这些对象。则，以及在何处使用这些对象。约束、默认值、规则等都是数据完整性对象，用来确保表中的数据质量。对表中数据的查询操作，只能在满
7、足定义的约束、默认值、规则等条件下，才能执行成功。这些因素的考虑往往与表中数据的商业特性相关。2024年8月11日第10页6. 6. 因素六：考虑是否使用外键，在何处使用因素六：考虑是否使用外键，在何处使用外键外键在ER图中，需要绘制出实体之间的关系。在表的设计时，实体之间的关系需要借助主键外键对来实现。因此，该因素也是确保ER图完整实施的一个重要内容。只有通过这种关系，才能确保表和表之间强制的商业性关系。2024年8月11日第11页7. 7. 7. 7. 因素七：考虑是否使用索引，在何处使用索引，因素七：考虑是否使用索引，在何处使用索引，因素七：考虑是否使用索引，在何处使用索引，因素七：考虑
8、是否使用索引，在何处使用索引，使用什么样的索引？使用什么样的索引？使用什么样的索引？使用什么样的索引？索引也是一种数据库对象，是加快对表中数据检索的手段，是提高数据库使用效率的一种重要方法。在哪些列上使用索引，在哪些列上不使用索引，是使用聚集索引，还是使用非聚集索引，是否使用全文索引，等等。对这些因素的认真考虑和实现，也是对表质量的更高的要求。2024年8月11日第12页3.1.2 表的数据类型表的数据类型种 类数 据 类 型数字整数int, bigint, smallint, tinyint精确数值decimal, numeric近似数值float, real货币money, smallmo
9、ney日期和时间datetime, smalldatetime字符Non-Unicodechar, varchar, varchar(max), textUnicodenchar, nvarchar, nvarchar(max), ntext二进制binary, varbinary, varbinary(max)图像image全局标识符uniqueidentifierXMLxml特殊bit, cursor, timestamp, sysname, table, sql_variant3.1.3 创建表创建表1.使用使用SSMS创建表创建表使用使用SQL Server Management St
10、udio创建表创建表 多媒体：创建表多媒体：创建表在这个交互式多媒体中，你可以了解如何使用SQL Server Management Studio创建表，请注意你需要为表指定一个自动增长的列作为ID，同时另外一列将拥有默认值。use xscjgocreate table student(学号 char(10) not null, 姓名 varchar(8) not null, 性别 char(2) not null, 专业 varchar(30), 出生日期 smalldatetime not null, 简历 text)例：在例：在xscj数据库下创建数据库下创建student表表2.使用使
11、用T-SQL命令创建表命令创建表创建创建studentsstudents表表2024年8月11日第18页创建创建ordersorders表表2024年8月11日第19页在创建表中使用计算列在创建表中使用计算列2024年8月11日第20页计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。 例如，在 AdventureWorks 示例数据库中，Sales.SalesOrderHeader 表的 TotalDue 列具有以下定义：TotalDue AS Subtotal + TaxAmt
12、+ Freight。 一般情况下，计算列是未实际存储在表中的虚拟列。计算列是未实际存储在表中的虚拟列。每当在查询中引用计算列时，都将重新计算它们的值。每当在查询中引用计算列时，都将重新计算它们的值。数据库引擎在数据库引擎在 CREATE TABLE 和和ALTER TABLE 语句语句中使用中使用 PERSISTED 关键字来将计算列实际存储在表中。关键字来将计算列实际存储在表中。如果在计算列的计算更改时涉及任何列，将更新计算列如果在计算列的计算更改时涉及任何列，将更新计算列的值。的值。 使计算列中的数据物理化使计算列中的数据物理化2024年8月11日第23页PERSISTEDPERSISTE
13、DPERSISTEDPERSISTED 指定 SQL Server 数据库引擎将在表中物理存储计算值，而且，当计算列依赖的任何其他列发生更新时对这些计算值进行更新。创建全局临时表创建全局临时表2024年8月11日第25页临时表临时表临时表,在数据库，顾名思义就是起到建立一个临时性的存放某数据集的作中。 临时表一般分为:事务临时表的管理、会话临时表 事务临时表的话，当事务结束的时候，就会清空这个事务临时表。所以，当我们在数据库临时表中插入数据后，只要事务没有提交的话，该表中的数据就会存在。但是，当事务提交以后，该表中的数据就会被删除。而且，这个变化不会在重做日志中显示。 会话临时表，顾名思义，是
14、指数据只在当前会话内有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。局部临时表和全局临时表局部临时表和全局临时表局部临时表只能被当前登录用户使用，全局临时表可以被不同登录用户使用。其实从局部和全局两个词就能知道。use xscjgoalter table studentadd 少数民族否 bitgo例：在例：在xscj数据库下修改数据库下修改student表，增加少数民族否一列，为表，增加少数民族否一列，为bit类型。然后在此表中删除此列类型。然后在此表中删除此列.3.6 修改表修改表alter table studentdrop column 少数民族否go3.6.
15、1 使用使用SSMS图形修改表图形修改表3.6.2 使用使用T-SQL命令修改表命令修改表use xscjgoalter table studentalter column 姓名 varchar(10)go例：在例：在xscj数据库下修改数据库下修改student表，将姓名长表，将姓名长度由原来的度由原来的8修改为修改为10；将出生日期由原来的；将出生日期由原来的smalldatetime修改为修改为date；alter table studentalter column 出生日期 dategoDrop table studentgo例：在例：在xscj数据库下删除数据库下删除student表
16、表3.7 删除表删除表3.7.1 使用使用SSMS图形删除表图形删除表3.7.2 使用使用T-SQL命令删除表命令删除表3.2 列的数据类型列的数据类型3.2.1 数据类型的分类数据类型的分类种种 类数数 据据 类 型型数字数据类型int,bigint,smallint,tinyint,decimal,numeric,float,real,money,smallmoney,bit字符数据类型char, varchar, nchar, nvarchar, ntext text日期和时间datetime, smalldatetime,date,time,datetime2,datetimeoffs
17、et二进制数据类型binary, varbinary,image其他数据类型uniqueidentifier,xml, timestamp, sql_variant3.2.2 数字数据类型数字数据类型数字数据类型表示符号用途精确数字类型整数数据类型int、bigint、smallint、tinyint存储数字数据，直接进行数据运算位数据类型bit用于一些条件逻辑判断货币数据类型money、smallmoney存储货币数据近似数字类型十进制小数型decimal、numeric存储带固定精度和位数的小数浮点数据类型float、real存储范围非常大的数字，用于表示指定数的大致数据值3.2.3 字符
18、数据类型字符数据类型字符字符类型型表示符号表示符号用法用法ANSI字符字符char存存储指定指定长度的字符串度的字符串varchar存存储可可变长度字符度字符text存存储大量非大量非Unicode字符字符Unicode字符字符nchar用于存用于存储Unicode字符字符nvarchar与与varchar类似，存似，存储可可变长度度Unicode字符字符ntext与与text类似，存似，存储大量大量Unicode字符数据字符数据char类型存储字符数据时，每一个字符占一个字节大小。使用char类型时，应该给定数据的最大长度，定义格式为：char（n）如果没有指定n的大小，默认值是1，最长可以
19、容纳8000个字符；如果实际数据的字符长度大于给定的最大长度时，超过的字符将会被截断；若实际数据的字符长度小于给定的最大长度时，多余的字节被系统使用空格填充。varchar的使用方式与char基本相同。它们的区别在于：当列中字符长度基本一致时（如学号、姓名等），可以使用char类型；当数据长度相差较大时（如备注），使用varchar类型可以节省存储空间；当数据有可能涉及到英语外其他语言时，应该使用Unicode字符编码。每一个Unicode字符需要两个字节存储，因此，Unicode格式比ANSI格式有更大的字符集范围：ANSI字符集仅有256个字符，Unicode字符集有65536个不同的字符
20、。使用Unicode字符可以在表的一个列中同时出现中文、英文、法文等，而不会出现编码冲突。通常使用以下方式来表示Unicode字符：NUnicode字符nchar和nvarchar分别用于存储固定长度和可变长度的Unicode字符数据；text和ntext，分别对应存储长度很大的ANSI字符和Unicode字符数据。微软公司建议，用户应该避免使用text和ntext数据类型，而使用varchar和nvarchar存储大文本数据。3.2.4 日期时间数据类型日期时间数据类型ldatetime型数据长度为8字节，其中前4个字节用于存储日期，后4个字节用于存储时间。由于历史原因的限制，datetim
21、e支持的日期范围从1753年1月1日至9999年12月31日，时间精确度是3.33毫秒；l由于存在各种各样的表示日期的习惯，输入datetime数据的值是一件比较棘手的事情。如11/10/01对于不同的人有不同的理解方式。当将其作为datetime型数据输入时，SQL Server将根据当前系统的语言设置进行转换。系统的语言由登录SQL Server时的默认语言决定，也可以使用SET DATEFORMAT语句，指定字符D、M和Y的组合。n设置格式为：SET DATEFORMAT format | format _var；n参数format或format_var 是指日期的顺序。有效的形式包括
22、MDY、DMY、YMD、YDM、MYD 和 DYM，其中Y表示年，M表示月，D表示日。在默认情况下，日期格式为MDY；n在SQL Server 2008中，可以使用的时间格式是HH:MI:SS.mmm。其中HH表示小时，MI表示分钟，SS表示秒，mmm表示千分之一秒；n存在一种标准输入格式，不受DATEFORMAT设置的影响，不会产生歧义。它的中间部分没有分隔符，形式为：YYYYMMDD HH:MI:SS.mmm。如：20111001、111001和20111001 20:50:30.988都是这种格式的应用。在输入datetime类型数据时，我们推荐使用这种格式。在SQL Server 20
23、08系统中，日期时间数据类型的最大转变就是在datetime和smalldatetime两种类型的基础上又引入了4种日期时间数据类型，分别为date、time、datetime2和datetimeoffset。下面分别介绍：ldate数据类型只存储日期型数据类型，不存储时间数据，取值范围从0001-01-01到9999-12-31。引入date类型，克服了datetime类型中既有日期又有时间的缺陷，使对日期的查询更加方便；ltime数据类型与date数据类型类似，如果只想存储时间数据而不需要存储日期部分就可以利用time数据类型，取值范围从00：00：00.0000000到23：59：59.
24、9999999；ldatetime2数据类型是一种日期时间混合的数据类型，不过其时间部分秒数的小数部分可以保留不同位数的值，比datetime数据类型的取值范围要广，可以存储从公元元年1月1日到9999年12月31日的日期。用户可以根据自己的需要设置不同的参数来设定小数位数，最高可以设定到小数点后七位（参数为7），也可以不要小数部分（参数为0），以此类推；ldatetimeoffset数据类型用于存储与特定的日期和时区相关的日期和时间。这种数据类型的日期和时间存储为协调世界时（Coordinated Universal Time，UTC）的值，然后，根据与该值有关的时区，定义要增加或减去的时间
25、数。datetimeoffset类型是由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为7。3.2.5 二进制数据类型二进制数据类型 二进制数据类型用于表示位数据流，包括二进制数据类型用于表示位数据流，包括binary（固定长度）、（固定长度）、varbinary（可变长度）和（可变长度）和image三种。三种。lbinary用于存储固定长度的二进制数据；用于存储固定长度的二进制数据；lvarbinary用于存储可变长度的二进制数据，若存储的二进制大于用于存储可变长度的二进制数据，若存储的二进制大于8000字节，就必须使用字节，就必须使用varbinary（max）数
26、据类型；）数据类型；limage数据类型用于存储图像信息，在数据类型用于存储图像信息，在SQL Server 2008中，只有在数中，只有在数据的字节数超过了据的字节数超过了8KB的情况下，才使用的情况下，才使用image数据类型，其他情况应使数据类型，其他情况应使用用varbinary（max）代替，其中）代替，其中max最大可以达到最大可以达到231-1字节；字节；l当二进制数据存储到表中时，可以使用当二进制数据存储到表中时，可以使用SELECT语句来检索。但是，检语句来检索。但是，检索结果以索结果以16进制数据格式来显示。进制数据格式来显示。3.2.6 其他数据类型其他数据类型ltime
27、stamp时间戳数据类型与时间、日期无任何关系。时间戳数据类型与时间、日期无任何关系。timestamp值是二进制，表明数据库中的数据修改发生的相对顺值是二进制，表明数据库中的数据修改发生的相对顺序。实现序。实现timestamp数据类型最初是为了支持数据类型最初是为了支持SQL Server恢复算恢复算法。每一个数据库都有一个时间戳计数器，当对该数据库中包含法。每一个数据库都有一个时间戳计数器，当对该数据库中包含timestamp列的表执行插入或更新操作时，计数器值就会增加。列的表执行插入或更新操作时，计数器值就会增加。这样，可以轻易地确定表中的某个数据行的值是否在上次读取后这样，可以轻易地
28、确定表中的某个数据行的值是否在上次读取后发生了更新：如果发生了更新，则该时戳计数器的值也发生了变发生了更新：如果发生了更新，则该时戳计数器的值也发生了变化。可以使用化。可以使用DBTS函数返回数据库的时戳值；函数返回数据库的时戳值；lsql_variant类型：用于存储类型：用于存储SQL Server 2008支持的各种数据支持的各种数据类型类型(不包括不包括text、ntext、image、timestamp和和sql_variant类型类型)的值。数据列类型为的值。数据列类型为sql_variant的列可能包含不同类型的行，一的列可能包含不同类型的行，一般在不能准确确定将要存储的数据类型
29、时，使用这种数据类型。般在不能准确确定将要存储的数据类型时，使用这种数据类型。该数据类型可以用在列、变量、用户定义的函数等返回值中；该数据类型可以用在列、变量、用户定义的函数等返回值中；luniqueidentifier唯一标识符类型，是一个具有唯一标识符类型，是一个具有16字节的全局字节的全局唯一性标志符，用来确保对象的唯一性。可以在定义列或者变唯一性标志符，用来确保对象的唯一性。可以在定义列或者变量时使用该数据类型，主要目的是在合并复制和事务复制中确量时使用该数据类型，主要目的是在合并复制和事务复制中确保表中数据行的唯一性。例如，在客户标识号列使用这种数据保表中数据行的唯一性。例如，在客户
30、标识号列使用这种数据类型可以区别不同的客户。类型可以区别不同的客户。uniqueidentifier数据类型的初始值数据类型的初始值可以通过可以通过NEWID函数得到；函数得到；lxml类型：类型：xml数据类型可以用来保存整个数据类型可以用来保存整个XML文档，允许用文档，允许用户存储和处理户存储和处理XML数据。用户可以像使用数据。用户可以像使用int数据类型一样使用数据类型一样使用xml数据类型。另外，数据类型。另外，xml数据类型还提供一些高级功能，比如数据类型还提供一些高级功能，比如借助借助Xquery语法执行搜索。语法执行搜索。3.3列的其他属性列的其他属性3.3.1 默认约束默认约束3.3.2 空值约束空值约束3.5 查看表查看表3.5.1 查看表中相关信息查看表中相关信息3.5.2 查看表中存储的数据查看表中存储的数据3.5.3 查看表与其他数据对象的依赖关系查看表与其他数据对象的依赖关系
