MySQL數(shù)據(jù)庫原理及應(yīng)用（第3版）強化訓(xùn)練答案

【強化訓(xùn)練1-1】

設(shè)學(xué)生一課程數(shù)據(jù)庫中有3個關(guān)系。

學(xué)生關(guān)系：S(sno,sname,ssex,sage)

課程關(guān)系：C(cno,cname,teacher)

學(xué)習(xí)關(guān)系：SC(sno,eno,degree)

請寫出完成如下查詢所需的關(guān)系代數(shù)表達式。

(1)查詢學(xué)習(xí)課程號為C3號課程的學(xué)生學(xué)號和成績。

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

(2)查詢學(xué)習(xí)課程號為C4課程的學(xué)生學(xué)號和姓名。

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

(3)查詢學(xué)習(xí)課程名為malhs的學(xué)生學(xué)號和姓名。

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

(4)查詢學(xué)習(xí)課程號為C1或C3課程的學(xué)生學(xué)號。

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

(5)查詢不學(xué)習(xí)課程號為C2的學(xué)生的姓名和年齡。

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

(6)查詢學(xué)習(xí)全部課程的學(xué)生姓名。

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

(7)杳詢所學(xué)課程包括000101所學(xué)課程的學(xué)生學(xué)號。

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

【強化訓(xùn)練2-1】

將圖2.8所示的E-R圖，根據(jù)轉(zhuǎn)換規(guī)則轉(zhuǎn)換為關(guān)系模式。

系(王芻，電話號碼)

教師(教師號,姓名，性別，職稱，系名)

學(xué)生(變號，姓名，性別，年齡，系名)

課程(課程號,課程名)

選修(塑，課程號,成績)

講授(教師號，課程號)

【強化訓(xùn)練4-1】

利用SQL語句定義class表、department表、floor表、teacher表、teaching表的結(jié)構(gòu)°

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)

)；

【強化訓(xùn)練5-1]

(1)查詢有考試成績的課程號。

SELECTDISTINCTeno

FROMsc

WHEREdegreeISNOTNULL；

(2)查詢數(shù)學(xué)系的男生信息。

SELECT*

FROMstudent

WHEREsdept='數(shù)學(xué)系，andssex二勇；

(3)查詢計算機工程系和數(shù)學(xué)系學(xué)生的姓名、性別和出生日期，顯示列名分別為“姓名”

“性別”和“出生日期

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

FROMstudent

WHEREsdept='計算機工程系ORsdept=，數(shù)學(xué)系，；

(4)查詢所有姓李的學(xué)生的個人信息。

SELECT*

FROMstudent

WHEREsnameLIKE，李％)

(5)查詢考試成績在90分以上，或成績不及格(低于60分)的學(xué)生學(xué)號和成績。

SELECTsno,degree

FROMsc

WHEREdegree>90ORdegree<60；

【強化訓(xùn)練5-2]

(I)統(tǒng)計每個學(xué)生的平均成績。

SELECTsnozavg(degree)

FROMsc

GROUPBYsno；

(2)統(tǒng)計每門課的平均成績。

SELECTcnozavg(degree)

FROMsc

GROUPBYeno；

(3)統(tǒng)計各系每門課的總成績和平均成績。

SELECTsdeptzcno/sum(degrec)/avg(degree)

FROMstudenta,scb

WHEREa.sno=b.sno

GROUPBYsdept,cno；

(4)查詢每門課程的最高成績和最低成績。

SELECTcnozmax(degree)zmin(degree)

FROMsc

GROUPBYeno；

(5)統(tǒng)計不及格人數(shù)超過20人的課程號,并按不及格人數(shù)降序排序。

SELECTeno

FROMsc

WHEREdegree<60

GROUPBYeno

HAVINGcount(*)>20

ORDERBYcount(*)DESC；

【強化訓(xùn)練7-1]

請思考，如何在命令行工具中實現(xiàn)例7.17?

mysql>USEgradem；

mysql>DELIMITER//

mysqI>CREATEPROCEDUREproc_stud()

->READSSQLDATA

->BEGIN

->SELECTsno,sname,classno,saddressFROMstudent

->WHEREsaddressLIKE%青島％'ORDERBYsno；

->END//

QueryOK,0rowsaffected

mysql>DELIMITER；

【強化訓(xùn)練7-2】

在gradem數(shù)據(jù)庫中定義一個觸發(fā)器trig_snoupdale,當(dāng)sludent表中的學(xué)生學(xué)號變更時，

同時更新sc表中相應(yīng)的學(xué)生學(xué)號信息。

mysql>USEgradem；

mysql>DELIMITER&&

inysql>CREATETRIGGERtrig_snoupdate

->AFTERUPDATEONstudentFOREACHROW

->BEGIN

->IFnew.sno!=old.snoTHEN

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

->ENDIF；

->END&&

QueryOK,0rowsaffected

mysql>DELIMITER；

【強化訓(xùn)練8?1】

(1)使用GRANT語句將gradem數(shù)據(jù)庫中student表的DELETE權(quán)限授予用戶

kenlo

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

QueryOK,0rowsaffected

(2)使用GRANT語句將gradem數(shù)據(jù)庫中sc表的degree列的UPDATE權(quán)限授予

用戶palolo

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

QueryOK,0rowsaffected

【強化訓(xùn)練8-2]

使用SELECT...INTOOUTFILE命令將gradem數(shù)據(jù)庫中的course表中的記錄導(dǎo)出到文

本文件，使用FIELDS選項和LINES選項，要求字段之間使用逗號。，”間隔，所有字段值

用雙引號引起來，定義轉(zhuǎn)義字符為單引號“V”。

USEgradem；

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

FIELDS

TERMINATEDBY1；

ENCLOSEDBYV

ESCAPEDBY'\"

LINES

TERMINATEDBY'\r\n'；

【強化訓(xùn)練8-3]

使用LOADDATAINFILE命令將d:\bak\sc.txt文件中的數(shù)據(jù)導(dǎo)入gradem數(shù)據(jù)庫的sc

表中，使用FIELDS選項和LINES選項，要求字段之間使用逗號“，”間隔，所有字段值用

雙引號引起來，定義轉(zhuǎn)義字符為單引號，

mysql>USEgradem；

databasechanged；

mysql>deletefromsc；

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

FIELDS

TERMINATEDBY'；

ENCLOSEDBYT

ESCAPEDBYV

LINF.S

TERMINATEDBYV\n,；

【強化訓(xùn)練8-4]

使用