1、太原理工大学数据库实验报告 本科实验报告课程名称： 数据库系统原理 实验项目：交互式SQL、数据完整性、用户鉴别与 数据控制实验地点： 逸夫楼303 专业班级：软件工程* 学号：* 学生姓名： * * * 2013年 5 月18 日一、实验目的和要求 熟悉通过 SQL 对数据库进行操作。二、实验内容和原理 1在 RDBMS 中建立一个学生-课程数据库，进行实验所要求的各种操作，所有的 SQL 操作均在此建立的新库里进行。 2根据以下要求认真进行实验，记录所有的实验用例及执行结果。 数据定义：基本表的创建、修改及删除；索引的创建和删除。 数据操作：完成各类查询操作(单表查询，连接查询，嵌套查询，
2、集合查询)；完成各类更新操作(插入数据，修改数据，删除数据)。 视图的操作： 视图的定义(创建和删除)，查询，更新(注意更新的条件)。 三、主要仪器设备 操作系统：Windows 7。 数据库管理系统：SQL Server2008。四、操作方法与实验步骤 实验数据记录 实验结果(一)数据定义： 一基本表的操作 1建立基本表 1)创建学生表 Student,由以下属性组成：学号 Sno(char 型，长度为 9，主码)，姓名 Sname(char 型，长度为 20，唯一)，性别 Ssex(char 型，长度为 2)，年龄(smallint),所在系(char 型，长度为 20)。 create
3、table Student (Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20); 2）创建课程表 Course,由以下属性组成：课程号 Cno(char 型，主码，长度为 4)，课程名 Cname(char 型，长度为 40)，先行课 Cpno(char型，长度为 4,外码)，学分 Ccredit(smallint)。 create table Course (Cno char(4) primary key, Cname char(40), Cpno char(
4、4), Ccredit smallint); 若设置 Cpno 外码，插入数据时会提示违反外码约束。 3)创建学生选课表 SC， 由以下属性组成： 学号 Sno(char 型， 长度为 9)，课程号 Cno(char 型，长度为 4)，成绩 Grade(smallint),其中 Sno 和Cno 构成主码。 create table sc (Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key (Sno) references student(Sno), foreign key (Cno) ref
5、erences course(Cno)； 2修改基本表： 1)向 Student 表增加“入学时间列”，其数据类型为日期型。 alter table Student add S_entrance date; 2)将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。 alter table Student alter column Sage int; 3)增加课程名称必须取唯一值的约束条件。 alter table Course add unique(Cname); 注意：修改表结构后，再次查看表，注意观察变化。 3删除基本表： 1)在所有的操作结束后删除 Student 表。 dr
6、op table Student; 2)在所有的操作结束后删除 Course 表。 drop table Course; 3)在所有的操作结束后删除 SC 表。 drop table SC; 思考：删除表时，不同的删除顺序会有不同结果，为什么？注意错误提示。 二索引操作 1建立索引 1)为学生课程数据库中的 Student，Course，SC 3 个表建立索引。其中 Student 表按学号升序建唯一索引， Course 表按课程号升序建唯一索引，SC 表按学号升序和课程号降序建唯一索引。 create unique index Stusno on Student(Sno); create u
7、nique index Coucno on Course(Cno); create unique index SCno on SC(Sno ASC,Cno DESC); 2删除索引 1)删除 Student 表的 Stusname 索引。 drop index student.Stusname; (二)数据操作 一更新操作 1,插入数据 1)在 Student 表中插入下列数据： 200215121，李勇，男，20，CS 200215122，刘晨，女，19，CS 200215123，王敏。女，18，MA 200215125，张立，男，19，IS insert into student(Sno,
8、Sname,Ssex,Sage,Sdept)values(200215121,李勇,男,20,CS); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(200215122,刘晨,女,19,CS); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(200215123,王敏,女,18,MA); insert into student(Sno,Sname,Ssex,Sage,Sdept)values(200215125,张立,男,19,IS) 2)在 Course 表中插入以下数据：
9、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 insert into course(Cno,Cname,Cpno,Ccredit)values(1,数据库,5,4); insert into course(Cno,Cname,Ccredit)values(2,数学,2); insert into course(Cno,Cname,Ccredit)values(6,数据处理,2); insert into course(Cno,Cname,Cpno,Cc
10、redit)values(4,操作系统,6,3); insert into course(Cno,Cname,Cpno,Ccredit)values(7,PASCAL 语言,6,4); insert into course(Cno,Cname,Cpno,Ccredit)values(5,数据结构,7,4); insert into course(Cno,Cname,Cpno,Ccredit)values(1,数据库,5,4); insert into course(Cno,Cname,Cpno,Ccredit)values(3,信息系统,1,4); 3) 在 SC 表中插入以下数据： 2002
11、15121,1,92 200215121,2,85 200215121,3,88 200215122,2,90 200215122,3,80 insert into sc (Sno,Cno,Grade) values (200215121,1,92); insert into sc (Sno,Cno,Grade) values (200215121,2,85; insert into sc (Sno,Cno,Grade) values (200215121,3,88); insert into sc (Sno,Cno,Grade) values (200215122,2,90); insert
12、 into sc (Sno,Cno,Grade) values (200215122,3,80); 4)将一个新学生元祖(学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18 岁)插入到 Student 表中。 insert into student (Sno,Sname,Ssex,Sdept,Sage) values (200215128,陈冬,男,IS,18); （已做）5)将学生张成民的信息插入到 Student 表中。 insert into student values(200215126,张成民,男,18,CS); （已做）6)插入一条选课记录：(2002151
13、28，1)。 insert into sc(Sno,Cno) values(200215128,1); （已做）7)对每一个系，求学生的平均年龄，并把结果存入数据库。 create table Dept_age (Sdept char(15), Avg_age smallint); insert into Dept_age (Sdept,Avg_age) select Sdept,avg(Sage)from student group by Sdept; 2修改数据 1)将学生 200215121 的年龄改为 22 岁。 update student set Sage=22 where Sno
14、=200215121; 2)将所有学生的年龄增加一岁。 update student set Sage=Sage+1; 3)将计算机科学系全体学生的成绩置零。 update sc set Grade=0 where CS=(select Sdept from student where student.Sno=sc.Sno); 3删除数据 1)删除学号为 200215128 的学生记录。 delete from student where Sno=200215128; 2)删除所有学生的选课记录。 delete from sc; 3)删除计算机科学系所有学生的选课记录。 delete from
15、 sc where CS=(select Sdept from student where student.Sno=SC.Sno ); 二查询操作1单表查询 2) 查询全体学生的姓名、学号、所在系。select sno,sname,sdeptfrom Student;5) 查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示所有系名。select sname,2013-sage birth,lower(sdept) sdeptfrom Student;10)查询年龄在 20-23 岁(包括 20 岁和 23 岁)之间的学生的姓名、系别和年龄。select sname,sdept,sage
16、from Studentwhere sage between 20 and 2312)查询计算机科学系(CS)、数学系(MA)、和信息系(IS)学生的姓名和性别。select sname,ssexfrom Studentwhere Sdept in(cs,ma,is)15)查询所有姓刘的学生的姓名、学号和性别。select sname,Sno,ssexfrom Studentwhere sname like 刘%19)查询 DB_Design 课程的课程号和学分。select Cno,Ccredit from course where Cname like DB_Design escape ;
17、 23)查询计算机科学系年龄在 20 岁以下的学生姓名。select snamefrom studentwhere sdept=cs and sage90;6)查询每个学生的学号、姓名、选修的课程名及成绩。select student.Sno,Sname,Cname,Grade from student,sc,course where student.Sno=sc.Sno and sc.Cno=course.Cno;3嵌套查询 1)查询与“刘晨”在同一个系学习的学生。select Sno,Sname,Sdeptfrom Studentwhere Sdept in( select Sdept f
18、rom Student where Sname=刘晨);2)查询选修了课程名为“信息系统”的学生学号和姓名。select Sno,Sname from student where Sno in (select Sno from sc where Cno in (select Cno from course where Cname=信息系统); 3)找出每个学生超过他选修课程平均成绩的课程号。select cno from sc xwhere grade (select AVG(Grade) from sc y where x.sno=y.sno group by Sno);4)查询其他系中比计
19、算机科学系某一学生年龄小的学生姓名和年龄。select sname,Sagefrom Studentwhere Sage = any (select sage from student where Sdept=cs) and Sdeptcs;5)查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。select sname,Sagefrom Studentwhere Sage = all (select sage from student where Sdept=cs) and Sdeptcs;6)查询选修了 1 号课程的学生姓名。select Sname from student whe
20、re exists (select * from sc where Sno=student.Sno and Cno=1);7) 查询没有选修 1 号课程的学生姓名。select Sname from student where not exists (select * from sc where Sno=student.Sno and Cno=1);8) 查询选修了全部课程的学生姓名。select Sname from student where not exists (select * from Course where not exists (select * from sc where
21、Sno=student.Sno and Cno=course.Cno);9) 查询至少选修了学生 200215121 选修的全部课程的学生号码。 select distinct Sno from sc scx where not exists (select * from sc scy where scy.Sno=200215122 and not exists (select * from sc scz where scz.Sno=scx.Sno and scz.Cno=scx.Cno); 4集合查询 1)查询计算机科学系的学生及年龄不大于 19 岁的学生。select * from stu
22、dent where Sdept=CS union select * from student where Sage=19;2)查询选修了课程 1 或课程 2 的学生。select Sno from sc where Cno=1 union select Sno from sc where Cno=2;3)查询计算机科学系的学生与年龄不大于 19 岁的学生的交集。select * from student where Sdept=CS intersect select * from student where Sage=19;4)查询既选修了课程 1 又选修了课程 2 的学生。select S
23、no from sc where Cno=1 intersect select Sno from sc where Cno=2;5)查询计算机科学系的学生与年龄不大于 19 岁的学生的差集。select * from student where Sdept=CS except select * from student where Sage=90;5)定义一个反应学生出生年份的视图。create view BT_S(Sno,Sname,Sbirth) as select Sno,Sname,2004-Sage from student;6)将学生的学号及他的平均成绩定义为一个视图。create
24、 view S_G(Sno,Gavg) as select Sno,avg(Grade) from sc group by Sno; 7)将 Student 表中所有女生记录定义为一个视图。 create view F_Student(F_sno,name,sex,age,dept) as select * from student where Ssex=女;2删除视图： 1)删除视图 BT_S: 3查询视图： 1)在信息系学生的视图中找出年龄小于 20 岁的学生。select Sno,Sage from IS_Student where Sage=80;4更新视图： 1)将信息系学生视图 IS_Student 中学号为 200215125 的学生姓名改为“刘辰”。 update IS_Student set Sname=刘辰 where Sno=200215125;2)向信息系学生视图 IS_Student 中插入一个新的学生记录，其中学号为 200215129，姓名为赵新，年龄为
