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

1、数据库原理及应用 山东信息职业技术学院1实训一 数据库和表的创建实训目的(1) 掌握数据库和表的基础知识。(2) 掌握使用企业管理器和 Transact-SQL 语句创建数据库和表的方法。(3) 掌握数据库和表的修改、查看、删除等基本操作方法。实训内容和要求1数据库的创建、查看、修改和删除(1) 使用企业管理器创建数据库创建成绩管理数据库 Grademanager，要求见表 10-1。表 10-1 Grademanager 数据库参数表参 数 参 数 值 参 数 参 数 值存储的数据文件路径 D:dbgrademanager_data.mdf 存储的日志文件路径 D:dbgrademanage

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

3、_data.ndf增加的日志文件路径 C:grademanager1_log.ldf增加的数据文件初始大小7MB 增加的日志文件初始大小3MB增加的数据文件最大值 20MB 增加的日志文件最大值 30MB增加的数据文件增长量 2MB 增加的日志文件增长量 2MB(3) 使用企业管理器删除该数据库(4) 使用 Transact-SQL 命令创建上述要求的数据库(5) 使用 Transact-SQL 命令查看和修改上述要求的数据库(6) 使用 Transact-SQL 命令删除该数据库2表的创建、查看、修改和删除(1) 在 Grademanager 数据库中创建如表 10-3、表 10-4 和表

4、10-5 所示结构的表。数据库原理及应用 山东信息职业技术学院2表 10-3 Student 表的表结构字段名称 数据类型 长度 精度 小数位数 是否允许 NULL 值 说明Sno char 10 0 0 否 主码Sname varchar 8 0 0 是Ssex char 2 0 0 是 取值：男或女Sbirthday datetime 8 0 0 是Sdept char 16 0 0 是Speciality varchar 20 0 0 是表 10-4 Course 表(课程名称表) 的表结构字段名称 数据类型 长 度 精 度 小数位数 是否允许 NULL 值 说 明Cno char 5

5、0 0 否 主码Cname varchar 20 0 0 否表 10-5 SC 表(成绩表) 的表结构字段名称 数据类型 长 度 精 度 小数位数 是否允许 NULL 值 说 明Sno char 10 0 0 否 外码Cno char 5 0 0 否 外码Degree decimal 5 5 1 是 1100 之间(2) 向表 10-3、表 10-4 和表 10-5 输入数据记录，见表 10-6、表 10-7 和表 10-8。表 10-6 学生关系表 StudentSno Sname Ssex Sbirthday Sdept Speciality20050101 李勇 男 1987-01-12

6、 CS 计算机应用20050201 刘晨 女 1988-06-04 IS 电子商务20050301 王敏 女 1989-12-23 MA 数学20050202 张立 男 1988-08-25 IS 电子商务表 10-7 课程关系表 CourseCno Cname Cno CnameC01 数据库 C03 信息系统C02 数学 C04 操作系统表 10-8 成绩表 SCSno Cno Degree20050101 C01 9220050101 C02 8520050101 C03 8820050201 C02 9020050201 C03 80(3) 修改表结构。 向 student 表中增加“

7、入学时间”列，其数据类型为日期时间型。 将 student 表中的 sdept 字段长度改为 20。数据库原理及应用 山东信息职业技术学院3 将 student 表中的 Speciality 字段删除。(4) 删除 student 表。思考题(1) SQL Server 的数据库文件有几种？扩展名分别是什么？(2) SQL Server 2000 中有哪几种整型数据类型？它们占用的存储空间分别是多少？取值范围分别是什么？(3) 在定义基本表语句时，NOT NULL 参数的作用是什么？(4) 主码可以建立在“值可以为 NULL”的列上吗？实训二 单表查询实训目的(1) 掌握 SELECT 语句的

8、基本用法。(2) 使用 WHERE 子句进行有条件的查询。(3) 掌握使用 IN 和 NOT IN， BETWEENAND 和 NOT BETWEENAND 来缩小查询范围的方法。(4) 掌握聚集函数的使用方法。(5) 利用 LIKE 子句实现字符串匹配查询。(6) 利用 ORDER BY 子句对查询结果排序。(7) 利用 GROUP BY 子句对查询结果分组。实训内容和要求1表结构修改(1) 在实训一所创建的数据库中增加 Teacher 表，表结构见表 10-9。表 10-9 Teacher 表(教师表)的表结构字段名称 数据类型 长 度 精 度 小数位数 是否允许 NULL 值 说 明Tn

9、o char 3 0 0 否 主码Tname varchar 8 0 0 是Tsex char 2 0 0 是 取值：男或女Tbirthday datetime 8 0 0 是Tdept char 16 0 0 是(2) 在实训一所创建的数据库中增加 Teaching 表，表结构见表 10-10。表 10-10 Teaching 表(授课表)的表结构字段名称 数据类型 长 度 精 度 小数位数 是否允许 NULL 值 说 明Cno char 5 0 0 否 外码Tno char 3 0 0 否 外码Cterm tinyint 1 0 0 是 15 之间(3) 向表 10-9 和表 10-10

10、中输入数据记录，见表 10-11 和表 10-12。表 10-11 教师表 TeacherTno Tname Tsex Tbirthday Tdept101 李新 男 1977-01-12 CS数据库原理及应用 山东信息职业技术学院4102 钱军 女 1968-06-04 CS201 王小花 女 1979-12-23 IS202 张小青 男 1968-08-25 IS表 10-12 授课表 TeachingCno Tno CtermC01 101 2C02 102 1C03 201 3C04 202 42完成下面查询(1) 查询年龄大于 18 岁的女生的学号和姓名。(2) 查询所有男生的信息。

11、(3) 查询所有任课教师的 Tname、Tdept。(4) 查询“电子商务”专业的学生姓名、性别和出生日期。(5) 查询成绩不及格的学生学号及课号，并按成绩降序排列。(6) 查询 Student 表中的所有系名。(7) 查询“C01”课程的开课学期。(8) 查询成绩在 8090 分之间的学生学号及课号。(9) 统计有学生选修的课程的门数。(10) 查询在 1970 年 1 月 1 日之前出生的男教师信息。(11) 计算“C01”课程的平均成绩。(12) 输出有成绩的学生学号。(13) 查询所有姓“刘”的学生信息。(14) 统计输出各系学生的人数。(15) 查询成绩为 79 分、89 分或 99

12、 分的记录。(16) 查询选修了“C03 ”课程的学生的学号及其成绩，查询结果按分数降序排列。(17) 查询各个课程号及相应的选课人数。(18) 统计每门课程的选课人数和最高分。(19) 统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。(20) 查询选修了 3 门以上课程的学生学号。思考题(1) 聚集函数能否直接使用在 SELECT 子句、HAVING 子句、WHERE 子句、GROUP BY 子句中？(2) 关键字 ALL 和 DISTINCT 有什么不同的含义？(3) SELECT 语句中的通配符有几种？含义分别是什么？(4) 数据的范围除了可以利用 BETWEENAND 运算符

13、表示外能否用其他方法表示？怎样表示？实训三 多表连接查询实训目的(1) 掌握 SELECT 语句在多表查询中的应用。(2) 掌握多表连接的几种连接方式及应用。数据库原理及应用 山东信息职业技术学院5实训内容和要求(1) 查询计算机系(CS) 女学生的学生学号、姓名及考试成绩。(2) 查询“李勇”同学所选课程的成绩。(3) 查询“李新”老师所授课程的课程名称。(4) 查询女教师所授课程的课程号及课程名称。(5) 查询至少选修一门课程的女学生姓名。(6) 查询姓“王”的学生所学的课程名称。(7) 查询选修“数据库”课程且成绩在 8090 分之间的学生学号及成绩。(8) 查询课程成绩及格的男同学的学

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

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

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

17、，备份表名自定。(1) 向 Student 表中插入记录(“20050203“，“ 张静“，“1981-3-21“，“女“，“CS“ ，“电子商务“)。(2) 插入学号为“20050302”、姓名为“李四”的学生信息。(3) 把计算机系的学生记录保存到表 TS 中(TS 表已存在，表结构与 Student 表相同)。(4) 将学号为“20050202”的学生姓名改为“张华”，系别改为 “CS”，专业改为“多媒体技术”。(5) 将“李勇”同学的专业改为“计算机信息管理”。(6) 将“20050201”学生选修“C03”号课程的成绩改为该课的平均成绩。(7) 把成绩低于总平均成绩的女同学的成绩提高

18、 5%。(8) 把选修了“数据库”课程而成绩不及格的学生的成绩全改为空值 (NULL)。(9) 删除学号为“20050302”的学生记录。(10) 删除“计算机系”所有学生的选课记录。(11) 删除 SC 表中尚无成绩的选课记录。(12) 把“张晨”同学的成绩全部删除。思考题(1) 如何从备份表中恢复 3 个表？(2) DROP 命令和 DELETE 命令的本质区别是什么？(3) 利用 INSERT、UPDATE 和 DELETE 命令可以同时对多个表进行操作吗？数据库原理及应用 山东信息职业技术学院7实训六 索引与视图实训目的(1) 掌握索引的创建和使用。(2) 掌握视图的创建、修改和删除。

19、(3) 掌握使用视图来访问数据。实训内容和要求1索引的创建与使用(1) 用索引管理器创建 SC 表的索引 IX_XS_KC 选择要创建索引的数据库文件夹，并在右边的对象窗口中选择并打开其中的【表】对象。 选择所要创建索引的 SC 表，并从【操作】菜单中选择【所有任务】子菜单下的【管理索引】命令，打开 SQL Server 的索引管理器窗口。 单击其中的【新建】按钮，创建新的索引，并为其设置相应的属性。为 SC 表创建一个基于【课程号】列和【成绩】列的索引 IX_XS_KC，其中课程号列按升序排列，成绩列按降序排列。首先，单击【新建】按钮，此时系统打开【新建索引】对话框，在其中的【列名】列表框中

20、选择【课程号】选项。再选择【成绩】选项，并选中其后的【排列次序(DESC)】选项，使成绩列按降序排列。接着，选择【填充因子】选项，其值保留系统默认的 80，并选中【填充索引】选项，使索引中间页具有与叶级页相同的填充程度。最后，将索引名设置为 IX_XS_KC。 单击【确定】按钮，完成新索引的创建。回到索引管理器窗口。(2) 强制使用刚才创建的索引查询数据 启动 SQL Server 查询分析器，打开【SQL 查询分析器】窗口，并在工具栏的数据库下拉列表框中选择要操作的【Grademanager】数据库。 强制使用 IX_XS_KC 索引查询所有课程的及格成绩记录。在查询命令窗口中输入以下 SQ

21、L 查询命令并执行：SELECT SNO,CNO,DEGREEFROM SCWITH (INDEX (IX_XS_KC)WHERE DEGREE=60观察一下显示出来的数据是否有序。(3) 学生练习(分别利用企业管理器和 Transact-SQL 命令创建 ) 为 Student 表创建一个名为 S_dept 的索引，以系别排序。 为 Teacher 表创建一个名为 T_name 的聚集索引，以 Tname 降序排序。 查看 Student 表的索引。 删除 S_dept 索引。2视图的创建与使用(1) 利用企业管理器创建视图 启动 SQL Server 企业管理器，打开 【SQL Serve

22、r Enterprise Manager】窗口。数据库原理及应用 山东信息职业技术学院8 选择要创建视图的 Grademanager 数据库，并在右边的对象窗口中选择其中的 【视图】对象。 选择【操作】菜单中的【新建视图】命令，打开 SQL Server 的视图设计窗口。 在【数据源关系图】窗口中右击，打开【添加表】窗口，添加 Student 表和 SC 表。 选择 Student 表的 Sno 和 Sname 列，选择 SC 表的 Cno 和 Degree 列，作为视图的显示列。 设置 Sno 列的排序类型为升序。 设置查询条件：先在 Cno 行的【准则】列设置条件为“=C01 ”，然后在

23、Degree 行的【准则】列设置条件为“=70 ”，并复制该行，取消【输出】列中的复选，并修改 【准则】列的条件为“=90 ”。单击快捷工具栏上的快捷按钮，关闭视图设计窗口，保存对视图的修改。13(2) 学生练习(分别利用企业管理器和 Transact-SQL 命令创建 ) 创建一个简单视图，查询“计算机系”学生的信息。 创建一个简单视图，统计每门课程的选课人数和最高分。 创建一个复杂视图，查询与“张立”同学同一系别的学生信息。 创建一个复杂视图，查询选修了课程的同学的姓名、课程名及成绩。思考题(1) 使用索引为什么能提高查询速度？(2) 索引经常被创建在哪些字段上？(3) 聚集索引与其他索引

24、有什么不同？数据库原理及应用 山东信息职业技术学院9实训七 存储过程与触发器实训目的(1) 理解存储过程和触发器的功能及特点。(2) 学会使用 Transact-SQL 编写存储过程和触发器的方法。(3) 学会如何使用企业管理器创建存储过程和触发器。(4) 掌握存储过程的创建、执行与删除操作。(5) 掌握触发器的创建、修改和删除操作。(6) 理解可以使用存储过程和触发器来维护数据的完整性。实训内容和要求1存储过程的创建、执行与删除(1) 创建带输入参数的存储过程 利用企业管理器创建一个带输入参数的存储过程 proc_Stud1，其中的输入参数用于接收课程号，默认值为“C01”，然后在 SC 表

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

26、存储过程 proc_Cno，输出其所授课程的课程号，接着用此课程号来完成上一部分实训中所创建的存储过程 proc_Stud1 的功能。相应的 CREATE PROCEDURE 语句如下：(授课课老师 char(8)=李新)DECLARE 课程号 char(3)-嵌套调用存储过程 proc_CnoEXECUTE proc_Cno授课老师， 课程号 OUTPUT-查询指定课程成绩不及格的学生的基本信息SELECT a.Sno，a.Sname，a.Ssex，a.SdeptFROM Student a，SC bWHERE b.Cno=课程号AND b.Degree60AND a.Sno=b.Snopr

27、oc_Cno 的存储过程如下：CREATE PROCEDURE proc_Cno教师 char(10)=李新课程号 char(3) OUTPUTASSELECT 课程号=Cno FROM Teaching a,Teacher bWHERE a.Tno=b.Tno and b.Tname=教师 数据库原理及应用 山东信息职业技术学院10 单击工具栏上的快捷键，对输入的 CREATE PROCEDURE 语句进行语法分析。如果有语法错误，则进行修改，直到没有语法错误为止。 单击工具栏上的快捷按钮，执行 CREATE PROCEDURE 语句。(3) 执行所创建的两个存储过程 在查询分析窗口中输入以

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

29、理器中为 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 原成绩，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 更新了非成绩列！ 单击【确定】按钮，完成触发器的创建。