ehxz 发表于 2011-7-7 15:37:23

常用的SQL数据库语句总结 .

1、as 的用处   

as可以对表和列取别名

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

例如原来的 select field1 from tableA,改为selectfield2 asfield1 from tableA

代码基本就可以不动了。

2、自增长的字段的插入

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

需要临时去掉,可以使用如下语句set IDENTITY_INSERTtablename on ,在处理完成后在使用如下语句恢复 set IDENTITY_INSERTtablenameoff

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 .

(
@Max int,--最大值
@Min int--,--最小值
)
AS
BEGIN   
   DECLARE @result int
      SELECT @result=RAND()*(@Max-@Min)+@Min   
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 .
    @nodename int,
    @Result int output---2,操作失败;0操作成功
AS
BEGIN            
begin
    BEGIN TRY
   UPDATE FunctionTree SET NodeName=@nodename
      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=0then ''男'' else ''女'' end )
   from tablename '

9、使用游标
declare @temp table(TaskID int ,NodeId int)--声明临时表
insert into @temp select A.TaskID ,A.NodeIdfrom tableA A,tableB B
whereA.TaskID=B.TaskID--//--给声明的临时表中插入记录
DECLARE tnames_cursor CURSORLOCAL 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 .
(
@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 TableNameSET field1= @field1, field2 = @field2   
   where ID =@ID ;
    end      
end

11、定义函数

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

create FUNCTION .
(
@startTime datetime,
@endTime datetime--,
)
RETURNSint
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 .(@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 = N'@SalesID 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、触发器需要注意的问题
在使用触发器时有一个问题,或许很少有人注意到,一条更新语句一次更新了十条记录,会触发几次触发器?只有一次!!很奇怪,但却是事实,所以这需要注意,一不小心就会把好多数据漏了

solomon 发表于 2011-7-11 08:34:40

很好,收藏了!{:lh_05:}

fuxiaoyang13 发表于 2011-7-18 15:38:52

收藏收藏!!!

aixiaomaomi 发表于 2013-4-2 22:34:47

好东西啊。 收了,谢谢楼主了

superolder 发表于 2015-3-16 14:30:21

這篇sql的教學還真的不錯用~

sybasekandari 发表于 2016-5-19 23:18:53

总结相当实在到位。必须拿来捧读:loveliness:
页: [1]
查看完整版本: 常用的SQL数据库语句总结 .

免责声明:
本站所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。

Mail To:Admin@SybaseBbs.com