数据库实验内容Word文档下载推荐.docx
《数据库实验内容Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《数据库实验内容Word文档下载推荐.docx(15页珍藏版)》请在冰豆网上搜索。
createtablec(
cnochar(20)notnull,
cnamechar(20),
cpnochar(20),
ccreditint,
primarykey(cno),
(4)
创建学生-课程表SC:
成绩
grade
createtablesc(
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
andsagenotbetween19and21
4.找出“CS”系年龄最大的学生,显示其学号、姓名;
andsage=(selectmax(sage)froms)
5.找出各系年龄最大的学生,显示其学号、姓名;
selectsno,sname
fromsasa
wheresage=
(selectmax(sage)
fromsasb
wherea.sdept=b.sdept);
6.统计“CS”系学生的人数;
selectcount(sno)as'
人数'
7.统计各系学生的人数,结果按升序排列;
selectcount(sno)as人数,sdept
froms
groupbysdept
orderbycount(sno)
8.按系统计各系学生的平均年龄,结果按降序排列;
selectavg(sage)as平均年龄,sdept
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
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
2'
14.查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩;
selectsc.sno,s.sname,sc.grade
fromsc,s,c
ands.sno=sc.sno
andame='
db'
andsc.grade<
60
15.查询每位学生选修了课程的学生信息(显示:
学号,姓名,课程号,课程名,成绩);
selects.sno,sname,o,cname,sc.grade
froms,c,sc
16.查询没有选修课程的学生的基本信息;
selects.*
wherenotexists(select*fromscwheres.sno=sc.sno)
17.查询选修了3门以上课程的学生学号;
selects.sno
groupbys.sno
havingcount(o)>
2
18.查询选修课程成绩至少有一门在80分以上的学生学号;
selectdistinctsno
fromsc
wheresc.grade>
80
19.查询选修课程成绩均在80分以上的学生学号;
selectsno
groupbysno
havingmin(grade)>
20.
查询选修课程平均成绩在80分以上的学生学号;
实验三数据更新与约束实验
1.使用SQL进行数据完整性控制。
(1)在创建下列关系表时完成如下约束:
定义实体完整性;
参照完整性(外码、在删除S中的元组时级联删除SC中相应元组、当更新S中的Sno时同时更新SC中的Sno);
用户定义完整性:
学生年龄<
30、所在系取值唯一。
(2)修改S中的约束条件,学号在100-1000之间。
学生关系表S:
创建成绩关系表SC:
altertablesaddconstraintsnocheck(sno>
100andsno<
1000)
(3)用实验验证当操作违反了完整性约束时,系统如何处理?
如:
在S中插入一条记录,学号不在100-1000之间;
在SC中插入一条记录,课程号在C表中没有。
insertintosvalues('
3901'
'
张晓兵'
m'
19,'
软件工程'
)
insertintoscvalues('
110'
100)
(4)删除C中的一条课程记录(该课程至少应有一学生选修,即在SC表中有记录),观察SC表中记录的变化。
deletefromc
wherecname='
wherecno=2
select*fromsc
(5)删除2中增加的约束,再在S中插入一条记录,学号不在100-1000之间,结果如何?
altertablesdropconstraintsno
39033'
sw'
问题:
外键与参照主键(或候选键)是否一定要相同?
2.数据更新
(1)
将数据分别插入表S、C、SC;
在表S、C、SC上练习数据的插入、修改、删除操作。
(比较在表上定义/未定义主码(PrimaryKey)或外码(ForeignKey)时的情况)
将表S、C、SC中的数据全部删除,再利用磁盘上备份的数据来恢复数据。
deletefroms
deletefromsc
如果要在表SC中插入某个学生的选课信息(如:
学号为“”,课程号为“c123”,成绩待定),应如何进行?
insertintosc
values('
113'
null)
求各系学生的平均成绩,并把结果存入数据库;
createtablesdept_grade
(sdeptchar(20),
avggradeint
insertintosdept_grade(sdept,avggrade)
selectsdept,avg(grade)
wheresc.sno=s.sno
groupbysdept
select*fromsdept_grade
将“CS”系全体学生的成绩置零;
updatesc
setgrade=0
where
(selectsdept
)='
select*fromsc
删除“CS”系全体学生的选课记录;
where(selectsdept
select*fromsc
(8)
删除学号为“S1”的相关信息;
wheresno='
s1'
select*froms
(9)
将学号为“S1”的学生的学号修改为“S001”;
updates
setsno='
s001'
把平均成绩大于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
70andssex='
select*froms_grade
(11)
把选修了课程名为“数据结构”的学生的成绩提高10%;
updatesc
setgrade=grade*1.1
whereoin
(
selecto
whereo=oandcname='
);
(12)
把选修了“C2”号课程,且成绩低于该门课程的平均成绩的学生成绩删除掉;
wherecno='
c2'
andgrade<
(
selectavg(grade)
fromscwherecno='
c2'
实验四视图的定义、使用
1.
定义“SSCH”院学生基本情况视图V_SSCH;
1
createviewv_sschas
selectsno,sname,ssex,sdept
sshc'
2.
将S,C,SC表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_S_C_G;
createviewv_s_c_gas
selects.sno,sname,o,cname,grade
froms,c,sc
3.
将各院学生人数,平均年龄定义为视图V_NUM_AVG;
createviewv_num_avg(avg_sage,count_sno)as
selectavg(sage),count(sno)
4.
将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
createviewv_avg_s_g(sno,count_cno,avg_grade)as
selectsc.sno,count(o),avg(grade)
5.
查询平均成绩为90分以上的学生学号、姓名和成绩;
selectsc.sno,sname,avg(grade)
groupbysc.sno,sname
90
6.通过视图V_SSCH,新增加一个学生记录('
S12'
YANXI'
19,'
SSCH'
),并查询结果;
insertintov_ssch
values('
s12'
yanxi'
ssch'
)
select*fromv_ssch
7.通过视图V_SSCH,删除学号为“S12”学生信息,并查询结果;
deletefromv_ssch
wheresno='
8.将视图V_SSCH中学号为“S12”的学生改名“中南人”
updatev_ssch
中南人'
实验六存储过程建立与调用
1、建立五张表,每张表至少需要10条记录。
为每张表建立主键约束。
(1)/*员工人事表employee*/
emp_no
char(5)
Notnull
primarykey
员工编号
emp_name
char(10)
员工姓名
sex
char
(1)
dept
char(4)
所属部门
title
char(6)
职称
date_hired
datetime
到职日
birthday
生日
salary
int
薪水
addr
char(50)
住址
在表employee加入CHECK约束:
输入的员工编号必须以E开头的5位数编号,性别只能为M/F。
(2)/*客户表customer*/
cust_id
客户号
cust_name
char(20)
Notnull,
客户名称
char(40)
客户住址
tel_no
客户电话
zip
邮政编码
(3)/*销售主表sales*/
order_no
订单编号
sale_id
业务员编号
tot_amt
numeric(9,2)
订单金额
order_date
订货日期
ship_date
出货日期
invoice_no
发票号码
销售主表sales中的发票编号字段建立UNIQUE约束。
(4)/*销货明细表sale_item*/
prod_id
产品编号
qty
销售数量
unit_price
numeric(7,2)
单价
订单日期
(5)/*产品名称表product*/
pro_id
prod_name
产品名称
2、建立表的同时创建表的约束。
(1)为每张表建立主键约束。
(2)通过拖放操作加入外键。
(3)在表employee加入CHECK约束:
(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
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
execfindliue'
刘德华'
dropprocedurefindliue
7、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。
createprocedurefindli
selectemp_no,sales.order_no,sales.tot_amt
whereemp_no=sale_id
andemp_namelike@emp_name
andemployee.title='
职员'
execfindli'
刘%'
dropprocedurefindli
8、利用存储过程计算出订单编号为10003的订单的销售金额。
createproceduresaletotal
@order_nochar(5)as
selecttot_amt
fromsales
whereorder_no=@order_no
execsaletotal'
dropproceduresaletotal