数据库原理实验报告文档格式.docx
《数据库原理实验报告文档格式.docx》由会员分享,可在线阅读,更多相关《数据库原理实验报告文档格式.docx(21页珍藏版)》请在冰豆网上搜索。
)
c.练习T-SQL中的函数
如求200的平方根
在查询分析器的查询窗口中输入
selectsqrt(200)
go
观察执行结果。
(14.142135623730951)
实验总结:
1、了解SQLServer2000的启动。
实验二
实验项目名称sql数据定义语言的应用
实验主要内容及方法数据库、数据表、视图、索引的定义、删除及修改
1、解并掌握SQL查询分析器及企业管理器的使用。
2、握数据库、基本表、索引的定义、删除与修改。
实验目的:
熟练掌握库、表的定义、删除与修改,为后继学习作准备。
表sc
Sno
Cno
Grade
S01
S02
S03
S04
…
C01
C03
C02
92
84
90
94
82
72
75
表Courses
Cname
Pre_Cno
Credits
C04
C05
C06
C07
英语
数据结构
数据库
DB_设计
C++
网络原理
操作系统
4
2
3
表Students
Sname
Ssex
Sage
Sdept
S05
S06
S07
S08
S09
S10
王平
刘华
范君
李伟
黄河
长江
文涛
杨丽
欧浩
刘洪
男
女
21
19
18
20
17
信息
计算机
数学
历史
中文
对于如下给定的数据表格进行实验
表中数据可以自行给定。
一、定义数据库
1.用企业管理器创建数据库
创建一个名称为Test的数据库,数据文件的初始大小设为5MB,文件增长增量设为2MB,文件增长方式设为自动增长,文件的增长上限设为50MB;
日志文件的初始大小为10MB,文件增长增量为1MB,文件的增长限制设为100MB。
具体创建步骤如下:
1)进入SQLServer企业管理器
2)展开目录树,在“数据库”选项上单击右键,选择“新建数据库”。
该窗口有3个选项卡:
常规、数据文件和事务日志。
输入数据库名称:
Test,点击数据文件选项卡,在数据文件中,你可以设定数据文件的名称,所在目录以及文件的大小等信息。
完成后点击事务日志选项卡,事务日志中,你可以设定日志文件的名称,所在目录以及文件的大小等信息。
接受系统的缺省值,点击确定按钮,完成数据库的新建。
2.用T-SQL语言创建数据库
CREATEDATABASEdatabase_name
其他配置可以采用默认设置。
[练习]:
按以上要求,在查询分析器中用T-SQL语言创建数据库“Test”,写出SQL语句。
Createdatabasetest
CREATEDATABASE进程正在磁盘'
test'
上分配0.75MB的空间。
test_log'
上分配0.49MB的空间。
3.查看并修改数据库的属性
(1)进入企业管理器,鼠标右键单击数据库“Test”,选择“属性”,查看选项。
将数据库Test数据文件的初始大小改为20MB,最大值改为80MB,数据增长改为5%。
二、定义基本表
例1建立表1.1所示的学生表Students,每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。
这里要求Sno和Sname不能为空值,且取值唯一。
CREATETABLEStudents/*列级完整性约束条件*/
(SnoCHAR(5)NOTNULL,/*Sno不能为空值*/
SnameCHAR(20)NOTNULL,/*Sname不能为空值*/
SsexCHAR
(2),
SageINT,
SdeptCHAR(15),
CONSTRAINTun_SnoUNIQUE(Sno),/*Sno取值唯一的约束*/
CONSTRAINTun_SnameUNIQUE(Sname));
/*Sname取值唯一的约束*/
在查询分析器中输入以上代码,点击执行按扭,即可得到空表Students.
说明:
在MicrosoftSQLServer2000的查询分析器(QueryAnalyzer)中使用单条SQL语句,其末尾不需要分号“;
”作为命令结尾标记。
通常,SQLServer2000对大多数末尾带有分号的SQL命令都能顺利执行,但对少数的SQL命令,末尾若带分号,则SQLServer2000会给出错误信息提示。
例2建立表1.2所示的课程表Courses,其属性名意义分别为Cno-课程号,Cname-课程名,Pre_Cno-先修课程号,Credits-学分。
CREATETABLECourses
(CnoCHAR(5)NOTNULL,/*Cno不能为空值*/
CnameCHAR(20)NOTNULL,/*Cname不能为空值*/
Pre_CnoCHAR(5),
CreditsINT,
CONSTRAINTun_CnoUNIQUE(Cno));
/*Cno取值唯一的约束*/
例3建立表1.3所示的成绩表Sc。
其中的属性名意义分别为Sno-学号,Cno-课程号和Grade-考试成绩。
CREATETABLESc
(SnoCHAR(5)NOTNULL,/*Sno不能为空值*/
CnoCHAR(5)NOTNULL,/*Cno不能为空值*/
GradeINT,
CONSTRAINTSno_CnoUNIQUE(Sno,Cno));
/*Sno+Cno取值唯一的约束*/
三、修改基本表
例4向基本表Students中增加“入学时间”属性列,其属性名为Sentrancedate,数据类型为DATETIME型。
ALTERTABLEStudentsADDSentrancedateDATETIME
例5将Sage(年龄)的数据类型改为SMALLINT型。
ALTERTABLEStudentsALTERCOLUMNSageSMALLINT
例6删除Sname(姓名)必须取唯一值的约束。
ALTERTABLEStudentsDROPCONSTRAINTun_Sname
A注意:
SQLServer2000增加了删除属性的命令。
比如,删除属性列Sentrancedate的命令为:
LTERTABLEStudentsDROPCOLUMNSentrancedate;
为了保证后面例子能够顺利运行,请大家一定将属性列Sentrancedate从Students表中删除。
为了调试SQL语句方便,这里没有在表Sc中增加参照完整性约束,甚至没有定义主键。
四、删除基本表
例7删除Students表。
DROPTABLEStudents;
此表删除后,请立即用例1将其建立起来,以便后面的例子使用。
五、建立索引
例8为学生选课数据库中的Students,Courses,Sc三个表建立索引。
其中Students表按Sno(学号)升序建唯一索引,Courses表按Cno(课程号)升序建唯一索引,Sc表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。
其语句为:
CREATEUNIQUEINDEXStu_SnoONStudents(Sno);
CREATEUNIQUEINDEXCou_CnoONCourses(Cno);
CREATEUNIQUEINDEXRep_ScnoONSc(SnoASC,CnoDESC);
例9在基本表Students的Sname(姓名)和Sno(学号)列上建立一个聚簇索引,而且Students中的物理记录将按照Sname值和Sno值的升序存放。
CREATECLUSTEREDINDEXStu_Sname_SnoONStudents(Sname,Sno);
六、删除索引
例10删除基本表Sc上的Rep_SCno索引。
DROPINDEXSc.Rep_Scno;
七、建立视图
例11建立数学系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有数学系的学生,视图的属性名为Sno,Sname,Sage,Sdept。
CREATEVIEWC_Student
AS
SELECTSno,Sname,Sage,Sdept
FROMStudents
WHERESdept=’数学’
WITHCHECKOPTION
例12建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。
本视图由三个基本表的连接操作导出,其SQL语句如下:
CREATEVIEWStudent_CR
SELECTStudents.Sno,Sname,Cname,Grade
FROMStudents,Sc,Courses
WHEREStudents.Sno=Sc.SnoANDSc.Cno=Courses.Cno
例13定义一个反映学生出生年份的视图。
CREATEVIEWStudent_birth(Sno,Sname,Sbirth)
ASSELECTSno,Sname,1996-Sage
八、删除视图
例14删除视图Student_CR。
DROPVIEWStudent_CR;
实验三
实验项目名称sql数据操纵语言的应用(6课时,综合)
实验主要内容及方法数据的各种操纵方法
(1)掌握sql数据插入、修改、删除和查询语句的一般格式。
(2)掌握sql数据插入、修改、删除和查询使用方法。
熟练掌握四大语句的使用。
一、插入数据
1)采用insert语句将实验二给定的表格中数据插入数据库。
表一:
INSERTINTOStudentsVALUES('
S01'
'
王平'
男'
21'
信息'
S02'
刘华'
女'
19'
S03'
范君'
18'
计算机'
S04'
李伟'
S05'
黄河'
数学'
S06'
长江'
20'
S07'
文涛'
17'
S08'
杨丽'
历史'
S09'
欧浩'
中文'
S10'
刘洪'
表二:
insertintoCourses(Cno,Cname,Credits)
VALUES('
C01'
英语'
4)
insertintoCoursesVALUES('
C02'
数据结构'
C05'
2)
C03'
数据库'
C04'
DB_设计'
3)
insertintoCourses(Cno,Cname,Credits)VALUES('
C++'
C06'
网络原理'
C07'
操作系统'
表三:
INSERTINTOScVALUES('
92)
84)
90)
94)
82)
72)
75)
(2)在数据库中定义一个关系History_Student,其关系模式与Students完全一样,试将关系Students中的所有元组插入到关系History_Student中去。
建立History_Student表
createtableHistory_Student
SdeptCHAR(15),);
查询Students,并将查询结果插入History_Student表。
insert
intoHistory_Student(Sno,Sname,Ssex,Sage,Sdept)
selectSno,Sname,Ssex,Sage,Sdept
fromStudents
二、修改数据
1)将学号为“S03”的学生年龄改为22岁,即要修改满足条件的一个元组的属性值。
updateStudents
setSage=22
whereSno='
2)将所有学生的年龄增加1岁。
即要修改多个元组的值。
setSage=Sage+1
3)将数学系所有学生的成绩置零。
注意:
由于学生所在系的信息在Students表中,而学习成绩在Sc表中,因此,可以将SELECT子查询作为WHERE子句的条件表达式。
updateSc
setgrade=0
where'
=
(selectSdept
whereStudents.Sno=Sc.Sno)
三、删除数据
1)删除学号为“S04”的学生选修的课号为“C02”的记录。
delete
fromsc
wheresno='
s04'
andcno='
c02'
2)删除所有学生的选课记录。
3)删除数学系所有学生的选课记录。
delete
(selectsdept
fromstudents
wherestudents.sno=sc.sno)
四、查询数据
1、无条件查询
1)查询全体学生的详细记录
select*
2)查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。
selectsname,sno,sdept
3)查询全体学生的姓名(Sname)、出生年份及学号(Sno)。
提示:
由于SELECT子句的<
目标列表达式>
不仅可以是表中的属性列,也可以是表达式,故可以查询经过计算的值。
selectsname,2010-sage
4)查询全体学生的姓名、出生年份和学号,要求用小写字母表示学号中的字母。
selectsname,2010-sage,lower(sno)
2、条件查询
1)查询数学系全体学生的学号(Sno)和姓名(Sname)。
selectsno,sname
wheresdept='
2)查询所有年龄在18-22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。
selectsname,sage
where18<
=sageandsage<
=22
3)查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。
wheresagenotbetween18and22
4)查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。
selectsno,sname,ssex
wheresdeptin('
自动化系'
5)查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。
selectsname,ssex
wheresdeptnotin('
6)查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。
selectsname,sno,ssex
wheresnamelike'
刘%'
7)查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。
selectcno,credits
fromcourses
wherecname='
8)假设某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。
selectsnocno
wheregradeisnull
9)查询所有有成绩的学生学号(Sno)和课程号(Cno)。
selectsno,cno
wheregradeisnotnull
3、查询结果排序
1)查询选修了C03号课程的学生的学号(Sno)和成绩(Grade),并按成绩降序排列。
selectsno,grade
wherecno='
c03'
orderbygradedesc
2)查询全体学生情况,查询结果按所在系的系名(Sdpet)升序排列,同一系中的学生按年龄(Sage)降序排列。
orderbysdept,sagedesc
4、集函数的使用
1)查询学生总人数。
selectcount(*)
2)查询选修了课程的学生人数。
selectcount(distinctsno)
3)计算选修C01号课程的学生平均成绩。
selectavg(grade)
c01'
4)查询选修C01号课程的学生最高分数。
selectmax(grade)
5、查询结果分组
1)求各个课程号(Cno)及相应的选课人数。
SELECTCno,COUNT(Sno)
FROMSc
GROUPBYCno;
2)查询选修了3门或3门以上课程的学生学号(Sno)。
selectsno
groupbysno
havingcount(*)>
=3
6、不同表之间的连接查询
1)查询每个学生及其选修课程的情况。
本查询实际上是涉及Students与Sc两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的。
selectstudents.*,sc.*
fromstudents,sc
wherestudents.