mysql数据库实验答案

mysql数据库实验答案

本文档内字体为阿里巴巴普惠体R,CTRL+A全选可调整字体属性及字体大

小

-CAL-FENGHALNetworkInformationTechnologyCompany.2020YEAR

实验一创建、修改数据库和表结构

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)；

(5)工资表(教师号，基本工资，职务工资，合计)，Salary(Tno,jbgz,

zwgz,hj)；

CreateDatabaseStudentdefaultcharactersetutf8default

COLLATEutf8_bin;

UseStudent;

CreateTableStudent(

SNochar(20)primarykey,

SNamechar(20)

SSexchar(4)default男',

SAgeint

)ENGINE=InnoDB;

CreateTableCourse(

CNochar(20)primarykey,

CNamechar(20)NOTNULL,

CReditfloat

)ENGINE=InnoDB;

CreateTableSC(

SNochar(20)NOTNULL,

CNochar(20)NOTNULL,

Gradefloat,

PrimaryKey(SNo,CNo),

ForeignKey(SNo)ReferencesStudent(SNo)OnDelete

Cascade,

ForeignKey(CNo)ReferencesCourse(CNo)

2

)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,

jbgzfloat,

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中，为姓名sname建立非聚簇索引。

3

CreateIndexSName_IndexOnStudent(SName);

3、在课程表的课程号Cno上建立唯一索引。

CreateUniqueIndexCNo_IndexOnCourse(CNo);

4、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相

同时成绩为

降序。

CreateIndexSCNo_IndexOnSC(SNoASC,GradeDESC);

5、用drop删除学生表student的索引。

DropIndex;

6、增加学生表student中姓名唯一约束。

AlterTableStudentAddUnique(SName);

7、增加学生表student中性别'男‘、'女'唯一约束。

AlterTableStudentAddConstraint:SSexcheck(SSex='男'orSSex=

'女')；

8、增加学生表student中年龄18~25岁约束。

AlterTableStudentAddConstraint:SAgecheck(SAge>=18AndSAge

<=25);

9、增加选课表SC中学号sno的外码约束。

AlterTableSCAddForeignKey(SNo)referencesStudent(SNo);

实验三数据的插入、更新及删除操作

1、用insert输入数据。

学生表student的数据

991201张三22男计算机系

991202李四21男信息系

4

991101王五23男数学系

991102陈六19男计算机系

991103吴七24女数学系

000101刘八22女信息系

InsertIntoStudentValues(*99120T,'张三'，'里'22,’计算机科学与

技术系')；

InsertIntoStudentValues991202,,'李四'，，甲，21,'信息科学系')

InsertIntoStudentValues('991101，'王五'，，甲，23,'数理系')；

InsertIntoStudentValues('991102',‘陈六'，，甲，19,’计算机科学与

技术系')；

InsertIntoStudentValues(>991103*,'吴七'，'女'，24,‘数理系')；

InsertIntoStudentValues('000101','刘八'，'女'，22,'信息科学系')

课程表course的数据

1数学5

2数据结构4

3程序设计2

4数据库原理3

5操作系统3

InsertIntoCourseValues(JT,'数学'，5);

InsertIntoCourseValues,2','数据结构'，4);

InsertIntoCourseValues('3','程序设计'，2)；

InsertIntoCourseValues('4','数据库原理',3)

InsertIntoCourseValues('5','操作系统'，3)；

选课表SC的数据

991201190

991201580

991201385

991201490

991102185

991102298

000101291

InsertIntoSCValuesC99120V,T',90)

InsertIntoSCValues99120T,'5',80)

InsertIntoSCValuesC99120V,'3',85)

InsertIntoSCValuesC99120V,'4',90)

InsertIntoSCValuesC991102*,*T,85)

5

InsertIntoSCValuesC991102\'2',98);

InsertIntoSCValuesC000101*,'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','信息科学系'，

‘教授'，'长沙’)；

InsertIntoTValuesC100r,'王五'，'男'，T973-07-20','计算机科学与

技术系'，‘讲师'，'湘潭')；

InsertIntoTValuesC1008',‘陈六'，'男'，*1970-08-20,,'计算机科学与

技术系'，‘副教授'，'北京'）；

基本表Salary的数据

000110003001300

000215005002000

10018002001000

InsertIntoSalaryValues(*0001*,1000,300,1300);

InsertIntoSalaryValuesC0002,,1500,500,2000);

InsertIntoSalaryValues(J100T,800,200,1000);*/

2、用delete删除数据记录

(1)删除教师表T中教师号为0001的元组。

(2)删除教师表T中的全部数据。

updatetsetbirthday=，1961-10-04*whereTno='0001'

DeleteFromT;

3、用update更新数据记录

(1)把0001号教师的基本工资加100。

(2)把所有教师的基本工资都加100。

UpdateSalarySetjbgz=jbgz+100WhereTNo='0001'

UpdateSalarySetjbgz=jbgz+100

6

实验四数据的查询

1、简单查询，用select检索

(1)查询所有学生的基本情况。

select*fromstudent;

(2)查询教师每月应交纳的个人所得税。

selecthj*asmonthshuifromSalary;

(3)查询张三与李四两位同学的基本情况。

select*fromstudentwheresname='张三'orsname='李四';

(4)查询9911班学生的基本信息(规定学生学号的前四位是班级号)。

select*fromstudentwheresnolike'9911%’;

(5)查询所有年龄在20岁以下的学生姓名及其年龄。

selectsname,sagefromstudentwheresage<20;

(6)查询选修了2门以上课程的学生学号。

selectsnofromSCgroupbysno

havingcount(*)>2;

2、多表查询，用select检索

(1)查询教师的收入情况，包括教师号、姓名及月总收入。

select,Tname,hjaddress=Ty.Addressand<>

(5)查询选修2号课程且成绩在90分以上的所有学生。

select*fromstudent,SC

where=and='2'and>90;

(6)查询与王五在同一个系学习的学生。

select*

fromstudent

wheresdept=(selectsdept

fromstudentwheresname=，王五');

实验五视图

1、建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。

createviewboystudent_view

7

asselect,sname,eno,grade

fromstudent,SC

where='男'and=SC.sno;

2、在男学生视图中查询平均成绩大于80分的学生学号与姓名。

selectsno,sname

fromboystudent_view

groupbysno,sname

havingAVG(grade)>80

3、建立信息系选修了1号课程的学生的视图。

createviewxinxi_viewl

asselect,sname,ssex,sage

fromstudent,SC

where='信息'and=and='1'

4、建立信息系选修了1号课程且成绩在90分以上的学生的视图。

createviewxinxi_view2

asselect,sname,sage,ssex

fromstudent,SC

where=，信息、'and=and=，1'and>90

5、建立计算机系选修了2号课程的学生的视图。

createviewjisuanji_view

asselect,sname,sage,ssex

fromstudent,SC

where='计算机'and=and=,2'

6、建立一个学生出生年份的视图。

createviewyear_view

asselect,sname,2007-sageasbirthday

fromstudent

7、建立一个计算机系学生的视图，并要求在进行修改、插入操作时，仍然要确保视图

只有计算机系的学生。

createviewjisuanji2_view

asselect,sname,sage,ssex

fromstudent

wheresdept=，计算机'

withcheckoption

8

8、向学生视图中插入一条新记录(951101,钱进，男，20)

createviewstudent_viewl

asselectsno,sname,ssex,sage

fromstudent;

insertintostudent_viewlvalues('951101'钱进‘，'男'，20)

9、将学生视图中学号为991101的学生姓名改为“刘平平”。

updatestudent_viewl

setsname='刘平平'

wheresno='991101'

10、删除计算机系视图中学号为991201的记录。

delete

fromjisuanji2_view

wheresno='99120T

11、删除学生出生年份的视图。

dropviewyear_view;

实验六集合函数的应用

1、使用select语句查询

(1)9911班学生的人数和平均成绩(规定学生学号的前四位是班级号)

SELECTCOUNT(sno)as总人数，AVGAS平均分FROMsc

WHEREsnolike'9912%'

(2)每个学生的学号和相应的平均成绩

SELECTsno,AVG(grade)FROM,sc

GROUPBY

(3)成绩大于90分的学生学号

SELECTsno,MAXasmaxgradeFROMsc

GROUPBYsno

HAVINGMAX(grade)>90

(4)月工资大于平均工资的教师号

SELECTTNoFROMSalary

WHEREhj>=(SELECTAVG(hj)

fromSalary)

9

2、实验完成后，撤消建立的基本表和视图。

实验七创建、执行、修改和删除存储过程

1、建立查询计算机系女学生的存储过程。

Usestudent;

CREATEPROCEDURESELECT_WOMAN()

BEGIN

SELECT*FROMstudentwheressex='女';

end;

callselect_wanman();

2、定义一个存储过程不带参数，作用是查询选修了课程学生的学号，并执行这

个存储过程。

usestudent;

Createprocedurenumofsc()

BEGIN

selectsnofromSCgroupbysno

havingcount(*)>0;

End;

callnumofsc();

3、定义一个带输出参数的存储过程，作用是查询Student表中的学生个数，并赋值给输出参

数，

执行该存储过程，同时通过输出参数来输出学生个数。

•CREATEPROCEDUREprod(OUTsint)

•BEGIN

SELECTCOUNT(*)INTOsFROMstudent;

END;

执行CALLprocl(@p0);

SELECT@p0AS'学生人数';

4、定义一个带输出参数和输出参数的存储过程，执行该存储过程，通过输入参

数学号,查到到姓名，同时输入姓名，。

usestudent;

Createproceduregetnamebysno(inxhchar(10),outnamechar(20))

10

Begin

Selectsnameintonamefromstudentwheresno=xh;

End;

执行

set@name=nul1;

callgetnamebysno('000105",©name);

select@name;

5、修改刚建立的存储过程。

ALTERPROCEDURESELECT.'

6、删除刚建立的存储过程。

DROPPROCEDURESELECT_WOMAN

实验八触发器的插入、删除、更新和创建

1、对student表创建delete触发器，当删除某个学生记录时，同时在选

课表SC中删除该学生的选课记录。

mysql通过

usestudent;

InsertIntoStudentValues('000101'刘八'，'女'，22,'信息科学

系')；

InsertIntoSCValuesC000101,,'2',91);

select*fromstudent;

select*fromsc;

createtriggertlAFTERdeleteonstudent

FOREACHROWBEGIN

DeletefromSCWHEREsno=;

END;

2、在student表上定义了一个update触发程序，用于检查更新sage时将

使用的新值，小于16时取得16,大于40时取40,并更改值。

11

usestudent;

createtriggerupd_checkbeforeupdateonstudent

FOREACHROW

begin

if<16then

set=16;

elseif>60then

set=40;

endif;

end;

select*fromstudent;

updatestudentsetsage=2wheresno=，000105';

select*fromstudent;

3、在student表上定义了一个insert触发程序，用于检查插入ssex时只

能取男和女，输入其它时取NULL。

usestudent;

createtriggerins_checkbeforeinsertonstudent

FOREACHROW

begin

if◊'男'and◊'女'then

set=null;

endif;

end;

select*fromstudent;

InsertIntoStudentValues('000106'陈规则五',22,'信息科学

系')；

select*fromstudent;

4、altertrigger

5、droptrigger〈触发名)on表名；

6、createdatabasetrigg;

CREATETABLEt_a(

idsmallint(l)unsignedNOTNULLAUTO_INCREMENT,

usernamevarchar(20)DEFAULTNULL,

groupidmediumint(8)unsignedNOTNULLDEFAULT'O',

PRIMARYKEY('id')

)ENGINE二MylSAMAUT0_INCREMENT=16DEFAULTCHARSET=latinl;

12

CREATETABLEt_b(

idsmallint(l)unsignedNOTNULLAUTO_INCREMENT,

usernamevarchar(20)DEFAULTNULL,

groupidmediumint(8)unsignedNOTNULLDEFAULT'O',

PRIMARYKEY('id')

)ENGINE=MyISAMAUT0_INCREMENT=57DEFAULTCHARSET=latinl;

CREATE

TRIGGERtr_a_insertAFTERINSERTONt_a

FOREACHROWBEGIN

INSERTINTOt_bSETusername=,groupid=;

END;

INSERTINTOt_a(username,groupid)VALUES('',123);

SELECTid,username,groupidFROMt_a;

SELECTid,username,groupidFROMt_b;

CREATETRIGGER'tr_a_delete'AFTERDELETEONt_a

FOREACHROWBEGIN

DELETEFROMt_b'WHEREusername=ANDgroupid=;

END;

deletefromt_awheregroupid=123;

SELECTid,username,groupidFROMt_a;

SELECTid,username,groupidFROMt_b;

CREATETRIGGERtr_a_updateAFTERUPDATEONt_a

FOREACHROWBEGIN

IF!=OR!=THEN

UPDATEt_bSETgroupid=,username=

WHEREusername=ANDgroupid=;

ENDIF;

end;

实验九MYSQL权限与安全

13

1、用户的创建

①利用CREATEUSER语句创建用户userl、user2>user3,密码均

为'123456'o

mysql>createuser'userl'@'localhost'identifiedby'123456’,

'user2,@9localhost1identifiedby'123456','user3,@'localhost，

identifiedby'123456';

QueryOK,0rowsaffectedsec)

②利用INSERTINTO语句向USER表创建用户user4,密码均

为'123456'o

mysql>insertinto

(host,user,password,ssl_cipher,x509_issuer,x509_subject)

values('localhost','user5',password('123456';

QueryOK,1rowaffectedsec)

③利用GRANT语句创建用户user5,密码均为'123456',且为全局级用

户。

mysql>grantselect,insert,updateon*・*to'user5,localhost)

identifiedby'123456';

QueryOK,0rowsaffectedsec)

2、用户授权(利用GRANT语句)

①授予userl用户为数据库级用户，对数据库StudDB拥有所有权。

mysql>grantallon*・*to'userl'localhost'identified

by123456,;

QueryOK,0rowsaffectedsec)

②授予user2用户为表级用户，对StudDB中的student表select,

create,drop权限。

mysql>grantselect,create,droponto'user2,@'localhost,

identifiedby

'123456';

QueryOK,0rowsaffectedsec)

14

③授予user3用户为列级用户，对StudDB中的student表的sname列用户

select和update权限。

mysql>grantselect,update(sname)onto'user2'@'localhost'

identifiedby'123456';

QueryOK,0rowsaffectedsec)

④授予user4用户为过程级用户，对中的numofstudent存储过程拥有

EXECUTE执行的权限。

mysql>grantexecuteonprocedurenumofstudentto

'user4Jlocalhost'identifiedby'123456';

QueryOK,0rowsaffectedsec)

利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进行验证。

查看权限

mysql>select*fromwhereuser=,testT;

Emptysetsec)

mysql>showgrantsfor'root'@'localhost，;

select*fromwhereuser=,root’;

mysql>deletefromwhereuser="root"andhost="localhost”;

QueryOK,1rowaffectedsec)

mysql>flushprivileges;

QueryOK,0rowsaffectedsec)

删除权限

mysql>dropuser'user4Jlocalhost，;

QueryOK,0rowsaffectedsec)

3、权限回收

①收回user3用户对StudDB中的student表的sname列的update权限。

mysql>revokeupdateon*・*from'user2,@'localhost,;

QueryOK,0rowsaffectedsec)

15

②收回user4用户对中的numofstudent存储过程拥有EXECUTE执行的权

限。

mysql>revokeexecuteonprocedurenumofstudentfrom

'user4'&localhost，;

QueryOK,0rowsaffectedsec)

利用上述建立的用户连接并登陆MYSQL数据库，对相关权限进行验证。

4、用户密码及用户名修改

①利用使用SETpassword修改和UPDATE方式修改root用户的密码。

mysql>setpassword=password(“123456");

QueryOK,0rowsaffectedsec)

mysql>updatesetpassword=password('123')whereuser="root"and

host

="locahst";

QueryOK,0rowsaffectedsec)

Rowsmatched:0Changed:0Warnings:0

mysql>flushprivileges;

QueryOK,0rowsaffectedsec)②利用SETpasswordfor'用户，@'

主机'二password("新密码”)方法修改userl用户的密码。

5、超级用户ROOT密码丢失的找回

①利用skip-grant-tables配置参数修改配置文件，实现无密码登陆MYSQL

服务器，并利用UPDATE语句修改密码。

实验十：数据的复制与恢复

1、备份所有数据库到D盘，文件名自拟。

16

武C\WINDOWS\”stem32Kmd.exe

C：\DocunentsandSettingsXAdministratoi*>cdC:\waep7nysql\bin

C:\wamp5ysql\birOmysqldump-uroot——all-database>D：\all.sql

C：\wamp5ysql\bin>dii*D：\

岖动器D中的卷没有标签。

春的序列号是8B74-2906

D：\的目录

2007-09-0311:18<DIR>Adobe

2007-09-0311:56<DIR>DreamvieauerMX2004

2007-09-0311:58<DIR>ExtensionManager

2007-09-0312:00<DIR>FireworksMX2004

2007-09-0312:03<DIR>FlashMX2004

2007-09-0312：18<DIR>MicrosoftOffice

2007-09-0314:50<DIR>freeiine

2007-09-0409:31<DIR>IBM智能词典2000

2007-09-0409:33<DIR>UiaUoiceOutloud

23J11<DIR>ProgranFiles

2009-04-2815:31296.435更|

1个文件296,435字节

10个目录21,712,535,552句用字节

|C：7，amp5ysql\bin>]

2、将表emp的备份文件还原到test数据库中。

gysql>usetest

databasechanged

wn9sql>showtables；

1rowinset<0.00sec>

C：XwanpSnysqlXbin>nysql-uroottest<D：\enp.sql