《数据库原理及应用》实验(共37页).doc

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

1、精选优质文档-倾情为你奉上数据库原理及应用实验专心-专注-专业 姓名 黄鸿波 学号 班别 计本10-2数据库原理及应用（本科）实验指导书-计算机信息教研室-桂林理工大学信息科学与工程学院二一二年五月目 录实验1 创建数据库与数据表2实验2 简单查询和连接查询10实验3 嵌套查询和集合查询12实验4 数据完整性14时间： 机房号： 得分： 实验1 创建数据库与数据表实验目的：1熟悉SQL SERVER环境；2掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令；3掌握数据表的创建和数据记录的插入方法。实验内容：1. 创建教学管理“JXGL”数据库，并建立学生STUD

2、ENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：学生STUDENT：SnoSnameSsexSageSdept95001李勇M20CS95002刘晨F19IS95003王敏F18MA95004张立M18IS课程COURSE：CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64选修SC：SnoCnoGrade95001192950012859500138895002290950023802. 创建供应系统“GYXT”数据库，其中包括供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表，各表中数据

3、如下所示。供应商S：SNOSNAMECITYS1精益天津S2万胜北京S3东方北京S4丰泰隆上海S5康健南京零件P：PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30工程项目J：JNOJNAMECITYJ1三建北京J2一汽长春J3弹簧厂天津J4造船厂天津J5机车厂唐山J6无线电厂常州J7半导体厂南京供应情况SPJ：SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P

4、1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500实验步骤：1创建教学管理“JXGL”数据库。(注：可采用可视窗体和SQL命令两种方法创建。以下是SQL命令方法)(1) 创建教学管理“JXGL”数据库。在命令窗格中输入如下命令，然后单击“运行”钮执行该命令。CREATE DATABASE JXGL;(2) 清空命令窗格后，在JXGL数据库中建立STUDENT表，并插入记录，然后执行。CREATE TABLE STUDENT( Sno char(5) not null unique, Sna

5、me char(20) not null unique, Ssex char(1),Sage int,Sdept char(20);INSERT INTO STUDENT VALUES(95001,李勇,M,20,CS);INSERT INTO STUDENT VALUES(95002,刘晨,F,19,IS);INSERT INTO STUDENT VALUES(95003,王敏,F,18,MA);INSERT INTO STUDENT VALUES(95004,张立,M,18,IS);(3) 清空查询窗格后，JXGL数据库中建立COURSE表，并插入记录，然后执行。CREATE TABLE

6、COURSE( Cno char(2) not null PRIMARY KEY(Cno), Cname char(20),Cpno char(2),Ccredit smallint);INSERT INTO COURSE VALUES(1,数据库,5,4);INSERT INTO COURSE VALUES(2,数学, ,2);INSERT INTO COURSE VALUES(3,信息系统,1,4);INSERT INTO COURSE VALUES(4,操作系统,6,3);INSERT INTO COURSE VALUES(5,数据结构,7,4);INSERT INTO COURSE V

7、ALUES(6,数据处理, ,2);INSERT INTO COURSE VALUES(7,C语言,6,4);(4) 清空查询窗格后，JXGL数据库中建立SC表，并插入记录，然后执行。CREATE TABLE SC( Sno char(5) not null, Cno char(2) not null,Grade smallint,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno) REFERENCES STUDENT(Sno),FOREIGN KEY(Cno) REFERENCES COURSE(Cno);INSERT INTO SC VALUES(95001,1,9

8、2);INSERT INTO SC VALUES(95001,2,85);INSERT INTO SC VALUES(95001,3,88);INSERT INTO SC VALUES(95002,2,90);INSERT INTO SC VALUES(95002,3,80);(5) 查看三个表中的内容。分别执行以下命令，查看STUDENT、COURSE和SC数据表中的内容。SELECT * FROM STUDENT;SELECT * FROM COURSE;SELECT * FROM SC;(6) ALTER TABLE、DROP TABLE、DROP DATABASE命令运用。向STUDE

9、NT表增加“入学时间”列，其数据类型为日期型，用SELECT命令查看表中内容。ALTER TABLE STUDENT ADD SCOME DATETIME;SELECT * FROM STUDENT;删除“入学时间”列，再用SELECT命令查看表中内容。ALTER TABLE STUDENT DROP COLUMN SCOME；SELECT * FROM STUDENT;删除数据表。DROP TABLE SC;DROP TABLE STUDENT;DROP TABLE COURSE; 删除数据库命令。切换到其他任意数据库，然后可用如下命令删除JXGL数据库。DROP DATABASE JXGL

10、;重复(1) (4)过程，建立数据库备以后的查询使用。2创建供应系统“GYXT”数据库。(注：可采用可视窗体和SQL命令两种方法创建。)下面写出实现如下操作的SQL语句：(1) 创建供应系统“GYXT”数据库。CREATE DATABASE GYXT;(2) 建立供应商表S。CREATE TABLE S(SNO CHAR(5) PRIMARY KEY, SNAME CHAR(6)NOT NULL, CITY CHAR(10) NOT NULL);(3) 建立零件表P。CREATE TABLE P(PNO CHAR(2) PRIMARY KEY, PNAME CHAR(6) NOT NULL,

11、COLOR CHAR(2) DEFAULT(红),WEIGHT INT,);(4) 建立工程项目表J。CREATE TABLE J(JNO CHAR(5) PRIMARY KEY, JNAME CHAR(10)NOT NULL, CITY CHAR(10) NOT NULL););*/CREATE TABLE SPJ (5) 建立供应情况表SPJ。CREATE TABLE SPJ(SNO char(5) not null, PNO char(2) not null,JNO char(5) not null,QTY int,PRIMARY KEY(SNO,PNO,JNO),FOREIGN KEY

12、(SNO) REFERENCES S(SNO),FOREIGN KEY(PNO) REFERENCES P(PNO),FOREIGN KEY(JNO) REFERENCES J(JNO),); (6) 应用INSERT INTO命令将相应数据写入到供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表。INSERT INTO S VALUES(S1,精益,天津);INSERT INTO S VALUES(S2,万胜,北京);INSERT INTO S VALUES(S3,东方,北京);INSERT INTO S VALUES(S4,丰泰隆,上海);INSERT INTO S VALUE

13、S(S5,南京,南京);*/INSERT INTO P VALUES(P1,螺母,红,12);INSERT INTO P VALUES(P2,螺栓,绿,17);INSERT INTO P VALUES(P3,螺丝刀,蓝,14);INSERT INTO P VALUES(P4,螺丝刀,红,14);INSERT INTO P VALUES(P5,凸轮,蓝,40);INSERT INTO P VALUES(P6,齿轮,红,30);INSERT INTO J VALUES(J1,三建,北京);INSERT INTO J VALUES(J2,一汽,长春);INSERT INTO J VALUES(J3,

14、弹簧厂,天津);INSERT INTO J VALUES(J4,造船厂,天津);INSERT INTO J VALUES(J5,机车厂,唐山);INSERT INTO J VALUES(J6,无线电厂,常州);INSERT INTO J VALUES(J7,半导体厂,南京INSERT INTO SPJ VALUES(S1,P1,J1,200);INSERT INTO SPJ VALUES(S1,P1,J3,100);INSERT INTO SPJ VALUES(S1,P1,J4,700);INSERT INTO SPJ VALUES(S1,P2,J2,100);INSERT INTO SPJ

15、VALUES(S2,P3,J1,400);INSERT INTO SPJ VALUES(S2,P3,J2,200);INSERT INTO SPJ VALUES(S2,P3,J4,500);INSERT INTO SPJ VALUES(S2,P3,J5,400);INSERT INTO SPJ VALUES(S2,P5,J1,400);INSERT INTO SPJ VALUES(S2,P5,J2,100);INSERT INTO SPJ VALUES(S3,P1,J1,200);INSERT INTO SPJ VALUES(S3,P3,J1,200);INSERT INTO SPJ VALU

16、ES(S4,P5,J1,100);INSERT INTO SPJ VALUES(S4,P6,J3,300);INSERT INTO SPJ VALUES(S4,P6,J4,200);INSERT INTO SPJ VALUES(S5,P2,J4,100);INSERT INTO SPJ VALUES(S5,P3,J1,200);INSERT INTO SPJ VALUES(S5,P6,J2,200);INSERT INTO SPJ VALUES(S5,P6,J4,500);时间： 机房号： 得分： 实验2 简单查询和连接查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标

17、准SQL查询语句的理解。2熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的基本查询操作。2基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求数学系学生的学号和姓名。USE JXGLSELECT Sno,SnameFROM STUDENTWHERE Sdept=MA;(2) 求选修了课程的学生学号。USE JXGLSELECT distinct SnoFROM sc;(3) 求选修了数学课的学生学号和成绩，并要求对查询结

18、果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT Sno,GradeFROM SCWHERE Cno=2ORDER BY Grade DESC,Sno ASC;(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。SELECT Sno,Grade=0.8*GradeFROM SCWHERE Cno=2 AND Grade BETWEEN 80 AND 90ORDER BY Grade DESC,Sno ASC;(5) 求数学系或计算机系姓刘的学生的信息。SELECT *FROM STUDENT WHERE Sname LIKE 刘%AND (Sd

19、ept=MAOR Sdept=IS)(6) 求缺少了成绩的学生的学号和课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NULL;(7) 查询每个学生的情况以及他（她）所选修的课程。SELECT STUDENT.Sno, Sname Ssex, Sage,Sdept,SC.Cno,Grade,CnameFROM STUDENT, SC ,COURSEWHERE STUDENT.Sno = SC.Sno AND COURSE.Cno=SC.Cno (8) 求学生的学号、姓名、选修的课程名及成绩。SELECT STUDENT.Sno, Sname,Grade,Cnam

20、eFROM STUDENT, SC ,COURSEWHERE STUDENT.Sno = SC.Sno AND COURSE.Cno=SC.Cno (9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。SELECT STUDENT.Sno, Sname,Grade,CnameFROM STUDENT, SC ,COURSEWHERE STUDENT.Sno = SC.Sno AND COURSE.Cno=SC.Cno AND SC.Grade=90 AND COURSE.Cname=数学; (10)查询每一门课的间接先行课（即先行课的先行课）。SELECT t1.Cno,t1.Cna

21、me,t3.Cno,t3.Cname from COURSE t1, COURSE t2,COURSE t3WHERE t1.Cpno=t2.Cno and t2.Cpno=t3.Cno2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1零件的供应商号SNO。USE GYXTSELECT DISTINCT SNOFROM SPJWHERE JNO=J1;(2) 求供应工程J1零件P1的供应商号SNO。USE GYXTSELECT DISTINCT SNOFROM SPJWHERE JNO=J1AND PNO=P1 ; (3) 统计每种零件的供应总量。USE GYXTSELECT S

22、PJ.PNO,PNAME,COUNT(*)FROM SPJ,PWHERE SPJ.PNO=P.PNOGROUP BY SPJ.PNO,PNAME;时间： 机房号： 得分： 实验3 嵌套查询和集合查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的高级查询操作。2基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求选修了数学的学

23、生的学号和姓名。SELECT STUDENT.Sno,SnameFROM STUDENT,SCWHERE STUDENT.Sno=SC.Sno AND Cno=2;(2) 求数学课程成绩高于李勇的学生学号和成绩。SELECT Sname,STUDENT.Snofrom SC,STUDENTWHERE Grade(SELECT Gradefrom SCWHERE Sno=95001 and Cno=2)and Cno=2 and STUDENT.Sno=SC.Sno;(3) 求其他系中年龄小于计算机系年龄最大者的学生。SELECT Sname,STUDENT.Snofrom STUDENTWHE

24、RE Sage(SELECT MAX(Sage)from STUDENTWHERE Sdept=IS)and not Sdept in (IS);(4) 求其他系中比计算机系学生年龄都小的学生。SELECT Sname,STUDENT.Snofrom STUDENTWHERE Sage=2 and min(grade)60;(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。SELECT Sno,avg(Grade) as avg_grade FROM SCgroup by Sno HAVING COUNT(Sno)=2 and min(grade)60ORD

25、ER BY avg(Grade) DESC;(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT Sno,avg(Grade) as avg_grade FROM SCgroup by Sno HAVING COUNT(Sno)=2 and min(Grade)=60ORDER BY avg(Grade) DESC;13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT Sno, COUNT(*) AS Cno_Num, AVG(Grade) AS AveScoreFROM SCGROUP BY S

26、no HAVING min(Grade)=60ORDER BY AveScore DESC, Cno_Num DESC2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1红色零件的供应商号SNO。SELECT SNOFROM SPJWHERE PNO in( SELECT PNOFROM PWHERE COLOR=红)AND JNO=J1(2) 求零件供应总量在1000种以上的供应商名字。SELECT SNAMEFROM S WHERE SNO IN(SELECT SNO FROM SPJ GROUP BY SNOHAVING SUM(QTY)=1000)时间： 机房号： 得分：

27、实验4 数据完整性实验目的：1. 了解SQL Server用约束来维护数据完整性的机制。2掌握在企业管理器和查询分析器中如何创建、使用及删除约束的方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现约束的创建、查看和删除操作。2创建人事关系RSGX数据库及其中的职工数据表和部门数据表，并实现完整性约束条件的定义。实验步骤：约束是SQL Server提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。常用的约束是NOT NULL（非空）、CHECK（检查）、UNIQUE（惟一）、PRIMARY KEY（主键）、FOREIGN KEY（外键）和DEFALT（默

28、认）约束。1在教学管理JXGL数据库中进行如下操作：(1) 建立一个CITY表（CityNo,CityName），将CityNo指定为主键。企业管理器方法：在JXGL数据库中创建CITY表，其方法见实验1。打开企业管理器，展开JXGL数据库下的“表”文件夹，在右边的详细窗格中选择要创建约束的表CITY，右击该表，选择“设计表”命令，启动表设计器。在表设计器中，右击需要设为主键的字段CityNo，选择“设置主键”命令其左侧将出现一个钥匙样图标。设置完毕，关闭表设计器。查询分析器方法：用SQL语句创建主键，可使用CREATE TABLE命令完成：CREATE TABLE CITY ( CityNo

29、 CHAR(2) CONSTRAINT c1 PRIMARY KEY, CityName VARCHAR(30) NOT NULL)；若创建表时没有设置主键，也可以使用ALTER TABLE命令为已存在的表创建主键约束：ALTER TABLE CITY ADD CONSTRAINT c1 PRIMARY KEY(CityNo)；其中c1为主键约束名称。(2) 为JXGL数据库中的CITY表，为其“CityName”创建惟一约束。企业管理器方法：在企业管理器中，选择设置惟一约束的CITY表，打开表设计器。在表设计器中，右击CityName字段，选择“索引/键”命令，出现“属性”对话框，并进入“索

30、引/键”选项卡。在该选项卡中，单击“新建”按钮，然后在“索引名”文本框中输入惟一约束名称，再从列名选项的下拉列表框中选择字段名称（如“CityName”），最后选择“创建UNIQUE”复选框和“约束”单选框，如图5.1所示。单击“关闭”按钮，完成惟一约束的创建。 图5.1 创建惟一约束对话框 图5.2 设置检查约束查询分析器方法：为已存在的表创建惟一约束，SQL语句如下：ALTER TABLE CITY ADD CONSTRAINT c2 UNIQUE(CityName)；(3) 为JXGL数据库中的SC表，为其“GRADE”创建检查约束。企业管理器方法：在企业管理器中，选择设置检查约束的SC

31、表，打开表设计器。在表设计器中，右击GRADE字段，选择“CHECK约束”命令，出现“属性”对话框，并进入“CHECK约束”选项卡。在该选项卡中，单击“新建”按钮，然后在“约束名”文本框中输入惟一约束名称，再在“约束表达式”文本框中输入约束条件“GRADE=0 AND GRADE=0 AND GRADE=100)；(4) 为JXGL数据库中的STUDENT表，为其“Ssex”创建默认约束M。企业管理器方法：在企业管理器中，选择设置默认约束的STUDENT表，打开表设计器。在表设计器中，选择Ssex字段，在默认值栏中输入“M”即可，如图5.3所示。单击“关闭”按钮，完成默认约束的创建。查询分析器

32、方法：为已存在的表创建默认约束，SQL语句如下：ALTER TABLE STUDENT ADD CONSTRAINT c4 DEFAULT M FOR Ssex；“默认值”框 图5.3 创建默认约束 图5.4 创建外键约束(5) 为JXGL数据库中的SC表，为其“Sno”创建外键约束。企业管理器方法：在企业管理器中，选择设置外键约束的SC表，打开表设计器。鼠标右击，选择“关系”命令，打开“属性”对话框的“关系”选项卡。在该选项卡中，单击“新建”按钮，从主键表下拉列表中选择外键引用表STUDENT，并在其下拉列表框中选择外键引用的SNO列；在外键表下拉表框中选择要创建外键的SC表，并从其下拉表框

33、中选择要创建外键约束的SNO列，如图5.4所示。单击“关闭”按钮，完成外键约束的创建。查询分析器方法：为已存在的表创建外键约束，SQL语句如下：ALTER TABLE SC ADD CONSTRAINT c5 FOREIGN KEY(Sno) REFERENCES STUDENT(Sno)；(6) 查看约束的定义在企业管理器中，选择要查看约束的表，打开表设计器，单击鼠标右键，分别选择“索引/键”、“关系”、“CHECK”命令，可以查看各类约束。(7) 删除约束企业管理器方法：在企业管理器的表设计器中，可以删除主键、去掉默认值。单击鼠标右键，分别选择“索引/键”、“关系”、“CHECK”命令，可

34、以删除各类约束。查询分析器方法：删除约束的SQL语句格式为：ALTER TABLE DROP CONSTRAINT ；例如，删除上例在JXGL数据库中为SC表的“Sno”列创建的外键约束。ALTER TABLE SC DROP CONSTRAINT c5;2创建人事关系RSGX数据库，并定义职工和部门两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码；要求在模式中完成以下完整性约束条件的定义：(1) 定义每个模式的主码；(2) 定义参照完整性；(3) 定义职工年龄不得超过60岁。CREATE TABLE RSGX

35、CREATE TABLE department(Dno char(12) primary key,Dname char(10),Dmanager char(16),Dphone char(13)CREATE TABLE employee(Eno char(10),Ename char(10),Eage char(6),Eduty char(10),Elaborage char(10),Dno char(10) not null foreign key references department(Dno),check(Eage=60),primary key(Eno)附录一： 实验课考核办法上机

36、实验是数据库系统原理这门课程的重要环节。通过基于关系型网络数据库管理系统SQL Server的上机实验，使学生进一步了解关系数据模型及关系数据库管理系统的基本原理和标准的SQL语言的使用。本课程的实验环境：服务器使用WINDOWS 2000 SERVER操作系统，并安装SQL Server 2000标准版数据库软件；客户端操作系统为WINDOWS。实验方式和基本要求：实验一人一组，要求学生在规定时间内独立完成操作，并在实验结束后提交实验报告。结合实验纪律、出勤情况、实验操作、综合实验报告，分五级给出成绩，评分标准如下：优秀：出勤满，实验纪律好，听从指导老师指挥，动手能力强，综合试验数据处理正确，报告分析思路清晰，字迹工整。良好：出勤满，实验纪律好，听从指导老师指挥，动手能力较强，综合试验数据处理正确，报告分析思路清晰，字迹较工整。中等：出勤满，实验纪律好，听从指导老师指挥，动手能力较强，综合试验数据处理较正确，报告分析思路较清晰，字迹基本工整。及格：只有少数特殊情况下没有出勤，实验纪律一般，听从指导老师指挥，动手能力一般，综合试验数据处理基本正确，报告分析思路一般。不及格：经常不参加实验，实验纪律不好；不听从指导老师指挥；动手能力较差；综合试验数据处理不正确；不写实验报告。