太原理工数据库_实验报告

本科实验报告课程名称：数据库系统原理B实验项目：交互式SQL、数据完整性实验地点：专业班级：学号：学生姓名：指导教师：2015年6月24日实验二：交互式SQL语句的使用一、实验目的（1）掌握数据库对象的操作过程,包括创建、修改、删除。（2）熟悉表的各种操作，包括插入、修改、删除、查询（3）熟练掌握常用SQL语句的基本语法二、实验内容和要求选择如下一个应用背景之一：学生选课系统其它你熟悉的应用（1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。（2）要求认真进行实验，记录各实验用例及执行结果。（3）深入了解各个操作的功能。实验要求包括如下方面的内容：3.1数据定义1．基本表的创建、修改及删除2．索引的创建3．视图的创建3.2数据操作完成各类更新操作包括：1．插入数据2．修改数据3.删除数据3.3数据查询操作完成各类查询操作1．单表查询2．分组统计3.连接查询4.嵌套查询5.集合查询3.4数据操作1．创建视图2．视图查询三、主要仪器设备操作系统：Windows7Windows2003Server数据库管理系统：SQLServer。四、操作方法与实验步骤1.创建，修改及删除基本表（1）创建Student表CREATETABLEStudent(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8),SsexCHAR(2)NOTNULL,SageINT,SdeptCHAR(20));（2）创建Course表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40)NOTNULL,CpnoCHAR(4),CcreditSMALLINT,);（3）创建SC表CreatetableSC(Snochar(8),Cnochar(4),Gradesmallint,Primarykey(Sno,Cno),Foreignkey(Sno)referencesStudent(Sno),Foreignkey(Cno)referencesCourse(Cno));（4）创建员工表EmployeeCREATETABLEEmployee(编号CHAR(8)PRIMARYKEY,姓名VARCHAR(8)notnull部门CHR（40）,工资numeric(8,2),生日datetime，职称char(20),);指出该语句中的错误并改正后执行。（5）检查表是否创建成SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSCSELECT*FROMEmployee（6）修改表结构及约束增加班级列ALTERTABLEStudentADDSclasschar(4)修改年龄列ALTERTABLEStudentALTERCOLUMNSagesmallint增加约束ALTERTABLECourseADDUNIQUE(Cname)（7）删除表DROPTABLEEmployee2．创建索引（1）为Course表按课程名称创建索引CREATEINDEXiCnameOnCourse(Cname)（2）为Student表按学生姓名创建唯一索引CREATEUNIQUEINDEXiSnameONStudent(Sname)（3）为SC表按学号和课程号创建聚集索引CREATECLUSTEREDINDEXiSnoCnoOnSC(Sno,Cnodesc)（4）为Course表按课程号创建唯一索引CREATEUNIQUEINDEXiCnoONCourse(Cno)3．创建视图建立信息系学生的视图：CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='IS'3.2数据操作1.插入数据(1)插入到Student表INSERTINTOStudentVALUES('20100001','李勇','男',20,'CS','1001')INSERTINTOStudentVALUES('20100002','刘晨','女',19,'CS','1001')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)VALUES('20100021','王敏','女',18,'MA','1002')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)VALUES('20100031','张立','男',19,'IS','1003')INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100003','刘洋','女','1001')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUES('20100010',’赵斌’,'男','19','IS','1005')INSERTINTOStudentVALUES('20100022',19,'张明明','男','CS','1002')(2)插入到Course表insertintoCourse(Cno,Cname,Cpno,Ccredit)values('1','数据库系统原理','5',4)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('2','高等数学',null,2)insertintoCourse(Cno,Cname,Cpno,Ccredit)values('3','管理信息系统','1','4')insertintoCourse(Cno,Cname,Cpno,Ccredit)values('4','操作系统原理','6','3')insertintoCourse(Cno,Cname,Cpno,Ccredit)values('5','数据结构','7','4')insertintoCourse(Cno,Cname,Cpno,Ccredit)values('6','数据处理',null,'2')insertintoCourse(Cno,Cname,Cpno,Ccredit)values('7','C语言',null,'4')(3)插入到Sc表insertintoScvalues('20100001','1',92)insertintoScvalues('20100001','2',85)insertintoSc(Sno,Cno)values('20100003','1')insertintoSc(Sno,Cno,Grade)values('20100010','3',null)insertintoScvalues('20100001','3',88)insertintoScvalues('20100002','1',90)insertintoScvalues('20100002','2',80)(4)多行插入到表中createtablecs_Student(学号char(8),姓名char(8),年龄smallint);insertintocs_StudentselectSno,Sname,SagefromStudentwhereSdept='CS';(5)检查插入到表中的数据SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSC2.修改数据（1）将学生20100001的年龄改为22岁。UPDATEstudentSETSage=22WHERESno='20100001';（2）将所有学生的年龄增一岁。UPDATEStudentSETSage=Sage+1（3）填写赵斌同学的管理信息系统课程的成绩UPDATESCSETGrade=85WHERESno='20100010'ANDCno='3'（4）将计算机科学系全体学生的成绩加5分UPDATEscSETGrade=Grade+5WHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=sc.Sno);（5）请自己完成如下操作将刘晨同学的2号课程成绩修改为80updateSCsetGrade=80whereSno='20100002'ANDCno='2'将“20100021”同学的学号修改为“20100025”updateStudentsetSno=20100025whereSno='20100021';（6）检查数据是否修改3.删除数据（1）删除学号为201000022的学生记录DELETEFROMStudentWHERESno='20100022'（2）删除学号20100001学生的1号课程选课记录将选课信息复制到一个临时表tmpSC中：SELECT*INTOtmpSCFROMSC在tmpSC中执行删除操作：DELETEFROMtmpSCWHERESno='20100001'andCno='1'（3）删除临时表中20100002学生的全部选课记录deletefromtmpSCwhereSno='20100002';（4）删除计算机科学系所有学生的选课记录DELETEFROMtmpSCWHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno);（5）删除全部选课记录DELETEFROMtmpSC（6）检查数据是否删除3.3数据查询操作完成如下查询操作：1．单表查询（1）按指定目标列查询查询学生的详细记录：SELECT*FROMStudent;查询学生的学号、姓名和年龄SELECTSno,Sname,SageFROMStudent;（2）目标列包含表达式的查询查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth:',2004-Sage,LOWER(Sdept)FROMStudent;（3）查询结果集中修改列名称查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth:'asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)FROMStudent;（4）取消重复行查询选修了课程的学生学号：比较ALL和DISTINCT的区别SELECTSnoFROMSC;SELECTDISTINCTSnoFROMSC;（5）简单条件查询查询计算机科学系全体学生的名单SELECTSnameFROMStudentWHERESdept='CS';（6）按范围查询查询年龄在20~23岁之间的学生的姓名、系SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23（7）查询属性值属于指定集合的行查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS');（8）模糊查询查询所有姓刘学生的姓名、学号SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'刘%'（9）查询空值查询缺少成绩的学生的学号和相应的课程号SELECTSno,CnoFROMscWHEREGradeisnull;（10）多重条件查询查询计算机科学系年龄在岁以下的学生姓名SELECTSnameFROMstudentWHERESdept='CS'andSage20;（11）结果集排序查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列SELECT*FROMStudentORDERBYSdept,SageDESC;（12）完成下列查询查询学生基本信息，结果集属性名使用汉字selectSname'姓名',Ssex'性别',Sage'年龄',Sdept'所在系',Sclass'班级'fromStuden