实验2+表的创建与管理.docx
《实验2+表的创建与管理.docx》由会员分享，可在线阅读，更多相关《实验2+表的创建与管理.docx（14页珍藏版）》请在冰豆网上搜索。
实验2+表的创建与管理
实验2表的创建与管理
目的和要求
1．巩固数据库的基础知识；
2．掌握创建与删除表的两种方法；
3．掌握修改表结构的方法。
背景知识
在关系数据库中，每个关系都体现为一张表，表是数据库的一个对象之一，是用来存储数据和操作数据的逻辑结构，使用数据库时，绝大多数时间都是在与表打交道。
表中有两个基本的对象：
字段和记录。
字段也叫属性，也就是表中的列。
字段的值也称为属性值，属性值的取值范围叫做值域。
记录是字段的有序集合。
一般用记录描述一个实体，因此有的时候记录也可以定义为能完整地描述一个实体的字段集，在二维表格中也就是行。
设计数据库时，应该先确定需要什么样的表，各表中都有什么样的数据以及各个表的存取权限等。
在创建和操作表的过程中，需要对表进行细致的设计，在创建表之前必须先确定字段值。
创建一张表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和各种附加成分。
也可以先创建一张基础表，向表中添加一些数据并使用一段时间，然后在需要的时候利用修改表的方法再添加各种约束、索引、默认设置、规则以及其他对象。
最好在创建表及其对象时预先进行设计：
⑴指定表中所包含数据的类型：
表中的每个字段有特殊的数据类型，可以限制插入数据的类型。
数据类型的定义可以减少输入出错的可能性。
另外SQLServer2000还支持用户自定义数据类型，当然自定义数据类型必须以系统定义的数据类型为基础，不能自己凭空创建数据类型。
选择数据类型时应该遵循以下规则：
✧如果列的长度可变，使用某个变长数据类型
✧对于数值数据类型，数值大小和所需要的精度有助于做出响应的决定
✧如果存储量超过8000字节，使用Text或Image类型，否则的话可以是用Char或Binary，也可以使用Varchar，因为它比Text或Image具有更强的功能
✧对于货币数据来说，使用Money数据类型
✧不要把Float或Real数据类型作为主键，因为这些数据的值是不准确的
⑵哪些列允许空值：
空值或NULL并不等于零、空白或零长度字符串,而是意味着没有输入，常用来表明值未知或不确定。
指定一列不允许空值而确保行中该列永远有数据可以保证数据的完整性，如果不允许空值，用户在向表中写数据的时候必须在列中输入一个值，否则该行不被接收入数据库。
⑶是否要使用以及何时使用约束、默认设置以及规则，哪些列是主键，哪些是外键。
⑷需要的索引类型以及哪里需要索引。
⑸设计的数据库应该符合第三范式。
数据库要想符合第三范式，必须首先符合第一、二范式。
⑹SQLServer2000中，每个数据库可以有20亿个表，每张表可以有1024个列，每列可以有8060个字节。
内容和步骤
一、表的创建
在SQLServer2000下创建表有两种方法：
1．利用企业管理器创建表
⑴打开企业管理器树型结构上的数据库节点。
⑵选中创建好的数据库，单击数据库中的表对象
⑶然后右击窗口右侧选择新建表，弹出如图2-1所示窗体：
2-1创建新的表格
1）在这个窗体中，需要对表的内容进行定义，内容如下：
✧输入列名，列名在表中必须唯一，列的命名规则必须遵守SQLServer2000数据库对象的命名规则；
✧选择正确的数据类型、数据长度和精度；
✧规定该列的值是否可以为空；
✧设置表格的主键和索引；
注意：
创建主键工作比较简单，首先选中要建立主键的列，然后右击鼠标，从快捷菜单中选择【设置主键】命令即可。
建立为主键的列的旁边会出现一个小钥匙形的标志。
也可以右击弹出的快捷菜单中的【属性】命令，弹出属性对话框，然后选择【索引/键】标签，如下图2-2所示进行设置：
图2-2创建主键对话框
2）在此处可以为表选择文件组，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。
应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。
3）保存表格：
创建完成后，单击【存盘】按钮，为表取一个名字进行存盘，注意，应该为表取一个有意义的且符合SQLServer2000命名规则的名字。
2．使用Transact_SQL语句创建表
2．1使用Transact_SQL语句创建表格的语法如下：
CREATETABLE
[database_name.[owner].]table_name
（
{
|column_nameAScomputed_column_expression
|
}[,…n]
）
[ON{filegroup|DEFAULT}]
[TEXTIMAGE_ON{filegroup|DEFAULT}]
:
:
={column_namedata_type}
[DEFAULTconstant_expression]
|[IDENTITY[（seed,increment）]
]
[ROWGUIDCOL]
[][…n]
说明：
Ø[database_name.[owner].]table_name：
定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。
Ø：
列的定义。
Øcolumn_name：
列的名字。
列的命名必须遵守有关数据库对象的命名规则。
Ødata_type：
列的数据类型。
ØDEFAULTconstant_expression：
定义该列的默认值。
ØIDENTITY：
定义该列是一个标识列。
当一个新的数据行插入表中的时候，SQLServer2000为标识列提供一个唯一的、递增的数值。
在一张表格中，只能定义一个标识列。
在定义标识列时，必须同时定义起始值和增量。
ØSeed：
定义标识列的起始值。
所谓起始值就是插入表的第一行的数据的标识列的值。
ØIncrement：
定义标识列的增量。
所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。
ØROWGUIDCOL：
定义该列是一个行全局唯一的标识列。
在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。
Øcolumn_constraint：
定义与列相关联的约束。
ØAScomputed_column_expression：
定义某列是计算列，列的计算表达式用computed_column_expression表示。
Øtable_constraint：
定义对表的约束。
ØON{filegroup|DEFAULT}：
定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。
Ø[TEXTIMAGE_ON{filegroup|DEFAULT}]：
如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。
2．2使用Transact_SQL语句创建表的步骤：
⑴打开查询分析器
⑵输入创建表的Transact_SQL语句
⑶点击【查询分析】（CTRL+F5）按钮，完成表的创建。
例1：
生成一个表名为student的表
执行结果如图2-3所示：
2-3使用Transact_SQL语句创建表
例2创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。
第二列Name默认值为Unknown，第三列定义一个约束（日期不能大于输入当天的日期），插入一条记录并进行查询，显示如图2-4。
图2-4数据操纵
二、表的修改
在SQLServer2000下修改表有两种方法：
1．利用企业管理器修改表的步骤如下：
⑴从树型结构上，展开要修改定义的表所在的数据库
⑵选中该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表格
⑶选择要修改定义的表，如右键点击所要修改的学生表，选择【设计表】，则弹出如图2-3所示的对话框，在这个窗体中可以进行增加列、删除列和修改列属性等操作。
在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束,如图2-5。
2-5修改表格对话框
2．利用Transact-SQL语句修改表
使用Transact-SQL可以在更大的范围内修改表格的定义，可以修改的内容包括：
修改、增加、删除列或约束，是约束和触发器无效等。
Transact-SQL修改表格定义的语法如下：
ALTERTABLEtable
{[ALTERCOLUMNcolumn_name
{new_data_type[NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL
}]
|ADD
{[]
|column_nameAScomputed_column_expression
}[,…n]
|ADD
{}[,…n]
|DROP
{[CONSTRAINT]constraint_name
|COLUMNcolumn
}[,…n]
|{CHECK|NOCHECK}CONSTRAINT
{ALL|constraint_name[,…n]
|{ENABLE|DISABLE}TRIGGER
{ALL|trigger_name[,…n]}
说明：
ØALTERCOLUMN：
修改已经存在的列的属性。
Ø{ADD|DROP}ROWGUIDCOL：
将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。
ØADD{[]|column_nameAScomputed_column_expression}[,…n]：
增加新的列
ØADD{}[,…n]:
定义新的表约束。
ØDROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]：
删除约束或删除列。
Ø{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,…n]}：
是所有约束或者指定的约束有效或失效。
Ø{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}：
使所有的触发器或者部分触发器有效或无效。
例：
使用Transact-SQL语句对学生表进行各种修改。
◆向表中添加新的字段：
在学生表中添加一个“班级”字段，数据类型为字符型。
ALTERTABLEstudentadd班级char
◆删除表中的旧列：
将学生表中的“Sdept”字段删除。
ALTERTABLEstudentDROPcolumnSdept
◆更改表以添加具有约束的列：
给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。
ALTERTABLEstudentADDgradeint
Constraintchnamecheck（grade<100）
Execsp_helpchname
给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。
AltertablestudentAddbirthdayDATETIMENULL
CONSTRAINTch_birthdayCHECK（birthday◆添加具有默认值的可为空的列：
在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。
Altertablestudent
AddmatriculationdaysmalldatetimeNULL
Constraintadddateflt
Defaultgetdate（）
三、表的删除
删除表格相对于创建表格来说要简单的多，在SQLServer2000下修改表有两种方法：
1．在企业管理器中进行表的删除，步骤如下：
1树型结构上，展开要删除的表所在的数据库
2择该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表格
3择表格，可以同时按下Ctrl键或Shift键选择多哥要执行操作的表格
4标右击，从弹出的快捷菜单中选择【删除】命令，弹出2-4所示的对话框。
对话框中列出了所有将被删除的表格。
可以选择其中的表格，然后单击【显示相关性】按钮，查看与该表格相关的数据库对象的信息。
5击【全部除去】按钮删除所有选中的表。
注意：
删除表格必须小心，因为表格一旦删除便无法恢复。
而表格中原先包含的数据也将随着表格的删除而消失。
要想挽回被无意中删除的数据的唯一方法是从原先的数据库中备份恢复。
2．删除表对象，我们可以在企业管理器中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP删除表的定义及表中的所有数据、索引、触发器、约束和权限规范。
如要删除ta1表，语法为：
DROPTABLEtable_name
例：
删除当前连接的数据库中的一个名字为ta1的表格和删除pubs数据库中的example的表格的语句如下：
DROPTABLEta1
DROPTABLEpubs.dbo.example
注意：
删除表格的权力只属于表格的拥有者，并且不能转移，不能使用DROPTABLE语句删除系统表。
练习题
练习1在Student数据库中利用查询分析器创建课程信息表Course、表结构如下：
主键
列名
数据类型
宽度
小数位
空否
备注
Pk
Cno
Char
3
N
课程代码
Cname
Char
20
Y
课程名称
Cpno
Char
3
Y
先行课号
Credit
Decimal
3
1
Y
学分
利用Transact-SQL语句创建选课成绩表Sc和操作员表Operator,表结构如下：
主码
列名
数据类型
宽度
小数位
空否
备注
Pk
Sno
Char
5
N
学号
Cno
Char
3
N
课程代码
Grade
Decimal
5
1
Y
成绩
主码
列名
数据类型
宽度
小数位
空否
备注
Pk
Operator_id
Char
4
N
操作员编号
Operator_name
Char
12
Y
操作员姓名
Password
Char
10
Y
密码
Permission
Char
20
Y
权限
练习2表的管理
⑴用企业管理器创建教工表teacher，要求如下：
列名
数据类型
允许为空？
约束
IDENTITY属性
教工号
Int
否
主键
无
姓名
Varchar（20）
是
无
无
家庭住址
Char（30）
是
无
无
电话
Int
是
无
无
性别
Char
（2）
否
默认为‘男’
无
系别
Varchar（16）
否
必须是（‘计算机软件’、‘计算机体系结构’、‘计算机网络’）之一
无
⑵在查询分析器中用命令：
DROPTABLEteacher删除该表。
⑶根据条件在查询分析器中输入以下语句：
CREATETABLEteacher
（
教工号INTPRIMARYKEYIDENTITY（1,1）,
姓名VARCHAR（18）NULL,
家庭住址CHAR（30）NULL,
电话INTNULL,
职称CHAR（14）DEFAULT'讲师',
所在专业VARCHAR（16）CONSTRAINTchname1
CHECK（所在专业IN（'计算机体系结构','计算机网络'，‘计算机软件’））
）
点击【查询分析】（CTRL+F5）按钮，看看执行结果。
练习3查看SQLServer2000中系统自带数据库pubs的表信息，熟悉各表之间的相互关系。
升级会员 