SQL数据库编程学习应用语句大全
1as 的用处
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条记录的情况
不妨试试如下代码
[sql] view plaincopy
/*
按CurrentNodeLevel列分组，每组按NodeID排序，取出每个组中的前3个元素*/
declare @temp table (NodeIDint, CurrentNodeLevelint ,rowNumberint)--定义临时表insert into @temp
selectNodeID,CurrentNodeLevel,ROW_NUMBER()
OVER (partitionby CurrentNodeLevel
order by NodeID ) as rowNumber
from SchoolTerminalStruct---给临时表中插入数据
select * from @temp where rowNumber
4生成随机数
主意此方法只能写成存储过程，不能写成函数
[sql] view plaincopy
-----返回Max ，Min之间的随机数不能写成函数
createproc [dbo].[getRAND]
(
@Max int,--最大值
@Min int--,--最小值
)
AS
BEGIN
DECLARE @result int
SELECT @result=RAND()*(@Max-@Min)+@Min
select@result
END
5把满足一定条件的数据用逗号分隔
这可能也是一个很常用的语句了，经常出现在一对多的关系中对外展示，要求把子表中的数据取出来用逗号或者其他符号分隔开
[sql] view plaincopy
/*
把满足 t2.NodeID=t1.NodeID的tableA 的字段NodeName 以逗号分隔开合并为一个字段输出
*/
select *,stuff((select \'，\' + t1.NodeName from tableA t1,tableB t2
wheret2.NodeID=t1.NodeID
for xml path(\'\')) , 1 , 1 , \'\')as text
fromtableA
6在数据库中处理异常
通过个参数附加output标志来输出参数，通过TRY，CATCH捕捉异常
[sql] view plaincopy
CREATE PROCEDURE [dbo].[sp_UpdateFunctionTree]
@nodenameint,
@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 完成
[sql] view plaincopy
/*
在State=0 时返回field1 ,在State=1时返回 field2 ,其他时返回field3
*/
SELECT (CASE WHENState=0 THEN field1 WHENState=1 THEN field2
ELSE field3 END ) as State
FROM tablename
8单引号的处理
在包含单引号时应该使用两个单引号转义
[sql] view plaincopy
dbo.sp_executesql @statement = N\' select indexID,Sex=( case when Sex=0then \'\'男\'\' else \'\'女\'\' end )
fromtablename \'
9使用游标
[sql] view plaincopy
declare @temp table(TaskIDint ,NodeIdint)--声明临时表
insert into @temp select A.TaskID ,A.NodeIdfrom tableAA,tableB B
whereA.TaskID=B.TaskID--//--给声明的临时表中插入记录
DECLARE tnames_cursor CURSORLOCAL FORWARD_ONLY READ_ONLY--声明游标
FOR select TaskID,NodeId from @temp;--游标需要用到的列
open tnames_cursor--打开游标
DECLARE @TaskIDint,@NodeIdint;--声明变量
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合并更新和插入
在开发中大部分情况下，插入和更新传递的参数基本上是一样，那为什么不合并呢
[sql] view plaincopy
createproc [dbo].[Save_TableName]
(
@field1 varchar(50),
@field2varchar(200) ,
@ID int,
@insertOrUpdate --小于0插入 大于0更新
)
as
begin
if(@insertOrUpdate
begin
INSERT INTOTableName (field1,field2,ID )
values ( @field1,@field2,@ID);
end
else
begin
UPDATE TableNameSET field1= @field1, field2 = @field2
where ID =@ID;
end
end
11定义函数
在没有满足要求的情况时，可以定义函数，但是使用自己定义的函数时需要加上架构名称
[sql] view plaincopy
create FUNCTION [dbo].[CheckTime]
(
@startTimedatetime,
@endTimedatetime--,
)
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
调用此函数
[sql] view plaincopy
dbo.CheckTime(@startTime1, @startTime2)--需要加上架构名称
12递归读取数据
在实际的应用中经常遇到树结构的表，但读取会比较麻烦，这里提够一个函数
[sql] view plaincopy
/*
函数 返回表，返回给定节点的所有子孙节点，而不仅仅是子节点
*/
Create Function [dbo].[GetChildren](@NodeIDInt)
Returns @Tree Table (NodeIDInt, NodeNameVarchar(50), ParentIDInt )
As
Begin
Insert @Tree Select NodeID, NodeName, ParentIDFromTreetable 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.NodeIDAndA.NodeID Not In (Select NodeID From @Tree)Return
End
13通过默认值实现存储过程重载
存储过程可以使用默认值，估计都知道，但以此就可以实现类似函数重载的效果
例如，如下的存储过程由于使用了默认值，就可以不传递参数，传递一个参数，两个，三个，
[sql] view plaincopy
CREATE PROCEDURE dbo.my_proc
@firstint = NULL,-- NULL default value
@secondint = 2,-- Default value of 2
@thirdint = 3-- Default value of 3
AS SELECT @first, @second, @third;
14在数据库中拼字符串，也可以用参数
在实际的开发中，某些情况下在数据库中拼字符串不可避免，但又担心有特殊字符，导致拼出来的SQL有问题，其实数据库中拼字符串也可以使用参数，这就要用到dbo.sp_executesql ，这样就可以避免SQL注入和特殊字符导致的错误
如
[sql] view plaincopy
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\'@SalesIDint\';--赋值
/* 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触发器需要注意的问题
在使用触发器时有一个问题，或许很少有人注意到，一条更新语句一次更新了十条记录，会触发几次触发器？只有一次！！很奇怪，但却是事实，所以这需要注意，一不小心就会把好
多数据漏了
