資源描述：
《SQL常用查詢語句.doc》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、SQL基本常用查詢語句--selectselect*fromstudent;--all查詢所有selectallsexfromstudent;--distinct過濾重復(fù)selectdistinctsexfromstudent;--count統(tǒng)計(jì)selectcount(*)fromstudent;selectcount(sex)fromstudent;selectcount(distinctsex)fromstudent;--top取前N條記錄selecttop3*fromstudent;--aliascolumnname列重命名selectida
2、s編號,name'名稱',sex性別fromstudent;--aliastablename表重命名selectid,name,s.id,s.namefromstudents;--column列運(yùn)算select(age+id)colfromstudent;selects.name+'-'+c.namefromclassesc,studentswheres.cid=c.id;--where條件select*fromstudentwhereid=2;select*fromstudentwhereid>7;select*fromstudentwherei
3、d<3;select*fromstudentwhereid<>3;select*fromstudentwhereid>=3;select*fromstudentwhereid<=5;select*fromstudentwhereid!>3;select*fromstudentwhereid!<5;--and并且select*fromstudentwhereid>2andsex=1;--or或者select*fromstudentwhereid=2orsex=1;--between...and...相當(dāng)于并且select*fromstudentwhe
4、reidbetween2and5;select*fromstudentwhereidnotbetween2and5;--like模糊查詢select*fromstudentwherenamelike'%a%';select*fromstudentwherenamelike'%[a][o]%';select*fromstudentwherenamenotlike'%a%';select*fromstudentwherenamelike'ja%';select*fromstudentwherenamenotlike'%[j,n]%';select*fr
5、omstudentwherenamelike'%[j,n,a]%';select*fromstudentwherenamelike'%[^ja,as,on]%';select*fromstudentwherenamelike'%[ja_on]%';--in子查詢select*fromstudentwhereidin(1,2);--notin不在其中select*fromstudentwhereidnotin(1,2);--isnull是空select*fromstudentwhereageisnull;--isnotnull不為空select*fr
6、omstudentwhereageisnotnull;--orderby排序select*fromstudentorderbyname;select*fromstudentorderbynamedesc;select*fromstudentorderbynameasc;--groupby分組按照年齡進(jìn)行分組統(tǒng)計(jì)selectcount(age),agefromstudentgroupbyage;按照性別進(jìn)行分組統(tǒng)計(jì)selectcount(*),sexfromstudentgroupbysex;按照年齡和性別組合分組統(tǒng)計(jì)，并排序selectcount(
7、*),sexfromstudentgroupbysex,ageorderbyage;按照性別分組，并且是id大于2的記錄最后按照性別排序selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;查詢id大于2的數(shù)據(jù)，并完成運(yùn)算后的結(jié)果進(jìn)行分組和排序selectcount(*),(sex*id)newfromstudentwhereid>2groupbysex*idorderbysex*id;--groupbyall所有分組按照年齡分組，是所有的年齡selectcount(*),agefro
8、mstudentgroupbyallage;--having分組過濾條件按照年齡分組，過濾年齡為空的數(shù)據(jù)，并且統(tǒng)計(jì)分組的條
