MySQL数据库原理及应用（第3版）强化训练答案

【强化训练1-1】

设学生一课程数据库中有3个关系。

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

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

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

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

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

加sno,dcgrcc(OCno='C3，(SC))

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

ano.snamc(Ocno='C4，(S8SC))

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

%no,sname(Gcname='niaih$,(S8SC8C))

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

7Csno(Ocno='CI'Vcno='C3'(SC))

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

Jtsnamc,sagc(S)-7Csnamc,sage(Ocno='C2'(ScoSC))

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

加sname(SoO(Jt$o(SC)^7tcno(C)))

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

7Csno/cno(SC)T7Tcno(Osno='0(»101'(SC))

【强化训练2-1】

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

系(王刍，电话号码)

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

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

课程(课程号,课程名)

选修(塑，课程号,成绩)

讲授(教师号，课程号)

【强化训练4-1】

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

CREATETABLEclass

(classnochar(8)PRIMARYKEY,

classnamevarchar(20)z

specialityvarchar(60),

inyearyear,

classnumbcrtinyint,

headerchar(10)z

deptnochar(4),

classroomvarchar(16),

monitorchar(8)z

CONSTRAINTBlFOREIGNKEY(dcptno)REFERENCESdcpartmcnt(dcptno)

)；

CREATETABLEdepartment

(deptnochar(4)PRIMARYKEY,

deptnamechar(14),

deptheaderchar(8)z

officechar(20)z

deptphonechar(20)DEFAULT'不详'

）；

CREATETABLEfloor

(sfloorchar(10)NOTNULL,

sroomnochar(5)NOTNULL,

ssexchar(2),

maxntinyint,

CONSTRAINTB2PRIMARYKEY(sfloorzsroomno)

)；

CREATETABLEteacher

(tnochar(4)PRIMARYKEY,

tnamcchar(lO),

tsexchar(2)DEFAULT

ttitlechar(10)z

tbirthdaydate,

deptnochar(4),

CONSTRAINTB3FOREIGNKEY(deptno)REFERENCESdepartment(deptno)

)；

CREATETABLEteaching

(tnochar(4)NOTNULL,

enochar(3)NOTNULL,

ctermtinyintNOTNULL,

CONSTRAINTB4PRIMARYKEY(tno,cno,clerm),

CONSTRAINTB5FOREIGNKEY(tno)REFERENCESteacher(tno),

CONSTRAINTB6FOREIGNKEY(cno)REFERENCEScourse(cno>,

CONSTRAINTB7FOREIGNKEY(cterm)REFERENCEScouse(ctcrm)

)；

【强化训练5-1]

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

SELECTDISTINCTeno

FROMsc

WHEREdegreeISNOTNULL；

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

SELECT*

FROMstudent

WHEREsdept='数学系，andssex二勇；

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

“性别”和“出生日期

SELECTsnameAS'姓名',ssexAS性别',sbirthdayASH生日期‘

FROMstudent

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

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

SELECT*

FROMstudent

WHEREsnameLIKE，李％)

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

SELECTsno,degree

FROMsc

WHEREdegree>90ORdegree<60；

【强化训练5-2]

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

SELECTsnozavg(degree)

FROMsc

GROUPBYsno；

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

SELECTcnozavg(degree)

FROMsc

GROUPBYeno；

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

SELECTsdeptzcno/sum(degrec)/avg(degree)

FROMstudenta,scb

WHEREa.sno=b.sno

GROUPBYsdept,cno；

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

SELECTcnozmax(degree)zmin(degree)

FROMsc

GROUPBYeno；

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

SELECTeno

FROMsc

WHEREdegree<60

GROUPBYeno

HAVINGcount(*)>20

ORDERBYcount(*)DESC；

【强化训练7-1]

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

mysql>USEgradem；

mysql>DELIMITER//

mysqI>CREATEPROCEDUREproc_stud()

->READSSQLDATA

->BEGIN

->SELECTsno,sname,classno,saddressFROMstudent

->WHEREsaddressLIKE%青岛％'ORDERBYsno；

->END//

QueryOK,0rowsaffected

mysql>DELIMITER；

【强化训练7-2】

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

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

mysql>USEgradem；

mysql>DELIMITER&&

inysql>CREATETRIGGERtrig_snoupdate

->AFTERUPDATEONstudentFOREACHROW

->BEGIN

->IFnew.sno!=old.snoTHEN

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

->ENDIF；

->END&&

QueryOK,0rowsaffected

mysql>DELIMITER；

【强化训练8・1】

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

kenlo

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

QueryOK,0rowsaffected

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

用户palolo

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

QueryOK,0rowsaffected

【强化训练8-2]

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

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

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

USEgradem；

SELECT*FROMcourseINTOOUTFILE"D:/BAK/cou^se.txt,,

FIELDS

TERMINATEDBY1；

ENCLOSEDBYV

ESCAPEDBY'\"

LINES

TERMINATEDBY'\r\n'；

【强化训练8-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'；

ENCLOSEDBYT

ESCAPEDBYV

LINF.S

TERMINATEDBYV\n,；

【强化训练8-4]

使用