SQL Server T-SQL高级查询

高级查询在数据库中用得是最频繁的，也是应用最广泛的。

? 基本常用查询

 *  student;

 查询所有
  sex  student;
 过滤重复
  sex  student;
 统计
 (*)  student;
 (sex)  student;
 ( sex)  student;
 取前N条记录
  3 *  student;
  name 列重命名
 id  编号,name ,sex 性别  student;
  name 表重命名
 id,name,s.id,s.name  student s;
 列运算
 (age + id) col  student;
 s.name +  + c.name  classes c,student s  s.cid = c.id;
 条件
 *  student  id = 2;
 *  student  id > 7;
 *  student  id < 3;
 *  student  id <> 3;
 *  student  id >= 3;
 *  student  id <= 5;
 *  student  id !> 3;
 *  student  id !< 5;
 并且
 *  student  id > 2  sex = 1;
 或者
 *  student  id = 2  sex = 1;
 ...  ... 相当于并且
 *  student  id  2  5;
 *  student  id   2  5;
 模糊查询
 *  student  name   ;
 *  student  name   ;
 *  student  name    ;
 *  student  name  ;
 *  student  name   ;
 *  student  name  ;
 *  student  name  ;
 *  student  name  ;
 子查询
 *  student  id  (1,2);
  不在其中
 *  student  id   (1,2);
  是空
 *  student  age  ;
   不为空
 *  student  age   ;
  排序
 *  student   name;
 *  student   name ;
 *  student   name ;
  分组
 (age),age  student   age;
 (*),sex  student   sex;
 student   sex,age   age;
 student  id > 2   sex   sex;
  student  id > 2   sex * id   sex * id;
   所有分组
 student    age;
 分组过滤条件
 student   age  age   ;
 student   cid,sex  cid > 1;
 student   age  (age) >= 2;
 cid > 1  (cid) > 2;



? 嵌套子查询 

??? 子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。 

? 
# from (select … table)示例 


 *  (
 id,name  student  sex = 1
 t.id > 2;



上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句： 

???? 1、 包含常规选择列表组件的常规select查询 

???? 2、 包含一个或多个表或视图名称的常规from语句 

???? 3、 可选的where子句 

???? 4、 可选的group by子句 

???? 5、 可选的having子句 

? 
# 示例 


 *,( (*)  student  cid = classes.id)  num 
 classes   num;



? 
# in,not in子句查询示例 


 *  student  cid  (
 id  classes  id > 2  id < 4
 *  student  cid   (
 id  classes  name = 



in、not in 后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id； 

? 
# exists和not exists子句查询示例 


 *  student   (
 *  classes  id = student.cid  id = 3
 *  student    (
 *  classes  id = student.cid



exists和not exists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id； 

? 
# some、any、all子句查询示例 


 *  student  cid = 5  age >  (
 age  student  cid = 3
 *  student  cid = 5  age >  (
 *  student  cid = 5  age >  (



? 
? 聚合查询 

1、 distinct去掉重复数据 

  sex  student;
 (sex),( sex)  student;



? 
2、 compute和compute by汇总查询 


 age  student 
 age > 20   age  (age)  age;
 student 
 age > 20   sex,age  (age)  sex;
 age > 20   age,id  (age);
 age > 20   age  (age),(id);



compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下： 

???? a、 可选by关键字。它是每一列计算指定的行聚合 

???? b、 行聚合函数名称。包括sum、avg、min、max、count等 

???? c、 要对其执行聚合函数的列 

???? compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。 

? 
3、 cube汇总 

cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。 

 student   sex  ;
(age)  student  age      age  ;



cube要结合group by语句完成分组汇总 

? 
? 排序函数 

?? 排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如： 

?? 1、 对某张表进行排序，序号需要递增不重复的 

?? 2、 对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的 

?? 3、 在某些排序的情况下，需要跳空序号，虽然是并列 

基本语法 

([分组语句] 排序子句[][])
  列名,列名
 分组列,分组列



? 
# row_number函数 

根据排序子句给出递增连续序号 


 s.id,s.name,c.name,row_number() (  c.name)  number 
 student s,classes c  cid = c.id;



? 
# rank函数函数 

根据排序子句给出递增的序号，但是存在并列并且跳空 


(  cid)  rank  student;
(  c.name)  rank 
 cid = c.id;



? 
# dense_rank函数 

根据排序子句给出递增的序号，但是存在并列不跳空 


(  c.name)  dense 
 cid = c.id;



? 
# partition by分组子句 

可以完成对分组的数据进行增加排序，partition by可以与以上三个函数联合使用。 


(partition  c.name   s.id)  rank 
 cid = c.id;
(partition  c.name   s.id)  rank 
 cid = c.id;
(partition  c.name   s.id)  rank 
 cid = c.id;



? 
# ntile平均排序函数 

将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。 


(  c.name)  ntile 
 cid = c.id;



? 
? 集合运算 

操作两组查询结果，进行交集、并集、减集运算 

1、 union和union all进行并集运算 

 并集、不重复
 student  name  

 student  id = 4;
 *  student  name  
 
 *  student;



? 
2、 intersect进行交集运算 


 *  student  name  

 *  student;



? 
3、 except进行减集运算 



 *  student  name  ;



? 
? 公式表表达式 

查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。 

我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create view语句的执行范围内进行定义。 


 statNum(id,num)  
 cid,(*) 
 student 
 id > 0
  cid
 statNum   id;
 
(*) 
 student 
 id > 0
  cid
 (id),(num)  statNum;



? 
? 连接查询 

1、 简化连接查询 


 student s,classes c  s.cid = c.id;



? 
2、 left join左连接 


 student s   classes c  s.cid = c.id;



? 
3、 right join右连接 


 student s   classes c  s.cid = c.id;



? 
4、 inner join内连接 


 student s   classes c  s.cid = c.id;
 student s  classes c  s.cid = c.id;



? 
5、 cross join交叉连接 


 student s   classes c
 s.cid = c.id;



? 
6、 自连接（同一张表进行连接查询） 


  s.*  student s,student s1  s.id <> s1.id  s.sex = s1.sex;



? 
? 函数 

1、 聚合函数 

max最大值、min最小值、count统计、avg平均值、sum求和、var求方差 

 
(age) max_age,
(age) min_age,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">    (age) count_age,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">    (age) avg_age,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">    (age) sum_age,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">    var(age) var_age 
 student;



? 
2、 日期时间函数 

 dateAdd(,3,getDate());--加天
 dateAdd(,getDate());--加年
 dateAdd(,getDate());--加小时
 dateDiff(,,getDate());
 dateDiff(,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">--相差小时数
 dateDiff(,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> dateName(,getDate());--当前月份
 dateName(,getDate());--当前分钟
 dateName(weekday,getDate());--当前星期
 datePart(,getDate());--当前月份
 datePart(weekday,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"> datePart(,getDate());--当前秒数
 (getDate());--返回当前日期天数
 ();--返回当前日期天数
 (getDate());--返回当前日期月份
 ();
 (getDate());--返回当前日期年份
 ();
 getDate();--当前系统日期
 getUTCDate();--utc日期



? 
3、 数学函数 

 pi();--PI函数
 rand(100),rand(50),rand(),rand();--随机数
 round(rand(),3),round(rand(100),5);--精确小数位
 round(123.456,2),round(254.124,-2);
 round(123.4567,1,2);



? 
4、 元数据 

 col_name(object_id(),1);--返回列名
 col_length(,col_name(object_id(),2)); 
 type_name(type_id()),type_id();
 columnProperty(object_id(),,);
,);



? 
5、 字符串函数 

 ascii();--字符转换ascii值
 ascii();
 (97);--ascii值转换字符
 (65);
 (65);
 (45231);
 (32993);--unicode转换字符
 unicode(),unicode();--返回unicode编码值
 soundex(),soundex(),soundex();
 patindex(,),patindex(,),patindex(,);--匹配字符索引
  + (2) + , + (5) + ;--输出空格
 charIndex(,);--查找索引
,6);--查找索引
 quoteName(),quoteName();
 str(123.456,str(123.456,4);
 difference(,);--比较字符串相同
);
);
);
);
 replace(,,);--替换字符串
 stuff(,4,);--指定位置替换字符串
 replicate(,3);--重复字符串
 (,(,(,7,5);--截取字符串
 len();--返回长度
 reverse();--反转字符串
 (,4);--取左边字符串
 (,6);--取右边字符串
 (),();--小写
 (),();--大写
 ltrim(),ltrim(),ltrim();
 rtrim(),rtrim(),rtrim();



? 
6、 安全函数 

 ;
 ;
 user_id(),user_id(),user_id(),user_id();
 user_name(),user_name(1),user_name(0),user_name(2);
 ;
 suser_id();
 suser_sid(),suser_sid(),suser_sid(),suser_sid();
 is_member(),is_member();
 suser_name(),suser_name(1),suser_name(2),suser_name(3);
 suser_sname(),suser_sname(0x01),suser_sname(0x02),suser_sname(0x03);
 is_srvRoleMember(),is_srvRoleMember();
 permissions(object_id());
 ;
 schema_id(),schema_id(),schema_id();
 schema_name(),schema_name(1),schema_name(2),schema_name(3);



? 
7、 系统函数 

 app_name();--当前会话的应用程序名称
 (2011  datetime),(  money),(  varbinary);--类型转换
 (datetime,);--类型转换
 (,),(,);--返回其参数中第一个非空表达式
 collationProperty(,);
 ;--当前时间戳
 isDate(getDate()),isDate(),isNumeric(1),isNumeric();
 dataLength();
 host_id();
 host_name();
 db_name();
 ident_current(),ident_current();--返回主键id的最大值
 ident_incr(),ident_incr();--id的增量值
 ident_seed(),ident_seed();
 @@;--最后一次自增的值
 (,1)  id  tab  student;--将studeng表的烈属，以/1自增形式创建一个tab
 *  tab;
 @@;--影响行数
 @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
 @@error;--T-SQL的错误号
 @@procid;



? 
8、 配置函数 

 datefirst 7;--设置每周的第一天，表示周日
   ,datepart(dw,getDate())  ;
 @@dbts;--返回当前数据库唯一时间戳
  ;
   ;--返回语言id
   ;--返回当前语言名称
 @@lock_timeout;--返回当前会话的当前锁定超时设置（毫秒）
 @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
   ;--返回decimal 和numeric 数据类型所用的精度级别
 @@SERVERNAME;-- Server 的本地服务器的名称
 @@SERVICENAME;--服务名
 @@SPID;--当前会话进程id
 @@;
 @@version;--当前数据库版本信息



? 
9、 系统统计函数 

 @@CONNECTIONS;--连接数
 @@PACK_RECEIVED;
 @@CPU_BUSY;
 @@PACK_SENT;
 @@TIMETICKS;
 @@IDLE;
 @@TOTAL_ERRORS;
 @@IO_BUSY;
 @@TOTAL_READ;--读取磁盘次数
 @@PACKET_ERRORS;--发生的网络数据包错误数
 @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
 patIndex(,);
 patIndex(,);
 patIndex(,);
 patIndex(,);



? 
10、 用户自定义函数 

# 查看当前数据库所有函数 


 definition,*  sys.sql_modules m  sys.objects o  m.object_id = o.object_id
 type (,,);



? 
# 创建函数 

 (object_id(,)   )
  fun_add

  fun_add(@num1 ,@num2 )
 
   caller


 @ ;
 (@num1  )
 @num1 = 0;
 (@num2  )
 @num2 = 0;
 @ = @num1 + @num2;
 @;

 dbo.fun_add(id,age)  student;
 (object_id(,)   )
  fun_append

  fun_append(@args nvarchar(1024),@args2 nvarchar(1024))
 nvarchar(2048)
 @args + @args2;
 dbo.fun_append(name,)  student;



? 
# 修改函数 

  fun_append(@args nvarchar(1024),monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">     nvarchar(1024)
 @ (1024);    
 @args = (@args,);
 @args2 = (@args2,);;
 @ = @args + @args2;
 @;

)  student;



? 
# 返回table类型函数 


 name,object_id,type  sys.objects  type  (,)  type  ;
 ( ( *  sys.objects  type  (,)  name = ))
  fun_find_stuRecord
  fun_find_stuRecord(@id )
 
 ( *  student  id = @id);
 *  dbo.fun_find_stuRecord(2);
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!