CREATEDATABASEeduONNAMEedu_dataFILENAMEcedu_datamdfSIZE10MMAXSIZE1024MFILEGROWTH1MLOGONNAMEedu_logFILENAMEcedu_logldfSIZE5MMAXSIZE100MFILEGROWTH10createdatabasestusestdropdatabasestcreateschemastdropschemacascade
1createtablestStude
t2createschemastcreatetableStude
t3setsearch_pathtostcreatetableStude
t
createtableStude
tS
ochar9primarykeyS
amechar20u
iqueSsexchar20checksexorsexcreatetablescS
ochar9C
ochar4Gradesmalli
tprimarykeyS
oC
oforeig
keyS
orefere
cesStude
tS
oforeig
keyC
orefere
cesCourseC
o
faltertableStude
taddScomdatetimeaddu
iqueC
amealtercolum
Sagesmalli
tcreateu
ique每一个索引值对应唯一数据i
dexstude
to
Stude
tS
ocreateclusteri
dexStus
ameo
Stude
tS
amedropi
dexststude
t
select2004Sageagedisti
ctS
ofromStude
twhereSdepti
iscsselectfromStude
twhereS
olike212wherertrimS
amelike_liuescapeselectS
ofromSCwhereGradeie
ullorderbyGradedesc降序selectcou
tdistictfromStude
tsumavgmaxmi

分组gropby分组后聚集函数作用于每一个组selectC
ocou
tS
o集合查询使用了聚集函数或在groupby后fromSCgroupbyC
oselectsdeptcou
tfromStude
twheressexgroupbysdeptorderbysdeptdeschavi
g对集合特性的限制selectS
ocou
tC
o
ffromSCwhereGrade85groupbyS
ohavi
gcou
t3orderbycou
tdesc
selectStude
tS
oS
ameC
ogradefromStude
tSCwhereStude
tS
oSCS
o两表连接条件selectStuded
tS
oS
ameC
ogradefromStude
ti
erjoi
SCo
Stude
tS
oSCS
owheresdeptCSselectS1S
oS1S
ameS1SdeptfromStude
tS1Stude
tS2whereS1SdeptS2Sdepta
dS2S
ame选修了课程的学生信息selects1S
oS
ameC
ogradefromStude
tS1leftouterjoi
SCs2o
s1S
os2S
oorderbys1S
o课程被选修的情况rightouterjoi
所有情况fullouterjoi
selectsStude
ts
oS
ameavggrade平均成绩fromStude
ti
erjoi
SCo
Stude
tS
oSCS
ogroupbyStude
tS
oS
amehavi
gavggrade85orderby平均成绩desc
i
a
yallselectS
ameSagefromStude
twhereSdeptcsa
dSagea
yselectSagefromStude
twhereSdeptcs
fwhereSageselectmaxSagefromStude
twhereSdeptcsa
dSdeptcsall与mi

selectS
amefromStude
twhereexistsselectfromSCwhereS
oStude
tS
oa
dC
o1selectS
amefromStude
tSCwhereStude
tS
oSCS
oa
dSCc
o1selectS
oC
ofromSCxwheregradeselectavggradefromSCywhereyS
oxS
oselectS
oC
ofromSCxSCywhereyS
oxS
ogroupbyS
oC
ohavi
ggradeavggradeselectS
amefromStude
twhere
otexistsselectfromCoursewhere
otexistsselectfromSCwhereS
oStude
tS
oa
dC
oCourseC
o
selectS
ofromStude
twhere
otexistsr