mysql数据库实验答案

.word...word..实验一创立、修改数据库和表构造1、用create建立教学数据库的五个根本表：(1)学生表〔学号，，性别，年龄〕,student((Sno,sname,ssex,sage)；⑵课程表〔课程号，课程名，学分〕，Course(o,ame,credit)；〔3〕选课表〔学号，课程号，成绩〕,SC(Sno,,o,grade)；教师表〔教师号，，性别，出生年月，系部，职称，地址〕，T(Tno，Tname，ssex，birthday，dept，title，address)；工资表〔教师号，根本工资，职务工资，合计〕，Salary仃no,gz，zwgz,hj)；CreateDatabaseStudentdefaultcharactersetutf8defaultCOLLATEutf8_bin;UseStudent;CreateTableStudent(SNochar(20)primarykey,SNamechar(20),SSexchar(4)default'男',SAgeint)ENGINE=InnoDB;CreateTableCourse(ochar(20)primarykey,amechar(20)NOTNULL,CReditfloat)ENGINE=InnoDB;CreateTableSC(SNochar(20)NOTNULL,ochar(20)NOTNULL,Gradefloat,PrimaryKey(SNo,o),ForeignKey(SNo)ReferencesStudent(SNo)OnDeleteCascade,ForeignKey(o)ReferencesCourse(o))ENGINE=InnoDB;CreateTableT(TNochar(20)PrimaryKey,TNamechar(20)NOTNULL,TSexchar(4)default'男',birthdayDateTime,deptchar(20),titlechar(20),addresschar(20))ENGINE=InnoDB;CreateTableSalary(TNochar(20)NOTNULL,gzfloat,zwgzfloat,hjfloat,ForeignKey(TNo)ReferencesT(TNo)OnDeleteCascade)ENGINE=InnoDB;2、用alter修改根本表〔1〕在已存在的学生表student中增加一个sdept〔系〕的新的属性列;altertableStudentaddDeptchar(20);〔2〕将学生表student中sname属性列的数据类型修改为变长字符串varchar(10)。alterableStudentmodifycolumsnamevarchar(10)3、建立一个临时表，然后将其删除CreateTabletemp(ANochar(20)NOTNULL,Bfloat,Cchar(10))Droptabletemp实验二建立与删除索引1、用createindex在学生表student的学号sno上建立聚簇索引。CreateClusteredIndexSNo_IndexOnStudent(SNo);2、在学生表student中，为XXsname建立非聚簇索引。CreateIndexSName_IndexOnStudent(SName);3、在课程表的课程号o上建立唯一索引。CreateUniqueIndexo_IndexOnCourse(o);4、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号一样时成绩为降序。CreateIndexSo_IndexOnSC(SNoASC,GradeDESC);5、用drop删除学生表student的索引。DropIndexStudent.SNo_Index;6、增加学生表student中XX唯一约束。AlterTableStudentAddUnique(SName);7、增加学生表student中性别'男'‘女'唯一约東。AlterTableStudentAddConstraint:SSexcheck(SSex='男1orSSex='女');8、增加学生表student中年龄18~25岁约束。AlterTableStudentAddConstraint:SAgecheck(SAge>=18AndSAge<=25);9、增加选课表SC中学号sno的外码约束。AlterTableSCAddForeignKey(SNo)referencesStudent(SNo);实验三数据的插入、更新及删除操作1、用insert输入数据。学生表student的数据991201X三22男计算机系991202李四21男信息系991101王五23男数学系991102陈六19男计算机系991103X七24女数学系000101X八22女信息系InsertIntoStudentValues('991201','X三'，'男22,'计算机科学与技术系');InsertIntoStudentValues('991202','李四','男',21,'信息科学系');InsertIntoStudentValues('991101','王五','男',23,'数理系');InsertIntoStudentValues('991102','陈六','男',19,'计算机科学与技术系');InsertIntoStudentValues('991103','X七'女24,'数理系');InsertIntoStudentValues('000101','X八'，'女22,信息科学系');课程表course的数据

1数学52数据构造43程序设计24数据库原理35操作系统3InsertIntoCourseValues（'1','数学',5）;InsertIntoCourseValues（'2','数据构造',4）;InsertIntoCourseValues（'3','程序设计',2）;InsertIntoCourseValues（'4','数据库原理',3）;InsertIntoCourseValues（'5','操作系统',3）;选课表sc的数据991201190991201580991201385991201490991102185991102298000101291InsertIntoSCValues（'991201','1',90）;InsertIntoscValues（'991201','5',80）;InsertIntoscValues（'991201','3',85）;InsertIntoscValues（'991201','4',90）;InsertIntoscValues（'991102','1',85）;InsertIntoscValues（'991102','2',98）;InsertIntoscValues（'000101','2',91）;根本表T的数据0001X三男1968-10信息副教授0002李四女1956-11信息教授1001王五男1973-07计算机讲师1008陈六男1970-08计算机副教授InsertIntoTValues（'0001','X三','男','1968-10-10',信息科学系'，副教授''）;InsertIntoTValues（'0002','李四','女','1956-11-10','信息科学系','教授',''）;InsertIntoTValues（'1001','王五','男','1973-07-20','计算机科学与技术系','讲师',''）;InsertIntoTValues（'1008','陈六','男','1970-08-20','计算机科学与技术系','副教授',''）;根本表Salary的数据00011000300130000021500500200010018002001000InsertIntoSalaryValues('0001',1000,300,1300);InsertIntoSalaryValues('0002',1500,500,2000);InsertIntoSalaryValues('1001',800,200,1000);*/2、用delete删除数据记录⑴删除教师表T中教师号为0001的元组。⑵删除教师表T中的全部数据。updatetsetbirthday='1961-10-04'whereTno='0001DeleteFromT;3、用update更新数据记录（1）把0001号教师的根本工资加100。（2）把所有教师的根本工资都加100。UpdateSalarySetgz=gz+100WhereTNo='0001'UpdateSalarySetgz=gz+100实验四数据的查询1、简单查询，用select检索〔1〕查询所有学生的根本情况。select*fromstudent;〔2〕查询教师每月应交纳的个人所得税。selecthj*0.005asmonthshuifromSalary;〔3〕查询X三与李四两位同学的根本情况。select*fromstudentwheresname='X三'orsname='李四'；〔4〕查询9911班学生的根本信息〔规定学生学号的前四位是班级号〕。select*fromstudentwheresnolike'9911%'；〔5〕查询所有年龄在20岁以下的学生XX及其年龄。selectsname,sagefromstudentwheresage<20；〔6〕查询选修了2门以上课程的学生学号。

selectsnofromSCgroupbysnohavingcount(*)>2;2、多表查询，用select检索〔1〕查询教师的收入情况，包括教师号、XX及月总收入。selectT.Tno,Tname,hj〃不能写成selectTno,因为Tno不明确fromT,SalarywhereT.Tno=Salary.Tno;〔2〕查询每个学生的学号、、选修课程及成绩。selectstudent.sno,sname,o,gradefromstudent,scwherestudent.sno=sc.sno;〔3〕查询每一门课的间接先修课。selectCA.oAS课程号,CB.PreCourseAS间接先修课号fromcourseCA,courseCBwhereCA.PreCourse=CB.oandCB.PreCourseisnotnull;〔4〕查询有一样地址的两位教师的信息。select*fromTTxwhereTx.addressin(selectaddressfromTTywhereTx.Tname<>Ty.Tname);select*fromTTx,TTywhereTx..address=Ty.AddressandTx.Tname<>Ty.Tname〔5〕查询选修2号课程且成绩在90分以上的所有学生。select*fromstudent,SCwherestudent.sno=SC.snoandSC.o='2'andSC.grade>90;〔6〕查询与王五在同一个系学习的学生。select*fromstudentwheresdept=(selectsdeptfromstudentwheresname='王五');实验五实验五1、建立男学生的视图，属性包括学号、、选修课程名和成绩。createviewboystudent_viewasselectstudent.sno,sname,o,gradefromstudent,SCwherestudent.ssex='男'andstudent.sno=SC.sno;2、在男学生视图中查询平均成绩大于80分的学生学号与XX。selectsno,snamefromboystudent_viewgroupbysno,snamehavingAVG(grade)>803、建立信息系选修了1号课程的学生的视图。createviewxinxi_view1asselectstudent.sno,sname,ssex,sagefromstudent,SCwherestudent.sdept='信息'andstudent.sno=SC.snoandSC.o=T4、建立信息系选修了1号课程且成绩在90分以上的学生的视图。createviewxinxi_view2asselectstudent.sno,sname,sage,ssexfromstudent,SCwherestudent.sdept='信息'andstudent.sno=SC.snoandSC.o='1'andSC.grade>905、建立计算机系选修了2号课程的学生的视图。createviewjisuanji_viewasselectstudent.sno,sname,sage,ssexfromstudent,SCwherestudent.sdept='计算机’andstudent.sno=SC.snoandSC.o='2'6、建立一个学生出生年份的视图。createviewyear_viewasselectstudent.sno,sname,2007-sageasbirthdayfromstudent7、建立一个计算机系学生的视图，并要求在进展修改、插入操作时，仍然要确保视图只有计算机系的学生。createviewjisuanji2_viewasselectstudent.sno,sname,sage,ssexfromstudentwheresdept='计算机’withcheckoption8、向学生视图中插入一条新记录〔951101，钱进，男，20〕createviewstudent_view1asselectsno,sname,ssex,sagefromstudent;insertintostudent_view1values('951101','钱进','男',20)9、将学生视图中学号为991101的学生XX改为“X平平"。updatestudent_view1setsname='X平平'wheresno='991101'10、删除计算机系视图中学号为991201的记录。deletefromjisuanji2_viewwheresno='991201'11、删除学生出生年份的视图。dropviewyear_view;实验六集合函数的应用1、使用select语句查询〔1〕9911班学生的人数和平均成绩〔规定学生学号的前四位是班级号〕SELECTCOUNT(sno)as总人数，AVG(sc.grade)AS平均分FROMscWHEREsnolike‘9912%'〔2〕每个学生的学号和相应的平均成绩SELECTsno,AVG(grade)FROM,scGROUPBYstudent.sno〔3〕成绩大于90分的学生学号SELECTsno,MAX(sc.grade)asmaxgradeFROMscGROUPBYsnoHAVINGMAX(grade)>90〔4〕月工资大于平均工资的教师号SELECTTNoFROMSalaryWHEREhj>=(SELECTAVG(hj)fromSalary)2、实验完成后，撤消建立的根本表和视图。实验七创立、执行、修改和删除存储过程1、建立查询计算机系女学生的存储过程。Usestudent;CREATEPROCEDURESELECT_WOMAN()BEGINSELECT*FROMstudentwheressex=女:end;callselect_wanman();2、定义一个存储过程不带参数，作用是查询选修了课程学生的学号，并执行这个存储过程。usestudent;Createprocedurenumofsc()BEGINselectsnofromSCgroupbysnohavingcount(*)>0;End;callnumofsc();3、定义一个带输岀参数的存储过程，作用是查询Student表中的学生个数，并赋值给输岀参数,执行该存储过程，同时通过输出参数来输出学生个数。CREATEPROCEDUREproc1(OUTsint)BEGINSELECTCOUNT(*)INTOsFROMstudent;END;执行CALLproc1(p0);SELECTp0AS'学生人数'；4、定义一个带输岀参数和输岀参数的存储过程，执行该存储过程，通过输人参数学号查到到XX,同时输人XX,。usestudent;Createproceduregetnamebysno(inxhchar(10),outnamechar(20))BeginSelectsnameintonamefromstudentwheresno=xh;End;执行setname=null;callgetnamebysno('000105',name);selectname;5、修改刚建立的存储过程。ALTERPROCEDURESELECT_'6、删除刚建立的存储过程。DROPPROCEDURESELECT_WOMAN实验八触发器的插入、删除、更新和创立1、对student表创立delete触发器，当删除某个学生记录时，同时在选课表SC中删除该学生的选课记录。mysql通过usestudent;InsertIntoStudentValues('000101','X八'，'女',22,信息科学系')；InsertIntoSCValues('000101','2',91);select*fromstudent；select*fromsc；createtriggert1AFTERdeleteonstudentFOREACHROWBEGINDeletefromSCWHEREsno=old.sno；END；2、在student表上定义了一个update触发程序，用于检查更新sage时将使用的新值，小于16时取得16,大于40时取40，并更改值。usestudent；createtriggerupd_checkbeforeupdateonstudentFOREACHROWbeginifnew.sage<16thensetnew.sage=16;elseifnew.sage>60thensetnew.sage=40;endif;end;select*fromstudent;updatestudentsetsage=2wheresno='000105';select*fromstudent;3、在student表上定义了一个insert触发程序，用于检查插入ssex时只能取男和女，输入其它时取NULL。usestudent;createtriggerins_checkbeforeinsertonstudentFOREACHROWbeginifnew.ssexo'男'andnew.ssex<>'女'thensetnew.ssex=null;endif;end;select*fromstudent;InsertIntoStudentValues('000106','陈规那么','五',22,'信息科学系');select*fromstudent;4、altertrigger5、droptrigger<触发名>on表名;6、createdatabasetrigg;CREATETABLEt_a(idsmallint(1)unsignedNOTNULLAUTO_INCREMENT,usernamevarchar(20)DEFAULTNULL,groupidmediumint(8)unsignedNOTNULLDEFAULT'0',PRIMARYKEY('id'))ENGINE=MyISAMAUTO_INCREMENT=16DEFAULTCHARSET=latin1;CREATETABLEt_b(idsmallint(1)unsignedNOTNULLAUTO_INCREMENT,usernamevarchar(20)DEFAULTNULL,groupidmediumint(8)unsignedNOTNULLDEFAULT'0',PRIMARYKEY('id'))ENGINE=MyISAMAUTO_INCREMENT=57DEFAULTCHARSET=latin1;CREATETRIGGERtr_a_insertAFTERINSERTONt_aFOREACHROWBEGININSERTINTOt_bSETusername=NEW.username,groupid=NEW.groupid;END;INSERTINTOt_a(username,groupid)VALUES('',123);SELECTid,username,groupidFROMt_a;SELECTid,username,groupidFROMt_b;CREATETRIGGER'tr_a_delete'AFTERDELETEONt_aFOREACHROWBEGINDELETEFROM't_b'WHEREusername=Old.usernameANDgroupid=OLD.groupid;END;deletefromt_awheregroupid=123;SELECTid,username,groupidFROMt_a;SELECTid,username,groupidFROMt_b;CREATETRIGGERtr_a_updateAFTERUPDATEONt_aFOREACHROWBEGINIFnew.groupid!=old.groupidORold.username!=new.usernameTHENUPDATEt_bSETgroupid=NEW.groupid,username=NEW.usernameWHEREusername=OLD.usernameANDgroupid=OLD.groupid;ENDIF;end;实验九MYSQL权限与平安1、用户的创立①利用CREATEUSER语句创立用户user1.use「2、user3，密码均为'123456'。mysql>createuser'user1''localhost'identifiedby'123456','user2''localhost'identifiedby'123456','user3''localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)利用INSERTINT0语句向USER表创立用户user4，密码均为'123456'。mysql>insertintomysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject)values('localhost','user5',password('123456'),'','','');Query0K,1rowaffected(0.00sec)利用GRANT语句创立用户user5，密码均为'123456'，且为全局级用户。mysql>grantselect,insert,updateon*.*to'user5''localhost'identifiedby'123456';Query0K,0rowsaffected(0.00sec)2、用户授权〔利用GRANT语句〕授予user1用户为数据库级用户，对数据库StudDB拥有所有权。mysql>grantallon*.*to'user1''localhost'identifiedby'123456';Query0K,0rowsaffected(0.01sec)授予user2用户为表级用户，对StudDB中的student表select，create,drop权限。mysql>grantselect,create,droponStudDB.studentto'user2''localhost'identifiedby'123456';Query0K,0rowsaffected(0.00sec)授予user3用户为列级用户，对StudDB中的student表的sname列用户select和update权限。mysql>grantselect,update(sname)onStudDB.studentto'user2''localhost'identifiedby'123456';Query0K,0rowsaffected(0.02sec)授予user4用户为过程级用户，对StudDB.student中的numofstudent存储过程拥有EXECUTE执行的权限。mysql>grantexecuteonprocedurenumofstudentto'user4''localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进展验证。查看权限mysql>select*frommysql.userwhereuser='test1';Emptyset(0.00sec)mysql>showgrantsfor'root''localhost';select*frommysql.userwhereuser='root';mysql>deletefrommysql.userwhereuser="root"andhost="localhost";QueryOK,1rowaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)删除权限mysql>dropuser'user4''localhost';QueryOK,0rowsaffected(0.00sec)3、权限回收收回user3用户对StudDB中的student表的sname列的update权限。mysql>revokeupdateon*.*from'user2''localhost';QueryOK,0rowsaffected(0.00sec)收回user4用户对StudDB.student中的numofstudent存储过程拥有EXECUTE执行的权限。mysql>revokeexecuteonprocedurenumofstudentfrom'user4''localhost';QueryOK,0rowsaffected(0.00sec)利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进展验证。4、用户密码及用户名修改①利用使用SETpassword修改和UPDATE方式修改root用户的密码。mysql>setpassword=password("123456");QueryOK,0rowsaffected(0.00sec)mysql>updatemysql.usersetpassword=password('123')whereuser="root"andhost="locahst";QueryOK,0rowsaffected(0.00sec)Rowsmatched:0Changed:0Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.02sec)②利用SETpasswordfor'用户'主机'=password(<(新密码")方法修改user1用户的密码。5、超级用户ROOT密码丧失的找回①利用skip-grant-tables配置参数修改配置文件，实现无密码登陆MYSQL效劳器，并利用UPDATE语句修改密码。实验十：数据的复制与恢复1、备份所有数据库到D盘，文件名自拟。

•：=：■■.C:\WIND0WS\system32\cmd.eKe「C：XDocumentsandSettin^s\fldministrator>cdC：XwampMiysq1\binC：Xwamp\m5Psq1Xbin>nysqldump—uroot一一all-database>D：Xall.sqlC：\wamp\jTi5Psql\bin>dirD：\驱动器I＞中的卷没有标签。卷的序列1号是8B74-2906D：\的目录2007-09-0311:18<DIR>Adobe2007-09-0311:56<DIR>DreamweauerMX20042007-09-0311:58<DIR>ExtensionManager2007-09-0312:00<DIR>FireworksMK20042007-09-0312:03<DIR>FlashMX20942007-09-0312:18<DIR>MicrosoftOffice2007-09-0314:50<DIR>fpeeime2007-09-0409：31<DIR>IBN智能词典20002007-09-0409:33<DIR>UiaUoiceOut：loud2007-09-0623:11<DIR>PpogpamFiles2009-04-2815:31296,435all.sail1个文件296,435字书10个目录21,712,535,552旬角字节C：\wamp'Mn5Psql\bin>_2、将表emp的备份文件emp.sql复原到test数据库中。usetestDatabasechang-ed.m<；3ql>shovjtables；+十+十1rouInset<0.00sec>C：XjwampXni/sqlMjin>mysql-uroottest<D：\emp.sq;l号sqlShin》将表吨的备份文件mmp述还原sJtestiJt号sqlShin》mysql2>sIioljtables；!Tables_in_test1!|emp|!ite:■sec>T112roueinset.OS3、备份数据库test到D盘，文件名自拟。G\WIND0W5\s：ystcm32\cmcl-eHeC：SwampXmysqlXbin>ni«!sqldLimp-Liroottest>D:\test・5：里1_C：SwampXmysqlXbin>dipD：X驱动盎»申的卷没有标整。卷的序夕［专是8B7^-2906D：\莊巨录2007-09-03il：18<DIR>Adobe如阳-丽-的ll：5fi<DTR>DreamueauerMX20042007-09-03il：18<DIR>Adobe如阳-丽-的ll：5fi<DTR>DreamueauerMX2004対即-丽-昭11:58<