常用的SQL数据库语句总结

时间：2023-03-09 08:08:29
常用的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条记录的情况

不妨试试如下代码

4生成随机数

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

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

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

6在数据库中处理异常

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

7查询中的条件判断

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

8单引号的处理

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

explain what does that mean? i do not agree ! just use N'男'

9使用游标

2015-11-18 go on tomorrow

'i use En because my input method has something wrong , i will try to restart my PC'

10合并更新和插入

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

  1. create proc [dbo].[Save_TableName]
  2. (
  3. @field1 varchar(50),
  4. @field2     varchar(200) ,
  5. @ID int,
  6. @insertOrUpdate --小于0插入 大于0更新
  7. )
  8. as
  9. begin
  10. if(@insertOrUpdate<0)
  11. begin
  12. INSERT INTO   TableName (field1,field2,ID )
  13. values ( @field1  ,@field2,@ID);
  14. end
  15. else
  16. begin
  17. UPDATE TableName  SET field1= @field1, field2 = @field2
  18. where ID =@ID;
  19. end
  20. end

11定义函数

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

  1. create FUNCTION [dbo].[CheckTime]
  2. (
  3. @startTime datetime,
  4. @endTime datetime--,
  5. )
  6. RETURNS  int
  7. AS
  8. BEGIN
  9. DECLARE @result int
  10. SET  @result=DATEDIFF(hour, @startTime, @endTime) --
  11. if(@result=0)--小时相同 比较分钟
  12. begin
  13. SET  @result=DATEDIFF(minute, @startTime, @endTime)
  14. if(@result=0)--分钟相同比较秒
  15. begin
  16. SET  @result=DATEDIFF(second, @startTime, @endTime)
  17. end
  18. end
  19. RETURN @result
  20. END

调用此函数

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

12递归读取数据

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

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

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

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

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

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

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

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

如

  1. DECLARE @IntVariable INT;--定义变量
  2. DECLARE @SQLString NVARCHAR(500);--存储拼出来的SQL
  3. DECLARE @ParmDefinition NVARCHAR(500);---存储拼出来的SQL中的参数
  4. /* Build the SQL string one time. */
  5. SET @SQLString =
  6. N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
  7. /* Specify the parameter format one time. */
  8. SET @ParmDefinition = N'@SalesID int';--赋值
  9. /* Execute the string with the first parameter value. */
  10. SET @IntVariable = 275;--赋值
  11. EXECUTE dbo.sp_executesql  @SQLString, @ParmDefinition,
  12. @SalesID = @IntVariable;
  13. /* Execute the same string with the second parameter value. */
  14. SET @IntVariable = 276;
  15. EXECUTE dbo.sp_executesql  @SQLString, @ParmDefinition,
  16. @SalesID = @IntVariable;

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

相关文章