数据库原理及应用实验指导.docx

1、数据库原理及应用实验指导试验报告报告人学号 班级 姓名 1、时间2、地点3、试验项目及内容4、学生本人编写的SQL 代码（为减少同学书写量或打印费用，只要求报告中包含章节号及SQL 代码，实验指导书内容不必在实验报告中出现）数据库原理及应用（应用本科）实验指导书-计算机信息教研室-桂林工学院电子与计算机系二六年九月目 录实验1 创建数据库与数据表2实验2 简单查询和连接查询10实验3 嵌套查询和集合查询12实验4 数据完整性14时间： 机房号： 得分： 实验1 创建数据库与数据表实验目的：1熟悉SQL SERVER环境，了解企业管理器和查询分析器的基本使用方法；2掌握数据库和数据表的创建和删除

2、，实践SQL的CREATE、ALTER和DROP命令；3掌握数据表的创建和数据记录的插入方法。实验内容：1. 创建教学管理“JXGL”数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：学生STUDENT：SnoSnameSsexSageSdept95001李勇M20CS计算机系95002刘晨F19IS信息系95003王敏F18MA数学系95004张立M18IS信息系课程COURSE：CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64选修SC：SnoCnoGrade9500119

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

4、S1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500实验步骤：1用企业管理器创建教学管理“JXGL”数据库。(1) 连接“服务器管理器”。选择“开始/程序/Microsoft SQL Server/服务管理器”，进入SQL服务管理器窗口，如图所示。选择要启动的服务器和准备启动的服务，单击“开始/继续”按钮，开

5、始启动服务，稍后绿灯亮，启动成功。(2) 打开“企业管理器”。选择“开始/程序/Microsoft SQL Server/企业管理器”，就出现企业管理器窗口。企业管理器是SLQ Server的一个最重要的管理工具，它提供了一个遵从Microsoft管理控制台（MMC）的用户界面，以层叠形式显示出所有SQL Server对象，使用企业管理器几乎可以完成所有的管理工作。在控制台根目录窗口中展开“控制台根目录”树，依次展开“Microsoft SQL Servers/SQL Server组/(Local)(Windows NT)/数据库”文件夹，如图所示。 图 SQL服务管理器 图 企业管理器(3)

6、 创建教学管理“JXGL”数据库。选择“操作”菜单中的“新建数据库”命令，进入“数据库属性窗口”，在“常规”标签“名称”框中输入“JXGL”，其他选项采用默认值，如图所示。单击“确定”按钮，系统自动创建JXGL数据库。(4) 在JXGL数据库中建立STUDENT表，并插入学生记录。在控制台根目录窗口中展开“数据库”文件夹，就会看到新建立的数据库“JXGL”，如图所示，鼠标右击“表”图标，在弹出的快捷菜单中选择“新建表”命令，启动“表设计器”，在表设计器窗口上部网格中，每一行描述了表中的一个字段，每一行有四列，分别描述了列名、数据类型、长度和允许空等属性，将STUDENT表的各列属性依次输入到网

7、格中去，如图所示。 图 创建数据库窗口 图“新建表”命令 图表设计器 图 设置主键 然后，可以右击列名Sno，选择“设置主键”命令，其左侧将出现一个钥匙样图标，如图所示。单击表设计器的工具栏中的“保存”按钮，出现保存对话框，输入表名“STUDENT”，如图所示，并按“确定”按钮。图 保存对话框 在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“打开表/返回所有行”命令，如图所示。然后在出现的表格中依次输入新记录，如图所示。图 “返回所有行”命令图 输入数据(5) 同样方法，可以在JXGL数据库中建立COURSE表，并插入记录。(6) 同样方法，可以

8、在JXGL数据库中建立SC表，并插入记录。(7) 查看三个表中的内容。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“打开表/返回所有行”命令，如图所示，即可查年STUDENT表中的内容。同样方法，可以查看COURSE表和SC表中的内容。(8) 修改数据表结构。向STUDENT表增加“入学时间”列，其数据类型为日期型。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“设计表”命令，即进入表设计器窗口，如图所示，即可继续增加“入学时间”列，数据类型选择为“datetime”型。单击工具栏中的“保存”按钮

9、。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“打开表/返回所有行”命令，如图所示，即可查年STUDENT表中的内容，此时Scome列值都为NULL。删除“入学时间”列。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“设计表”命令，即进入表设计器窗口，右击上方网格中的“Scome”，选择为“删除列”命令。单击工具栏中的“保存”按钮。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“打开表/返回所有行”命令，如图所示，看到Scome列已被删除。(9) 删

10、除数据表。在企业管理器中，展开JXGL数据库节点，单击“表”图标，在右侧窗格中右击STUDENT表，选择“删除”命令，即可删除STUDENT表。同样方法，可以删除其他指定表。(10) 删除数据库在企业管理器中，右击JXGL数据库，选择“删除”命令，即可删除JXGL数据库。2用查询分析器创建教学管理“JXGL”数据库。(1) 连接“服务器管理器”。(2) 打开“查询分析器”。选择“开始/程序/Microsoft SQL Server/查询分析器”，就出现登录窗口，如图所示，在“SQL Server”栏中输入登录的服务器名称，这里的“.”表示是本地服务器。使用SQL Server身份验证模式，并在

11、登录名栏中输入用户名，然后在密码栏中输入密码，这里的用户名和密码都输入“sa”。最后单击“确定”按钮连接到数据库服务器，出现如图所示的查询分析器窗口。 图 登录窗口 图 查询分析器查询分析器界面如图所示，它由标题栏、菜单栏、工具栏、对象浏览窗格、查询窗格、查询结果窗格等组成。其中，查询窗格是编辑和执行SQL Server程序的地方，查询结果窗格是浏览查询结果的地方，结果可以“文本显示”和“表格显示”两种方式显示出来。(3) 创建教学管理“JXGL”数据库。在查询窗格中输入如下命令，然后单击“运行”钮执行该命令。CREATE DATABASE JXGL;(4) 清空查询窗格后，在JXGL数据库中

12、建立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 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 VAL

13、UES(95004,张立,M,18,IS);(5) 清空查询窗格后，JXGL数据库中建立COURSE表，并插入记录，然后执行。CREATE TABLE 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 V

14、ALUES(4,操作系统,6,3);INSERT INTO COURSE VALUES(5,数据结构,7,4);INSERT INTO COURSE VALUES(6,数据处理, ,2);INSERT INTO COURSE VALUES(7,C语言,6,4);(6) 清空查询窗格后，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(

15、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 INTO SC VALUES(95002,2,90);INSERT INTO SC VALUES(95002,3,80);(7) 查看三个表中的内容。分别执行以下命令，查看STUDENT、COURSE和SC数据表中的内容。SELECT * FROM STUDENT;SELECT * FROM

16、 COURSE;SELECT * FROM SC;(8) 修改数据表结构。向STUDENT表增加“入学时间”列，其数据类型为日期型，用SELECT命令查看表中内容。ALTER TABLE STUDENT ADD SCOME DATETIME;SELECT * FROM STUDENT;删除“入学时间”列，再用SELECT命令查看表中内容。ALTER TABLE STUDENT DROP COLUMN SCOME；SELECT * FROM STUDENT;(9) 删除数据表。DROP TABLE SC;DROP TABLE STUDENT;DROP TABLE COURSE;(10) 删除数据

17、库命令。切换到其他任意数据库，然后可用如下命令删除JXGL数据库。DROP DATABASE JXGL;3创建供应系统“GYXT”数据库。(注：可采用企业管理器和查询分析器两种方法创建。)下面写出实现如下操作的SQL语句：(1) 创建供应系统“GYXT”数据库。(2) 建立供应商表S。(3) 建立零件表P。(4) 建立工程项目表J。(5) 建立供应情况表SPJ。时间： 机房号： 得分： 实验2 简单查询和连接查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验内容：1基于实验一创建的

18、教学管理JXGL数据库，参考教材查询中的SQL操作实例，实现数据的基本查询操作。2基于实验一创建的供应系统GYXT数据库，参考教材查询中的SQL操作实例，实现数据的基本查询操作。实验步骤： 首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。1在教学管理JXGL数据库中进行如下操作：(1) 求数学系学生的学号和姓名。(2) 求选修了课程的学生学号。(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数输出。(5) 求数学

19、系或计算机系姓刘的学生的信息。(6) 求缺少了成绩的学生的学号和课程号。2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1零件的供应商号SNO。(2) 求供应工程J1零件P1的供应商号SNO。(3) 统计每种零件的供应总量。时间： 机房号： 得分： 实验3 嵌套查询和集合查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，参考教材查询中的SQL操作实例，实现数据的高级查询操作。2基于实验一创建的供应系统GYXT数据库，参考

20、教材查询中的SQL操作实例，实现数据的高级查询操作。实验步骤： 首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。1在教学管理JXGL数据库中进行如下操作：(1) 求选修了数学的学生的学号和姓名。(2) 求数学课程成绩高于李勇的学生学号和成绩。(3) 求其他系中年龄小于计算机系年龄最大者的学生。(4) 求其他系中比计算机系学生年龄都小的学生。(5) 求选修了数学课的学生姓名。(6) 求没有选修数学课的学生姓名。2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1红色零件的供应商号SNO。(2) 求零件供应总量在1000种以上的供应

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

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

23、BLE 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字段，选择“索引/键”命

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

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

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

27、键约束的SNO列，如图所示。单击“关闭”按钮，完成外键约束的创建。查询分析器方法：为已存在的表创建外键约束，SQL语句如下：ALTER TABLE SC ADD CONSTRAINT c5 FOREIGN KEY(Sno) REFERENCES STUDENT(Sno)；(6) 查看约束的定义在企业管理器中，选择要查看约束的表，打开表设计器，单击鼠标右键，分别选择“索引/键”、“关系”、“CHECK”命令，可以查看各类约束。(7) 删除约束企业管理器方法：在企业管理器的表设计器中，可以删除主键、去掉默认值。单击鼠标右键，分别选择“索引/键”、“关系”、“CHECK”命令，可以删除各类约束。查询分析器方法：删除约束的SQL语句格式为：ALTER TABLE DROP CONSTRAINT ；例如，删除上例在JXGL数据库中为SC表的“Sno”列创建的外键约束。ALTER TABLE SC DROP CONSTRAINT c5;2创建人事关系RSGX数据库，并定义职工和部门两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码；要求在模式中完成以下完整性约束条件的定义：(1) 定义每个模式的主码；(2) 定义参照完整性；(3) 定义职工年龄不得超过60岁。