sql知识点总结

mssql不支持的内置函数：
SELECT INET_ATON('99.1.168.192');#1661053120
SELECT INET_NTOA(1661053120);#99.1.168.192

SELECT INET_ATON('98.101.168.192');#1650829504
SELECT INET_NTOA(1650829504);#98.101.168.192
119.3.251.107改为192.168.101.98

UPDATE room_info SET RoomTeltComIp=1650829504,RoomUnicomIp=1650829504,RoomMobileIp=1650829504,OnlyReadDbIp=1650829504

--网狐网络地址@int32=b1.b2.b3.b4
--DECLARE @int32 int=0x00697a64--6912612
DECLARE @int32 int=0x0100007f--16777343
DECLARE @b1 tinyINT=@int32%256
DECLARE @b2 tinyINT=(@int32%65536)/256
DECLARE @b3 tinyINT=(@int32/65536)%256
DECLARE @b4 tinyINT=(@int32/65536)/256
select @int32 as int32,master.dbo.fn_varbintohexstr(@int32),@b1 as b1,@b2 as b2,@b3 as b3,@b4 as b4

时间戳显示出来
,DATEADD(S,AddTime,'1970-01-01 08:00:00'),[AddTime]

用charindex代替like：
charindex(子串,目标字段)>0

--十进制转十六进制
SELECT master.dbo.fn_varbintohexstr(16777216)--0x01000000
SELECT master.dbo.fn_varbintohexsubstring(1,16777216,1,0)
select 0x01000000

--按位存储
declare @dwFlag bigint=0x1000000000000003
if (@dwFlag & 1)>0 begin
print 1
end
if (@dwFlag & 2)>0 begin
print 2
end
if (@dwFlag & 4)>0 begin
print 3
end
if (@dwFlag & 0x100000000000000)>0 begin
print 29
end
if (@dwFlag & 0x1000000000000000)>0 begin
print 33
end

调时间测试后，可以运行如下语句进行数据库作业的下次执行时间的回复

-- 关闭 SQL Server 代理服务
exec xp_cmdshell 'net stop SQLSERVERAGENT'

-- 刷新数据库所有作业
USE msdb;
UPDATE dbo.sysjobschedules
SET next_run_date = 0
WHERE job_id IN (SELECT job_id FROM dbo.sysjobs);

-- 启动 SQL Server 代理服务
exec xp_cmdshell 'net start SQLSERVERAGENT'

--select * from paimaitable
select convert(varchar(10),getdate(),20)--2024-09-13
select convert(varchar(10),getdate(),120)--2024-09-13
declare @time datetime
set @time=cast(convert(varchar(10),getdate(),120) as datetime)
select @time as time--2024-09-13 00:00:00.000
set @time=dateadd(second,1,@time)
select @time as time--2024-09-13 00:00:01.000