SQL Server高级技巧.docx

HYPERLINK"http://www.cnblogs.com/hoojo/archive/2011/07/16/2108129.html"SQLServerT-SQL高级查询
高级查询在数据库中用得是最频繁的，也是应用最广泛的。
Ø基本常用查询
--select
select*fromstudent;

--all查询所有
selectallsexfromstudent;

--distinct过滤重复
selectdistinctsexfromstudent;

--count统计
selectcount(*)fromstudent;
selectcount(sex)fromstudent;
selectcount(distinctsex)fromstudent;

--top取前N条记录
selecttop3*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=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*fromstudentwhereid=2orsex=1;

--between...and...相当于并且
select*fromstudentwhereidbetween2and5;
select*fromstudentwhereidnotbetween2and5;

--like模糊查询
select*fromstudentwherenamelike'%a%';
select*fromstudentwherenamelike'%[a][o]%';
select*fromstudentwherenamenotlike'%a%';
select*fromstudentwherenamelike'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,2);

--isnull是空
select*fromstudentwhereageisnull;

--isnotnull不为空
select*fromstudentwhereageisnotnull;

--orderby排序
select*fromstudentorderbyname;
select*fromstudentorderbynamedesc;
select*fromstudentorderbynameasc;

--groupby分组
按照年龄进行分组统计
selectcount(age),agefromstudentgroupbyage;
按照性别进行分组统计
selectcount(*),sexfromstudentgroupbysex;
按照年龄和性别组合分组统计，并排序
selectcount(*),sexfromstudentgroupbysex,ageorderbyage;
按照性别分组，并且是id大于2的记录最后按照性别排序
selectcount(*),sexfromstudentwhereid>2groupbysexorderbysex;
查询id大于2的数据，