SqlServer 存储过程和函数语法（部分与 mysql 的区别）

SET NOCOUNT ON;

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
https://www.cnblogs.com/lmfeng/archive/2011/10/12/2208821.html

SQLServer cast()函数

https://www.cnblogs.com/baxianhua/p/9104732.html
mysql :https://www.cnblogs.com/phpk/p/10931804.html

默认值

SqlServer：@score int=60
mysql：DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
https://www.runoob.com/w3cnote/mysql-stored-procedure.html

调用存储过程

SqlServer： exec scr.GetRoleSql_User p_user_id, v_role_sql output;
mysql： set @v_role_sql=1;
call scr.GetRoleSql_User(1,@v_role_sql);
select @v_role_sql
https://www.cnblogs.com/hlhx/p/9745966.html

WHILE @@FETCH_STATUS=0

Sql中的游标指针的位置判断。
0 FETCH 语句成功
-1 FETCH 语句失败或此行不在结果集中　　
-2 被提取的行不存在　　
@@fetch_status值的改变是通过fetch next from实现的

isnull

sqlserver isnull(null,'')
Mysql select IFNULL(null,'')

Replace

sqlserver:replace([content],'www.abc.com','www.bbb.com');
mysql :replace([content],'www.abc.com','www.bbb.com');

charindex

select CHARINDEX('zhang', 'Devil_Zhang')
运行结果：7

MySQL 语法
select instr('Devil_Zhang','zhang')
运行结果：7

getdate()

sqlserver getdate()
mysql: now() sysdate();
https://blog.csdn.net/iteye_7682/article/details/82671422?utm_medium=distribute.pc_relevant.none-task-blog-OPENSEARCH-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-OPENSEARCH-1.nonecase

临时表

sqlserver:
创建临时表
方法一：
create table #临时表名(字段1 约束条件,
字段2 约束条件,
.....)
create table ##临时表名(字段1 约束条件,
字段2 约束条件,
.....)
方法二：
select * into #临时表名 from 你的表;
select * into ##临时表名 from 你的表;
注：以上的#代表局部临时表，##代表全局临时表
————————————————
版权声明：本文为CSDN博主「平行宇宙i」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_38158541/article/details/88981963

mysql :CREATE TEMPORARY TABLE if not EXISTS temptables(fn_code varchar(30),create_by int);

raiserror

sqlserver:
raiserror 的作用： raiserror 是用于抛出一个错误
raiserror('Recursive exceed the limit!',1,10)
https://www.cnblogs.com/xugang/archive/2011/04/09/2010216.html

mysql：
signal sqlstate '45000' SET MESSAGE_TEXT = 'test error';
https://www.yiibai.com/mysql/signal-resignal.html

update inner set

https://blog.csdn.net/waiwai4701/article/details/52710800
sqlserver :

update a set a.name  = b.name
from product_detail a
inner join product_base_info b on a.id = b.id

2、也可以直接用where语句

update a set a.name = b.name
from product_detail a,product_base_info b
where a.id = b.id

来源： https://www.cnblogs.com/xxjcai/p/11983163.html

UPDATE T SET T.depth = P.Depth + 1, T.Lineage = P.Lineage + '//' + cast(T.id as nvarchar(50))
        FROM #t AS T 
        INNER JOIN #t AS P ON (T.pid=P.id) 
        WHERE P.Depth>=0 AND P.Lineage Is Not Null AND T.Depth Is Null

mysql :
https://www.cnblogs.com/dirgo/p/9491777.html

UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition

@@rowcount

sqlserver:https://www.cnblogs.com/aspnetjia/p/5138332.html
mysql :https://blog.csdn.net/business122/article/details/7548838
found_rows()和row_count()

-- 
 insert into scr_menu_tree_t select *from scr_menu_tree_t_copy1;
  SELECT row_count();

SQL Server中except和intersect用法

https://www.cnblogs.com/cnmarkao/p/3770017.html
https://forums.mysql.com/read.php?10,513537,514032#msg-514032
https://www.cnblogs.com/951106Nancy/p/9290539.html

mysql 临时表 cann't reopen

http://www.zzvips.com/article/38287.html
解决：多建几个临时表，或者不影响数据情况下建普通表

Mysql SELECT INTO 一次性给多个变量赋值

sqlserver: select @ex = dj_no_prefix, @line = isnull(current_num, 0) + 1, @len = dj_no_length from app_fn where upper(fn_code) = upper(@pFnCode);
mysql: SELECT c1,c2 FROM t1 FOR UPDATE INTO @v1,@v2;
https://blog.csdn.net/miaomiao19971215/article/details/105693778

日期格式

sqlserver

 select cast(convert(nvarchar(10), getdate(), 111) as datetime)

mysql

cast(DATE_FORMAT(NOW(),'%Y-%m-%d') as datetime(3))
 select cast(DATE_FORMAT(NOW(),'%Y-%m-%d') as datetime(3)) 
select cast(DATE_FORMAT(NOW(),'%c') as char(10))
select cast('2020-05-04' as datetime(3))
select DATE_FORMAT(cast('2020-05-04' as datetime(3)),'%e')

guid

sqlserver newid()
mysql UUID()

SELECT STUFF( ',123,123', 1, 1, '' )

sqlserver : SELECT STUFF( ',123,123', 1, 1, '' )
https://www.cnblogs.com/Angel-szl/p/11208175.html
mysql : SELECT INSERT( ',123,123', 1, 1, '' )

多行拼接为单行

sqlserver SELECT ',' + t2.DRS_INTERFACE_NAME FROM SCR_DRS_INTERFACE t2 FOR XML path ( '' )
mysql: SELECT group_concat(t2.DRS_INTERFACE_NAME Separator ';') FROM SCR_DRS_INTERFACE t2

select 525540 / 60 / 24;

SqlServer： select 525540 / 60 / 24; 364
mysql select 525540 / 60 / 24; 364.95833333

mysql向上向下取整

ceil （） /ceiling（）向上取整
eg： ceil（1.2） = 2
floor （）向下取整
eg： floor（1.2） = 1
round（）四舍五入

timeStampDiff abs round

sqlserver:
select datediff(s, GETDATE(), GETDATE()-1)
select abs((datediff(s, GETDATE(), GETDATE()-1) + 0.0) / 60)
select round(abs((datediff(s, GETDATE(), GETDATE()-1) + 0.0) / 60), 0)
mysql:
SELECT TimeStampDiff(SECOND ,NOW(), date_sub(NOW(),interval 1 day))
select abs(TimeStampDiff(SECOND ,NOW(), date_sub(NOW(),interval 1 day)) / 60)
select round(abs(TimeStampDiff(SECOND ,NOW(), date_sub(NOW(),interval 1 day)) / 60),0)

创建表时默认值为函数

sqlserver: default newid();
mysql：表字段默认值不能为函数,触发器实现

CREATE TRIGGER fs_file_row_guid
BEFORE INSERT ON fs_file
 FOR EACH ROW if (new.row_guid='' or new.row_guid is null) then  
       set new.row_guid= uuid();  
     end if

https://blog.csdn.net/qiuxinfa123/article/details/103440957

with as

https://blog.csdn.net/dongying1751/article/details/102457754
sqlserver:

   with test (id,name)as 
 (
 select 1, 1
 )
  select * from test

递归，mysql 不可以（现在也可以了8.0）

with cte_roota (team_leaderid, team_type_id, team_pid)
 as
 (
  select team_leaderid, team_type_id, team_pid
    from def_team
   where team_id = dbo.getuserteamid(1)
  union all
  select a.team_leaderid, a.team_type_id, a.team_pid
    from def_team a
   inner join cte_roota b
   on a.team_id = b.team_pid
 )
	
 SELECT * from cte_roota

mysql: 8.0.20

 WHILE tempTeamId IS NOT NULL 
 DO
 INSERT INTO cte_roota SELECT TEAM_LEADERID, TEAM_TYPE_ID, TEAM_PID FROM EROW_BIZ.DEF_TEAM WHERE TEAM_ID= tempTeamId;
 SELECT TEAM_PID INTO tempTeamId FROM EROW_BIZ.DEF_TEAM WHERE TEAM_ID= tempTeamId;
  END WHILE;

    
     with test as
 (
 select fn_code, create_by from app_fn
 )
  select * from test

with recursive t1(id, parent_id) as (
select*from blog_temp.etc_test t0 where t0.parent_id is null -- Anchor member.
 union all
select t2.id, t2.parent_id from blog_temp.etc_test t2, t1 -- Recursive member.
 where t2.parent_id = t1.id
)
select*from t1;

https://blog.csdn.net/nayi_224/article/details/97778698

mysql 分割字符串为表

select substring_index(substring_index('82,83,84,85,86',',',help_topic_id+1),',',-1) as Id
from mysql.help_topic
where help_topic_id<(length('82,83,84,85,86')-length(replace('82,83,84,85,86',',',''))+1);

oracle

SELECT REGEXP_SUBSTR('1,2,3','[^,]+',1,rownum) as model_id
FROM DUAL
CONNECT BY ROWNUM <= LENGTH('1,2,3') -LENGTH(REPLACE('1,2,3',',','')) + 1;

https://www.cnblogs.com/lllini/p/11955211.html

update set select where