sql数据库系统原理上机实验综合版Word下载.docx
《sql数据库系统原理上机实验综合版Word下载.docx》由会员分享，可在线阅读，更多相关《sql数据库系统原理上机实验综合版Word下载.docx（19页珍藏版）》请在冰豆网上搜索。
字符（文本）型
8
主键
sname
4
不为空
ssex
1
sage
整数（数值）型
sdept
字符型
10
表2课程信息表：
course
cno
2
cname
30
credit
cpno
3
表3学生选课表：
sc
主属性，外键
grade
是
提示：
在不使用SQL语句创建表的情况下，可通过ACCESS中的关系（菜单—工具—关系）和SQLSERVER2005中的数据库关系图（数据库节点展开—数据库关系图）实现外键的创建。
外键字段和参照字段之间的数据类型以及长度要保持一致。
2、输入表中的记录
分别在student表、course表和sc表中输入如下表中的记录：
95001
李勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
18
MA
95004
张立
95005
刘云
pcno
数据库
5
数学
6
信息系统
操作系统
数据结构
7
数据处理
PASCAL语言
92
85
88
90
80
58
观察输入时有无提示错误，如果有如何修改，体会参照完整性的作用，弄清楚先输入那些表中记录，为什么？
3、对表中的记录进行浏览、修改、删除操作。
4、利用“分离数据库”和“附加数据库”操作对SQLServer中创建的数据库做备份和还原操作。
5、在SQLSERVER2005中新建查询，建立到服务器的连接
6、用SQL语言CREATETABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束，
具体约束如下：
默认值为’男’
取值为’男’或’女’
否
取值在0-100之间
7、向创建的表中输入数据，测试所创建的完整性约束是否起作用
8、用SQL语言ALTER语句修改表结构;
1）STUDENT表中增加一个字段入学时间scome，
2）删除STUDENT表中sdept字段；
3）删除创建的SC表中CNO字段和COURSE表CNO字段之间的外键约束；
4）重建3）中删除的约束
9、重新定义一个简单表，然后用SQL语言DROP语句删除该表结构;
10、用SQL语言CREATEINDEX语句定义表STUDENT的SNAME字段的降序唯一索引;
11、用SQL语言DROP语句删除索引;
实验二SQL简单查询
目的：
掌握简单数据查询操作。
内容：
使用各种查询条件完成指定的查询操作
步骤：
1）创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。
）;
（可以使用实验一中已经建立的表和数据）
2）对各表中的数据进行不同条件的查询；
包括的运算：
投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列表范围、算术运算符、内部函数、排序、分组、分组函数使用
（1）查询全体学生的学号和姓名
（2）查询全体学生的详细记录
（3）查询软件学院的学生姓名、年龄、系别
（4）查询所有选修过课程的学生学号（不重复）
（5）查询考试不及格的学生学号（不重复）
（6）查询不是软件学院的学生性别、年龄、系别
（7）查询年龄18－20岁的学生学号、姓名、系别、年龄；
（8）查询姓刘的学生情况
（9）查询姓刘或姓李的学生情况
（10）查询姓刘且名字为两个字的学生情况
（11）查询1983年以后出生的学生姓名。
（12）利用内部函数year（）查找软件学院学生的出生年份
（13）利用字符转换函数实现字符联接。
Selectsname+‘年龄为’+cast（sageaschar
（2））+’岁’
Fromstudent
（14）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
（15）查询学生总人数。
（16）查询选修了课程的学生人数。
（17）查询选修了7号课程的学生总人数和平均成绩
（18）查询选修6号课程学生的最好成绩
（19）查询每个系的系名及学生人数。
（20）查找每门课的选修人数及平均成绩
（21）查找没有先修课的课程情况
要求：
1、将上述任务中完整的SQL语句调试并使之运行正确；
2、写出实验报告；
实验三SQL复杂查询
掌握复杂数据查询操作。
掌握各种连接查询、嵌套查询的使用
1）实验一中的数据为基础
2）对各表中的数据进行不同条件的连接查询和嵌套查询；
●查询每个学生及其选课情况；
●查询每门课的间接先修课
●将STUDENT,SC进行右连接
●查询既选修了2号课程又选修了3号课程的学生姓名、学号；
●查询和刘晨同一年龄的学生
●选修了课程名为“数据库”的学生姓名和年龄
●查询其他系比IS系任一学生年龄小的学生名单
●查询其他系中比IS系所有学生年龄都小的学生名单
●查询选修了全部课程的学生姓名
●查询计算机系学生及其性别是男的学生
●查询选修课程1的学生集合和选修2号课程学生集合的差集
●查询李丽同学不学的课程的课程号
●查询选修了3号课程的学生平均年龄
●求每门课程学生的平均成绩
●统计每门课程的学生选修人数（超过3人的才统计）。
要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列
●查询学号比刘晨大，而年龄比他小的学生姓名。
●求年龄大于所有女同学年龄的男同学姓名和年龄
实验四SQL数据更新
掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。
1）应用INSERT,UPDATE,DELETE语句进行更新操作；
a）插入如下学生记录（学号：
95030，姓名：
李莉，年龄：
18）
b）插入如下选课记录（95030，1）
c）计算机系学生年龄改成20
d）把数学系所有学生成绩改成0
e）把低于总平均成绩的女同学成绩提高5分
f）修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高
4％（两个语句实现，注意顺序）
g）删除95030学生信息
h）删除SC表中无成绩的记录
i）删除张娜的选课记录
j）删除不及格的学生选课记录
k）删除数学系所有学生选课记录
l）删除所有未被选修的课程
m）查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU（SNO,SNAME,SSEX）中
n）建立一个sdeptgrade表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade
2）熟练掌握INSERT,UPDATE,DELETE语句并能综合应用；
附录（总版）：
语句清单
--sql查询实验一代码
createtablestudent1
（snochar（8）primarykey,
snamechar（4）notnull,-------创建表student1
ssexchar
（2）default'
男'
check（ssexin（'
'
女'
））,
sageintdefault（'
是'
）,
sdeptchar（10）default（'
否'
）
createtablecourse1
（cnochar
（2）primarykey,
cnamechar（30）,------创建表course1
creditint,
cpnochar（3）,
createtablesc1
（snochar（8）foreignkeyreferencesstudent1（sno）,创建表sc1
cnochar
（2）foreignkeyreferencescourse1（cno）,
gradeintdefault（'
）check（gradebetween0and100）,
----------------------第题
altertablestudent1addscomeint
altertablestudent1dropcolumnsdept
selectname
fromsys.foreign_key_columnsfjoinsys.objectsoonf.constraint_object_id=o.object_id
wheref.parent_object_id=object_id（'
sc1'
）--------查找出约束名，然后再用drop语句删除约束
altertablesc1dropconstraintFK__sc1__cno__740F363E--------在括号中补充用上面语句查找出的约束名
altertablesc1addconstraint[pk_sc1]primarykey（cno）
altertablesc1addconstraint[fk_sc1_course1]foreignkey（cno）referencescourse1（cno）
altertablesc1dropconstraint[fk_sc1_course1]
createuniqueindexstudent1_indexonstudent1（snamedesc）
dropindexstudent1_indexonstudent1
createtableteacher
（tnochar（4）primarykey,
tnamechar（8）notnull,
titlechar（5）,
droptableteacher
--sql查询实验二的代码
SELECTsno,sname--1
fromstudent
select*------------2
selectsname,sage,sdept-----3
wherestudent.sdept='
IS'
selectdistinctstudent.sno-----4
fromstudent,sc
wheresc.sno=student.sno
selectdistinctstudent.sno----5
wheresc.sno=student.snoandgrade<
60
selectssex,sage,sdept-----------6
wheresnonotin
（selectsno
wheresdept='
/*这是exists的用法
selectssex,sage,sdept
fromstudentasy
wherenotexists（select*
fromstudent
whereY.sdept='
CS'
*/
selectsno,sname,sage,sdept-------7
wheresagebetween18and20;
select*---------------8
wheresnamelike'
刘%'
select*-----------------9
[刘李]%'
selectsname----------------10
刘__'
--中文占用两个字符，所以这里用两个_
selectsname-------------------11
where2013-sage>
1983
--selectyear（sage）这是取单个人的出生年份的用法，调用year函数
selectyear（getdate（））-s.sage+1
fromstudentass-------12
Selectsname+'
年龄为'
+cast（sageaschar
（2））+'
岁'
Fromstudent---------13
select*
fromstudent---------------------14
orderbysdept,sagedesc
selectCOUNT（sno）----------------15
selectCOUNT（distinctsno）
fromsc--------------16
/*
SELECTCOUNT（*）
FROMstudent----------16题解法二
WHEREEXISTS（SELECT*FROMSCWHEREsc.sno=student.sno）
selectCOUNT（student.sno）,avg（sc.grade）
fromstudent,sc------------17
wherestudent.sno=sc.snoandcno='
7'
selectmax（grade）
fromsc-------------------18
wherecno='
6'
selectsdept,COUNT（sno）
fromstudent-------------------19
groupbysdept
selectCOUNT（student.sno）,AVG（grade）
fromstudent,sc----------20
wherestudent.sno=sc.sno
groupbycno
/*-----------------20第二种解法
selectcname,COUNT（student.sno）,AVG（grade）
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
groupbyo,cname
select*------为什么这里查询不到结果
fromcourse-------------21
wherecpnoisnull
--sql查询实验三代码
--------1
selectstudent.*,o,sc.grade----左外连接（显性连接）
fromstudentleftjoinsconstudent.sno=sc.sno
/*
selectstudent.*,o,sc.grade
fromstudentinnerjoinsc----内连接法（显性连接）
onstudent.sno=sc.sno
selectstudent.*,o,sc.grade----------隐性连接
selectame,first.cpno,o
fromcoursefirst,coursesecond----------2
wherefirst.cpno=o
selectstudent.*,sc.*---------------3
fromstudentrightjoinsconstudent.sno=sc.sno
selectstudent.sno,sname
fromstudent,sc,（selectstudent.sno
fromstudent,sc-----4导出表的使用
wheresc.sno=student.snoandcno='
2'
groupbystudent.sno）asresult（sno）
3'
groupbystudent.sno,student.sname
fromstudent,scasx--------4法二
wherestudent.sno=x.snoandcno='
andexists（selectstudent.sno
fromstudent,scasy
andexists（select*
fromstudent,scasz
wherez.sno=y.snoandz.sno=x.sno））
fromstudent,scasx,scasy------4法三
wherestudent.sno=x.snoandx.sno=y.snoando='
ando='
fromstudentasx,studenty--------5
wherex.sname='
刘晨'
andx.sage=y.sage
selectsname,sage
fromstudent,sc,course-----------6
wherestudent.sno=sc.snoando=oandame='
数据库'
selectsname
wheresage<
any（selectsage
fromstudent----------7
wheresdept='
is'
）andsdept<
>
'
all（selectsage------8
fromstudent
andsdept<
fromstudent------------9
wheresnoin（selectsno
fromsc
groupbysno
havingCOUNT（*）=7）
select*--------------10
andssex='
selectdistinctstudent.sname-------------11
whereo='
1'
except（selectstudent.sname
fromstudent,sc
whereo='
selectcno
fromsc---------------------12
wheresc.snonotin（selectsno
fromst
升级会员 