常用的sql语句

1.插入一条数据后，获取这条数据的自增ID值

 string sql = @"INSERT INTO dbo.D_EquipmenetErrorInfo (StartTime, EndTime, DepartmentLineId, EquipmentId,State,isUp)
                            VALUES (getdate(),getdate(), {0},{1},0,2);select @@IDENTITY";

2.查询数据库中一张表的总行数

  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL4')        --1937984220 
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL3')        --89825045   
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL2')        --806093     
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL1')        --0
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL4NOTREF')  --10427288    
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACEREF')          --2027743030  
  select top 1 rowcnt from sysindexes where id=object_id('D_ORDERTRACEREF')     --2133150522  

3.从一个数据库中将数据实时导入到另一个数据库中的存储过程

CREATE PROC [dbo].[pro_UpdateDownData_errorData]
AS
BEGIN
  --将D_PortlCoutInfo表状态为0的数据上传
 if (SELECT Count(ID) FROM dbo.D_EquipmenetErrorInfo where isUp=0)=0
 BEGIN
SELECT '0' AS result
RETURN
 END
 
  CREATE TABLE #errordata
  (
  ID  BIGINT
  )
  INSERT INTO #errordata
  (
  ID
  )
  SELECT ID FROM dbo.D_EquipmenetErrorInfo where isUp=0 
  
--查询数据并插入
INSERT  INTO [192.168.0.7].InformationSystem.dbo.D_EquipmenetErrorInfo
(OldId,StartTime,EndTime,DepartmentLineId,EquipmentId,State,isUp,ErrorRemark)
(
SELECT Id,StartTime,EndTime,DepartmentLineId,EquipmentId,State,isUp,ErrorRemark  
FROM  InformationSystem.dbo.D_EquipmenetErrorInfo a 
WHERE isUp=0
)

BEGIN TRY
BEGIN TRANSACTION
--标记工业网数据表状态
UPDATE dbo.D_EquipmenetErrorInfo SET  isUp = 1 
WHERE  ID IN (SELECT ID FROM #errordata)


DROP TABLE #errordata


SELECT '1' AS result,'上传成功' AS msg
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT '-1' AS result,'上传失败' AS msg
ROLLBACK TRANSACTION
END CATCH

END

4.批量插入数据库中某张表多条数据存储过程

ALTER PROCEDURE [dbo].[AddPortlCoutInfo]
(
@xml varchar(max) = ''-- 入参XML
)
AS
SET NOCOUNT ON 
BEGIN TRY
DECLARE @paraXml XML;
BEGIN TRANSACTION --开始事务
SET @paraXml = CONVERT(XML, @xml);

INSERT INTO D_PortlCoutInfo(PortId,Count,Added,Note)
SELECT T.C.value('@PortId' , 'varchar(50)'),
T.C.value('@Count' , 'int'),
T.C.value('@Added' , 'int'),
T.C.value('@Note' , 'varchar(200)')
FROM @paraXml.nodes('//PortInfo') as T(C)

COMMIT TRANSACTION --提交事务
SET NOCOUNT OFF
RETURN 0
    END TRY
    BEGIN CATCH
   /**//*-------- 事务回滚 --------*/
   ROLLBACK TRANSACTION
   SET NOCOUNT OFF 
   RETURN   -1  
    END CATCH
    
    
--XML数据格式
--<?xml version="1.0"?>
--<Info>
--  <PortInfo PortId= "1"  Count="1" Added="1" Note="Note值" />
-- <PortInfo PortId= "2"  Count="2" Added="2" Note="Note值" />
--</Info>'

5.批量修改数据库中的多条数据的sql语句

UPDATE dbo.D_StateTimeConfig SET ErrorTime = CASE EquimentId 
WHEN 1 THEN 0 WHEN 2 THEN {0} WHEN 3 THEN {1} WHEN 4 THEN {2}
WHEN 5 THEN {3} WHEN 6 THEN {4} WHEN 7 THEN {5}  WHEN 8 THEN {6}
WHEN 9 THEN 0 WHEN 10 THEN {7} WHEN 11 THEN 0  WHEN 12 THEN {8}
WHEN 13 THEN 0 WHEN 14 THEN {9}
END

6.数据库执行一条数据的执行时间

set statistics profile on 
set statistics io on
set statistics time on 
go


《sql语句》


go


set statistics profile off 
set statistics io off
set statistics time off

7.数据分页查询存储过程

USE [SmartPlatform_DEBUG]
GO
/****** Object:  StoredProcedure [dbo].[GetRecordByPage]    Script Date: 08/03/2015 12:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[GetRecordByPage]
@PageSize int, -- 每页的记录条数
@PageNumber int, --当前页
@QuerySql varchar(max),--查询字符串
@KeyField varchar(500), -- 排序的字段
@Sort varchar(20)='asc',-- 顺序还是逆序
@total int output       -- 数据的总行数
AS
BEGIN

declare @sqlText as varchar(max) 
declare @sqlcount as nvarchar(max)
declare @i int

set @sqlText = 'select * from(
              select row_number() over(order by '+@KeyField+''+@Sort+') rowno,* from('+@QuerySql+') t
              ) a
              where rowno between '+RTRIM((@PageNumber-1)*@PageSize+1)+' and '+RTRIM(@PageNumber*@PageSize)

set @sqlcount = N'select @ct=count(*) from('+@QuerySql+') t'

exec sp_executesql @sqlcount,N'@ct int output',@ct=@i output

set @total=@i

exec(@sqltext)

END

8.一次插入多条数据的sql

insert [dbo].[frm_Role](RoleName)
  select 'aa' union all
  select 'bbb'

9. 对时间的操作

  时间的加 和减

 and DATEADD(mi, 30, tm.StartTme) >='" + startTime + "' and DATEADD(mi, -30, tm.EndTime)<='" + endTime 

时间差的计数：

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

DateDiff 函数用于判断在两个日期之间存在的指定时间间隔的数目。例如可以使用 DateDiff 计算两个日期相差的天数，或者当天到当年最后一天之间的星期数。

要计算 date1 和 date2 相差的天数，可以使用“一年的日数”（“y”）或“日”（“d”）。当 interval 为“一周的日数”（“w”）时，DateDiff 返回两个日期之间的星期数。如果 date1 是星期一，则 DateDiff 计算到 date2 之前星期一的数目。此结果包含 date2 而不包含 date1。如果 interval 是“周”（“ww”），则 DateDiff 函数返回日历表中两个日期之间的星期数。函数计算 date1 和 date2 之间星期日的数目。如果 date2 是星期日，DateDiff 将计算 date2，但即使 date1 是星期日，也不会计算 date1。

如果 date1 晚于 date2，则 DateDiff 函数返回负数。

firstdayofweek 参数会对使用“w”和“ww”间隔符号的计算产生影响。

如果 date1 或 date2 是日期文字，则指定的年度会成为日期的固定部分。但是如果 date1 或 date2 被包括在引号 (" ") 中并且省略年份，则在代码中每次计算 date1 或 date2 表达式时，将插入当前年份。这样就可以编写适用于不同年份的程序代码。

在 interval 为“年”（“yyyy”）时，比较 12 月 31 日和来年的 1 月 1 日，虽然实际上只相差一天，DateDiff 返回 1 表示相差一个年份。

10, 获取数据库中所有表的所有字段的信息

select d.name 'tb-name',
      isnull(f.value,d.name) as 'tb_descript',
      a.colorder 'column_order',
 a.name 'colum_name',
 (case
     when (select Count(*)
 from sysobjects 
 where (name in (select name
 from sysindexes
 where (id = a.id)
 and (indid in (select indid
 from sysindexkeys
 where  (id = a.id)
 and (colid in (select colid syscolumns
                 where (id =a.id)
 and (name = a.name)) )) )) )
 
              and (xtype='px'))>0 then 1
else 0
end) 'is_primary_key',
a.xtype as 'c_type',
columnproperty(a.id,a.name,'PRECISION') as 'length',
a.isnullable 'is_nullable',
isnull(g.[value],a.name)  as N'column_descript'
from syscolumns a
     left join systypes b on a.xtype = b.xusertype
inner join sysobjects d 
      on a.id=d.id 
  and(d.xtype='U' or d.xtype='V')
  and d.name <>'dtproperties'
  and d.name <>'sysdiagrams'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id
left join sys.extended_properties f on D.id = F.major_id and f.minor_id=0
   

11.  查看数据库的链接信息

sp_who2