数据库原理及应用实验.pdf

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

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

2、：1.创建教学管理“JXGL”数据库，并建立学生 STUDENT、课程 COURSE 和选修 SC 三个数据表，各表中的数据如下所示：学生 STUDENT：Sno Sname Ssex Sage Sdept 95001 李勇 M 20 CS 95002 刘晨 F 19 IS 95003 王敏 F 18 MA 95004 张立 M 18 IS 课程 COURSE：Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 2 3 信 息 系统 1 4 4 操 作 系统 6 3 5 数 据 结构 7 4 6 数 据 处理 2 7 C 语言 6 4 选修 SC：Sno Cno Gra

3、de 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 2.创建供应系统“GYXT”数据库，其中包括供应商表 S、零件表 P、工程项目表 J 和供应情况表 SPJ 四个数据表，各表中数据如下所示。供应商 S：SNO SNAME CITY S1 精益 天津 S2 万胜 北京 S3 东方 北京 S4 丰泰隆 上海 S5 康健 南京 零件 P：PNO PNAME COLOR WEIGHT P1 螺母 红 12 P2 螺栓 绿 17 P3 螺丝刀 蓝 14 P4 螺丝刀 红 14 P5 凸轮 蓝 40 P6 齿轮 红 30 工程项目 J：JN

4、O JNAME CITY J1 三建 北京 J2 一汽 长春 J3 弹簧厂 天津 J4 造船厂 天津 J5 机车厂 唐山 J6 无 线 电厂 常州 J7 半 导 体厂 南京 供应情况 SPJ：SNO PNO JNO QTY S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J2 100 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 S3 P1 J1 200 S3 P3 J1 200 S4 P5 J1 100 S4 P6 J3 300 S4 P6

5、J4 200 S5 P2 J4 100 S5 P3 J1 200 S5 P6 J2 200 S5 P6 J4 500 实验步骤：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 n

6、ull 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 COURSE(Cno char(2

7、)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 VALUES(6,数据处理,2);INSERT

8、 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,92);INSERT INTO SC VALUES(950

9、01,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命令运用。向STUDENT表增加“入学时间”列，其数据类型为日期型，用SELECT命令

10、查看表中内容。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;重复(1)(4)过程，建立数据库备以后的查询使用。2创建供应系统“GYX

11、T”数据库。(注：可采用可视窗体和 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,COLOR CHAR(2)DEFAULT(红),WEIGHT INT,);(4)建立工

12、程项目表 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(SNO)REFERENCES S(SNO),FOREIGN KEY(PNO)REFERENCES P(P

13、NO),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 VALUES(S5,南京,南京);*/INSERT INTO P VALUES(P1,螺母,红,12);INS

14、ERT 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,弹簧厂,天津);INSERT INTO J VALUES(J4,造船厂,天津);INSERT INT

15、O 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 VALUES(S2,P3,J1,400);INSERT INTO SPJ VALUES(S2,P3

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

17、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 查询分析器的使用方法，加深对标准 SQL 查询语句的理解。2熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验内

18、容：1基于实验一创建的教学管理 JXGL 数据库，实现数据的基本查询操作。2基于实验一创建的供应系统 GYXT 数据库，实现数据的基本查询操作。实验步骤：写出下列操作的 SQL 语句。1在教学管理 JXGL 数据库中进行如下操作：(1)求数学系学生的学号和姓名。USE JXGL SELECT Sno,Sname FROM STUDENT WHERE Sdept=MA;(2)求选修了课程的学生学号。USE JXGL SELECT distinct Sno FROM sc;(3)求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT Sno,Gr

19、ade FROM SC WHERE Cno=2 ORDER BY Grade DESC,Sno ASC;(4)求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数 0.8 输出。SELECT Sno,Grade=0.8*Grade FROM SC WHERE Cno=2 AND Grade BETWEEN 80 AND 90 ORDER BY Grade DESC,Sno ASC;(5)求数学系或计算机系姓刘的学生的信息。SELECT*FROM STUDENT WHERE Sname LIKE 刘%AND(Sdept=MAOR Sdept=IS)(6)求缺少了成绩的学生的学号

20、和课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL;(7)查询每个学生的情况以及他（她）所选修的课程。SELECT STUDENT.Sno,Sname Ssex,Sage,Sdept,SC.Cno,Grade,Cname FROM STUDENT,SC,COURSE WHERE STUDENT.Sno=SC.Sno AND COURSE.Cno=SC.Cno (8)求学生的学号、姓名、选修的课程名及成绩。SELECT STUDENT.Sno,Sname,Grade,Cname FROM STUDENT,SC,COURSE WHERE STUDENT.

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

22、COURSE t3 WHERE t1.Cpno=t2.Cno and t2.Cpno=t3.Cno 2在供应系统 GYXT 数据库中进行如下操作：(1)求供应工程 J1 零件的供应商号 SNO。USE GYXT SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1;(2)求供应工程 J1 零件 P1 的供应商号SNO。USE GYXT SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1AND PNO=P1;(3)统计每种零件的供应总量。USE GYXT SELECT SPJ.PNO,PNAME,COUNT(*)FROM SPJ,P

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

24、NT.Sno,Sname FROM STUDENT,SC WHERE STUDENT.Sno=SC.Sno AND Cno=2;(2)求数学课程成绩高于李勇的学生学号和成绩。SELECT Sname,STUDENT.Sno from SC,STUDENT WHERE Grade(SELECT Gradefrom SC WHERE Sno=95001 and Cno=2)and Cno=2 and STUDENT.Sno=SC.Sno;(3)求其他系中年龄小于计算机系年龄最大者的学生。SELECT Sname,STUDENT.Snofrom STUDENT WHERE Sage(SELECT M

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

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

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

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

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

30、EATE TABLE CITY (CityNo 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

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

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

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

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

35、管理器方法：在企业管理器的表设计器中，可以删除主键、去掉默认值。单击鼠标右键，分别选择“索引/键”、“关系”、“CHECK”命令，可以删除各类约束。查询分析器方法：删除约束的 SQL 语句格式为：ALTER TABLE DROP CONSTRAINT；例如，删除上例在 JXGL 数据库中为 SC 表的“Sno”列创建的外键约束。ALTER TABLE SC DROP CONSTRAINT c5;2创建人事关系 RSGX 数据库，并定义职工和部门两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码；要求在模式中完成以

36、下完整性约束条件的定义：(1)定义每个模式的主码；(2)定义参照完整性；(3)定义职工年龄不得超过 60 岁。CREATE TABLE RSGX 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 r

37、eferences department(Dno),check(Eage=60),primary key(Eno)附录一：实验课考核办法 上机实验是数据库系统原理这门课程的重要环节。通过基于关系型网络数据库管理系统 SQL Server 的上机实验，使学生进一步了解关系数据模型及关系数据库管理系统的基本原理和标准的 SQL 语言的使用。本课程的实验环境：服务器使用 WINDOWS 2000 SERVER 操作系统，并安装 SQL Server 2000标 准 版 数 据 库 软 件；客 户 端 操 作 系 统 为WINDOWS。实验方式和基本要求：实验一人一组，要求学生在规定时间内独立完成操作

38、，并在实验结束后提交实验报告。结合实验纪律、出勤情况、实验操作、综合实验报告，分五级给出成绩，评分标准如下：优秀：出勤满，实验纪律好，听从指导老师指挥，动手能力强，综合试验数据处理正确，报告分析思路清晰，字迹工整。良好：出勤满，实验纪律好，听从指导老师指挥，动手能力较强，综合试验数据处理正确，报告分析思路清晰，字迹较工整。中等：出勤满，实验纪律好，听从指导老师指挥，动手能力较强，综合试验数据处理较正确，报告分析思路较清晰，字迹基本工整。及格：只有少数特殊情况下没有出勤，实验纪律一般，听从指导老师指挥，动手能力一般，综合试验数据处理基本正确，报告分析思路一般。不及格：经常不参加实验，实验纪律不好；不听从指导老师指挥；动手能力较差；综合试验数据处理不正确；不写实验报告。