new《数据库原理及应用》实验指导书.doc

《new《数据库原理及应用》实验指导书.doc》由会员分享，可在线阅读，更多相关《new《数据库原理及应用》实验指导书.doc（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库原理及应用实验指导书中北大学软件学院数据库原理及应用实验类别：课内实验 实验课程名称：数据库原理及应用实验室名称：软件工程实验室 实验课程编号： 总学时：72 学 分： 适用专业：软件工程先修课程：离散数学、计算机导论及操作、数据结构实验在教学培养计划中地位、作用：数据库原理及应用是一门理论与实践相结合的课程，上机实验环节是本课程的重要组成部分。实验的目的是为了配合课堂教学，进一步强化对数据库原理的理解。实验的任务是要结合数据库原理和 SQL Server 2005数据库系统，熟练掌握和深入理解课堂教学内容，实现从理论到实践的统一。 实验一 SQL的数据定义（2学时）1、实验目的(1)

2、掌握DBMS的数据定义功能(2) 掌握SQL语言的数据定义语句2、实验内容 (1) 创建、删除表(2) 查看、修改表的定义(3) 理解索引的特点(4) 创建和删除索引3、实验要求(1) 熟练掌握SQL的数据定义语句CREATE、ALTER、DROP(2) 写出实验报告4、实验步骤设有一个学生-课程数据库，包括学生关系Student、课程关系Course和选修关系SC：学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade) (1) 创建、删除表，例如：CREAT

3、E TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1) , Sage INT, Sdept CHAR(15) CREATE TABLE SC( Sno CHAR(5), Cno CHAR(3), Grade int, Primary key (Sno, Cno);DROP TABLEStudent (2) 查看、修改表的定义，例如：ALTER TABLE Student ADD Scome DATETIMEALTER TABLE Student ALTER COLUMN Sage SMA

4、LLINT(3) 创建和删除索引CREATE UNIQUE INDEX Stusno ON Student(Sno)；CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)； DROP INDEX Stusno实验二 SQL的单表数据查询（2学时）1、实验目的(1) 掌握RDBMS的数据单表查询功能(2) 掌握SQL语言的数据单表查询语句2、实验内容(1) SELECT语句的基本用法(2) 使用WHERE子句进行有条件的查询(3) 使用IN，NOT IN，BETWEEN

5、 AND等谓词查询(4) 利用LIKE子句实现模糊查询(5) 利用ORDER BY子句为结果排序(6) 用SQL Server的聚集函数进行统计计算(7) 用GR0UP BY子句实现分组查询的方法3、实验要求(1) 熟练掌握SQL的数据查询语句SELECT(2) 写出实验报告4、实验步骤4.1 创建（定义）示例数据库S_T（复习）（1） 利用SQL Server集成管理器（简称SSMS）交互式创建数据库；（2） 利用SQL语句中的Create Database命令创建数据库（在查询子窗口中输入并执行SQL语句。例如：Create Database S_T;4.2 将S_T设为当前数据库（复习）

6、例如：use S_T;4.3 在当前数据库S_T中创建学生表Student、课程表Course和选修表SC（复习）（1）创建3个表 利用SQL语句中的Create Table命令创建表create table Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );go/*表Student的主码为Sno，属性列Sname取唯一值*/create table Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(4

7、0), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) );go/*表Course的主码为Cno，属性列Cpno(先修课)为外码，被参照表为Course，被参照列是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) REFERENCES Course(C

8、no) ); go /*表SC的主码为(Sno, Cno), Sno和Cno均为外码，被参照表分别为Student和Course，被参照列分别为Student.Sno和Course.Cno*/ 利用SQL Server集成管理器（简称SSMS）交互式创建3个表。（2）在3个表中添加示例数据（任选一种数据添加方法）表Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept李勇男20CS刘晨女19CS王敏女18MA张立男19IS表Course课程号Cno课程名Cname现行课Cpno学分Ccredit1数据库542数学23信息系统544操作系统635数据结构746数据处理27

9、PASCAL语言64表SC学号Sno课程号Cno成绩Grade192285388290380 用SQL语句中的更新语句（Insert语句、Update语句和Delete语句）往3个表输入示例数据。use S_T;/*将S_T设为当前数据库*/insert into student values(,李勇,男,20,CS);insert into student values(,刘晨,女,19,CS);insert into student values(,王敏,女,18,MA);insert into student values(,张立,男,19,IS);go/*为表Student添加数据*/

10、insert into course values(1, 数据库, NULL,4);insert into course values(2, 数学, NULL,2);insert into course values(3, 信息系统, NULL,4);insert into course values(4, 操作系统, NULL,3);insert into course values(5, 数据结构, NULL,4);insert into course values(6, 数据处理, NULL, 2);insert into course values(7, java, NULL,4);g

11、oupdate Course set Cpno = 5 where Cno = 1;update Course set Cpno = 1 where Cno = 3;update Course set Cpno = 6 where Cno = 4;update Course set Cpno = 7 where Cno = 5;update Course set Cpno = 6 where Cno = 7;/*为表Course添加数据*/goinsert into SC values(, 1,92);insert into SC values(, 2,85);insert into SC v

12、alues(, 3,88);insert into SC values(, 2,90);insert into SC values(, 3,80);/*为表SC添加数据*/ go 利用SQL Server集成管理器（简称SSMS）交互式输入数据。4.4 对学生关系Student、课程关系Course和选修关系SC进行查询。4.4.1 基本练习 (1)SELECT语句的基本用法例如：查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student (2)使用WHERE子句进行有条件的查询例如：查询选修2号课程且成绩在90分以上的所有学生的学号、姓

13、名SELECT Student.Sno, student.SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND SC.Cno= 2 AND SC.Grade 90 (3)使用IN，NOT IN，BETWEEN等谓词查询例如：查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname,SsexFROM StudentWHERE Sdept IN ( IS,MA,CS )例如：查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM St

14、udentWHERE Sage BETWEEN 20 AND 23(4)利用LIKE子句实现模糊查询例如：查询所有姓刘学生的姓名、学号和性别。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE 刘%(5)利用ORDER子句为结果排序例如：查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno,GradeFROM SCWHERE Cno= 3ORDER BY Grade DESC(6)用SQL Server的统计函数进行统计计算例如：计算1号课程的学生平均成绩。SELECT AVG(Grade)FROM SCWHERE

15、 Cno= 1(7)用GR0UP BY子句实现分组查询的方法例如：查询选修了3门以上课程的学生学号。SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*) 34.4.2 扩展练习（要求写出并执行SQL语句来完成以下各种操作，记录查询结果）（1）查询全体学生的学号、姓名和年龄；SELECT Sno,Sname,SageFROM Student李勇 20刘晨 19王敏 18张立 19（2）查询所有计算机系学生的详细记录；SELECT *FROM Studentwhere Sdept IN (CS)李勇 男20CS 刘晨 女19CS （3）找出考试成绩为优秀（90分

16、及以上）或不及格的学生的学号、课程号及成绩；SELECT Sno,Cno,GradeFROM SCWHERE Grade90;1 92 （4）查询年龄不在1920岁之间的学生姓名、性别和年龄；SELECT Sname,Ssex,SageFROM StudentWHERE Sage NOT BETWEEN 19 AND 20;王敏 女18（5）查询数学系（MA）、信息系（IS)的学生的姓名和所在系；SELECT Sname,SdeptFROM Studentwhere Sdept IN (MA,IS)王敏 MA 张立 IS （6）查询名称中包含“数据”的所有课程的课程号、课程名及其学分；SELE

17、CT Cno,Cname,CcreditFROM CourseWHERE Cname LIKE 数据%;1 数据库 45 数据结构 46 数据处理 2（7) 找出所有没有选修课成绩的学生学号和课程号；SELECT Sno,CnoFROM SCWHERE Grade IS NULL;（思考：如何查询所有没有选修课成绩的计算机系的学生学号和课程号？）SELECT SC.Sno,CnoFROM SC,StudentWHERE Sdept=CS AND Grade IS NULL ;（8）查询学生选修课的最高分、最低分以及平均成绩；SELECT MAX(Grade),MIN(Grade),AVG(Gr

18、ade)FROM SCWHERE Sno=;928588（9）查询选修了2号课程的学生的学号及其成绩，查询结果按成绩升序排列；SELECT Sno,GradeFROM SCWHERE Cno=2ORDER BY Grade ASC;8590（10）查询每个系名及其学生的平均年龄。SELECT Sdept,AVG(Sage)FROM StudentGROUP BY SdeptCS 19IS 19MA 18 （思考：如何查询学生平均年龄在19岁以下（含19岁）的系别及其学生的平均年龄？）SELECT Sdept,AVG(Sage)FROM StudentGROUP BY SdeptHAVING A

19、VG(Sage)= 90;又如：查询每个学生的学号、姓名、选修的课程名及成绩。SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;（5）嵌套查询（带有IN谓词的子查询）例如：查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = 刘晨);

20、/* 解法一*/可以将本查询中的IN谓词用比较运算符=来代替：SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = 刘晨); /* 解法二*/也可以使用自身连接完成以上查询：SELECT s1.Sno, s1.Sname, s1.SdeptFROM Student s1, Student s2WHERE s1.Sdept = S2.Sdept AND s2.Sname = 刘晨; /* 解法三*/还可以使用EXISTS谓词完成本查询：SELECT Sno, Sname

21、, SdeptFROM Student S1WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname=刘晨); /* 解法四*/又如：查询选修了课程名为“信息系统”的学生号和姓名。SELECT Sno, SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 信息系统 ) );也可以使用连接查询来完成上述查询：SELECT Student.Sno, Snam

22、eFROM Student, SC, CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = 信息系统;（6）嵌套查询（带有比较运算符的子查询）例如：找出每个学生超过他所选修课程平均成绩的课程号。SELECT Sno, CnoFROM SC xWHERE Grade = ( SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno);（7）嵌套查询（带有ANY或ALL谓词的子查询）例如：查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄。SELECT Sna

23、me, Sage FROM StudentWHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept = CS) AND Sdept CS; 本查询也可以使用聚集函数来实现：SELECT Sname, Sage FROM StudentWHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept = CS) AND Sdept CS;又如：查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。SELECT Sname, Sage FROM StudentWHERE Sage ALL (SELECT

24、Sage FROM Student WHERE Sdept = CS) AND Sdept CS; 也可以使用聚集函数来实现：SELECT Sname, Sage FROM StudentWHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept = CS) AND Sdept CS;（8）嵌套查询（带有EXISTS谓词的子查询）例如：查询所有选修了1号课程的学生姓名。SELECT SnameFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);又如

25、：查询所有未选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);可以使用带有EXISTS谓词的子查询实现全称量词或蕴涵逻辑运算功能：例如：查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno

26、);又如：查询至少选修了学生选修的全部课程的学生号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno= AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno);（9）集合查询例如：查询计算机系的学生以及年龄不大于19岁的的学生。SELECT *FROM StudentWHERE Sdept=CSUNION /*并集运算*/SELECT *FROM StudentWHERE S

27、age=19; 可以改用多重条件查询：SELECT *FROM StudentWHERE Sdept=CS OR Sage=19;又如：查询既选修了课程1又选修了课程2的学生（交集运算）。SELECT SnoFROM SCWHERE Cno=1INTERSECT /*交集运算*/SELECT SnoFROM SCWHERE Cno=2;可以使用嵌套查询：SELECT SnoFROM SCWHERE Cno=1 AND Sno IN (SELECT Sno FROM SC WHERE Cno=2);思考：能不能改用多重条件查询？SELECT SnoFROM SCWHERE Cno=1 AND Cno=2;再如：查询计算机系的学生与年龄不大于19岁的学生的差集。SELECT *FROM StudentWHERE Sdept=CSEXCEPT /*差集运算*/SELECT *FROM StudentWHERE Sage19;4.2.2 扩展练习（要求写出并执行SQL语句完成以下各种操作，记录查询结果）（1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生学号及成绩-如果没有学生选择