实训一数据库和表的创建.docx

1、实训一数据库和表的创建实训一 数据库和表的创建实训目的(1) 掌握数据库和表的基础知识。(2) 掌握使用企业管理器和Transact-SQL语句创建数据库和表的方法。(3) 掌握数据库和表的修改、查看、删除等基本操作方法。实训内容和要求1数据库的创建、查看、修改和删除(1) 使用企业管理器创建数据库创建成绩管理数据库Grademanager，要求见表10-1。表10-1 Grademanager数据库参数表参 数参 数 值参 数参 数 值存储的数据文件路径D:dbgrademanager_data.mdf存储的日志文件路径D:dbgrademanager_log.ldf数据文件初始大小5MB日

2、志文件初始大小2MB数据文件最大值20MB日志文件最大值15MB数据文件增长量原来10%日志文件增长量2MB(2) 查看与修改数据库属性 在企业管理器中查看创建后的grademanager数据库，查看grademanager_data.mdf、grademanager_log.ldf两个数据库文件所处的文件夹。 使用企业管理器更改数据库。更改的参数见表10-2。表10-2 要更改的参数表参 数参 数 值参 数参 数 值增加的文件组名grademanagergroup增加的数据文件路径D:grademanager1_data.ndf增加的日志文件路径C:grademanager1_log.ldf

3、增加的数据文件初始大小7MB增加的日志文件初始大小3MB增加的数据文件最大值20MB增加的日志文件最大值30MB增加的数据文件增长量2MB增加的日志文件增长量2MB(3) 使用企业管理器删除该数据库(4) 使用Transact-SQL命令创建上述要求的数据库(5) 使用Transact-SQL命令查看和修改上述要求的数据库(6) 使用Transact-SQL命令删除该数据库2表的创建、查看、修改和删除(1) 在Grademanager数据库中创建如表10-3、表10-4和表10-5所示结构的表。表10-3 Student表的表结构字段名称数据类型长度精度小数位数是否允许NULL值说明Snoch

4、ar1000否主码Snamevarchar800是Ssexchar200是取值：男或女Sbirthdaydatetime800是Sdeptchar1600是Specialityvarchar2000是表10-4 Course表(课程名称表)的表结构字段名称数据类型长 度精 度小数位数是否允许NULL值说 明Cnochar500否主码Cnamevarchar2000否表10-5 SC表(成绩表)的表结构字段名称数据类型长 度精 度小数位数是否允许NULL值说 明Snochar1000否外码Cnochar500否外码Degreedecimal551是1100之间(2) 向表10-3、表10-4和表

5、10-5输入数据记录，见表10-6、表10-7和表10-8。表10-6 学生关系表StudentSnoSnameSsexSbirthdaySdeptSpeciality李勇男1987-01-12CS计算机应用刘晨女1988-06-04IS电子商务王敏女1989-12-23MA数学张立男1988-08-25IS电子商务表10-7 课程关系表CourseCnoCnameCnoCnameC01数据库C03信息系统C02数学C04操作系统表10-8 成绩表SCSnoCnoDegreeC0192C0285C0388C0290C0380(3) 修改表结构。 向student表中增加“入学时间”列，其数据类

6、型为日期时间型。 将student表中的sdept字段长度改为20。 将student表中的Speciality字段删除。(4) 删除student表。思考题(1) SQL Server的数据库文件有几种？扩展名分别是什么？(2) SQL Server 2000中有哪几种整型数据类型？它们占用的存储空间分别是多少？取值范围分别是什么？(3) 在定义基本表语句时，NOT NULL参数的作用是什么？(4) 主码可以建立在“值可以为NULL”的列上吗？实训二 单表查询实训目的(1) 掌握SELECT语句的基本用法。(2) 使用WHERE子句进行有条件的查询。(3) 掌握使用IN和NOT IN，BET

7、WEENAND和NOT BETWEENAND来缩小查询范围的方法。(4) 掌握聚集函数的使用方法。(5) 利用LIKE子句实现字符串匹配查询。(6) 利用ORDER BY子句对查询结果排序。(7) 利用GROUP BY子句对查询结果分组。实训内容和要求1表结构修改(1) 在实训一所创建的数据库中增加Teacher表，表结构见表10-9。表10-9 Teacher表(教师表)的表结构字段名称数据类型长 度精 度小数位数是否允许NULL值说 明Tnochar300否主码Tnamevarchar800是Tsexchar200是取值：男或女Tbirthdaydatetime800是Tdeptchar1

8、600是(2) 在实训一所创建的数据库中增加Teaching表，表结构见表10-10。表10-10 Teaching表(授课表)的表结构字段名称数据类型长 度精 度小数位数是否允许NULL值说 明Cnochar500否外码Tnochar300否外码Ctermtinyint100是15之间(3) 向表10-9和表10-10中输入数据记录，见表10-11和表10-12。表10-11 教师表TeacherTnoTnameTsexTbirthdayTdept101李新男1977-01-12CS102钱军女1968-06-04CS201王小花女1979-12-23IS202张小青男1968-08-25I

9、S表10-12 授课表TeachingCnoTnoCtermC011012C021021C032013C0420242完成下面查询(1) 查询年龄大于18岁的女生的学号和姓名。(2) 查询所有男生的信息。(3) 查询所有任课教师的Tname、Tdept。(4) 查询“电子商务”专业的学生姓名、性别和出生日期。(5) 查询成绩不及格的学生学号及课号，并按成绩降序排列。(6) 查询Student表中的所有系名。(7) 查询“C01”课程的开课学期。(8) 查询成绩在8090分之间的学生学号及课号。(9) 统计有学生选修的课程的门数。(10) 查询在1970年1月1日之前出生的男教师信息。(11)

10、计算“C01”课程的平均成绩。(12) 输出有成绩的学生学号。(13) 查询所有姓“刘”的学生信息。(14) 统计输出各系学生的人数。(15) 查询成绩为79分、89分或99分的记录。(16) 查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列。(17) 查询各个课程号及相应的选课人数。(18) 统计每门课程的选课人数和最高分。(19) 统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。(20) 查询选修了3门以上课程的学生学号。思考题(1) 聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？(2) 关键字ALL和DIS

11、TINCT有什么不同的含义？(3) SELECT语句中的通配符有几种？含义分别是什么？(4) 数据的范围除了可以利用BETWEENAND运算符表示外能否用其他方法表示？怎样表示？实训三 多表连接查询实训目的(1) 掌握SELECT语句在多表查询中的应用。(2) 掌握多表连接的几种连接方式及应用。实训内容和要求(1) 查询计算机系(CS)女学生的学生学号、姓名及考试成绩。(2) 查询“李勇”同学所选课程的成绩。(3) 查询“李新”老师所授课程的课程名称。(4) 查询女教师所授课程的课程号及课程名称。(5) 查询至少选修一门课程的女学生姓名。(6) 查询姓“王”的学生所学的课程名称。(7) 查询选

12、修“数据库”课程且成绩在8090分之间的学生学号及成绩。(8) 查询课程成绩及格的男同学的学生信息及课程号与成绩。(9) 查询选修“C04”课程的学生的平均年龄。(10) 查询学习课程名为“数学”的学生学号和姓名。(11) 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。(12) 查询在第3学期所开课程的课程名称及成绩。思考题(1) 指定一个较短的别名有什么好处？(2) 内连接与外连接有什么区别？实训四 嵌 套 查 询实训目的(1) 掌握嵌套查询的使用方法。(2) 掌握相关子查询与嵌套子查询的区别。(3) 掌握带IN谓词的子查询的使用方法。(4) 掌握带比较运算符的子查询的使用方法

13、。(5) 掌握带ANY或ALL谓词的子查询的使用方法。(6) 掌握带EXISTS谓词的子查询的使用方法。实训内容和要求(1) 查询与“李勇”同一个系的同学姓名。(2) 查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。(3) 查询出生日期大于所有女同学出生日期的男同学的姓名及系别。(4) 查询成绩比该课程平均成绩高的学生的学号及成绩。(5) 查询不讲授“C01”课的教师姓名。(6) 查询没有选修“C02”课程的学生学号及姓名。(7) 查询选修了“数据库”课程的学生学号、姓名及系别。(8) 查询选修了全部课程的学生姓名。(9) 查询没有学生选修的课程号及课程名称。(10) 查询所有与李勇选修

14、课程相同的学生信息。(11) 分别用子查询和连接查询，求“C02”号课程不及格的学生信息。思考题(1) “=”与IN在什么情况下作用相同？(2) 使用存在量词NOTEXISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假？(3) 当既能用连接查询又能用嵌套查询时，应该选择哪种查询较好？为什么？实训五 数 据 更 新实训目的(1) 掌握利用INSERT命令实现对表数据的插入操作。(2) 掌握利用UPDATE命令实现对表数据的修改操作。(3) 掌握利用DELETE命令实现对表数据的删除操作。实训内容和要求利用SELECT INTO命令备份Student、SC、Course这3个表，备份

15、表名自定。(1) 向Student表中插入记录(，张静，1981-3-21，女，CS，电子商务)。(2) 插入学号为“”、姓名为“李四”的学生信息。(3) 把计算机系的学生记录保存到表TS中(TS表已存在，表结构与Student表相同)。(4) 将学号为“”的学生姓名改为“张华”，系别改为“CS”，专业改为“多媒体技术”。(5) 将“李勇”同学的专业改为“计算机信息管理”。(6) 将“”学生选修“C03”号课程的成绩改为该课的平均成绩。(7) 把成绩低于总平均成绩的女同学的成绩提高5%。(8) 把选修了“数据库”课程而成绩不及格的学生的成绩全改为空值(NULL)。(9) 删除学号为“”的学生记

16、录。(10) 删除“计算机系”所有学生的选课记录。(11) 删除SC表中尚无成绩的选课记录。(12) 把“张晨”同学的成绩全部删除。思考题(1) 如何从备份表中恢复3个表？(2) DROP命令和DELETE命令的本质区别是什么？(3) 利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗？实训六 索引与视图实训目的(1) 掌握索引的创建和使用。(2) 掌握视图的创建、修改和删除。(3) 掌握使用视图来访问数据。实训内容和要求1索引的创建与使用(1) 用索引管理器创建SC表的索引IX_XS_KC 选择要创建索引的数据库文件夹，并在右边的对象窗口中选择并打开其中的【表】对象。

17、 选择所要创建索引的SC表，并从【操作】菜单中选择【所有任务】子菜单下的【管理索引】命令，打开SQL Server 的索引管理器窗口。 单击其中的【新建】按钮，创建新的索引，并为其设置相应的属性。为SC表创建一个基于【课程号】列和【成绩】列的索引IX_XS_KC，其中课程号列按升序排列，成绩列按降序排列。首先，单击【新建】按钮，此时系统打开【新建索引】对话框，在其中的【列名】列表框中选择【课程号】选项。再选择【成绩】选项，并选中其后的【排列次序(DESC)】选项，使成绩列按降序排列。接着，选择【填充因子】选项，其值保留系统默认的80，并选中【填充索引】选项，使索引中间页具有与叶级页相同的填充程

18、度。最后，将索引名设置为IX_XS_KC。 单击【确定】按钮，完成新索引的创建。回到索引管理器窗口。(2) 强制使用刚才创建的索引查询数据 启动SQL Server查询分析器，打开【SQL查询分析器】窗口，并在工具栏的数据库下拉列表框中选择要操作的【Grademanager】数据库。 强制使用IX_XS_KC索引查询所有课程的及格成绩记录。在查询命令窗口中输入以下SQL查询命令并执行：SELECT SNO,CNO,DEGREEFROM SCWITH (INDEX (IX_XS_KC)WHERE DEGREE=60观察一下显示出来的数据是否有序。(3) 学生练习(分别利用企业管理器和Transa

19、ct-SQL命令创建) 为Student表创建一个名为S_dept的索引，以系别排序。 为Teacher表创建一个名为T_name的聚集索引，以Tname降序排序。 查看Student表的索引。 删除S_dept索引。2视图的创建与使用(1) 利用企业管理器创建视图 启动SQL Server企业管理器，打开【SQL Server Enterprise Manager】窗口。 选择要创建视图的Grademanager数据库，并在右边的对象窗口中选择其中的【视图】对象。 选择【操作】菜单中的【新建视图】命令，打开SQL Server的视图设计窗口。 在【数据源关系图】窗口中右击，打开【添加表】窗口

20、，添加Student表和SC表。 选择Student表的Sno和Sname列，选择SC表的Cno和Degree列，作为视图的显示列。 设置Sno列的排序类型为升序。 设置查询条件：先在Cno行的【准则】列设置条件为“=C01”，然后在Degree行的【准则】列设置条件为“=70”，并复制该行，取消【输出】列中的复选，并修改【准则】列的条件为“=90”。 单击快捷工具栏上的快捷按钮，关闭视图设计窗口，保存对视图的修改。(2) 学生练习(分别利用企业管理器和Transact-SQL命令创建) 创建一个简单视图，查询“计算机系”学生的信息。 创建一个简单视图，统计每门课程的选课人数和最高分。 创建一

21、个复杂视图，查询与“张立”同学同一系别的学生信息。 创建一个复杂视图，查询选修了课程的同学的姓名、课程名及成绩。思考题(1) 使用索引为什么能提高查询速度？(2) 索引经常被创建在哪些字段上？(3) 聚集索引与其他索引有什么不同？实训七 存储过程与触发器实训目的(1) 理解存储过程和触发器的功能及特点。(2) 学会使用Transact-SQL编写存储过程和触发器的方法。(3) 学会如何使用企业管理器创建存储过程和触发器。(4) 掌握存储过程的创建、执行与删除操作。(5) 掌握触发器的创建、修改和删除操作。(6) 理解可以使用存储过程和触发器来维护数据的完整性。实训内容和要求1存储过程的创建、执

22、行与删除(1) 创建带输入参数的存储过程 利用企业管理器创建一个带输入参数的存储过程proc_Stud1，其中的输入参数用于接收课程号，默认值为“C01”，然后在SC表中查询该课成绩不及格的学生学号，接着 在Student表中查找这些学生的基本信息，包括学号、姓名、性别、系别和专业信息，最后输出。 单击存储过程属性对话框中的【语法检查】按钮，对输入的CREATE PROCEDURE 语句进行语法分析。如果有语法错误，则进行修改，直到没有语法错误为止。 在查询分析器中使用EXECUTE命令执行上述存储过程。(2) 创建带嵌套调用的存储过程 在查询分析器窗口中输入创建存储过程的CREATE PRO

23、CEDURE语句。创建一个带嵌套调用的存储过程proc_Stud2。该存储过程也有一个输入参数，它用于接收授课教师的姓名，默认值为“李新”，然后嵌套调用存储过程proc_Cno，输出其所授课程的课程号，接着用此课程号来完成上一部分实训中所创建的存储过程proc_Stud1的功能。相应的CREATE PROCEDURE语句如下：(授课课老师 char(8)=李新)DECLARE 课程号char(3)-嵌套调用存储过程proc_CnoEXECUTE proc_Cno授课老师，课程号 OUTPUT-查询指定课程成绩不及格的学生的基本信息SELECT a.Sno，a.Sname，a.Ssex，a.Sd

24、eptFROM Student a，SC bWHERE b.Cno=课程号AND b.Degree60AND a.Sno=b.Snoproc_Cno的存储过程如下：CREATE PROCEDURE proc_Cno教师 char(10)=李新课程号 char(3) OUTPUTASSELECT 课程号=Cno FROM Teaching a,Teacher bWHERE a.Tno=b.Tno and b.Tname=教师 单击工具栏上的快捷键，对输入的CREATE PROCEDURE 语句进行语法分析。如果有语法错误，则进行修改，直到没有语法错误为止。 单击工具栏上的快捷按钮，执行CREAT

25、E PROCEDURE语句。(3) 执行所创建的两个存储过程 在查询分析窗口中输入以下EXECUTE语句，执行存储过程proc_Stud1。EXECUTE proc_Stud1 C03 单击工具栏上的快捷键，执行存储过程。 在查询分析器窗口中输入以下EXECUTE语句，执行存储过程proc_Stud2。EXECUTE proc_Stud2 DEFAULT 单击工具栏上的快捷键，执行存储过程。(4）删除新建的存储过程。 在查询分析器窗口中输入DROP PROCEDURE语句和所有新创建的存储过程名。DROP PROCEDURE Proc_Stud1，proc_Stud2 单击工具栏上的快捷键，删

26、除存储过程。2触发器的创建、执行和删除(1) 创建触发器。 在企业管理器中为SC表创建一个触发器tri_UPDATE_DELETE_SC，该触发器是基于UPDATE操作和DELETE操作的复合型触发器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器激活生效，显示相关的操作信息。-创建触发器CREATE TRIGGER tri_UPDATE_DELETE_SCON SCFOR UPDATE，DELETEAS-检测成绩列表是否被更新IF UPDATE(Degree)BEGIN-显示学号、课程号、原成绩和新成绩信息SELECT INSERTED.Cno，DELETED.Degree AS原成绩

27、，INSERTED.Degree AS原成绩 FROM DELETED,INSERTEDWHERE DELETED.Sno=INSERTED.SnoEND-检查是更新还是删除操作ELSE IF COLUMNS_UPDATED( )=0BEGIN-显示被删除的学号、课程号和成绩信号SELECT 被删除的学号=DELETED.Sno，DELETED.CnoDELETED.Degree AS 原成绩FROM DELETEDENDELSE-返回提示信息PRINT 更新了非成绩列！ 单击【确定】按钮，完成触发器的创建。(2) 激活触发器。 在查询分析器窗口中输入以下UPDATE SC语句，修改成绩列，激发触发器。UPDATE SC SET Degree=Degree+5WHERE Cno=C01 在查询分析器窗口中输入以下UPDATE SC语句修改非成绩列，激发触发器。UPDATE SC SET Cno=C13WHERE Cno=C03 在查询命令窗口中输入以下DELETE SC语句，删除成绩记录，激发