资源描述：
《SQL常用查询语句.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、SQL基本常用查询语句--selectselect*fromstudent;--all查询所有selectallsexfromstudent;--distinct过滤重复selectdistinctsexfromstudent;--count统计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列运算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...相当于并且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分组按照年龄进行分组统计selectcount(age),agefromstudentgroupbyage;按照性别进行分组统计selectcount(*),sexfromstudentgroupbysex;按照年龄和性别组合分组统计，并排序selectcount(
7、*),sexfromstudentgroupbysex,ageorderbyage;按照性别分组，并且是id大于2的记录最后按照性别排序selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;查询id大于2的数据，并完成运算后的结果进行分组和排序selectcount(*),(sex*id)newfromstudentwhereid>2groupbysex*idorderbysex*id;--groupbyall所有分组按照年龄分组，是所有的年龄selectcount(*),agefro
8、mstudentgroupbyallage;--having分组过滤条件按照年龄分组，过滤年龄为空的数据，并且统计分组的条
