SQLServer实验手册.docx
《SQLServer实验手册.docx》由会员分享,可在线阅读,更多相关《SQLServer实验手册.docx(31页珍藏版)》请在冰豆网上搜索。
SQLServer实验手册
SQL-Server实验
第一部分企业管理器的使用
试验一注册服务器
目的:
1掌握利用企业管理器管理数据库服务器。
一启动数据库服务器
打开服务管理器,“开始/继续“键为绿色。
二注册数据库服务器
1打开“企业管理器”
2在企业管理器中的左边,选中“SqlServer组”。
3如已经有一台服务器,则那台服务器就是你的数据库服务器。
选择“删除sqlserver注册”,删除该服务器。
4然后,在“sqlserver组”上用Mouse右键弹出菜单,选择“新建注册”,
5输入你的计算机的名字。
使用sqlserver身份验证,用户名:
sa,密码:
空。
注意:
如该服务器的验证模式为:
仅windows,则需要更改为:
混合模式。
三更改验证模式
1使用window身份验证模式注册服务器。
2然后选中该台服务器,按右键,选择“属性”,再选“安全”
3设置身份验证模式为混合模式:
Sqlserver和windows
试验二创建数据库
目的:
1掌握利用企业管理器创建、维护数据库。
了解sqlserver的数据库的存储结构。
要求:
1建立数据库
2修改数据库
3删除数据库
一建立school数据库
1使用SQL-Server的企业管理器创建数据库school。
要求记录:
1)数据库school对应的数据库文件在那个文件夹。
2)数据库school对应的数据库文件名。
2删除School数据库
二建立sale数据库
1建立sale数据库,要求数据库存储在c:
\data文件夹下,初始大小为5MB,增量为1MB。
2使用SQL-Server的企业管理器,将数据库的每次增量改为20%。
思考:
1如何将数据库school从一台计算机部署到其他计算机上。
2如何将数据库sale的文件存储在c盘和d盘。
试验三创建表
目的:
1掌握掌握利用企业管理器创建、维护表的方法。
2了解SQLServer的数据类型。
要求:
1建立表2修改表结构3删除表
一在数据库school中利用企业管理器建立student,sc,course表。
学生表、课程表、选课表属于数据库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,2
二设定主码
1Student表的主码:
sno
2Course表的主码:
cno
3Sc表的主码:
sno,cno
三表结构修改
1在student表中添加列:
家庭地址address长度为60varchar型
入学日期inDate日期型
注意:
设定后保存。
2将家庭地址address长度为50
3删除student表的inDate列
4删除表sc的主码
四删除表
1删除表SC
五设定约束
1student表中的姓名不可为空
2student表中的系科不可为空,默认值为‘JSJ’
思考:
1student表的性别不能为空且取值范围为{男,女},年龄必须大于16岁
实验四数据输入
目的:
1掌握利用企业管理器进行数据处理的方法。
要求:
1数据数据2修改数据3删除记录
一输入数据
1student表数据输入
学号
姓名
性别
年龄
系科
5001
赵强
男
20
SX
5002
杨丽华
女
21
JSJ
5001
李静
女
22
SX
记录输入过程中遇到的问题。
2course表数据的输入
Cno
Cname
Cpno
Ccredit
1801
C语言
4
1802
数据结构
1081
4
3SC表数据的输入
Sno
Cno
Grade
5001
1801
90
5001
1802
79
5002
1801
91
5008
1801
99
记录输入过程中遇到的问题。
二修改数据
1把5001学生的年龄改为22岁
2把5001学生的1802课程的成绩为该81分
三删除记录
1把5001学生的年龄改为22岁
2把sc表中5001学生的1802课程的记录删除
思考:
1sc表中5008这一条记录的存在是否合理?
原因?
如何可以避免此种情况发生。
实验五登录到数据库服务器
目的:
1了解如何登录到另外一台数据库服务器。
一察看其他电脑的服务器名
1打开服务管理器,察看你需要登录的数据库服务器的服务器名。
2登录服务器
1)在企业管理器中的左边,选中“SqlServer组”,Mouse右键弹出菜单,选择“新建注册”,
2)输入你想要登录的服务器名字。
3)使用sqlserver身份验证,用户名:
sa,密码:
空。
注意:
如该服务器的验证模式为:
仅windows,则需要更改为:
混合模式。
3注册成功后
1)进入该服务器的school数据库。
2)打开表student表,把0001学生的年龄改为24岁。
3)再在那台服务器上查询0001学生的年龄。
第二部分SQL语言
试验一数据库创建
目的:
1掌握利用SQL语言进行数据库的创建、维护。
2sp_helpdb命令
要求:
1创建数据库2修改数据库3删除数据库
一建立school数据库
1使用查询分析器创建数据库school
2使用SP_helpdb查询数据库School的信息
3使用SQL-Server的企业管理器查看数据库school的信息。
4记录:
1)school数据库文件所在的文件夹。
2)school数据库的文件名
二删除School数据库
1使用查询分析器删除数据库school
2使用SQL-Server的企业管理器删除数据库school。
三createDatabase深入研究
1建立school数据库,要求数据库存储在c:
\data文件夹下,初始大小为5MB,增量为1MB。
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。
三使用SP_HELP查看表student的表结构
利用企业管理器查看表sc的表结构
四利用sql语句表结构修改
1在student表中添加列:
家庭地址address长度为60varchar型
入学日期inDate日期型
完成后用sp_help查看是否成功。
2将家庭地址address长度为50
完成后用sp_help查看是否成功。
3删除student表的inDate列
五删除表
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。
二使用SP_HELP查看表student的主码名,约束名,并记录。
使用SP_HELP查看表sc的主码名,外码名,并记录。
三利用altertable添加、删除完整性约束
1删除SC的主码,sc表的主码名为pk_sc
复习在Sql-Server企业管理器中如何完成。
2删除SC表参照course表的外码。
如何知道SC表参照course表的外码的名字。
3添加SC表的主码。
主码名为PK_SC
4添加SC表的Cno的外码,参照表Course的Cno.
5加自定义约束:
表SC的成绩只能在0–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
●输入上述数据,记录出现的问题,说明原因。
●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
●输入上述数据,记录出现的问题,说明原因。
●select*fromstudent查看你输入了那些数据。
2course表数据的输入
Cno
Cname
Cpno
Ccredit
1085
C++
9
1086
语文
1086
3
●输入上述数据,记录出现的问题,说明原因。
●select*fromstudent查看你输入了那些数据。
3SC表数据的输入
Sno
Cno
Grade
3002
1081
128
●输入上述数据,记录出现的问题,说明原因。
●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
记录试验结果.,写出出现此结果的原因.
2)在student表中添加新行
Sno
Sname
Ssex
Sage
Sdept
4003
赵辉
男
21
SX
记录试验结果.,写出出现此结果的原因.
3)删除student表的4001,4002学生
记录试验结果.,写出出现此结果的原因.
思考:
●删除SC表的记录有限制吗?
●采取什么技术能使不能成功执行的命令变得可以执行,且使数据库保持数据完整性。
4)把student表的学号4003改为4018,4001改为4021。
记录试验结果.,写出出现此结果的原因.
思考:
采取什么技术能使本题不能执行的命令可以执行,且使数据库保持数据完整性。
5)把sc表中的如下记录的学号从4001改为4011。
Sno
Cno
Grade
4001
1088
90
记录试验结果.,写出出现此结果的原因.
●如不成功,则可以采取什么方法来实现此要求。
●如不成功,那么把4001修改为4003,能成功吗?
思考:
参照完整性规则中,外码可以为空,但SC表中的外码可以为空吗?
为什么?
举一个外码可以为空的例子。
试验五索引
目的:
掌握索引的建立、删除的方法。
一创建索引
1建student的索引
为姓名建立索引,索引名:
Ix_student_sname
为系科建立索引,索引名:
Ix_student_sdept
2SC的索引
为课程号建立索引:
ix_sc_cno
3Course的索引
为课程名建立唯一性索引:
Ix_course_cname
4如何SP_HELP查看索引刚才建立的索引?
如何在企业管理器中查看索引?
二删除索引course表的索引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
2批量插入数据
1)建立一个新表sc_name,有属性sno,sname,ssex,cno,grade。
2)把SX系学生的sno,sname,ssex,cno,grade插入到表sc_name中。
3)察看sc_name表的数据
二Update
1修改0001学生的系科为:
JSJ
2把陈小明的年龄加1岁,性别改为女。
2修改李文庆的1001课程的成绩为93分
3把“数据库原理”课的成绩减去1分
三Delete
1删除所有JSJ系的男生
2删除“数据库原理”的课的选课纪录
思考:
修改数据的命令与修改表结构的命令有何区别?
试验七Sql查询语句
目的:
掌握Select查询语句。
一单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
2查询姓名中第2个字为“明”字的学生学号、性别。
3查询1001课程没有成绩的学生学号、课程号
4查询JSJ、SX、WL系的年龄大于25岁的学生学号,姓名,结果按系及学号排列
5按10分制查询学生的sno,cno,10分制成绩
(1-10分为1,11-20分为2,30-39分为3,。
。
。
90-100为10)
6查询student表中的学生共分布在那几个系中。
(distinct)
7查询0001号学生1001,1002课程的成绩。
二统计
1查询姓名中有“明”字的学生人数。
2计算‘JSJ’系的平均年龄及最大年龄。
3查询学生中姓名为张明、赵英的人数
4计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列
5计算1001,1002课程的平均分。
6查询平均分大于80分的学生学号及平均分
7统计选修课程超过2门的学生学号
8统计有10位成绩大于85分以上的课程号。
9统计平均分不及格的学生学号
10统计有大于两门课不及格的学生学号
三连接
1查询JSJ系的学生选修的课程号
2查询选修1002课程的学生的学生姓名(不用嵌套及嵌套2种方法)
3查询数据库原理不及格的学生学号及成绩
4查询选修“数据库原理”课且成绩80以上的学生姓名(不用嵌套及嵌套2种方法)
5查询平均分不及格的学生的学号,姓名,平均分。
6查询女学生平均分高于75分的学生姓名。
7查询男学生学号、姓名、课程号、成绩。
(一门课程也没有选修的男学生也要列出,不能遗漏)
四嵌套、相关及其他
1查询平均分不及格的学生人数
2查询没有选修1002课程的学生的学生姓名
3查询平均分最高的学生学号及平均分(2种方法TOP,any,all)
*4