数据库原理实验报告

题目：数据库原理实验报告

课本内容练习创建表createtableStudent( Snochar(9)primarykey, Snamechar(20)unique, Ssexchar(2), Sagesmallint, Sdeptchar(20))createtableCourse( Cnochar(4)primarykey, Cnamechar(40), Cpnochar(4), Ccreditsmallint, foreignkey(Cpno)referencesCourse(Cno));createtableSC( Snochar(9), Cnochar(4), Gradesmallint, primarykey(Sno,Cno), foreignkey(Sno)referencesStudent(Sno), foreignkey(Cno)referencesCourse(Cno))createtableDept_age( Sdeptchar(15), Avg_agesmallint,)插入insertintoStudent(Sno,Sname,Ssex,Sage,Sdept)values('200215121','李勇','男',20,'CS')insertintoStudent(Sno,Sname,Ssex,Sage,Sdept)values('200215122','刘晨','女',19,'CS')insertintoStudent(Sno,Sname,Ssex,Sage,Sdept)values('200215123','王敏','女',18,'MA')insertintoStudent(Sno,Sname,Ssex,Sage,Sdept)values('200215125','张立','男',19,'IS')insertintoCourse(Cno,Cname,Cpno,Ccredit)values('2','数学',NULL,2)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('6','数据处理',NULL,2)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('4','操作系统','6',3)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('7','PASCAL语言','6',4)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('5','数据结构','7',4)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('3','信息系统','1',4)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('8','DB_Design','7',4)insertintoSC(Sno,Cno,Grade)values('200215121','1',92)insertintoSC(Sno,Cno,Grade)values('200215121','2',85)insertintoSC(Sno,Cno,Grade)values('200215121','3',88)insertintoSC(Sno,Cno,Grade)values('200215122','2',90)insertintoSC(Sno,Cno,Grade)values('200215122','3',80)查询selectSno,SnamefromStudent;/*查询经过计算的值*/selectSname,2014-SagefromStudent/*指定字符通过小写输出*/selectSname,'yearofbirth',2014-Sage,lower(Sdept)fromStudent/*为查询结果指定列标题*/selectSnamename,'yearofbirth'birth,2014-Sagebirthday,lower(Sdept)departmentfromStudent/*消除取值重复的行*/selectdistinctSnofromSC/*比较大小查询*/selectSname,SagefromStudentwhereSage>19selectSnamefromStudentwhereSdept='CS'/*确定范围*/selectSname,Sdept,SagefromStudentwhereSagebetween20and30selectSname,Sdept,SagefromStudentwhereSagenotbetween20and30/*确定集合*/selectSname,SsexfromStudentwhereSdeptin('CS','MA')/*字符匹配*/select*fromstudentwheresnolike'200215121'select*fromstudentwheresno<>'200215121'select*fromstudentwheresno!='200215121'/*不含有通配符的时候可以使用“=;<>;!=”代替like，notlike*/selectSname,Sno,SsexfromStudentwhereSnamelike'刘%'selectSnamefromStudentwhereSnamelike'欧阳__'/*注意一个汉字两个字符*/selectSname,SnofromStudentwhereSnamelike'__阳%'selectSname,SnofromStudentwhereSnamenotlike'刘%'/*字符匹配*/selectCno,CcreditfromCoursewhereCnamelike'DB\_Desigen'escape'\'/*转码字符由自己定义*//*涉及空值的查询*/selectSno,CnofromSCwhereGradeisnull/*注意is不能用=替代*/selectSno,CnofromSCwhereGradeisnotnull/*多重条件查询*/selectSnamefromStudentwhereSdept='CS'andSage<20/*and优先级比or高*/selectSname,SsexfromStudentwhereSdept='CS'orSdept='IS'orSdept='MA'/*有时in谓词可看做多个or的缩写*//*orderby子句*/selectSno,GradefromSCwhereCno='3'orderbyGradedesc/*asc:升序；desc：降序，缺省升序*/select*fromStudentorderbySdept,Sagedesc/*聚集函数*/selectcount(*)/*貌似distinct和*是不能同时使用的*/fromSCselectcount(distinctSno)fromStudentselectavg(Grade)fromSCwhereCno=1/*计算一号课程的平均成绩*/selectmax(Grade)fromSCwhereCno=2/*计算号课程的最高成绩*/selectsum(Ccredit)fromSC,CoursewhereSno='200215121'andSC.Cno=Course.Cno/*查询学号为的学生选修课程的总学分*//*groupby子句*/selectCno,COUNT(Sno)fromSCgroupbyCno/*having短语*/selectSnofromSCgroupbySnohavingCOUNT(*)>2/*连接查询*/selectStudent.*,SC.*fromStudent,SCwhereStudent.Sno=SC.Sno/*自然连接，两个表中不重复的属性名可以不加前缀*/selectStudent.Sno,Sname,Ssex,Sdept,Cno,GradefromStudent,SCwhereStudent.Sno=SC.Sno/*自身连接，要为表取别名，此时必须都要有前缀*/selectfirst.Cno,second.CpnofromCoursefirst,Coursesecondwherefirst.Cpno=second.Cno/*外连接*/selectStudent.Sno,Sname,Ssex,Sdept,Cno,GradefromStudentrightouterjoinSCon(Student.Sno=Sc.Sno)/*复合条件连接*/selectStudent.Sno,SnamefromStudent,SCwhereStudent.Sno=SC.SnoandSc.Cno='2'andSC.Grade>80/*复合条件连接*/selectStudent.Sno,Sname,Cname,GradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno/*嵌套查询*//*in谓词*/selectSno,Sname,SdeptfromStudentwhereSdeptin( selectSdept fromStudent whereSname='刘晨')/*此查询可以利用自身连接代替selectS1.Sno,S1.Sname,S1.SdeptfromStudentS1,StudentS2whereS1.Sdept=S2.SdeptandS2.name='刘晨'*/selectSno,SnamefromStudentwhereSnoin( selectSno fromSC whereCnoin( selectCno fromCourse whereCname='信息系统' ))/*以上代码的连接方式*/selectStudent.Sno,SnamefromStudent,Course,SCwhereCourse.Cname='信息系统'andSC.Cno=Course.CnoandStudent.Sno=Sc.Sno/*带有比较运算符的子查询*//*相关子查询*/selectSno,CnofromSCxwhereGrade>=( selectavg(Grade) fromSCy wherey.Sno=x.Sno)/*带有any(some)或all谓词的子查询*/selectSname,SagefromStudentwhereSage<any( selectSage fromStudent whereSdept='CS')andSdept<>'CS'selectSname,SagefromStudentwhereSage<all( selectSage fromStudent whereSdept='Cs')andSdept<>'CS'/*带有exists谓词的子查询*/selectSnamefromStudentwhereexists( select* fromSC whereSno=Student.SnoandCno='1')selectSnamefromStudentStu1whereexists( select* fromStudentStu2 whereStu2.Sname='刘晨'andStu1.Sdept=Stu2.Sdept)更新/*插入数据*//*插入元组*/insertintoStudent(Sno,Sname,Ssex,Sdept,Sage)values('200215128','陈东','男','IS',18)/*插入子查询结果*/insertintoDept_age(Sdept,Avg_age) selectSdept,avg(Sage) fromStudent groupbySdept/*修改数据*/updateStudentsetSage=22whereSno='200215121'updateStudentsetSage=Sage+1/*带子查询的修改语句*/updateSCsetGrade=0where'CS'=( selectSdept fromStudent whereStudent.Sno=SC.Sno)/*删除数据*/deletefromSCwhere'CS'=( selectSdept fromStudent whereStudent.Sno=SC.Sno)视图/*建立视图*/createviewIS_StudentASselectSno,Sname,SagefromStudentwhereSdept='IS'withcheckoption/*增删改操作时要保证修改的行满足视图定义中的谓词条件*/createviewIS_S1(Sno,Sname,Grade)asselectStudent.Sno,Sname,GradefromStudent,SCwhereSdept='IS'andStudent.Sno=SC.SnoandSC.Cno='1'实验一SQLSERVER2000的基本使用实验目的和要求：通过本实验，使学生熟悉SQLSERVER2000的工作环境，尤其是SQLSERVER2000的企业管理器与查询分析器，熟悉对SQLSERVER2000的各种操作。具体地，掌握表（关系）和索引的建立方法；掌握表结构（关系模式）的修改方法。实验内容：在studentdb数据库中利用查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：①student（学生信息表）：主码列名数据类型宽度小数位空否取值范围备注Pksnochar5

N学号

snamechar10

N姓名

ssexchar2

Y性别

sagesmallintY不小于12年龄sdeptchar15Y系名createtablestudent( snochar(5)primarykeynotnull, snamechar(10)uniquenotnull, ssexchar(2), sagesmallintcheck(sage>=12), sdeptchar(15),)②course（课程表）：主码列名数据类型宽度小数位空否备注PkcnoChar2

N课程号

cnameChar20

Y课程名称

cpnoChar2

Y先行课号

ccreditsmallintY学分createtablecourse( cnochar(2)primarykey, cnamechar(20), cpnochar(2), ccreditsmallint, foreignkey(cpno)referencesCourse(cno))③sc（学生选课表）：主码列名数据类型宽度小数空否外码参照关系取值范围备注PksnoChar5

NFkstudent学号cnoChar2

NFkcourse课程号

gradeDecimal51Y0≤x≤100成绩createtablesc( snochar(5), cnochar(2), gradedecimal(5)constraintg1check(grade>=0andgrade<=100), primarykey(sno,cno), foreignkey(sno)referencesstudent(sno), foreignkey(cno)referencescourse(cno))在spjdb数据库中利用查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：①S（供应商信息表）：主码列名数据类型宽度小数位空否取值范围备注Pksnochar2

N供应商号

snamechar10

N供应商名称

statussmallintY大于0供应商状态citychar10Y所在城市createtableS( snochar(2)primarykey, snamechar(10)notnull, statussmallintcheck(status>0), citychar(10),)②P（零件信息表）：主码列名数据类型宽度小数位空否取值范围备注Pkpnochar2

N零件号

pnamechar10

N零件名称colorchar2Y颜色

weightsmallintY大于0重量createtableP( pnochar(2)primarykey, pnamechar(10)notnull, colorchar(2), weightsmallintcheck(weight>0),)③J（工程项目表）：主码列名数据类型宽度小数位空否取值范围备注Pkjnochar2

N工程项目号

jnamechar10

N工程项目名称citychar10Y所在城市createtableJ( jnochar(2)primarykey, jnamechar(10)notnull, citychar(2), weightsmallint,)④SPJ（供应情况表）：主码列名数据类型宽度小数空否外码参照关系取值范围备注PksnoChar2

NFkS供应商号pnoChar2

NFkP零件号jnoChar2

NFkJ工程项目号

qtysmallintYx>0成绩createtableSPJ( snochar(2)notnull, pnochar(2)notnull, jnochar(2)notnull, qtysmallintcheck(qty>0), primarykey(sno,pno,jno), foreignkey(sno)referencesS(sno), foreignkey(pno)referencesP(pno), foreignkey(jno)referencesJ(jno),)修改表结构，具体要求如下：将表course的cname列的数据类型改为varchar(40).altertablecoursealtercolumncnamevarchar(40)为表student增加一个新列:birthday(出生日期),类型为datetime,默认为空值altertablestudentaddbirthdaydatetimenull将表sc中的grade列的取值范围改为小于等于150的正数.altertablescdropconstraintg1altertablescaddconstraintg1check(grade<=150)为Student表的“Sex”字段创建一个缺省约束，缺省值为’男’altertablestudentaddconstraintssexcheck(ssexin('男'))为“Sdept”字段创建一个检查约束，使得所在系必须是’计算机’、’数学’或’信息’之一。altertablestudentaddconstraintsdeptcheck(sdeptIN('CS','MA','IS'))为Student表的“Sname”字段增加一个唯一性约束altertablestudentaddunique(sname);为SC表建立外键,依赖于Student表的fk_S_c约束。altertablescaddconstraintfkforeignkey(sno)referencesstudent(sno)禁止启用Student表的“Sdept”的CHECK约束ck_student。altertablestudentdropconstraintSdept分别建立以下索引(如果不能成功建立,请分析原因)在student表的sname列上建立普通降序索引.createindexstunameonstudent(snameDESC)在course表的cname列上建立唯一索引.createuniqueindexscnameoncourse(cnameDESC)在sc表的sno列上建立聚集索引.createclusteredindexssnoonsc(sno)未能成功创建，因为primarykey对应的列会自动生成一个聚簇索引，sc中sno是primarykey要想创建他的聚集索引必须先删他的主键约束在spj表的sno(升序),pno(升序)和jno(降序)三列上建立一个普通索引.createindexsssonSPJ(snoASC,pnoASC,jnoDESC)错误总结：在创建表格添加约束时，最好为约束命名，便于以后更改约束。添加外键时，参与构造外键关系的列必须定义为具有同一长度和小数位数。实验二SQL语言的基本操作一、实验目的和要求：掌握利用SQL语句完成各种查询操作的能力。重点掌握用SELECT语句进行各种查询；掌握INSERT语句的用法。二、实验内容：向数据库中插入数据student表insertintostudent(sno,sname,ssex,sage,sdept)values('15121','李勇','男',20,'CS')insertintostudent(sno,sname,ssex,sage,sdept)values('15122','刘晨','女',19,'CS')insertintostudent(sno,sname,ssex,sage,sdept)values('15123','王敏','女',18,'MA')insertintostudent(sno,sname,ssex,sage,sdept)values('15125','张立','男',19,'IS')course表insertintocourse(cno,cname,cpno,ccredit)values('2','数学',NULL,2)insertintocourse(cno,cname,cpno,ccredit)values('6','数据处理',NULL,2)insertintocourse(cno,cname,cpno,ccredit)values('4','操作系统','6',3)insertintocourse(cno,cname,cpno,ccredit)values('7','PASCAL语言','6',4)insertintocourse(cno,cname,cpno,ccredit)values('5','数据结构','7',4)insertintocourse(cno,cname,cpno,ccredit)values('1','数据库','5',4)insertintocourse(cno,cname,cpno,ccredit)values('3','信息系统','1',4)insertintocourse(cno,cname,cpno,ccredit)values('8','DB_Design','7',4)sc表insertintosc(sno,cno,grade)values('15121','1',92)insertintosc(sno,cno,grade)values('15121','2',85)insertintosc(sno,cno,grade)values('15121','3',88)insertintosc(sno,cno,grade)values('15122','2',90)insertintosc(sno,cno,grade)values('15122','3',80)Select语句用SQL语句完成一下的要求：查询信息系（IS）的所有学生信息select*fromstudentwheresdept='is'查询选修了“数学”课的所有学生名单selectsnamefromstudentwheresnoin( selectsno fromsc wherecno='2')查询至少选修了一门其直接先行课为5号课程的学生的姓名。selectsnamefromstudentwheresnoin( selectsno fromsc wherecnoin( selectcno fromcourse wherecpno='5' ))查询全体学生的姓名和出生年份。selectsname,2013-sage出生年份fromstudent查询所有姓王的学生。select*fromstudentwheresnamelike'王%'查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。selectsname,gradefromstudent,scwherestudent.sno=o='3'orderbygradedesc查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select*fromstudentorderbysdept,sagedesc计算2号课程的平均成绩。selectavg(grade)fromscwherecno='2'查询选修了2号课程的学生的最高成绩。selectmax(grade)fromscwherecno='2'求各个课程号及相应的选课人数。selectcno,count(sno)fromscgroupbycno查询至少选修了3门课程以上的学生学号。selectsnofromscgroupbysnohavingcount(*)>=3查询“数据库”的间接先行课。o,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=o查询平均成绩最高的学生的学号和姓名。selectsno,snamefromstudentwheresnoin( selecttop1sno from( selectsno,avg(grade)score fromscgroupbysno)a orderbyscoredesc)查询数学成绩最高的学生的学号和姓名。selectstudent.sno,snamefromstudent,scwherestudent.sno=sc.snoand grade=( selectmax(grade) fromsc,course wherecname='数学'o=o )查询出成绩最低学号最大的学生学号。selectsnofromscwheresno=( selectmax(sno) fromsc)andgradein( selectmin(grade) fromsc )查询成绩高于学生平均成绩的记录。selectsno,gradefromscwheregrade>=( selectavg(grade) fromsc )查询至少选修了1号课程和3号课程的学生学号。selectsnofromscwheresnoin( selectsno fromsc wherecno=1)andcno=3查询只选修了1号课程和3号课程的学生学号。selecta.snofromsca,scbwherea.sno=b.snoand o='2'and o='3'and a.snoin( selectsno fromsc groupbysno havingcount(*)=2)查询没有选修1号课程的学生姓名。selectsnamefromstudentwherenotexists( select* fromsc wherecno='1'andsno=student.sno )查询选修了全部课程的学生姓名。selectsnamefromstudentwherenotexists( select* fromcourse wherenotexists( select* fromsc wheresno=student.snoandcno=o) )查询至少选修了95002所选修的全部课程的学生学号。selectsnamefromstudentwheresnoin( selectdistinctsno fromscscx wherenotexists( select* fromscscy wherescy.sno='95002'and notexists( select* fromscscz wherescz.sno=o=o)))查询没有不及格课程的学生的学号和姓名。selectsno,snamefromstudentwheresnoin( selectsno fromsc groupbysno havingmin(sc.grade)>=60)查询没有不及格学生的课程的课程号和课程名。selectcno,cnamefromcoursewherecnoin( selectcno fromsc groupbycno havingmin(sc.grade)>=60)建立信息系学生视图，并从视图中查询年龄最大的学生记录。建立信息系学生视图：createviewCS_studentasselect*fromstudentwheresdept='CS'从视图中查询年龄最大的学生记录select*fromCS_studentwheresage=( selectmax(sage) fromCS_student)错误总结：向表中插入数据时，如果插入的字符串长度大于约束，会提示将截断字符串或二进制数据实验三视图的创建及使用一、实验目的和要求：掌握视图的建立和使用，以及SQL的更新语句。掌握CREATEVIEW语句，能够运用该语句建立数据视图；掌握UPDATE语句的用法；掌握DELETE语句的用法。二、实验内容：建立视图IS_STUDENT显示“CS”系所有学生的学号、姓名、性别。createviewcs_studentasselectsno,sname,ssexfromstudentwheresdept='CS'查询视图CS_STUDENT的所有信息。select*fromcs_student查询视图CS_STUDENT的所有男生信息。select*fromcs_studentwheressex='男'用insert语句向视图中插入元组（'95009','王五','男'），查看基本表student表中插入的数据值。insertintocs_studentvalues('95009','王五','男')修改视图的定义加入WITHCHECKOPTION子句，向视图插入一个新元组再查看student表中插入的数据值。重新定义createviewcs_student2asselectsno,sname,ssexfromstudentwheresdept='CS'withcheckoption插入insertintocs_student2values('95009','王五','男')报错：试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITHCHECKOPTION，而该操作的一个或多个结果行又不符合CHECKOPTION约束。用update语句把视图CS_STUDENT的所有记录性别都改为'男'，查看基本表student表中的数据值（对比没有WITHCHECKOPTION子句和有WITHCHECKOPTION子句的情况）。updatecs_studentsetssex='男'updatecs_student2setssex='男'用delete语句删除视图CS_STUDENT的所有记录，查看基本表student表中的数据值（对比没有WITHCHECKOPTION子句和有WITHCHECKOPTION子句的情况）。没有WITHCHECKOPTION子句deletefromcs_studentwheresno='15121'deletefromcs_studentwheresno='15122'有WITHCHECKOPTION子句deletefromcs_student2wheresno='15121'deletefromcs_student2wheresno='15122'建立视图用来显示每个系学生的平均年龄。尝试对视图进行增删改操作，分析能否执行成功。createviewstu_avgageasselectsdept系部,avg(sage)平均年龄fromstudentgroupbysdept建立视图显示学生各门课程的成绩，显示字段有：学号、姓名、课程名称、成绩。尝试对视图进行增删改操作，分析能否执行成功。insertintoSC(Sno,Cno,Grade)values('15121','1',92)insertintoSC(Sno,Cno,Grade)values('15121','2',85)insertintoSC(Sno,Cno,Grade)values('15121','3',88)insertintoSC(Sno,Cno,Grade)values('15122','2',90)insertintoSC(Sno,Cno,Grade)values('15122','3',80)添加一组数据insertintoS_gradevalues('95009','王五','数据库','95')建立视图显示所有学生的数学成绩，显示字段有：学号、姓名、成绩。如果某个学生选择了数学课程则显示相应成绩；如果某个学生没有选择数学课程，则显示其学号和姓名，在成绩上显示空白。createviewsgradeasselectisnull(a.sno,b.sno)as学号,isnull(a.sname,'')as姓名,isnull(ltrim(b.grade),'')as成绩fromstudentaleftjoin(selectc.sno,c.Gradefrom[Course]bleftjoin[SC]o=ame='数学')bona.sno=b.sno实验四数据完整性维护一、实验目的和要求：掌握在SQLServer2000中定义及使用各种约束的能力，掌握用SQL语言定义关系模式的完整性约束条件，掌握DBMS完整性控制机制的三个方面。二、实验内容：用SQL语句定义表student(sno,sname,ssex,sage)，并加入如下约束：主键：sno；sname有唯一约束；sname,ssex,sage都不允许空。createtablestudent( snochar(10)primarykey, snamechar(10)uniquenotnull, ssexchar(2)notnull, sagesmallintnotnull, sdeptchar(10))用SQL语句定义表course(cno,cname)，并加入如下约束：主键：cno；cname不允许空。createtablecourse( cnochar(10)primarykey, cnamechar(10)uniquenotnull, cpnochar(10), ccreditchar(10))用SQL语句定义表sc(sno,cno,cj)，并加入如下约束：主键：sno,cno；为sno定义名为lsno的默认参照完整性；为cno定义名为lcno的默认参照完整性；createtablesc( snochar(10), cnochar(10), gradesmallint, primarykey(sno,cno), constraintlsnoforeignkey(sno)referencesstudent(sno), constraintlcnoforeignkey(cno)referencescourse(cno))用SQL语句向student表输入如下元组：('95001','李勇','男',20,'CS');('95002','刘晨','女',21,'IS');insertintostudentvalues('95001','李勇','男',20,'CS');insertintostudentvalues('95002','刘晨','女',21,'IS');①用SQL语句向course表输入如下元组：('1','数据库','5',4);('2','数学',NULL,2);insertintocoursevalues('1','数据库','5',4);insertintocoursevalues('2','数学',NULL,2);②用SQL语句向sc表输入如下元组：('95001','1',92);('95001','2',85);('95002','2',90);insertintoscvalues('95001','1',92);insertintoscvalues('95001','2',85);insertintoscvalues('95002','2',90);执行下列语句，并查看执行结果。如果不能正确执行给出错误原因。①insertintostudentvalues('95001','张力','男',20,'CS');insertintostudentvalues('95001','张力','男',20,'CS');错误原因：95001已经存在，primarykey不能重复②insertintostudentvalues('95003','李勇','男',20,'CS');insertintostudentvalues('95003','李勇','男',20,'CS');错误原因：姓名有unique限制，“李勇”已经存在③insertintoSCvalues('95004','1',92);insertintoSCvalues('95004','1',92);错误原因：sc中sno有外码限制，student中没有95004元组④deletefromstudentwheresno='95001';deletefromstudentwheresno='95001'错误原因：student中元组是sc中元组的参照。⑤updatecoursesetcno='3'wherecno='2';updatecoursesetcno='3'wherecno='2'错误原因：course中不存在cno=3的元组给student表的ssex列添加名为fm的约束，使其取值只能取'男'或'女'。alter