MySQL數(shù)據(jù)庫原理及應(yīng)用（武洪萍 第2版）（微課版）-課外拓展答案

MySQL數(shù)據(jù)庫原理及應(yīng)用（武洪萍第2版）（微課版）-課外拓展答案

項目2實訓(xùn)：設(shè)計數(shù)據(jù)庫

1.實踐內(nèi)容

（1）“醫(yī)院病房管理系統(tǒng)”數(shù)據(jù)庫的設(shè)計。

某醫(yī)院病房計算機管理中需要如下信息。

科室：科室名，科室地址，科電話，醫(yī)生姓名，科室主任。

病房：病房號，床位號，所屬科室名。

醫(yī)生：姓名，職稱，所屬科室名，年齡，工作證號。

病人：病歷號，姓名，性別，診斷，主管醫(yī)生，病房號。

其中，一個科室有若干個病房、多個醫(yī)生，一個病房只能屬于一個科室，一個醫(yī)生只屬于一個科室,

但可負責(zé)多個病人的診治，一個病人的主管醫(yī)生只有一個。

參考答案：E-R圖及轉(zhuǎn)換成的關(guān)系模式如下所示：

病房（病房號，床位號，科室名）；

科室（科室名，科室地址，科電話，科室主任，醫(yī)生姓名）；

醫(yī)生（姓名，職稱，年齡，工作證號，科室名）；

病人（病歷號，姓名，性別，診斷，主管醫(yī)生姓名，病房號）。

<2）“訂單管理系統(tǒng)”數(shù)據(jù)庫的設(shè)計。

設(shè)某單位銷售產(chǎn)品所需管理的信息有：訂單號，客戶號，客戶名，客戶地址，產(chǎn)品號，產(chǎn)品名，產(chǎn)

品價咯，訂購數(shù)量，訂購日期。一個客戶可以有多個訂單，一個訂單可以訂多種產(chǎn)品。

參考答案：E-R圖及轉(zhuǎn)換成的關(guān)系模式如下所示：

客戶（客戶號，客戶名，客戶地址）；

訂單（訂單號，客戶號）*

產(chǎn)品（產(chǎn)品號，產(chǎn)品名，產(chǎn)品價格）；

訂購（訂單號，產(chǎn)品號,訂購數(shù)量，訂購日期）。

（3）“課程安排管理系統(tǒng)”數(shù)據(jù)庫的設(shè)計。

課程安排管理需要對課程、學(xué)生、教師和教室進行協(xié)調(diào)。每個學(xué)生最多可以同時選修5門課程，每

門課程必須安排一間教室以便學(xué)生可以去上課,一個教室在不同的時間可以被不同的班級使用：一個教

師可以教授多個班級的課程，也可以教授同一班級的多門不同的課程，但教師不能在同一時間教授多個

班級或多門課程；課程、學(xué)生、教師和教室必須匹配。

參考答案：E-R圖及轉(zhuǎn)換成的關(guān)系模式如下所示：

項目2課外拓展：設(shè)計網(wǎng)絡(luò)玩具銷售系統(tǒng)

，裝具編號：＞（名稱'1

一」類型）

|玩具

'價格

/..-V——.一■—―■——一

廣電話）（通信地址）；所買個數(shù);所剩數(shù)量

客戶（客戶編號，姓名，性別，年齡，電話，通信地址）；

玩具（玩具編號，名稱，類型，價格，所剩數(shù)量）；

訂購（客戶編號，玩具編號，訂單號，所買個數(shù)）。

項目3實訓(xùn)：安裝MySQL5.7及NavicatforMySQL

1.實踐內(nèi)容及要求

（1）在Windows平臺下安裝與配置MySQL5.7.21版。

參考課本P73-78

（2）在服務(wù)對話框中，手動啟動或者關(guān)閉MySQL服務(wù)。

參考課本P78.79

（3）使用Net命令啟動或關(guān)閉MySQL服務(wù)。

啟動：netstartmysq157

關(guān)閉：netstopmysql57

（4）分別用Navicat工具和命令行方式登錄MySQL0

參考課本P79-80

（5）在my.ini文件中將數(shù)據(jù)庫的存儲位置改為D:\MYSQL\DATAo

參考課本?84-85

（6）使用配置向?qū)薷漠?dāng)前密碼，并使用新密碼重新登錄。

（7）配置Path變量，確保MySQL的相關(guān)路徑包含在Path變量中。

參考課本Psi-85

項目4實訓(xùn)：創(chuàng)建和刪除數(shù)據(jù)庫

1.實訓(xùn)內(nèi)容及要求

（1）創(chuàng)建數(shù)據(jù)庫。

①使用Navicat創(chuàng)建學(xué)生信息管理數(shù)據(jù)庫grademo

參考課本P91

②使用SQL語句創(chuàng)建數(shù)據(jù)庫MyDBo

CREATEDATABASEMyDB；

（2）查看數(shù)據(jù)庫屬性。

①在Navicat中查看創(chuàng)建后的gradem數(shù)據(jù)庫和MyDB數(shù)據(jù)庫的狀態(tài)，查看數(shù)據(jù)庫所在的文件夾。

（略）

②利用SHOWDATABASES命令顯示當(dāng)前的所有數(shù)據(jù)庫。

SHOWDATABASES；

（3）刪除數(shù)據(jù)庫。

①使用Navicat圖形工具刪除gradem數(shù)據(jù)庫。

參考課本P93

②使用SQL語句刪除MyDB數(shù)據(jù)庫。

DROPDATABASEMyDB；

③利用SHOWDATABASES命令顯示當(dāng)前的所有數(shù)據(jù)庫。

SHOWDATABASES；

項目5實訓(xùn)表的創(chuàng)建與管理

1.實訓(xùn)目的

（1）掌握表的基礎(chǔ)知識。

（2）掌握使用Navicat管理工具和SQL語句創(chuàng)建表的方法。

（3）掌握表的維護、修改、查看、刪除等基本操作方法。

2.實訓(xùn)內(nèi)容和要求

（1）在gradem數(shù)據(jù)庫中創(chuàng)建表5.10?表5.14所示結(jié)構(gòu)的表。

表5.10student表的表結(jié)構(gòu)

字段名稱數(shù)據(jù)類型長度小數(shù)位數(shù)是否允許NULL值說明

snochar10否主碼

snamevarchar8是

ssexchar2是取值：男或女

sbirthdaydate是

saddressvarchar50是

sdeptchar16是

specialityvarchar20是

表5.11course表（課程名稱表）的表結(jié)構(gòu)

字段名稱數(shù)據(jù)類型長度小數(shù)位數(shù)是否允許NULL值說明

enochar5否主碼

cnamevarchar20否

表5.12sc表（成績表）的表結(jié)構(gòu)

字段名稱數(shù)據(jù)類型長度小數(shù)位數(shù)是否允許NULL值說明

snochar10否組合主碼，外碼

enochar5否組合主碼，外碼

degreedecimal41是取值rioo

表5.13teacher表（教師表）的表結(jié)構(gòu)

字段名稱|數(shù)據(jù)類型|長度|小數(shù)位數(shù)|是否允許NULL值|說明

tnochar3否主碼

tnamevarchar8是

tsexchar2是取值：男或女

tbirthdaydate是

tdeptchar16是

表5.14teaching表（授課表）的表結(jié)構(gòu)

字段名稱數(shù)據(jù)類型長度小數(shù)位數(shù)是否允許NULL值說明

enochar5否組合主碼，外碼

tnochar3否組合主碼，外碼

ctermtinyint10是取值rio

（2）向表5.10?表5.14輸入數(shù)據(jù)記錄，見表5.15?表5.19。

表5.15學(xué)生關(guān)系表student

snosnamessexsbirthdaysaddresssdeptspeciality

20050101李勇男1987-01-12山東濟南計算機工程系計算機應(yīng)用

20050201劉晨女1988-06-04山東青島信息工程系電子商務(wù)

20050301王敏女1989-12-23江蘇蘇州數(shù)學(xué)系數(shù)學(xué)

20050202張立男1988-08-25河北唐山信息工程系電子商務(wù)

表5.16課程關(guān)系表course

enocnameenocname

C01數(shù)據(jù)庫C03信息工程系統(tǒng)

C02數(shù)學(xué)C04操作系統(tǒng)

表5.17成績表sc

snoenodegree

2005010101C0192

2005010101C0285

2005010101C0388

2005020201C0290

2005020201C0380

表5.18教師表teacher

tnotnamotSGXtbirthdaysdept

101李新男1977-01-12計算機工程系

102錢軍女1968-06-04計算機工程系

201王小花女1979-12-23信息工程系

202張小青男1968-08-25信息工程系

表5.19授課表teaching

enotnocterm

C011012

C021021

C032013

C042024

（3）修改表結(jié)構(gòu)。

①向student表中增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期時間型。

②將student表中的sdept字段長度改為20。

③將student表中的speciality字段刪除。

④刪除student表。

3.思考題

（1）MySQL的數(shù)據(jù)文件有幾種？擴展名分別是什么？

.frm描述表結(jié)構(gòu)的文件

.myd表的數(shù)據(jù)文件

,myi表數(shù)據(jù)文件中的索引文件

（2）MySQL中有哪幾種整型數(shù)據(jù)類型？它們占用的存儲空間分別是多少？取值范圍分別是什么？

見課本101頁表5.1

（3）在定義基本表語句時，NOTNULL參數(shù)的作用是什么？

指不允許為空值，該列必須輸入數(shù)據(jù)

（4）主碼可以建立在“值可以為NULL”的列上嗎？

不能

項目6實訓(xùn)|：數(shù)據(jù)查詢實現(xiàn)

一、簡單查詢

1.實踐目的

（1）掌握SELECT語句的基本用法。

（2）使用WHERE子句進行有條件的查詢。

（3）掌握使用IN和NOTIN,BETWEEN…AND和NOTBETWEEN…AND來限定查詢范圍的方法。

（4）掌握利用LIKE子句實現(xiàn)字符串匹配查詢。

2.實踐內(nèi)容和要求

完成下面查詢。

（1）查詢所有學(xué)生的基本信息、所有課程的基本信息和所有學(xué)生的成績信息（用3條SQL語句）。

select*fromstudent；

select*fromcourse；

select?fromsc；

（2）查詢所有學(xué)生的學(xué)號、姓名、性別和出生日期。

selectsno,sname,ssex,sbirthdayfromstudent；

（3）查詢所有課程的課程名稱。

selectcnamefromcourse；

（4）查詢前10門課程的課程號及課程名稱。

selecteno,cnamefromcourselimit10；

（5）查詢所有學(xué)生的姓名及年齡。

selectsname,year（curdate（））-year（sbirthday）fromstudent；

（6）查詢所有年齡大于18歲的女生的學(xué)號和姓名。

selectsno,snamefromstudent

where（year（curdate（））-year（sbirthday））>18andssex='女'；

（7）查詢所有男生的信息。

select*fromstudentwheressex='男'；

（8）查詢所有任課教師的姓名和所在系別。

selectTname,deptnofromteacher；

（9）查詢“電子商務(wù)”專業(yè)的學(xué)生姓名、性別和出生日期。

selectsname,ssex,sbirthdayfromstudentwherespeciality='電子商務(wù)'；

（10）查詢sludenl表中的所有系名。

selectdistinctsdeptfromstudent；

<11）查詢“C01”課程的開課學(xué)期。

selectctermfromteachingwherecno='c01.'；

（12）查詢成績在80?90分的學(xué)生學(xué)號及課程號。

selectofromscwheredegreebetween80and90；

（13）查詢在1970年1月1日之前出生的男教師信息。

select*fromteacherwhereyear（Tbirthday<1970）andTsex='男'；

（14）輸出有成績的學(xué)生學(xué)號。

selectdistinctsnofromscwheredegreeisnotnull；

（15）查詢所有姓“劉”的學(xué)生信息。

select?fromstudentwheresname；

（16）查詢生源地不是山東省的學(xué)生信息。

select*fromstudentwheresaddresslike'_?。?andsaddressnotlike'%山東?。ァ?

（17）查詢成績?yōu)?9分、89分或99分的記錄。

select*fromscwheredegreein（79,89,99）；

或者select*fromscwheredegree=79ordegree=89ordegree=99；

（18）查詢名字中第2個字是“小”字的男生的學(xué)生姓名和地址。

selectsname,saddressfromstudentwheresnamelike'_?。?andssex='男

（19）查詢名稱以“計算機，開頭的課程名稱。

selectcnamefromcoursewherecnamelike'計算機/_%'escape*/';

（20）查詢計算機工程系和軟件工程系的學(xué)生信息。

select*fromstudentwheresdept='計算機工程系'orsdept='軟件工程系'；

或者select*fromstudentwheresdeptin（'計算機工程系'，'軟件工程系'）；

3.思考題

（1）LIKE的通配符有哪些？分別代表什么含義？

%：任意多個字符

_：單個字符

（2）知道學(xué)生的出生日期，如何求出其年齡？

用當(dāng)前的年份減去學(xué)生的出生年份，其中，CURDATEO函數(shù)返回當(dāng)前的系統(tǒng)日期和時間，YEARO

函數(shù)返回指定日期的年部分的整數(shù)C

（3）關(guān)鍵字ALL和DISTINCT有什么不同的含義？

ALL：表示輸出所有記錄，包括重復(fù)值

DISTINCT：表示在查詢結(jié)果中去掉重復(fù)值

（4）IS能用“=”來代替嗎？

不能

（5）數(shù)據(jù)的范圍除了可以利用BETWEEN…AND運算符表示外，能否用其他方法表示？怎樣表示？

能

二、分組與排序

1.實踐目的

（1）掌握聚集函數(shù)的使用方法。

（2）掌握利用GROUPBY子句對查詢結(jié)果分組。

（3）掌握利用ORDERBY子句對查詢結(jié)果排序。

（4）掌握SELECT命令的靈活應(yīng)用。

2.實踐內(nèi)容和要求

完成下面查詢。

(1)統(tǒng)計有學(xué)生選修的課程的門數(shù)。

selectcount(distincteno)fromsc；

(2)計算“C01”課程的平均成績。

selectavg(degree)fromscwherecno=,c01'；

(3)查詢選修了“C03”課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分數(shù)降序排列。

selectsno,degreefromscwherecno='c03'orderbydegreedesc；

(4)查詢各個課程號及相應(yīng)的選課人數(shù)。

selecteno,count(*)fromscgroupbyeno；

(5)統(tǒng)計每門課程的選課人數(shù)和最高分。

selecteno,count(*),max(degree)fromscgroupbyeno；

(6)統(tǒng)計每個學(xué)生的選課門數(shù)和考試總成績，并按選課門數(shù)降序排列。

selectsno,count(?),sum(degree)fromscgroupbysnoorderbycount(?)desc；

(7)查詢選修了3門以上課程的學(xué)生學(xué)號。

selectsnofromscgroupbysnohavingcount(?)>3；

(8)查詢成績不及格的學(xué)生學(xué)號及課程號，并按成績降序排列。

selectsno,enofromscwheredegree<60orderbydegreedesc；

(9)查詢至少選修一門課程的學(xué)生學(xué)號。

selectsnofromscgroupbysnohavingcount(?)>=1；

或：selectdistinctsnofromsc；

(10)統(tǒng)計輸出各系學(xué)生的人數(shù)。

selectsdept,count(?)fromstudentgroupbysdept；

(11)統(tǒng)計各系學(xué)生人數(shù)。

selectsdept,count(?)fromstudentgroupbysdept；

(12)統(tǒng)計各系的男、女生人數(shù)。

selectsdept,ssex,count(?)fromstudentgroupbysdept,ssex；

(13)統(tǒng)計各班級的學(xué)生人數(shù)。

selectclassno,count(*)fromstudentgroupbyclassno；

(14)統(tǒng)計各班的男、女生人數(shù)。

selectclassno,ssex,count(*)fromstudentgroupbyclassno,ssex；

(15)統(tǒng)計各系的老師人數(shù),并按人數(shù)降序排序.

selectdeptno,count(*)fromteachergroupbydeptnoorderbycount(*)asc;

(16)統(tǒng)計不及格人數(shù)超過10人的課程號。

selecteno,count(*)fromscwheredegree<60groupbyenohavingcount(*)>10；

(17)查詢軟件工程系的男生信息，查詢結(jié)果按出生日期升序排序，出生日期相同的按地址降序排序。

select*fromstudent

wheresdept='軟件工程系'andssex='男'orderbysbirthdayasc,saddressdesc；

3.思考題

(1)聚集函數(shù)能否直接使用在SELECT子句、HAYING子句、WHERE子句、GROUPBY子句中？

可以直接用在SELECT子句、HAVING子句、GROUPBY子句中

不能用在WHERE子句中

(2)WHERE子句與HAVING子句有何不同？

HAVING條件作用于結(jié)果組，選擇滿足條件的結(jié)果組；

WHERE條件作用于被查詢的表，從中選擇滿足條件的記錄。

(3)對杳詢結(jié)果進行重新排序時,必須指定排序方式嗎？

不用，可以安照默認的排序方式

<4）在對數(shù)據(jù)進行分組統(tǒng)計數(shù)據(jù)時，能不能按照多個字段進行分組？

能

三、多表連接查詢

1.實踐目的

（1）掌握SELECT語句在多表查詢中的應(yīng)用。

（2）掌握多表連接的幾種連接方式及應(yīng)用。

（3）能夠靈活運用多表連接查詢解決實際問題。

2.實踐內(nèi)容和要求

完成下面查詢。

（1）查詢計算機工程系女學(xué)生的學(xué)生學(xué)號、姓名及考試成績。

selecta.sno,sname,degreefromstudenta,scb

wherea.sno=b.snoandsdept='計算機工程系'andssex='女'；

（2）查詢“李勇”同學(xué)所選課程的戌績。

selecteno,degreefromstudenta,scbwherea.sno=b.snoandsname='李勇'；

（3）查詢“李新”老師所授課程的課程名稱。

selectcnamefromteachera,teachingb,coursec

wherea.Tno=b.tnoandb.cno=c.enoandTname=’李新'；

（4）查詢女教師所授課程的課程號及課程名稱。

selectb.eno,cnamefromteachera,teachingb,coursec

wherea.Tno=b.tnoandb.cno=c.enoandTsex='女'；

（5）查詢至少選修一門課程的女學(xué)生姓名。

selectsnamefromstudenta,scbwherea.sno=b.snoandssex='女

（6）查詢姓“王”的學(xué)生所學(xué)的課程名稱。

selectcnamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandsnamelike'王％’；

<7）查詢選修“數(shù)據(jù)庫”課程且成績在80?90分的學(xué)生學(xué)號及成績。

selecta.sno,degreefromsca,courseb

wherea.cno=b.enoandcname='數(shù)據(jù)庫原理及應(yīng)用'anddegreebetween80and90；

（8）查詢課程成績及格的男同學(xué)的學(xué)生信息及課程號與成績。

selecta.*.eno.degreefromstudenta,scb

wherea.sno=b.snoanddegree>60andssex=,男'；

（9）查詢選修“C04”課程的學(xué)生的平均年齡。

selectavg（year（curdate（））-year（sbirthday））fromstudenta,scb

wherea.sno=b.snoandcno=*c04,；

（10）查詢選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和姓名。

selecta.sno,snamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandcname='高等數(shù)學(xué)'；

<11）查詢“錢軍”教師任課的課程號，選修其課程的學(xué)生的學(xué)號和成績。

selectb.tno,sno,degreefromteachera,teachingb,scc

wherea.Tno=b.tnoandb.cno=c.enoandTname='錢軍

（12）查詢在第3學(xué)期所開課程的課程名稱及學(xué)生的成績。

selectcname,degreefromteachinga,courseb,scc

wherea.cno=b.enoandb.cno=c.enoandcterm=3;

（13）查詢“C02”課程不及格的學(xué)生信息。

selecta.*fromstudenta,scb

wherea.sno=b.snoandcno=,c02*anddegree<60；

<14）查詢軟件系成績在90分以上的學(xué)生姓名、性別和課程名稱。

selectsname,ssex,cnamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandsdept二'軟件工程系'anddegree>90；

（15）查詢同時選修了“C04”和“C02”課程的學(xué)生姓名和成績。

selectsname.b.degree,c.degreefromstudenta,scb,scc

wherea.sno=b.snoanda.sno=c.snoandb.sno=c.snoandb.cno='c04'andc.cno=,c02*；

3.思考題

（1）指定一個較短的別名有什么好處？

簡化表名，一個查詢語句里可能會多次用到別名，所以較短的別名便于應(yīng)用

（2）內(nèi)連接與外連接有什么區(qū)別？

內(nèi)連接中，只有在兩個表中匹配的行才能在結(jié)果中集中出現(xiàn)；

在外連接中，可以只限制一個表，而對另一個表不加限制（所有的行都出現(xiàn)在結(jié)果集中）。

四、嵌套查詢

1.實踐目的

（1）掌握嵌套查詢的使用方法。

（2）掌握相關(guān)子查詢與嵌套子查詢的區(qū)別。

（3）掌握帶IN謂詞的子查詢的使用方法。

（4）掌握帶比較運算符的子查詢的使用方法。

（5）掌握帶ANY或ALL謂詞的子查詢的使用方法。

（6）了解帶EXISTS謂詞的子查詢的使用方法。

2.實踐內(nèi)容和要求

完成下面查詢。

<1）查詢“李勇”同學(xué)所選課程的成績。

selecteno,degreefromsc

wheresnoin（selectsnofromstudentwheresname二’李勇'）；

（2）查詢“李新”老師所授課程的課程名稱。

selectcnamefromcourse

wherecno=（selectenofromteachingwheretno=（selectTnofromteacherwhereTname=r

李新'））；

<3）查詢女教師所授課程的課程號及課程名稱。

selecteno,cnamefromcourse

whereenoin（selectenofromteachingwheretnoin（selectTnofromteacherwhereTsex=,

女'））；

（4）查詢姓“王”的學(xué)生所學(xué)的課程名稱。

selectcnamefromcourse

whereenoin（selectenofromscwheresnoin（selectsnofromstudentwheresnamelike

'王%'））;

（5）查詢“C02”課程不及格的學(xué)生信息。

select*fromstudent

wheresnoin（selectsnofromscwherecno=*c02,anddegree<60）;

（6）查詢選修“數(shù)據(jù)庫”課程且成績在80?90分的學(xué)生學(xué)號及成績。

selectsno,degreefromsc

whereeno=（selectenofromcoursewherecname='數(shù)據(jù)庫原理及應(yīng)用'）anddegreebetween

80and90；

(7)查詢選修“C04”課程的學(xué)生的平均年齡。

selectavg(year(curdatc0)-year(sbirthday))fromstudent

wheresnoin(selectsnofromscwherecno=1c041)；

(8)查詢選修課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和姓名。

selectsno,snamefromstudent

wheresnoin(selectsnofromscwherecno=(selectenofromcoursewherecname=1高等

數(shù)學(xué)'))；

(9)查詢“錢軍”教師任課的課程號，選修其課程的學(xué)生的學(xué)號和成績。

selectc.eno,sno,degreefromteachingb,scc

whereb.cno=c.enoandTno=(selectTnofromteacherwhereTname='錢軍')；

(10)查詢在第3學(xué)期所開課程的課程名稱及學(xué)生的成績。

selectcname,degreefromcoursea,scb

wherea.cno=b.enoandb.enoin(selectenofromteachingwherecterm=3)；

(11)查詢與“李勇”同一個系的同學(xué)姓名。

selectsnamefromstudent

wheresdeptin(selectsdeptfromstudentwheresname=’李勇’)；

(12)查詢學(xué)號比“劉晨”同學(xué)大，而出生日期比她小的學(xué)生姓名。

selectsnamefromstudent

wheresno>all(selectsnofromstudentwheresname='劉晨')

andyear(curdate())-year(sbirthday)<al1(selectyear(curdate())-year(sbirthday)from

studentwheresname='劉晨')；

(13)查詢出生日期大于所有女同學(xué)出生日期的男同學(xué)的姓名及系別。

selectsname,sdeptfromstudent

wheresbirthday>all(selectsbirthdayfromstudentwheressex='女')

andssex=*男*；

(14)查詢成績比該課程平均成績高的學(xué)生的學(xué)號及成績。

selectsno,degreefromscawhere

degree>(selectavg(degree)fromscbwhereb.cno=a.eno)；

(15)查詢不講授“C01”課的教師姓名。

selectTnamefromteacher

whereTnonotin(selecttnofromteachingwherecno='c017);

(16)查詢沒有選修“C02”課程的學(xué)生學(xué)號及姓名。

selectsno,snamefromstudent

wheresnonotin(selectsnofromscwherecno=1c02')；

(17)查詢選修了“數(shù)據(jù)庫”課程的學(xué)生學(xué)號、姓名及系別。

selectsno,sname,sdeptfromstudent

wheresnoin(selectsnofromsc

wherecno=(selectenofromcoursewherecname='數(shù)據(jù)庫原理及應(yīng)用'))；

3.思考題

(1)IN與“=”在什么情況下作用相同？

子查詢返回值唯一時

(2)使用存在量詞[NOT]EXISTS的嵌套查詢時，何時外層查詢的WHER3條件為真？何時為假？

子查詢有滿足條件的記錄時為真.反之.條件為假

（3）當(dāng)既能用連接查詢又能用嵌套查詢時，應(yīng)該選擇哪種查詢較好？為什么？

嵌套查詢執(zhí)行速度比較快，執(zhí)行效率高

（4）子查詢一般分為幾種？

兩種：相關(guān)子查詢和不相關(guān)子查詢

（5）相關(guān)子查詢的執(zhí)行過程是什么？

1、子查詢?yōu)橥獠坎樵兊拿恳粋€元組執(zhí)行一次，外部查詢將子查詢引用列的值傳給子查詢。

2、如果子查詢的任何行與其匹配，外部查詢則取此行放入結(jié)果表。

3、再回到1追到處理完外部表的每一行。

五、數(shù)據(jù)更新

1.實訓(xùn)目的

（1）掌握利用INSERT語句實現(xiàn)對表數(shù)據(jù)的插入操作。

（2）掌握利用UPDATE語句實現(xiàn)對表數(shù)據(jù)的修改操作。

（3）掌握利用DELETE語句實現(xiàn)對表數(shù)據(jù)的刪除操作。

2.實踐內(nèi)容和要求

利用SELECTINTO…語句備份student、sc、course這3個表，備份表名自定。

（1）向student表中插入記錄（"2005010203","張靜"，"女「1981-3-21",”軟件H程系"，”軟件技

術(shù)。

insertintostudent（sno,sname,sbirthday,ssex,sdept,speciality）

values（'20050203','張靜'，'1981—3-21女'，'CS','電子商務(wù)'）；

（2）插入學(xué)號為“2005010302”、姓名為“李四”的學(xué)生信息。

insertintostudent（sno,sname）values（'20050302‘，'李四'）；

（3）把計算機工程系的學(xué)生記錄保存到表TS中（TS表已存在，表結(jié)構(gòu)與student表相同）。

InsertintoTSselect*fromstudentwheresdept='計算機工程系'

（4）將學(xué)號為“2005010202”的學(xué)生姓名改為“張華”，系別改為“電子工程系”，專業(yè)改為“電

子應(yīng)用技術(shù)”。

updatestudentsetsname二'張華'，sdept='CS',speciality'多媒體技術(shù)'

wheresno=,200502021；

（5）將“李勇”同學(xué)的專業(yè)改為“計算機信息管理”。

updatestudentsetspecialit產(chǎn)'計算機信息管理'wheresname='李勇'；

（6）刪除學(xué)號為“2005010302”的學(xué)4記錄.

deletefromstudentwheresno=,20050302,；

（7）刪除“計算機工程系”所有學(xué)生的選課記錄。

deletefromscwheresnoin（selectsnofromstudentwheresdept='計算機工程系'）；

（8）刪除sc表中尚無成績的選課記錄。

deletefromsc_copywheredegreeisnull；

（9）把“劉晨”同學(xué)的選修記錄全部刪除。

deletefromscwheresno=（selectsnofromstudentwheresname='劉晨’）；

3.思考題

（1）如何從備份表中恢復(fù)3個表？

可以利用insertinto〈表名〉select*from〈備份表名）語句

（2）DROP語句和DELETE語句的本質(zhì)區(qū)別是什么？

drop命令是刪除表

delete命令是刪除表里面的記錄，表還存在

（3）利用INSERT、UPDATE和DELETE語句可以同時對多個表進行操作嗎？

不可以

課外拓展內(nèi)容參考答案:

(l)select*fromToyswherecToyNamelike'%Racer%'；

(4)select?shopperwherevFirstNamelike's%'；

(3)selectdistinctcStatefromRecipient;

(4)selectcToyName,cCategoryIDfromToys

(5)selectcOrderNo,cToylD,vDescriptionfromOrderDetaila,Wrapperb

Wherea.cWrapperId=b.cWrapperld

(6)selectcToyName,cBrandld,cCategoryIdfromToys；

(7)selecta.vFirstName,a.vLastName,a.vAddress,b.vFirstName,b.vLastName.b.vAddress

fromShoppera,Recipientb,Ordersc

wherea.cShopperId=c.cShopperldandb.cOrderNo=c.cOrderNo

(8)selectcToyName,b.cCartldfromToysaleftjoinShoppingCartbona.cToyId=b.cToyld

(9)insertintoPremiumToysselect*fromToyswheremToyRate>20

(10)selectvFirstName,vLastName,vAddress,cCityfromShopper；

selectvFirstName,vLastName,vAddress,cCityfromRecipient；

(11)selectcToyNamefromToyswheremToyRate=(selectmax(mToyRate)fromToys)

(12)selectmShippingChargesfromOrderswherecShippingModeId=*01'

(13)selectcOrderNofromOrdersorderbymTotalCostdesclimit3

(14)select*fromToyswheremToyRatebetween10and20

(15)selectvLastName,vFirstName,vEmailldfromShopperwherecState

in('California'Illinois*)；

(16)selectcOrderNo,dOrderDate,cShopperld,mTotalCostfromOrders

WheredOrderDate='2001-05-20*andmTotalCost>75

(17)selectcToyNamefromToyswherecCategoryId=,002*andmToyRate<20

(18)select*fromOrders

(19)select?fromShopperwherecStateO*Texas'

(20)selectcToyName,mToyRatefromToysorderbymToyRatedesc

(21)selectcToyNamefromToyswheremToyRate<20orderbymToyRate

(22)selectcOrderNo,cShopperld,mTotalCostfromOrdersorderbymTotalCostasc

(23)selectcount(distinctcToyld)fromOrderDetail

(24)selectmax(mToyRate),min(mToyRate),avg(mToyRate)fromToys

(25)selectsum(mTotalCost)fromOrders

(26)selectcOrderNo,mToyCostfromOrderDetail

(27)selectcOrderNo,mToyCostfromOrderDetailwheremToyCost>50

(28)selectcToyldfromPickOfMonthwhereiYear=,2000'orderbyiTotalSoldlimit5

(29)selectcOrderNo,a.cToyld,mToyRatefromToysa,OrderDetailbwherea.cToyId=b.cToyld；

selectmToyCostfromOrderDetail；

selectsum(mToyCost)fromOrderDetail；

(30)selectcToyName,vToyDescription,mToyRatefromToys；

(31)selectcOrderNo,dShipmentDate,dActualDeliverDate,dActualDeliverDate-dShipmentDateas

DaysinTransitfromShipment

(32)selectcOrderNo.dActualDeliverDate-dShipmentDateasDaysinTransitfromShipmentwhere

cOrderNo=,000009'

項目7實訓(xùn)：索引、視圖的創(chuàng)建與管理使用

1.實訓(xùn)目的

（1）理解索引的概念與類型。

（2）掌握創(chuàng)建、更改、刪除索引的方法。

<3）掌握維護索引的方法。

（4）理解視圖的概念及優(yōu)點。

（5）掌握視圖的創(chuàng)建、修改和刪除。

（6）掌握使用視圖來訪問數(shù)據(jù)的方法。

2.實訓(xùn)內(nèi)容及要求

（1）使用Navicat圖形工具創(chuàng)建、管理索引。

①在數(shù)據(jù)庫test下創(chuàng)建workinfo表。創(chuàng)建表的同時在id字段上創(chuàng)建名為index_id的唯一性索弓I,

而且以降序的格式排列。workinfo表結(jié)構(gòu)內(nèi)容如表7.1所示。

表7.1workinfo表結(jié)構(gòu)內(nèi)容

字段名字段描述數(shù)據(jù)類型主鍵外鍵非空唯一自增

id編號INT(IO)是否是是是

name職位名稱VARCHAR(20)否否是否否

type職位類別VARCHAR(IO)否否否否否

address工作地址VARCIIAR(5O)否否否否否

wages工資INT否否否否否

contents工作內(nèi)容INTYTEXT否否否否否

extra附加信息TEXT否否否否否

②創(chuàng)建索引。為name字段創(chuàng)建索引index_name,在type和address字段上創(chuàng)建名為index_t的

組合索引。

createindexindex_nameonwworkinfo(sname);

createindexindex_tonworkinfo(type,address)；

③在extra字段上創(chuàng)建名為index_ext的全文索引。

createspatialindexindex_extonworkinfo(extra)；

④刪除索引。刪除workinfo表的唯一性索引index_id。

dropindexindex_idonworkinfo；

（2）使用SQL語句創(chuàng)建、管理索引。

①利用createtabic語句在test數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)表writers,其表結(jié)構(gòu)內(nèi)容如表7.2所示。創(chuàng)

建表的同時在w_id字段上添加名稱為uniqidx的唯一索引。

表7.2writers表結(jié)構(gòu)內(nèi)容

字段名字段描述數(shù)據(jù)莞型主鍵外鍵非空唯一自增

w_id編號INT(IO)是否是是是

w_name作者姓名VARCHAR(20)否否是否否

waddress作者地址VARCIIAR(50)否否否否否

w_age年齡INT否否是否否

w_note說明TEXT否否否否否

②使用altertable語句在w_name字段上建立名稱為nameidx的普通索引。

altertablewritersaddindexnameidx(w_name)

③使用createindex語句在w_address和w_age字段上建立名稱為multiidx的組合索引。

createindexmultiidxonwriters(w_address,w_age)

?使用createindex語句在w_note字段上建立名稱為ftidx的全文索引。

createfulltcxtindexftidxonwriters(w_notc)

⑤刪除索引。利用altertable語句將全文索引ftidx刪除，利用dropindex語句將nameidx索

引刪除。

altertablewritersdropindexftidx；

dropindexnameidxonwreites;

(3)使用SQL語句創(chuàng)建、管理視圖。

①創(chuàng)建視圖。

a.創(chuàng)建一個名為sc_viewl的視圖，從數(shù)據(jù)庫gradem的sc表中查詢出成績大于90分的所有學(xué)生選

修課程成績的信息。

createviewsc_viewlasselect*fromscwheredegree>90；

b.創(chuàng)建一個名為sc_view2的視圖，從數(shù)據(jù)庫gradem的sc表中查詢出成績小于80分的所有學(xué)生的

學(xué)號.課程號、成績等信息。

createviewsc_view2

asselectsno,eno,degreefromscwheredegree<80；

c.創(chuàng)建一個名為sc_view3的視圖，由數(shù)據(jù)庫gradem的student、course、sc表創(chuàng)建一個顯示

“20070303”班學(xué)生選修課程(包括學(xué)生姓名、課程