Sql Server 笔记

//SQL Server 分页查询
1 SELECT ID,Ttile,PageURL,ParamsGet,ParamsPost FROM ExternalPV_202111 WHERE ResultCode=0 ORDER BY ID  OFFSET Page*PageSize ROW FETCH NEXT PageSize ROW ONLY 

　　Page: 页码,第几页

　　PageSize: 每页显示多少行

　　OFFSET/FETCH NEXT 只支持SQL2012及以上版本

 

//批量插入

SELECT vale1, value2 into Table2 from Table1   //Table2表不存在

Insert into Table2(field1,field2,...) select value1,value2,... from Table1;//Table2表已存在

//联表更新 
update #table1 set #table1.name = b.name, from #table2 b where #table1.id = b.id and #table1.name is null;

//更新字段用“,”号分隔
update tableNameA set columnName=stuff((select ',' + me.columnName from tableNameB(nolock) me where tableNameA.columnName1=me.columnName1 for xml path('')), 1, 1, '')

//修改字段长度
ALTER TABLE  tableName  COLUMN columnName nvarchar(max);

 

时间格式化：

SELECT CONVERT(NVARCHAR, GETDATE(), 23) //2022-08-31

SELECT CONVERT(NVARCHAR, GETDATE(), 112) //20220831

SELECT CONVERT(NVARCHAR, GETDATE(), 102) //2022.08.31

SELECT CONVERT(NVARCHAR, GETDATE(), 20) //2022-08-31 11:40:00

SELECT CONVERT(NVARCHAR, GETDATE(), 121) //2022-08-31 11:40:44.293 毫秒