mysql数据库实验答案

/实验一创建、修改数据库和表结构1、用create建立教学数据库的五个基本表：学生表（学号，姓名，性别，年龄），student((Sno，sname，ssex，sage)；（2）课程表（课程号，课程名，学分），Course(Cno,Cname,credit)；（3）选课表（学号，课程号，成绩），SC(Sno,,Cno,grade)；(4)教师表（教师号，姓名，性别，出生年月，系部，职称，地址），T(Tno，Tname，ssex，birthday，dept，title，address)；(5)工资表（教师号，基本工资，职务工资，合计），Salary(Tno，jbgz，zwgz，hj)；CreateDatabaseStudentdefaultcharactersetutf8defaultCOLLATEutf8_bin;UseStudent;CreateTableStudent( SNo char(20)primarykey, SName char(20), SSex char(4)default'男', SAge int)ENGINE=InnoDB;CreateTableCourse( CNo char(20)primarykey, CName char(20)NOTNULL, CRedit float)ENGINE=InnoDB;CreateTable SNo char(20)NOTNULL, CNo char(20)NOTNULL, Grade float, PrimaryKey(SNo,CNo), ForeignKey(SNo)ReferencesStudent(SNo)OnDeleteCascade, ForeignKey(CNo)ReferencesCourse(CNo))ENGINE=InnoDB;CreateTableT( TNo char(20)PrimaryKey, TName char(20)NOTNULL, TSex char(4)default'男', birthdayDateTime, dept char(20), title char(20), addresschar(20))ENGINE=InnoDB;CreateTableSalary( TNo char(20)NOTNULL, jbgz float, zwgz float, hj float, ForeignKey(TNo)ReferencesT(TNo)OnDeleteCascade)ENGINE=InnoDB;2、用alter修改基本表（1）在已存在的学生表student中增加一个sdept（系）的新的属性列；altertableStudentaddDeptchar(20);（2）将学生表student中sname属性列的数据类型修改为变长字符串varchar(10)。alterableStudentmodifycolumsnamevarchar(10)3、建立一个临时表，然后将其删除CreateTabletemp( ANo char(20)NOTNULL,B float,Cchar(10))Droptabletemp实验二建立与删除索引1、用createindex在学生表student的学号sno上建立聚簇索引。CreateClusteredIndexSNo_IndexOnStudent(SNo);2、在学生表student中，为姓名sname建立非聚簇索引。CreateIndexSName_IndexOnStudent(SName);3、在课程表的课程号Cno上建立唯一索引。CreateUniqueIndexCNo_IndexOnCourse(CNo);4、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相同时成绩为降序。CreateIndexSCNo_IndexOnSC(SNoASC,GradeDESC);5、用drop删除学生表student的索引。DropIndexStudent.SNo_Index;6、增加学生表student中姓名唯一约束。AlterTableStudentAddUnique(SName);7、增加学生表student中性别‘男’、‘女’唯一约束。AlterTableStudentAddConstraint:SSexcheck(SSex='男'orSSex='女');8、增加学生表student中年龄18~25岁约束。AlterTableStudentAddConstraint:SAgecheck(SAge>=18AndSAge<=25);9、增加选课表SC中学号sno的外码约束。AlterTable-实验三数据的插入、更新与删除操作1、用insert输入数据。学生表student的数据991201张三22男计算机系991202李四21男信息系991101王五23男数学系991102陈六19男计算机系991103吴七24女数学系000101刘八22女信息系InsertIntoStudentValues('991201','张三','男',22,'计算机科学与技术系');InsertIntoStudentValues('991202','李四','男',21,'信息科学系');InsertIntoStudentValues('991101','王五','男',23,'数理系');InsertIntoStudentValues('991102','陈六','男',19,'计算机科学与技术系');InsertIntoStudentValues('991103','吴七','女',24,'数理系');InsertIntoStudentValues('000101','刘八','女',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的数据0001张三男1968-10信息副教授湘潭0002李四女1956-11信息教授长沙1001王五男1973-07计算机讲师湘潭1008陈六男1970-08计算机副教授北京InsertIntoTValues('0001','张三','男','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删除数据记录（1）删除教师表T中教师号为0001的元组。（2）删除教师表T中的全部数据。updatetsetbirthday='1961-10-04'whereTno='0001'DeleteFromT;3、用update更新数据记录把0001号教师的基本工资加100。把所有教师的基本工资都加100。UpdateSalarySetjbgz=jbgz+100WhereTNo='0001'UpdateSalarySetjbgz=jbgz+100实验四数据的查询1、简单查询，用select检索（1）查询所有学生的基本情况。select*fromstudent;（2）查询教师每月应交纳的个人所得税。selecthj*0.005asmonthshuifromSalary;（3）查询张三与李四两位同学的基本情况。select*fromstudentwheresname='张三'orsname='李四';（4）查询9911班学生的基本信息（规定学生学号的前四位是班级号）。select*fromstudentwheresnolike'9911%';（5）查询所有年龄在20岁以下的学生姓名与其年龄。selectsname,sagefromstudentwheresage<20;（6）查询选修了2门以上课程的学生学号。selectsnofromSCgroupbysno havingcount(*)>2;2、多表查询，用select检索（1）查询教师的收入情况，包括教师号、姓名与月总收入。selectT.Tno,Tname,hj//不能写成selectTno,因为Tno不明确fromT,SalarywhereT.Tno=Salary.Tno;（2）查询每个学生的学号、姓名、选修课程与成绩。selectstudent.sno,sname,cno,gradefromstudent,scwherestudent.sno=sc.sno;（3）查询每一门课的间接先修课。selectCA.cnoAS课程号,CB.PreCourseAS间接先修课号fromcourseCA,courseCBwhereCA.PreCourse=CB.cnoandCB.PreCourseisnotnull;（4）查询有相同地址的两位教师的信息。select*fromTTxwhereTx.addressin(selectaddress fromTTywhereTx.Tname<>Ty.Tname);select*fromTTx,TTywhereTx..address=Ty.AddressandTx.Tname<>Ty.Tname（5）查询选修2号课程且成绩在90分以上的所有学生。select*fromstudent,SCwherestudent.sno=SC.snoandSC.cno='2'andSC.grade>90; （6）查询与王五在同一个系学习的学生。select*fromstudentwheresdept=(selectsdept fromstudentwheresname='王五');实验五视图1、建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。createviewboystudent_viewasselectstudent.sno,sname,cno,grade fromstudent,SC wherestudent.ssex=’男’andstudent.sno=SC.sno;2、在男学生视图中查询平均成绩大于80分的学生学号与姓名。selectsno,snamefromboystudent_viewgroupbysno,snamehavingAVG(grade)>803、建立信息系选修了1号课程的学生的视图。createviewxinxi_view1asselectstudent.sno,sname,ssex,sage fromstudent,SC wherestudent.sdept='信息'andstudent.sno=SC.snoandSC.cno='1'4、建立信息系选修了1号课程且成绩在90分以上的学生的视图。createviewxinxi_view2asselectstudent.sno,sname,sage,ssex fromstudent,SC wherestudent.sdept='信息'andstudent.sno=SC.snoandSC.cno='1'andSC.grade>905、建立计算机系选修了2号课程的学生的视图。createviewjisuanji_viewasselectstudent.sno,sname,sage,ssex fromstudent,SC wherestudent.sdept='计算机'andstudent.sno=SC.snoandSC.cno='2'6、建立一个学生出生年份的视图。createviewyear_viewasselectstudent.sno,sname,2007-sageasbirthdayfromstudent7、建立一个计算机系学生的视图，并要求在进行修改、插入操作时，仍然要确保视图只有计算机系的学生。createviewjisuanji2_viewasselectstudent.sno,sname,sage,ssex fromstudent wheresdept='计算机'withcheckoption8、向学生视图中插入一条新记录（951101，钱进，男，20）createviewstudent_view1asselectsno,sname,ssex,sage fromstudent; insertintostudent_view1values('951101','钱进','男',20)9、将学生视图中学号为991101的学生姓名改为“刘平平”。updatestudent_view1setsname='刘平平'wheresno='991101'10、删除计算机系视图中学号为991201的记录。deletefromjisuanji2_view wheresno='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()BEGINselectsnofromSCgroupbysno havingcount(*)>0;End;callnumofsc();3、定义一个带输出参数的存储过程，作用是查询Student表中的学生个数，并赋值给输出参数,执行该存储过程，同时通过输出参数来输出学生个数。CREATEPROCEDUREproc1(OUTsint)BEGINSELECTCOUNT(*)INTOsFROMstudent;END;执行CALLproc1(@p0);SELECT@p0AS`学生人数`;4、定义一个带输出参数和输出参数的存储过程，执行该存储过程，通过输入参数学号,查到到姓名,同时输入姓名,。usestudent;Createproceduregetnamebysno(inxhchar(10),outnamechar(20))BeginSelectsnameintonamefromstudentwheresno=xh;End;执行set@name=null;callgetnamebysno('000105',@name);select@name;5、修改刚建立的存储过程。ALTERPROCEDURESELECT_'6、删除刚建立的存储过程。DROPPROCEDURESELECT_WOMAN实验八触发器的插入、删除、更新和创建对student表创建delete触发器，当删除某个学生记录时，同时在选课表SC中删除该学生的选课记录。mysql通过usestudent;InsertIntoStudentValues('000101','刘八','女',22,'信息科学系');InsertIntoSCValues('000101','2',91);select*fromstudent;select*fromsc;createtriggert1AFTERdeleteonstudentFOREACHROWBEGINDeletefromSCWHEREsno=old.sno;END;在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;在student表上定义了一个insert触发程序，用于检查插入ssex时只能取男和女，输入其它时取NULL。usestudent;createtriggerins_checkbeforeinsertonstudentFOREACHROWbeginifnew.ssex<>'男'andnew.ssex<>'女'thensetnew.ssex=null;endif;end;select*fromstudent;InsertIntoStudentValues('000106','陈规则','五',22,'信息科学系');select*fromstudent;altertriggerdroptrigger<触发名>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、user2、user3，密码均为’123456’。mysql>createuser'user1'@'localhost'identifiedby'123456','user2'@'localhost'identifiedby'123456','user3'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)②利用INSERTINTO语句向USER表创建用户user4，密码均为’123456’。mysql>insertintomysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject)values('localhost','user5',password('123456'),'','','');QueryOK,1rowaffected(0.00sec)③利用GRANT语句创建用户user5，密码均为’123456’，且为全局级用户。mysql>grantselect,insert,updateon*.*to'user5'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)2、用户授权（利用GRANT语句）①授予user1用户为数据库级用户，对数据库StudDB拥有所有权。mysql>grantallon*.*to'user1'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.01sec)②授予user2用户为表级用户，对StudDB中的student表select，create,drop权限。mysql>grantselect,create,droponStudDB.studentto'user2'@'localhost'identifiedby'123456';QueryOK,0rowsaffected(0.00sec)③授予user3用户为列级用户，对StudDB中的student表的sname列用户select和update权限。mysql>grantselect,update(sname)onStudDB.studentto'user2'@'localhost'identifiedby'123456';QueryOK,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