实验2+表的创建与管理.docx

1、实验2+表的创建与管理实验2 表的创建与管理目的和要求1巩固数据库的基础知识；2掌握创建与删除表的两种方法；3掌握修改表结构的方法。背景知识在关系数据库中，每个关系都体现为一张表，表是数据库的一个对象之一，是用来存储数据和操作数据的逻辑结构，使用数据库时，绝大多数时间都是在与表打交道。表中有两个基本的对象：字段和记录。字段也叫属性，也就是表中的列。字段的值也称为属性值，属性值的取值范围叫做值域。记录是字段的有序集合。一般用记录描述一个实体，因此有的时候记录也可以定义为能完整地描述一个实体的字段集，在二维表格中也就是行。设计数据库时，应该先确定需要什么样的表，各表中都有什么样的数据以及各个表的存

2、取权限等。在创建和操作表的过程中，需要对表进行细致的设计，在创建表之前必须先确定字段值。创建一张表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和各种附加成分。也可以先创建一张基础表，向表中添加一些数据并使用一段时间，然后在需要的时候利用修改表的方法再添加各种约束、索引、默认设置、规则以及其他对象。最好在创建表及其对象时预先进行设计： 指定表中所包含数据的类型：表中的每个字段有特殊的数据类型，可以限制插入数据的类型。数据类型的定义可以减少输入出错的可能性。另外SQL Server2000还支持用户自定义数据类型，当然自定义数据类型必须以系统定义的数据类型为基础，不能自己凭空创建数据类

3、型。选择数据类型时应该遵循以下规则： 如果列的长度可变，使用某个变长数据类型 对于数值数据类型，数值大小和所需要的精度有助于做出响应的决定 如果存储量超过8000字节，使用Text或Image类型，否则的话可以是用Char或Binary，也可以使用Varchar，因为它比Text或Image具有更强的功能 对于货币数据来说，使用Money数据类型 不要把Float或Real数据类型作为主键，因为这些数据的值是不准确的 哪些列允许空值：空值或NULL并不等于零、空白或零长度字符串,而是意味着没有输入，常用来表明值未知或不确定。指定一列不允许空值而确保行中该列永远有数据可以保证数据的完整性，如果不

4、允许空值，用户在向表中写数据的时候必须在列中输入一个值，否则该行不被接收入数据库。 是否要使用以及何时使用约束、默认设置以及规则，哪些列是主键，哪些是外键。 需要的索引类型以及哪里需要索引。 设计的数据库应该符合第三范式。数据库要想符合第三范式，必须首先符合第一、二范式。 SQL Server2000中，每个数据库可以有20亿个表，每张表可以有1024个列，每列可以有8060个字节。内容和步骤一、表的创建 在SQL Server2000 下创建表有两种方法：1利用企业管理器创建表 打开企业管理器树型结构上的数据库节点。 选中创建好的数据库，单击数据库中的表对象 然后右击窗口右侧选择新建表，弹出

5、如图2-1所示窗体：2-1 创建新的表格1) 在这个窗体中，需要对表的内容进行定义，内容如下： 输入列名，列名在表中必须唯一，列的命名规则必须遵守SQL Server2000数据库对象的命名规则； 选择正确的数据类型、数据长度和精度； 规定该列的值是否可以为空； 设置表格的主键和索引；注意：创建主键工作比较简单，首先选中要建立主键的列，然后右击鼠标，从快捷菜单中选择【设置主键】命令即可。建立为主键的列的旁边会出现一个小钥匙形的标志。也可以右击弹出的快捷菜单中的【属性】命令，弹出属性对话框，然后选择【索引/键】标签，如下图2-2所示进行设置：图2-2 创建主键对话框2) 在此处可以为表选择文件组

6、，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。3) 保存表格：创建完成后，单击【存盘】按钮，为表取一个名字进行存盘，注意，应该为表取一个有意义的且符合SQL Server2000命名规则的名字。2使用Transact_SQL语句创建表21 使用Transact_SQL语句创建表格的语法如下： CREATE TABLE database_name.owner. table_name ( | column_name AS compute

7、d_column_expression| ,n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type DEFAULT constant_expression | IDENTITY (seed, increment )ROWGUIDCOL n 说明： database_name.owner. table_name：定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。 ：列的定义。 column_name：列的名字。列的命名必须遵守有关数据库对象的命名

8、规则。 data_type：列的数据类型。 DEFAULT constant_expression：定义该列的默认值。 IDENTITY：定义该列是一个标识列。当一个新的数据行插入表中的时候，SQL Server2000为标识列提供一个唯一的、递增的数值。在一张表格中，只能定义一个标识列。在定义标识列时，必须同时定义起始值和增量。 Seed：定义标识列的起始值。所谓起始值就是插入表的第一行的数据的标识列的值。 Increment：定义标识列的增量。所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。 ROWGUIDCOL：定义该列是一个行全局唯一的标识列。在一张表中只有一个唯一标识符

9、列可以被定义为ROWGUIDCOL。 column_constraint：定义与列相关联的约束。 AS computed_column_expression：定义某列是计算列，列的计算表达式用computed_column_expression表示。 table_constraint：定义对表的约束。 ON filegroup | DEFAULT ：定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。 TEXTIMAGE_ON filegroup | DEFAULT ：如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。22

10、使用Transact_SQL语句创建表的步骤： 打开查询分析器 输入创建表的Transact_SQL语句点击【查询分析】（CTRL+F5）按钮，完成表的创建。例1：生成一个表名为student的表执行结果如图2-3所示：2-3 使用Transact_SQL语句创建表例2 创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。第二列Name默认值为Unknown，第三列定义一个约束(日期不能大于输入当天的日期)，插入一条记录并进行查询，显示如图2-4。图2-4 数据操纵二、表的修改 在SQL Server2000 下修改表有两种方法：1 利用企业管理器修改表的步骤如下： 从

11、树型结构上，展开要修改定义的表所在的数据库 选中该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表格 选择要修改定义的表，如右键点击所要修改的学生表，选择【设计表】，则弹出如图2-3所示的对话框，在这个窗体中可以进行增加列、删除列和修改列属性等操作。在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束,如图2-5。2-5 修改表格对话框2利用Transact-SQL语句修改表 使用Transact-SQL可以在更大的范围内修改表格的定义，可以修改的内容包括：修改、增加、删除列或约束，是约束和触发器无效等。Transact-SQL修改表格定义的语法如下：ALTE

12、R TABLE table ALTER COLUMN column_name new_data_type NULL| NOT NULL|ADD |DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression , n |ADD , n |DROP CONSTRAINT constraint_name |COLUMN column , n |CHECK |NOCHECK CONSTRAINT ALL | constraint_name , n |ENABLE |DISABLE TRIGGER ALL |trigger_name

13、, n说明： ALTER COLUMN：修改已经存在的列的属性。 ADD |DROP ROWGUIDCOL：将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。 ADD | column_name AS computed_column_expression , n：增加新的列 ADD , n:定义新的表约束。 DROP CONSTRAINT constraint_name |COLUMN column , n：删除约束或删除列。 CHECK |NOCHECK CONSTRAINT ALL | constraint_name , n：是所有约束或者指定的约束有效或失效。

14、ENABLE |DISABLE TRIGGER ALL |trigger_name , n：使所有的触发器或者部分触发器有效或无效。例：使用Transact-SQL语句对学生表进行各种修改。 向表中添加新的字段：在学生表中添加一个“班级”字段，数据类型为字符型。ALTER TABLE student add班级char 删除表中的旧列：将学生表中的“Sdept”字段删除。ALTER TABLE student DROP column Sdept 更改表以添加具有约束的列：给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。ALTER TABLE student ADD

15、grade int Constraint chname check(grade100)Exec sp_help chname给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。Alter table student Add birthday DATETIME NULLCONSTRAINT ch_birthday CHECK (birthdaygetdate() 添加具有默认值的可为空的列：在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。Alter table studentAdd matriculationday smal

16、ldatetime NULLConstraint adddatefltDefault getdate()三、表的删除 删除表格相对于创建表格来说要简单的多，在SQL Server2000 下修改表有两种方法：1 在企业管理器中进行表的删除，步骤如下：1 树型结构上，展开要删除的表所在的数据库2 择该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表 格3 择表格，可以同时按下Ctrl键或Shift键选择多哥要执行操作的表格4 标右击，从弹出的快捷菜单中选择【删除】命令，弹出2-4所示的对话框。对话框中列出了所有将被删除的表格。可以选择其中的表格，然后单击【显示相关性】按钮，查看与该表格

17、相关的数据库对象的信息。5 击【全部除去】按钮删除所有选中的表。 注意： 删除表格必须小心，因为表格一旦删除便无法恢复。而表格中原先包含的数据也将随着表格的删除而消失。要想挽回被无意中删除的数据的唯一方法是从原先的数据库中备份恢复。2删除表对象，我们可以在企业管理器中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP 删除表的定义及表中的所有数据、索引、触发器、约束和权限规范。如要删除ta1表，语法为：DROP TABLE table_name例：删除当前连接的数据库中的一个名字为ta1的表格和删除pubs数据库中的example的表格的语句如下：DROP TABLE ta

18、1 DROP TABLE pubs.dbo.example 注意：删除表格的权力只属于表格的拥有者，并且不能转移，不能使用DROP TABLE语句删除系统表。练习题练习1 在Student数据库中利用查询分析器创建课程信息表Course、表结构如下：主键列名数据类型宽度小数位空否备 注PkCnoChar3N课程代码CnameChar20Y课程名称CpnoChar3Y先行课号CreditDecimal31Y学分 利用Transact-SQL语句创建选课成绩表Sc和操作员表Operator,表结构如下：主码列名数据类型宽度小数位空否备 注PkSnoChar5N学号CnoChar3N课程代码Grad

19、eDecimal51Y成绩主码列名数据类型宽度小数位空否备 注PkOperator_idChar4N操作员编号Operator_nameChar12Y操作员姓名PasswordChar10Y密码PermissionChar20Y权限练习2 表的管理 用企业管理器创建教工表teacher，要求如下：列名数据类型允许为空？约束IDENTITY属性教工号Int否主键无姓名Varchar(20)是无无家庭住址Char(30)是无无电话Int是无无性别Char(2)否默认为男无系别Varchar(16)否必须是（计算机软件、计算机体系结构、计算机网络）之一无 在查询分析器中用命令：DROP TABLE

20、teacher删除该表。 根据条件在查询分析器中输入以下语句：CREATE TABLE teacher ( 教工号 INT PRIMARY KEY IDENTITY(1,1), 姓名 VARCHAR(18) NULL, 家庭住址 CHAR(30) NULL, 电话 INT NULL, 职称 CHAR(14) DEFAULT 讲师, 所在专业 VARCHAR(16) CONSTRAINT chname1 CHECK(所在专业 IN (计算机体系结构,计算机网络，计算机软件) )点击【查询分析】（CTRL+F5）按钮，看看执行结果。练习3 查看SQL Server2000中系统自带数据库pubs的表信息，熟悉各表之间的相互关系。