--charindex比like的检索速度快
charindex('2',productid) 查询'2'在字段中所处的位置
select * from dbo.VIEW_9IB_DETAIL where speccolor like '%粉色%'
select * from dbo.VIEW_9IB_DETAIL where charindex('粉色',speccolor)>0
--CONVERT与CAST
convert 是专对SQL Server使用的,使日期与时间值,小数之间转换具有更宽的灵活性。
cast(字段 as 数据类型(长度))
convert(数据类型(长度),字段,106) -106表仅日期 108仅时间 (101-114)
---Replace
replace(字段名,'-','')
---恢复日期的完整格式
select CONVERT(nvarchar(10),CONVERT(datetime,'2010-6-9'),120)
select CONVERT(nvarchar(10),CONVERT(datetime,'2010-6-09'),120)
select CONVERT(nvarchar(10),CONVERT(datetime,'2010-06-9'),120)
select CONVERT(nvarchar(10),CONVERT(datetime,'2010-06-9'),111) --2011/04/06
convert(nvarchar(10), getdate(),112) --20110803
-----查询本月的第一天到今天的记录
substring(convert(char,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0),120),1,10)
and substring(convert(char,getdate(),120),1,10)
--上个月的第一天和最后一天
select cast(convert(varchar(7),dateadd(month,-1,getdate()),120) + '-01' as datetime),
dateadd(day,-1,convert(varchar(7),getdate(),120) + '-01' )
--下月的最后一天
select dateadd(month,datediff(month,-1,getdate())+1,-1) '下月最后一天'
--获得指定日期今天是星期几
select datepart(dw,'2011-04-12 12:23')-1
select datepart(ww,'2011-04-12 12:23'),datepart(dw,'2011-04-12 12:23')-1,dateadd(day,-1*7-datepart(dw,'2011-04-12 12:23')+1,'2011-04-13 12:23')
--添加数据
INSERT INTO [TaobaoMailPush].[dbo].[TBL_MERUMAGA_MAIL_ASSIGNMENT_DELETED]
([M_ID])
SELECT
TBL1.[M_ID]
FROM [TaobaoMailPush].[dbo].[TBL_MERUMAGA_MAIL_ASSIGNMENT] AS TBL1 LEFT JOIN dbo.TBL_MERUMAGA_CONTENT AS TBL2
ON TBL1.M_ID=TBL2.M_ID
WHERE TBL1.[M_ID] = @M_ID
------------查询数据库中的表
select * from sysobjects where xtype = 'U' and name like 'tb%'
---------------------like查询
select * from VIEW_COLOR_MASTER_FILE where color_cd like '20[0-9]'
select * from VIEW_COLOR_MASTER_FILE where color_cd like '20[0123456789]'
select * from VIEW_COLOR_MASTER_FILE where color_cd like '20[^56789]'
select * from VIEW_COLOR_MASTER_FILE where color_name_ka like N'蓝__'
-----------------------将相同记录加上记录号
select cMeascode,ROW_NUMBER() OVER(PARTITION BY cMeascode ORDER BY cMeascode) as 'aa' from
dbo.t_scadaTMeas
----------------------中日转换时
select STAFF_NAME from DVStaff.dbo.TBL_DV_STAFF_INFO where STAFF_ID=CREATOR_ID COLLATE database_default
-------------------------两个服务器之间的操作
exec sp_addlinkedserver'ITSV','','SQLOLEDB','192.168.9.75'
select * from ITSV.goodsup.dbo.DV_IMPORTEXPORT