常用sql 语句

死锁查询过程

use master
go
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR 
select  0 ,blocked
from (select * from sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from sysprocesses where  blocked>0 ) b  where a.blocked=spid)
union
select spid,blocked from sysprocesses where  blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0 
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
=============================================
--创建链接服务器
exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','10.1.1.22'

--带端口的连接

-- exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','10.1.1.22,5600'

exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'shise','06_Shi$kbZxsE'
go
 
--查询示例
select * from srv_lnk.数据库名.dbo.表名
 
--导入示例
select * into 表 from srv_lnk.数据库名.dbo.表名
 
go
--以后不再使用时删除链接服务器
exec sp_dropserver 'srv_lnk','droplogins'
==============================================
print 'next month:'
print dateadd(month,datediff(month,0,getdate())+1,0)
print 'previous month:'
print dateadd(month,datediff(month,0,getdate())-1,0)
print 'local month tom:'
print dateadd(ms,-3,dateadd(month,datediff(month,0,getdate())+1,0))
print 'local month up:'
print dateadd(month,datediff(month,0,getdate()),0)
print 'Today: from'
print dateadd(day,datediff(day,0,getdate()),0)
print 'to:'
print dateadd(ms,-3,dateadd(day,datediff(day,0,getdate())+1,0))
===============================================

SqlServer之Convert 函数应用


Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

===============================================

sql查询
select * from OPENDATASOURCE('SQLOLEDB','Data Source=10.1.1.22;User ID=shise;Password=06_Shi$kbZxsE').shise.dbo.vipcustomer

excel查询

SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="e:/test.xls";User ID=;Password=;Extended properties=Excel 5.0')...[sheet1$]

access查询

SELECT * FROM OpenDataSource ('Microsoft.Jet.OLEDB.4.0','Data Source="e:/test.mdb";User ID=;Password=')...tuser

 

EXEC master..xp_cmdshell 'bcp test.dbo.tusers out e:/Temp3.txt -c

-q -S"192.168.1.135" -U"sa" -P"hujie"' --参数：S 是SQL服务器名；U是用户；P是密码说明： 可以是各种格式文件，txt,xls等

SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Text;HDR =Yes;DATABASE=e:/')...[test#txt]

 

批量插入从文本

BULK INSERT tusers
FROM 'e:/test.txt'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '/n'
)
==================================================================
事务

BEGIN TRANSACTION
.
if @@error<>0
 ROLLBACK TRANSACTION
else
 COMMIT TRANSACTION
==================================================================
select count(distinct mobil) from sms_mo
==================================================================

列出表结构
SELECT
 表名=case when a.colorder=1 then d.name else '' end,
 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
 序号=a.colorder,
 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
 SELECT name FROM sysindexes WHERE indid in(
 SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
 ))) then '√' else '' end,
 字段名=a.name,
 类型=b.name,
 占用字节=a.length,
 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
 小数位=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
 允许空值=case when a.isnullable=1 then '√'else '' end,
 默认值=isnull(e.text,''),
 字段说明=isnull(g.[value],'')
  
 --into Email_tablestru 
 FROM syscolumns a
 left join systypes b on a.xusertype=b.xusertype
 inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
 left join syscomments e on a.cdefault=e.id
 left join sysproperties g on a.id=g.id and a.colid=g.smallid 
 left join sysproperties f on d.id=f.id and f.smallid=0
 --where d.name=@tablename    --如果只查询指定表,加上此条件
 where d.name like 'Email%'
 order by d.name,a.id,a.colorder

-------------------------------------------------------

print 'next month:'
print dateadd(month,datediff(month,0,getdate())+1,0)
print 'previous month:'
print dateadd(month,datediff(month,0,getdate())-1,0)
print 'local month tom:'
print dateadd(ms,-3,dateadd(month,datediff(month,0,getdate())+1,0))
print 'local month up:'
print dateadd(month,datediff(month,0,getdate()),0)
print 'Today: from'
print dateadd(day,datediff(day,0,getdate()),0)
print 'to:'
print dateadd(ms,-3,dateadd(day,datediff(day,0,getdate())+1,0))
print convert(varchar(20),getdate(),112)
print dateadd(day,datediff(day,0,getdate())+1,0)

--------------------------------------------------

declare @LastName varchar(20)

declare @FirstName varchar(20)

DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor into @LastName,@FirstName
WHILE @@FETCH_STATUS = 0
BEGIN

    pint @LastName
   FETCH NEXT FROM Employee_Cursor into @LastName,@FirstName
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor