mysql数据库实验答案

1、实验一创建、修改数据库和表结构仁用Create建立教学数据库的五个基本表：(1) 学生表(学号，姓名，性别，年龄)，StUdent(SnO, sname, ssex, Sage);(2) 课程表(课程号，课程名，学分),COUrSe (Cno, Cname, Credit);(3) 选课表(学号，课程号，成绩)，SC (Sno, Cno, grade );(4) 教师表(教师号，姓名，性别，出生年月，系部，职称，地址)，T(Tno, Tname, ssex, birthday, dept, title, address) J工资表(教师号，基本工资，职务工资，合计)，Salary(Tno, j

2、bgz, zwgz, hj);Create Database StUdent defauIt CharaCter Set utf8default COLLATE utf8_binJUSe Student;Create Table StUClent (SNOChar (20) Pr imary key,SName Char (20),SSeX Char(4) defauIt "男打SAge int)ENGINE=InnoDB;Create TabIe COUrSe(CNOChar (20) Primary key,CName Char (20) NOT NULL,CReditfI Oa

3、t)ENGINE=InnoDB;Create TabIe SC(SNOChar (20) NOT NULL,CNOChar (20) NOT NULL,Grade fI oat,Primary Key (SNo, CNo),CaSCade)FOreign Key (SNO) ReferenCeS StUdent (SNO) On De I eteFOreign Key (CNO) ReferenCeS COUrSe (CNO)ENGINE=InnoDB;Create TabIe T (TNOChar (20) Primary Key,TName Char (20) NQT NULL,TSeX

4、Char (4) defauIt "男'，birthday DateTime) dept Char (20), title Char (20), address Char (20)ENGINE=InnoDB;Create Table SaIary(TNoChar (20) NOT NULL, jbgz fIoat,ZWgZ float, hj float,FOreign Key(TNO) ReferenCeS T (TNO) On De IeteCaSCaCle)ENGINE=InnoDB;2、用alter修改基本表(1)在已存在的学生表StUdent中增加一个SdePt (

5、系)的新的属性列；alter table StUdent add DePt Char (20);(2)将学生表StUdent中Sname属性列的数据类型修改为变长字符串VarChar (10) oalter able StUdent modify COIUm Sname VarChar (10)3、建立一个临时表，然后将其删除Create TabIe temp (ANOChar (20) NoT NULL, B f I oat, C Char(IO)DrOP table temp实验二建立与删除索引*1、用Create index在学生表StUdent的学号Sno上建立聚簇索引。Create

6、CIUStered IndeX SNO_IndeX On StUdent (SNO);2、在学生表StUdent中，为姓名Sname建立非聚簇索引。Create IncleX SName_IndeX On StUdent (SName) J3、在课程表的课程号CnO上建立唯一索引。Create Un i que IndeX CNO_Index On COUrSe(CNO);4、在选课表的学号sno、成绩Grade JL建立复合索引，要求学号为升 序，学号相同时成绩为降序。Create IndeX SCNO_IndeX On SC(SNO ASC, Grade DESC) J5、用drop删除学

7、生表StUdent的索引。DrOP IndeX ;6、增加学生表StUdent中姓名唯一约束。Alter Table StUdent Add Unique(SName);7、增加学生表StUdent中性别'男，、'女'唯一约束。AIter Table StUdent Add ConstraintzSSex CheCk(SSeX 二'男'orSSeX ='女')；8、增加学生表StUdent中年龄1825岁约束。Alter Table Student Add ConstraintzSAge CheCk(SAg >= 18 AndSAg

8、e <= 25);9、增加选课表SC中学号SnO的外码约束。AI ter TabIe SC Add FOre i gn Key (SNO) references StUdent (SNO);实验三数据的插入、更新及删除操作1用insert输入数据。991201张三22男计算机系991202李四21男信息系991101王五23男数学系991102陈六19男计算机系991103吴七24女数学系OOOloI刘八22女信息系学生表StUdent的数据1计算ValUeS(199120,'男SInSert IntO StUdent'张三InSert IrTtO StUdentValU

9、eS(1991202f,1李四，,21,'信息机科学与技术系);科学系')；InSert IntO StUdentValues(,99110,'王五'男身 23,'数理系')；InSert Into StUdent ValUeS(,991102,'陈六'，'男'，19,'计算机科学与技术系）；InSert IntO StUdent VaIUeS（'991103,'吴七'，'女'，24,'数理系InSert IntO StUdent ValUeS（,00010,

10、'刘八'，'女'，22,'信息科学系'）；课程表COUrSe的数据1数学52数据结构43程序设计24数据库原 理35操作系统3InSert IntO COUrSe VaIUeS(f 1l,数学,5);InSertIntOCOUrSe VaIUeS(f2l数据结构S 4);InSertIntOCOUrSe VaIUeS(f3f'程序设计2);InSertIrTtO COUrSeValUeS(,4,'数据库原理3);InSertIrTto COUrSeVaIUeS (f5,'操作系统3);9912011909912015809

11、91201385991201490991102185选课表SC的数据991102298OOOlOl291ValUeS (f 99120,SCInSertIntOf1f,90);InSertIntOSCVaIUeS(199120,f51,80);InSertIrTtOSCValUeS(199120185);InSertIrTtOSCValUeS(1991201,ll4f,90);InSertIntOSCValUeS(1991102,l1f,85);InSertIntOSCValUeS(f991102,98);InSertIntOSCValUeS(IOOo10,l2f,91);基本表T的数据

12、9;男，,1968-10-10,'信息InSert IntO T ValUeS(,0002,李四'女1956-11-10,'信息0001张三男1968-10信息副教 授湘潭0002李四女1956-11信息教授长沙1001王五男1973-07计算机讲师湘潭1008陈六男1970-08计算机副教 授北京InSert IntO T ValUeS(,000,'张三'，科学系'副教授'，'湘潭');科学系，教授'，'长沙J;InSert IntO T ValUeS('1001'王五男，,1973-07

13、-20,'计算机科学与技术系，讲师湘潭');InSert IntO T ValUeS(,1008,'陈六'男，,1970-08-20,'计算机科学与技术系，副教授北京')；基本表SaIary的数据00011000300130000021500500200010018002001000InSert Into SaIary VaIUeS (,000, 1000, 300, 1300);InSert IntO Salary ValUeS(,0002', 1500, 500, 2000)；InSert IntO SaIary ValUeS(

14、9;1001800, 200, IOOO);*/2、用de I ete删除数据记录(1) 删除教师表T中教师号为OOoI的元组。(2) 删除教师表T中的全部数据。UPdate t Set b i rthday= * 1961-10-O4' Where TnO ='0001De Iete From T;3. 用UPdate更新数据记录(D 把0001号教师的基本工资加100o(2) 把所有教师的基本工资都加100oUPdate SaIary Set JbgZ = jbgz + 100 Where TNO = , 0001UPdate Salary Set jbgz = jbgz

15、+ 100实验四数据的查询1 简单查询，用Se I ect检索(1)查询所有学生的基本情况。SeIect * from StUderTt;(2)查询教师每月应交纳的个人所得税。SeIect hj* as monthshui from Salary;(3) 查询张三与李四两位同学的基本情况。Se Iect * from StUdent Where Sname=I 张三'Or Sname=I 李四(4) 查询9911班学生的基本信息(规定学生学号的前四位是班级号)。SeIect * from StUdent Where SnO I ike ,9911%,;(5) 查询所有年龄在20岁以下的

16、学生姓名及其年龄。SeleCt Snamel Sage from StUdent Where sage<20;(6) 查询选修了 2门以上课程的学生学号。SeleCt SnO from SC group by SnOhav i ng COUnt (*)>2;2、多表查询，用Se I ect检索(1)查询教师的收入情况，包括教师号、姓名及月总收入。Se Iect ,Tname, hjaddress=Ty. AddreSS and O(5) 查询选修2号课程且成绩在90分以上的所有学生。Se Iect * from student,SCWhere = and ,2, and >9

17、0;(6) 查询与王五在同一个系学习的学生。SeleCt *from StUdertWhere SdePt= (Se I ect SclePtfrom StUdent Where Sname=I XiI);实验五视图仁 建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。Create VieW boystudent_viewas Se I ect , Snafne, cno, gradefrom student, SCWhere = 男'and =SC sno;2、在男学生视图中查询平均成绩大于80分的学生学号与姓名。Se Iect sno, Snamefrom boystuden

18、t_vi ew group by Sno, Snamehaving AVG(grade)>803、建立信息系选修了 1号课程的学生的视图。Create VieW XinXi_VieWlas Se Iect , sname, ssex, Sagefrom student,SCWhere ='信息'and = and =, 1'4、建立信息系选修了 1号课程且成绩在90分以上的学生的视图。Create VieW XinXi view2as Se Iect ,Sname, Saget SSeXfrom student, SCWhere ='信息'and

19、- and =' 1, and >905、建立计算机系选修了 2号课程的学生的视图。Create VieW j isuanj i_VieWas Se Iect ,Sname, sage, SSeXfrom student, SCWhere ='计算机，and = and =,26、建立一个学生出生年份的视图。Create VieW year_VieWas Se Iect , sname, 2007-Sage as b i rthday from StUdent7、建立一个计算机系学生的视图，并要求在进行修改、插入操作时,仍然要确保视图只有计算机系的学生。Create Vi

20、eW j i SUanj i 2_v i ewas Se Iect , sname, sage, SSeXfrom StUdentWhere SdePt=I 计算机With CheCk OPt i On8. 向学生视图中插入一条新记录（951101,钱进，男，20）Create VieW StUdent VieWl as SeIeCt sno,sname, ssex, Sagefrom StUdent;insert into StUdent_VieWl ValUeS（'951101','钱进'，'男,20）9、将学生视图中学号为991101的学生姓名改为

21、“刘平平”。UPdate StUderrt_view1Set Sname='刘 平平'Where sno=,9911010. 删除计算机系视图中学号为991201的记录。de Ietefrom jisuanj i2_VieWWhere sno=,9912011删除学生出生年份的视图。drop VieW year_view;实验六集合函数的应用1、使用Se I ect语句查询(1) 9911班学生的人数和平均成绩(规定学生学号的前四位是班级号)SELECT COUNT (SnO) as 总人数，AVGAS 平均分 FROM SCWHERE SnO Iike '9912%&

22、#39;(2) 每个学生的学号和相应的平均成绩SELECT sno, AVG (grade) FROM, SCGROUP BY(3) 成绩大于90分的学生学号SELECT sno, MAX as maxgrade FROM SCGROUP BY SnOHAVING MAX(grade)>90(4) 月工资大于平均工资的教师号SELECT TNo FROM SalaryWHERE hj>=( SELECT AVG (hj)from Salary )2、实验完成后，撤消建立的基本表和视图。 实验七 创建、执行、修改和删除存储过程 仁建立查询计算机系女学生的存储过程。 USe StUde

23、nt;CREATE PROCEDURE SELECT_WOiVlAN ()BEGlNSELECT * FROM StUdent Where ssex='女'； end;Ca I I Se Iect_wanman();2、定义一个存储过程不带参数，作用是查询选修了课程学生的学号, 并执行这个存储过程。USe StUdent;Create PrOCedUre numofsc OBEGlNSe Iect SnO from SC group by SnOhav i ng COUnt (*)>0;End;CaI I nUmOfSC O;3、定义一个带输出参数的存储过程，作用是查询S

24、tUdent表中的学生个数，并赋值给输出参数,执行该存储过程，同时通过输出参数来输出学生个数。 CREATE PROCEDURE PrOCl (OUT S int) BEGINSELECT COUNT(*) INTO S FRoM student;END;执行 CALL PrOCl ( p0 );SELECT Po AS '学生人数4、定义一个带输出参数和输出参数的存储过程，执行该存储过程，通过输入参数学号，查到到姓名，同时输入姓名，OUSe student;Create PrOCedUre getnamebysno(in xh Char (10), OUt name Char (20

25、)BeginSeIect Sname into name from StUdent Where SnO=Xh;End;执行Set name=nu11;Ca 11 getnamebysno(10001051, ©name);SeIect name;5、修改刚建立的存储过程。ALTER PROCEDURE SELECT 16、删除刚建立的存储过程。DROP PROCEDURE SELECT WOMAN实验八 触发器的插入、删除、更新和创建1、对StUdent表创建de I ete触发器，当删除某个学生记录时，同时在 选课表SC中删除该学生的选课记录。mysq I 通过USe studen

26、t;InSert IntO StUden VaIUeSCOOolO,'刘八'，'女'，22,' 信息科学系)；InSert IntO SC ValUeS('00010, ,2, 91);SeIeCt * from StUdert ;SeIeCt * from SC ;Create trigger t1 AFTER de Iete On StUdentFOR EACH ROW BEGINDe Iete from SC WHERE SnO =;END;2、在StUdent表上定义了 一个UPdate触发程序，用于检查更新Sage 时将使用的新值，小于

27、16时取得16,大于40时取40,并更改值。USe StUdent;Create trigger UPd_CheCk before UPdate On StUdentFOR EACH ROWbeginif <16 thenSet =16;el seif >60 thenSet =40;end if;end;SeleCt * from StUdent;UPdate StUdent Set Sage=2 Where sno=,000105,JSeleCt * from StUdent ;3x在StUdent表上定义了一个insert触发程序，用于检查插入SSeX时只能取男和女，输入其它

28、时取NULL。USe StUdent;Create trigger ins_check before insert On StUdentFOR EACH ROWbeginif <>l 男'and 女'thenSet =nu11;SeleCt * from StUdent;Insert IntO StUden t Va lues （OOOlO6 '陈规则'，'五'，22,信息科学系）；SeleCt * from StUdent;4、alter tr igger5x drop trigger 触发名 On 表名;b、 Create dat

29、abase trigg;CREATE TABLE t_a (id SmaIIint(I) UnSigned NOT NULL AUTO_INCREMENTtUSername VarChar (20) DEFAULT NULL,group id med i umint (8) UnS igned NOT NULL DEFAULT fO1,PRlMARY KEY CiCr)ENGINE=MyISAM AUTO_INCREMENT=I6 DEFAULT CHARSET=Iatin1;CREATE TABLE t_b (id SmaIlint(I) UnSigned NOT NULL AUTO.INC

30、REMENT,USername VarChar (20) DEFAULT NULL,group id med i um i nt(8) UnSigned NOT NULL DEFAULT ,0',PRlMARY KEY CiCr)ENGINE=MylSAM AUTO_INCREMENT=57 DEFAULT CHARSET=IatinI;CREATETRIGGER tr_a_insert AFTER INSERT ON t_aFOR EACH ROW BEGININSERT INTO t_b SET USername = , group id=;END；INSERT INTO t_a

31、(USername9 group id) VALUES (,1,123);SELECT i d, USername, group i Cl FROM t_a;SELECT id, username, group id FROM t_b;CREATE TRlGGER r_a_delete AFTER DELETE ON t_aFOR EACH RaW BEGlNDELETE FROM 壮J WHERE USername= AND group id=;END;de Iete from t_a Where groupid=123;SELECT i d, USername, group i Cl FR

32、OM t_a;SELECT i d, USername, group i Cl FROM t_b;CREATE TRlGGER tr_a_UPdate AFTER UPDATE ON t_aFOR EACH ROW BEGINIF != OR != THENUPDATE t_b SET group id=, USername=WHERE USername = AND group id =;END IF;end;实验九MYSQL权限与安全1用户的创建 利用CREATE USER语句创建用户USerR USer2、user3,密码均为'123456, OmysqI> Create U

33、Ser IUSerI IOCaIhost'identified by'123456,user2,*IOCalhost,i dent i f i edby ,123456l,user3,1IOCaIhOStl identified by ,123456,;QUery OK, O rows affected SeC) 利用INSERT INTO语句向USER表创建用户user4,密码均为'123456, OmysqI>insertinto(host, user, PaSSWOrd, SSl_CiPher, x509_issuer, x509_SUbjeCt)VaIUe

34、S (,IOCaIhOStl,IUSer5f, PaSSWOrCI (,123456l), 1 1 r )；QUery OK, 1 row affected SeC) 利用GRANT语句创建用户User5,密码均为'123456,且为全 局级用户。mysq I >grartSe I ect, i nsert, UPdateOn *. * to'user5''Iocalhost' identified by,123456,;QUery OK, O rows affected SeC)2、用户授权(利用GRANT语句) 授予USerl用户为数据库级用户

35、，对数据库StUdDB拥有所有权。mysqI> grant al I On * * to ,USerlIOCalhost' identif ied by,123456,;QUery OKt O rows affected sec) 授予user2用户为表级用户，对StUdDB中的StUdent表select,Create, drop 权卩艮。mysq I > grant SeleCtt Create, drop On to ,user2lf IOCaIhOStl identified by,123456,;QUery OK, O rows affected SeC) 授予u

36、ser3用户为列级用户，对StUdDB中的StUdent表的Snanle 列用户Se I ect和UPdate权限。mysqI> grant Se Iect,UPdate (Sname) Onto,user2*'Iocalhost' identified by,123456,JQUery OK, O rows affected SeC) 授予user4用户为过程级用户，对中的numofstudent存储过程 拥有EXECUTE执行的权限。mysqI> grant execute On PrOCedUre numofstudent to ,user4,* IOCalh

37、ost' identified by,123456,;QUery OK, O rows affected SeC)利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进行 验证。查看权限mysqI> SeIect * from Where USer=IteStlf;EinPty Set SeC)mysqI> ShOW grants for r root'IOCalhOSt'SeIeCt * from Where USer=IroOtmysqI> delete from Where user=,frootfl and host=1' IOCa

38、lhOSt'"QUery OK, 1 row affected SeC)mysqI> flush PriVi leges;QUery OK, 0 rows affected SeC)删除权限mysqI> drop USer IUSer4''IOCalhost,;QUery OK, O rows affected SeC)3、权限回收收回user3用户对StUdDB中的StUdent表的Snanle列的UPdate权限。mysqI> revoke update On *. * from IUSer2*©* IOCalhOStlJQUe

39、ry OK, O rows affected SeC)收回user4用户对中的numofstudent存储过程拥有EXECUTE执 行的权限。mysqI> revoke execute On PrOCedUre numofstudentfrom,usr4,fIOCalhOStl;QUery OKl 0 rows affected SeC)利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进行 验证。4、用户密码及用户名修改利用使用SET PaSSWOrd修改和UPDATE方式修改root用户的密码Omysq I > Set PaSSWOrd=PaSSWOrd (123456,

40、r);QUery OK, 0 rows affected SeC)mysqI> UPdate Set PaSSWOrd=PaSSWOrd (f123r) Where user=,f root, and host="Iocahst"QUery OK, O rows affected SeC)ROWS matched: O Changed: O Warnings: OmysqI> flush PriVi leges;QUery OK, O rows affected SeC)利用 SET PaSSWOrd for ' 用户'主机二PaSSWOrd (

41、"新密码”)方法修改USerl用户的密码。5、超级用户RoOT密码丢失的找回利用sk i p-grant-tab I es配置参数修改配置文件，实现无密码登陆MYSQL服务器，并利用UPDATE语句修改密码。实验十：数据的复制与恢复K备份所有数据库到D盘，文件名自拟。CA C:WIND0WSsystem32cmd.exeC:Documents and SettingSdminist*ator>cd C：XwanpMnysqlXbinC: vjanp>5Fsqlbin>ysqIdunp uroot 一一all-database > D： all.sql C: v

42、ap5Fsqlbin>dir D： 驱动器D中的卷没有标签。暮的厚列号是8B74-29060：的目录2007-09-0311:18<DIR>AdObe2007-09-0311:56<DIR>DreanweaUer MX 20042007-09-0311:58<DIR>Extension Manager2007-09-0312：00<DIRFireWOrkS MX 20042007-09-0312:03<DIR>FIaSh MX 20342007-09-0312:18<DIR>MiCrOSOft Off ice2007-09

43、-0314:50<DIR>freeie2007-09-0409:31<DIR>IBrI智能词典20002007-09-0409:33<DIR>UiaUOiCe OUtIOUd2007-09-0623:11<PIR>PrOgran FileS2009-04z2B"15:312%,435 &1丄兰卫I1个文件296.435 字节10个目隶21,712,535.552旬角字节G 5amp5ysq,bin >2、将表emp的备份文件还原到test数据库中。m<sql> USe test DatabaSe Chanffe

44、(I IIySq1> ShoW tables；C: XWamP×nysq 1 Xbin>ms»sq 1 -UrOOt test < D： enp.sq;!C ×amp×jnvsqlbin >1件QmP Eql还原到t ©Et数据库mysql show tables；2 rows in Set <.0 SeC)3、备份数据库test到D盘，文件名自拟。羽 CWINDOW5l<ylcm32lndcxcc“ ×wa3pnysql×bin>mysqldu3p-urooc tesb D&quo

45、t;tesesal9×wamp ny s q 1. ×b in >d i* D 2Slik区IUD:/200709103 20R7-09I03 20R7IS9I03 2007-9IQ3 2007I09IQ3 2007I09IQ3 200Y09I03 2007-09IQ4 200P 09 04 2007 09 0G 200704128 2009-04128 2009104128Ii 200iiiiill5 5 3994222 11DIF gF A.DIF kD3 A.D3 入 DIFV ADIFV Dim DimAdobeD*eMmwe勺CelrMX 2904 Exrtension M>n>ge>*MX 2004× 20043Hc