工程硕士 数据库实验内容.docx
《工程硕士 数据库实验内容.docx》由会员分享，可在线阅读，更多相关《工程硕士 数据库实验内容.docx（25页珍藏版）》请在冰豆网上搜索。
工程硕士数据库实验内容
工程硕士数据库复习
实验1数据库系统基础操作
实验内容
选择一个常用的数据库产品，如MSSQLServer、Oracle、MySQL、DB2、Sybase、Informix或VisualFoxPro等，进行实际的安装操作，并记录安装过程。
安装过程:
1.|运行安装程序
2.接受最终用户许可协议.
3.安装sql2005所需的软件组件。
4.扫描计算机配置。
5.进入安装向导。
6.系统配置检查。
7.注册个人信息。
8.功能选择。
9.选择身份验证模式‘
10.设置错误和使用情况报告设置.
11.安装sql2005
12.完成安装
实验2数据库的基本操作
实验内容
通过数据库管理系统提供的管理界面程序交互式操作（记录交互式界面操作过程）或通过命令创建数据库JXGL。
使用T-sql语句创建数据库
CREATEDATABASEJXGL
OnPrimary
（NAME=‘JXGL’
FILENAME=‘C:
\JXGL.MDF’
SIZE=10240KB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
LOGON
（NAME=‘JXGL_LOG’
FILENAME=‘C:
\JXGL_LOG.LDG,
SIZE=1024KB,
MAXSIZE=5120KB,
FILEGROWTH=1024KB
）
图形方式
实验3表与视图的基本操作
实验内容
1、创建数据库及表
用掌握的某种方法，创建订报管理子系统的数据库DingBao，在DingBao数据库中用交互式界面操作方法或CREATETABLE命令创建如下三表的表结构（表名及字段名使用括号中给出的英文名），并完成三表所示内容的输入，根据需要可自行设计输入更多的表记录。
创建表结构时要求满足：
（1）报纸编码表（PAPER）以报纸编号（pno）为主键；
（2）顾客编码表（CUSTOMER）以顾客编号（cno）为主键；
（3）报纸订阅表（CP）以报子编号（pno）与顾客编号（cno）为主键，订阅份数（num）的缺省值为1。
10.3报纸编码表（PAPER）10.4报纸订阅表（CP）
报纸编号（pno）
报纸名称（pna）
单价（ppr）
000001
人民日报
12.5
000002
解放军报
14.5
000003
光明日报
10.5
000004
青年报
11.5
000005
扬子晚报
18.5
顾客编号（cno）
报纸编号（pno）
订阅份数（num）
0001
000001
2
0001
000002
4
0001
000005
6
0002
000001
2
0002
000003
2
0002
000005
2
0003
000003
2
0003
000004
4
0004
000001
1
0004
000003
3
0004
000005
2
0005
000003
4
0005
000002
1
0005
000004
3
0005
000005
5
0005
000001
4
10.5顾客编码表（CUSTOMER）
顾客编号（cno）
顾客姓名（cna）
顾客地址（adr）
0001
李涛
无锡市解放东路123号
0002
钱金浩
无锡市人民西路234号
0003
邓杰
无锡市惠河路270号
0004
朱海红
无锡市中山东路432号
0005
欧阳阳文
无锡市中山东路532号
使用T-sql语句创建数据库
方式1：
Createdatabasejxgl
方式2
CREATEDATABASEDingBao
OnPrimary
（NAME=‘DingBao’
FILENAME=‘C:
\DingBao.MDF’
SIZE=10240KB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
LOGON
（NAME=‘JXGL_LOG’
FILENAME=‘C:
\DingBao_LOG.LDG,
SIZE=1024KB,
MAXSIZE=5120KB,
FILEGROWTH=1024KB
）
使用T-sql语句创建数据库
Createtablepaper（PnoINTPRIMARYKEY，
Pnavarchar（20）notnull,
PPRINTnotnullcheck（PPR>0））
CreatetableCP（CNOINTPRIMARYKEY,
PNOINTPRIMARYKEY,
NUMINTdefault
（1））
CreatetableCUSTOMER（CNOINTPRIMARYKEY,
CANVARCHAR（20）NOTNULL,
ADRVARCHAR（20）NOTNULL）
图形方式：
创建一个ACCESS数据库DingBao（DingBao.MDB文件），把你创建的三表导出到ACCESS数据库中。
点击下一步，系统将自动完成数据导出工作
2、创建与使用视图
（1）在DingBao数据库中,创建含有顾客编号、顾客名称、报纸编号、报纸名称、订阅份数等信息的视图，视图名设定为C_P_N。
CreateviewC_P_N（CNO,CNA,PNO,PNA,NUM）asselecto,customer.CNA,paper.pno,paper.pna,cp.numfrompaper,cp,customerwherepaper.pno=cp.pnoando=o
（2）修改已创建的视图C_P_N，使其含报纸单价信息。
AlterviewC_P_N（CNO,CNA,PNO,PNA,NUM,PPR）asselecto,customer.CNA,paper.pno,paper.pna,cp.num,paper.pprfrompaper,cp,customerwherepaper.pno=cp.pnoando=o
（3）通过视图C_P_N，查询“人民日报”被订阅的情况，能通过视图C_P_N实现对数据的更新操作吗？
请尝试各种更新操作，例如修改某人订阅某报的份数，修改某报的名称等。
查询：
SelectCNO,CNA,PNO,PNA,NUM,PPRfromC_P_Nwherepna='人民日报'
更新：
将“人民日报”修改成“新民晚报”
UPDATEC_P_NSETpna='新民晚报'wherepna='人民日报'
（4）删除视图C_P_N
DROPVIEWC_P_N
实验4SQL语言——SELECT查询操作
实验内容
下面实验中要使用到包括如下三个表的简易“教学管理”数据库jxgl：
（1）学生表Student，由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，记作：
Student（Sno,Sname,Ssex,Sage,Sdept），其中主码为Sno。
（2）课程表Course，由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，记作：
Course（Cno,Cname,Cpno,Ccredit），其中主码为Cno。
（3）学生选课SC，由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，记作：
SC（Sno,Cno,Grade），其中主码为（SNO,CNO）。
首先，先创建数据库jxgl，然后创建Student、SC、Course三表，添加表记录。
参阅MSSQLServer中的创建命令如下：
USEjxgl
CreateTableStudent
（SnoCHAR（5）NOTNULLPRIMARYKEY（Sno）,
SnameVARCHAR（20）,
SageSMALLINTCHECK（Sage>=15ANDSage<=45）,
SsexCHAR
（2）DEFAULT'男'CHECK（Ssex='男'ORSsex='女'）,
SdeptCHAR
（2））;
CreateTableCourse（CnoCHAR
（2）NOTNULLPRIMARYKEY（Cno）,CnameVARCHAR（20）,CpnoCHAR
（2）,CcreditSMALLINT）;
CreateTableSC（SnoCHAR（5）NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESStudent（Sno）,CnoCHAR
（2）NOTNULL,GradeSMALLINTCHECK（（GradeISNULL）OR（GradeBETWEEN0AND100））,PRIMARYKEY（Sno,Cno）,CONSTRAINTC_FFOREIGNKEY（Cno）REFERENCESCourse（Cno））;
INSERTINTOStudentVALUES（'98001','钱横',18,'男','CS'）;
INSERTINTOStudentVALUES（'98002','王林',19,'女','CS'）;
INSERTINTOStudentVALUES（'98003','李民',20,'男','IS'）;
INSERTINTOStudentVALUES（'98004','赵三',16,'女','MA'）;
INSERTINTOCourseVALUES（'1','数据库系统','5',4）;
INSERTINTOCourseVALUES（'2','数学分析',null,2）;
INSERTINTOCourseVALUES（'3','信息系统导论','1',3）;
INSERTINTOCourseVALUES（'4','操作系统原理','6',3）;
INSERTINTOCourseVALUES（'5','数据结构','7',4）;
INSERTINTOCourseVALUES（'6','数据处理基础',null,4）;
INSERTINTOCourseVALUES（'7','C语言','6',3）;
INSERTINTOSCVALUES（'98001','1',87）;
INSERTINTOSCVALUES（'98001','2',67）;
INSERTINTOSCVALUES（'98001','3',90）;
INSERTINTOSCVALUES（'98002','2',95）;
INSERTINTOSCVALUES（'98002','3',88）;
请有选择地实践以下各大题：
1、基于“教学管理”数据库jxgl，试用SQL的查询语句表达下列查询：
（1）检索年龄大于23岁的男学生的学号和姓名。
SELECTSno,SnameFROMStudentWHERE（Sage>23）AND（Ssex='男'）
（2）检索至少选修一门课程的女学生姓名。
selecta.Snamefromstudentasa,scasb
wherea.Ssex='女'anda.Sno=b.Snogroupbya.Snamehavingcount（b.Cno）>=1
（3）检索王同学不学的课程的课程号。
SelectCnofromCoursewhereCnonotin（selectCnofromSCwhereSnoin（selectSnofromstudentwhereSnamelike'王%'））
（4）检索至少选修两门课程的学生学号。
selectSnofromscgroupbySnohavingcount（*）>=2
（5）检索全部学生都选修的课程的课程号与课程名。
selectCno,CnamefromCoursewherenotexists（select*fromStudentwherenotexists（select*fromscwheresc.Sno=Student.Snoandsc.Cno=Course.Cno））
（6）检索选修了所有3学分课程的学生学号。
selectSnofromscwhereCnoin（selectCnofromCoursewhereCcredit=3）
2、基于“教学管理”数据库jxgl，试用SQL的查询语句表达下列查询：
（1）统计有学生选修的课程门数。
SELECTCOUNT（DISTINCTCno）FROMSC
（2）求选修4号课程的学生的平均年龄。
SELECTAVG（a.Sage）fromStudentasa,Scasbwherea.Sno=b.Snoandb.Cno=4
（3）求学分为3的每门课程的学生平均成绩。
SELECTAVG（a.Grade）fromScasa,Courseasbwherea.Cno=b.Cnoandb.Ccredit=3
（4）统计每门课程的学生选修人数，超过3人的课程才统计。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECTCno,Count（*）as选修人数fromscgroupbycnohavingcount（*）>3orderby选修人数DESC,cnoASC
（5）检索学号比王非同学大，而年龄比他小的学生姓名。
SELECTSnamefromstudentwhereSno>（selectsnofromstudentwhereSname='王非'）andSage<（selectSagefromstudentwhereSname='王非'）
（6）检索姓名以王打头的所有学生的姓名和年龄。
SELECTSname,Sagefromstudentwheresnamelike'王%'
（7）在SC中检索成绩为空值的学生学号和课程号。
SELECTSno,CnofromScwhereGradeisNULL
（8）求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECTSname,SagefromStudentwhereSage>（Selectavg（Sage）fromStudentwhereSsex='女'）andSsex='男'
（9）求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECTSname,SagefromStudentwhereSage>（SelectMax（Sage）fromStudentwhereSsex='女'）andSsex='男'
（10）检索所有比“王华”年龄大的学生姓名、年龄和性别。
SELECTSname,Sage,SsexfromStudentwhereSage>（SelectSagefromStudentwhereSname='王华'）
（11）检索选修“2”课程的学生中成绩最高的学生的学号。
SELECTSnofromscwhereCno=2andGrade=（selectMax（Grade）fromscwhereCno=2）
（12）检索学生姓名及其所选修课程的课程号和成绩。
SELECTa.Sname,b.Cno,b.Gradefromstudentasa,scasbwherea.Sno=b.Sno
（13）检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
SELECTSUM（Grade）fromscwhereGrade>=60and
cnoin（selectcnofromscgroupbycnohavingcount（*）>4）
orderbySUM（Grade）DESC
实验5SQL语言——更新操作命令
实验内容
1、学生表Student、课程表Course、选课表SC的表结构等信息同上实验4，请实践以下命令式更新操作：
（1）在学生表Student和学生选课表SC中分别添加如下两表中的记录。
InsertintoStudentvalues（'98010','赵青江',18,'男','CS'）
InsertintoStudentvalues（'98011','张丽萍',19,'女','CH'）
InsertintoStudentvalues（'98012','陈景欢',20,'男','IS'）
InsertintoStudentvalues（'98013','陈婷婷',16,'女','PH'）
InsertintoStudentvalues（'98014','李军',16,'女','EH'）
InsertintoSCvalues（'98010','1',87）
InsertintoSCvalues（'98010','2',null）或InsertintoSC（sno,cno）values（'98010','2'）
InsertintoSCvalues（'98010','3',80）
InsertintoSCvalues（'98010','4',87）
InsertintoSCvalues（'98010','6',85）
InsertintoSCvalues（'98011','1',52）
InsertintoSCvalues（'98011','2',47）
InsertintoSCvalues（'98011','3',53）
InsertintoSCvalues（'98011','5',45）
InsertintoSCvalues（'98012','1',84）
InsertintoSCvalues（'98012','3',null）或InsertintoSC（sno,cno）values（'98012','3'）
InsertintoSCvalues（'98012','4',67）
InsertintoSCvalues（'98012','5',81）
（2）备份Student表到TS中，并清空TS表。
Select*intoTSfromStudent
DeletefromTS或truncatetableTS
（3）给IS系的学生开设7号课程，建立所有相应的选课记录，成绩暂定为60分。
Insertintoscselectsno,'7',60fromstudentwheresdept='IS'
（4）把年龄小于等于16的女生记录保存到表TS中。
INSERTINTOTSSELECT*FROMSTUDENTWHERESAGE<=16Andssex='女'
（5）在表Student中检索每门课均不及格的学生学号、姓名、年龄、性别及所在系等信息，并把检索到的信息存入TS表中。
INSERTINTOTSSELECT*FROMSTUDENTWHERESNOIN（SELECTSNOFROMSCGROUPBYSNOHAVINGMAX（GRADE）<60）
（6）将学号为“98011”的学生姓名改为'刘华'，年龄增加1岁。
UPDATESTUDENTSETSNAME='刘华',SAGE=SAGE+1WHERESNO='98011'
（7）把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值（NULL）。
UPDATESCSETGRADE=NULLFROMCOURSEWHERESC.GRADE<60ANDSCX.CNO=COURSE.CNOANDCNAME='数据库系统'
（8）将Student的前4位学生的年龄均增加1岁。
Updatestudentsetsage=sage+1wheresnoin（selecttop4snofromstudent[orderbysno]）
（9）学生王林在3号课程考试中作弊，该课成绩改为空值（NULL）。
UPDATESCSETGRADE=NULLWHEREcno='3'ANDSNOIN（SELECTSNOFROMSTUDENTWHERESNAME='王林'）
（10）把成绩低于总平均成绩的女同学成绩提高5％。
updatescsetsc.grade=1.05*sc.gradewheresc.snoin（selectsnofromstudentwheressex='女'）ANDgrade<（selectavg（grade）fromsc）
（11）在基本表SC中修改课程号为“2”号课程的成绩，若成绩小于等于80分时降低2％，若成绩大于80分时降低1％（用两个UPDATE语句实现）。
UPDATESCSETGRADE=GRADE*0.98WHERECNO='2'ANDGRADE<=80
UPDATESCSETGRADE=GRADE*0.99WHERECNO='2'ANDGRADE>80
（12）利用“SELECTINTO……”命令来备份Student、SC、Course三表，备份表名自定。
SELECT*INTOTSTUDENTFROMSTUDENT
SELECT*INTOTSCFROMSC
SELECT*INTOTCOURSEFROMCOURSE
（13）在基本表SC中删除尚无成绩的选课元组。
DELETEFROMSCWHEREGRADEISNULL
（14）把“钱横”同学的选课情况全部删去。
DELETEFROMSCWHERESNOIN（SELECTSNOFROMSTUDENTWHERESNAME='钱横'）
（15）能删除学号为“98005”的学生记录吗？
一定要删除该记录的话，该如何操作？
给出操作命令。
Deletefromscwheresno='98005'
Deletefromstudentwheresno='98005'
（16）删除姓“张”的学生记录。
Deletefromscwheresnoin
升级会员 