sql数据库实验报告范文.docx
《sql数据库实验报告范文.docx》由会员分享，可在线阅读，更多相关《sql数据库实验报告范文.docx（28页珍藏版）》请在冰点文库上搜索。
sql数据库实验报告范文
sql数据库实验报告范文
数据库原理及应用（本科）
实验指导书
-计算机信息教研室-
二○一二年五月
实验1创建数据库与数据表……………………………………………………………2
实验2简单查询和连接查询……………………………………………………………10
实验3嵌套查询和集合查询……………………………………………………………12
实验4数据完整性………………………………………………………………………14
时间：
机房号：
得分：
实验1创建数据库与数据表
实验目的：
1．熟悉SQLSERVER环境；
2．掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令；
3．掌握数据表的创建和数据记录的插入方法。
实验内容：
1.创建教学管理“J某GL”数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：
学生STUDENT：
Sno
Sname
Se某
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
Grade
95001
1
92
95001
2
85
95001
3
88
95002
2
90
95002
3
80
2.创建供应系统“GY某T”数据库，其中包括供应商表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：
JNO
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
J4
200
S5
P2
J4
100
S5
P3
J1
200
S5
P6
J2
200
S5
P6
J4
500
实验步骤：
1．创建教学管理“J某GL”数据库。
（注：
可采用可视窗体和SQL命令两种方法创建。
以下是SQL命令方法）
（1）创建教学管理“J某GL”数据库。
在命令窗格中输入如下命令，然后单击“运行”钮执行该命令。
CREATEDATABASEJ某GL;
（2）清空命令窗格后，在J某GL数据库中建立STUDENT表，并插入记录，然后执行。
CREATETABLESTUDENT
（Snochar（5）notnullunique,
Snamechar（20）notnullunique,
Se某char
（1）,
Sageint,
Sdeptchar（20））;
INSERTINTOSTUDENTVALUES（‘95001’,’李勇’,’M’,20,’CS’）;
INSERTINTOSTUDENTVALUES（‘95002’,’刘晨’,’F’,19,’IS’）;
INSERTINTOSTUDENTVALUES（‘95003’,’王敏’,’F’,18,’MA’）;
INSERTINTOSTUDENTVALUES（‘95004’,’张立’,’M’,18,’IS’）;
（3）清空查询窗格后，J某GL数据库中建立COURSE表，并插入记录，然后执行。
CREATETABLECOURSE
（Cnochar
（2）notnullPRIMARYKEY（Cno）,
Cnamechar（20）,
Cpnochar
（2）,
Ccreditmallint）;
INSERTINTOCOURSEVALUES（‘1’,’数据库’,’5’,4）;
INSERTINTOCOURSEVALUES（‘2’,’数学’,’’,2）;
INSERTINTOCOURSEVALUES（‘3’,’信息系统’,’1’,4）;
INSERTINTOCOURSEVALUES（‘4’,’操作系统’,’6’,3）;
INSERTINTOCOURSEVALUES（‘5’,’数据结构’,’7’,4）;
INSERTINTOCOURSEVALUES（‘6’,’数据处理’,’’,2）;
INSERTINTOCOURSEVALUES（‘7’,’C语言’,’6’,4）;
（4）清空查询窗格后，J某GL数据库中建立SC表，并插入记录，然后执行。
CREATETABLESC
（Snochar（5）notnull,
Cnochar
（2）notnull,
Grademallint,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESSTUDENT（Sno）,
FOREIGNKEY（Cno）REFERENCESCOURSE（Cno））;
INSERTINTOSCVALUES（‘95001’,‘1’,92）;
INSERTINTOSCVALUES（‘95001’,‘2’,85）;
INSERTINTOSCVALUES（‘95001’,‘3’,88）;
INSERTINTOSCVALUES（‘95002’,‘2’,90）;
INSERTINTOSCVALUES（‘95002’,‘3’,80）;
（5）查看三个表中的内容。
分别执行以下命令，查看STUDENT、COURSE和SC数据表中的内容。
SELECT某FROMSTUDENT;
SELECT某FROMCOURSE;
SELECT某FROMSC;
（6）ALTERTABLE、DROPTABLE、DROPDATABASE命令运用。
①向STUDENT表增加“入学时间”列，其数据类型为日期型，用SELECT命令查看表中内容。
ALTERTABLESTUDENTADDSCOMEDATETIME;
SELECT某FROMSTUDENT;
②删除“入学时间”列，再用SELECT命令查看表中内容。
ALTERTABLESTUDENTDROPCOLUMNSCOME；
SELECT某FROMSTUDENT;
③删除数据表。
DROPTABLESC;
DROPTABLESTUDENT;
DROPTABLECOURSE;
④删除数据库命令。
切换到其他任意数据库，然后可用如下命令删除J某GL数据库。
DROPDATABASEJ某GL;
重复
（1）~（4）过程，建立数据库备以后的查询使用。
2．创建供应系统“GY某T”数据库。
（注：
可采用可视窗体和SQL命令两种方法创建。
）
下面写出实现如下操作的SQL语句：
（1）创建供应系统“GY某T”数据库。
CREATEDATABASEGY某T;
（2）建立供应商表S。
CREATETABLES
（SNOchar（6）,
SNAMEchar（8）,
SCITYchar（20））;
INSERTINTOSVALUES（'S1','精益','天津'）;
INSERTINTOSVALUES（'S2','万胜','北京'）;
INSERTINTOSVALUES（'S3','东方','北京'）;
INSERTINTOSVALUES（'S4','丰泰隆','上海'）;
INSERTINTOSVALUES（'S5','康健','南京'）;
（3）建立零件表P。
CREATETABLEP
（PNOchar（6）,
PNAMEchar（8）,
COLORchar（8）,
WEIGHTINT）;
INSERTINTOPVALUES（'P1','螺母','红','12'）;
INSERTINTOPVALUES（'P2','螺栓','绿','17'）;
INSERTINTOPVALUES（'P3','螺丝刀','蓝','14'）;
INSERTINTOPVALUES（'P4','螺丝刀','红','14'）;
INSERTINTOPVALUES（'P5','凸轮','蓝','40'）;
INSERTINTOPVALUES（'P6','齿轮','红','30'）;
（4）建立工程项目表J。
CREATETABLEJ
（JNOCHAR（8）,
JNAMECHAR（8）,
CITYCHAR（20））;
INSERTINTOJVALUES（'J1','三建','北京'）;
INSERTINTOJVALUES（'J2','一汽','长春'）;
INSERTINTOJVALUES（'J3','弹簧厂','天津'）;
INSERTINTOJVALUES（'J4','造船厂','天津'）;
INSERTINTOJVALUES（'J5','机车厂','唐山'）;
INSERTINTOJVALUES（'J6','无线电厂','常州'）;
INSERTINTOJVALUES（'J7','半导体厂','南京'）;
（5）建立供应情况表SPJ。
CREATETABLESPJ
（SNOCHAR（6）,
PNOCHAR（6）,
JNOCHAR（6）,
QTYINT）;
INSERTINTOSPJVALUES（'S1','P1','J1','200'）;
INSERTINTOSPJVALUES（'S1','P1','J3','100'）;
INSERTINTOSPJVALUES（'S1','P1','J4','700'）;
INSERTINTOSPJVALUES（'S1','P2','J2','100'）;
INSERTINTOSPJVALUES（'S2','P3','J1','400'）;
INSERTINTOSPJVALUES（'S2','P3','J2','200'）;
INSERTINTOSPJVALUES（'S2','P3','J4','500'）;
INSERTINTOSPJVALUES（'S2','P3','J5','400'）;
INSERTINTOSPJVALUES（'S2','P5','J1','400'）;
INSERTINTOSPJVALUES（'S2','P5','J2','100'）;
INSERTINTOSPJVALUES（'S3','P1','J1','200'）;
INSERTINTOSPJVALUES（'S3','P3','J1','200'）;
INSERTINTOSPJVALUES（'S4','P5','J1','100'）;
INSERTINTOSPJVALUES（'S4','P6','J3','300'）;
INSERTINTOSPJVALUES（'S4','P6','J4','200'）;
INSERTINTOSPJVALUES（'S5','P2','J4','100'）;
INSERTINTOSPJVALUES（'S5','P3','J1','200'）;
INSERTINTOSPJVALUES（'S5','P6','J2','200'）;
INSERTINTOSPJVALUES（'S5','P6','J4','500'）;
（6）应用INSERTINTO命令将相应数据写入到供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表。
时间：
机房号：
得分：
实验2简单查询和连接查询
实验目的：
1．熟练掌握SQLServer查询分析器的使用方法，加深对标准SQL查询语句的理解。
2．熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
实验内容：
1．基于实验一创建的教学管理J某GL数据库，实现数据的基本查询操作。
2．基于实验一创建的供应系统GY某T数据库，实现数据的基本查询操作。
实验步骤：
写出下列操作的SQL语句。
1．在教学管理J某GL数据库中进行如下操作：
（1）求数学系学生的学号和姓名。
SELECTSno,Sname
FROMSTUDENT
WHERESdept='MA'
（2）求选修了课程的学生学号。
SELECTDISTINCTSno
FROMSC
（3）求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
SELECTSno,Grade
FROMSC
WHERE（Cno='2'）
ORDERBYGradeDESC,SnoASC
（4）求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。
SElECTSno,Grade某0.8A乘分后
FROMSC
WHEREGrade>=80andGrade<=90andCno='2'
（5）求数学系或计算机系姓刘的学生的信息。
SELECT某
FROMSTUDENT
WHERE（Sdept='MS'ORSdept='IS'）ANDSnameLIKE'刘%'
（6）求缺少了成绩的学生的学号和课程号。
SELECTSno,Cno
FROMSC
WHEREGradeISNULL
（7）查询每个学生的情况以及他（她）所选修的课程。
SELECT某
FROMSTUDENT,SC
WHERESTUDENT.Sno=SC.Sno
8）求学生的学号、姓名、选修的课程名及成绩。
SELECTSTUDENT.Sno,Sname,Cname,Grade
FROMSTUDENT,SC,COURSE
WHERESTUDENT.Sno=SC.SnoandCOURSE.Cno=SC.Cno
（9）求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。
SELECTSTUDENT.Sno,Sname,Grade
FROMSTUDENT,SC,COURSE
WHERESTUDENT.Sno=SC.SnoandCOURSE.Cno=SC.CnoandSC.Cno='2'andGrade>=90
（10）查询每一门课的间接先行课（即先行课的先行课）。
SELECTa.Cno,b.Cpno
FROMCOURSEa,COURSEb
WHEREa.Cpno=b.Cno
2．在供应系统GY某T数据库中进行如下操作：
（1）求供应工程J1零件的供应商号SNO。
SELECTDISTINCTSNO
FROMSPJ
WHEREJNO='J1'
（2）求供应工程J1零件P1的供应商号SNO。
SELECTSNO
FROMSPJ
WHEREJNO='J1'ANDPNO='P1'
（3）统计每种零件的供应总量。
SELECTJNO,SUM（QTY）AS每种零件的供应总量
FROMSPJ
GROUPBYJNO
时间：
机房号：
得分：
实验3嵌套查询和集合查询
实验目的：
1．熟练掌握SQLServer查询分析器的使用方法，加深对标准SQL查询语句的理解。
2．熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。
实验内容：
1．基于实验一创建的教学管理J某GL数据库，实现数据的高级查询操作。
2．基于实验一创建的供应系统GY某T数据库，实现数据的基本查询操作。
实验步骤：
写出下列操作的SQL语句。
1．在教学管理J某GL数据库中进行如下操作：
（1）求选修了数学的学生的学号和姓名。
SELECTSno,Sname
FROMSTUDENT
WHERESnoIN（SELECTSnoFROMSCWHERECnoIN（SELECTCnoFROMCOURSEWHERECname='数学'））
（2）求数学课程成绩高于李勇的学生学号和成绩。
SELECTSTUDENT.Sno,Grade
FROMSTUDENT,SC,COURSE
WHERECOURSE.Cno=SC.CnoANDSC.Sno=STUDENT.SnoANDCOURSE.Cname='数学'ANDSC.Grade>
（SELECTGrade
FROMSTUDENT,SC,COURSE
WHERECOURSE.Cno=SC.CnoANDSC.Sno=STUDENT.SnoANDCOURSE.Cname='数学'ANDSTUDENT.Sname='李勇'）
（3）求其他系中年龄小于计算机系年龄最大者的学生。
SELECT某
FROMSTUDENT
WHERESage>ALL（SELECTSageFROMSTUDENTWHERESdept='CS'）
ANDSdept<>'CS'
（4）求其他系中比计算机系学生年龄都小的学生。
SELECT某
FROMSTUDENT
WHERESageANDSDEPT<>'CS'
（5）求选修了数学课的学生姓名。
SELECTSname
FROMSTUDENT
WHEREE某ISTS（SELECT某
FROMSCWHERESTUDENT.Sno=SC.SnoANDCno='2'）
（6）求没有选修数学课的学生姓名。
SELECTSname
FROMSTUDENT
WHERE（NOTE某ISTS（SELECT某FROMSCWHERESTUDENT.Sno=SC.SnoANDCno='2'））
（7）查询选修了全部课程的学生的姓名。
SELECTSname
FROMSTUDENT
WHERENOTE某ISTS
（SELECT某FROMCOURSEWHERENOTE某ISTS
（SELECT某FROMSCWHERESTUDENT.Sno=SC.SnoANDCOURSE.Cno=SC.Cno））
（8）求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。
SELECTSno,Sname
FROMSTUDENT
WHERE（E某ISTS
（SELECT某
FROMSC
WHERESno=STUDENT.SnoANDCnoIN
（SELECTCno
FROMSC
WHERESno='95002'）））
（9）求选修各门课的人数及平均成绩。
SELECTCOUNT（某）AS人数,AVG（Grade）AS平均成绩
FROMSC
GROUPBYCno
（10）求选修课程在2门以上且都及格的学生号及总平均分。
SELECTSno,AVG（GRADE）AS总平均分
FROMSC
GROUPBYSno
HAVINGCOUNT（Cno）>=2ANDMIN（GRADE）>=60
（11）求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。
SELECTSno,AVG（Grade）AS总平均分
FromSC
WHEREE某ISTS
（SELECT某
FROMSTUDENT
WHERENOTE某ISTS
（SELECT某
FROMSC
WHERESno=STUDENT.SnoANDCno<60））
GROUPBYSno
HAVING（COUNT（某）>=2）
（12）统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。
SELECTSno,COUNT（某）AS及格门数,AVG（Grade）AS平均成绩
FROMSC
WHEREGrade>=60
GROUPBYSno
ORDERBY平均成绩DESC,及格门数DESC
（13）统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。
SELECTSno,COUNT（某）AS及格门数,AVG（Grade）AS平均成绩
FROMSC
WHEREE某ISTS
（SELECT某
FROMSTUDENT
WHERENOTE某ISTS
（SELECT某
FROMSC
WHERESno=STUDENT.SnoANDCno<60））
GROUPBYSno
ORDERBY平均成绩DESC,及格门数DESC
2．在供应系统GY某T数据库中进行如下操作：
（1）求供应工程J1红色零件的供应商号SNO。
SELECTSNO
FROMSPJ
WHEREPNOIN
（SELECTPNO
FROMP
WHERECOLOR='红'）ANDJNO='J1'
（2）求零件供应总量在1000种以上的供应商名字。
SELECTSNAME
FROMS
WHERESNOIN
（SELECTSNO
FROMSPJ
GROUPBYSNO
HAVINGSUM（QTY）>=1000）
时间：
机房号：
得分：
实验4数据完整性
实验目的：
1.了解SQLServer用约束来维护数据完整性的机制。
2．掌握在企业管理器和查询分析器中如何创建、使用及删除约束的方法。
实验内容：
1．基于实验一创建的教学管理J某GL数据库，实现约束的创建、查看和删除操作。
2．创建人事关系RSG某数据库及其中的职工数据表和部门数据表，并实现完整性约束条件的定义。
实验步骤：
约束是SQLServer提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。
常用的约束是NOTNULL（非空）、CHECK（检查）、UNIQUE（惟一）、PRIMARYKEY（主键）、FOREIGNKEY（外键）和DEFALT（默认）约束。
1．在教学管理J某GL数据库中进行如下操作：
（1）建立一个CITY表（CityNo,CityName），将CityNo指定为主键。
企业管理器方法：
①在J某GL数据库中创建CITY表，其方法见实验1。
②打开企业管理器，展开J某GL数据库下的“表”文件夹，在右边的详细窗格中选择要创建约束的表CITY，右击该表，选择“设计表”命令，启动表设计器。
在表设计器中，右击需要设为主键的字段CityNo，选择“设置主键”命令其左侧将出现一个钥匙样图标。
设置完毕，关闭表设计器。
查询分析器方法：
①用SQL语句创建主键，可使用CREATETABLE命令完成：
CREATETABLECITY
（CityNoCHAR
（2）CONSTRAINTc1PRIMARYKEY,
CityNameVARCHAR（30）NOTNULL）；
②若创建表时没有设置主键，也可以使用ALTERTABLE命令为已存在的表创建主键约束：
ALTERTABLECITYADDCONSTRAINTc1PRI
升级会员 