数据库实验报告

1、实验一创立数据库及关系表一、实验目的1 .掌握SQLServer数据库治理系统的使用,能够中该环境中进行日常数据库操作；2 .掌握在SQLServer中使用图形化工具创立数据库的方法；3 .掌握建立关系表的语句,掌握定义主码约束及外码约束的语句；4 .掌握修改表结构的语句.二、实验要求1 .了解SQLServer数据库的组成,会使用图形化工具创立数据库.2 .编写建立表及主、外码约束的T-SQL语句,并执行这些语句,在数据库中建立符合要求的关系表.3 .编写修改表结构的语句.三、实验内容和步骤1 .创立符合如下条件的数据库：数据库的名字为：Students数据文件的逻辑文件名为：Student

2、s_dat,存放在D:Test录下假设D:盘中无此子目录,可先建立此目录,然后再创立数据库.；文件的初始大小为：5MB增长方式为自动增长,每次增加1MB日志文件的逻辑文件名字为：Students_log,也存放在D:Test目录下；日志文件的初始大小为：2MB日志文件的增长方式为自动增长,每次增加10%2 .在已建立的Students数据库中,写出创立满足下述条件的四张表的SQL语句,并查看执行结果.Student表结构列名说明数据类型约束Sno学号普通编码定长字符串,长度为7主码Sname姓名普通编码定长字符串,长度为10非空Ssex性别普通编码定长字符串,长度为2Sage年龄微整型tiny

3、intSdept所在系普通编码不定长字符串,长度为20Spec专业普通编码定长字符串,长度为10Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串,长度为10主码Cname课程名普通编码不定长字符串,长度为20非空Credit学时数整型Semester学分小整型SC表结构列名说明数据类型约束Sno学号普通编码定长字符串,长度为7主码,引用Student的外码Cno课程号普通编码定长字符串,长度为10主码,引用Course的外码Grade成绩小整型Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串,长度为8非空Tname教师名普通编码定长字符串,长度为10非

4、空Dept所在部门普通编码不定长字符串,长度为20Salary工资定点小数,小数点前4位,小数点后2位Birthday出生日期小日期时间型(smallldatetime)Createtablestudent(Snochar(7)primarykey,Snamenchar(10)notnull,Ssexnchar(2),Sagetinyint,Sdeptnvarchar(20),Specchar(10)createtablecourse(Cnochar(10),Cnamenvarchar(20)notnull,Creditint,Semestertinyint,Primarykey(Cno)cr

5、eatetablesc(Snochar(7)notnull,Cnochar(10)notnull,Gradetinyint,primarykey(Sno,Cn0,foreignkey(Sno)referencesStudent(Snc),foreignkey(Cno)referencesCourse(Cno),)createtableteacher(Tnochar(8)notnull,Tnamechar(10)notnull,Deptnvarchar(20),Salarynumeric(6,2),Birtherysmalldatetime)执行结果:2.写出实现如下操作的SQL®句,

6、并查看执行结果：(1)在SC表中添加一个新的修课类别列：列名为：XKLB类型为chaK4).altertablescaddXKLBchar(4)(2)将Course表中的Credit列的类型改为：tinyint(微整型,取值范围在0255).altertablecoursealtercolumnCredittinyint(3)删除Student表的spec列.altertablestudentdropcolumnspec(4)为Teacher表添加主码约束,其主码列为：Tno.altertableteacheraddconstrainty_sprimarykey(Tno)3.执行以下语句,能否

7、成功为什么INSERTINTOSCVALUES(S001','C0T,88,'必修)答：不成功,由于错误显示sc表的sno学号列有外键约束,它是引用的student学生表里面的学号,所以执行前面的语句时s001'不存在于学生表中,所以执行不成功.假设在student学生表中插入学号s001'的学生信息,如：insertintostudentvalues('s001',王盼,'女,19,'数学系')那么再执行该语句时成功.四.实验小结：通过这次实验,学习了SQLServer数据库治理系统的使用的方法,通过自己创立数据

8、库和表,熟悉了上课中讲到的相关知识,在完成实验的同时对自己所编写的代码进行调试和查错,并且自己发现错误并改正,对知识的理解和掌握都有很大的帮助.实验二数据操作语句一、实验目的理解数据操作语句的功能.二、实验要求1 .根据实验一建立的数据库和关系表,导入局部数据；2 .编写实现数据操作功能的SQL语句.三、实验内容及步骤1 .查询计算机系年龄最大的三个学生的姓名和年龄.selecttop3sname,sage,sdeptfromstudentwheresdept='计算机系'orderbysagedesc2 .统计每个学生的选课门数,并按选课门数的递增顺序显示结果.selects

9、no,count(*)fromscgroupbysnoorderbycount(*)3 .列出总成绩超过200分的学生,要求列出学号、总成绩.selectsno,sum(grade)总成绩fromscgroupbysnohavingsum(grade)>2004 .查询成绩80分以上的学生的姓名、课程号和成绩,并将结果按成绩的降序排列.selectSname,cno,Gradefromscjoinstudentson=wheregrade>80orderbygradedesc5,查询学生的选课情况,要求列出每位学生的选课情况(包括未选课的学生),并列出学生的学号、姓名、课程号和测试

10、成绩.select,SnameCno,Gradefromstudentleftouterjoinscon=6 .列出“VET课程测试成绩前三名的学生的学号、姓名、所在系和测试成绩.selecttop3,snamesdept,gradefromstudentsjoinscon=joincoursecon=wherecname='VB'orderbygradedesc7,查询哪些课程没有人选,要求列出课程号和课程名.select,CnamefromCoursecleftjoinscon=whereisnull8 .查询计算机系学生测试成绩高于全体学生的总平均成绩的学生的姓名、测试的

11、课程名和测试成绩.selectsname,cname,gradefromstudentsjoinscon=joincoursecon=wheregrade>all(selectavg(grade)fromsc)andsdept='计算机系9 .查询VB测试成绩最低的学生的姓名、所在系和VB成绩.selecttop1sname,sdept,gradefromstudentsjoinscon=joincoursecon=wherecname='VB'orderbygrade10 .查询VB课程测试成绩高于VB平均成绩的学生的姓名.selectsnamefromstud

12、entsjoinscon=joincoursecon=wherecname='VB'andgrade>all(selectavg(grade)fromscjoincoursecon=wherecname='VB')11 .查询平均成绩大于80分的课程的名字和学分.selectcname,semesterfromcoursecwherecnoin(selectfromscgroupbyhavingavg(grade)>80)12 .统计每门课程的选课人数,包括有人选的课程和没有人选的课程,列出课程号,选课人数及选课情况,其中选课情况为：如果此门课程的选

13、课人数超过100人,那么显示“人多；如果此门课程的选课人数在40100,那么显示“一般；如果此门课程的选课人数在140,那么显示“人少；如果此门课程没有人选,那么显示“无人选select,count选课人数,casewhencount(*)>100then'人多'whencount(*)between40and100then'一般'whencount(*)between1and40then'人少'whencount(*)=0then'无人选'endas选课情况fromscgroupby13 .查询至少选修了“9512102号

14、学生选修的全部课程的学生,列出学号和所选的课程号.selectsno,cnofromscsc1wherenotexists(select*fromcoursecjoinscon=wheresno='9512102,andnotexists(select*fromscwhere=and=)14 .查询至少选修了第2学期开设的全部课程的学生的姓名和所在系.selectsname,sdeptfromstudentswherenotexists(select*fromcoursecwhere=24分;andnotexists(select*fromscwhere=and=)15 .修改“VET

15、课程的测试成绩,如果是计算机系的学生,那么增加如果是信息系的学生那么增加8分,其他系的学生增加10分cupdatescsetgrade=grade+casesdeptwhen'计算机系"then4when'信息系"then8else10endfromstudentsjoinscon=joincoursecon=16 .删除修课成绩小于50分的学生的修课记录deletefromscwheregrade<5017 .删除信息系修课成绩小于50分的学生的修课纪录,分别用子查询和多表连接形式实现.-子查询deletefromscwheresnoin(selec

16、tsnofromstudentwheresdept='信息>系')andgrade<50-多表连接deletefromscfromscjoinstudentson=wheresdept='信息>系'andgrade<5018 .将所有选修了“c01课程的学生的成绩加10分updatescsetgrade=grade+10fromscwherecno='c01'19 .将计算机系所有选修了计算机文化学课程的学生的成绩加10分,分别用子查询和多表连接形式实现.-多表连接updatescsetgrade=grade+10from

17、scjoinstudentson=joincoursecon=wherecname='计算机文化学'andsdept='计算机系'-子查询updatescsetgrade=grade+10wheresnoin(selectsnofromstudentwheresdept='计算机系'andcnoin(selectcnofromcoursewherecname='计算机文化学')20 .删除“VB测试成绩最低的学生的VB修课记录.Deletefromscwherecno=(selectcnofromcoursewherecname=

18、'VB')Andgrade=(selectmin(grade)fromscwherecno=(selectcnofromcoursewherecname='VB')21 .修改高等数学的测试成绩,修改规那么如下：如果是计算机系学生,那么加10分,如果是信息系学生那么加5分,如果是数学系学生那么分数不变.updatescsetgrade=grade+casesdeptwhen'计算机系'then10when'信息系'then5else0endfromstudentsjoinscon=joincoursecon=wherecname=

19、'高等数学'四.实验小结这次上机实验,主要对数据操作语句的根本运用和理解,在实验中从简单到复杂的问题中,一点点的掌握了SQL语言的功能,在实验中通过自己对语句的检验分析,还有老师的指导,根本掌握了数据库语言的运用,课后会多多稳固.实验三数据完整性约束一、实验目的理解数据完整性约束的功能.二、实验要求1 .掌握建立声明性数据完整性约束的SQL语句；2 .掌握建立过程完整性约束的SQL语句.三、实验内容1 .在实验一建立的Students数据库中,编写建立满足完整性要求的定义表的SQL语句,执行并观察执行结果.(说明：表名、列名均用给出的中文)(1)图书表,结构如下：书号：统一字符

20、编码定长类型,长度为6,主码,书名：统一字符编码可变长类型,长度为30,非空,第一普通编码定长字符类型,长度为10,非空,出版日期：小日期时间型,小于等于当前系统日期,印刷数量：小整型,取值范围：10005000,默认为4000,价格：定点小数,小数局部一位,整数局部3位.(2)书店表,结构如下：书店编号：统一字符编码定长类型,长度为6,主码,店名：统一字符编码可变长类型,长度为30,非空,：普通编码定长字符类型,12位长,取值形式：010-8位数字地址：普通编码可变长字符类型,40位长.前两个字符必须是：北京.(3)图书销售表(XSB,结构如下：书号：统一字符编码定长类型,长度为6,非空,书

21、店编号：统一字符编码定长类型,长度为6,非空,销售日期：小日期时间型,非空,默认值为系统当前日期,销售数量：微整型,取值大于0,主码(书号,书店编号,销售日期)；书号为引用“图书表的外码；书店编号为引用“书店表的外码.createtable图书表(书号nchar(6)PRIMARYKEY书名nvarchar(30)notnull,第一作者char(10)notnull,出版日期smalldatetimecheck(出版日期<=getdate(),印刷数量smallintdefault4000,价格numeric(4,1),check(印刷数量between'1000'an

22、d'5000')createtable书店表(书店编号nchar(6)primarykey,店名nvarchar(30)notnull,char(12)check(like'010-0-90-90-90-90-90-90-90-9'),地址nvarchar(40)check(地址like'北京')createtable图书销售表(书号nchar(6)notnull,书店编号nchar(6)notnull,销售日期smalldatetimedefaultgetdate()notnull,销售数量tinyintcheck(销售数量>0),pri

23、marykey(书号,书店编号,销售日期),foreignkey(书号)references图书表(书号),foreignkey(书店编号)references书店表(书店编号)2 .执行以下两条数据插入语句,是否都能执行成功为什么INSERTINTO图书表VALUES('T0001','数据库系统根底,张三,2021-2-1,3000,成功INSERTINTO图书表VALUES,T0002','计算机网络',张三,'2021-1-1',500,不成功,由于印刷数量是500,不满足规定的1000到50002 .执行以下两条数据插入

24、语句,是否执行成功为什么INSERTINTO书店表VALUES('S001','新华书店,12345678','北京市海淀区)不成功,不满足格式.修改后的：3 .执行以下两条数据插入语句,是否执行成功为什么INSERTINTO图书销售表(书号,书店编号,销售数量)VALUES(0001,'S001',20)不成功,由于书号为引用“图书表的外码.4 .编写实现如下功能的触发器：(1)限制测试成绩必须在0100分之间.createtriggertri_aonscafterinsert,updateasifexists(select*froms

25、cwheregradenotbetween0and100)rollback(2)限制不能将不及格的测试成绩改为及格createtriggertri_bonscafterupdateasifexists(select*frominsertedjoindeletedon=and=where>=60and<=60)rollback(3)限制不能删除选修课程测试成绩不及格学生的该门课程的选课及测试记录.createtriggertri_conscafterdeleteasifexists(select*fromdeletedwheregrade<60)rollback(4)限制每个学

26、生总的选课门数不能超过20门.createtriggertri_donscafterinsert,updateasifexists(selectcount(cno)fromscgroupbysnohavingcount(cno)>20)rollback(5)限制每个学期开设的课程总学分必须在2025之间.createtriggertri_eoncourseafterinsert,update,deleteasifexists(selectsum(credit)fromcoursegroupbysemesterhavingsum(credit)notbetween20and25)rollb

27、ack五.实验小结通过这次上机实验,把上课中所讲的数据完整性约束的相关知识得到了复习,通过上机更直观的理解了完整性约束的功能,通过添加删除约束的操作更好的掌握了有关知识,收获很大.实验四平安治理一、实验目的1.掌握平安治理的过程.2,掌握在SQLServer环境中实现平安治理的操作.二、实验要求1.能够在SQLServer环境中建立SQLServer身份验证的登录账户.2,能够建立数据库用户.3,编写给用户授权的SQL语句.4.验证平安治理的效果.三、实验内容及步骤1 .建立SQLServer认证的登录帐户：u1、u2、u3,并将u1、u2、u3映射为实验一建立的Students数据库的用户.2 .用u1建立一个新的连接,并在students数据库中执行下述语句,是否能成功为什么SELECT*FROMCourse不成功,由于u1没有被授予对象'Course'的查询权限.3