Sql Server 手册

1.字符串操作函数

假设@a='abc789'

1.charindex 返回字符或者字符串在另一个字符串中的起始位置

charindex('b',@a)  --结果：2

2.substring 返回字符、二进制、文本或图像表达式的一部分（截取字符串）

substring(@a,1,len($a))  --substring(@a,start,length)

结果：abc789

3.left 返回从字符串左边开始指定个数的字符

left(@a,1) --结果：a

left(@a,3) --结果：abc

4.right 返回从字符串右边开始指定个数的字符

right(@a,1)  --结果：9

right(@a,3) --结果：789

right('000000'+cast(id as varchar),7)  --左侧六位自动补零

5. patindex 返回指定表达式中某模式第一次出现的起始位置

patindex('%[^0-9]%',@a) --结果：1

patindex('%[0-9]%',@a) --结果：4

6.stuff 删除指定长度的字符并在指定的起始点插入另一组字符

stuff('abc789', 2, 3, 'ijklmn') --stuff(@a,start,length,replacestr)

结果：aijklmn89

示例：

INSERTINTO table_1(name,classid) 
SELECT'苹果',1 UNIONALL 
SELECT'香蕉',1 UNIONALL 
SELECT'樱桃',1 UNIONALL
SELECT'可可(牛奶)'

select case charindex('(',[name]) when 0 then [name] else substring([name],0,charindex('(',[name])) end from table_1

查询结果：

...

樱桃

可可　　　　--被截取了

7.replace替换回车、换行、回车换行符

SELECT *, REPLACE(detail, CHAR(13) , '<br>')

SELECT *, REPLACE(detail, CHAR(10), '<br>')

SELECT *, REPLACE(detail, CHAR(13) + CHAR(10), '<br>') 

2.表变量是否为空

SELECT@tmpCount=COUNT(1) FROM @resultTemp
IF(@tmpCount>0)
BEGIN
        ......
END

3.sp_executesql 执行动态Sql，支持参数

DECLARE @strsql NVARCHAR(500)
SET @strsql='SELECT @total=SUM(a.total) FROM '+@DateName+'.dbo.table_1 a,TestDBA.dbo.Project b WHERE a.code=b.code'
EXEC sp_executesql @strsql,N'@total AS FLOAT OUTPUT',@alltotal OUTPUT

 4.多个字段中取最大值

select max(m)
from
(
    select max(aa)m from TableName
    union all
    select max(bb) from TableName
    union all
    select max(cc) from TableName
) t

 5.日期函数

 1.当前系统日期、时间

    select getdate()

 2.dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值

    例如：向日期加上2天

    select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000

 3.datediff 返回跨两个指定日期的日期和时间边界数。

    select datediff(day,'2004-09-01','2004-09-18') --返回：17

 4.datepart 返回代表指定日期的指定日期部分的整数。

    select datepart(month, '2004-10-15') --返回 10

 5.datename 返回代表指定日期的指定日期部分的字符串

    select datename(weekday, '2004-10-15') --返回：星期五

 6. day(), month(),year() --可以与datepart对照一下

 7.当前日期、当前时间   

    select 当前日期=convert(varchar(10),getdate(),120),当前时间=convert(varchar(8),getdate(),114)

 8.本年第几周、今天周几

    select 今天是周几=datename(dw,'2004-10-15')

    select 本年第多少周=datename(week,'2004-10-15'),今天是周几=datename(weekday,'2004-10-15')  

 9.短日期格式

    select  convert(varchar(10),getDate(),120)

    select  CONVERT(VARCHAR(10),getdate(),23)

      函数                                                参数/功能
  GetDate( )                                          返回系统目前的日期与时间
  DateDiff (interval,date1,date2)              以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
  DateAdd (interval,number,date)            以interval指定的方式，加上number之后的日期
  DatePart (interval,date)                        返回日期date中，interval指定部分所对应的整数值
  DateName (interval,date)                      返回日期date中，interval指定部分所对应的字符串名称

  参数 interval的设定值如下：

  值 缩 写（Sql Server）                           Access 和 ASP 说明
  年：Year             Yy         yyyy                  1753 ~ 9999
  季：Quarter        Qq         q                       1 ~ 4
  月：Month          Mm        m                      1 ~ 12
  一年的日数,一年中的第几日：Day of year        Dy          y            1-366
  日：Day             Dd         d                       1-31
  一周的日数,一周中的第几日：Weekday            Dw         w            1-7
  周，一年中的第几周：         Week                  Wk        ww          0 ~ 51
  时：Hour            Hh         h                       0 ~ 23
  分：Minute          Mi          n                       0 ~ 59
  秒：Second         Ss         s                       0 ~ 59
  毫秒：Millisecond  Ms         -                       0 ~ 999

  access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似

  一个月第一天
  SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

  本周的星期一

  SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

  一年的第一天

  现在用年(yy)的时间间隔来显示这一年的第一天。

  SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

  季度的第一天

  假如你要计算这个季度的第一天，这个例子告诉你该如何做。

  SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

  当天的半夜

  SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  上个月的最后一天

  它通过从一个月的最后一天这个例子上减去3毫秒来获得。有一点要记住，在Sql Server中时间是精确到3毫秒。这就是为什么我需要减去3毫秒来获得我要的日期和时间。

  SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

  计算出来的日期的时间部分包含了一个Sql Server可以记录的一天的最后时刻(“23:59:59:997”)的时间。

  去年的最后一天

  SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

  本月的最后一天

  SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

  本年的最后一天

  SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

  本月的第一个星期一

  select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

 6.金额加逗号 

 DECLARE @i MONEY
 SET @i=123456789.00
 SELECT CONVERT(varchar,@i,1)

 DECLARE @i VARCHAR(50)
 SET @i=123456789.00
 SELECT CONVERT(varchar,CONVERT(money,@i),1)

7、查库名、库ID

SELECT Name FROM Master..SysDatabases ORDER BY Name

8、判断是否存在表名、存储过程、函数

--判断库是否存在
if exists(select * from master..sysdatabases where name=N'库名')
drop database 库名
GO

-- 判断要创建的表名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[表名]
GO

--列是否存在
IF COL_LENGTH( '表名','列名') IS NULL
alter table 表名 drop constraint 默认值名称
go
alter table 表名 drop column 列名
go

--判断要创建临时表是否存在
If Object_Id('Tempdb.dbo.#Test') Is Not Null
drop table #Test
GO

-- 判断要创建的存储过程名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[存储过程名]
GO

-- 判断要创建的视图名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[视图名]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[视图名]
GO

-- 判断要创建的函数名是否存在
if exists (select * from sysobjects where xtype='fn' and name='函数名')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[函数名]
GO

 9.SQL2008配置管理工具服务显示远程过程调用失败

打开程序和功能，卸载了一个叫"Microsoft SQL Server 2012LocalDB"，重新打开SQL配置管理器，发现可以了。

10.还原数据库的时候，提示"因为数据库正在使用，所以无法获得对数据库的独占访问权"

1,设置数据库在单用户模式下工作。
设置方法：在需要还原的数据库上右击，在右键菜单命令上选择"属性"- >"选项"- >"状态"- >"限制访问"- >"Single"。这是SQLSERVER2005的菜单命令，其它版本请自己查找。

2,利用SQL语句，杀死正在使用该数据库的所有进程，自己以前在做一个SQL SERVER操作小工具的时候有写过该功能的SQL，贴出来供大家参考：
复制代码 代码如下:
declare @dbname varchar(50)
set @dbname='数据库名称'
declare @sql varchar(50)
declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses where db_name(dbid)=@dbname
open cs_result
fetch next from cs_result into @sql
while @@fetch_status=0
begin
execute(@sql)
fetch next from cs_result into @sql
end
close cs_result
deallocate cs_result
该SQL语句利用游标循环所有正在使用该数据库的进程，并通过kill命令杀死进程。

3,利用SQL语句,断开所有用户链接，并回滚所有事务，具体SQL语句如下：
复制代码 代码如下:
ALTER DATABASE [数据库名称]
SET OFFLINE WITH ROLLBACK IMMEDIATE

注意:在使用方法2与3时，不要在需要的还原的数据库下执行，建议在master数据库下面执行。

11、不允许保存更改，阻止保存要求重新创建表的更改

在SQL Server Management Studio点击菜单【工具】 - 【选项】，
打开【选项】对话框，展开【Designers】，取消【阻止保存要求重新创建表的更改】复选框

12、配置SQL代理作业，执行存储过程调用链接服务器，提示“ 对远程服务器的访问遭拒绝，因为不存在登录映射。”

在链接服务器，右键“属性” - “安全性” - 增加用户"NT AUTHORITY\NETWORK SERVICE" - 输入链接服务器的sa账号和密码

13、查询所有的表、视图、触发器、存储过程

select * from sysobjects where xtype='TR' order by name --所有触发器
select * from sysobjects where xtype='P' order by name --所有存储过程
select * from sysobjects where xtype='V' order by name --所有视图
select * from sysobjects where xtype='U' order by name --所有表