新SQLSERVER实验练习答案.docx
《新SQLSERVER实验练习答案.docx》由会员分享,可在线阅读,更多相关《新SQLSERVER实验练习答案.docx(48页珍藏版)》请在冰豆网上搜索。
新SQLSERVER实验练习答案
SQL-Server实验
答案
上海师范大学计算机系
第一部分企业管理器的使用
第二部分SQL语言
试验一数据库创建
目的:
1掌握利用SQL语言进行数据库的创建、维护。
2sp_helpdb命令
要求:
1创建数据库2修改数据库3删除数据库
一建立school数据库
1使用查询分析器创建数据库school
CreateDataBaseschool
2使用SP_helpdb查询数据库School的信息
3使用SQL-Server的企业管理器查看数据库school的信息。
4记录:
1)school数据库文件所在的文件夹。
2)school数据库的文件名
二删除School数据库
1使用查询分析器删除数据库school
DROPDATABASEschool
2使用SQL-Server的企业管理器删除数据库school。
三createDatabase深入研究
1建立school数据库,要求数据库存储在c:
\data文件夹下,初始大小为5MB,增量为1MB。
CREATEDATABASEschoolON(
Name=‘school_dat’,
Filename='c:
\sqldata\school.mdf',
SIZE=5,
FILEGROWTH=1)
2使用SQL-Server的企业管理器,将数据库的每次增量改为20%。
试验二创建表
目的:
1掌握利用SQL语言创建表的方法。
2sp_help命令
要求:
1创建表2修改表结构3删除表
一写出使用CreateTable语句创建表student,sc,course的SQL语句。
学生表、课程表、选课表属于数据库School,其各自得数据结构如下:
学生Student(Sno,Sname,Ssex,Sage,Sdept)
序号
列名
含义
数据类型
长度
1
Sno
学号
字符型(char)
6
2
Sname
姓名
字符型(varchar)
8
3
Ssex
性别
字符型(char)
2
4
Sage
年龄
整数(smallint)
5
sdept
系科
字符型(varchar)
15
课程表course(Cno,Cname,Cpno,Ccredit)
序号
列名
含义
数据类型
长度
1
Cno
课程号
字符型(char)
4
2
cname
课程名
字符型(varchar)
20
3
Cpno
先修课
字符型(char)
4
4
Ccredit
学分
短整数(tinyint)
学生选课SC(Sno,Cno,Grade)
序号
列名
含义
数据类型
长度
1
Sno
学号
字符型(char)
4
2
Cno
课程名
字符型(char)
6
3
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),
Cnochar(4),
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。
createtableStudent(
Snochar(6),
Snamechar(10)notnullunique,
Ssexchar
(2)check(ssex='男'orssex='女'),
Sagesmallintcheck(sage>16),
Sdeptchar(10)notnulldefault'JSJ',
primarykey(sno)
)
createtablecourse(
Cnochar(4),
Cnamechar(16),
Cpnochar(4),
Ccreditintcheck(Ccredit>=0andCcredit<=5),
check(cno<>cpno),--约束
primarykey(cno)
)
createtableSC(
Snochar(6),
Cnochar(4),
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加自定义约束:
表SC的成绩只能在0–100分之间。
五使用
select*fromstudent查看信息
select*fromcourse查看信息
select*fromsc查看信息
试验四数据完整性试验
目的:
1理解实体完整性、参照完整性、用户自定义完整性的作用
2特别掌握外码的作用。
要求:
记录试验中遇到的问题,并写出原因。
实验前需要利用试验三完成的脚本文件createSchool.sql,重新建立数据库school。
一实体完整性
1student表数据输入
学号
姓名
性别
年龄
系科
3001
赵达
男
20
SX
3002
杨丽
女
21
JSJ
3001
李寅
女
21
SX
●输入上述数据,记录出现的问题,说明原因。
第三行不能输入。
Student的主码为sno,因此sno列的值不能重复。
●select*fromstudent查看你输入了几行数据。
2course表数据的输入
Cno
Cname
Cpno
Ccredit
1081
电子商务
4
3SC表数据的输入
Sno
Cno
Grade
3001
1081
90
3001
1081
79
输入上述数据,记录出现的问题,说明原因。
二用户自定义完整性约束
表student有用户自定义约束:
性别不能为空且取值范围为{男,女}
年龄大于16岁
表course的自定义约束:
Ccredit取值范围{0,1,2,3,4,5}
课程表的每一行的Cno与cpno不可相同
1student表数据输入
学号
姓名
性别
年龄
系科
3005
赵达
男
14
SX
3006
杨丽
南
21
JSJ
●输入上述数据,记录出现的问题,说明原因。
3005学生不能输入,年龄问题,有约束sage>16
3006学生不能输入,性别不对。
●select*fromstudent查看你输入了那些数据。
2course表数据的输入
Cno
Cname
Cpno
Ccredit
1085
C++
9
1086
语文
1086
3
●输入上述数据,记录出现的问题,说明原因。
1086不能输入,因为有约束check(cno<>cpno)
●select*fromstudent查看你输入了那些数据。
3SC表数据的输入
Sno
Cno
Grade
3002
1081
128
●输入上述数据,记录出现的问题,说明原因。
3002这条数据不能输入,因为grade不能大于100分
●select*fromstudent查看你输入了那些数据。
三参照完整性约束
●掌握表之间建立外码后,对被参照表的如下操作会有何影响:
修改主码、插入新行、删除新行?
●对参照表添加新行、删除行、修改外码值有何影响?
●掌握级联修改、级联删除的概念。
注意:
表SC的Sno是外码,参照student的sno。
表SC的Cno是外码,参照course的cno。
1输入实验前的数据
学生表Student
Sno
Sname
Ssex
Sage
Sdept
4001
赵尹
男
20
SX
4002
杨开
女
20
JSJ
课程表course
Cno
Cname
Cpno
Ccredit
1088
Java
5
1089
数学
3
学生选课SC
Sno
Cno
Grade
4001
1088
90
4002
1088
86
2试验过程
1)在SC表中添加新行:
Sno
Cno
Grade
4001
1066
76
记录试验结果.,写出出现此结果的原因.
不能添加,因为在cno是外码,参照course的cno,但在course中没有1066课程。
2)在student表中添加新行
Sno
Sname
Ssex
Sage
Sdept
4003
赵辉
男
21
SX
记录试验结果.,写出出现此结果的原因.
可以输入
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。
Sno
Cno
Grade
4001
1088
90
记录试验结果.,写出出现此结果的原因.
不能修改,因为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
begin
select@data=count(*)fromstudentwheresname<'张3800'orsname>'张8800'
set@nCount=@nCount+1
end
4测试
1)建立姓名的索引,查看运行时间(8秒).
createindexix_student_snameonstudent(sname)--建立索引
execusp_test
2)删除姓名索引,查看运行时间(2分11秒),比较与1)的时间长短。
dropindexstudent.ix_student_sname--删除索引
execusp_test
试验六更新数据
目的:
掌握insert,update,delete语句的使用。
一insert
1写出把下述学生的信息添加到student表中的命令。
学号
姓名
性别
年龄
系科
4001
赵茵
男
20
SX
4002
杨华
女
21
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分制成绩