个人SQL备忘录

SQL

一、Sql Server

1. 查看SQL server版本

SELECT SERVERPROPERTY('productversion') as '产品版本', SERVERPROPERTY ('productlevel') as '产品级别' , SERVERPROPERTY ('edition') as '版本'

2.除数为0时解决方案

https://blog.csdn.net/tlammon/article/details/115469274

select isnull(a/nullif(b,0),0) from table2;
--当除数b为0时，除数变为null，则a/b也为null，再通过isnull函数将null转为0.

3.获取指定月份里的所有日期

DECLARE @V_DATE DATE = GETDATE();
WITH CTE_DATE AS (
        SELECT  DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE
        UNION ALL
        SELECT  DATEADD(dd,1,CDATE)
        FROM    CTE_DATE
        WHERE   DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))
    )
SELECT * FROM CTE_DATE

4.计算指定日期是当月的第几周

select cast(datepart(mm,'2019-05-31') as varchar(2)) + '月第'
 + cast((datepart(wk,'2019-05-31') - datepart(wk,convert(varchar(7),'2019-05-31',120) + '-01') + 1) as varchar(2)) + '周'

5.获取本年所有日期

DECLARE @year INT
SET @year = YEAR(GETDATE())

declare @Data_Table table(
	DataList date
)
insert into @Data_Table
SELECT CAST(DATEADD(DAY, sv.number, LTRIM(@year) + '-01-01') AS DATE) AS date_list
FROM [master].dbo.spt_values AS sv
WHERE sv.[type] = 'P'
AND DATEADD(DAY, sv.number, LTRIM(@year) + '-01-01') BETWEEN LTRIM(@year) + '-01-01' AND DATEADD(YEAR, 1, LTRIM(@year) + '-01-01') - 1

select * from @Data_Table

6.Merge语句

使用merge语句可实现不同服务器之间同步更新

MERGE INTO 表名 AS 目标
USING (SELECT * FROM 表名2) AS 源
ON (目标.字段名 = 源.字段名)
WHEN MATCHED THEN
    UPDATE SET 目标.字段名 = 源.字段名
WHEN NOT MATCHED BY TARGET THEN
    INSERT (字段名1, 字段名2, ...)
    VALUES (源.字段名1, 源.字段名2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

• 打开SQL Server 代理
• 新建作业，常规名称自定义
• 新建步骤，步骤名称自定义，类型为T-SQL，粘贴语句，数据库选择确定
• 制定计划，根据实际业务场景设置

二、MySQL

1.ID重排序

--对ID自增库中删除后，不重新排序
set @i=0;
update [TableName] set id=(@i:=@i+1)
alter table [TableName] auto_increment=1;
↓
set @i=0;
update [TableName] set id=(@i:=@i+1);
↓
set @i=0;
update [TableName] set id=(@i:=@i+1)
alter table [TableName] auto_increment=1;