sql实验综合Word格式文档下载.docx
- 文档编号:21643604
- 上传时间:2023-01-31
- 格式:DOCX
- 页数:13
- 大小:140.45KB
sql实验综合Word格式文档下载.docx
《sql实验综合Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《sql实验综合Word格式文档下载.docx(13页珍藏版)》请在冰豆网上搜索。
qtyint,
primarykey(sno,pno,jno),
foreignkey(sno)referencess(sno),
foreignkey(pno)referencesp(pno),
foreignkey(jno)referencesj(jno),
)
(二)修改基本表
1)altertablepaddcitychar(4)
2)altertablepaltercolumnweightsmallint
3)altertablepdropcolumncity
(三)建立索引
(1)
在S表上建立关于SNO的唯一索引supply_sno。
代码:
createuniqueindexsupply_snoons(sno)
(2)在J表上建立关于JNO升序的唯一索引project_jno。
createuniqueindexproject_inoonj(jnoasc)
(1)
删除S表上的索引supply_sno。
DROPINDEXS.supply_sno;
(2)删除J表上的索引project_jno
dropindexj.project_ino
(四)输入数据
①insert
intoj(jno,jname,city)
values('
j1'
'
三建'
北京'
)
2.1)updatepsetcolor='
蓝'
wherecolor='
红'
2.2)updatespjsetsno='
s3'
wheresno='
s5'
andjno='
j4'
andpno='
p6'
3.deletefromspjwheresno='
s2'
deletefromswheresno='
(五)单表查询
1)selectsname,city
froms
2)selectpname,color,weight
fromp
3)selectsno
fromspj
wherejno='
4)selectsno
fromspj
p1'
(六)连接查询
1)selectsno
fromp,spj
wherep.pno=spj.pnoandjno='
andcolor='
2)selectpname,qty
j2'
3)selectjname
froms,j,spj
wheres.sno=spj.snoandspj.jno=j.jnoands.city='
上海'
(七)视图操作
createviewV_SPJ
as
selectsno,pno,qty
fromj,spj
wherej.jno=spj.jnoandjname='
1)selectpno,qty
fromV_SPJ
wheresno='
s1'
2)selectpno,qty
3)dropviewV_SPJ
(八)嵌套查询
andpnoin(
selectpnofrompwherecolor='
2)selectjno
fromjwherejnonotin
(selectspj.jnofromspj,p,s
wherespj.sno=s.sno
andspj.pno=p.pno
ands.city='
天津'
andp.color='
4)selectpnofromspj
wheresnoin
(selectsnofromswherecity='
5)selectjname
fromj
wherejnoin
(selectspj.jnofromspj,s
wheres.city='
ands.sno=spj.sno)
———————————————————————————————————
实验目的及要求
1)
熟悉通过SQL对数据进行完整性控制。
2)
使用SQL对数据进行完整性控制(3类完整性、CHECK短语、CONSTRAIN子句、触发器)。
用实验证实,当操作违反了完整性约束条件时,系统是如何处理的。
(一)列级定义主码
createtablestudent
(snochar(7)primarykey,snamechar(8),ssexchar
(2),sagesmallint,sdeptchar(20))
createtablecourse(cnochar(4)primarykey,cnamechar(30),ccreditsmallint)
createtablesc(snochar(7)notnull,cnochar(4)notnull,gradesmallintnotnull,primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno))
createtabledept(dnamechar(40)unique,deptnochar(4)primarykey)
(二)在表级定义主码:
createtablestudent(snochar(7),snamechar(8),ssexchar
(2),sagesmallint,sdeptchar(20),
primarykey(sno))
createtablecourse(cnochar(4),cnamechar(30),ccreditsmallint,
primarykey(cno))
createtabledept(dnamechar(40)unique,deptnochar(4),
primarykey(deptno))
(3)使用CHECK短语指定列值应该满足的条件。
3.1定义表Student,要求属性Ssex的值只允许取“男”或“女”;
createtablestudent
(snochar(7),
snamechar(8),
ssexchar
(2)check(ssexin('
男'
女'
)),
sagesmallint,
sdeptchar(20),
3.2定义表SC,要求属性Grade的值定义在0-100之间。
createtablesc
(snochar(7),cnochar(4),
gradesmallintcheck(grade>
=0andgrade<
=100),
primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno))
3.3使用CHECK短语指定列用户定义的元组上的约束条件。
定义表Student,要求当学生性别为男时,其名字不能以Ms.打头。
4.1定义表Student,要求学号在90000-99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”。
要求全部用约束命名子句实现。
(snonumeric(7)
constraintc1check(snobetween902000and99999),
snamechar(8)
constraintc2notnull,
sagenumeric(3)
constraintc3check(sage<
30),
ssexchar
(2)
constraintc4check(ssexin('
4.2修改表Student中的完整性限制,去掉对性别的限制,并将年龄的限制由小于30改为小于40.
altertablestudent
dropconstraintc4;
dropconstraintc3;
addconstraintc3check(sage<
40)
5.1创建一个触发器,当学生的信息删除后,将其选课信息也删除。
createtriggerstud_sc
onstudent
afterdelete
deletefromsc
whereSIDIN(selectSIDfromdeleted);
deletefromStudent
whereSID='
s007'
5.2删除触发器TRI。
删除触发器:
droptriggerstud_sc
5.3在插入学生选课信息时,统计C003课程的成绩分布情况(即按照各分数段统计人数)
createtriggerstud_donsc
afterinsertasif'
C003'
=(selectCIDfrominserted)
updateranksetNum=(selectcount(*)fromscwheregrade>
=90andcid='
c003'
)wheredivision='
优秀'
InsertintoSCvalues('
S008'
C001'
90)
5.4统计学生课程的平均成绩
onStudent
afterinsert
asselectCIDfromSCgroupbyCID
insertintostudentvalues('
陈陈'
22'
5.5自动统计所有学生的加权平均分数(还没有成绩的课程不算在内),其中每个学生的加权平均分的计算公式如下:
createtriggerstud_c2
onsc
afterinsert
asselectSID,SUM(Grade*Course.Price)/sum(course.price)
fromsc,course
wheresc.cid=course.cidandsc.gradeisnotnull
groupbySID
验证如下:
Insertintoscvalues('
s002'
95)
Deletefromsc
Wheresid='
andcid='
————————————————————————————————
目的:
熟悉使用存储过程和游标来进行数据库应用程序的设计。
要求:
对学生课程数据库,编写存储过程,完成下面功能:
1)统计任意一门课的平均成绩
2)统计任意一门课的成绩分布情况,即按照各分数段统计人数
3)查找某个学生的选课信息
4)将学生成绩从百分制改为等级制(即A、B、C、D、E)
5)在SC表中插入新列“status”,检查学生成绩,如果有同学不及格,status值为“重修”,如果及格,status值为“取得学分”,如果暂时没有成绩,status值为“待定”
createprocedureavg_course
@HCoursechar(20)
as
selectavg(grade)
wherecname=@HCourseandsc.cid=course.cid
go
验证:
execavg_course'
大学英语'
createprocedureCount_fenbu
@Hcnamechar(20)
selectCount(Sid)as优秀人数
fromCourse,sc
whereCourse.cid=sc.cidandcname=@Hcnameandgradebetween90and100
selectCount(Sid)as良好人数
whereCourse.cid=sc.cidandcname=@Hcnameandgradebetween80and90
selectCount(Sid)as一般人数
whereCourse.cid=sc.cidandcname=@Hcnameandgradebetween70and80
selectCount(Sid)as及格人数
whereCourse.cid=sc.cidandcname=@Hcnameandgradebetween60and70
whereCourse.cid=sc.cidandcname=@Hcnameandgradebetween0and60
execCount_fenbu'
离散数学'
Createprocedurestu_sc
@Hsnamechar(20)
As
Select*fromstudent,sc
Wheresname=@Hsnameandsc.sid=student.sid
Go
execstu_sc‘张三’
Createprocedurechange_grade
Declare@sidchar(4),@cidchar(4),@gradeint,@grade_charchar
(1)
Declarecur_gradecursorforselectsid,cid,gradefromsc
Opencur_grade
Fetchnextfromcur_gradeinto@sid,@cid,@grade
While@@fetch_status=0
begin
If@grade>
90and@grade<
=100set@grade_char='
A'
Elseif@grade>
80and@grade<
=90set@grade_char='
B'
70and@grade<
=80set@grade_char='
C'
60and@grade<
=70set@grade_char='
D'
Elseset@grade_char='
E'
Updatesc
setnewgrade=@grade_char
wheresid=@sidandcid=@cid
end
closecur_grade
deallocatecur_grade
execchange_grade
60set@sta_char='
取得学分'
Elseif@grade<
重修'
Elseset@sta_char='
待定'
setstatus=@sta_char
execstatus_grade
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 实验 综合