伍宏淳 3114000825 my软件工程与数据库技术实验指导及报告.doc

2017版软件工程及数据库技术实验报告实验报告作者（姓名及学号）：伍宏淳 3114000825 实验指导教师： 鲍芳 实验时间： 2017-2018（1）实验班级： 自动化学院自动化14级 班实验中心三号楼 广东工业大学广东工业大学实验报告 自动化 学院 自动化 专业 1 班 成绩评定_学号3114000825姓名 伍宏淳 (合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验 一 题目 数据库的定义、建立和维护实验 第_周星期_第_节一、 实验目的与要求要求熟练掌握和使用SQL、SQLServer Management Studio创建数据库、表、索引和修改表结构，并学会使用SQL Server查询分析器接受SQL语句和进行结果分析；要求熟练掌握和使用SQL、SQLServer Management Studio向数据库输入数据、修改数据和删除数据操作。二、 实验方案1、 在SQLServer Management Studio中创建及打开数据库，在“新建数据库”时指明数据文件及事务日志的位置；2、 在SQLServer Management Studio中用SQL语句实现以下各表：学生（学号，姓名，性别，出生日期，所属学院，已通过选课数）；课程（课程号，课程名，先行课号）；选课（学号，课程号，成绩）；3、 建立库、表和表间的联系，选择合适的数据类型，定义必要的列级约束（包括性别约束男或女，已通过选课数的缺省值为0；成绩约束0100，允许为null）；定义表级约束（参照完整性约束）。创建数据库中的表并刷新后，在对象资源管理器窗口可见该表及其所拥有的键及约束：学生：键：1，约束：2；课程：键：1；选课：键：3，约束：2。4、 通过SQLServer Management Studio实现对学生成绩管理数据库的数据增加、数据删除和数据修改操作。要求每个表的记录在10行以上（可参考实验二至四的查询任务要求设计表中记录）；输入数据检验各数据约束的限制；数据更改和数据删除时检验外码约束；三、 实验结果和数据处理1、给出实现数据库表的SQL语句；实验的SQL语句：CREATE TABLE 学生 (学号 CHAR(5) NOT NULL PRIMARY KEY, 姓名 CHAR(8) NOT NULL, 性别 CHAR(2), 出生日期 datetime not null, 所属学院 CHAR(20), 已通过选课数 INT DEFAULT 0, CONSTRAINT C2 CHECK (性别 IN (男,女); CREATE TABLE 课程 (课程号 CHAR(5) PRIMARY KEY, 课程名 CHAR(20), 先行课号 CHAR(30);CREATE TABLE 选课 (学号 CHAR(5), 课程号 CHAR(5), 成绩 SMALLINT,CONSTRAINT C3 CHECK (成绩 BETWEEN 0 AND 100),CONSTRAINT C4 PRIMARY KEY (学号,课程号),CONSTRAINT C5 FOREIGN KEY (学号) REFERENCES 学生(学号),CONSTRAINT C6 FOREIGN KEY (课程号) REFERENCES 课程(课程号);上面的语句为三个表的创建语句。2、给出用SQL语句实现数据增加、数据删除和数据修改的共6个例句；增加语句：insertinto 学生(学号,姓名,性别,出生日期,所属学院)values (S05,红唇,男,1995-03-24,数学);数据修改语句：update 选课set 成绩=成绩*0.5where exists(select*from 课程where 课程名=模拟电子技术)在表中插入子查询的结果集：CREATE TABLE 学院平均年龄 (学院 CHAR(20),平均年龄SMALLINT); Insert Into学院平均年龄 Select 学院, avg(all 年龄)From 学生Group by 学院;数据删除语句：Delect from 选课Where 学号 in (select 学号From 学生Where 学院=自动化); 3、以截图形式附上所设计的数据库表的样本数据，每张表的记录为10行；学生：课程：选课：四、 结论答：创建表时，先要设计好表内的内容和约束，添加约束可以防止在增加表中内容时，排除一些及其错误性的内容。在添加内容的时候有多行添加和单行添加，删除行，更新行，可以根据需要对数据库进行SQL语句操作。五、 问题与讨论1、 举例说明创建外键的SQL语句中的参照表和被参照表各指什么？举例说明如何在数据更改和数据删除时检验外码约束？举例说明创建外键时有无ON DELETE CASCADE 关键字对删除记录有何影响？（两位同学为一组，设计不同的外键约束实验方案后对比实验结果）答：1. ALTER TABLE 选课ADDCONSTRAINTFK_课程号 FOREIGN KEY(课程号) REFERENCES 课程(课程号)参照表：选课，被参照表：课程2. 删除学生表的学生信息，选课表相应的信息也会删除。3. ON DELETE CASCADE指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。2、 举例说明如何在输入数据时检验各数据约束的限制?属性“成绩”成绩输入为null或0时有区别吗？答：1. 运行程序的时候，看看有没有报错误。2. 区别，null是无数据，并不代表0，0是已经把该学生的成绩变为0了。3、 （可选）如果考虑课程表中的递归关系，应该如何修改你的sql语句？在向此表插入数据时应该注意什么问题？4、 实验中遇到的问题及解决方法。答：遇到的问题：实验中遇到的问题主要是数据类型的选择和格式问题，数据类型中选择null跟int类型是不同概念，最后用INT DEFAULT 0来代替null，已选课程没有的时候为0；问题所在：在写创建外键的SQL语句时，忘了写外键的被参照表表名；解决方法：写上外键的被参照表表名。广东工业大学实验报告 自动化 学院 自动化 专业 1 班 成绩评定_学号3114000825姓名 伍宏淳 (合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验 二 题目数据库的简单查询和连接查询实验 第_周星期_第_节一、 实验目的与要求要求熟练掌握和使用SQL、SQLServer Management Studio对数据库进行简单表的数据查询、数据排序和和数据联结查询的操作方法，加深对SQL语言的查询语句的理解。二、 实验方案通过SQLServer Management Studio实现对学生成绩管理数据库的以下简单查询和连接查询：1. 求数学系学生的学号和姓名；2. （optional）查询数学系学生的信息，包括学号、姓名、性别和年龄（提示：year（getdate（）-year(出生日期) as 年龄）；3. 求选修了课程的学生学号；4. 求选修课程C1的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列；5. 求选修课程C1且成绩在8090分之间的学生学号和期末考试成绩，其中期末考试成绩为选修表中成绩乘以系数0.8；6. 求数学系或计算机系姓张的学生的信息；7. 求缺少了成绩的学生的学号和课程号；8. 查询每门课程的先行课程名；三、 实验结果和数据处理（给出各SQL语句及对应查询结果）1. select 姓名,学号 from 学生 where 所属学院=数学2. select 姓名,学号,性别,year(getdate()-year(出生日期) as 年龄 from 学生 where 所属学院=数学3. select 学号 from 选课 where 课程号 is not null4. select 学号,成绩 from 选课 where 课程号=c1 order by 成绩 desc, 学号5. select 学号,0.8*成绩 as 期末成绩 from 选课 where (课程号=c1) and (成绩=80 and 成绩( select 成绩 from 选课 where 课程号 =c1 and 学号= (select 学号 from 学生 where 姓名=张三);3、 求选修C1课程号的学生学号； Select 学号 From 学生Where exists( select *From 选课Where 学生.学号=学号and课程号=c1)方法二： Select 学号From 学生,选课Where 学生.学号=选课.学号and课程号=c1;4、 求没有选修C2课程号的学生学号；Select 学号From 学生Where not exists( select *From 选课Where 学生.学号=学号and课程号=c2)方法二：Select 学号From 学生,选课Where 学生.学号=选课.学号and课程号c1;5、 求选修C1课程而没有选修C2课程的学生姓名；Select 学号From 选课Where 课程号=c1Minus Select 学号From 选课Where 课程号=c2方法二：select distinct 姓名 from(select 学号,姓名 from 学生)a,(select 学号 from 选课 where 课程号=c1)b,(select 学号 from 选课 where 课程号!=c2)cwhere a.学号=b.学号 and b.学号=c.学号;6、 求选修C1及C2课程的学生姓名（使用关键字INTERSECT）； 通过SQLServer Management Studio实现以下对数据库的统计查询；Select 学号From 选课Where 课程号=c1Intersect Select 学号From 选课Where 课程号=c2方法二：select 姓名 from 学生 where 学号=(select 学号 from 选课 where 课程号=c1INTERSECTselect 学号 from 选课 where 课程号=c2);7、用SQL语句实现计算机系学生人数的统计；select count(*) as 计算机系学生总数 from 学生 where 所属学院=计算机;8、用SQL语句实现各系学生人数的统计；select count(*) as 院系学生总数, 所属学院 as 院系 from 学生 group by 所属学院;9、统计被选课程及选修该课程的人数；select 课程号,课程名,选课人数 from (select 课程号 as 选课课程号,count(*) as 选课人数 from 选课 group by 课程号)a,(select 课程号,课程名 from 课程)bwhere a.选课课程号=b.课程号;方法二：select 课程号, count(*) from 选课 group by 课程号;10、用SQL语句统计选修了课程的学生人数；（提示：剔除重复记录需要使用关键字.）select COUNT(distinct 学号) as 已选课学生人数 from 选课;11、统计各门课程的选修情况（要求显示未被选修课程及选修某课程的人数等）；（提示：使用左连接）select 课程号,课程名,选课人数 from(select 课程号,课程名 from 课程)a left join (select 课程号 as 选课课程号,count(*) as 选课人数 from 选课 group by 课程号)b on a.课程号=b.选课课程号;12、用SQL语句统计每个学生全部课程的平均成绩；select 学号,姓名,平均成绩 from(select 学号,姓名 from 学生)a left join (select 学号 as 选课学号,AVG(成绩) as 平均成绩 from 选课 group by 学号)b on a.学号=b.选课学号;13、用SQL语句统计选修课超过3门课的学生学号；（提示：使用having ）select 学号 from 选课 group by 学号 having COUNT(*)3;四、 结论1、数据的输入必须符合主键约束，切主键的值不能为空，不能重复；2、主键的值不能修改，删除记录必须遵从参照完整性约束；3、更新的数据必须符合属性所设置的数据类型；4、根据嵌套的可以解决大部分的查询问题，大大提高效率，但是在查询过程中要确定好之间的关系，只有这样才能写出有效的SQL语句，查询到理想的结果。五、 问题与讨论1、 嵌套查询和连接查询有何区别？在本实验的查询任务中，哪些是可以由嵌套查询和连接查询实现？答：若一个查询同时涉及两个或两个以上的表，则称之为连接查询。嵌套查询是指在一个外层查询中包含有另一个内层查询，即一个SQL查询语句块可以嵌套在另一个查询块的WHERE子句中。嵌套查询是可以用连接来代替的，而且使用连接的方式，性能要比嵌套查询高出很多。实验中有很多可以用到，例如：求选修C1课程号的学生学号；求没有选修C2课程号的学生学号；求选修C1课程而没有选修C2课程的学生姓名。2、 针对查询任务4“求没有选修C2课程号的学生学号”，SQL语句“SELECT 学号 FROM 选课WHERE 课程号 C2 ”可以完成该查询任务吗？为什么？答：不可以，符号不合法，必须使用逻辑and的方法代替。3、 试用GROUP BY（分组条件）字句后，语句中的统计函数的运行结果有什么不同？答：用GROUP BY字句后，列值相等都集中以一个元组的形式出现。运行结果会根据GROUP BY后面接的列来分开统计，WHERE一般在GROUP BY前面，HAVING一般在GROUP BY后面。广东工业大学实验报告 自动化 学院 自动化 专业 1 班 成绩评定_学号3114000825姓名 伍宏淳 (合作者_号_) 教师签名_预习情况操作情况考勤情况数据处理情况实验四题目视图、存储过程与触发器的定义及使用实验 第_周星期_第_节一、 实验目的与要求掌握SQLServer Management Studio中视图的创建和使用，加深对视图和SQL Server图表作用的理解；掌握SQLServer Management Studio中存储过程及触发器的创建方法及其使用方法，了解视图与存储过程的异同，约束与触发器的区别。二、 实验方案通过SQLServer Management Studio实现成绩管理数据库的视图、存储过程及触发器的创建及使用，完成以下任务：1、 建立自动化学院学生信息统计的视图，要求包括自动化学院学生的学号、姓名、性别和年龄；2、 创建一个带输入参数的存储过程selectByDepartmentWithInput。该存储过程的作用是：当给定基本表学生所属学院名称时，将返回该学院所有学生的基本信息。3、 执行selectByDepartmentWithInput存储过程，查询“自动化”学院的学生信息。4、 为选课表建立一个名为tri_NumberPassedAdded的INSERT触发器，当用户向该表中插入记录时，如果成绩大于或等于60，则令学生表中该学生的属性“已通过选课数”加1，否则显示“无成绩或成绩低于60，不需要更新学生表中的已通过课程数！”5、 由学生、课程和选课三个表，定义一个自动化学院的学生成绩视图，其属性包括学号、姓名、课程名和成绩；6、 将学生的学号、平均成绩定义成一个视图“学生平均成绩”，并基于该视图统计学生的学号、姓名、平均成绩及已通过选课数；三、实验结果和数据处理1、 建立自动化学院学生信息统计的视图，要求包括自动化学院学生的学号、姓名、性别和年龄；CREATE VIEW 自动化学院学生信息统计 ASSELECT 学号,姓名,性别,year(getdate()-year(出生日期) as 年龄 from 学生 where 所属学院=自动化;select * from 自动化学院学生信息统计;2、 创建一个带输入参数的存储过程selectByDepartmentWithInput。该存储过程的作用是：当给定基本表学生所属学院名称时，将返回该学院所有学生的基本信息。CREATE PROCEDURE selectByDepartmentWithInputCampusName varchar(20)AS SELECT 学号,姓名,性别,year(getdate()-year(出生日期) as 年龄 from 学生 where 所属学院=CampusName;GO3、 执行selectByDepartmentWithInput存储过程，查询“自动化”学院的学生信息。exec selectByDepartmentWithInput 自动化;4、 为选课表建立一个名为tri_NumberPassedAdded的INSERT触发器，当用户向该表中插入记录时，如果成绩大于或等于60，则令学生表中该学生的属性“已通过选课数”加1，否则显示“无成绩或成绩低于60，不需要更新学生表中的已通过课程数！”CREATE TRIGGER tri_NumberPassedAdded ON 选课for insertAS IF (select 成绩 from inserted)=60 update 学生 set 已通过选课数=已通过选课数+1 where 学号=(select 学号 from inserted)ELSE print(无成绩或成绩低于60，不需要更新学生表中的已通过课程数！);5、 由学生、课程和选课三个表，定义一个自动化学院的学生成绩视图，其属性包括学号、姓名、课程名和成绩；CREATE VIEW 自动化学院的学生成绩 ASSELECT 学号,姓名,课程名,成绩from 学生 join (select 学号 as 成绩学号,课程名,成绩 from 选课 left join 课程 on 选课.课程号=课程.课程号 where 成绩 is not null)a on 学生.学号=a.成绩学号; select * from 自动化学院的学生成绩;6、 将学生的学号、平均成绩定义成一个视图“学生平均成绩”，并基于该视图统计学生的学号、姓名、平均成绩及已通过选课数；CREATE VIEW 学生平均成绩