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()*(@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 [dbo].[sp_UpdateFunctionTree]

@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=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 =@ID ;

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 = 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触发器需要注意的问题

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

第二篇：SQL基础语句总结

一. 四种基本的SQL语句

1. 查询

select * from table

2. 更新

update table set field=value

3. 插入

insert [into] table (field) values(value)

4. 删除

delete [from] table

二.语句的执行顺序

1.语法分析

分析语句中语法是否符合规范，衡量语句中各表达式的意义。

2.语义分析

检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。

3.选择优化器

不同的数据库有不同的算法（这个涉及到数据结构），数据库会根据自己的理解（数据库本身）为 SQL语句选择不同的优化器，不同的优化器会选择不同的“执行计划”

4.运行“执行计划”

根据“执行计划”执行SQL语句。

以上所述是数据执行时的大体路线。

5.select 语句的执行顺序

借用ItZik Ben-Gan、Lubor Kollar、Dejan Sarka所著的《Sql Server 20## 技术内幕：T-SQL查询》的一段话足以说明：

(8) select (9) distinct (11) <top_specification > <select_list>

(1)from<lef t_table>

(3) <join_type> join <right_table>

(2) on <join _condition>

(4) where <where_condition>

(5)group by <group_by_list>

(6) with {cube|rollup}

(7)having(having_condition)

(10) order by <order_by_condition>

从这个顺序可以看出，所有的查询语句都是从from开始执行的。在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的基础。

第一步：from

首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表vt1 .

第二步：on

接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2 .

第三步：join

如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3.

第四步：多表

如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3.

第五步：where

应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4，在这有个比较重要的细节不得不说一下，对于包含outer join子句的查询，就有一个让人感到困惑的问题，到底在on筛选器还是用where筛选器指定逻辑表达式呢？on和where的最大区别在于，如果在on应用逻辑表达式那么在第三步outer join中还可以把移除的行再次添加回来，而where的移除的最终的。

第六步：group by

分组，生成虚拟表 vt4

第七步：having

         对vt4应用having筛选器，生成虚拟表 vt5

第八步：select

处理select 列表，生成虚拟表vt6

第九步：distinct

将vt6 中重复的行去掉，生成虚拟表vt7

第十步：order   by

将vt7中的行按order by 子句中的列列表排序，生成一个游标vc8

第十一步： top

从vc8的开始处选择指定数量或比例的行，生成虚拟表vt9,并返回给调用者

三. SQL语句扩展

1.select

1.1 选择性插入语句

1.1.1 Insert into  table1 (field1 )  Select field2 from table2

要求table1必须存在。

1.1.2 select field1 into table1 from table2

   要求table1不存在，在运行时会自动创建表名为table1,字段名为field1的一个表。

1.2打开其它数据源

/* OracleSvr为链接服务器名 ，本示例假定已经创建了一个名为 ORCLDB 的 Oracle 数据库别名。*/

EXEC sp_addlinkedserver 'OracleSvr',  --链接服务器名OracleSvr，sysname类型

   'MSDAORA', --provider_name数据源提供程序，此处为oracle

   'ORCLDB' --数据源名称

GO

Select * from OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

如果有多个sql server实例：

SELECT *FROM  [servername\instancename.]pubs.dbo.authors.

注意：一个对象的完整名称包括四个标识符：服务器名称、数据库名称、所有者名称和对象名称。其格式如下：

[ [ [ server. ] [ database ] .] [ owner_name ] .] object_name

中间的名称可以省略，但是.不可以省略。如：server…object_name

2.update

2.1多表更新

Update table1 set  table1.field 1=table2.field2  from

table1，table2 /*猜测下连接方式全联接 FULL [OUTER] JOIN */

where  table1.field3= table2 .filed3

知识：SQL Server的update语句中from后可跟多个表，Oracle则不支持该用法

Oracle 中：Update  table1  set  table1.field1=

(select table2.field2 from table2 where .field3= table2 .filed3)

3.insert

3.1 插入语句的规范问题

在sql server 2000,sql server 2005中

标准语句：insert into  table(field)  values (value)

提示：在access中不正确，原因sql语句不规范，因此在书写sql语句的过程中一定要按正规的语法来写。

4.delete

4.1标准删除

标准语句：delete from table where condition

提示：同insert

4.2其它删除

4.2.1  truncate

语法：truncate table  table_name

删除表中所有行，不记录单个行删除操作，不记录日志,，所有速度比Delete快。

4.2.2  drop

语句： Drop table table_name

删除表及相关，有fk约束的不能删，先去年fk;系统表不能使用。

5.order by

功能：排序

技巧：order by newid() 随机排序

四. 动态SQL语句

4.1基本原则

4.1.1预编译问题

在EXECUTE执行之前，数据库不会编译 EXECUTE 语句内的语句，动态SQL语句就是放到存储过程中，它也不会预先编译。

4.1.2什么时候使用动态SQL语句

字段名，表名，数据库名作为变量时，必须用动态SQl语句

4.2.exec[ute]

4.2.1语法

exec (‘select * from table_name where name=’’’+@name+’’’’) --括号不能少

4.2.2传递参数

           --假设存储过程test_sp中需要一个参数：类型nvarchar(50) 名称 @parm

Declare @parms nvarchar(50)

Set @parms=’测试变量’

Exec test_sp  [@parm=]@parms –方括号内的可以省略

如果是批处理中的第一句，则可以省略Exec

4.2.3输出参数

declare @num int, @field int,

      @sqls nvarchar(4000)

Set @field=1

set @sqls='select @a=count(*) from table_name  where field=@field'

exec sp_executesql @sqls,N'@a int output,@field int',@num output ,@field

select @num

4.3.sp_executesql

语法：exec[ute] sp_executesql N’select * from table_name where field=@field’,N’@field int’,@field=1

使用sp_exexutesql比使用exec更有效率.