太原理工大学数据库实验报告概要.docx
《太原理工大学数据库实验报告概要.docx》由会员分享，可在线阅读，更多相关《太原理工大学数据库实验报告概要.docx（52页珍藏版）》请在冰豆网上搜索。
太原理工大学数据库实验报告概要
本科实验报告
课程名称：
数据库系统概论
实验项目：
交互式SQL、数据完整性、用户鉴别与
数据控制
实验地点：
致远楼B503
专业班级：
软件1229班学号：
2012005829
学生姓名：
田亚鹏
指导教师：
李雪梅
2014年3月18日
一、实验目的和要求
熟悉通过SQL对数据库进行操作。
二、实验内容和原理
1．在RDBMS中建立一个学生-课程数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。
2．根据以下要求认真进行实验，记录所有的实验用例及执行结果。
数据定义：
基本表的创建、修改及删除；索引的创建和删除。
数据操作：
完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）；完成各类更新操作（插入数据，修改数据，删除数据）。
视图的操作：
视图的定义（创建和删除），查询，更新（注意更新的条件）。
3、主要仪器设备
操作系统：
Windows7。
数据库管理系统：
SQLServer2008。
四、操作方法与实验步骤实验数据记录实验结果
（一）数据定义：
一．基本表的操作
1．建立基本表
1）创建学生表Student,由以下属性组成：
学号Sno（char型，长度为9，
主码），姓名Sname（char型，长度为20，唯一），性别Ssex（char型，
长度为2），年龄（smallint）,所在系（char型，长度为20）。
createtableStudent
（Snochar（9）primarykey,
Snamechar（20）unique,
Ssexchar
（2）,
Sagesmallint,
Sdeptchar（20））;
2）创建课程表Course,由以下属性组成：
课程号Cno（char型，主码，
长度为4），课程名Cname（char型，长度为40），先行课Cpno（char
型，长度为4,外码），学分Ccredit（smallint）。
createtableCourse
（Cnochar（4）primarykey,
Cnamechar（40）,
Cpnochar（4）,
Ccreditsmallint）;
若设置Cpno外码，插入数据时会提示违反外码约束。
3）创建学生选课表SC，由以下属性组成：
学号Sno（char型，长度为9），
课程号Cno（char型，长度为4），成绩Grade（smallint）,其中Sno和
Cno构成主码。
createtablesc
（Snochar（9）,
Cnochar（4）,
Gradesmallint,
primarykey（Sno,Cno）,
foreignkey（Sno）referencesstudent（Sno）,
foreignkey（Cno）referencescourse（Cno））；
2．修改基本表：
1）向Student表增加“入学时间列”，其数据类型为日期型。
altertableStudentaddS_entrancedate;
2）将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。
altertableStudentaltercolumnSageint;
3）增加课程名称必须取唯一值的约束条件。
altertableCourseaddunique（Cname）;
注意：
修改表结构后，再次查看表，注意观察变化。
3．删除基本表：
1）在所有的操作结束后删除Student表。
droptableStudent;
2）在所有的操作结束后删除Course表。
droptableCourse;
3）在所有的操作结束后删除SC表。
droptableSC;
思考：
删除表时，不同的删除顺序会有不同结果，为什么？
？
注意错误
提示。
二．索引操作
1．建立索引
1）为学生—课程数据库中的Student，Course，SC3个表建立索引。
其
中Student表按学号升序建唯一索引，Course表按课程号升序建唯一
索引，SC表按学号升序和课程号降序建唯一索引。
createuniqueindexStusnoonStudent（Sno）;
createuniqueindexCoucnoonCourse（Cno）;
createuniqueindexSCnoonSC（SnoASC,CnoDESC）;
2．删除索引
1）删除Student表的Stusname索引。
dropindexstudent.Stusname;
已建立
已删除
（二）数据操作
一．更新操作
1,插入数据
1）在Student表中插入下列数据：
200215121，李勇，男，20，CS
200215122，刘晨，女，19，CS
200215123，王敏。
女，18，MA
200215125，张立，男，19，IS
insertintostudent（Sno,Sname,Ssex,Sage,Sdept）values（'200215121','李
勇','男',20,'CS'）;
insertintostudent（Sno,Sname,Ssex,Sage,Sdept）values（'200215122','刘
晨','女',19,'CS'）;
insertintostudent（Sno,Sname,Ssex,Sage,Sdept）values（'200215123','王
敏','女',18,'MA'）;
insertintostudent（Sno,Sname,Ssex,Sage,Sdept）values（'200215125','张
立','男',19,'IS'）
2）在Course表中插入以下数据：
1,数据库,5,4
2,数学,null，2
6,数据处理,null，2
4,操作系统,6,3
7,PASCAL语言,6,4
5,数据结构,7,4
1,数据库,5,4
3,信息系统,1,4
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'1','数据库','5',4）;
insertintocourse（Cno,Cname,Ccredit）values（'2','数学',2）;
insertintocourse（Cno,Cname,Ccredit）values（'6','数据处理',2）;
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'4','操作系统','6',3）;
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'7','PASCAL语言','6',4）;
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'5','数据结构','7',4）;
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'1','数据库','5',4）;
insertintocourse（Cno,Cname,Cpno,Ccredit）values（'3','信息系统','1',4）;
3）在SC表中插入以下数据：
200215121,1,92
200215121,2,85
200215121,3,88
200215122,2,90
200215122,3,80
insertintosc（Sno,Cno,Grade）values（'200215121','1',92）;
insertintosc（Sno,Cno,Grade）values（'200215121','2',85;
insertintosc（Sno,Cno,Grade）values（'200215121','3',88）;
insertintosc（Sno,Cno,Grade）values（'200215122','2',90）;
insertintosc（Sno,Cno,Grade）values（'200215122','3',80）;
4）将一个新学生元祖（学号：
200215128；姓名：
陈冬；性别：
男；所在
系：
IS；年龄：
18岁）插入到Student表中。
insertintostudent（Sno,Sname,Ssex,Sdept,Sage）values
（'200215128','陈冬','男','IS',18）;（已做）
5）将学生张成民的信息插入到Student表中。
insertintostudentvalues（'200215126','张成民','男',18,'CS'）;（已做）
6）插入一条选课记录：
（‘200215128’，‘1’）。
insertintosc（Sno,Cno）values（'200215128','1'）;（已做）
7）对每一个系，求学生的平均年龄，并把结果存入数据库。
createtableDept_age（Sdeptchar（15）,
Avg_agesmallint）;
insertintoDept_age（Sdept,Avg_age）selectSdept,avg（Sage）fromstudent
groupbySdept;
2．修改数据
1）将学生200215121的年龄改为22岁。
updatestudentsetSage=22whereSno='200215121';
2）将所有学生的年龄增加一岁。
updatestudentsetSage=Sage+1;
3）将计算机科学系全体学生的成绩置零。
updatescsetGrade=0where'CS'=（selectSdeptfromstudent
wherestudent.Sno=sc.Sno）;
3．删除数据
1）删除学号为200215128的学生记录。
deletefromstudentwhereSno='200215128';
2）删除所有学生的选课记录。
deletefromsc;
3）删除计算机科学系所有学生的选课记录。
deletefromscwhere'CS'=（selectSdeptfromstudentwhere
student.Sno=SC.Sno）;
2．查询操作
1．单表查询
2）查询全体学生的姓名、学号、所在系。
selectsno,sname,sdept
fromStudent;
5）查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示
所有系名。
selectsname,2013-sagebirth,lower（sdept）sdept
fromStudent;
10）查询年龄在20-23岁（包括20岁和23岁）之间的学生的姓名、系别
和年龄。
selectsname,sdept,sage
fromStudent
wheresagebetween20and23
12）查询计算机科学系（CS）、数学系（MA）、和信息系（IS）学生的姓名和
性别。
selectsname,ssex
fromStudent
whereSdeptin（'cs','ma','is'）
15）查询所有姓刘的学生的姓名、学号和性别。
selectsname,Sno,ssex
fromStudent
wheresnamelike'刘%'
19）查询DB_Design课程的课程号和学分。
selectCno,CcreditfromcoursewhereCnamelike'DB\_Design'
escape'\';
23）查询计算机科学系年龄在20岁以下的学生姓名。
selectsname
fromstudent
wheresdept='cs'andsage<20
28）计算1号课程的学生平均成绩。
selectAVG（grade）no1
fromsc
whereCno='1'
31）求各个课程号及相应的选课人数。
selectCno,COUNT（sno）number
fromsc
groupbyCno
2．连接查询
1）查询每个学生及其选修课程的情况。
selectStudent.*,sc.*
fromsc,Student
wheresc.Sno=Student.Sno
2）对上个题用自然连接完成。
selectstudent.Sno,Sname,Sage,Ssex,Sdept,Cno,Grade
fromsc,Student
wheresc.Sno=Student.Sno
3）
查询每一门课的间接先修课（即先修课的先修课）。
selecto,second.cpno
fromCoursefirst,Coursesecond
wherefirst.Cpno=second.Cno
4）查询每个学生及其选修课程的情况，用外连接来完成
selectstudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Gradefrom
studentleftouterjoinscon（student.Sno=sc.Sno）;
5）查询选修2号课程且成绩在90分以上的所有学生。
selectstudent.Sno,Snamefromstudent,scwhere
student.Sno=sc.Snoandsc.Cno='2'andsc.Grade>90;
6）查询每个学生的学号、姓名、选修的课程名及成绩。
selectstudent.Sno,Sname,Cname,Grade
fromstudent,sc,course
wherestudent.Sno=sc.Snoandsc.Cno=course.Cno;
3．嵌套查询
1）查询与“刘晨”在同一个系学习的学生。
selectSno,Sname,Sdept
fromStudent
whereSdeptin（
selectSdept
fromStudent
whereSname='刘晨'）;
2）查询选修了课程名为“信息系统”的学生学号和姓名。
selectSno,Sname
fromstudent
whereSnoin
（selectSno
fromsc
whereCnoin
（selectCno
fromcourse
whereCname='信息系统'））;
3）找出每个学生超过他选修课程平均成绩的课程号。
selectcno
fromscx
wheregrade>
（selectAVG（Grade）
fromscy
wherex.sno=y.sno
groupbySno）;
4）查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。
selectsname,Sage
fromStudent
whereSage<=any
（selectsage
fromstudent
whereSdept='cs'）
andSdept<>'cs';
5）查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。
selectsname,Sage
fromStudent
whereSage<=all
（selectsage
fromstudent
whereSdept='cs'）
andSdept<>'cs';
6）查询选修了1号课程的学生姓名。
selectSname
fromstudent
whereexists
（select*
fromsc
whereSno=student.SnoandCno='1'）;
7）
查询没有选修1号课程的学生姓名。
selectSname
fromstudent
wherenotexists
（select*
fromsc
whereSno=student.SnoandCno='1'）;
8）查询选修了全部课程的学生姓名。
selectSname
fromstudent
wherenotexists
（select*
fromCourse
wherenotexists
（select*
fromsc
whereSno=student.SnoandCno=course.Cno））;
9）查询至少选修了学生200215121选修的全部课程的学生号码。
selectdistinctSno
fromscscx
wherenotexists
（select*
fromscscy
wherescy.Sno='200215122'andnotexists
（select*
fromscscz
wherescz.Sno=scx.Snoandscz.Cno=scx.Cno））;
4．集合查询
1）查询计算机科学系的学生及年龄不大于19岁的学生。
select*
fromstudent
whereSdept='CS'
union
select*
fromstudent
whereSage<=19;
2）查询选修了课程1或课程2的学生。
selectSno
fromsc
whereCno='1'
union
selectSno
fromsc
whereCno='2';
3）查询计算机科学系的学生与年龄不大于19岁的学生的交集。
select*
fromstudent
whereSdept='CS'
intersect
select*
fromstudent
whereSage<=19;
4）查询既选修了课程1又选修了课程2的学生。
selectSno
fromsc
whereCno='1'
intersect
selectSno
fromsc
whereCno='2';
5）查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select*
fromstudent
whereSdept='CS'
except
select*
fromstudent
whereSage<=19
三、视图操作
1．建立视图
1）建立信息系学生的视图。
createviewIS_Student
asselectSno,Sname,Sage
fromstudent
whereSdept='IS';
2）建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视
图只有信息系的学生。
createviewIS_Student
asselectSno,Sname,Sage
fromstudent
whereSdept='IS'withcheckoption;
3）建立信息系选修了1号课程的学生的视图。
createviewIS_S1（Sno,Sname,Grade）
asselectstudent.Sno,Sname,Grade
fromstudent,sc
whereSdept='IS'andstudent.Sno=sc.Snoandsc.Cno='1';
4）
建立信息系选修了1号课程且成绩在90分以上的学生的视图。
createviewIS_S2（Sno,Sname,Grade）
asselectSno,Sname,Grade
fromIS_S1
whereGrade>=90;
5）定义一个反应学生出生年份的视图。
createviewBT_S（Sno,Sname,Sbirth）
asselectSno,Sname,2004-Sage
fromstudent;
6）
将学生的学号及他的平均成绩定义为一个视图。
createviewS_G（Sno,Gavg）
asselectSno,avg（Grade）
fromsc
groupbySno;
7）将Student表中所有女生记录定义为一个视图。
createviewF_Student（F_sno,name,sex,age,dept）
asselect*
fromstudent
whereSsex='女';
2．删除视图：
1）删除视图BT_S:
3．查询视图：
1）在信息系学生的视图中找出年龄小于20岁的学生。
selectSno,Sage
fromIS_Student
whereSage<=20;
2）
查询选修了1号课程的信息系学生。
selectIS_Student.Sno,Sname
fromIS_Student,sc
whereIS_Student.Sno=sc.Snoandsc.Cno='1';
3）
在S_G视图中查询平均成绩在80分以上的学生学号和平均成绩。
select*
fromS_G
whereGavg>=80;
4．更新视图：
1）将信息系学生视图IS_Student中学号为200215125的学生姓名改为
“刘辰”。
updateIS_Student
setSname='刘辰'
whereSno='200215125';
原视图
2）向信息系学生视图IS_Student中插入一个新的学生记录，其中学号
为2
升级会员 