常用的SQL数据库语句总结详解数据库

1as 的用处  

  as可以对表和列取别名

  在开发过程中经常遇到开始给某一个的字段去field1的名称，但后来有感觉field1字段指定不确切，于是又把此字段改成了field2，由于开始认为field1是常量，于是到处使用字符串field1，而且程序中又含有大量对field1的处理，  此时就可以使用as

例如原来的 select field1 from tableA，改为select  field2 as  field1 from tableA

代码基本就可以不动了。

2自增长的字段的插入

开发中经常遇到某个字段类型为IDENTITY，也就是自增长类型，但由于特殊需要，又要插入数据

需要临时去掉，可以使用如下语句set IDENTITY_INSERT  tablename on ，在处理完成后在使用如下语句恢复 set IDENTITY_INSERT  tablename  off 

3分组取每组的前N个数据

开发中还会遇到需要对某一组数据先分组，然后取每组的前n条记录的情况

不妨试试如下代码

/* 
 
按CurrentNodeLevel列分组，每组按NodeID排序，取出每个组中的前3个元素 
*/ 
declare @temp table (NodeID int, CurrentNodeLevel int ,rowNumber int)--定义临时表 
insert into @temp  
select NodeID,CurrentNodeLevel,ROW_NUMBER() 
                       OVER (   partition   by CurrentNodeLevel 
                               order by NodeID ) as rowNumber 
from SchoolTerminalStruct---给临时表中插入数据 
select * from @temp where rowNumber<=3--从临时表中取数据

4生成随机数

主意此方法只能写成存储过程，不能写成函数

-----返回Max ，Min之间的随机数  不能写成函数 
create proc [dbo].[getRAND] 
 
( 
 @Max int,--最大值 
 @Min int--,--最小值 
) 
AS 
BEGIN     
   DECLARE @result int 
      SELECT @result=RAND()*(@[email protected])[email protected]   
      select  @result 
END

5把满足一定条件的数据用逗号分隔

这可能也是一个很常用的语句了，经常出现在一对多的关系中对外展示，要求把子表中的数据取出来用逗号或者其他符号分隔开

/* 
 
把满足 t2.NodeID=t1.NodeID的tableA 的字段NodeName 以逗号分隔开合并为一个字段输出 
 
*/ 
 
select *,stuff((select '，' + t1.NodeName from tableA t1,tableB t2 
                where   t2.NodeID=t1.NodeID 
                for xml path('')) , 1 , 1 , '')  as text 
from tableA

6在数据库中处理异常

通过个参数附加output标志来输出参数，通过TRY，CATCH捕捉异常

CREATE PROCEDURE [dbo].[sp_UpdateFunctionTree]  
    @nodename int, 
    @Result int output--－2，操作失败；0操作成功 
AS 
BEGIN              
  begin 
    BEGIN TRY 
     UPDATE FunctionTree SET [email protected] 
      set @Result=0 --操作成功 
     END TRY 
 BEGIN CATCH 
     set @Result=-2 --操作失败 
 END CATCH 
  end      
END

7查询中的条件判断

经常遇到在某些条件下应该查询这个字段，在另外一些条件下需要其他字段的情况，可以通过unoin来完成，但也可以通过CASE WHEN 完成

/* 
 
在State=0 时返回field1 ,在State=1时返回 field2 ,其他时返回field3 
 
*/ 
 
    SELECT (CASE WHEN   State=0 THEN field1 WHEN   State=1 THEN field2 
    ELSE field3 END ) as State 
    FROM tablename  

8单引号的处理

在包含单引号时应该使用两个单引号转义

dbo.sp_executesql @statement = N' select indexID,Sex=( case when Sex=0  then ''男'' else ''女'' end ) 
   from tablename ' 

9使用游标

 declare @temp table(TaskID int ,NodeId int)--声明临时表 
 insert into @temp select A.TaskID ,A.NodeId  from tableA A,tableB B 
 where  A.TaskID=B.TaskID--//--给声明的临时表中插入记录  
 DECLARE tnames_cursor CURSOR  LOCAL FORWARD_ONLY READ_ONLY--声明游标 
 FOR select TaskID,NodeId from @temp;--游标需要用到的列 
 open tnames_cursor--打开游标 
 DECLARE @TaskID int,@NodeId int;--声明变量 
 FETCH NEXT FROM tnames_cursor INTO @TaskID,@NodeId--移动游标给变量赋值,应该与游标需要用到的列一一对应 ，顺序类型应该一致 
 WHILE (@@FETCH_STATUS = 0)--循环 
 BEGIN 
      BEGIN     
        exec TaskType @TaskID,@NodeId--调用存储过程     
     END 
  FETCH NEXT FROM tnames_cursor INTO @TaskID,@NodeId ,@TaskRunCYCType--移动游标  给变量赋值 应该与游标需要用到的列一一对应 ，顺序类型应该一致 
 END 
  CLOSE tnames_cursor--关闭游标 
  DEALLOCATE tnames_cursor--释放游标

10合并更新和插入

在开发中大部分情况下，插入和更新传递的参数基本上是一样，那为什么不合并呢

create proc [dbo].[Save_TableName] 
( 
@field1 varchar(50), 
@field2     varchar(200) , 
@ID int, 
@insertOrUpdate --小于0插入 大于0更新 
) 
as 
begin 
   if(@insertOrUpdate<0) 
 begin 
     INSERT INTO   TableName (field1,field2,ID ) 
      values ( @field1  ,@field2,@ID);    
 end 
else 
     begin 
     UPDATE TableName  SET field1= @field1, field2 = @field2     
     where ID [email protected]; 
    end        
end

11定义函数

在没有满足要求的情况时，可以定义函数，但是使用自己定义的函数时需要加上架构名称

create FUNCTION [dbo].[CheckTime] 
( 
 @startTime datetime, 
 @endTime datetime--, 
) 
RETURNS  int 
AS 
BEGIN 
 DECLARE @result int 
    SET  @result=DATEDIFF(hour, @startTime, @endTime) -- 
if(@result=0)--小时相同 比较分钟 
begin 
     SET  @result=DATEDIFF(minute, @startTime, @endTime) 
        if(@result=0)--分钟相同比较秒 
          begin 
          SET  @result=DATEDIFF(second, @startTime, @endTime) 
          end     
end 
 RETURN @result 
END

调用此函数

dbo.CheckTime(@startTime1, @startTime2)--需要加上架构名称

12递归读取数据

在实际的应用中经常遇到树结构的表，但读取会比较麻烦，这里提够一个函数

/* 
函数 返回表，返回给定节点的所有子孙节点，而不仅仅是子节点 
*/ 
Create Function [dbo].[GetChildren](@NodeID Int) 
Returns @Tree Table (NodeID Int, NodeName Varchar(50), ParentID Int ) 
As 
Begin 
 Insert @Tree Select NodeID, NodeName, ParentID From Treetable Where ParentID = @NodeID 
 While @@Rowcount > 0 
  Insert @Tree Select A.NodeID, A.NodeName, A.ParentID 
  From Treetable A 
  Inner Join @Tree B 
  On A.ParentID = B.NodeID And A.NodeID Not In (Select NodeID From @Tree) 
 Return 
End

13通过默认值实现存储过程重载

存储过程可以使用默认值，估计都知道，但以此就可以实现类似函数重载的效果

例如，如下的存储过程由于使用了默认值，就可以不传递参数，传递一个参数，两个，三个，

CREATE PROCEDURE dbo.my_proc 
    @first int = NULL,  -- NULL default value 
    @second int = 2,    -- Default value of 2 
    @third int = 3      -- Default value of 3 
AS SELECT @first, @second, @third;

14在数据库中拼字符串，也可以用参数

  在实际的开发中，某些情况下在数据库中拼字符串不可避免，但又担心有特殊字符，导致拼出来的SQL有问题，其实数据库中拼字符串也可以使用参数，这就要用到dbo.sp_executesql ，这样就可以避免SQL注入和特殊字符导致的错误

如

DECLARE @IntVariable INT;--定义变量 
DECLARE @SQLString NVARCHAR(500);--存储拼出来的SQL 
DECLARE @ParmDefinition NVARCHAR(500);---存储拼出来的SQL中的参数 
/* Build the SQL string one time. */ 
SET @SQLString = 
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID'; 
/* Specify the parameter format one time. */ 
SET @ParmDefinition = [email protected] int';--赋值 
 
/* Execute the string with the first parameter value. */ 
SET @IntVariable = 275;--赋值 
EXECUTE dbo.sp_executesql  @SQLString, @ParmDefinition, 
                      @SalesID = @IntVariable; 
/* Execute the same string with the second parameter value. */ 
SET @IntVariable = 276; 
EXECUTE dbo.sp_executesql  @SQLString, @ParmDefinition, 
                      @SalesID = @IntVariable;

16触发器需要注意的问题

在使用触发器时有一个问题，或许很少有人注意到，一条更新语句一次更新了十条记录，会触发几次触发器？只有一次！！很奇怪，但却是事实，所以这需要注意，一不小心就会把好多数据漏了