数据库实验报告22.docx

1、数据库实验报告22数据库原理及应用（本科）实验指导书-计算机信息教研室- 二一二年五月实验1 创建数据库与数据表2实验2 简单查询和连接查询10实验3 嵌套查询和集合查询12实验4 数据完整性14时间： 机房号： 得分： 实验1 创建数据库与数据表实验目的：1熟悉SQL SERVER环境；2掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令；3掌握数据表的创建和数据记录的插入方法。实验内容：1. 创建教学管理“JXGL”数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：学生STUDENT：SnoSnameSsexSag

2、eSdept95001李勇M20CS95002刘晨F19IS95003王敏F18MA95004张立M18IS课程COURSE：CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64选修SC：SnoCnoGrade95001192950012859500138895002290950023802. 创建供应系统“GYXT”数据库，其中包括供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表，各表中数据如下所示。供应商S：SNOSNAMECITYS1精益天津S2万胜S3S4丰泰隆S5康健零件P：PNOPNAMECOLO

3、RWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30工程项目J：JNOJNAMECITYJ1J2一汽J3弹簧厂天津J4造船厂天津J5机车厂J6无线电厂J7半导体厂供应情况SPJ：SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500实验步

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

5、DENT 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 COURSE( Cno char(2) not null PRIMARY KEY(Cno), Cname char(20),Cpno char(2),Ccredi

6、t 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 VALUES(6,数据处理, ,2);INSERT INTO COURSE VALUES(7,C语言,6,4);(4) 清空查询窗格后，JXGL数据库中建立SC表，

7、并插入记录，然后执行。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,92);INSERT INTO SC VALUES(95001,2,85);INSERT INTO SC VALUES(95001,3,88);INSERT INT

8、O 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命令运用。向STUDENT表增加“入学时间”列，其数据类型为日期型，用SELECT命令查看表中内容。ALTER TABLE STUDENT ADD SCOME DATETIME;SEL

9、ECT * 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;重复(1) (4)过程，建立数据库备以后的查询使用。2创建供应系统“GYXT”数据库。(注：可采用可视窗体和SQL命令两种方法创建。)下面写出实现如下操作的SQ

10、L语句：(1) 创建供应系统“GYXT”数据库。CREATE DATABASE GYXT;(2) 建立供应商表S。CREATE TABLE S (SNO char(6), SNAME char(8), SCITY char(20);INSERT INTO S VALUES(S1,精益,天津);INSERT INTO S VALUES(S2,万胜,);INSERT INTO S VALUES(S3,);INSERT INTO S VALUES(S4,丰泰隆,);INSERT INTO S VALUES(S5,康健,); (3) 建立零件表P。CREATE TABLE P(PNO char(6),

11、 PNAME char(8), COLOR char(8), WEIGHT INT); 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); (4) 建立工程项目表J。CREATE TABLE J(JNO CHAR(8), JNAME CHA

12、R(8), CITY CHAR(20); INSERT INTO J VALUES(J1,); INSERT INTO J VALUES(J2,一汽,); INSERT INTO J VALUES(J3,弹簧厂,天津); INSERT INTO J VALUES(J4,造船厂,天津); INSERT INTO J VALUES(J5,机车厂,); INSERT INTO J VALUES(J6,无线电厂,); INSERT INTO J VALUES(J7,半导体厂,);(5) 建立供应情况表SPJ。CREATE TABLE SPJ(SNO CHAR(6), PNO CHAR(6), JNO

13、CHAR(6), QTY INT); 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 VALUES(S2,P3,J1,400); INSERT INTO SPJ VALUES(S2,P3,J2,200); INSERT INTO SPJ VALUES(S2,P3,J4,500); INSERT INTO SPJ

14、 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 VALUES(S4,P5,J1,100); INSERT INTO SPJ VALUES(S4,P6,J3,300); INSERT INTO SPJ VALUES(S4,P6,J4,200); INSERT INTO

15、 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);(6) 应用INSERT INTO命令将相应数据写入到供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表。时间： 机房号： 得分： 实验2 简单查询和连接查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握简单表的数据查询、数据排序和数据连接查询的操作

16、方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的基本查询操作。2基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求数学系学生的学号和姓名。SELECT Sno,SnameFROM STUDENTWHERE Sdept=MA (2) 求选修了课程的学生学号。 SELECT DISTINCT SnoFROM SC(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。 SELECT Sno,GradeFROM SCWHERE (C

17、no=2)ORDER BY Grade DESC ,Sno ASC (4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。 SElECT Sno,Grade*0.8 As 乘分后FROM SCWHERE Grade=80 and Grade=90(10)查询每一门课的间接先行课（即先行课的先行课）。SELECT a.Cno,b.CpnoFROM COURSE a,COURSE bWHERE a.Cpno=b.Cno2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1零件的供应商号SNO。SELECT DISTINCT SNOFROM SPJWHE

18、RE JNO=J1(2) 求供应工程J1零件P1的供应商号SNO。SELECT SNOFROM SPJWHERE JNO=J1 AND PNO=P1(3) 统计每种零件的供应总量。SELECT JNO,SUM(QTY) AS 每种零件的供应总量FROM SPJGROUP BY JNO时间： 机房号： 得分： 实验3 嵌套查询和集合查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的高级查询操作。2基于实验一创建的供应系统GYX

19、T数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求选修了数学的学生的学号和姓名。SELECT Sno,SnameFROM STUDENTWHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM COURSE WHERE Cname=数学) (2) 求数学课程成绩高于李勇的学生学号和成绩。SELECT STUDENT.Sno,Grade FROM STUDENT,SC,COURSE WHERE COURSE.Cno=SC.Cno AND SC.Sno=ST

20、UDENT.Sno AND COURSE.Cname=数学 AND SC.Grade (SELECT Grade FROM STUDENT,SC,COURSE WHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname=数学 AND STUDENT.Sname=李勇)(3) 求其他系中年龄小于计算机系年龄最大者的学生。SELECT *FROM STUDENT WHERE SageALL(SELECT Sage FROM STUDENT WHERE Sdept=CS)AND SdeptCS(4) 求其他系中比计算机系学生年龄都小

21、的学生。SELECT *FROM STUDENT WHERE SageANY (SELECT Sage FROM STUDENT WHERE SDEPT=CS) AND SDEPTCS(5) 求选修了数学课的学生姓名。SELECT SnameFROM STUDENTWHERE EXISTS (SELECT * FROM SC WHERE STUDENT.Sno=SC.Sno AND Cno=2)(6) 求没有选修数学课的学生姓名。SELECT SnameFROM STUDENTWHERE (NOT EXISTS (SELECT * FROM SC WHERE STUDENT.Sno=SC.Sn

22、o AND Cno=2)(7) 查询选修了全部课程的学生的姓名。SELECT Sname FROM STUDENTWHERE NOT EXISTS (SELECT * FROM COURSE WHERE NOT EXISTS (SELECT * FROM SC WHERE STUDENT.Sno=SC.Sno AND COURSE.Cno=SC.Cno)(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。SELECT Sno,Sname FROM STUDENT WHERE (EXISTS (SELECT * FROM SC WHERE Sno=STUDENT.Sn

23、o AND Cno IN (SELECT Cno FROM SC WHERE Sno=95002)(9) 求选修各门课的人数及平均成绩。SELECT COUNT(*) AS 人数,AVG(Grade) AS 平均成绩FROM SCGROUP BY Cno(10)求选修课程在2门以上且都及格的学生号及总平均分。SELECT Sno,AVG(GRADE)AS 总平均分FROM SCGROUP BY SnoHAVING COUNT(Cno)=2 AND MIN(GRADE)=60(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。SELECT Sno, AVG(Gr

24、ade) AS 总平均分From SCWHERE EXISTS (SELECT * FROM STUDENT WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=STUDENT.Sno AND Cno =2)(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT Sno, COUNT(*) AS 及格门数, AVG(Grade) AS 平均成绩FROM SC WHERE Grade=60 GROUP BY Sno ORDER BY 平均成绩 DESC, 及格门数 DESC(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT Sno, COUNT(*) AS 及格门数, AVG(Grade) AS 平均成绩 FROM SC WHERE EXISTS (SELECT * FROM STUDENT WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=STUDENT.Sno AND Cno =1000)时间： 机房号： 得