《数据库系统原理》实验报告Word格式文档下载.docx

1、学生表：StudentSno（学号）Sname（姓名）Ssex（性别）Sage（年龄）Sdept（所在系）200215121李勇男20CS（计算机科学系）200215122刘晨女19200215123王敏18MA（数学系）200215125张立IS（信息系）课程表：CourseCno（课程号）Cname（课程名）Cpno（先修课）Ccredit（学分）1数据库542数学3信息系统操作系统6数据结构7数据处理PASCAL学生选课表：SCGrade（成绩）9285889080实验一 SQL数据定义一、实验目的和要求1掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；2掌握索

2、引的建立与删除的方法。二、实验内容与步骤（一）建立数据库通过企业管理器或查询分析器建立学生-课程数据库xskc。create database xskcon （ name=xskc_data, filename=d:sjksyxskc_data.mdf）log on （ name=xskc_log,sjksyxskc_log.ldf注：先在E：盘上建立一个文件夹（例如：E:sjksy），数据库文件保存到自建的文件夹中。（二）基本表的定义、修改与删除1定义基本表利用查询分析器或企业管理器创建基本表，并输入数据。【题1-01】 建立一个学生表Student，它由学号Sno、姓名Sname、性别Ss

3、ex、年龄Sage、所在系Sdept五个属性组成。要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。CREATE TABLE Student （Sno CHAR（9） PRIMARY KEY,S Sname CHAR（8） NOT NULL, Ssex CHAR（2） DEFAULT 男, Sage INT, Sdept CHAR（20） ）;说明：在Microsoft SQL Server 2000的查询分析器（Query Analyzer）中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。通常，SQL Server 2000对大多数末尾带有分号的SQL命令都能顺利执

4、行，但对少数的SQL命令，末尾若带分号，则SQL Server 2000会给出错误信息提示。 比如，若在实验五的例1的SQL命令末尾加上一个分号“；”，SQL Server 2000就会出现“Incorrect syntax near ;”的提示，虽然SQL Server 2000实际上已经执行了该命令。【题1-02】 建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。要求“课程号”为主键，“课程名”属性不能为空。CREATE TABLE Course （Cno CHAR（4） PRIMARY KEY, Cname CHAR（40） N

5、OT NULL , Cpno CHAR（4）, Ccredit INT, FOREIGN KEY（Cpno） REFERENCES Course（Cno）【题1-03】 建立学生选修课表SC，包含学号Sno、课程号Cno、成绩Grade三个字段。要求建立主键及与student、kc表联接的外键，并创建检查约束（Grade=0 and Grade=100）。CREATE TABLE SC （ Sno CHAR（9）, Cno CHAR（4）, Grade INT, PRIMARY KEY（Sno,Cno）, FOREIGN KEY（Sno） REFERENCES Student（Sno）, FO

6、REIGN KEY（Cno） REFERENCES Course（Cno）2 修改基本表利用查询分析器或企业管理器修改基本表。【题1-04】 向基本表Student中增加“入学时间”属性列，其属性名为S_entrance，数据类型为日期型。ALTER TABLE Student ADD S_entrance DATETIME;【题1-05】 将Student表中Sage（年龄）的数据类型改为SMALLINT型。ALTER TABLE Student ALTER COLUMN Sage SMALLINT;【题1-06】 对Course表，增加课程名称必须取唯一值的约束条件。ALTER TABLE

7、 Course ADD UNIQUE（Cname）;【题1-07】将Student表的Sdept列允许空值的属性更改为不允许为空。ALTER TABLE Student ALTER COLUMN Sdept CHAR（20） NOT NULL;【题1-08】删除Student表中的S_entrance列。ALTER TABLE Student DROP COLUMN S_entrance;3 删除基本表【题1-09】 删除Student表。DROP TABLE Student;此表删除后，请立即将其建立起来，以便后面的例子使用。（三）索引的建立和删除1 建立索引【题1-10】 在基本表Stud

8、ent的Sname（姓名）列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。其语句为：CREATE CLUSTERED INDEX Stu_Sname ON Student（Sname）;【题1-11】分别为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按Sno（学号）升序建唯一索引，Course表按Cno（课程号）升序建唯一索引，SC表按Sno（学号）升序和Cno（课程号）号降序建唯一索引。CREATE UNIQUE INDEX Stu_Sno ON Student（Sno）;CREATE UNIQUE INDEX

9、Cou_Cno ON Course（Cno）;CREATE UNIQUE INDEX SCno ON SC（Sno ASC, Cno DESC）;2 删除索引【题1-12】删除Student表的Stu_Sname索引。DROP INDEX Student.Stu_Sname;实验二 SQL数据查询1掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2熟练掌握查询语句的一般格式。3熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。二、实验内容及步骤1 无条件查询【题2-01】 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：SELECT * /*这里

10、的“*”等价于ALL*/FROM Student;其结果为Student表中的全部数据。【题2-02】 查询全体学生的姓名（Sname）、学号（Sno）、所在系（Sdept）。这是一个无条件的投影查询，其命令为：SELECT Sname, Sno, Sdept【题2-03】 查询全体学生的学号（Sno）、姓名（Sname）及出生年份。由于SELECT子句的不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：SELECT Sno, Sname, 2014-Sage as 2014-Sage【题2-04】 查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名

11、。SELECT Sno, Sname, 2012-Sage Year of Birth, LOWER（Sdept） Sdept【题2-05】 查询选修了课程的学生学号。SELECT DISTINCT SnoFROM SC;2 条件查询【题2-06】 查询数学系（MA）全体学生的学号（Sno）和姓名 （Sname）。SELECT Sno, SnameFROM StudentWHERE Sdept=MA【题2-07】查询考试成绩有不及格的学生的学号。FROM SCWHERE Grade60;【题2-08】查询所有年龄在20岁以下的学生姓名（Sname）及年龄（Sage）。SELECT Sname,

12、 SageWHERE Sage20;【题2-09】查询所有年龄在1820岁（包括18岁和20岁）之间的学生姓名（Sname）及年龄（Sage）。WHERE Sage=18 AND Sage=22;或WHERE Sage BETWEEN 18 AND 22;【题2-10】 查询年龄不在18-20岁之间的学生姓名（Sname）及年龄（Sage）。WHERE Sage NOT BETWEEN 18 AND 20;【例11】 查询计算机系、数学系和信息系学生的学号（Sno）、姓名（Sname）和性别（Ssex）。SELECT Sno, Sname, Ssex FROM StudentWHERE Sde

13、pt IN （CS, IS）;等价于：SELECT Sno, Sname, Ssex OR Sdept=【例12】 查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名（Sname）和性别（Ssex）。SELECT Sname, SsexWHERE Sdept NOT IN （【例13】 查询所有姓刘的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。SELECT Sname, Sno, SsexWHERE Sname LIKE 刘%【例14】 查询姓“刘”且全名为4个汉字的学生的姓名（Sname）和所在系（Sdept）。SELECT Sname, Sdept

14、 FROM Student刘_【例15】 查询所有不姓刘的学生姓名（Sname）和年龄（Sage）。WHERE Sname NOT LIKE 【例16】 查询课程名为“DB_设计”的课程号（Cno）和学分（Ccredit）。SELECT Cno, CcreditFROM CourseWHERE Cname LIKE DB_设计 ESCAPE 【例17】 查询以DB_开头，且倒数第2个汉字字符为“设”的课程的详细情况。SELECT *DB_%设_ESCAPE【例18】 假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号（Sno）和相应的课程号（Cno）

15、。SELECT Sno, CnoWHERE Grade IS NULL;【例19】 查询所有有成绩的学生学号（Sno）和课程号（Cno）。SELECT Sno, CnoFROM SCWHERE Grade IS NOT NULL;【题20】查询计算机系年龄在20岁以下的学生姓名。select Snamefrom studentwhere Sage实验三 连接、嵌套和集合查询3熟练掌握连接、嵌套和集合查询的使用。（一）连接查询1 不同表之间的连接查询【题3-01】 查询每个学生及其选修课程的情况。本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的

16、，因此，其操作命令为：SELECT Student.*, SC.*FROM Student, SCWHERE Student.Sno = SC.Sno；若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeWHERE Student.Sno= SC.Sno;2 自身连接【例35】 查询每一门课的间接先修课（即先修课的先修课）。在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的

17、先修课程。这就需要要将Course表与其自身连接。为方便连接运算，这里为Course表取两个别名分别为A，B。则完成该查询的SQL语句为：SELECT A.Cno, A.Cname, B.CpnoFROM Course A, Course BWHERE A.Cpno =B.Cno;3 外连接【例36】把例33中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为：SELECT Student.Sno, Sname, Ssex, Sdept, Cno, GradeLEFT JOIN SC ONStudent.Sno= SC.Sno;以上左连接操作也可以用如下的右连接操

18、作代替，其结果完全一样。RIGHT JOIN Student ONSC.Sno=Student.Sno;4 复合条件连接【例38】 查询每个学生的学号（Sno）、姓名（Sname）、选修的课程名（Cname）及成绩（Grade）。本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno;（二）嵌套查询1 带谓词IN的嵌套查询【例39】查询与“李伟”在同一个系学习的学生学号（S

19、no）、姓名（Sname）和系名（Sdept）。该查询可构造嵌套查询实现，其SQL语句如下：SELECT Sno, Sname, SdeptWHERE Sdept IN （SELECT Sdept FROM Student WHERE Sname=李伟）;本例中的查询也可以用自身连接来完成，其SQL语句如下：SELECT A.Sno , A.Sname , A.SdeptFROM Student A , Student BWHERE A.Sdept=B.Sdept AND B.Sname=李伟;【例40】 查询选修了编号为“2”的课程的学生姓名（Sname）和所在系（Sdept）。SELECT

20、 Sname, SdeptWHERE Sno IN （SELECT Sno FROM SC WHERE Cno=【例41】 查询选修了课程名为“数据结构”的学生学号（Sno）和姓名（Sname）。本查询涉及学号、姓名和课程名（Cname）三个属性。学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course两个表之间没有公共属性，必须通过SC表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。SELECT Sno, Sname /* 最后在Studen关系中 */FROM Student /* 取出Sno和Sname */ （SELECT Sno /* 然后在SC关系中找出 */ FROM SC /*选修了3号课程的学生学号*/ WHERE Cno IN （SELECT Cno /* 首先在Course关系中 */ FROM Course /*找出“数据结构”的课程号*/ WHERE Cname = 数据结构）； /*结果为5号 */本查询同样可以用连接查询实现：SELECT S.Sno, SnameFROM Student S, SC R, Course CWHERE S.Sno=R.Sno AND R.Cno=C.Cno AND C.C