sql server

1.处理时间 yyyy-MM-dd

select format(SYSDATETIME(),'yyyy-MM-dd')

2.获取入职10天的所有用户

 select * from sys_user where DATEDIFF(day,onboardTime,GETDATE())=10

 3.声明 调用的函数

--  使用的过程中，因为nvarchar 书写错误，没带（50）返回值一直不对，要注意参数类型

create function [dbo].[func_userAction_nums](@userCode nvarchar(50), @contentId nvarchar(36)) -- 参数
      --该视频的标签当前用户 visitNums 最大值
      returns int                                              --返回值类型
      as
      begin
            declare @nums int                                      --声明变量
            select @nums = t.visitNum from (                     --赋值
                  select top 1 st.F_ContentID,
                                     st.Tag,
                                     stv.visitNum
                       from Story_Tag st 
                       left join (select Tag, COUNT(1) as visitNum from dbo.Story_UserAction where UserCode=@userCode group by Tag) stv on stv.Tag=st.Tag
                    where stv.visitNum is not null and st.F_ContentID=@contentId order by isnull(stv.visitNum,0) desc
                  ) t
            if(@nums is null)
                  set @nums=0                                     -- 判断 重新赋值
            return @nums
      end
GO

 4.获取三个月后的时间

 select CONVERT(NVARCHAR(50),DATEADD(MONTH,3,GETDATE()),23)

 5.第5天后开始的课程

datediff(day, dateadd(day,5,getdate()), ts.StartDate)=0 

 6.遍历

-- 方法1：游标
-- 声明变量
DECLARE
    @empid AS INT,
    @firstname AS NVARCHAR(10),
    @lastname AS NVARCHAR(20);
    
-- 声明游标
DECLARE C_Employees CURSOR FAST_FORWARD FOR SELECT empid,firstname,lastname FROM HR.Employees;
    
OPEN C_Employees;

-- 取第一条记录
FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname;

WHILE @@FETCH_STATUS=0
BEGIN
    -- 操作
    UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;
    
    -- 取下一条记录
    FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname;
END

-- 关闭游标
CLOSE C_Employees;

-- 释放游标
DEALLOCATE C_Employees;

 7.导入时，先更新。若更新条数为0，表明没有，插入一条数据

update Sys_User set UserName=N'小明', UserCode=N'aaa', Password=N'123', Email = N'xxx.163.com', UpdateDate=getdate() where UserCode = N'aaa' 
IF @@ROWCOUNT=0 BEGIN INSERT INTO Sys_User(UserID, UserName, UserCode, Password, Email, CreateDate, UpdateDate) VALUES(NEWID(),N'小明',N'aaa', N'123', N'xxx.163.com', getdate(),  getdate()) END; 

 8.新增时，用户编码和邮箱必须唯一。单一条件可重复

insert into Sys_User(UserID, UserName, UserCode, Email, CreateDate)
select newid(), '"+ user.UserName +"', '"+ user.UserCode +"', '"+ user.Email +"', getdate() where not exists (select 1 from Sys_User where UserCode='"+ user.UserCode +"' and Email='"+ user.Email +"')