还剩33页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
本科实验报告课程名称数据库系统概论实验项目交互式SQL、数据完整性、用户鉴别与数据控制实验地点致远楼B503专业班级软件1229班学号2012005829学生姓名田亚鹏指导教师李雪梅2014年3月18日
一、实验目的和要求熟悉通过SQL对数据库进行操作
二、实验内容和原理1.在RDBMS中建立一个学生-课程数据库,进行实验所要求的各种操作,所有的SQL操作均在此建立的新库里进行2.根据以下要求认真进行实验,记录所有的实验用例及执行结果数据定义基本表的创建、修改及删除;索引的创建和删除数据操作完成各类查询操作单表查询,连接查询,嵌套查询,__查询;完成各类更新操作插入数据,修改数据,删除数据视图的操作视图的定义创建和删除,查询,更新注意更新的条件
3、主要仪器设备操作系统Windows7数据库管理系统SQLServer2008
四、操作方法与实验步骤实验数据记录实验结果一数据定义一.基本表的操作1.建立基本表1创建学生表Student由以下属性组成学号Snochar型,长度为9,主码,姓名Snamechar型,长度为20,唯一,性别S___char型,长度为2,年龄__allint所在系char型,长度为20createtableStudentSnochar9pri__rykeySnamechar20uniqueS___char2Sage__allintSdeptchar20;2)创建课程表Course由以下属性组成课程号Cnochar型,主码,长度为4,课程名Cnamechar型,长度为40,先行课Cpnochar型,长度为4外码,学分Ccredit__allintcreatetableCourseCnochar4pri__rykeyCnamechar40Cpnochar4Ccredit__allint;若设置Cpno外码,插入数据时会提示违反外码约束3创建学生选课表SC,由以下属性组成学号Snochar型,长度为9,课程号Cnochar型,长度为4,成绩Grade__allint其中Sno和Cno构成主码createtablescSnochar9Cnochar4Grade__allintpri__rykeySnoCnoforeignkeySnoreferen__sstudentSnoforeignkeyCnoreferen__scourseCno;2.修改基本表1向Student表增加“入学时间列”,其数据类型为日期型altertableStudentaddS_entran__date;2将年龄的数据类型由字符型假设原来的数据类型是字符型改为整数altertableStudentaltercolumnSageint;3增加课程名称必须取唯一值的约束条件altertableCourseadduniqueCname;注意修改表结构后,再次查看表,注意观察变化3.删除基本表1在所有的操作结束后删除Student表droptableStudent;2在所有的操作结束后删除Course表droptableCourse;3在所有的操作结束后删除SC表droptableSC;思考删除表时,不同的删除顺序会有不同结果,___??注意错误提示二.索引操作1.建立索引1为学生—课程数据库中的Student,Course,SC3个表建立索引其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引createuniqueindexStusnoonStudentSno;createuniqueindexCoucnoonCourseCno;createuniqueindexSCnoonSCSnoASCCnoDESC;2.删除索引1删除Student表的Stusname索引dropindexstudent.Stusname;二数据操作一.更新操作1插入数据1在Student表中插入下列数据200215121,李勇,男,20,CS200215122,刘晨,女,19,CS200215123,王敏女,18,__200215125,张立,男,19,ISinsertintostudentSnoSnameS___SageSdeptvalues200215121李勇男20CS;insertintostudentSnoSnameS___SageSdeptvalues200215122刘晨女19CS;insertintostudentSnoSnameS___SageSdeptvalues200215123王敏女18__;insertintostudentSnoSnameS___SageSdeptvalues200215125张立男19IS2在Course表中插入以下数据1数据库542数学null,26数据处理null,24操作系统637PASCAL语言645数据结构741数据库543信息系统14insertintocourseCnoCnameCpnoCcreditvalues1数据库54;insertintocourseCnoCnameCcreditvalues2数学2;insertintocourseCnoCnameCcreditvalues6数据处理2;insertintocourseCnoCnameCpnoCcreditvalues4操作系统63;insertintocourseCnoCnameCpnoCcreditvalues7PASCAL语言64;insertintocourseCnoCnameCpnoCcreditvalues5数据结构74;insertintocourseCnoCnameCpnoCcreditvalues1数据库54;insertintocourseCnoCnameCpnoCcreditvalues3信息系统14;3在SC表中插入以下数据200215121192200215121285200215121388200215122290200215122380insertintoscSnoCnoGradevalues200215121192;insertintoscSnoCnoGradevalues200215121285;insertintoscSnoCnoGradevalues200215121388;insertintoscSnoCnoGradevalues200215122290;insertintoscSnoCnoGradevalues200215122380;4将一个新学生元祖学号200215128;姓名陈冬;性别男;所在系IS;年龄18岁插入到Student表中insertintostudentSnoSnameS___SdeptSagevalues200215128陈冬男IS18;(已做)5将学生张成民的信息插入到Student表中insertintostudentvalues200215126张成民男18CS;(已做)6插入一条选课记录‘200215128’,‘1’insertintoscSnoCnovalues2002151281;(已做)7对每一个系,求学生的平均年龄,并把结果存入数据库createtableDept_ageSdeptchar15__g_age__allint;insertintoDept_ageSdept__g_ageselectSdept__gSagefromstudentgroupbySdept;2.修改数据1将学生200215121的年龄改为22岁updatestudentsetSage=22whereSno=200215121;2将所有学生的年龄增加一岁updatestudentsetSage=Sage+1;3将计算机科学系全体学生的成绩置零updatescsetGrade=0whereCS=selectSdeptfromstudentwherestudent.Sno=sc.Sno;3.删除数据1删除学号为200215128的学生记录deletefromstudentwhereSno=200215128;2删除所有学生的选课记录deletefromsc;3删除计算机科学系所有学生的选课记录deletefromscwhereCS=selectSdeptfromstudentwherestudent.Sno=SC.Sno;2.查询操作1.单表查询2查询全体学生的姓名、学号、所在系selectsnosnamesdeptfromStudent;5查询全体学生的姓名、出生年份和所在院系,要求用小写字母表示所有系名selectsname2013-sagebirthlowersdeptsdeptfromStudent;10查询年龄在20-23岁包括20岁和23岁之间的学生的姓名、系别和年龄selectsnamesdeptsagefromStudentwheresagebetween20and2312查询计算机科学系CS、数学系__、和信息系IS学生的姓名和性别selectsnames___fromStudentwhereSdeptincs__is15查询所有姓刘的学生的姓名、学号和性别selectsnameSnos___fromStudentwheresnamelike刘%19查询DB_Design课程的课程号和学分selectCnoCcreditfromcoursewhereCnamelikeDB\_Designescape\;23查询计算机科学系年龄在20岁以下的学生姓名selectsnamefromstudentwheresdept=csandsage2028计算1号课程的学生平均成绩select__Ggradeno1fromscwhereCno=131求各个课程号及相应的选课人数selectCnoCOUNTsnonumberfromscgroupbyCno2.连接查询1查询每个学生及其选修课程的情况selectStudent.*sc.*fromscStudentwheresc.Sno=Student.Sno2对上个题用自然连接完成selectstudent.SnoSnameSageS___SdeptCnoGradefromscStudentwheresc.Sno=Student.Sno3查询每一门课的间接先修课即先修课的先修课selectfirst.cnosecond.cpnofromCoursefirstCoursesecondwherefirst.Cpno=second.Cno4查询每个学生及其选修课程的情况,用外连接来完成selectstudent.SnoSnameS___SageSdeptCnoGradefromstudentleftouterjoinsconstudent.Sno=sc.Sno;5查询选修2号课程且成绩在90分以上的所有学生selectstudent.SnoSnamefromstudentscwherestudent.Sno=sc.Snoandsc.Cno=2andsc.Grade90;6查询每个学生的学号、姓名、选修的课程名及成绩selectstudent.SnoSnameCnameGradefromstudentsccoursewherestudent.Sno=sc.Snoandsc.Cno=course.Cno;3.嵌套查询1查询与“刘晨”在同一个系学习的学生selectSnoSnameSdeptfromStudentwhereSdeptinselectSdeptfromStudentwhereSname=刘晨;2查询选修了课程名为“信息系统”的学生学号和姓名selectSnoSnamefromstudentwhereSnoinselectSnofromscwhereCnoinselectCnofromcoursewhereCname=信息系统;3找出每个学生超过他选修课程平均成绩的课程号selectcnofromscxwheregradeselect__GGradefromscywherex.sno=y.snogroupbySno;4查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄selectsnameSagefromStudentwhereSage=anyselectsagefromstudentwhereSdept=csandSdeptcs;5查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄selectsnameSagefromStudentwhereSage=allselectsagefromstudentwhereSdept=csandSdeptcs;6查询选修了1号课程的学生姓名selectSnamefromstudentwhereexistsselect*fromscwhereSno=student.SnoandCno=1;7查询没有选修1号课程的学生姓名selectSnamefromstudentwherenotexistsselect*fromscwhereSno=student.SnoandCno=1;8查询选修了全部课程的学生姓名selectSnamefromstudentwherenotexistsselect*fromCoursewherenotexistsselect*fromscwhereSno=student.SnoandCno=course.Cno;9查询至少选修了学生200215121选修的全部课程的学生号码selectdistinctSnofromscscxwherenotexistsselect*fromscscywherescy.Sno=200215122andnotexistsselect*fromscsczwherescz.Sno=scx.Snoandscz.Cno=scx.Cno;4.__查询1查询计算机科学系的学生及年龄不大于19岁的学生select*fromstudentwhereSdept=CSunionselect*fromstudentwhereSage=19;2查询选修了课程1或课程2的学生selectSnofromscwhereCno=1unionselectSnofromscwhereCno=2;3查询计算机科学系的学生与年龄不大于19岁的学生的交集select*fromstudentwhereSdept=CSintersectselect*fromstudentwhereSage=19;4查询既选修了课程1又选修了课程2的学生selectSnofromscwhereCno=1intersectselectSnofromscwhereCno=2;5查询计算机科学系的学生与年龄不大于19岁的学生的差集select*fromstudentwhereSdept=CSex__ptselect*fromstudentwhereSage=19
三、视图操作1.建立视图1建立信息系学生的视图createviewIS_StudentasselectSnoSnameSagefromstudentwhereSdept=IS;2建立信息系学生的视图并要求进行修改和插入操作时仍需保证该视图只有信息系的学生createviewIS_StudentasselectSnoSnameSagefromstudentwhereSdept=ISwithcheckoption;3建立信息系选修了1号课程的学生的视图createviewIS_S1SnoSnameGradeasselectstudent.SnoSnameGradefromstudentscwhereSdept=ISandstudent.Sno=sc.Snoandsc.Cno=1;4建立信息系选修了1号课程且成绩在90分以上的学生的视图createviewIS_S2SnoSnameGradeasselectSnoSnameGradefromIS_S1whereGrade=90;5定义一个反应学生出生年份的视图createviewBT_SSnoSname__irthasselectSnoSname2004-Sagefromstudent;6将学生的学号及他的平均成绩定义为一个视图createviewS_GSnoG__gasselectSno__gGradefromscgroupbySno;7将Student表中所有女生记录定义为一个视图createviewF_StudentF_snoname___agedeptasselect*fromstudentwhereS___=女;2.删除视图1删除视图BT_S:3.查询视图1在信息系学生的视图中找出年龄小于20岁的学生selectSnoSagefromIS_StudentwhereSage=20;2查询选修了1号课程的信息系学生selectIS_Student.SnoSnamefromIS_StudentscwhereIS_Student.Sno=sc.Snoandsc.Cno=1;3在S_G视图中查询平均成绩在80分以上的学生学号和平均成绩select*fromS_GwhereG__g=80;4.更新视图1将信息系学生视图IS_Student中学号为200215125的学生姓名改为“刘辰”updateIS_StudentsetSname=刘辰whereSno=200215125;2向信息系学生视图IS_Student中插入一个新的学生记录,其中学号为200215129,姓名为赵新,年龄为20岁insertintoIS_Studentvalues200215129赵新20IS;3删除信息系学生视图IS_Student中学号为200215129的记录deletefromIS_StudentwhereSno=200215129;
一、实验目的和要求
(1)了解SQLSerer数据库系统中数据完整性控制的基本方法
(2)熟练掌握常用CREATE或ALTER在创建或修改表时设置约束
(3)了解触发器的机制和使用
(4)验证数据库系统数据完整性控制
二、实验内容和原理结合ST数据库中的各个表,设置相关的约束,要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等,掌握各约束的定义方法设置一个触发器,实现学生选课总学分的完整性控制,了解触发器的工作机制设计一些示例数据,验证完整性检查机制要求包括如__面的内容
3.1使用SQL语句设置约束使用CREATE或ALTER语句完成如下的操作,包括1.设置各表的主键约束2.设置相关表的外键
3.设置相关属性的非空约束、默认值约束、唯一约束
4.设置相关属性的CHECK约束
3.2使用触发器创建一个触发器,实现如下的完整性约束当向SC表中插入一行数据时,自动将学分累加到总学分中记录修改学分的操作
3.4检查约束和触发器分别向相关表插入若干条记录,检查你设置的完整性约束是否有效1.插入若干条包含正确数据的记录,检查插入情况2.分别针对设置的各个约束,插入违反约束的数据,检查操作能否进行3.向SC表插入若干行数据,检查触发器能否实现其数据一致性功能
三、主要仪器设备使用SQLServer数据库管理系统提供的S__S和查询编辑器
4、实验内容实验结果与分析创建学生选课数据库TEST,包括三个基本表,其中Student表保存学生基本信息,Course表保存课程信息,SC表保存学生选课信息,其结构如下表CREATETABLEStudentSnoCHAR8PRI__RYKEYSnameCHAR8NOTNULLS___CHAR2CHECKS___in男女Sage__ALLINTSdeptCHAR20SclassCHAR4NOTNULLStotal__allintDEFAULT0;CREATETABLECourseCnoCHAR4CONSTRAINTFK_CoursePRI__RYKEYCnameCHAR40CpnoCHAR4Ccredit__ALLINT;CREATETABLESCSnoCHAR8FOREIGNKEYSnoREFEREN__SStudentSnoCnoCHAR4Grade__ALLINTCONSTRAINTSC_CHECKCHECKGrade0ANDGrade100PRI__RYKEYSnoCnoCONSTRAINTFK_SCFOREIGNKEYCnoREFEREN__SCourseCno;插入数据INSERTINTOstudentVALUES20_____1李勇男20CS10010INSERTINTOStudentVALUES20_____2刘晨女19CS10010INSERTINTOStudentVALUES20100021王敏女18__10020INSERTINTOStudentVALUES20100031张立男19IS10030INSERTINTOStudentVALUES20_____3刘洋女NULLNULL10010INSERTINTOStudentVALUES20100010赵斌男19IS10050INSERTINTOStudentVALUES20100022张明明男19CS10020INSERTINTOCourseCnoCnameCpnoCcreditVALUES1数据库系统原理54INSERTINTOCourseCnoCnameCpnoCcreditVALUES2高等数学null2INSERTINTOCourseCnoCnameCpnoCcreditVALUES3管理信息系统14INSERTINTOCourseCnoCnameCpnoCcreditVALUES4操作系统原理63INSERTINTOCourseCnoCnameCpnoCcreditVALUES5数据结构74INSERTINTOCourseCnoCnameCpnoCcreditVALUES6数据处理null2INSERTINTOCourseCnoCnameCpnoCcreditVALUES7C语言null4INSERTINTOSCVALUES20_____1192INSERTINTOSCVALUES20_____1285INSERTINTOSCVALUES20_____1388INSERTINTOSCVALUES20_____2190INSERTINTOSCVALUES20_____2280INSERTINTOSCVALUES20_____31nullINSERTINTOSCVALUES20_____103null修改约束对数据库中已经存在的表,可对其增加约束或修改已存在的约束
(1)添加约束ALTERTABLECourseADDUNIQUECnameALTERTABLECourseADDFOREIGNKEYCpnoREFEREN__SCourseCno掌握如何添加约束掌握如何声明唯一约束
(2)修改约束ALTERTABLESCDROPCONSTRAINTSC_CHECKALTERTABLESCADDCONSTRAINTSC_CHECKCHECKGrade=0ANDGrade=100掌握如何修改约束理解命名约束的优点
二、检查完整性约束通过修改数据库中的数据检查完整性约束条件的作用1.检查主键约束
(1)执行下面的语句修改Student表,观察语句能否正确运行,解释___?INSERTINTOStudentVALUES20_____1__男20CS10010INSERTINTOStudentVALUES20_____1__男20CS10010消息2627,级别14,状态1,第1行违反了PRI__RYKEY约束PK__Student__DDDF64467F60ED59不能在对象dbo.Student中插入重复键语句已终止(因为sno为主键不能有重复,所以无法运行)UPDATEStudentSETSno=20100021WHERESname=张立消息2627,级别14,状态1,第1行违反了PRI__RYKEY约束PK__Student__DDDF64467F60ED59不能在对象dbo.Student中插入重复键语句已终止(张立要修改的学跟王敏的学号重复了,由于学号是学生表的主键,不可以重复,所以无法运行)
(2)执行下面的语句修改SC表,观察语句能否正确运行,解释___?INSERTINTOSCVALUES20_____1178INSERTINTOSCVALUES20_____1null782.检查唯一约束执行下面的语句修改Course表,观察语句能否正确运行,解释___?INSERTINTOCourseVALUES8J__A73INSERTINTOCourseVALUES9数据结构733.检查默认值、允许空值列运行如下的语句INSERTINTOStudentSnoSnameS___SclassVALUES20_____2张盛男1008观察插入数据行的数值SELECT*FROMStudentWHereSno=20_____24.检查非空约束下面的语句包含空值,检查运行结果,解释___?INSERTINTOStudentSnoSnameS___SclassVALUES20_____3张盛男NULLINSERTINTOStudentSnoSnameS___VALUES20_____4张盛男5.检查CHECK约束执行下面的语句,解释其运行结果INSERTINTOSCVALUES20_____1495INSERTINTOSCVALUES20_____14102INSERTINTOStudentSnoSnameS___SclassVALUES20_____3张盛男1008INSERTINTOStudentSnoSnameS___SclassVALUES20_____4张盛10086.检查外键约束
(1)执行下面的语句检查外键约束的作用INSERTINTOSCVALUES20100301195INSERTINTOSCVALUES20_____11095UPDATESCSETCno=10WhereCno=1UPDATECourseSETCno=10WhereCno=3
(2)执行下面的语句检查对被引用表的约束Student表DELETEStudentWHERESno=20100021DELETEStudentWHERESno=20_____1UPDATECourseSETCno=10WHERECname=数据库系统原理
三、触发器的定义及使用1.定义触发器
(1)定义一个触发器,实现有关学分的完整性约束当向SC表插入一行选课记录时,自动将该课程的学分累加到该学生的总学分中CREATETRIGGERtr_INSERTONSCFORINSERTAS--声明变量DECLARE@snochar8DECLARE@criditintDECLARE@cnochar4--提取插入的数据SELECT@sno=Sno@cno=CnoFROMinserted--提取学生的总学分SELECT@cridit=CcreditFROMSCjoinCourseONSC.Cno=Course.CnoWHERESC.Cno=@cno--更新总学分UPDATEStudentSETStotal=Stotal+@criditWHERESno=@snoGO
(2)定义一个触发器,实现对SC表的操作登记当用户向SC表插入或修改时,记录该操作到数据库中创建日志登记表:CREATETABLELOG_TABLEusernamechar10--操作人员datedatetime--修改时间Snochar8--学生学号Cnochar4--课程号创建日志登记触发器:CREATETRIGGERtr_UPDATEONSCFORINSERTUPDATEASDECLARE@snochar8DECLARE@cnochar4DECLARE@new__allintSELECT@sno=Sno@cno=CnoFROMinsertedINSERTINTOLOG_TABLEVALUESCURRENT_USERgetdate@Sno@Cno;GO
(3)执行插入操作,触发触发器INSERTINTOSCVALUES20_____1695
(4)验证触发器是否触发select*fromstudentselect*fromLOG_TABLE
1、实验目的
(1)了解SQLSerer数据库系统中数据访问控制的基本方法
(2)了解使用S__S如何给用户授权
(3)熟练掌握常用GRANT和REVOKE进行权限控制
(4)验证数据库系统的访问控制
2、实验平台使用SQLServer数据库管理系统提供的S__S3实验内容及要求实验应包括如__面的内容创建新的数据库用户对上一实验建立的表进行权限设置检查数据库用户的权限撤销用户权限4实验报告要求写出实验的基本过程参考示例在SQLServer中,对用户访问数据库的权限管理可以使用两种方式,一种是使用S__S的图形界面进行管理,一种是使用SQL语句
3.1使用S__S的图形界面创建用户并授权创建一个数据库用户张明,授予他可以查询Student表的权限1.使用S__S的图形界面创建登录名在快捷菜单中选择“新建登录名”进入“登录名”窗口,输入登录信息张明123456782.使用S__S的图形界面创建数据库用户选择“ST”数据库,在快捷菜单中选择“新建登录名”3.使用S__S的图形界面进行授权,设置用户对对象的访问权限展开ST数据库的“安全性”下的“用户”,选择“张明db”
(1)打开用户属性窗口
3.2使用SQL语句创建用户并授权在ST或TEST数据库中,使用SQL语句创建两个数据库用户,对数据库中的Student、SC、和Course表,分别对其授予不同的访问权限,如下表表
3.1授予用户权限1.创建登录名用户要访问数据库,必须首先登录到服务器上因此,首先需要为用户创建登录名以系统___账户登录,进入数据库服务器,执行以下的操作
(1)分别创建两个登录名王明和__CREATELOGIN王明WITHpassword=123456CREATELOGIN__WITHpassword=1234562.创建数据库用户用户登录服务器后,要进入某一数据库访问,需要为登录用户在该数据库中建立一个数据库用户名对创建的两个登录名,在TEST数据库分别为其创建两个数据库用户
(1)切换到TEST数据USETEST
(2)创建登录名在TEST数据库中的用户CREATEUSER王明dbFORLOGIN王明CREATEUSER__FORLOGIN__
3.授权在数据库中添加新用户后,新用户没用访问数据库中数据的权限,需要数据库管理人员给其授予相关的权限后,才能执行相应的操作按照表
3.1,授权如下
(1)授予王明权限GRANTSELECTONStudentTO王明db
(2)授予__权限GRANTSELECTONStudentTO__GRANTINSERTUPDATEDELETEONStudentTO__GRANTSELECTONCourseTO__
3.3检查权限控制分别以两个登录名登录数据库,执行一些对数据库的操作,检查用户否允许执行相关的操作1.检查王明的权限以用户“王明”登录,执行如下的操作,检查能否正确的运行SELECT*FROMStudentINSERTINTOStudentSnoSnameSclassVALUES20101101高志1008UPDATEStudentSetSage=Sage+1DELETEFROMStudentWhereSno=20101010SELECT*FROMCourseSELECT*FROMSC2.检查__的权限以用户“__”登录,执行如下的操作,检查能否正确的运行SELECT*FROMStudentINSERTINTOStudentSnoSnameSclassVALUES20101201钱红1008UPDATEStudentSetSage=Sage+1DELETEFROMStudentWhereSno=20101210SELECT*FROMCourseINSERTINTOCourseVALUES20FORTRAN语言NULL2UPDATECourseSetCcredit=3WHERECno=2DELETEFROMCourseWhereCno=
63.增加__的权限以用户“__”登录,执行如下的操作
(1)检查__能否对SC表执行查询、插入、修改、删除操作
(2)使用___帐号给__授予对SC表的查询、插入、修改、删除权限
(3)再次检查__能否对SC表执行查询、插入、修改、删除操作
3.4撤销权限当不希望用户执行某些对数据库的操作时,可以撤销先前授予用户的操作权限
(1)撤销权限以系统___帐号登录,撤销“__”对Student表的权限REVOKESELECTONStudentFROM__REVOKEINSERTUPDATEDELETEONStudentFROM__
(2)检查权限以“__”帐号登录,检查如下的命令能否正确的运行SELECT*FROMStudentINSERTINTOStudentSnoSnameSclassVALUES20101501赵光1008UPDATEStudentSetSage=Sage+1DELETEFROMStudentWhereSno=20101510已建立已删除原视图。