实验2-1、基本表的创建与约束机制

1、实验内容版权所有，转载请注明来源 2022-01-17 by 黄玉兰实验二 基本表的创建与约束机制一、 实验目的1、熟悉SQL语言的基本语法2、掌握T-SQL语句创建、修改表的方法及建立约束的方法3、掌握使用SSMS创建、修改表的方法及建立约束的方法4、掌握T-SQL语句对表中数据进行操作5、会利用SSMS对表中数据进行操作6、理解并掌握五种约束机制的基本概念二、 实验内容和要求在SQL Server 2005中用SSMS和T-SQL语句两种方式建立并管理数据库，进行实验所要求的各种操作，所有的SQL操作均在以自己姓名命名的数据库里进行，请完成下列操作。【基本知识拓展，请认真阅读，完成并理解实

2、验任务】（一）数据库的基本概念数据库文件SQL Server 2005中的文件通常有两种类型：逻辑文件名和物理文件名。1、 逻辑文件名是在所有T-SQL语句中引用物理文件时所使用的名称。逻辑文件名必须符合标识符的命令规则，而且数据库中的逻辑文件名必须是唯一的。2、物理文件名是包括目录路径的物理文件名。它必须符合操作系统的命名规则。逻辑文件名和物理文件名是一一对应的，其对应关系由SQL Server系统来维护。SQL Server 2005数据库文件有3类：（1）主数据文件（也称主文件）：主数据文件主要用来存储数据库的启动信息、部分或全部数据，是数据库的关键文件。主数据文件是数据库的起点，包含指

3、向数据库中其他文件的指针。每个数据库都有一个主数据库文件。主数据库文件推荐扩展名为.mdf。（2）次要数据文件（也称辅助数据文件）：除主数据文件以外的所有其他数据文件都是次要数据文件。用于存储主数据文件中未存储的剩余数据和数据库对象。一个数据库可以没有，也可以有多个次要数据文件。次要数据文件推荐扩展名为 .ndf。（3）事务日志文件（简称日志文件）：存放用来恢复数据库所需的事务日志信息，每个数据库必须有一个或多个日志文件。事务日志文件推荐扩展名为.ldf。 一般情况下，一个数据库可以只有一个主数据库文件和一个事务日志文件组成，如果数据库很大，则可以设置多个次要数据文件和多个日志文件，

4、并将它们放在不同的磁盘上，以便提高数据存取和处理的效率。 【注意】SQL Server 2005不强制使用文件扩展名，但使用上述推荐扩展名，有利于标识文件的各种用途和类型。数据库的分类系统数据库依次打开SSMS中“对象资源管理器”对话框中的“服务器”|“数据库”|“系统数据库”文件夹，可以看到4个系统数据库，如图2-1所示。1 SQL Server 2005系统数据库分别是master数据库、tempdb数据库、model数据库和msdb数据库。（1）master数据库 master数据库记录SQL Server系统的所有系统级信息。包括实例范围内的元数据（如登录账户）、端点、链接服务器和系统

5、配置数据设置。master数据库记录了所有其他数据库是否存在以及这些数据库文件的位置。另外，数据库还记录了SQL Server的初始化信息。因此，如果master数据库不可用，则SQL Server将无法启动。（2）tempdb数据库 tempdb数据库是连接到SQL Server实例的所有用户都可用的全局资源，它保存了所有临时表和临时存储过程。另外，它还用来满足所有其他临时存储的要求，如存储SQL Server生成的临时工作表。每次启动SQL Server时，都要重新创建tempdb，以便系统启动时，该数据库总是空的。在断开连接时，系统会自动删除临时表和存储过程，并且在系统关闭后没有活动链接

6、。因此，tempdb中不会有什么内容从一个SQL Server会话保存到另一个会话。（3）model数据库 model数据库是在SQL Server实例上创建的所有数据库的模板。因为每次启动SQL Server时都会创建tempdb数据库，所以tempdb数据库必须始终存在于SQL Server系统中。model数据库相当于一个模子，所有在系统中创建的数据库的内容，在刚创建时都和数据库完全一样。可以在数据库中创建表或其它数据库对象，这些对象可以供以后建立的数据库所继承。（4）msdb数据库 msdb数据库由SQL Server代理（SQL Server Agent）来计划警报和作业。2实例数据

7、库 AdventureWorks、AdventureWorks DW是SQL Server 2005中的实例数据库，此类数据库是基于一个生产公司，以简单、易于理解的方式来展示SQL Server 2005。3用户数据库 用户根据数据库设计创建的数据库，一般是用来解决某一具体实际问题的数据库。如图书管理数据库，教务管理数据库等。（二）数据库的操作和管理1. 创建数据库 T-SQL提供了数据库创建语句CREATE DATABASE，其语法形式如下： CREATE DATABASE 数据库名ON <文件定义> ,n,<文件组>,nLOG ON < 文件定义 > ,

8、n其中，各参数的含义如下： 数据库名：是所创建数据库的逻辑名称。数据库名称在当前服务器中必须唯一且符合标识符的命名规则，最多可以包含128个字符； ON：用于指定数据文件及文件组属性，具体属性值在<文件定义>中指定；<文件定义>的详细格式如下： <文件定义>：=PRIMARY（NAME=逻辑文件名，FILENAME=存放数据库的物理路径和文件名,SIZE=数据文件的初始大小,MAXSIZE=指定文件的最大大小,FILEGROWTH=指定文件每次的增量） LOG ON ：用于指定事务日志文件的属性，具体属性值在<filespec>中指定。 如果在定

9、义时没有指定ON子句和LON ON 子句，系统将默认设置，自动生成一个主数据文件和一个事务日志文件，并将文件存储在系统默认路径上。下面分别用SSMS和T-sql形式介绍具体操作，请大家完成实验任务。实验任务2-1（利用SSMS工具）创建一个名为TestSGMS的测试数据库，文件及其他选项均为默认。 在任务管理器中，数据库选项处右击鼠标，点击新建数据库，在弹出的对话框中，左侧“选项页”中包括“常规”、“选项”和“文件组”三项。大家可一一展开查看。在“常规”选项卡中，可以设置新建数据库的名称、数据库的所有者、数据文件、事务日志文件等信息。观察相应的参数，无误后点击确定。如图所示。实验任务2-2（用

10、T-sql代码） 创建一个名为SGMS学生成绩管理系统数据库。要求有3个文件，其中，主数据文件为10MB，最大大小为50MB，每次增长20%；辅助数据文件属于文件组fgroup，文件为10MB，大小不受限制，每次增长10MB；事务日志文件大小为20MB，最大大小为100MB，每次增长10MB。文件存储为C:db路径下。（看懂即可，此题不需要实现）【注意】（1）服务器中不能存在同名的数据库名，所以类似CREATE语句均只能正确执行一次，下一次执行时，将提示该对象已存在；（2）创建数据库之前，文件存储路径C:db必须存在。2表的设计与创建表的设计与创建应遵循以下流程（1）定义表的结构（2）设置约束

11、（3）输入原始数据下面分别用SSMS和T-sql形式介绍具体操作，请大家完成实验任务，切忌只看不动手。实验任务2-3（利用T-sql工具）创建一个名为studentTest的基本表。点击新建查询，打开代码编辑窗口，输入以下代码，选择正确的数据库，点击感叹号执行按钮，显示命名已成功完成。此时表已经建立完成。可用select * from studentTest查看表。实验任务2-4（利用SSMS工具）创建一个名为courseTest的基本表。在左边对象资源管理器中，选择正确的数据库，展开后在“表”选项上，右击鼠标，选择“新建表”，打开表结构设计器。输入相关参数如列名，数据类型以及第一层约束：是否

12、允许为空值，最后在选项卡上右击鼠标选择保存时为表命名：courseTest。刷新左边资源管理器即可看到此表已经创建完成，可将此表逐步展开查看刚设置的相关列信息。3数据的插入、更新和删除实验任务2-5（利用T-sql代码） 向courseTest表中添加一条记录，记录信息为：（110001,数据库系统概论,4）。用全表查询方式查看或者刷新资源对象管理器，选择打开表查看。刷新查看效果实验任务2-6（利用T-sql代码） 向courseTest表中添加一条不完全记录，记录信息为：（110002,Java程序设计）。可以不给全部列赋值，但是没有赋值的列必须是可以为空的列。此时列名和数据必须一一对应。用

13、全表查询方式查看或者刷新资源对象管理器，选择打开表查看。（into可省略）刷新查看效果实验任务2-7（利用T-sql代码）对courseTest表中数据进行修改，将课程号是110002的课程的课程名称修改为“web应用系统设计”。刷新查看效果。思考批量数据修改时哪种方法更好？实验任务2-8（利用T-sql代码）对courseTest表中数据进行删除，将课程号是110002的课程删除。用delete语句此操作会记录日志。刷新查看效果实验任务2-9（利用T-sql代码）对courseTest表中数据全部删除。注意此时表并未被删除，只是表中数据被删除，结果是空表。用Truncate语句操作不会记录日

14、志，只记录整个数据页的释放操作，所以比delete要快。刷新查看效果实验任务2-10（利用SSMS工具）对studentTest表中数据进行以上更新修改删除操作。对象资源管理器中进行鼠标点击操作。刷新查看效果。（图略）4表的删除实验任务2-11（利用T-sql工具）删除studentTest的基本表。在代码编辑窗口，输入如下代码，并执行，刷新资源对象管理器中查看删除效果。实验任务2-12（利用SSMS工具）删除courseTest的基本表。在左边对象资源管理器中找到courseTest表，右击鼠标选择删除，选项，在弹出的对话框中点击确定即可删除表。（图略）【拓展知识部分结束，请按照要求完成以下

15、操作（课内未完成的同学请于课后及时完成，以免影响下周实验课的进度）】根据实际问题的需求，学生成绩管理系统数据库（以各自的姓名命名此数据库），设计9张表：创建方式SSMS工具或T-sql自选 (具体表的结构设计如下)表结构设计好之后，向表中输入数据时必须遵循所设置的约束条件，否则服务器将拒绝接受所输入的非法数据。（录入数据工作等下次实验课完成约束条件后再进行）Department名称数据类型可否为空说明DepartmentIDChar(3)否系部编号DepartmentNameVarchar（30）否系部们称DepartmentHeadChar（8）是部门负责人Teacher名称数据类型可否为空

16、说明TeacherIDChar(8)否教师编号teacherNameVarchar（10）否教师姓名departmentIDChar（3）是教师所在系部IDsexChar（2）是性别technicalPostchar（16）是职称TelephoneChar（16）是电话homeAddrVarchar（50）是家庭住址pwdVarchar（16）是密码remarknvarchar（200）是备注Course名称数据类型可否为空说明courseIDChar(8)否课程编号coursenameVarchar（20）否课程名称coursetypeIDChar（3）是课程类型编号totalperiodt

17、inyint是总课时weekperiodtinyint是周课时credithourtinyint是学分（>0）remarkVarchar（50）是备注Student名称数据类型可否为空说明studentIDChar(10)否学生编号studentNameVarchar（10）否学生姓名nationChar（10）是民族sexChar(2)是性别(默认男或女，默认男)birthdaydatetime是出生日期classIDChar(7)是班级编号telephoneVarchar（16)是联系电话credithourtinyint否已修学分(>0)ru_dateChar(4)是入学年份Speciality名称数据类型可否为空说明SpecialityidChar(5)否专业编号SpecialityNameVarchar（30）否专业名称DepartmentidChar（3）是专业所在系部IDCoursetype名称数据类型可否为空说明CoursetypeidChar(3)否课程类型编号TypenameVarchar（18）否课程类型名称Class名称数据类型可否为空说明classIDChar(7)否班级编号classNameVarchar（12）否班级名称SpecialitynameChar(5