伍宏淳 3114000825 my软件工程与数据库技术实验指导及报告.docx
《伍宏淳 3114000825 my软件工程与数据库技术实验指导及报告.docx》由会员分享，可在线阅读，更多相关《伍宏淳 3114000825 my软件工程与数据库技术实验指导及报告.docx（22页珍藏版）》请在冰豆网上搜索。
伍宏淳3114000825my软件工程与数据库技术实验指导及报告
2017版
《软件工程及数据库技术》
实验报告
实验报告作者（姓名及学号）：
伍宏淳**********
实验指导教师：
鲍芳
实验时间：
2017-2018
（1）
实验班级：
自动化学院自动化14级班
实验中心三号楼
广东工业大学
广东工业大学实验报告
自动化学院自动化专业1班成绩评定_______
学号**********姓名伍宏淳（合作者____号____）教师签名_______
预习情况
操作情况
考勤情况
数据处理情况
实验一题目数据库的定义、建立和维护实验第___周星期___第___节
一、实验目的与要求
要求熟练掌握和使用SQL、SQLServerManagementStudio创建数据库、表、索引和修改表结构，并学会使用SQLServer查询分析器接受SQL语句和进行结果分析；
要求熟练掌握和使用SQL、SQLServerManagementStudio向数据库输入数据、修改数据和删除数据操作。
二、实验方案
1、在SQLServerManagementStudio中创建及打开数据库，在“新建数据库”时指明数据文件及事务日志的位置；
2、在SQLServerManagementStudio中用SQL语句实现以下各表：
学生（学号，姓名，性别，出生日期，所属学院，已通过选课数）；
课程（课程号，课程名，先行课号）；
选课（学号，课程号，成绩）；
3、建立库、表和表间的联系，选择合适的数据类型，定义必要的列级约束（包括性别约束‘男’或‘女’，已通过选课数的缺省值为0；成绩约束0~100，允许为null）；定义表级约束（参照完整性约束）。
创建数据库中的表并刷新后，在对象资源管理器窗口可见该表及其所拥有的键及约束：
学生：
键：
1，约束：
2；课程：
键：
1；选课：
键：
3，约束：
2。
4、通过SQLServerManagementStudio实现对学生成绩管理数据库的数据增加、数据删除和数据修改操作。
要求每个表的记录在10行以上（可参考实验二至四的查询任务要求设计表中记录）；输入数据检验各数据约束的限制；数据更改和数据删除时检验外码约束；
三、实验结果和数据处理
1、给出实现数据库表的SQL语句；
实验的SQL语句：
CREATETABLE学生（学号CHAR（5）NOTNULLPRIMARYKEY,
姓名CHAR（8）NOTNULL,
性别CHAR
（2）,
出生日期datetimenotnull,
所属学院CHAR（20）,
已通过选课数INTDEFAULT0,
CONSTRAINTC2CHECK（性别IN（'男','女'）））;
CREATETABLE课程（课程号CHAR（5）PRIMARYKEY,
课程名CHAR（20）,
先行课号CHAR（30））;
CREATETABLE选课（学号CHAR（5）,
课程号CHAR（5）,
成绩SMALLINT,
CONSTRAINTC3CHECK（成绩BETWEEN0AND100）,
CONSTRAINTC4PRIMARYKEY（学号,课程号）,
CONSTRAINTC5FOREIGNKEY（学号）REFERENCES学生（学号）,
CONSTRAINTC6FOREIGNKEY（课程号）REFERENCES课程（课程号））;
上面的语句为三个表的创建语句。
2、给出用SQL语句实现数据增加、数据删除和数据修改的共6个例句；
增加语句：
insert
into学生（学号,姓名,性别,出生日期,所属学院）
values（'S05','红唇','男','1995-03-24','数学'）;
数据修改语句：
update选课
set成绩=成绩*0.5
whereexists（select*from课程
where课程名='模拟电子技术'）
在表中插入子查询的结果集：
CREATETABLE学院平均年龄（学院CHAR（20）,
平均年龄SMALLINT）;
Insert
Into学院平均年龄
Select学院,avg（all年龄）
From学生
Groupby学院;
数据删除语句：
Delectfrom选课
Where学号in（select学号
From学生
Where学院=’自动化’）;
3、以截图形式附上所设计的数据库表的样本数据，每张表的记录为10行；
学生：
课程：
选课：
四、结论
答：
创建表时，先要设计好表内的内容和约束，添加约束可以防止在增加表中内容时，排除一些及其错误性的内容。
在添加内容的时候有多行添加和单行添加，删除行，更新行，可以根据需要对数据库进行SQL语句操作。
五、问题与讨论
1、举例说明创建外键的SQL语句中的参照表和被参照表各指什么？
举例说明如何在数据更改和数据删除时检验外码约束？
举例说明创建外键时有无ONDELETECASCADE关键字对删除记录有何影响？
（两位同学为一组，设计不同的外键约束实验方案后对比实验结果）
答：
1.ALTERTABLE选课
ADDCONSTRAINTFK_课程号
FOREIGNKEY（课程号）REFERENCES课程（课程号）
参照表：
选课，被参照表：
课程
2.删除学生表的学生信息，选课表相应的信息也会删除。
3.ONDELETECASCADE指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。
如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。
2、举例说明如何在输入数据时检验各数据约束的限制?
属性“成绩”成绩输入为null或0时有区别吗？
答：
1.运行程序的时候，看看有没有报错误。
2.区别，null是无数据，并不代表0，0是已经把该学生的成绩变为0了。
3、（可选）如果考虑课程表中的递归关系，应该如何修改你的sql语句？
在向此表插入数据时应该注意什么问题？
4、实验中遇到的问题及解决方法。
答：
遇到的问题：
实验中遇到的问题主要是数据类型的选择和格式问题，数据类型中选择null跟int类型是不同概念，最后用INTDEFAULT0来代替null，已选课程没有的时候为0；
问题所在：
在写创建外键的SQL语句时，忘了写外键的被参照表表名；
解决方法：
写上外键的被参照表表名。
广东工业大学实验报告
自动化学院自动化专业1班成绩评定_______
学号3114000825姓名伍宏淳（合作者____号____）教师签名_______
预习情况
操作情况
考勤情况
数据处理情况
实验二题目数据库的简单查询和连接查询实验第___周星期___第___节
一、实验目的与要求
要求熟练掌握和使用SQL、SQLServerManagementStudio对数据库进行简单表的数据查询、数据排序和和数据联结查询的操作方法，加深对SQL语言的查询语句的理解。
二、实验方案
通过SQLServerManagementStudio实现对学生成绩管理数据库的以下简单查询和连接查询：
1.求数学系学生的学号和姓名；
2.（optional）查询数学系学生的信息，包括学号、姓名、性别和年龄（提示：
year（getdate（））-year（出生日期）as年龄）；
3.求选修了课程的学生学号；
4.求选修课程C1的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列；
5.求选修课程C1且成绩在80~90分之间的学生学号和期末考试成绩，其中期末考试成绩为选修表中成绩乘以系数0.8；
6.求数学系或计算机系姓张的学生的信息；
7.求缺少了成绩的学生的学号和课程号；
8.查询每门课程的先行课程名；
三、实验结果和数据处理
（给出各SQL语句及对应查询结果）
1.select姓名,学号from学生where所属学院='数学'
2.select姓名,学号,性别,year（getdate（））-year（出生日期）as年龄from学生where所属学院='数学'
3.select学号from选课where课程号isnotnull
4.select学号,成绩from选课where课程号='c1'orderby成绩desc,学号
5.select学号,0.8*成绩as期末成绩from选课where（课程号='c1'）and（成绩>=80and成绩<=90）
6.select*from学生where（所属学院='数学'or所属学院='计算机'）and姓名like'张%'
7.select学号,课程号from选课where成绩isnull
8.select课程号,课程名,先行课名from
（select课程号,课程名,先行课号from课程where先行课号isnotnull）a,
（select课程号as先行课号,课程名as先行课名from课程）b
Wherea.先行课号=b.先行课号
四、结论
l、同个数据库中，不允许有重名的基本表，表的命名格式必须遵从标示符的命名规则；
2、同个基本中不允许出现重复的列名，其命名格式必须遵从标示符的命名规则：
3、需要为表中的每一个属性设置数据类型：
4、一个基本表中只能定义一个主码约束，被定义为主码的列的值不能为空；
5、SQLSELECT子句指定哪些列将在查询结果集中列出
6、SQLFROM子句指定将从哪些表进行查询
7、SQLWHERE子句指定哪些行将在查询结果集中列出
五、问题与讨论
1、如何提高数据查询和连接速度？
答：
能够分开的操作尽量分开处理，提高每次的响应速度；
尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
对索引优化，建索引的选择必须结合SQL查询、修改、删除语句的需要，一般的说法是在WHERE里经常出现的字段建索引。
如果在WHERE经常是几个字段一起出现而且是用AND连接的，那就应该建这几个字段一起的联合索引，而且次序也需要考虑，一般是最常出现的放前面。
2、（可描述实验中遇到的问题及解决方法）
答：
问题：
在统计过程中，有重复项的加入。
解决：
使用DISTINCT来清楚重复项
广东工业大学实验报告
自动化学院自动化专业1班成绩评定_______
学号3114000825姓名伍宏淳（合作者____号____）教师签名_______
预习情况
操作情况
考勤情况
数据处理情况
实验三题目数据库的嵌套查询和统计查询实验第___周星期___第___节
一、实验目的与要求
进一步掌握SQLServerManagementStudio的使用方法，加深SQL语言的嵌套查询语句的理解；熟悉掌握数据查询中的分组和统计的操作方法。
二、实验方案
通过SQLServerManagementStudio实现对数据库的以下嵌套查询，并尝试用多种形式表示实验中的查询语句，并进行比较。
1、求选修了高等数学的学生学号和姓名；
2、求C1课程的成绩高于张三的C1课程成绩的学生学号和成绩；
3、求选修C1课程号的学生学号；
4、求没有选修C2课程号的学生学号；
5、求选修C1课程而没有选修C2课程的学生姓名；
6、求选修C1及C2课程的学生姓名（使用关键字INTERSECT）；
通过SQLServerManagementStudio实现以下对数据库的统计查询；
7、用SQL语句实现计算机系学生人数的统计；
8、用SQL语句实现各系学生人数的统计；
9、统计被选课程及选修该课程的人数；
10、用SQL语句统计选修了课程的学生人数；（提示：
剔除重复记录需要使用关键字…….）
11、统计各门课程的选修情况（要求显示未被选修课程及选修某课程的人数等）；（提示：
使用左连接）
12、用SQL语句统计每个学生全部课程的平均成绩；
13、用SQL语句统计选修课超过3门课的学生学号；（提示：
使用having）
三、实验结果和数据处理
1、求选修了高等数学的学生学号和姓名；
select学号,姓名from学生
where学号in（select学号from选课
where课程号in（select课程号
from课程
where课程名='高等数学'））;
方法二：
select学号,姓名from
（select学号,姓名from学生）a,
（select学号as选课学号,课程号as选课程号from选课）b,
（select课程号,课程名from课程）c
wherea.学号=b.选课学号andb.选课程号=c.课程号andc.课程名='高等数学';
2、求C1课程的成绩高于张三的C1课程成绩的学生学号和成绩；
select学号,成绩from选课
where课程号='c1'and成绩>（select成绩from选课
where课程号='c1'and学号=（select学号
from学生
where姓名='张三'））;
3、求选修C1课程号的学生学号；
Select学号
From学生
Whereexists（select*
From选课
Where学生.学号=学号and课程号=’c1’）
方法二：
Select学号
From学生,选课
Where学生.学号=选课.学号and课程号=’c1’;
4、求没有选修C2课程号的学生学号；
Select学号
From学生
Wherenotexists（select*
From选课
Where学生.学号=学号and课程号=’c2’
）
方法二：
Select学号
From学生,选课
Where学生.学号=选课.学号and课程号<>’c1’;
5、求选修C1课程而没有选修C2课程的学生姓名；
Select学号
From选课
Where课程号=’c1’
Minus
Select学号
From选课
Where课程号=’c2’
方法二：
selectdistinct姓名from
（select学号,姓名from学生）a,
（select学号from选课where课程号='c1'）b,
（select学号from选课where课程号!
='c2'）c
wherea.学号=b.学号andb.学号=c.学号;
6、求选修C1及C2课程的学生姓名（使用关键字INTERSECT）；
通过SQLServerManagementStudio实现以下对数据库的统计查询；
Select学号
From选课
Where课程号=’c1’
Intersect
Select学号
From选课
Where课程号=’c2’
方法二：
select姓名from学生where学号=（
select学号from选课where课程号=’c1’
INTERSECT
select学号from选课where课程号=’c2’）;
7、用SQL语句实现计算机系学生人数的统计；
selectcount（*）as计算机系学生总数
from学生where所属学院='计算机';
8、用SQL语句实现各系学生人数的统计；
selectcount（*）as院系学生总数,所属学院as院系
from学生groupby所属学院;
9、统计被选课程及选修该课程的人数；
select课程号,课程名,选课人数from
（select课程号as选课课程号,count（*）as选课人数from选课groupby课程号）a,
（select课程号,课程名from课程）b
wherea.选课课程号=b.课程号;
方法二：
select课程号,count（*）
from选课groupby课程号;
10、用SQL语句统计选修了课程的学生人数；（提示：
剔除重复记录需要使用关键字…….）
selectCOUNT（distinct学号）as已选课学生人数
from选课;
11、统计各门课程的选修情况（要求显示未被选修课程及选修某课程的人数等）；（提示：
使用左连接）
select课程号,课程名,选课人数from
（select课程号,课程名from课程）aleftjoin
（select课程号as选课课程号,count（*）as选课人数from选课groupby课程号）b
ona.课程号=b.选课课程号;
12、用SQL语句统计每个学生全部课程的平均成绩；
select学号,姓名,平均成绩from
（select学号,姓名from学生）aleftjoin
（select学号as选课学号,AVG（成绩）as平均成绩from选课groupby学号）b
ona.学号=b.选课学号;
13、用SQL语句统计选修课超过3门课的学生学号；（提示：
使用having）
select学号from选课groupby学号havingCOUNT（*）>3;
四、结论
1、数据的输入必须符合主键约束，切主键的值不能为空，不能重复；
2、主键的值不能修改，删除记录必须遵从参照完整性约束；
3、更新的数据必须符合属性所设置的数据类型；
4、根据嵌套的可以解决大部分的查询问题，大大提高效率，但是在查询过程中要确定好之间的关系，只有这样才能写出有效的SQL语句，查询到理想的结果。
五、问题与讨论
1、嵌套查询和连接查询有何区别？
在本实验的查询任务中，哪些是可以由嵌套查询和连接查询实现？
答：
若一个查询同时涉及两个或两个以上的表，则称之为连接查询。
嵌套查询是指在一个外层查询中包含有另一个内层查询，即一个SQL查询语句块可以嵌套在另一个查询块的WHERE子句中。
嵌套查询是可以用连接来代替的，而且使用连接的方式，性能要比嵌套查询高出很多。
②实验中有很多可以用到，例如：
求选修C1课程号的学生学号；求没有选修C2课程号的学生学号；求选修C1课程而没有选修C2课程的学生姓名。
2、针对查询任务4“求没有选修C2课程号的学生学号”，SQL语句“SELECT学号FROM选课WHERE课程号<>‘C2’”可以完成该查询任务吗？
为什么？
答：
不可以，<>符号不合法，必须使用逻辑and的方法代替<>。
3、试用GROUPBY（分组条件）字句后，语句中的统计函数的运行结果有什么不同？
答：
用GROUPBY字句后，列值相等都集中以一个元组的形式出现。
运行结果会根据GROUPBY后面接的列来分开统计，WHERE一般在GROUPBY前面，HAVING一般在GROUPBY后面。
广东工业大学实验报告
自动化学院自动化专业1班成绩评定_______
学号3114000825姓名伍宏淳（合作者____号____）教师签名_______
预习情况
操作情况
考勤情况
数据处理情况
实验四题目视图、存储过程与触发器的定义及使用实验第___周星期__第_节
一、实验目的与要求
掌握SQLServerManagementStudio中视图的创建和使用，加深对视图和SQLServer图表作用的理解；掌握SQLServerManagementStudio中存储过程及触发器的创建方法及其使用方法，了解视图与存储过程的异同，约束与触发器的区别。
二、实验方案
通过SQLServerManagementStudio实现成绩管理数据库的视图、存储过程及触发器的创建及使用，完成以下任务：
1、建立自动化学院学生信息统计的视图，要求包括自动化学院学生的学号、姓名、性别和年龄；
2、创建一个带输入参数的存储过程selectByDepartmentWithInput。
该存储过程的作用是：
当给定基本表学生所属学院名称时，将返回该学院所有学生的基本信息。
3、执行selectByDepartmentWithInput存储过程，查询“自动化”学院的学生信息。
4、为选课表建立一个名为tri_NumberPassedAdded的INSERT触发器，当用户向该表中插入记录时，如果成绩大于或等于60，则令学生表中该学生的属性“已通过选课数”加1，否则显示“无成绩或成绩低于60，不需要更新学生表中的已通过课程数！
”
5、由学生、课程和选课三个表，定义一个自动化学院的学生成绩视图，其属性包括学号、姓名、课程名和成绩；
6、将学生的学号、平均成绩定义成一个视图“学生平均成绩”，并基于该视图统计学生的学号、姓名、平均成绩及已通过选课数；
三、实验结果和数据处理
1、建立自动化学院学生信息统计的视图，要求包括自动化学院学生的学号、姓名、性别和年龄；
CREATEVIEW[自动化学院学生信息统计]AS
SELECT学号,姓名,性别,year（getdate（））-year（出生日期）as年龄
from学生where所属学院='自动化';
select*from[自动化学院学生信息统计];
2、创建一个带输入参数的存储过程selectByDepartmentWithInput。
该存储过程的作用是：
当给定基本表学生所属学院名称时，将返回该学院所有学生的基本信息。
CREATEPROCEDUREselectByDepartmentWithInput
@CampusNamevarchar（20）
ASSELECT学号,姓名,性别,year（getdate（））-year（出生日期）as年龄
from学生where所属学院=@CampusName;
GO
3、执行selectByDepartmentWithInput存储过程，查询“自动化”学院的学生信息。
execselectByDepartmentWithInput'自动化';
4、为选课表建立一个名为tri_NumberPassedAdded的INSERT触发器，当用户向该表中插入记录时，如果成绩大于或等于60，则令学生表中该学生的属性“已通过选课数”加1，否则显示“无成绩或成绩低于60，不需要更新学生表中的已通过课程数！
”
CREATETRIGGERtri_Numbe
升级会员 