数据库的卷二练习答案.docx
《数据库的卷二练习答案.docx》由会员分享，可在线阅读，更多相关《数据库的卷二练习答案.docx（17页珍藏版）》请在冰豆网上搜索。
数据库的卷二练习答案
--1、创建数据库
createdatabasetest
onprimary
（
name='test_data',
filename='D:
\SQL期末作业\test_data.mdf',
size=10MB,
maxsize=10MB,
filegrowth=1MB）
logon
（
name='test_log',
filename='D:
\SQL期末作业\test_log.ldf',
size=1MB,
maxsize=5MB,
filegrowth=10%
）
go
--2、删除数据库
dropdatabasetest
--3、创建student表
usetest
go
createtablestudent
（
st_idnvarchar（9）notnullconstraintpkprimarykey,
st_nmnvarchar（8）notnull,
st_sexnvarchar
（2）,
st_birthdatetimenull,
st_scoreintnull,
st_datedatetimenull,
st_fromnchar（20）,
st_dpidnvarchar
（2）,
st_mnttinyint
）
--3、创建course表
usetest
go
createtablecourse
（cs_idnvarchar（4）notnullconstraintpk_idprimarykey,
cs_nmnvarchar（20）notnull,
cs_tmint,
cs_scint）
--4、创建slt_course
usetest
go
createtableslt_course
（
cs_idnvarchar（4）notnullconstraintfk_idreferencescourse（cs_id）,
st_idnvarchar（9）notnullconstraintfk_st_idreferencesstudent（st_id）,
scoreint,
sltdatedatetime
）
--5、创建院系（dept）
usetest
go
createtabledept
（
dp_idnvarchar
（2）notnull,
dp_nmnvarchar（20）notnull,
dp_drtnvarchar（8）,
dt_telnvarchar（12）
）
--修改表结构
--1）向表中添加列
usetest
go
altertabledept
adddp_countnvarchar（3）
go
--2）修改列数据
usetest
go
altertabledept
altercolumndp_countint
go
--3、删除表指定列（column）
usetest
go
altertabledept
dropcolumndp_count
go
--4、删除dept表
usetest
go
droptabledept
go
--向表中输入数据记录
usetest
go
insertstudent
values
（'20151090101','邓建娥','女','1996','400',
'2015','山东省潍坊','01','学习部副部'）
go
--3、数据完整性
--1）空值约束
usetest
go
altertablestudent
altercolumnst_sexvarchar
（2）notnull
--默认值约束（default）
usetest
go
altertablestudent
addconstraintdf_fromdefault'陕西省'forst_from
go
--检查约束（check）
usetest
go
altertableslt_course
addconstraintck_coursecheck（score>=0andscore<=100）
go
--主键clustered为聚集索引，nonclustered为非聚集索引
usetest
altertabledept
addconstraintpk_dp_idprimarykeyclustered（dp_id）
go
--唯一性约束（unique）
usetest
altertabledept
addconstraintuq_nmuniquenonclustered（dp_nm）
go
--创建外键
usetest
altertablestudent
addforeignkey（st_dpid）referencesdept（dp_id）
--数据更新
--4.1、dept表中插入数据
usetest
go
insertdept（dp_id,dp_nm,dp_drt,dt_tel）
values（'11','自动控制系','李启宇','81234567'）
go
--4.2、student表中插入记录
usetest
go
insertstudent（st_id,st_nm,st_sex,st_birth,st_dpid）
values（'070201001','王小五','男','1990-09-09','11'）
go
--4.3course表中插入记录
usetest
go
insertcourse（cs_id,cs_nm）
values（'1234','操作系统'）
go
--4.4slt_course表中添加数据
usetest
go
insertslt_course（cs_id,st_id）
values（'1234','070201001'）
go
--2.修改表中数据
--4.5修改student表中记录
usetest
go
updatestudent
setst_score='88'
wherest_nm='王小五'
go
--4.6修改course表记录
usetest
go
updatecourse
setcs_sc=4,cs_tm=64
--4.7修改slt_course表中记录
usetest
go
updateslt_course
setscore=77
wherecs_id=1234andst_id=070201001
go
--删除表中数据
usetest
deleteslt_course
wherecs_id='1234'andst_id='070201001'
go
--删除course记录
usetest
deletecourse
wherecs_id='1234'
go
--5数据查询（）简单查询
--5.1查询所有系的信息
usetest
go
select*
fromdept
go
--5.2查询所有的课程号与课程名称
usetest
go
selectcs_id,cs_nm
fromcourse
go
--5.3查询student时使用列表达式：
入学成绩+400
usetest
go
selectst_score+400
fromstudent
go
--5.4用as关键字dept表指定列名：
系号、系名、系主任、联系电话
usetest
go
selectdp_idas系号,dp_nmas系名,dp_drtas系主任,dt_telas联系电话
fromdept
go
--5.5使用"="号为course表中属性指定列名：
课程号、课程名、学时（=cs_sc*16）、学分
usetest
go
select课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16
fromcourse
go
--5.6查询列表中使用系统函数显示所有学生的学号、姓名、性别和入学年份
usetest
go
selectst_id,st_nm,st_sex,st_date
fromstudent
groupbyst_id
go
--5.7消除查询结果中的重复项显示所有学生班级
usetest
selectdistinctst_id
fromstudent
go
--6数据查询（）-条件查询
--1.使用关系表达式查询条件
--6.1查询dept表中系号为的院系信息
usetest
go
select*
fromdept
wheredp_id='11'
go
--6.2查询student表中系的学生学号、姓名、性别和所在系编号
usetest
go
selectst_id,st_nm,st_sex,st_dpid
fromstudent
wherest_dpid='11'
go
--6.3查询student表中年及以后入学的学生信息
usetest
select*
fromstudent
wherest_date>'2008-12-31'
go
--6.4在查询student表班学生的学号、姓名、性别和入学成绩
usetest
go
selectst_id,st_nm,st_sex,st_score
fromstudent
wherest_bj='080808'
go
--使用逻辑表达式表示查询条件
--6.5查询选修了号课程且成绩在以下的学生学号
usetest
go
selectst_id
fromslt_course
wherecs_id='1002'andscore<'60'
go
--6.6查询系和系的学生信息
usetest
go
select*
fromstudent
wherest_dpid='11'orst_dpid='12'
go
--6.7查询所有“计算机”开头的课程信息
usetest
go
select*
fromcourse
wherecs_nmlike'计算机%'
go
--6.8查询在.7.1到.6.30之间出生的学生信息
usetest
go
select*
fromstudent
wherest_birthbetween'1989-07-01'and'1990-06-30'
go
--6.9查询选修了号课程且成绩在到之间的学生选课信息
usetest
go
select*
fromslt_course
wherecs_id='1001'and（scorebetween60and80）
go
--6.10使用IN关键字进行查询查询系、系、系的学生信息
usetest
go
select*
fromstudent
wherest_dpidin（'11','12','13'）
go
--6.11使用[NOT]NULL关键字进行查询查询系、系、系的学生信息
usetest
go
select*
fromstudent
where（st_dpidin（'11','12','13'））and（st_bjisnull）
go
--6.11查询所有生源为非空的学生信息
usetest
go
select*
fromstudent
wherest_fromisnotnull
go
--7数据查询（）——查询排序与查询结果存储
--7.1：
查询课程信息，按课程名称降序排序:
desc升序：
asc
usetest
go
select*
fromcourse
orderbycs_nmdesc
go
--7.2查询选修了号课程成绩非空的学生学号和成绩，并按成绩降序排序
usetest
go
selecta.st_id,b.score
fromstudentasajoinslt_courseasb
ona.st_id=b.st_id
orderbyb.scoredesc
go
--8数据查询（）——查询统计与汇总
--8.1查询选修号课程的学生人数
usetest
go
selectCOUNT（*）as学生人数
fromstudent
wherest_idin（selectst_idfromslt_coursewherecs_id='1001'）
go
--8.2查询选修班学生的平均入学成绩
usetest
go
selectAVG（st_score）as平均入学成绩
fromstudent
wherest_bj='070101'
gor
--8.3查询号学生选修课程的数量、总分以及平均分
usetest
go
selectCOUNT（*）as选修课数量,SUM（score）as总分,AVG（score）as平均分
fromslt_course
wherest_id='070101001'
go
--8.4查询选修号课程的学生人数、最高分、最低分和平均分
usetest
go
selectCOUNT（*）as学生人数,max（score）as最高分,MIN（score）as最低分,AVG（score）as平均分
fromslt_course
wherecs_id=1001
go
--8.5查询选修了均分在以上的课程号及均分
usetest
go
selectcs_id,AVG（score）平均分
fromslt_course
groupbycs_id
havingAVG（score）>75
go
--9数据查询（）——连接查询、子查询
--9.1查询学生学号、姓名、性别及其所选课程编号
usetest
go
selectstudent.st_id,st_nm,st_sex,cs_id
fromstudentjoinslt_course
onstudent.st_id=slt_course.st_id
go
--9.2查询学生学号、姓名及其所选课程名称及成绩
usetest
go
selectstudent.st_id,st_nm,cs_id,score
fromstudentjoinslt_course
onstudent.st_id=slt_course.st_id
go
--9.3查询选修了“数据结构”课程的学生学号、姓名及课程成绩
usetest
go
selectstudent.st_id,st_nm,score
fromstudentjoinslt_course
onstudent.st_id=slt_course.st_id
wherecs_idin（selectcs_idfromcoursewherecs_nm='数据结构'）
go
--10数据查询（）——子查询
--10.1查询选修了课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序
usetest
go
selectst_id,st_nm,st_sex
fromstudent
wherest_idin（selectst_idfromslt_coursewherecs_id='1002'andscore<'60'）
go
--11视图和索引
--11.1基于student表创建一个视图view_s2014，可以查看年及以后入学的学生信息
usetest
go
createviewview_s2014
as
select*
fromstudent
wherest_date>'2014-12-30'
go
--11.2为course表创建基于课程编号列的聚集索引kcbh_index
usete-77st
go
createclusteredindexkcbh_indexoncourse（cs_id）
go
execsp_helpindex'course'
--12游标
--12.1用游标实现：
输出所有女学生的学号、姓名、性别和班级代码。
usetest
go
declarec_xsxxcursorkeysetfor
selectst_id,st_nm,st_sex,st_bj
fromstudent
openc_xsxx
declare@xhnvarchar（8）,@xmnvarchar（8）,@xbnvarchar（8）,@bjdmnvarchar（10）
if@@ERROR=0--判断游标打开是否成功
begin
if@@CURSOR_ROWS>0
begin
print'共有女学生'+rtrim（cast（@@cursor_rowsaschar（3）））+'名,分别是:
'
print''
fetchnextfromc_xsxxinto@xh,@xm,@xb,@bjdm
while（@@FETCH_STATUS=0）
beginprint@xh+','+@xm+','+@xb+','
fetchnextfromc_xhxxinto@xh,@xm,@xb,@bjdm
end
end
end
else
print'游标存在问题！
'
closec_xsxx
deallocate
--13存储过程和触发器
--13.1创建一个带参数的存储过程cj：
当任意输入一个学生的姓名时，将从三个表
--（学生表、课程表、课程注册表）中返回该学生的学号、选修的课程名称和课程成绩
--学生表课程课程注册
usestudent
go
createproccj@xmvarchar（10）
as
select学生.学号,课程名称,成绩
from课程注册join学生on课程注册.学号=学生.学号
join课程on课程注册.课程号=课程.课程号
where学生.姓名=@xm
--执行cj存储过程，查询刘永辉的学号、选修课程和课程成绩。
usestudent
go
execcj@xm='刘永辉'
--13.3创建一个带返回值的存储过程，根据“课程”表和“课程注册”表中的数据
--返回某课程的成绩大于分的人数。
usestudent
go
createprocg@rs
--创建一个del_zy的delete触发器
usestudent
go
createtriggerdel_zyon专业
fordelete
as
declare@zydmchar（9）
select@zydm=专业代码fromdeleted
ifexists（select*from班级where专业代码=@zydm）
begin
print'正在使用，不能被使用！
'
rollbacktransaction
end
go
--13.5创建一个触发器bjdm_update，当班级表中的班级代码被更新时，
内部
升级会员 