SQL Server高级技巧.docx

SQL Server T-SQL高级查询高级查询在数据库中用得是最频繁的，也是应用最广泛的。? 基本常用查询--selectselect?*?from?student;?--all?查询所有select?all?sex?from?student;?--distinct?过滤重复select?distinct?sex?from?student;?--count?统计select?count(*)?from?student;select?count(sex)?from?student;select?count(distinct?sex)?from?student;?--top?取前N条记录select?top?3 *?from?student;?--alias?column?name 列重命名select?id?as?编号, name?名称, sex 性别?from?student;?--alias?table?name 表重命名select?id, name, s.id, s.name?from?student s;?--column?列运算select?(age + id) col?from?student;select?s.name +?-?+ c.name?from?classes c, student s?where?s.cid = c.id;?--where?条件select?*?from?student?where?id = 2;select?*?from?student?where?id 7;select?*?from?student?where?id 3;select?*?from?student?where?id 3;select?*?from?student?where?id = 3;select?*?from?student?where?id = 5;select?*?from?student?where?id ! 3;select?*?from?student?where?id ! 5;?--and?并且select?*?from?student?where?id 2?and?sex = 1;?--or?或者select?*?from?student?where?id = 2?or?sex = 1;?--between?...?and?... 相当于并且select?*?from?student?where?id?between?2?and?5;select?*?from?student?where?id?not?between?2?and?5;?--like?模糊查询select?*?from?student?where?name?like?%a%;select?*?from?student?where?name?like?%[a][o]%;select?*?from?student?where?name?not?like?%a%;select?*?from?student?where?name?like?ja%;select?*?from?student?where?name?not?like?%[j,n]%;select?*?from?student?where?name?like?%[j,n,a]%;select?*?from?student?where?name?like?%[^ja,as,on]%;select?*?from?student?where?name?like?%[ja_on]%;?--in?子查询select?*?from?student?where?id?in?(1, 2);?--not?in?不在其中select?*?from?student?where?id?not?in?(1, 2);?--is?null?是空select?*?from?student?where?age?is?null;?--is?not?null?不为空select?*?from?student?where?age?is?not?null;?--order?by?排序select?*?from?student?order?by?name;select?*?from?student?order?by?name?desc;select?*?from?student?order?by?name?asc;?--group?by?分组按照年龄进行分组统计select?count(age), age?from?student?group?by?age;按照性别进行分组统计select?count(*), sex?from?student?group?by?sex;按照年龄和性别组合分组统计，并排序sele