用T-SQL指令创建数据库和表.doc

实验报告课程名称数据库系统原理实验日期10月10日实验项目名称用T-SQL指令创建数据库和表实验地点数据库实验室实验类型验证型 设计型综合型 学 时2一、实验目的及要求（本实验所涉及并要求掌握的知识点）1、掌握用T-SQL语句创建数据库和表的方法，掌握主键和外键的设置方法2、在SQL Server Management Studio中查看每个表的主键设置（若用SQL语句创建表时未设置主键，则在此进行设置），将SC表的（Sno，Cno）属性组设置为主键，验证Sno和Cno两个主属性均不能被设置为空值，从而理解关系的实体完整性规则。3、理解并掌握“关系图”的作用和建立方法。在SQL Server Management Studio中为创建的表建立“关系图”，从而理解关系的参照完整性规则（它与实体完整性规则一起被称为关系的两个不变性，由RDBMS自动支持）。4、学习用SQL语句创建和修改基本表结构的方法。5、初步了解用T-SQL指令插入数据的方法。6、掌握用SQL指令修改表结构的方法7、掌握用T-SQL语句删除数据库和表的方法二、实验环境（本实验所使用的硬件设备和相关软件）安装XP操作系统的电脑一台Sql server2005开发版安装软件IIS5.0 三、实验内容及步骤1、通过新建查询用SQL指令创建教材P82图3.2学生-课程数据库，数据库名为MyDB在SQL Server Management Studio中，单击工具栏上的新建查询，然后在查询窗口中输入下面SQL语句：注意：FILENAME指定要创建数据库的物理文件名，即数据文件的物理存放位置，一定要确保给出的文件路径是真实存在的，如果该路径不存在，请先建立路径然后再创建数据库，否则会提示对文件的目录查找失败，出现操作系统错误3(系统找不到指定的路径。)错误的信息。单击工具栏中的“执行”图标，执行以上SQL语句。执行该命令后用下列方法查看数据库MyDB是否创建成功：l 若执行成功，则在左侧对象浏览器窗口中会看到MyDB数据库的图标。若执行成功但仍未看到MyDB图标，可右键单击左侧对象浏览器窗口中的SQL Server服务器，选择“刷新”命令。l 在右侧查询窗口中执行下面系统存储过程，可看到SQL Server实例中所有数据库（包括系统数据库）：sp_databases说明：系统存储过程sp_databases返回master数据库中的sysdatabases系统表中列出的数据库，可用下面SQL语句验证：SELECT * FROM sysdatabasesGO2、用T-SQL指令创建数据库MyDB的三张表Student、Course和SC 首先阅读下面内容：设计表时需要确定如下内容：l 表中需要的列以及每一列的类型(必要时还要有长度)。l 列是否可以为空。l 是否需要在列上使用约束、默认值和规则。l 需要使用什么样的索引。l 哪些列作为主键。当前数据库为master数据库表的设计要体现完整性约束的实现。实体完整性约束的体现是主键约束，即主键的各列不能为空，且主键作为行的唯一标识；外键约束是参照完整性约束的体现；默认值和规则等是用户定义的完整性约束的体现。（1）改变当前数据库为MyDB默认情况下登录SQL SERVER服务器后，以master数据库为当前数据库，如上图所示，应先使用下列命令改变当前数据库为MyDB数据库：USE MyDBGO执行该命令后，发现查询窗口的标题栏发生了变化，master被换成了MyDB，说明命令执行成功，当前数据库为MyDB。（2）在数据库MyDB中建立Student表在查询窗口中输入下面SQL语句：执行该命令后用下列方法查看MyDB的Student表是否创建成功：l 若执行成功，则在左侧对象浏览器窗口中展开MyDB数据库中的“表”会看到名为“dbo.Student”表的图标（dbo即数据库拥有者，表示Student表是由数据库的拥有者创建的）。若执行成功但仍未看到该图标，可右键单击左侧对象浏览器窗口中的MyDB数据库，选择“刷新”命令。l 在右侧查询窗口中执行下面系统存储过程，可看到SQL Server实例中所有数据库（包括系统数据库）：sp_tables说明：系统存储过程sp_tables返回当前环境下所有可查询的对象，即任何可出现在SQL语句的FROM子句中的对象（包括系统表、用户表和视图）。执行结果的TABLE_TYPE列显示“SYSTEM TABLE”则为系统表、“TABLE”为用户表、“VIEW”为视图。l 实际上这些数据库对象是被存储在master数据库中的sysobjects系统表中的。因此，可以通过下面SQL语句列出MyDB数据库中的所有表：其中，S表示系统表，U表示用户表，V表示视图，PK表示主码索引、UK表示唯一索引。（3）在数据库MyDB中建立Course表在查询窗口中输入下面SQL语句：按上述方法查看MyDB的Course表是否创建成功。（4）在数据库MyDB中建立SC表在查询窗口中输入下面SQL语句：按上述方法查看MyDB的SC表是否创建成功，在对象资源管理器中，取消SC表的（Sno，Cno）属性组的主键设置，(方法是：在对象资源管理器中选中SC表，单击右键选择修改，按Ctrl或Shift同时选中选中设置主键的列Sno和Cno列，单击工具栏上的主键按钮或单击右键，选“主键”，取消对主键的设置)然后重新将其设置为主键，加深理解主键（主码）的概念，掌握取消主键设置的方法（按Ctrl或Shift同时选中两个属性后，右键单击之，选“主键”）和将一个属性组设置为主键的方法（同取消主键设置的方法相同）。然后验证Sno和Cno两个主属性均不能被设置为空值，从而理解关系的实体完整性规则。注意：不能将创建以上3个表的SQL语句放在一个批内一起执行，这样，在创建SC表时，可能造成由于Student与Course尚未创建成功，因此无法在其上设置参照完整性约束（GO的作用是将SQL语句分批处理，即一个批内的SQL语句编译和执行后，再编译和执行另一个批）。也可以在表创建成功后再增加表级完整性约束条件：3、理解并掌握“关系图”的作用和建立方法在对象资源管理器中为创建的表建立“关系图”，在对象资源管理器中，找到要建立关系图的数据库，选择数据库关系图，单击右键选择新建数据库关系图，理解关系的参照完整性规则（它与实体完整性规则一起被称为关系的两个不变性，由RDBMS自动支持）。4、学习用SQL语句修改基本表的结构的方法。（1）在上面创建的MyDb数据库中的学生(Student)表添加Telehpone属性列，要求数据类型为11个长度的char(定长字符型)。（2）更改MyDb数据库中学生(Student)表的Sdept属性列为16个长度的varchar（可变长度的字符型）（3）为MyDb数据库中学生(sc)表的成绩(grade)列建立用户自定义约束，要求成绩必须在0到100的范围内。这里有10个0-9具体用法到查询部分详细讲解（4）为MyDb数据库中学生(student)表的学号(sno)列建立用户自定义约束，要求学号必须有10为阿拉伯数字组成。有关like关键字的用法，后面会详细讲解。（5）删除MyDb数据库中学生(sc)表在grade属性列上的check约束, 约束名为ck_grade。（6）删除第1题中添加的Telehpone属性列（7）为MyDb数据库中学生(Student)表的Ssex列添加默认值约束，设置其默认值为男，默认值约束的名字为Stu_sex_default阅读并验证教材P87例8-例10，若实验不通过请分析原因（注意是否存在表级约束条件）。5、初步了解在查询分析器中数据的插入、修改和删除方法完成下列操作，初步了解插入、修改和删除数据的SQL语句格式（教材P115P118）： （1）向Student表中插入两条记录（2）修改Student表中的记录：将李勇从信息系（IS）转入计科系（CS）（3）删除Student表中李勇的记录：李勇退学（4）完善数据库按教材P82图3.2所示内容向学生-课程数据库的3个表中添加数据。6、验证参照的完整性约束中级联更新和级联删除(1) 级联删除从学生表中删除学号为200215122的学生指令如下：查看成绩表中的信息输入如下指令，看学号为200215122学生的成绩是否级联删除 (2) 级联更新把学号为200215121学生的学号修改为200215128，指令如下： 查看成绩表中的信息输入如下指令，看学号为200215121学生的学号是否级联更新8、掌握用T-SQL语句删除数据库和表的方法（1）删除表格式：DROP TABLE （2）删除数据库：格式：DROP DATABASE 操作：USE master /*改变当前数据库为要删除数据库以外的数据库，否则可能提示MyDB数据库正在使用无法删除*/DROP DATABASE MyDB四、实验结果（本实验源程序清单及运行结果或实验结论、实验设计图）1.create database MyDbon(name=MyDb_data,filename=d:sqlMyDb_data.mdf,size=3,maxsize=4,filegrowth=10%)log on(name=MyDb_log,filename=d:sqlMyDb_log.ldf,size=1,maxsize=2,filegrowth=10%)2.(2) use MyDbgocreate table student(sno char(9) primary key,sname char(6) not null,ssex char(2) default(男),sage int,sdept varchar(10)(3) create table course (cno varchar(3) primary key,/*裂解完整性约束*/cname varchar(12),cpno varchar(3),ccredit int)(4)create table sc(sno char(9) not null,cno varchar(3) not null,grade int,primary key (sno,cno),foreign key(sno)references student (sno) on delete cascade on update cascade,foreign key(sno)references course (cno) on delete cascade on update cascade)4.(1)alter table studentadd Telephone char(11)(2) alter table stedentalter column Sdept varchar(16)(3)alter table scadd constraint ck_grade check (grade=0 and grade =10)(4)alter table studentadd constraint ck_sno check (sno like 0-90-90-90-90-90-90-90-90-90-9 )(5)alter table scdrop ck_grade(6)alter table studentdrop column Telephone(7)alter table studentadd constraint Stu_sex_default defa