mysql权威开发指南1引入一个名为BOOKSQL的新用户他密码是

第一章SQLcreateuser‘houjie410782’@’localhost’identifiedbygrantallprivilegeson*.*to‘BOOKSQL’@’LOCALHOST’withgrantoption;gratallprivilegeson*.*to‘houjie410782’@’localhost’withgrantoption;创建数据库tennis。createdatabasetennis;usecreatetableyers(yernointegernotnull,namechar(15)notnull,initialschar(3)notnull,birth_datedate,char(1)notnull,joinedsmallintnotnull,housenochar(4),postcodephonenochar(13),leaguenochar(4),primarykey(yerno))createteams(teamnointegernotnull,yernointegernotnull,divisionchar(6)notnull,primarykey)createtablematches(matchnointegernotnull,teamnointegernotnull,yernointegernotnull,wonsmallintnotnull,lostsmallintnotnull,primarykey(matchno))createpenalties(paymentnointegernotnull,yernointegernotnull,payment_datedatenotnull,amountdecimal(7,2)notnull,primarykey(paymentno))createtablecommittee_members(yernointegernotnull,begin_datedatenotnull,end_datedate,positionprimarykey()insertintoyersvalues(2,'Everett','R','1948-09-01','M',1975,'Stoneyinsertintoyersvalues(7,'Wise','GWS','1963-05-11','M',1981,' insertintoyersvalues(8,'NewCastle','B','1962-07-08','F',1980,'Stationinsertintoyersvalues(27,'Collins','DD','1964-12-28','F',1983,'Longinsertintoyersvalues(28,'Collins','C','1963-06-22','F',1983,'OldMianinsertintoyersvalues(39,'Bishop','D','1956-10-29','M',1980,'Eatoninsertintoyersvalues(44,'Baker','E','1963-01-09','M',1980,'Lewisinsertintoyersvalues(57,'Brown','M','1971-08-17','M',1985,' insertintoyersvalues(83,'Hope','PK','1956-11-11','M',1982,'Magdaleneinsertintoyersvalues(95,'Miller','P','1963-05-14','M',1972,'Highinsertintoyersvalues(100,'Parmanter','P','1963-02-28','M',1979,'Haseltineinsertintoyersvalues(104,'Moorman','D','1970-05-10','F',1984,'Stoutinsertintoyersvalues(112,'Bailey','IP','1963-10-01','F',1984,'Vixeninsertintoyersvalues(6,'parmenter','R','1964-06-25','M',1977,'Haseltineinsertintoteamsvalues(1,6,'first');insertintomatchesvalues(1,1,6,3,1);insertintomatchesvalues(2,1,6,2,3);insertintomatchesvalues(3,1,6,3,0);insertintomatchesvalues(4,1,44,3,2);insertintomatchesvalues(5,1,83,0,3);insertintomatchesvalues(6,1,2,1,3);insertintomatchesvalues(7,1,2,1,3);insertintomatchesvalues(8,1,8,0,3);insertintomatchesvalues(9,2,27,3,2);insertintomatchesvalues(13,2,8,0,3);insertintopenaltiesvalues(1,6,'1980-12-08',100.00);insertintopenaltiesvalues(2,44,'1981-05-05',75.00);insertintopenaltiesvalues(3,27,'1983-09-10',100.00);insertintopenaltiesvalues(4,104,'1984-12-08',50.00);insertintopenaltiesvalues(5,44,'1980-12-08',25.00);insertintopenaltiesvalues(6,8,'1980-12-08',25.00);insertintopenaltiesvalues(7,44,'1982-12-30',30.00);insertintopenaltiesvalues(8,27,'1984-11-insertintocommittee_membersvalues(2,'1990-01-01','1992-12-31','Chairman');insertintocommittee_membersvalues(2,'1994-01-01',NULL,'Member');insertintocommittee_membersvalues(6,'1990-01-01','1990-12-31','Secretary');insertintocommittee_membersvalues(6,'1991-01-01','1992-12-31','Member');insertintocommittee_membersvalues(6,'1992-01-01','1993-12-31','Treasurer');insertintocommittee_membersvalues(6,'1993-01-01',NULL,'Chairman');insertintocommittee_membersvalues(8,'1990-01-01','1990-12-31','Treasurer');insertintocommittee_membersvalues(8,'1991-01-01','1991-12-31','Secretary');insertintocommittee_membersvalues(8,'1993-01-01','1993-12-31','Member');insertintocommittee_membersvalues(8,'1994-01-01',NULL,'Member');insertintocommittee_membersvalues(27,'1990-01-01','1990-12-31','Member');insertintocommittee_membersvalues(27,'1991-01-01','1991-12-31','Treasurer');insertintocommittee_membersvalues(27,'1993-01-01','1993-12-31','Treasurer');insertintocommittee_membersvalues(57,'1992-01-01','1992-12-31','Secretary');insertintocommittee_membersvalues(95,'1994-01-01',NULL,'Treasurer');insertintocommittee_membersvalues(112,'1992-01-01','1992-12-31','Member');insertintocommittee_membersvalues(112,'1994-01-01',NULL,'Secretary');selectfromorderbyselectfromorderbyyerno;select*fromselectselectyerno,amountfrompenaltieswhereupdatepenaltiessetamount=200whereyerno=44;fromwherecreateindexpenalties_amountoncreateviewnumber_sets(matchno,difference)asselecematchno,abs(win-lost)from第二章Select语句：常用元素createtableincorrect_date(column1insertintoincorrect_datevalues(‘2009-13-selectcolumn1fromfrommatcheswherewon-selectteamno,divisionfromteams;selectteamnoasteam_number,divisionasdivision_of_teamfromteams;selectpaymentno,amount*100ascentsfrompenalties;从matchesselectmatchnoasprimaryKey,80aswon-lostasdifference,time('23:59:59')as'Almost_midNight','Text'asTextfrommatcheswhereselectamount*100ascentsfrompenaltiesgroupbycentsorderbyselectyers.yerno,,yers.initialsfromyerswhereyers.set@,town,postcodefromyerswhereyerno<@select@SQL_WARNINGSsetset统变量sql_select_limit的方式为：selectselectcurrent_date;selectcurrent_time;selectcurrent_user;selectyerno,name,when'F'then'Female'when'M'then'Male'endfromyersselectyerno,town,birth_date,casetownwhen'Stratford'then'ST'when'Plymouth'then'PM'when'Inglewood'then'IW'else'OT'casetowncasebirth_datewhen'1948-09-01'then'OldStratford'else'YoungStratford'casebirth_datewhen'1962-07-08'then'OldIngelwood'else'YoungInglewood'endastypefromyers;selectyerno,joined,whenjoined<1980thenwhenjoined<1983then‘jubior’else‘children’fromyersorderbyjoined;selectyerno,joined,town,whenjoined>=1980andjoined<1983then‘senior’whentown=’Eltham’then‘Elthammers’whenyerno<10then‘first_members’else‘Rest’endaslevelfromyers;selectpaymentno,year(payment_date)frompenaltieswherefromwhere空，将其显示为1。fromyerswhereselectyerno,dayname(birth_date),monthname(birth_date),dayofyear(birth_date)fromyerswhereselectyerno,birth_date,adddate(birth_date,interval7day)asadd7daysfromyerswheredayname(birth_date)='Saturday';fromcommittee_membersor(end_dateisnullorderbyyerno;selectyerno,begin_date,end_date,fromcommittee_membersorderbyyerno;selectpaymentnofrompenaltiesselectconcat(rtrim(name),cast(birth_dateaschar(10)))fromyerswhereupdatewhereyerno=2;selectteamno,cast(NULLaschar)fromteams;selectmatchno,won,lostfrommatcheswhere把502selectselectB’11’<<3;selectconv(6,10,2),conv(10,10,2),bin(6),bin(10);33.获取属于二进制表示1001和111的十进制值。selectconv(1001,2,10),conv(111,2,10);selectfromyerswhereyerno&1;selectyerno,namefromyerswhereyerno=(frommatches;selectyerno,town||’’||street||’’||housenoasAddressfromfromwhereselectpaymentno,payment_date,payment_date+interval7dayfrompenaltieswhereselectpaymentno,payment_date,adddate(payment_date,interval7day)asadd7daysfrompenaltieswhereselectpaymentno,payment_datefrompenalties25’,interval7selectadddate(‘2004-13-12’,interval1day);showcreatetablematches_special(matchnointegernotnull,teamnointegernotnull,yernointegernotnull,wonsmallintnotnull,lostsmallintnotnull,start_datedatenotnull,start_timetimenotnull,end_datedatenotnull,end_timetimenotnull,primarykey(matchno))selectmatchno,start_time,addtime(start_time,’08:00:00’)frommatch_special;selectfromwhereaddtime(end_time,’06:30:30’)<=’24:00:00’;createtabletstamp(col1timestamp);set@time=timestamp(‘1980-12-0823:59:59.59’);insertintotstampvalues(@time+interval3microsecond);selectcol1,col1+interval3microsecondfromtstamp;selectteamnofromteamswheretrueor等同于：selectteamnofromteams;因为trueorfalse为真指明那些罚款的编号大于4。frompenalties;selectyerno,joined,yerno<15,joined>1979fromyersStratfordHaseltineLaneselectfrom的selectyerno,namefromyers答案通49题。第三 SELECT语句、表表达式和子查selectfromyers,penaltieswhereamount>25havingcount(*)>1orderbyyerno;selectyerno,leaguenofromyersorderbyselectyernofromteamsselectyernofromselectyernofrom(selectyerno,fromwhereyerno<10)asyer10 selectyernofrom(selectyerno,from(selectyerno, fromwhereyerno>10)asgreater10whereyerno<100)aslessthan100 selectfromwhereyerno<100andyerno>10andjoined>1980 selectfromwhereyerno=100)asfromwhereselectfromwhereyear(birth_date)=(selectfromwhere(selectbirth_datefromyerswhereyerno=27),(selectbirth_datefromyerswhereyerno=44),(selectbirth_datefromyerswhereselectfromwhere fromwhere第四章SELECT子句：FROM子句createdatabaseextra;useextra;createtablecities(citynointegernotcitynamechar(20)notnull,primarykey(cityno));insertintocitiesselect*fromselectteamno,namefromteams,selectyers.yerno,,yers.initials,fromfromyersasp,penaltiesaspenselectt.fromteamsast,penaltiesaspenwheret.yerno=pen.selectdistinctt.fromteamsast,penaltiesaspenwheret.yerno=,p.initialsfromyersasp,matchesasmwherep.yerno=m.yerno;selectm.matchno,p.yerno,m.teamno,,t.divisionfrommatchesasm,yersasp,teamsastwherep.yerno=m.yernoandfromyersasp,penaltiesaspenwherepen.yerno=p.yernoandselectp.fromyersasp,='Parmenter'andpar.initials='R'andp.birth_date<par.birth_date;selectp.yernofromyersasp,extra.citiesselectp.yerno,name,pen.amountfromyersasp,penaltiesaspenselectp.fromyersaspinnerjoinpenaltiesaspenon(p.yerno=pen.yerno)wherep.birth_date>'1920-06-30';selectt.teamno,fromteamsast,yersaspwheret.yerno=p.selectfromteamsastinnerjoinyersaspon(t.yerno=p.yerno);15.（左外连接）对于所有球员，获得球员号码，名字和他所引起的罚款，结果按照球员号码排selectp.yerno,name,pen.amountfromyersasp,penaltiesaspenwherep.yerno=pen.yernoorderbyp.yerno;但是这样的写法是不完整的，为了强调所有球员selectp.fromyersaspleftouterjoinpenaltiesaspenonp.yerno=pen.yernoorderbyp.左外连接是否必要，取决于问题和连接之间的关系，在p.yerno和pen.yerno之间，存在selectfrompenaltiesaspenleftouterjoinyersasponpen.yerno=p.yernoorderby对于每个球员selectp.yerno,name,t.teamno,t.divisionfromyersaspleftouterjointeamsastonp.yerno=t.yernoorderbyp.Inglewood每个球员，获取球员编号，名字，罚款列表并且列出他曾经效力过selectp.yerno,,pen.amount,m.teamnofromyersaspleftouterjoinpenaltiesaspenonp.yerno=pen.yernoleftouterjoinmatchesasmonp.yerno=m.yernowhere19.（右外连接）对于所有球员selectp.fromteamsastrightouterjoinyersaspont.yerno=p.yerno;selectp.yerno,,pen.amountfromyersasp,penaltiesaspenselectp.fromyersaspnaturaljoinpenaltiesaspenwherep.birth_date>'1920-06-30';fromteamsleftouterjoinpenaltieswhere如：fromteamsinnerjoinonteams.yerno=yers.fromteamsinnerjoinyersonusing(yerno)select*frompenaltiesleftjoinusing(selectp.yernofromyersaspwhereselectfrom(select*fromwheretown='Stratford')asselectsmall_teams.yernofrom(selectyerno,divisionfromteams)assmall_teamsselectfrommatches)asmwhere创建一个名为townsselectfrom(select'Stratford'astown,4asselect'Douglas',2)astownsorderbytown;fromyersleftouterjoin(select'Stratford'astown,4asnumberselect'Douglas',2)astownsorderbyyerno;selectfromyersleftouter(select'Stratford'astown,4asselect'Douglas',2)astownsonyers.town=towns.townwheretowns.number>2;selectselect'Arnold')asfirst_names,(select'Berg'aslast_nameselect'William')asselectnumber,power(number,3)from(select10asselect19)asnumbersselectnumberfrom(selectcast(concat(digit1.digit,concat(digit2.digit,digit3.digit))asunsignedinteger)asnumberfrom(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’unionselect‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselect‘8’unionselect‘9’)asdigit1,(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’unionselect‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselect‘8’unionselect‘9’)asdigit2,(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’unionselect‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselect‘8’unionselect‘9’)asdigit3)asnumbersorderbynumber;selectnumberassquare,round(sqrt(number))asfrom(selectcast(concat(digit1.digit,concat(digit2.digit,digit3.digit))asunsignedinteger)asnumberfrom(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’unionselect‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselect‘8’unionselect‘9’)asdigit1,(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’unionselect‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselect‘8’unionselect‘9’)asdigit2,(select‘0’asdigitunionselect‘1’unionselect‘2’unionselect‘3’select‘4’unionselect‘5’unionselect‘6’unionselect‘7’unionselectunionselect‘9’)asdigit3)asnumbersorderbynumber;第五章SELECT语句：WHERE子selectfromwhereselectyerno,birth_date,joinedfromyerswhereselectfromwhereselectyerno,leaguenofromyerswhereselectyerno,leaguenofromyerswhere者是非空值但不相等的时候，结果为false，这个条件的结果不会是unknown。selectmatchnofrommatcheswherewon=2andfrommatcheswhere(2,4)>(1,3)并不等价于(2>1)and(4>3)，而是等价于：(2>1)or(2=1and (E1?E4)or(E1=E4andE2?E5)or(E1=E4andE2=E5andselectyers.yerno,fromyers,teamswhereyers.yerno=teams.yernoandselectyerno,namefromyerswhere(selectyernofromteamswhereselectyerno,name,initialsfromyerswhere(selectbirth_datefromyerswhereselectmatchnofrommatches(selectteamnofromteamswhereselectfromwhereleagueno<=>(selectleaguenofromyerswhereselectyerno,town,fromyers(selecttownfromyerswhereyerno=7), fromyerswhereyerno=2));selectdistinctyernofromcommittee_memberswherewhereyerno=6andselectyerno,name,initialsfromyerswhere(name,initials)<(selectname,initialsfromyerswhereyerno=6);selectmatchnowhere(start_date,start_time)>(selectstart_date,start_timefrommatch_specialwhereselectfrommatches,yerswhereyers.yerno=matches.yernoandselectmatchno,yerno,teamnofrommatcheswhereyerno=(selectyernofromteamswhereteams.yerno=matches.selectmatchnofromwheresubstr((selectdivisionfromteamswhere=substr((selectnamefromyerswhereyers.yerno=matches.selectyerno,leaguenofromyerswhereselectteamnofromteamswhereselectyerno,name, fromyerswhereyear(birth_date)>'1970-12-31' selectyerno,townfromyerswheretown='Plymouth'orselectyerno,name,townfromyerswhere或者：…wherenotselectyerno,leagueno,phonenofromyerswhereleaguenoand含那些居住在Stratford并且出生于1963年的球员。selectyerno,town,birth_datefromyerswhere(town='Stratford'oryear(birth_date)='1963')andnot(year(birth_date)='1963'andtown='Stratford');25inInglewoodPlymouthMidhurstDouglasselectfromwheretown='Inglewood'ortown='Plymouth'ortown='Midhurst'orselectyerno,name,townfromyers selectyerno,name,birth_datefromyerswhereyear(birth_date)inselectmatchno,won,lostfrommatcheswhere2inselectfromwhereyernoin(selectyernofrompenaltieswherepaymentno=1),(selectyernofromteamswhereteamno=2));selectmatchno,won,lostfromwherewonin(truncate(matchno/(selectlostfrommatcheswhereselectmatchnofromwhere(selectsubstr(name,1,1)fromwhereyers.yerno=matches.yerno)inselectmatchno,won,lostfrommatcheswherewon/lostin或者…wherewon,lost)inselectyerno,name,initialsfromyerswhere(name,initials)in(selectname,initialsfromyerswhereyerno=6),(selectname,initialsfromyerswhereyerno=27));selectyerno,name,initialsfromyerswhereyernoin(selectyernofromselectyerno,namefromyerswhereyernoin(selectyernofrommatcheswhereselectyerno,namefromyerswhereyerno(selectyernofrommatcheswhereteamnonotin(selectteamnofromteamswhereyerno=6));where(begin_date,end_date)in(selectbegin_date,end_datefromcommittee_memberswhereposition='Secretary');selectname,initials,townfromyerswhereyernoin(selectyernofromselectyerno,name,birth_datefromyerswhereyear(birth_date)between'1962and1964';392，34frommatcheswherewon+lostbetween2andselectyerno,name,birth_date,initialsfromyerswherebirth_datebetween(selectbirth_datefromyerswherename='Newcastle'and(selectbirth_datefromyerswherename='Miller'andinitials='P');41.(like运算符)找出名字以大写字母B开头的每个球员的名字和号码。selectname,yernofromwherenamelike…whereselectname,yernofromyerswherenamelikeselectname,fromwherenamelikeselectname,town,yernofromyerswherenamelikeselectname,yernofromyerswherenamelike'%#_%'escapeselectname,yernofromyerswherenameregexpselectname,yernofromyerswherenameregexp'^ba';fromyerswherenameregexpselectname,yernofromwherenameregexpselectname,yernofromyerswherenameregexp'm.n';selectname,yernofromyerswherenameregexpselectyerno,postcodefromyerswherepostcoderegexp'^[0-9][0-9]3';或…wherepostcodelike'3%';selectstreet,yernofromyerswherestreetregexpselectyerno,postcodefromyerswherepostcoderegexp'[0-9]+[a-z]+';到selectname,yernofromyerswherenameregexp'^[^A-M]';或selectname,yernofromwheresubstr(name,1,1)notinselectyerno,namefromyerswherenameregexp'^[a-z]{7}';selectyerno,namefromyerswherenameregexp'^[a-z]{6,7}$';selectyerno,postcodefromyerswherepostcoderegexp'4{4}';selectyerno,streetfromyersselectyerno,namefromyerswherenameregexp'[[.space.]]';selectyerno,streetfromyerswherestreetregexpStreet[[:>:]]';selectyerno,streetfromyerswherestreetlike'%street%';createtable(booknointegernotnullprimarykey,authorstextnotnull,titletextnotnull,year_publicationyearnotnull,summarytextnotnull);给booksselectyerno,leaguenofromyerswhereleaguenoisnotnullselectname,yerno,leaguenofromyerswhereleagueno<>'8467'orleaguenoisnull;selectname,initialsfromwhereyernoin(selectyernofromselectname,initialsfromyerswhereexists(select*frompenaltieswhereyerno=yers.selectname,initialsfromyerswherenot(select*fromteamswhereyerno=yers.selectyerno,name,birth_datefromyerswherebirth_date<=all(selectbirth_datefromselectyerno,birth_datefromyerswhere(selectbirth_datefromyersaspinnerjoinmatchesasmonp.yerno=m.yernowherem.teamno=2);selectteamno,yernofrommatchesasm1where(selectwonfrommatchesasm2whereselectleagueno,yernofromyerswhereleagueno>=all(selectleaguenofromyers);selectyerno,town,leaguenofromyersasp1whereleagueno<=all(selectp2.leaguenofromyersasp2wherep1.town=p2.townandleaguenoisnotnull);selectyerno,name,birth_datefromyerswhere(selectbirth_datefromselectdistinctyernofrompenaltieswhereyerno<>27(selectamountfrompenaltieswhereselectyerno,birth_date,townfromyersasp1where(selectbirth_datefromyersasp2whereselectyerno,namefromyerswhereyerno(selectyernofromselectyerno,namefromyersaspwhereexists(select*frompenaltiesaspenselectteamno,divisionfromteamswhereexists(select*frommatcheswhereyerno=44and1selectdistinctyernofrompenaltiesaspenwhereyernoin(selectyernofrompenaltieswhere获取那些没有为1selectyerno,namefromyerswhere1<>all(selectteamnofrommatcheswhereyerno=yers.selectteamnofromteamswherenotexists(select*frommatcheswhereyerno=27andselectyernofromyersaspwherenotexists(select*fromteamsastwherenotexists(select*frommatchesaswheret.teamno=m.teamnoandp.yerno=m.selectyernofromyerswherenotexists(select*frommatchesasm1whereyerno=57andnotexists(select*frommatchesasm2wherem1.teamno=m2.teamnoandselectyernofrommatcheswhereteamnoin(selectteamnofromteamswhereteamnonotin(selectteamnofrommatcheswherematchno=57));selectyernofromyerswheretown='Stratford';获取那些引起一次25罚款的每个球员号码selectyernofrompenaltieswhere第六章select语句：select子句和聚合函数获取整个penaltiesselect*fromselectpenalties.*frompenaltiesinnerjointeamsonpenalties.yerno=teams.yerno;selectpenalties.*frompenalties,teamsselectdistincttownfromselectdistinctstreet,townfromselectdistinctleaguenofromselectcount(*)asnumbersfromselectcount(*)fromyerswhereselectcount(allleagueno)fromyers;10town列中，有多少不同的城市名？selectcount(distincttown)fromyers;selectcount(distinctsubstr(name,1,1))fromselectcount(distinctyear(payment_date))fromselectcount(distinct )fromselectyerno,namefromyersaspwhere(selectcount(*)frompenaltiesaspenwherep.yerno=pen.>(selectcount(*)frommatchesasmwherep.yerno=m.select(selectcount(*)frompenaltieswherepenalties.yerno=yers.yerno)asnumbersfromyerswhere(selectcount(*)frompenaltieswherepenalties.yerno=yers.(selectcount(*)frompenalties),(selectcount(*)fromselectmax(amount)fromselectmin(amount)frompenaltieswhereyernoin(selectyernofromyerswherewheretown='Stratford'andyers.yerno=penalties.selectcount(*)frompenaltieswhereselectmin(amount)fromselect(max(amount)-min(amount))*100asdiff_centsfromselectsubstr(max(name),1,1)fromyersorderbyselectmax(leagueno)fromyerswheretown='Midhurst';输出文本Unknown。selectcasewhenmin(leagueno)isnullthen'Unknown'elseminfromyerswhereselectyerno,amount,payment_datefrompenaltiesaspen1whereamount=(selectmax(amount)frompenaltiesasselect(selectmax(amount)frompenaltieswherepenalties.yerno=yers.yerno)as(selectmax(won)frommatcheswherematches.yerno=yers.yerno)asfromselectfrom=selectsum(amount)fromwhereyernoin(selectyernofromyerswhereselectavg(amount)frompenaltieswhereselectdistinctyernofrompenaltieswhereamount>(selectavg(amount)fromselectavg(distinctamountfrompenalties;selectavg(length(rtrim(name))),max(length(rtrim(name)))fromyers;selectpaymentno,amount,abs(amount-(selectavg(amountfrompenaltiesasfromselectvariance(amount)frompenaltieswhereselectstddev(amount)frompenaltieswhereselectvar_samp(amount),variance(amount)fromselectstddev_samp(amount),stddev(amount)from第七章SELECTGROUPBY子句selecttownfromyersgroupbytown;selecttown,count(*)fromyersgroupbytown;selectteamno,count(*),sum(won)frommatchesgroupbyselectteamno,count(*)frommatcheswhereteamno(selectteamnofromteamsinnerjoinyersonteams.yerno=yers.yernowheretown='Eltham')groupbyselectamount,count(*),amount*count(*)assum_amountfrompenaltiesgroupbyselectteamno,yernofrommatchesgroupbyteamno,yerno;selectp.fromyersaspinnerjoinpenaltiesaspenonp.yerno=pen.yernogroupbyp.selectyear(payment_date),count(*)frompenaltiesgroupbyselecttruncate(yerno/25,0),count(*),max(yerno)fromyersgroupbytruncate(selectyerno,name,leaguenofromyersgroupbyleagueno;selectmatchno,teamno,count(*)frommatchesgroupbyteamnoorderbyteamnodesc1groupbyselectgroupby现在groupby子句中的表达式也可以出现在select子句中。groupby子句groupbytown，towngroupbytown13.（group_concat函数）对于每个球队，获取球队的号码和为该七对效力的球员列表。selectteamno,group_concat(yerno)frommatchesselectteamno,group_concat(teamno)frommatchesgroupbyselectgroup_concat(paymentno)frompenalties;selectfrom(selectyerno,sum(amount)astotalfrompenaltiesgroupbyyerno)astotalswhereyernoin(selectyernofromyerswheretown='Stratford'orselectyers.fromyers,(selectyerno,count(*)asnumber_of_penaltiesfrompenaltiesgroupbyyerno)asnumber_penalties,(selectyerno,count(*)asnumber_of_teamsfromteamsgroupbyyerno)aswhereyers.yerno=number_penalties.yernoandselectdistinctm.frommatchesasmleftouterjoin(selectyerno,count(*)asnumberpfrompenaltiesgroupbyyerno)asnp 第二组包含1981年7月1日到1982年12月31日之间的所有罚款，对于每一组，得到罚款的frompenaltiesasp,(select1aspgroup,'1980-01-01'asstart,'1981-06-30'asendselect3,'1983-01-01','1984-12-31')asgroupswherep.payment_datebetweenstartandendgroupbygroups.pgrouporderbyfrompenaltiesasp1,penaltiesasp2groupbyp1.paymentno,p1.amountorderbyp1.paymentno;selectp1.paymentno,p1.amount,(p1.amount*100)/sum(p2.amount)frompenaltiesasp1,penaltiesasp2orderbyp1.paymentno;selectyerno,sum(amount)frompenaltiesgroupbyyernoselectnull,sum(amount)fromselectyerno,sum(amount)frompenaltiesgroupbyyernowith fromyersgroup ,townwith第八章select语句：having子句selectyernofrompenaltiesgroupbyyernohavingcount(*)>selectyernofrompenaltiesgroupbyyernohavingselectyerno,sum(amount)frompenaltiesgroupbyhavingselectyerno,sum(amount)frompenaltieswhereyernoin(selectyernofromteams)groupbyyernohavingselectyerno,sum(amount)frompenaltiesgroupbyhaving(selectsum(amount)frompenaltiesgroupbyselectsum(amount)frompenaltieshaving第九章select语句：orderby子句selectpaymentno,yernofrompenaltiesorderbyselectyerno,amountfrompenaltiesorderbyselectamountorderbyselectname,initials,yernofromyersorderbysubstr(name,1,1);selectyerno,amountfromorderbyabs(amount-(selectavg(amount)fromselectyerno,amountfrompenaltiesasorderby(selectavg(amount)frompenaltiesasp2wherep1.yerno=p2.yerno);selectyerno,sum(amount)frompenaltiesgroupbyyernoorderbyselectyerno,name,(selectsum(amount)frompenaltiesaspenwherepen.yerno=p.yerno)fromyersasporderby3;selectyerno,amountfrompenaltiesorderbyyernodesc,amountasc;selectdistinctleaguenofromyersorderbyleaguenodesc;第十章select语句：LIMIT子句selectyerno,namefromyersorderbyyernodesclimitselectleagueno,yerno,namefromyersorderbyleaguenoasclimitselectyerno,count(*)asnumbersfrommatcheswherewon>lostgroupbyyernoorderbyyernodesclimit3;selectyerno,count(*)asnumbersfrommatcheswherewon>lostgroupbyorderbynumbersdesc,yernodesclimit3;selectfrom(selectyerno,count(*)asnumbersfrommatcheswherewon>lostgroupbyorderbynumbersdesc,yernodesclimit3)asT;selectavg(amount)(selectamountfrompenaltiesorderbyamountlimit4)as第3高的罚款额是多少?orderbyamountdesclimit3)asselectdistinctamountfrompenaltiesorderbyamountdesclimitselectyerno(selectyernofromyerswhereleaguenoisnotnullorderbyleaguenodesclimit6)asTorderbyyernoasclimitselectyerno,namefromyerswhereyernoin(selectyerno(selectyerno,sum(amount)astotalfrompenaltiesgroupbyyernoorderbytotaldesclimit3)asT);11.（出不来正确结果）获取那些至少引起了一次罚款，并且罚款额不等于最高的两个之一，也selectyerno,namefromyerswhereyernoin(selectyenrofrompenalties)yernonot(selectyernofrompenaltiesorderbyamountdesclimit2)andyernonotin(selectyernofrompenaltiesorderbyamountasclimitselectyerno,namefromyersorderbyyernoasclimit5offset3selectsql_calc_found_rowspaymentnofrompenaltieslimit第十一章组合表达式selectyerno,townfromyerswheretown='Inglewood'selectyerno,townfromyerswheretown='Plymouth';selectbirth_dateasdatefromyersselectpayment_dateasdatefromselectyernofrompenaltiesselectyernofromselectyernofrompenaltiesselectyernofromteamsselectyernofromyerswheretown=selectcast(teamnoaschar(4))asTEAMNO,cast(yernoaschar(4))asYERNO,sum(won+lost)astotalfromfrommatchesfrommatchesorderbyselectyernofrompenaltiesunionallselectyernofrom第十二章用户变量和SET set select@PIasselectname,town,postcodefromyerswhereset@abc=5,@def='Inglewood',@ghi=date('2001-09-用一个来定义用户变量PI。set@PI=cast(22asBINARY)/7.0;set@ANR=(selectyernofromteamswhereteamno=select@yerno:=name，townpostcodeselectselect@name:=name,@town:=town,@postcode:=postcodefromyerswhereyerno=select@penaltiestotal:=sum(amount),@numberpenalties:=count(*)frompenalties;select@yerno:=yernofromyersorderbyyernodesc;select@yerno;set@CNO=(selectyernofromteamswhereteamno=1);selectnamefromyerswhereyerno=@CNO;select*fromwherepaymentno<@varandcreatetablevaribles(varnamechar(30)notnullprimarykey,varvaluechar(30)notnull);set@var1=100,@var2=insertintovariblesvaluesvar1',@var1);insertintovariblesvaluesvar2',@var2);select@var1:=varvaluefromvaribleswhereselect@var2:=varvaluefromvaribleswhereselect@var1,docurrent_date+interval2第十四章HANDLER语句penaltieshandlerpenaltiesreadfirst;//penalties表的第一handlerpenaltiescreateindexpenalties_amountonhandlerpenaltiesopenashandlerPreadpenalties_amountfirst;handlerPreadpenalties_amountnext;handlerPreadpenalties-amountlast;handlerPreadpenalties_amountprev;handlerpenalti