数据库实验内容.docx
《数据库实验内容.docx》由会员分享，可在线阅读，更多相关《数据库实验内容.docx（15页珍藏版）》请在冰豆网上搜索。
数据库实验内容
数据库实验内容
实验一数据库、表的基本操作
（1）创建学生-课程数据库，数据文件名为student_data、大小10M，日志文件名为student_log、大小5M的新数据库，该数据库名为student。
createdatabasestudentonprimary
（name=student_data,
filename='i:
\student_data.dat',
size=10
）
logon
（name=student_log,
filename='i:
\student_log.dat',
size=5
）
（2） 创建学生关系表S：
学号
姓名
性别
年龄
所在系
Sno
Sname
Ssex
Sage
sdept
createtables（
snochar（20）notnull,
snamechar（20）,
ssexchar
（1）,
sageint,
sdeptchar（20）
primarykey（sno）,
）
（3） 创建课程关系表C：
课程号
课程名
先行课
学分
Cno
Cname
Cpno
ccredit
createtablec（
cnochar（20）notnull,
cnamechar（20）,
cpnochar（20）,
ccreditint,
primarykey（cno）,
）
（4） 创建学生-课程表SC：
学号
课程号
成绩
Sno
Cno
grade
createtablesc（
snochar（20）notnull,
cnochar（20）notnull,
gradeint,
primarykey（sno,cno）,
）
（5） 将以上创建表S、C、SC的SQL命令以.SQL文件的形式保存在磁盘上。
在表中加入至少4个元组，第一个为本人信息。
（6） 在表S上增加“出生日期”属性列。
altertablesaddbrithdaydatetime
（7） 删除表S的“年龄”属性列。
altertablesdropcolumnsage
（10） 删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。
droptablesc
（11）备份数据库，再还原。
实验二数据查询
在表S，C，SC上完成以下查询：
1．查询学生的基本信息；
select*froms
2．查询“CS”系学生的基本信息；
select*
froms
wheresdept='cs'
3．查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
selectsno,sname
froms
wheresdept='cs'andsagenotbetween19and21
4．找出“CS”系年龄最大的学生，显示其学号、姓名；
selectsno,sname
froms
wheresdept='cs'andsage=（selectmax（sage）froms）
5．找出各系年龄最大的学生，显示其学号、姓名；
selectsno,sname
fromsasa
wheresage=
（selectmax（sage）
fromsasb
wherea.sdept=b.sdept）;
6．统计“CS”系学生的人数；
selectcount（sno）as'人数'
froms
wheresdept='cs'
7．统计各系学生的人数，结果按升序排列；
selectcount（sno）as人数,sdept
froms
groupbysdept
orderbycount（sno）
8．按系统计各系学生的平均年龄，结果按降序排列；
selectavg（sage）as平均年龄,sdept
froms
groupbysdept
orderbyavg（sage）desc
9．查询无先修课的课程的课程名和学时数；
selectcname,ccredit
fromc
wherecpno=''
10．统计每位学生选修课程的门数、学分及其平均成绩；
selectsc.sno,count（o）as选修课程的门数,sum（ccredit）as学分,avg（grade）as平均成绩
fromsc,c
whereo=o
groupbysc.sno
11．统计选修每门课程的学生人数及各门课程的平均成绩；
selecto,count（sc.sno）as人数,avg（grade）as平均成绩
fromsc,c
whereo=o
groupbyo
12．找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；
selects.snoas学号,sdeptas系,avg（grade）as平均成绩
fromsc,s
wheres.sno=sc.sno
groupbys.sno,sdept
havingavg（grade）>85
orderby（avg（grade））asc
13．查询选修了“1”或“2”号课程的学生学号和姓名；
selects.sno,sname
froms,sc
whereo='1'ands.sno=sc.sno
union
selects.sno,sname
froms,sc
whereo='2'ands.sno=sc.sno
14．查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；
selectsc.sno,s.sname,sc.grade
fromsc,s,c
whereo=o
ands.sno=sc.sno
andame='db'
andsc.grade<60
15．查询每位学生选修了课程的学生信息（显示：
学号，姓名，课程号，课程名，成绩）；
selects.sno,sname,o,cname,sc.grade
froms,c,sc
whereo=o
ands.sno=sc.sno
16．查询没有选修课程的学生的基本信息；
selects.*
froms
wherenotexists（select*fromscwheres.sno=sc.sno）
17．查询选修了3门以上课程的学生学号；
selects.sno
froms,sc
wheres.sno=sc.sno
groupbys.sno
havingcount（o）>2
18．查询选修课程成绩至少有一门在80分以上的学生学号；
selectdistinctsno
fromsc
wheresc.grade>80
19．查询选修课程成绩均在80分以上的学生学号；
selectsno
fromsc
groupbysno
havingmin（grade）>80
20． 查询选修课程平均成绩在80分以上的学生学号；
selectsno
fromsc
groupbysno
havingavg（grade）>80
实验三数据更新与约束实验
1．使用SQL进行数据完整性控制。
（1）在创建下列关系表时完成如下约束：
定义实体完整性;参照完整性（外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno）;用户定义完整性：
学生年龄<30、所在系取值唯一。
（2）修改S中的约束条件，学号在100－1000之间。
学生关系表S：
学号
姓名
性别
年龄
所在系
Sno
Sname
Ssex
Sage
sdept
创建课程关系表C：
课程号
课程名
先行课
学分
Cno
Cname
Cpno
ccredit
创建成绩关系表SC：
学号
课程号
成绩
Sno
Cno
grade
altertablesaddconstraintsnocheck（sno>100andsno<1000）
（3）用实验验证当操作违反了完整性约束时，系统如何处理？
如：
在S中插入一条记录，学号不在100-1000之间；在SC中插入一条记录，课程号在C表中没有。
insertintosvalues（'3901','张晓兵','m',19,'软件工程'）
insertintoscvalues（'110','2',100）
（4）删除C中的一条课程记录（该课程至少应有一学生选修，即在SC表中有记录），观察SC表中记录的变化。
deletefromc
wherecname='db'
deletefromc
wherecno=2
select*fromsc
（5）删除2中增加的约束，再在S中插入一条记录，学号不在100-1000之间，结果如何？
altertablesdropconstraintsno
insertintosvalues（'39033','张晓兵','m',19,'sw'）
问题：
外键与参照主键（或候选键）是否一定要相同？
2.数据更新
（1） 将数据分别插入表S、C、SC；
（2） 在表S、C、SC上练习数据的插入、修改、删除操作。
（比较在表上定义/未定义主码（PrimaryKey）或外码（ForeignKey）时的情况）
（3） 将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。
deletefroms
deletefromc
deletefromsc
（4） 如果要在表SC中插入某个学生的选课信息（如：
学号为“”，课程号为“c123”，成绩待定），应如何进行？
insertintosc
values（'113','2',null）
（5） 求各系学生的平均成绩，并把结果存入数据库；
createtablesdept_grade
（sdeptchar（20）,
avggradeint
）
insertintosdept_grade（sdept,avggrade）
selectsdept,avg（grade）
fromsc,s
wheresc.sno=s.sno
groupbysdept
select*fromsdept_grade
（6） 将“CS”系全体学生的成绩置零；
updatesc
setgrade=0
where
（selectsdept
froms
wheres.sno=sc.sno
）='cs'
select*fromsc
（7） 删除“CS”系全体学生的选课记录；
deletefromsc
where（selectsdept
froms
wheres.sno=sc.sno
）='cs'
select*fromsc
（8） 删除学号为“S1”的相关信息；
deletefroms
wheresno='s1'
select*froms
（9） 将学号为“S1”的学生的学号修改为“S001”；
updates
setsno='s001'
wheresno='s1'
（10） 把平均成绩大于80分的男同学的学号和平均成绩存入另一个表
s_grade（sno，avg_grade）
createtables_grade
（snochar（20）notnull,
avg_gradeint
）;
insertintos_grade（sno,avg_grade）
selectsc.sno,avg（grade）
froms,sc,c
wheres.sno=sc.snoando=o
groupbysc.sno,ssex
havingavg（grade）>70andssex='m'
select*froms_grade
（11） 把选修了课程名为“数据结构”的学生的成绩提高10%；
updatesc
setgrade=grade*1.1
whereoin
（
selecto
fromsc,c
whereo=oandcname='db'
）;
（12） 把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；
deletefromsc
wherecno='c2'
andgrade<（
selectavg（grade）
fromscwherecno='c2'）
实验四视图的定义、使用
１． 定义“SSCH”院学生基本情况视图V_SSCH；1
createviewv_sschas
selectsno,sname,ssex,sdept
froms
wheresdept='sshc'
２． 将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G;
createviewv_s_c_gas
selects.sno,sname,o,cname,grade
froms,c,sc
wheres.sno=sc.snoando=o
３． 将各院学生人数，平均年龄定义为视图V_NUM_AVG;
createviewv_num_avg（avg_sage,count_sno）as
selectavg（sage）,count（sno）
froms
groupbysdept
4． 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
createviewv_avg_s_g（sno,count_cno,avg_grade）as
selectsc.sno,count（o）,avg（grade）
fromsc,c
whereo=o
groupbysc.sno
5． 查询平均成绩为90分以上的学生学号、姓名和成绩；
selectsc.sno,sname,avg（grade）
froms,sc
wheresc.sno=s.sno
groupbysc.sno,sname
havingavg（grade）>90
6．通过视图V_SSCH，新增加一个学生记录（'S12','YANXI',19,'SSCH'），并查询结果；
insertintov_ssch
values（'s12','yanxi',19,'ssch'）
select*fromv_ssch
7．通过视图V_SSCH，删除学号为“S12”学生信息，并查询结果；
deletefromv_ssch
wheresno='s12'
8．将视图V_SSCH中学号为“S12”的学生改名“中南人”
updatev_ssch
setsno='中南人'
wheresno='s12'
实验六存储过程建立与调用
1、建立五张表，每张表至少需要10条记录。
为每张表建立主键约束。
（1）/*员工人事表employee*/
emp_no
char（5）
Notnull
primarykey
员工编号
emp_name
char（10）
Notnull
员工姓名
sex
char
（1）
性别
dept
char（4）
所属部门
title
char（6）
职称
date_hired
datetime
到职日
birthday
datetime
生日
salary
int
薪水
addr
char（50）
住址
在表employee加入CHECK约束：
输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（2）/*客户表customer*/
cust_id
char（5）
Notnull
primarykey
客户号
cust_name
char（20）
Notnull,
客户名称
addr
char（40）
客户住址
tel_no
char（10）
客户电话
zip
char（6）
邮政编码
（3）/*销售主表sales*/
order_no
int
Notnull
primarykey
订单编号
cust_id
char（5）
Notnull,
客户号
sale_id
char（5）
业务员编号
tot_amt
numeric（9,2）
订单金额
order_date
datetime
订货日期
ship_date
datetime
出货日期
invoice_no
char（10）
发票号码
销售主表sales中的发票编号字段建立UNIQUE约束。
（4）/*销货明细表sale_item*/
order_no
int
Notnull,
primarykey
订单编号
prod_id
char（5）
Notnull,
primarykey
产品编号
qty
int
销售数量
unit_price
numeric（7,2）
单价
order_date
datetime
订单日期
（5）/*产品名称表product*/
pro_id
char（5）
Notnull
primarykey
产品编号
prod_name
char（20）
Notnull
产品名称
2、建立表的同时创建表的约束。
（1）为每张表建立主键约束。
（2）通过拖放操作加入外键。
（3）在表employee加入CHECK约束：
输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
（4）为销售主表sales中的发票编号字段建立UNIQUE约束。
3、通过快捷菜单得到脚本。
4、利用存储过程，给employee表添加一条业务部门员工的信息。
createprocedureadd_emp
@emp_nochar（5）,
@emp_namechar（10）,
@sexchar
（1）,
@deptchar（4）,
@titlechar（6）,
@date_hireddatetime,
@birthdaydatetime,
@salaryint,
@addrchar（50）
as
insertintoemployeevalues
（@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthday,@salary,@addr）
go
execadd_emp'E0003','张三','M','财务','经理','2001-1-1','2001-2-2',1000,'长沙市'
select*
fromemployee
dropprocedureadd_emp
5、利用存储过程从employee、sales、customer表的连接中返回所有业务员的姓名、客户姓名、销售金额。
createprocedurefind_nameas
selectemployee.emp_name,customer.cust_name,sales.tot_amt
fromemployee,customer,sales
wheresales.cust_id=customer.cust_idandsales.sale_id=employee.emp_no
go
execfind_name
dropprocedurefind_name
6、利用存储过程查找“刘德华”的员工编号、订单编号、销售金额。
createprocedurefindliue
@emp_namechar（10）
as
selectemp_name,emp_no,sales.order_no,sales.tot_amt
fromemployee,sales
whereemp_no=sales.sale_idandemp_name=@emp_name
go
execfindliue'刘德华'
dropprocedurefindliue
7、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
createprocedurefindli
@emp_namechar（10）
as
selectemp_no,sales.order_no,sales.tot_amt
fromemployee,sales
whereemp_no=sale_id
andemp_namelike@emp_name
andemployee.title='职员'
go
execfindli'刘%'
dropprocedurefindli
8、利用存储过程计算出订单编号为10003的订单的销售金额。
createproceduresaletotal
@order_nochar（5）as
selecttot_amt
fromsales
whereorder_no=@order_no
go
execsaletotal'1'
dropproceduresaletotal
升级会员 