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é)號和成績。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

將圖2.8所示的E-R圖，根捱轉(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)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)

)；

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

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

SELECTDISTINCTeno

FROMsc

WHEREdegreeISNOTNULL；

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

SELECT*

FROMstudent

WHEREsdepl='數(shù)學(xué)系，andssex='男'；

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

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

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

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]

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

SELECTsnozavg(degree)

FROMsc

GROUPBYsno；

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

SELECTcnozavg(degree)

FROMsc

GROUPBYeno；

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

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

FROMstudenta,scb

WHEREa.sno=b.sno

GROUPBYsdept,cno；

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

SELECTcno,max(degree),min(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//

mysql>CREATEPROCEDUREproc_stud()

->READSSQLDATA

->BEGIN

->SELECTsno,sname,classno,saddressFROMstudent

->WHEREsaddressLIKE%青島％，ORDERBYsno；

->END//

QueryOK,0rowsaffected

mysql>DELIMITER；

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

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

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

mysql>USEgradem；

mysql>DELIMITER&&

mysql>CREATETRIGGERtng_snoupdate

->AFTERUPDATEONstudentFOREACHROW

->BEGIN

->IFnew.sno!=old.snoTHEN

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

->ENDIF；

->END&&

QueryOK,0rowsaffected

mysql>DELIMITER；

【強化訓(xùn)練84】

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

kenl。

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

QueryOK,0rowsaffected

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

用戶paloK

mysql>GRANTupdate(degree)ongradem.scto'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/course.txt"

FIELDS

TERMINATEDBY1；

ENCLOSEDBYV

ESCAPEDBY'\"

LINES

TERMINATEDBYW；

【強化訓(xùn)練S?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'；

ENCLOSEDBY

ESCAPEDBY'V

LINES

TERMINATEDBYW；

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

使用mysqldump命令將gra