第4讲表管理

第4讲管理表目录4.1表的概述4.2列数据类型4.3列的其他属性4.4维护数据表4.5表数据完整性4.6表的索引与视图4.7扩展练习在使用数据库的过程中，接触最多的就是数据库中的表。表是数据存储的地方，是一种结构化的文件，可用来存储某种特定类型的数据，是数据库中最重要的部分，管理好表也就管理好了数据库。其他的数据，例如，视图、索引等都是依附于表对象而存在的。本章将详细介绍数据库中基于表的各种操作（创建、修改、删除等）、数据完整性以及表的索引和视图等内容。4本章知识要点：了解表的类型及创建熟悉各种数据类型掌握列的各种属性掌握修改表和列的操作熟悉保持数据完整性的各种约束了解表的索引和视图4.1表的概述表是数据库中最重要的数据库对象，是数据库的基本组成部分，是储存数据的逻辑载体。关系型数据库中的表都是二维的，表的一列称为一个字段；表的一行称为一个记录。SQLServer2000中，一个表中最多允许1024列。表的行数没有限制，与磁盘存储空间有关。关系数据库的理论基础是关系模型关系模型的结构是一张二维表结构学号姓名性别入学时间班级代码系部代码010101001001张斌男2001-9-1801010100101010102002001周红瑜女2001-9-1801010200201010201001001贾凌云男2002-9-1801020100102010202002001向雪林女2002-9-1801020200202关系模型与SQLServer表的对应关系模型SQLServer表关系名表名关系表关系模式表的定义属性表的列属性名列名值列值元组表的行或记录关键字主键关系完整性SQLServer的约束根据数据库逻辑设计的要求确定：♦需要什么样的表♦各表有哪些数据（数据类型）♦表的各列♦每一列的数据类型、列宽、是否允许为空，是否需要索引（类型），哪些列是主键、外键具体地,要考虑很多因素表的设计1.因素一：考虑表将要存储哪些数据对象，绘制出ER图ER图是描述数据库中所有实体以及实体之间关系的图形，是辅助设计关系模型的工具。实际上，表就是关系模型，也对应着模型中的实体，是存储数据的对象。在设计表时，应该综合考虑这些问题：数据库的目的是什么？数据库中将要包含哪些数据？数据库中应该包含多少表？每一个表将要包含哪些数据？表和表之间是否存在关系？如果存在关系，那么存在什么样的关系？对这个因素的深入思考，有助于创建合理、完整的表。2019年9月20日第9页行(记录)列实体与记录2.因素二：考虑表中将要包含的列以及这些列的数据类型、精度等属性。确定了表之后，就要确定表的内容。每一个表包含多个列，每一个列都有一个数据类型，数字数据类型的列还需要确定列的精度和约度，这些都是设计表时必不可少的因素。数字列应该使用数字数据类型，字符列应该使用字符数据类型，日期列应该使用日期数据类型。对于数字列，需要认真考虑其精度和约度，对于字符列，应该考虑其是否使用定长字符列和字符长度。考虑这些因素的目标是：使得表中的列的数量尽可能地少。如果列的数量过多，应该考虑将该表分解成两个表或多个表。2019年9月20日第11页3.因素三：考虑列的属性，例如哪些列允许空值，哪些列不允许空值列允许空值，表示该列可以不包含任何的数据，空值既不是数字0，也不是空字符，而是表示未知。如果允许列包含空值，表示可以不为该列输入具体的数据；如果不允许列包含空值，在输入数据时必须为该列提供数据。例如，在包含订单的orders表中，订单代码、产品代码、客户代码等列不允许空置，但是订单描述列则可以包含空值。考虑这个因素的目标是，尽可能地不使用允许空值的列，因为空值列有可能带来意想不到的查询效果。如果不得不允许某些列为空，那么应该使得这些列的数量最少。2019年9月20日第12页4.因素四：考虑表是否使用主键，如果使用则在何处使用主键主键是唯一确定每一行数据的方式，是一种数据完整性对象。主键往往是一个列或多个列的组合。一个表中最多只能有一个主键。一般地，应该为每一个表都指定主键，借此可以确定行数据的唯一性。2019年9月20日第13页5.因素五：考虑是否使用约束、默认值、规则，以及在何处使用这些对象。约束、默认值、规则等都是数据完整性对象，用来确保表中的数据质量。对表中数据的查询操作，只能在满足定义的约束、默认值、规则等条件下，才能执行成功。这些因素的考虑往往与表中数据的商业特性相关。2019年9月20日第14页6.因素六：考虑是否使用外键，在何处使用外键在ER图中，需要绘制出实体之间的关系。在表的设计时，实体之间的关系需要借助主键——外键对来实现。因此，该因素也是确保ER图完整实施的一个重要内容。只有通过这种关系，才能确保表和表之间强制的商业性关系。2019年9月20日第15页7.因素七：考虑是否使用索引，在何处使用索引，使用什么样的索引？索引也是一种数据库对象，是加快对表中数据检索的手段，是提高数据库使用效率的一种重要方法。在哪些列上使用索引，在哪些列上不使用索引，是使用聚集索引，还是使用非聚集索引，是否使用全文索引，等等。对这些因素的认真考虑和实现，也是对表质量的更高的要求。2019年9月20日第16页SQLServer种创建表的4条限制（1）每个数据库中最多有20亿个表（2）每个表上最多可以创建一个聚集索引和249个非聚集索引（3）每个表最多可以配置1024个字段（4）每条记录最多可以占8060B，但不包括text字段和image字段4.1.2表的类型普通表又称标准表，数据库中存储数据的表，经常使用，最重要、最基本分区表将数据水平划分成多个单元的表，这些单元可以分散到数据库中的多个文件组里面临时表临时创建不能永久生存系统表主要存储有关SQL服务器的配置、数据库设置、用户和表对象的描述等系统信息4.1.3见后4.2列数据类型1．数值类型2．字符串类型3．二进制类型4．日期时间类型5．货币类型6.文本、图像数据类型7.特殊数据类型8.用户自定义的数据类型9.逻辑型1．数值类型整型数据类型(1)bigint：占8字节的存储空间，存储数据范围为－263~263－1。(2)int：占4字节的存储空间，存储数据范围为－231~231－1。(3)smallint：占2字节的存储空间，存储数据范围为－215~215－1。(4)tinyint：占1字节的存储空间，存储数据范围为0~255。实型数据类型(1)decimal[(p[,s])]：小数类型。其中，p为数值总长度，包括小数位数，但不包括小数点，范围1~38。s为小数位数。默认decimal(18,0)。(2)numeric[(p[,s])]：与decimal[(p[,s])]等价。(3)float[(n)]：浮点类型，占8字节的存储空间。(4)real：浮点类型，占4字节的存储空间。2．字符串类型(1)char[(n)]：定长字符串类型，长度为n，n范围为1~8000。默认为char(10)。(2)text：文本类型，实际也是变长字符串类型，存储长度超过char(8000)的字符串，最大长度231－1。实际上，text类型列中仅存储一个指针，数据本身存储在连接表中。(3)varchar[(n)]：变长字符串类型，即自动删除字符串尾部空格后存储。长度为n，n范围为1~8000。默认为char(50)。3．二进制类型(1)binary[(n)]：定长二进制类型，占n+4字节的存储空间，n范围为1~8000。默认为binary(50)。(2)varbinary[(n)]：变长二进制类型，占n+4字节的存储空间，n范围为1~8000。默认为binary(50)。(3)image：大量二进制类型，实际也是变长二进制类型，通常用于以位字符串形式存储图形，最大长度为231－1，约2GB。4．日期时间类型SQLServer2000的日期时间类型数据同时包含日期和时间信息，没有单独的日期类型或时间类型。(1)datetime：日期时间类型，占8字节的存储空间，范围为1753年1月1日-9999年12月31日。(2)smalldatetime：日期时间类型，占4字节的存储空间，范围为1900年1月1日-2079年12月31日。日期时间类型常量两端应加单引号。如果只指定日期，则时间默认为午夜12点0分0秒；如果只指定时间，则日期默认为1900年1月1日。如果省略世纪，当年=50默认为20世纪，50默认21世纪。5．货币类型(1)money：货币类型，4位小数，占8字节的存储空间。(2)smallmoney：货币类型，4位小数，占4字节的存储空间。货币类型常量应以美元符号“$”或所定义的金融单位符号作前缀。除了SQLServer2000提供的数据类型外，如果需要，还可以定义新的数据类型，称为“自定义数据类型”。6文本、图像类型（1）text：用于存储大量文本数据，其容量理论上为231-1个字节，在实际应用时需要视硬盘的实际空间而定。（2）ntext:与text类型相似，不同的是ntext类型采用Unicode标准字符集，因此其理论上容量为230-1个字节。（3）image用于存储大量的二进制数据。其理论容量为231-1个字节，通常用来存储图形等对象。在输入数据时同binary数据类型一样，必须在数据前加上字符“0x”作为二进制标识。7特殊数据类型（1）Timestamp:用于表示SQLServer活动的先后顺序，以二进投影的格式表示。与插入数据或日期和时间没有关系。（2）Uniqueidentifier:由十六字节的十六进制数字组成。8逻辑类型只能存储0或1。当输入0和1以外的值时，系统自动转换为1。通常存储逻辑量，表示真与假。9.Unicode字符数据unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一编码方案。所有的计算机都用单一的unicode标准，unicode数据中的位模式一致地翻译成字符，这保证了同一位模式在所有的计算机上总是转换成同一字符。数据可以随意地从一个数据库或计算机传送到另一个数据库或计算机。nchar[(n)]:存放n个unicode字符数据，n必须是1~4000之间的数据nvarchar[(n)]：存放长度可变的n个unicode字符数据，n必须是1~4000之间的数据ntext：存储最大长度为（230-1）B的unicode字符数据。4.3列的其他属性在4.2节中，我们详细讲解了列的数据类型及长度，但是对于一个数据库管理员来说，仅仅知道这些是远远不够的。要设计好字段，还需要考虑更多的问题，例如那些字段是记录中必不可少的、那些字段的内容是不可重复的等等。这就要求我们尽可能掌握列更多的属性，以便实现更复杂的功能。304.3.1NULL、NOTNULL和默认值在列的【常规】属性中，除了名字、数据类型、长度，还有两个非常重要的选项，就是NULL、NOTNULL和默认值。314.3.2IDENTITY的应用使用IDENTITY关键字定义的字段又叫标识字段，一个标识字段是唯一标识表中每条记录的特殊字段，当一个新记录添加到这个表中时，这个字段就被自动赋给一个新值。默认情况下是+1递增。324.1.3创建表1.企业管理器创建表步骤：1、定义表结构：给表的每一列取字段名，并确定其数据类型、数据长度、是否可以为空2、设置约束：为了限制输入值的取值范围，以保证输入数据的正确性和一致性3、添加数据：2.使用T-SQL语句创建表CREATETABLE语句的语法：CREATETABLE[database_name.[owner]|owner.]table_name({column_definition|column_nameascomputed_column_expression|table_constraint::=[CONSTRAINTconstraint_name]}|[{PRIMARYKEY|UNIQUE}[,…n])[ON{filegro