MySQL数据库原理及应用微课版武洪萍第3版强化训练答案

【强化训练1・1】

设学牛一课程数据库中有3个关系.

学生关系：S(sno,sname,ssex,sage)

课程关系：C(cno,cname,teacher)

学习关系：SC(sno,eno,degree)

请写出完成如下查询所需的关系代数表达式。

(1)查询学习课程号为C3号课程的学生学号和成绩。

7tsno.degree(0Cno='C3'(SC))

(2)查询学习课程号为C4课程的学生学号和姓名。

^sno,snamc(^cno='C4"(SooSC))

(3)查询学习课程名为maths的学生学号和姓名。

兀.sname(Gcname='malhs'(SooSCooC))

(4)查询学习课程号为Cl或C3课程的学生学号。

-no(Ocno=5Vcno='C3'(SC))

(5)查询不学习课程号为C2的学生的姓名和年龄。

TCsnamc.saRc(S)-7Csnamc.sagc((ycno='C2,(S℃SC))

(6)查询学习全部课程的学生姓名。

^tsname(S0O(Jo(SC)^7Ccno(C)))

(7)查询所学课程包括000101所学课程的学生学号。

^sno,cno(SC)-r7tcno(^sno='0(»10r(SC))

【强化训练2-1】

将图2.8所示的E-R图，根捱转换规则转换为关系模式。

系(系名，电话号码)

教师(教师号,姓名，性别，职称，系名)

学生(茎号，姓名，性别，年龄，系名)

课程(课程号,课程名)

选修(笺殳，课程号,成绩)

讲授(教师号,课程号)

【强化训练4-1】

利用SQL语句定义class表、department表、floor表、teacher表、teaching表的结构。

CREATETABLEclass

(classnochar(8)PRIMARYKEY,

classnamevarchar(20)z

specialityvarchar(60)z

inyearyear,

classnumbertinyint,

headerchar(10)z

deptnochar(4)z

classroomvarcharf16),

monitorchar(8)z

CONSTRAINTBlFOREIGNKEY(deptno)REFERENCESdcpartment(deptno)

)；

CREATETABLEdepartment

(deptnochar(4)PRIMARYKEY,

deptnamechar(14),

deptheaderchar(8)z

officechar(20)z

deptphonechar(20)DEFAULT'不详'

)；

CREATETABLEfloor

(stloorchar(10)NOTNULL,

sroomnochar(5)NOTNULL,

ssexchar(2),

maxntinyint,

CONSTRAINTB2PRIMARYKEY(sfloor,sroomno)

)；

CREATETABLEteacher

(tnochar(4)PRIMARYKEY,

(namechar(10),

tsexchar(2)DEFAULT'男

(titlechar(lO),

(birthdaydate,

deptnochar(4),

CONSTRAINTB3FOREIGNKEY(deptno)REFERENCESdepartment(deptno)

)；

CREATETABLEteaching

(tnochar(4)NOTNULL,

enochar(3)NOTNULL,

ctermtinyinlNOTNULL,

CONSTRAINTB4PRIMARYKEY(tno,cno,ctcrm),

CONSTRAINTB5FOREIGNKEY(tno)REFERENCESteachcr(tno),

CONSTRAINTB6FOREIGNKEY(cno)REFERENCEScourse(cno),

CONSTRAINTB7FOREIGNKEY(cterm)REFERENCEScouse(cterm)

)；

【强化训练5-1]

(1)查询有考试成绩的课程号。

SELECTDISTINCTeno

FROMsc

WHEREdegreeISNOTNULL；

(2)查询数学系的男生信息。

SELECT*

FROMstudent

WHEREsdepl='数学系，andssex='男'；

(3)查询计算机工程系和数学系学生的姓名、性别和出生日期，显示列名分别为“姓

名”“性别”和“出生口期”。

SELECTsnameAS'姓名',ssexAS'性别',sbirthdayAS，出生日期，

FROMstudent

WHEREsdept=，计算机工程系，ORsdept='数学系，；

(4)查询所有姓李的学生的个人信息。

SELECT*

FROMstudent

WHEREsnameLIKE'李％，；

(5)查询考试成绩在90分以上，或成绩不及格(低于60分)的学生学号和成绩。

SELECTsno,degree

FROMsc

WHEREdegree>90ORdegree<60；

【强化训练5-2]

(1)统计每个学生的平均成绩。

SELECTsnozavg(degree)

FROMsc

GROUPBYsno；

(2)统计每门课的平均成绩。

SELECTcnozavg(degree)

FROMsc

GROUPBYeno；

(3)统计各系每门课的总成绩和平均成绩。

SELECTsdept,cno,sum(degree),avg(degree)

FROMstudenta,scb

WHEREa.sno=b.sno

GROUPBYsdept,cno；

(4)查询每门课程的最高成绩和最低成绩。

SELECTcno,max(degree),min(degree)

FROMsc

GROUPBYeno；

(5)统计不及格人数超过20人的课程号，并按不及格人数降序排序。

SELECTeno

FROMsc

WHEREdegree<60

GROUPBYeno

HAVINGcount(*)>20

ORDERBYcount(*)DESC；

【强化训练7-1】

请思考，如何在命令行工具中实现例7.17?

mysql>USEgradem；

mysql>DELIMITER//

mysql>CREATEPROCEDUREproc_stud()

->READSSQLDATA

->BEGIN

->SELECTsno,sname,classno,saddressFROMstudent

->WHEREsaddressLIKE%青岛％，ORDERBYsno；

->END//

QueryOK,0rowsaffected

mysql>DELIMITER；

【强化训练7-2]

在gradem数据库中定义一个触发器trig_$noupdale,当sludent表中的学生学号变更时,

同时更新SC表中相应的学生学号信息。

mysql>USEgradem；

mysql>DELIMITER&&

mysql>CREATETRIGGERtng_snoupdate

->AFTERUPDATEONstudentFOREACHROW

->BEGIN

->IFnew.sno!=old.snoTHEN

->UPDATEscSETsno=new.snoWHEREsno=old.sno；

->ENDIF；

->END&&

QueryOK,0rowsaffected

mysql>DELIMITER；

【强化训练84】

(1)使用GRANT语句洛gradem数据库中student表的DELETE权限授予用户

kenl。

mysql>GRANTDELETEongradem.studentTO'kenr@'localhost'；

QueryOK,0rowsaffected

(2)使用GRANT语句将gradem数据库中sc表的degree列的UPDATE权限授予

用户paloK

mysql>GRANTupdate(degree)ongradem.scto'palo1'©'localhost'；

QueryOK,0rowsaffected

【强化训练8-2】

使用SELECT--INTOOUTFILE命令将gradem数据库中的course表中的记录导出到文

本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号“，”间隔，所有字段

值用双引弓引起来，定义转义字符为单引号“V”。

USEgradem；

SELECT*FROMcourseINTOOUTFILE"D:/BAK/course.txt"

FIELDS

TERMINATEDBY1；

ENCLOSEDBYV

ESCAPEDBY'\"

LINES

TERMINATEDBYW；

【强化训练S・3】

使用LOADDATAINFILE命令将d:\bak\sc.txt文件中的数据导入gradem数据库的sc

表中，使用FIELDS选项和LINES选项，要求字段之间使用逗号“，”间隔，所有字段值

用双引号引起来，定义转义字符为单引号。

mysql>USEgradem；

databasechanged；

mysql>deletefromsc；

mysql>LOADDATAINFILE"d:/bak/sc.txt"INTOTABLEgradem.sc

FIELDS

TERMINATEDBY'；

ENCLOSEDBY

ESCAPEDBY'V

LINES

TERMINATEDBYW；

【强化训练8・4】

使用mysqldump命令将gra