新SQLSERVER实验练习Word下载.docx
- 文档编号:22746392
- 上传时间:2023-02-05
- 格式:DOCX
- 页数:48
- 大小:33.38KB
新SQLSERVER实验练习Word下载.docx
《新SQLSERVER实验练习Word下载.docx》由会员分享,可在线阅读,更多相关《新SQLSERVER实验练习Word下载.docx(48页珍藏版)》请在冰豆网上搜索。
学生表、课程表、选课表属于数据库School,其各自得数据结构如下:
学生Student(Sno,Sname,Ssex,Sage,Sdept)
序号
列名
含义
数据类型
长度
1
Sno
学号
字符型(char)
6
2
Sname
姓名
字符型(varchar)
8
3
Ssex
性别
4
Sage
年龄
整数(smallint)
5
sdept
系科
15
课程表course(Cno,Cname,Cpno,Ccredit)
Cno
课程号
cname
课程名
20
Cpno
先修课
Ccredit
学分
短整数(tinyint)
学生选课SC(Sno,Cno,Grade)
Grade
成绩
小数(decimal)
12,1
二把创建表的sql语句的脚本存储到文件school.sql。
createtableStudent(
Snochar(6),
Snamechar(10),
Ssexchar
(2),
Sagesmallint,
Sdeptchar(10),
)
createtablecourse(
Cnochar(4),
Cnamechar(16),
Cpnochar(4),
Ccreditint,
)
createtableSC(
Snochar(6),
Gradeint
三使用SP_HELP查看表student的表结构
利用企业管理器查看表sc的表结构
四利用sql语句表结构修改
1在student表中添加列:
家庭地址address长度为60varchar型
入学日期inDate日期型
ALTERTABLEstudentADDaddressvarchar(60)
ALTERTABLEstudentADDinDatedatetime
完成后用sp_help查看是否成功。
2将家庭地址address长度为50
ALTERTABLEstudentALTERCOLUMNvarchar(50)
完成后用sp_help查看是否成功。
3删除student表的inDate列
ALTERTABLEstudentDROPCOLUMNinDate
五删除表
1删除表sc
2删除表student
3删除表course
试验三创建数据完整性
1掌握创建数据完整性约束的命令。
2掌握完整性约束的修改、删除。
1能建立完整性约束2修改完整性约束3删除完整性约束
一写出带有完整性约束的CreateTable命令建立表student、course、sc。
1Student表的主码:
sno
student的约束:
●姓名不可为空,且唯一
●性别不能为空且取值范围为{男,女}
●年龄大于16岁
●sdept默认为‘JSJ’系
2Course表的主码:
cno
course的约束:
●Ccredit取值范围{0,1,2,3,4,5}
●课程表的每一行的Cno与cpno不可相同
3Sc表的主码:
sno,cno。
主码名为PK_SC
Sc的外码:
●外码:
SC表的sno参照表student的sno
sc表的Cno参照表course的cno
4把上述创建表的sql语句的脚本存储到文件createSchool.sql。
Snamechar(10)notnullunique,
Ssexchar
(2)check(ssex='
男'
orssex='
女'
),
Sagesmallintcheck(sage>
16),
Sdeptchar(10)notnulldefault'
JSJ'
primarykey(sno)
)
Ccreditintcheck(Ccredit>
=0andCcredit<
=5),
check(cno<
>
cpno),--约束
primarykey(cno)
)
Gradeintcheck(grade<
=100),
constraintpk_scprimarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno),
二使用SP_HELP查看表student的主码名,约束名,并记录。
使用SP_HELP查看表sc的主码名,外码名,并记录。
三利用altertable添加、删除完整性约束
1删除SC的主码,sc表的主码名为pk_sc
ALTERTABLEscDROPpk_sc
复习在Sql-Server企业管理器中如何完成。
2删除SC表参照course表的外码。
如何知道SC表参照course表的外码的名字。
1)SP_HELPcourse找到外码名,假设为:
fk_sc_cno_02938
2)ALTERTABLEscDROPfk_sc_cno_02938
3添加SC表的主码。
主码名为PK_SC
ALTERTABLEscADDCONSTRAINTpk_scPRIMARYKEY(sno,cno)
4添加SC表的Cno的外码,参照表Course的Cno.
ALTERTABLEscADDFOREIGNKEY(cno)REFERENCEScourse(cno)
5加自定义约束:
表SC的成绩只能在0–100分之间。
ALTERTABLEscADDcheck(grade>
=0andgrade<
=100)
四使用Sql-Server企业管理器完成:
1删除SC表参照course表的外码。
2建立SC表的Cno的外码,参照表Course的Cno.
*使该外码具有级联修改的功能。
3删除表SC的成绩只能在0–100分之间的约束.
3加自定义约束:
五使用
select*fromstudent查看信息
select*fromcourse查看信息
select*fromsc查看信息
试验四数据完整性试验
1理解实体完整性、参照完整性、用户自定义完整性的作用
2特别掌握外码的作用。
记录试验中遇到的问题,并写出原因。
实验前需要利用试验三完成的脚本文件createSchool.sql,重新建立数据库school。
一实体完整性
1student表数据输入
3001
赵达
男
SX
3002
杨丽
女
21
JSJ
李寅
●输入上述数据,记录出现的问题,说明原因。
第三行不能输入。
Student的主码为sno,因此sno列的值不能重复。
●select*fromstudent查看你输入了几行数据。
2course表数据的输入
Cname
1081
电子商务
3SC表数据的输入
90
79
输入上述数据,记录出现的问题,说明原因。
二用户自定义完整性约束
表student有用户自定义约束:
性别不能为空且取值范围为{男,女}
年龄大于16岁
表course的自定义约束:
Ccredit取值范围{0,1,2,3,4,5}
课程表的每一行的Cno与cpno不可相同
3005
14
3006
南
3005学生不能输入,年龄问题,有约束sage>
16
3006学生不能输入,性别不对。
●select*fromstudent查看你输入了那些数据。
1085
C++
9
1086
语文
1086不能输入,因为有约束check(cno<
cpno)
128
3002这条数据不能输入,因为grade不能大于100分
三参照完整性约束
●掌握表之间建立外码后,对被参照表的如下操作会有何影响:
修改主码、插入新行、删除新行?
●对参照表添加新行、删除行、修改外码值有何影响?
●掌握级联修改、级联删除的概念。
注意:
表SC的Sno是外码,参照student的sno。
表SC的Cno是外码,参照course的cno。
1输入实验前的数据
学生表Student
Sdept
4001
赵尹
4002
杨开
课程表course
1088
Java
1089
数学
学生选课SC
86
2试验过程
1)在SC表中添加新行:
1066
76
记录试验结果.,写出出现此结果的原因.
不能添加,因为在cno是外码,参照course的cno,但在course中没有1066课程。
2)在student表中添加新行
4003
赵辉
可以输入
3)删除student表的4001,4002学生
两个学生不能被删除,因为sc的外码sno参照student的sno,sc中已经有4001,4002学生的数据,因此不能删除。
思考:
●删除SC表的记录有限制吗?
没有
●采取什么技术能使不能成功执行的命令变得可以执行,且使数据库保持数据完整性。
级联删除
4)把student表的学号4003改为4018,4001改为4021。
4003可以改为4018,4001不能改为4021
因为sc的外码sno参照student的sno,sc中已经有4001的数据,但没有4003的选课数据。
采取什么技术能使本题不能执行的命令可以执行,且使数据库保持数据完整性。
级联修改
5)把sc表中的如下记录的学号从4001改为4011。
不能修改,因为sc的外码sno参照student的sno,4011在student中不存在。
如不成功,则可以采取什么方法来实现此要求。
需要在student表中添加4011学生。
●如不成功,那么把4001修改为4003,能成功吗?
能成功!
思考:
参照完整性规则中,外码可以为空,但SC表中的外码可以为空吗?
为什么?
举一个外码可以为空的例子。
不可以,因为sc表的主码为sno+cno,即sno,cno为主属性,所以不能为空。
试验五索引
掌握索引的建立、删除的方法。
一创建索引
1建student的索引
为姓名建立索引,索引名:
Ix_student_sname
为系科建立索引,索引名:
Ix_student_sdept
createindexix_student_snameONstudent(sname)
createindexix_student_sdeptONstudent(sdept)
2SC的索引
为课程号建立索引:
ix_sc_cno
createindexix_sc_cnoONsc(cno)
3Course的索引
为课程名建立唯一性索引:
Ix_course_cname
createuniqueindexix_course_cnameONcourse(cname)
4如何SP_HELP查看索引刚才建立的索引?
如何在企业管理器中查看索引?
二删除索引course表的索引IX_course_cname
DROPINDEXcourse.ix_course_cname
三思考:
如何把索引IX_student_sname修改为唯一性索引?
可以使用企业管理器
或先删除索引,再重新建立。
*四思考建立索引的目的
1输入下列存储过程,该程序生成大量数据供测试:
createprocedureusp_makedataas
declare@nCntint,@sNovarchar(6),@snamevarchar(8)
set@nCnt=12000--计数器
while@nCnt<
999999
begin
set@nCnt=@nCnt+1
set@sNo=convert(varchar(6),@nCnt)
set@sName='
张'
+@sno
insertintostudent(sno,sname,ssex,sage)values(@sno,@sname,'
20)
end
return
2execusp_makedata--生成测试数据
3输入下述测试程序:
createprocedureusp_testas
declare@nCountint,@dataint
set@nCount=0
while@nCount<
100
select@data=count(*)fromstudentwheresname<
'
张3800'
orsname>
张8800'
set@nCount=@nCount+1
4测试
1)建立姓名的索引,查看运行时间(8秒).
createindexix_student_snameonstudent(sname)--建立索引
execusp_test
2)删除姓名索引,查看运行时间(2分11秒),比较与1)的时间长短。
dropindexstudent.ix_student_sname--删除索引
试验六更新数据
掌握insert,update,delete语句的使用。
一insert
1写出把下述学生的信息添加到student表中的命令。
赵茵
杨华
Insertintostudent(sno,sname,ssex,sage,sdept)values(‘4001‘,’赵茵’,’男’,20,’SX’)
Insertintostudent(sno,sname,ssex,sage)values(‘4002‘,’杨华’,’女’,21)
2批量插入数据
1)建立一个新表sc_name,有属性sno,sname,ssex,cno,grade。
CREATETABLEsc_name(
Snochar(6),
Snamevarchar(20),
Ssexchar
(2),
cnochar(4),
gradeint)
2)把SX系学生的sno,sname,ssex,cno,grade插入到表sc_name中。
Insertintosc_name(sno,sname,ssex,cno,grade)
selectstudent.sno,sname,ssex,cno,gradefromstudent,sc
wherestudent.sno=sc.snoandsdept=’SX’
3)察看sc_name表的数据
select*fromsc_name
二Update
1修改0001学生的系科为:
JSJ
Updatestudentsetsdept=’JSJ’wheresno=’0001’
2把陈小明的年龄加1岁,性别改为女。
Updatestudentsetsage=sage+1,ssex=’女’wheresname=’陈小明’
3修改李文庆的1001课程的成绩为93分
updatescsetgrade=93wherecno=’1001’andsnoin(
selectsnofromstudentwheresname=’李文庆’)
4把“数据库原理”课的成绩减去1分
updatescsetgrade=grade-1wherecnoin(
selectcnofromcoursewherecname=’数据库原理’)
三Delete
1删除所有JSJ系的男生
deletefromstudentwheresdept=’JSJ’
2删除“数据库原理”的课的选课纪录
Deletefromscwherecnoin(selectcnofromcoursewherecname=’数据库原理’)
思考:
修改数据的命令与修改表结构的命令有何区别?
试验七Sql查询语句
目的:
掌握Select查询语句。
一单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
selectsno,sname,sagefromstudent
wheresagebetween19and21andssex=’女’orderbysagedesc
2查询姓名中第戎2个字为“明”字的学生学号、性别。
selectsname,ssexfromstudentwheresnamelike‘_明%’
3查询1001课程没有成绩的学生学号、课程号
selectsno,cnofromscwheregradeisnullandcno=’1001’
4查询JSJ、SX、WL系的学生学号,姓名,结果按系及学号排列
selectsno,snamefromstudentwheresdeptin(‘JSJ’,’SX’,’WL’)
orderbysdept,sno
5按10分制查询学生的sno,cno,10分制成绩
(1-10分为1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLSERVER 实验 练习