数据库实验三七.docx
《数据库实验三七.docx》由会员分享，可在线阅读，更多相关《数据库实验三七.docx（14页珍藏版）》请在冰点文库上搜索。
数据库实验三七
数据库实验三~七
实验三数据表的创建与管理
第一个表，学生表的创建和插入数据
CREATETABLESTUDENT
（
SNOCHAR（8）PRIMARYKEY,
SNAMECHAR（8）NOTNULL,
AGEINT,
SEXCHAR
（2）,
DEPTVARCHAR（50）
）;
INSERTINTOSTUDENT
VALUES（'','张林',18,'男','计算机系'）
INSERTINTOSTUDENT
VALUES（'','程明',18,'男','计算机系'）
INSERTINTOSTUDENT
VALUES（'','王艳',19,'女','计算机系'）
INSERTINTOSTUDENT
VALUES（'','严平平',20,'女','计算机系'）
INSERTINTOSTUDENT
VALUES（'','王洪敏',19,'女','信息管理系'）
INSERTINTOSTUDENT
VALUES（'','孙祥新',18,'男','信息管理系'）
INSERTINTOSTUDENT
VALUES（'','吕占英',19,'女','信息管理系'）
INSERTINTOSTUDENT
VALUES（'','李义',19,'男','机械工程系'）
INSERTINTOSTUDENT
VALUES（'','牟万里',18,'男','机械工程系'）
INSERTINTOSTUDENT
VALUES（'','刘丽霞',20,'女','机械工程系'）
第二个表，课程表的创建和插入数据
CREATETABLECOURSE
（
CNOCHAR（4）PRIMARYKEY,
CNAMECHAR（20）NOTNULL,
CREDITFLOAT,
PCNOVARCHAR（20）,
DESCRIBEVARCHAR（100）
）;
INSERTINTOCOURSE
VALUES（'0101','计算机基础',5,'0101','可自学'）
INSERTINTOCOURSE
VALUES（'0102','C++程序设计',4,NULL,'可自学'）
INSERTINTOCOURSE
VALUES（'0206','离散数学',4,'0102','可自学'）
INSERTINTOCOURSE
VALUES（'0208','数据结构',4,'0101','可自学'）
INSERTINTOCOURSE
VALUES（'0209','操作系统',4,'0101','可自学'）
INSERTINTOCOURSE
VALUES（'0210','微机原理',5,'0101','可自学'）
INSERTINTOCOURSE
VALUES（'0211','图形学',3,'0102','可自学'）
INSERTINTOCOURSE
VALUES（'0212','数据库原理',4,'0102','可自学'）
INSERTINTOCOURSE
VALUES（'0301','计算机网络',3,'0102','可自学'）
INSERTINTOCOURSE
VALUES（'0302','软件工程',3,'0102','可自学'）
第三个表，选课表的创建和插入数据
CREATETABLESC
（
SNOCHAR（8）,
CNOCHAR（4）,
GRADEFLOAT,
PRIMARYKEY（SNO,CNO）,
FOREIGNKEY（SNO）REFERENCESSTUDENT（SNO）,
FOREIGNKEY（CNO）REFERENCESCOURSE（CNO）
）;
INSERTINTOSC
VALUES（'','0101',68）
INSERTINTOSC
VALUES（'','0206',76）
INSERTINTOSC
VALUES（'','0101',62）
INSERTINTOSC
VALUES（'','0209',75）
INSERTINTOSC
VALUES（'','0210',77）
INSERTINTOSC
VALUES（'','0212',75）
INSERTINTOSC
VALUES（'','0301',87）
INSERTINTOSC
VALUES（'','0101',68）
INSERTINTOSC
VALUES（'','0212',88）
INSERTINTOSC
VALUES（'','0302',76）
INSERTINTOSC
VALUES（'','0101',66）
实验四简单查询和连接查询
1）简单查询实验
用Transact-SQL语句表示下列操作，在“学生选课”数据库中实现数据库查询操作：
（1）查询数学系学生的学号和姓名
SELECTSNO,SNAME
FROMSTUDENT
WHEREDEPT='数学系';
（2）查询选修了课程的学生学号
SELECTDISTINCTSNO
FROMSC;
（3）查询选修课程号为0101的学生学号成绩，并要求对查询结果按成绩降序排列。
如果成绩相同则按学号升序排列
SELECTSNO,GRADE
FROMSC
WHERECNO='0101'
ORDERBYGRADE,SNOASC;
（4）查询选修课程号为0101的成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.8输出
SELECTSNO,GRADE*0.8ASNEW_GRADE
FROMSC
WHERECNO='0101'AND（GRADEBETWEEN80AND90）
或者
SELECTSNO,GRADE*0.8ASNEW_GRADE
FROMSC
WHERECNO='0101'ANDGRADE>80ANDGRADE<90
（5）查询数学系或计算机系姓张的学生的信息
SELECT*
FROMSTUDENT
WHEREDEPTIN（'数学系','计算机系'）ANDSNAMELIKE'张%'
（6）查询缺少了成绩的学生的学号和课程号
SELECTSNO,CNO
FROMSC
WHEREGRADEISNULL
2）连接查询实验
用Transact-SQL语句表示下列操作，在“学生选课”数据库中实现数据库查询操作：
（1）查询每个学生的情况以及他（她）所选修的课程
SELECTS.SNO,S.SNAME,S.AGE,S.SEX,S.DEPT,SC.CNO,C.CNAME
FROMSTUDENTS,SC,COURSEC
WHERES.SNO=SC.SNOANDSC.CNO=C.CNO
（2）查询学生的学号、姓名、选修的课程名及成绩
SELECTS.SNO,S.SNAME,C.CNAME,SC.GRADE
FROMSTUDENTS,SC,COURSEC
WHERES.SNO=SC.SNOANDSC.CNO=C.CNO
（3）查询选修离散数学课程且成绩为90分以上的学生学号、姓名及成绩
SELECTS.SNO,S.SNAME,SC.GRADE
FROMSTUDENTS,SC,COURSEC
WHERES.SNO=SC.SNOANDSC.CNO=C.CNOANDC.CNAME='离散数学'ANDGRADE>90
（4）查询每一门课的间接先行课
SELECTC1.CNO,C1.CNAME,C2.PCNOASPPCNO
FROMCOURSEC1,COURSEC2
WHEREC1.PCNO=C2.CNO
实验五嵌套查询
用Transact-SQL语句表示下列操作，在学生选课库中实现其数据嵌套查询操作：
（1）查询选修了离散数学的学生学号和姓名
SELECTSNO,SNAME
FROMSTUDENT
WHERESNOIN
（SELECTSNO
FROMSC
WHERECNO=（SELECTCNO
FROMCOURSE
WHERECNAME='离散数学'）
）
（2）查询0101课程的成绩高于张林的学生学号和成绩
SELECTSNO,GRADE
FROMSC
WHERECNO='0101'ANDGRADE>
（SELECTGRADE
FROMSC
WHERECNO='0101'ANDSNO=（SELECTSNO
FROMSTUDENT
WHERESNAME='张林'）
）
（3）查询其他系中年龄小于计算机系年龄最大者的学生
SELECT*
FROMSTUDENT
WHEREDEPT<>'计算机系'ANDAGEFROMSTUDENT
WHEREDEPT='计算机系'）
或
SELECT*
FROMSTUDENT
WHEREDEPT<>'计算机系'ANDAGE<（SELECTMAX（AGE）
FROMSTUDENT
WHEREDEPT='计算机系'）
（4）查询其他系中比计算机系学生年龄都小的学生
SELECT*
FROMSTUDENT
WHEREDEPT<>'计算机系'ANDAGEFROMSTUDENT
WHEREDEPT='计算机系'）
或
SELECT*
FROMSTUDENT
WHEREDEPT<>'计算机系'ANDAGE<（SELECTMIN（AGE）
FROMSTUDENT
WHEREDEPT='计算机系'）
（5）查询同王洪敏数据库原理课程分数相同的学生的学号
SELECTSNO
FROMSC
WHERESNO<>（SELECTSNO
FROMSTUDENT
WHERESNAME='王洪敏'）
AND
CNOIN（SELECTCNO
FROMCOURSE
WHERECNAME='数据库原理'）
AND
GRADE=（SELECTGRADE
FROMSTUDENT,SC,COURSE
WHERESTUDENT.SNO=SC.SNO
ANDSC.CNO=COURSE.CNO
ANDSNAME='王洪敏'
ANDCNAME='数据库原理'）
（6）查询选修了0206课程的学生姓名
SELECTSNAME
FROMSTUDENT
WHERESNOIN（SELECTSNO
FROMSC
WHERECNO='0206'）
或
SELECTSNAME
FROMSTUDENTS,SC
WHERES.SNO=SC.SNOANDCNOIN（SELECTCNO
FROMSC
WHERECNO='0206'）
（7）查询没有选修0206课程的学生姓名
SELECTSNAME
FROMSTUDENT
WHERESNONOTIN（SELECTSNO
FROMSC
WHERECNO='0206'）
（8）查询选修了全部课程的学生的姓名
SELECTSNAME
FROMSTUDENT
WHERENOTEXISTS
（SELECT*
FROMCOURSE
WHERENOTEXISTS
（SELECT*
FROMSC
WHERESNO=STUDENT.SNOANDCNO=COURSE.CNO）
）
逻辑描述：
没有一门课是学生没有选的
例子：
查询选修了两门课的学生
SELECTDISTINCTA.SNO
FROMSCA,SCB
WHEREA.SNO=B.SNOANDA.CNO<>B.CNO
实验六组合查询和统计查询
在学生选课数据库中实现查询操作：
（1）查找选修“计算机基础”课程的学生成绩比此课程平均成绩大的学生学号，成绩。
SELECTSC.SNO,GRADE
FROMSC,COURSE
WHERESC.CNO=COURSE.CNO
ANDCOURSE.CNAME='计算机基础'
ANDGRADE>（SELECTAVG（GRADE）
FROMSC,COURSE
WHERESC.CNO=COURSE.CNO
ANDCOURSE.CNAME='计算机基础'）
（2）查询选修计算机基础课程的学生的平均成绩
SELECTAVG（GRADE）
FROMSC,COURSE
WHERESC.CNO=COURSE.CNO
ANDCOURSE.CNAME='计算机基础'
（3）查询年龄大于女同学平均年龄的男同学姓名和年龄
SELECTSNAME,AGE
FROMSTUDENT
WHERESEX='男'
ANDAGE>（SELECTAVG（AGE）
FROMSTUDENT
WHERESEX='女'）
（4）列出各系学生总人数，并按人数进行降序排列
SELECTDEPT,COUNT（*）AS'人数'
FROMSTUDENT
GROUPBYDEPT
ORDERBY2DESC
（5）统计各系各门课程的平均成绩
SELECTSC.CNO,CNAME,AVG（GRADE）AS'平均成绩'
FROMSC,COURSE
WHERESC.CNO=COURSE.CNO
GROUPBYSC.CNO,CNAME
（6）查询选修计算机基础和离散数学的学生学号和平均成绩
SELECTSNO,AVG（GRADE）
FROMSC
WHERESNOIN
（SELECTA.SNO
FROMSCA,SCB
WHEREA.SNO=B.SNO
ANDA.CNO=（SELECTCNO
FROMCOURSE
WHERECNAME='离散数学'）
ANDB.CNO=（SELECTCNO
FROMCOURSE
WHERECNAME='计算机基础'）
）
GROUPBYSNO
实验七视图、索引与数据库关系图
创建视图
createviewview_stu_grade
asselectstudent.sno,sname,cname,grade
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
修改视图
alterviewview_stu_grade
asselectstudent.sno,sname,cname,grade
fromstudent,sc,course
wherestudent.sno=sc.snoando=oandgrade>80
删除视图
dropviewview_stu_grade
创建索引
use学生选课
createindexIX_deptonstudent（dept）
createindexIX_ageonstudent（agedesc）
删除索引（部分略）
use学生选课
dropindexIX_dept
（实际情况
dropindexIX_deptonstudent
）
升级会员 