太原理工大学数据库实验报告概要

TAIYUANUNIVEftSIIYOFTECHNOLOGY本科实验报告课程名称：数据库系统概论实验项目：交互式SQL、数据完整性、用户鉴别与数据控制实验地点：致远楼B503专业班级：软件1229班学号：2012005829学生姓名：田亚鹏指导教师：李雪梅2014年3月18日一、实验目的和要求熟悉通过SQL对数据库进行操作。二、实验内容和原理1．在RDBMS中建立一个学生-课程数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。2．根据以下要求认真进行实验，记录所有的实验用例及执行结果。数据定义：基本表的创建、修改及删除；索引的创建和删除。数据操作：完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）；完成各类更新操作（插入数据，修改数据，删除数据）。视图的操作：视图的定义（创建和删除），查询，更新（注意更新的条件）。三、主要仪器设备操作系统：Windows7。数据库管理系统：SQLServer2008。四、操作方法与实验步骤实验数据记录实验结果（一）数据定义：一．基本表的操作1．建立基本表1）创建学生表Student,由以下属性组成：学号Sno（char型，长度为9,主码），姓名Sname（char型，长度为20，唯一），性别Ssex（char型，长度为2），年龄（smallint）,所在系（char型，长度为20）。createtableStudent（Snochar（9）primarykey,Snamechar（20）unique,Ssexchar（2）,Sagesmallint,Sdeptchar（20））;2）创建课程表Course，由以下属性组成：课程号Cno（char型，主码，长度为4），课程名Cname（char型，长度为40），先行课Cpno（char型，长度为4,外码），学分Ccredit（smallint）。createtableCourse（Cnochar（4）primarykey,Cnamechar（40）,Cpnochar（4）,Ccreditsmallint）;若设置Cpno外码，插入数据时会提示违反外码约束。3）创建学生选课表SC，由以下属性组成：学号Sno（char型，长度为9），课程号Cno（char型，长度为4），成绩Grade（smallint）,其中Sno和Cno构成主码。

createtablesc(Snochar(9),Cnochar(4),Gradesmallint,primarykey(Sno,Cno),foreignkey(Sno)referencesstudent(Sno),foreignkey(Cno)referencescourse(Cno))；2．修改基本表：|Schoola舞库关垂圉曰|Schoola舞库关垂圉曰口表scj至统表田二dbo,CourseS口dbo.Ec13dbo.Student国口视图®口同文词®口可編程性®口ServiceErok^r国口存睹S口妄全性altertableStudentaddS_entrancedate;0二dbo.Student日口列?Sno(PK,char(9),notnull]圍Sname[char(20],null)FlSsex(char(2),null)圍Sage(smallintnull)圍Sdept(char(2£l),null)|T|S_entrance(date,null]将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。altertableStudentaltercolumnSageint;日口dbo.Student日口列?Sno(PK,char(9),notnull)圍Sname(char(20),null)FlSsex(char(2),null)圍Sage(smallintnull)圍Sdept(char(20),null]圍S_entrance田毗巳null)增加课程名称必须取唯一值的约束条件。altertableCourseaddunique(Cname);3:二dbo.Course田口列曰口迪7PK_Course_ClFE&373D60DE

.jUQ_Course_9F5E029&0EA3；注意：修改表结构后，再次查看表，注意观察变化。

3．删除基本表：1)在所有的操作结束后删除Student表。droptableStudent;2)在所有的操作结束后删除Course表。droptableCourse;3)在所有的操作结束后删除SC表。droptableSC;思考：删除表时，不同的删除顺序会有不同结果，为什么？？注意错误提示。二．索引操作1．建立索引1)为学生—课程数据库中的Student，Course，SC3个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。createuniqueindexStusnoonStudent(Sno);createuniqueindexCoucnoonCourse(Cno);createuniqueindexSCnoonSC(SnoASC,CnoDESC);日口日口索引土Coucno〔唯一r非濟集）爲PK_Course_ClFE6373O6OEJiUQ_jCourse_9F5E029&0EA日口索引、孤PK_.Student_CAlFE4647F6OED±Stusno傩一r非果集］JlUQ_Student_52723D27023D5日口乘I,PK_.sc_E600025309DE7BCtSCno〔唯一「非象集）2．删除索引1）删除Student表的Stusname索引。dropindexstudent.Stusname;日口索引、乱PK_Student_CAlFE4&47F6OED59Stusname〔唯一r非聚集）已建立±Stuano傩一「非第集）启UQ_Student_52723D27O23D5AO0口索引、為PK_Student_CAlFE4&47F60ED5i出皈訪o］唯一「非韩已删除五UQ_Student_52723D27023D5Al(二)数据操作一．更新操作T玮>jfefrbg.插入数据1)在Student表中插入下列数据：200215121，李勇，男，20,CS200215122，刘晨，女，19，CS200215123，王敏。女，18，MA200215125，张立，男，19，ISinsertintostudent(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')SnoSnameSsexSageSdept12U0215121李勇男24CS22U0215122划晨女21CS3200215123王敏女20MA4200215125张立男21IS5200215126张成民20CS62flO21512B陈冬20IS2)在Course表中插入以下数据：数据库,5,4数学,null,2数据处理,null,2操作系统,6,3PASCAL语言,6,4数据结构,7,41,数据库,5,4信息系统,1,4insertintocourse(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);insertintocourse(Cno,Cname,Ccredit)values(2,'数学',2);insertintocourse(Cno,Cname,Ccredit)values('6','数据处理',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','信息系统；T,4);CnoCnameCpnoCcredit1数据库5422数学NULL233信息系统1444操作系统6355敎据结枸7466埶据处理NULL277PASCAL语言643)在SC表中插入以下数据：200215121,1,92200215121,2,85200215121,3,88200215122,2,90200215122,3,80insertintosc(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);将一个新学生元祖(学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入到Student表中。insertintostudent(Sno,Sname,Ssex,Sdept,Sage)values('200215128','陈冬','男','IS',18);(已做)将学生张成民的信息插入到Student表中。insertintostudentvalues('200215126','张成民','男',18,'CS');(已做)插入一条选课记录：(‘200215128'，‘1')。insertintosc(Sno,Cno)values('200215128','1');(已做)对每一个系，求学生的平均年龄，并把结果存入数据库。createtableDept_age(Sdeptchar(15),Avg_agesmallint);insertintoDept_age(Sdept,Avg_age)selectSdept,avg(Sage)fromstudent日Udbo.Dept_age日口列圍Sdept(char(15),null)圍Avg_age(smallintnull]groupbySdept;2．修改数据1）将学生200215121的年龄改为22岁。updatestudentsetSage=22whereSno='200215121';2）将所有学生的年龄增加一岁。updatestudentsetSage=Sage+1;3）将计算机科学系全体学生的成绩置零。updatescsetGrade=0where'CS'=（selectSdeptfromstudentwherestudent.Sno=sc.Sno）;3．删除数据1）删除学号为200215128的学生记录。deletefromstudentwhereSno='200215128';2）删除所有学生的选课记录。deletefromsc;3）删除计算机科学系所有学生的选课记录。deletefromscwhere'CS'=（selectSdeptfromstudentwherestudent.Sno=SC.Sno）;二．查询操作1．单表查询2）查询全体学生的姓名、学号、所在系。selectsno,sname,sdeptfromStudent;snosnamesdept1200215121李勇CS22CD215122划晨CS32CD215123王敏MA42CD215125张立IS52CD215126张成民CS62UK1512B陈冬IS查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示所有系名。selectsname,2013-sagebirth,lower(sdept)sdeptfromStudent;10)查询年龄在20-23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。selectsname,sdept,sagefromStudentwheresagebetween20and23吕仃amesdeptsage1划晨CS212王敏MA203张立IS214张成民CS205陈冬IS2012)查询计算机科学系(CS)、数学系(MA)、和信息系(IS)学生的姓名和性别。selectsname,ssexfromStudentwhereSdeptin('cs','ma','is')呂namessex1李勇男2刘晨女3王敏女4张立男5张成民男6陈冬男15)查询所有姓刘的学生的姓名、学号和性别。selectsname,Sno,ssexfromStudentwheresnamelike'刘%'snamessex1⑶晨?2C3215122女19)查询DB_Design课程的课程号和学分。selectCno,CcreditfromcoursewhereCnamelike'DB\_Design'escape'\';23)查询计算机科学系年龄在20岁以下的学生姓名。selectsnamefromstudentwheresdept='cs'andsage<20sname28)计算1号课程的学生平均成绩。selectAVG(grade)no1fromscwhereCno='1'

31）求各个课程号及相应的选课人数。selectCno,COUNT（sno）numberfromscgroupbyCno2．连接查询1）查询每个学生及其选修课程的情况selectStudent.*,sc.*fromsc,Studentwheresc.Sno=Student.SnoSnoSnameSsexSageSdeptSnoCnoGrade2W215121李勇男24CS20021512119222DD215121李勇男24CS2002151212B532DD215121李勇男24CS2002151213BB4200215122刘晨女21CS20021512229052^215122划晨女21CS20021512238062^21512B陈冬20IS2002151281NULL2）对上个题用自然连接完成。selectstudent.Sno,Sname,Sage,Ssex,Sdept,Cno,Gradefromsc,Studentwheresc.Sno=Student.SnoSnoSnameSageSsexSdeptCnoGrade2M215121李勇24CS19220215121李勇24男CS2S53200215121李勇24男CS3SS42CD215122刘晨21女CS29052CD215122刘晨21女CS3SO62CD21512B陈冬20男IS1NULL3）查询每一门课的间接先修课（即先修课的先修课）。o,second.cpnofromCoursefirst,Coursesecondwherefirst.Cpno=second.Cno4）查询每个学生及其选修课程的情况，用外连接来完成selectstudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Gradefromstudentleftouterjoinscon（student.Sno=sc.Sno）;SnoSnameSnoSname12D0215121李费22^215122刘15）查询选修2号课程且成绩在90分以上的所有学生。selectstudent.Sno,Snamefromstudent,scwherestudent.Sno=sc.Snoandsc.Cno='2'andsc.Grade>90;6）查询每个学生的学号、姓名、选修的课程名及成绩。selectstudent.Sno,Sname,Cname,Gradefromstudent,sc,coursewherestudent.Sno=sc.Snoandsc.Cno=course.Cno;SnoSnameCnameGrade124HJ21512B陈冬数据库NULL22DD215121李勇数据库9232DD215121李勇数学B542DD215121李勇信息系统BB5200215122划晨数学906200215122划晨信息系统303．嵌套查询1）查询与“刘晨”在同一个系学习的学生。selectSno,Sname,SdeptfromStudentwhereSdeptin(selectSdeptfromStudentwhereSname='刘晨');2）查询选修了课程名为“信息系统”的学生学号和姓名。selectSno,SnamefromstudentwhereSnoin(selectSnofromscwhereCnoin（selectCnofromcoursewhereCname='信息系统'））；3）找出每个学生超过他选修课程平均成绩的课程号selectcno

fromscxwheregrade>(selectAVG(Grade)fromscywherex.sno=y.snogroupbySno);查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄selectsname,SagefromStudentwhereSage<=any(selectsagefromstudentwhereSdept='cs')andSdept<>'cs';5）查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。selectsname,SagefromStudentwhereSage<=all(selectsagefromstudentwhereSdept='cs')andSdept<>'cs';查询选修了1号课程的学生姓名。selectSnamefromstudentwhereexists(select*fromscwhereSno=student.SnoandCno='1');查询没有选修1号课程的学生姓名。selectSnamefromstudentwherenotexists(select*fromscwhereSno=student.SnoandCno='1');Snsme查询选修了全部课程的学生姓名。selectSnamefromstudentwherenotexistsSnsme(select*fromCoursewherenotexists(select*fromscwhereSno=student.SnoandCno=course.Cno));9)查询至少选修了学生200215121选修的全部课程的学生号码selectdistinctSnofromscscxwherenotexists(select*fromscscywherescy.Sno='200215122'andnotexists(select*fromscsczwherescz.Sno=scx.Snoandscz.Cno=scx.Cno））;4．集合查询1）查询计算机科学系的学生及年龄不大于19岁的学生。select*fromstudentwhereSdept='CS'unionselect*fromstudentwhereSage<=19;2）查询选修了课程1或课程2的学生。selectSnofromscwhereCno='1'unionselectSnofromscwhereCno='2';3）查询计算机科学系的学生与年龄不大于19岁的学生的交集select*fromstudentwhereSdept='CS'.SnaSnameSsex.SageSdeptintersectselect*fromstudentwhereSage<=19;查询既选修了课程1又选修了课程2的学生。selectSnofromsc

whereCno='1'intersectselectSnofromscwhereCno='2';5）查询计算机科学系的学生与年龄不大于19岁的学生的差集。SnoSnameSsexSageSdept1|2W21&121|李勇男24CS22M215122刘晨21CS32M215126兆成民20CSselect*fromstudentwhereSdept='CS'exceptselect*fromstudentwhereSage<=19三、视图操作1．建立视图建立信息系学生的视图createviewIS_StudentasselectSno,Sname,SagefromstudentwhereSdept='IS';日［」School0口諏摇■阵关系图田口表日口视冒0口盂締视图@因dbo」S_£tuamnthF后K/荷□[JSchool□[JSchool田口站库关萦囹田□表0口视圉0EJ票张喟日邸dbo.IS_£l

日口列PHSno-[dhar(9^notnull)FlSname(char{2t01null]圍Grade(smallintnull)createviewIS_S2(Sno,Sname,Grade)asselectSno,Sname,GradefromIS_S1whereGrade>=90;0因Jdbo.I£_£2日口列IlSno(char(^),netnull)J]Sname(char[20),null)圍Grade{smallint,null)pin麒芳器2）建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。createviewIS_StudentasselectSno,Sname,SagefromstudentwhereSdept='IS'withcheckoption;建立信息系选修了1号课程的学生的视图。createviewIS_S1(Sno,Sname,Grade)asselectstudent.Sno,Sname,Gradefromstudent,scwhereSdept='IS'andstudent.Sno=sc.Snoandsc.Cno='1';4）建立信息系选修了1号课程且成绩在90分以上的学生的视图。

定义一个反应学生出生年份的视图。createviewBT_S(Sno,Sname,Sbirth)asselectSno,Sname,2004-Sagefromstudent;(3因.dbo.BT_S日EJ列圍Sno(char^),not_|]Sname(char[20).HSbirth(inLnull)将学生的学号及他的平均成绩定义为一个视图。createviewS_G((3因.dbo.BT_S日EJ列圍Sno(char^),not_|]Sname(char[20).HSbirth(inLnull)fromscgroupbySno;日因Jdbo.S_G日口列_BSno(cliarf^jnotnulf)SJGavg(intnulljoa脸詐7)将Student表中所有女生记录定义为一个视图。日因Jdbo.S_G日口列_BSno(cliarf^jnotnulf)SJGavg(intnulljoa脸詐fromstudentwhereSsex='女'；□因db&.F_Studerit□因db&.F_Studerit日口列圍F_&no(char(9^notnull)IIname[char(20).null)[DsexCchar[2),null][T|age(smallintjnull)圍deptCchsr(20),null)団鹵団鹵Efflfl鹵田田田田田田1)删除视图BT_S:日口视圏G口烝褪喟dbo.BT_.Sdbo.F_Studentdbo.IS_Sldbo.IS_S2dbo.IS_Studentdbo.S_G日口视囹©口垂鋼叽圉田[H]Jdbo,F_StudentQ回dbo,IS_Sl田口1db&,IS_S2田[H]Jdbo.IE_£tudent田因]db&,&_G3．查询视图：在信息系学生的视图中找出年龄小于20selectSno,SagefromIS_StudentwhereSage<=20；查询选修了1号课程的信息系学生。selectIS_Student.Sno,SnamefromIS_Student,scwhereIS_Student.Sno=sc.Snoandsc.Cno='1'；3）在S_G视图中查询平均成绩在80分以上的学生学号和平均成绩。select*fromS_GwhereGavg>=80；SnaSna5nama120021512521220021512&4．更新视图：1）将信息系学生视图IS_Student中学号为200215125的学生姓名改为“刘辰”。updateIS_StudentsetSname='刘辰'whereSno='200215125';SnoSnameSage1200215125张立21220[)21512B陈冬20原视图2）向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为200215129，姓名为赵新，年龄为20岁。insertintoIS_Studentvalues（'200215129','赵新',20,'IS'）;SnoSnameSagesdept20021512520021512552110215129TOC\o"1-5"\h\z张立2120021512520021512552110215129陈冬20IS赵新20IS3）删除信息系学生视图IS_Student中学号为200215129的记录。deletefromIS_StudentwhereSno='200215129';SnoSnameSagesdept1j2M215125张立21IS22CK1512S陈冬却IS

（1）了解SQLSerer数据库系统中数据完整性控制的基本方法（2）熟练掌握常用CREATE或ALTER在创建或修改表时设置约束（3）了解触发器的机制和使用（4）验证数据库系统数据完整性控制二、实验内容和原理结合ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：3.1使用SQL语句设置约束使用CREATE或ALTER语句完成如下的操作，包括：1．设置各表的主键约束2．设置相关表的外键3.设置相关属性的非空约束、默认值约束、唯一约束4.设置相关属性的CHECK约束3.2使用触发器创建一个触发器，实现如下的完整性约束：SC学計010001上林SC学計010001上林7010002程明3010003E玉民4Slndent学U课程号成绩学分010001106803010001206904010002106703当向SC表中插入一行数据时，自动将学分累加到总学分中。记录修改学分的操作。3.4检查约束和触发器分别向相关表插入若干条记录，检查你设置的完整性约束是否有效1．插入若干条包含正确数据的记录，检查插入情况2．分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行3．向SC表插入若干行数据，检查触发器能否实现其数据一致性功能。三、主要仪器设备使用SQLServer数据库管理系统提供的SSMS和查询编辑器创建学生选课数据库TEST，包括三个基本表，其中Student表保存学生基本信息,Course表保存课程信息，SC表保存学生选课信息，其结构如下表：0L_jTEST©口数搞库关系囹田口表m1~..TH!PEH表1.Student表结构列名弥用途类型长度备注Sno■?'-J'字符S主键Snamc姓名字符S非空Ssex性别字符2湧，女｝Sage年龄整型Sdept所在系字符20Sclass班级字符4非空Stotdl总学分默认値0CREATETABLEStudent(SnoCHAR(8)PRIMARYKE,SnameCHAR(8)NOTNULL,SsexCHAR(2)CHECK(Ssexin('男'，女')),SageSMALLINT,SdeptCHAR(20),SclassCHAR(4)NOTNULL,StotalsmallintDEFAULT0表2.Course表结构列名称川途类型氏度约束备注Cno课稈号字符斗主键Cname课程名字符4fl非空，唯一Cpno先修课程号字符4外键屮用本聂的CnoCcredit学分整型)；CREATETABLECourse(CnoCHAR(4)CONSTRAINTFK_CoursePRIMARYKE,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT);3.SC结构列名称类型长度约束备注Sno学号字符8L何屈4仁外魏引用Student的SnoCno课程号字符4L闵屈件•外键引用Course的CnoGrade成绩U<=成绩€=100CREATETABLESC(SnoCHAR(8)FOREIGNKEY(Sno)REFERENCESStudent(Sno),CnoCHAR(4),GradeSMALLINTCONSTRAINTSC_CHECKCHECK(Grade>0ANDGrade<100),PRIMARYKEY(Sno,Cno),CONSTRAINTFK_SCFOREIGNKEY(Cno)REFERENCESCourse(Cno));插入数据INSERTINTOstudentVALUES('20100001','李勇','男',20,'CS','1001',0)INSERTINTOStudentVALUES('20100002'，'刘晨','女',19,'CS','1001',0)INSERTINTOStudentVALUES('20100021','王敏','女',18,'MA','1002',0)INSERTINTOStudentVALUES('20100031','张立','男',19,TS','1003',0)INSERTINTOStudentVALUES('20100003','刘洋','女',NULL,NULL,'1001',0)INSERTINTOStudentVALUES('20100010','赵斌','男',19,TS','1005',0)INSERTINTOStudentVALUES('20100022','张明明','男',19,'CS','1002',0)sno呂仃amessexsagesdeptsclassstat曰I2D1DDDD1李勇卑2DCS1DD1D2201000D2刘晨女19CS1DD1D3却壮麗阳刘洋女NULLNULL1DD1D4却1麗加D19IS1OT5D5却1麗睦1王敏女1SMA1002D6却価D22张明明卑19CS1002D720100331张立卑19IS1DD3DINSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES(T,'数据库系统原理','5',4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('2','高等数学；null,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('3','管理信息系统',T,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)OnoCnameCpnoCoedit1數据库系统原理5422高等数学NULL233管理信息系统1444换作系统原理£355數据结枸74£6数据处理NULL277匸语言NULL4■SnciOnoGrade124TDPDD119222S532010(X)013S34301OTME19(}530KWM122SO6301000031NULL7aO1{HM1D3NULLINSERTINTOSCVALUES('20100001','1',92)INSERTINTOSCVALUES('20100001','2',85)INSERTINTOSCVALUES('20100001','3',88)INSERTINTOSCVALUES('20100002','1',90)INSERTINTOSCVALUES('20100002','2',80)INSERTINTOSCVALUES('20100003','1',null)INSERTINTOSCVALUES('201000010','3',null)修改约束对数据库中已经存在的表，可对其增加约束或修改已存在的约束：(1)添加约束ALTERTABLECourseADDUNIQUE(Cname)ALTERTABLECourseADDFOREIGNKEY(Cpno)REFERENCESCourse(Cno)掌握如何添加约束掌握如何声明唯一约束(2)修改约束ALTERTABLESCDROPCONSTRAINTSC_CHECKALTERTABLESCADDCONSTRAINTSC_CHECKCHECK(Grade>=0ANDGrade<=100)掌握如何修改约束理解命名约束的优点二、检查完整性约束通过修改数据库中的数据检查完整性约束条件的作用。1．检查主键约束(1)执行下面的语句修改Student表，观察语句能否正确运行，解释为什么？INSERTINTOStudentVALUES('20100101','李斌','男',20,'CS','1001',0)呂no吕namessexsageadeptsdassstotal1李勇20CS1DD1D2西1麗阳2刘晨女19CS1DD1D3西壮麗昭刘洋女NULLNULL1DD1D42D100D1D男19IS1005D52D100K1王敏女1SMA1DD2D6201^022味明明男19CS1DD2D7201^331味立男19IS1DD3DB2D1UD1D1男20CS1DD1INSERTINTOStudentVALUES('2O100001','李斌','男',20,'CS','1001',0)消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK_Student_DDDF64467F60ED59'。不能在对象’dbo.Student'中插入重复键。语句已终止。(因为sno为主键不能有重复，所以无法运行)UPDATEStudentSETSno='20100021'WHERESname='张立'消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'PK_Student_DDDF64467F60ED59'。不能在对象'dbo.Student'中插入重复键。语句已终止。(张立要修改的学跟王敏的学号重复了，由于学号是学生表的主键，不可以重复，所以无法运行)(2)执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？INSERTINTOSCVALUES('20100001','1',78)泊息£违反了曲工皿RYKZY约束1EK_SC_E^00023307020F21'o不能在对象1dho.SC中插入重复键。语句已终止。INSERTINTOSCVALUES('20100001',null,78)消息313,级别“，状态S第1行不能将值NU1工插入列TCn°T.表^EST-dho-SC列不允许有Null值。INSERT失畋语句已终止。2．检查唯一约束执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？INSERTINTOCourseVALUES('8','JAVA',7,3)INSERTINTOCourseVALUES(9,'数据结构',7,3)违反了UNIQUEK3Y约束'UQ3cu£se_9F5ZO2990CEAE8771c不能在对象'dho.Course'中插入重复键语句已终止。3．检查默认值、允许空值列运行如下的语句：INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100102','张盛','男','1008')观察插入数据行的数值SELECT*FROMStudentWHereSno='20100102'呂no吕仃amessexsageadeptschssstat曰I1」201DD1D2j弓編卑NULLNULL1008D4．检查非空约束下面的语句包含空值，检查运行结果，解释为什么？INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100103','张盛','男；NULL)消息三不能将值MULL插入列'scl-ss'表'IZST.^b^.S-tudenf列不允许有Null值。INSERT失败。语句已终止。INSERTINTOStudent(Sno,Sname,Ssex)VALUES('20100104','张盛','男')消息三不能将值NULL插入列'sclass',表'IZST.dho.Student1j列不允许有Null值。INSERT失败语句已终止。5．检查CHECK约束执行下面的语句，解释其运行结果。INSERTINTOSCVALUES('20100001','4',95)

违反了FHXARYKZY违反了FHXARYKZY约束1EK_SC_ZS00025307020F211o不能在对象1dha.SC1中插入重复键■=语句已终止。INSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100103','张盛','男','1008')snosnamessexsageadeptsdassstotal2D1DDDD1李勇卑20CS1DD1D2201^^2刘晨女19CS1DD1D3201^^3刘洋女NULLNULL1DD1D4201髭31D卑19IS1005D5201^K1王锁女1BMA1002D62D10DD22张明明卑19CS1DD2D72D100331卑19IS1003DB2D10D1D1李斌20CS1DD1D920100102张盛男NULLNULL1008DW201001103张盛NULLNULL1008DVINSERTINTOStudent(Sno,Sname,Ssex,Sclass)VALUES('20100104','张盛',”,'1008')■CHECKStudent_ssex_014935CB,f/^^该冲突岌生于数据库卄TEST",^,rdho..Student11Tcolumn1ssex消息347,■CHECKStudent_ssex_014935CB,f/^^该冲突岌生于数据库卄TEST",^,rdho..Student11Tcolumn1ssex6．检查外键约束(1)执行下面的语句检查外键约束的作用INSERTINTOSCVALUES('20100301','1',95)INSERTINTOSCVALUES('20100001','10',95)UPDATESCSETCno='10'WhereCno='1'UPDATECourseSETCno='10'WhereCno='3'消息347,纯别g状态。，第1行TOC\o"1-5"\h\zINSERT语句与F3ECII2NK3Y约束"FK_SC_Stic_08EA5"93,r冲突。该冲突发生于数据库,rIZSI,r^表,rdho.Student,rrcolumn1sno语句已终止。-消息347,级别吗状态。，第2行INSERT语苛与FOREIGNKEY约束nrFK_SC,''冲娈口谪冲字右生于数扌居库1RTEST,F,表irdho-Course11xcolumn1Cno1c.语句已终止。-消息347,级别氐状态。，第3行UPDAIZ语句与FOREISNKST约束,rFK_SC'r冲突。该冲突发生于数据库,rIZSI'r,表”dhn-Course1*,cdlumnTCno1o语句已终止。■消息347,纯别g状态。，第4行UPDAIZ语句与REFERENCE约束,rFK_S2,r冲突。该冲突发生于数据库,rIZSI,r^表,rdh：c.S2,rrcolumn'Cno'o语句已终止。2)执行下面的语句检查对被引用表的约束

Student表：snosnamessexsageadeptadassstat曰I12D1DDDD1李勇男2DCS1DD1D220100002划晨女19CS1DD1D32010DOT3划洋女NULLNULL1DD1D42010001019IS1DQ5D52010M21王敏女1SMA1M2D62D1CDD22张明明男19CS1M2D720100031张立男19IS1D03DB20100101男2DCS1DD1D920100102张盛男NULLNULL10D8D1020100103张盛男NULLNULL10D8DDELETEStudentWHERESno='20100021'snosnamessexsage1201DDDD1李勇snosnamessexsage1201DDDD1李勇男20220100002划晨女19320100003划洋女NULL420100010赵斌男1952D1CDD22张明明男19620100031张立男19720100101男2DB20100102张盛男NULL920100103张盛男NULLadeptsclassstotalCS1DD1DCS1DD1DNULL1阳1DNULLW昭DNULLW昭DIS1DQ5CS1DG2IS1DTOCS1D01日日二dbo.SCO□列oE□鑒QCJ釣康曰口槪器辺trJNSERT田口案引Q口统计信息DELETEStudentWHERESno='20100001'消息347,级别血状态0,第1行^DZLZIZ语句与2EFZRENCE约束"FK_SC_Snc_03EA5793,r冲突。该冲突发生于数据库"TEST",表,rdhc.SC,rrcclumnrSnc语句已终止。UPDATECourseSETCno='10'WHERECname='数据库系统原理'消息三；UPDATZ语句与REFERENCE约束irFK_SC,r)中突。该冲突发生于数据库"TMST",表,rdhc.SC,rrcolumn1Cnu语句已终止。三、触发器的定义及使用1．定义触发器(1)定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动将该课程的学分累加到该学生的总学分中CREATETRIGGERtr_INSERTONSCFORINSERTAS--声明变量DECLARE@snochar(8)DECLARE@criditintDECLARE@cnochar(4)--提取插入的数据SELECT@sno=Sno,@cno=CnoFROMinserted--提取学生的总学分SELECT@cridit=CcreditFROMSCjoinCourseON(SC.Cno=Course.Cno)WHERESC.Cno=@cno--更新总学分UPDATEStudentSETStotal=Stotal+@criditWHERESno=@snoGO(2)定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作到数据库中。创建日志登记表：CREATETABLELOG_TABLE(usernamechar(10),datedatetime,Snochar(8),Cnochar(4))创建日志登记触发器：CREATETRIGGERtr_UPDATEONSCFORINSER,UPDATEASDECLARE@snochar(8)DECLARE@cnochar(4)DECLARE@newsmallintSELECT@sno=Sno,@cno=CnoFROMinsertedINSERTINTOLOG_TABLEVALUES(CURRENT_USER,getdate(),@Sno,@Cno);GO(3)执行插入操作，触发触发器：INSERTINTOSCVALUES('20100001','6',95)--操作人员--修改时间--学生学号--课程号iJI連罔trJMSERTdoc.SCdhe.StLiderit□.jTESTtr_4）验证触发器是否触发select*fromstudentsno呂仃amessexsagesdeptsclassstat曰12D1DDDD1李勇卑2DCS1DD122却1麗阳2刘晨女19CS1DD1D3却匕麗阳刘洋玄NULLNULL1DD1D4却匕阳1D19IS1OT5D5却価D22张明明卑19CS1002D62O1C0331张立卑19IS1DD3D72O1CD1D1卑20CS1DD1D2O1M1O2张盛卑NULLNULL1008D9201001103张盛卑NULLNULL1008Dselect*fromLOG_TABLEusemamedateSnaCna1?dboI2D13-D4-251S:4S:D6.5®2010MM161、实验目的（1）了解SQLSerer数据库系统中数据访问控制的基本方法（2）了解使用SSMS如何给用户授权（3）熟练掌握常用GRANT和REVOKE进行权限控制（4）验证数据库系统的访问控制2、实验平台使用SQLServer数据库管理系统提供的SSMS。3实验内容及要求实验应包括如下方面的内容：创建新的数据库用户对上一实验建立的表进行权限设置检查数据库用户的权限撤销用户权限4实验报告要求写出实验的基本过程。参考示例：在SQLServer中，对用户访问数据库的权限管理可以使用两种方式，一种是使用SSMS的图形界面进行管理，一种是使用SQL语句。3.1使用SSMS的图形界面创建用户并授权创建一个数据库用户：张明，授予他可以查询Student表的权限。1．使用SSMS的图形界面创建登录名在快捷菜单中选择“新建登录名”：

2．使用SSMS的图形界面创建数据库用户选择“ST”数据库，在快捷菜单中选择“新建登录名”:曰J田口数据库关罢图田一J表田一i视囹田一J局义词©一J可編程性田!__|ServiceBroker田一J存储□_J宝牡田口用户田口坤色数垢库用户-新建迭择页雪常规雪安全对象圏扩展属性壓脚本▼於帮助用户容(U):毬:登录容(!):证书容称(C):密钥容称(K):无登录容3)默认架构00：此用户拥有的架构(D)：理自的架构;db_acceEEailriin连接服务器：rgj-PC\SQL2008连接：聲杳看i车榇国件进度就绪□□□□□□db_backupoperatcrdbd：atai-eaderdb-datawritmrdbdillaidniindb_denydat：ireader数据库角色成员身份(M)：角色成员db_accesE：=LilnirLdb_backiipoperatofdb_dadb_datawritdb_dillaidniindb_denydatareaderdb_denydat：鈿■fitefAKrii±iTl确定取消3．使用SSMS的图形界面进行授权，设置用户对对象的访问权限

展开ST数据库的“安全性”下的“用户”，选择“张明db”。打开用户属性窗口：3.2使用SQL语句创建用户并授权在ST或TEST数据库中，使用SQL语句创建两个数据库用户，对数据库中的StudentSC、和Course表，分别对其授予不同的访问权限，如下表。表3.1授予用户权限

衣3.1授予用户权限用户数据库对象权限資询插入修改删除王明StudentJCourseSC李刚StudentJ77JCourseJ7SC1．创建登录名用户要访问数据库，必须首先登录到服务器上。因此，首先需要为用户创建登录名。以系统管理员账户登录，进入数据库服务器，执行以下的操作。A消息命令已成功完成（A消息命令已成功完成CREATELOGIN王明WITHpassword='123456'CREATELOGIN李刚WITHpassword='123456'0口宝牡日口轄名S_PolicyEventPro«##MS_PolicyTsqIExecutNTAUTHOR]TY\NETWtNTAUTHORJTV\SYSTErNTSERV1：CE\MSSQL$S（NTSERVl：CE\SQLAgent!rgy-PC\rgy李刚王明张明2．创建数据库用户用户登录服务器后，要进入某一数据库访问，需要为登录用户在该数据库中建立一个数据库用户名。对创建的两个登录名，在TEST数据库分别为其创建两个数据库用户：（1）切换到TEST数据：USETEST（2）创建登录名在TEST数据库中的用户：CREATEUSER王明dbFORLOGIN王明CREATEUSER李刚FORLOGIN李刚BLjTEST田口数搞库关票图E）□表田口视图田口同义词田口可褊程性0口ServiceBrokerF田口勰日口宝锂日口用户•邑dbo萇guestINFORMAT[ON_SC'A李刚強王明日b授权在数据库中添加新用户后，新用户没用访问数据库中数据的权限，需要数据库管理人员给其授予相关的权限后，才能执行相应的操作。按照表3.1，授权如下：（1）授予王明权限GRANTSELECTONStudentTO王明db（2）授予李刚权限GRANTSELECTONStudentTO李刚GRANTINSER,UPDAT,DELETEONStudentTO李刚GRANTSELECTONCourseTO李刚3.3检查权限控制分别以两个登录名登录数据库，执行一些对数据库的操作，检查用户否允许执行相关的操作。1．检查王明的权限以用户“王明”登录，执行如下的操作，检查能否正确的运行：SELECT*FROMStudentsnosnamessexsageadeptsclassstotal12D1DDDD1李勇男20CS1DD1222D1000D2划晨女19CS1DD1D32D1000D3划洋女NULLNULL1DD1D4EDITED赵斌男19IS1DD5D5测阳D22兆明明19CS1DD2D6匹1麗阿1兆立19IS1DD3D7匹1阳1D1李诫男20CS1DD1DS2D10D1D2张盛男NULLNULL1008D92D10D1D3张盛男NULLNULL1008DINSERTINTOStudent（Sno,Sname,Sclass）VALUES（'20101101，'高志','1008'）消息’拒缅了对对象1Studen