资源描述:
《sqlserver常用语句》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、SQLServerT-SQL高级查询高级查询在数据库中用得是最频繁的,也是应用最广泛的。Ø基本常用查询--selectselect*fromstudent; --all查询所有selectallsexfromstudent; --distinct过滤重复selectdistinctsexfromstudent; --count统计selectcount(*)fromstudent;selectcount(sex)fromstudent;selectcount(distinctsex)fromstudent; --top取前N条记录select
2、top3*fromstudent; --aliascolumnname列重命名selectidas编号,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=
3、2;select*fromstudentwhereid>7;select*fromstudentwhereid<3;select*fromstudentwhereid<>3;select*fromstudentwhereid>=3;select*fromstudentwhereid<=5;select*fromstudentwhereid!>3;select*fromstudentwhereid!<5; --and并且select*fromstudentwhereid>2andsex=1; --or或者select*fromstudentwh
4、ereid=2orsex=1; --between...and...相当于并且select*fromstudentwhereidbetween2and5;select*fromstudentwhereidnotbetween2and5; --like模糊查询select*fromstudentwherenamelike'%a%';select*fromstudentwherenamelike'%[a][o]%';select*fromstudentwherenamenotlike'%a%';select*fromstudentwherenam
5、elike'ja%';select*fromstudentwherenamenotlike'%[j,n]%';select*fromstudentwherenamelike'%[j,n,a]%';select*fromstudentwherenamelike'%[^ja,as,on]%';select*fromstudentwherenamelike'%[ja_on]%'; --in子查询select*fromstudentwhereidin(1,2); --notin不在其中select*fromstudentwhereidnotin(1,
6、2); --isnull是空select*fromstudentwhereageisnull; --isnotnull不为空select*fromstudentwhereageisnotnull; --orderby排序select*fromstudentorderbyname;select*fromstudentorderbynamedesc;select*fromstudentorderbynameasc; --groupby分组按照年龄进行分组统计selectcount(age),agefromstudentgroupbyage;按照性
7、别进行分组统计selectcount(*),sexfromstudentgroupbysex;按照年龄和性别组合分组统计,并排序selectcount(*),sexfromstudentgroupbysex,ageorderbyage;按照性别分组,并且是id大于2的记录最后按照性别排序selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;查询id大于2的数据,并完成运算后的结果进行分组和排序selectcount(*),(sex*id)newfromstudentwhereid
8、>2groupbysex*idorderbysex*id; --groupbyall所有分组按照年龄分组,是所有的年龄selectcount(*),agefroms