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 语句成功 　zhidao　
-1 FETCH 语句失败或此行不在结果集中 　　
-2 被提取的行不存在 　　
@@fetch_status值的改变是通过fetch next from实现的
“版FETCH NEXT FROM Cursor”
0的时候，游标查询的结果有，游标就会将查询到的数据直接存入游标捕捉数据的权变量中。

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

日期格式

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

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()

cast (‘123‘ as varbinary)

sqlserver:
mysql: ?select cast(‘wIsc0m-1ng‘ as CHAR(36) CHARACTER SET BINARY)
SELECT 1 where ‘wIsc0m-1ng‘ in (select cast(‘wIsc0m-1ng‘ as CHAR CHARACTER SET BINARY))
SELECT 1 where ‘wIsc0m-1ng‘ in (select cast(‘wIsc0m-1ng‘ as BINARY))
SELECT 1 where ‘wIsc0m-1ng‘ in (select cast(‘wIsc0m-1ng‘ as CHAR(36) CHARACTER SET BINARY))
?select cast(‘wIsc0m-1ng‘ as binary)=‘wIsc0m-1ng‘
https://*.com/questions/55350301/select-castuuid-as-varbinary100-mysql-query-error

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

mysql

UPDATE table1 a,table2 b 
SET a.fee = b.fee 
WHERE a.id = b.id

UPDATE table1 a
LEFT JOIN table2 b ON a.id = b.id 
SET a.fee = b.fee

https://www.cnblogs.com/jierong12/p/9376103.html
sqlserver:

来源：?https://blog.csdn.net/icecoola_/article/details/79756247
---不使用别名
UPDATE A  SET A1 = B1, A2 = B2, A3 = B3  
FROM A 
LEFT JOIN B ON A.ID = B.ID
--使用别名
UPDATE v1  SET A1 = B1, A2 = B2, A3 = B3  
FROM A as v1
LEFT JOIN B ON v1.ID = B.ID

update?t1?set?t1.D?=?t2.D
from?t2
where?t1.A?=?t2.A

SELECT?LAST_INSERT_ID()?在插入生成AUTO_INCREMENT值的行之后?，您可以获得主键的值

SQLSERVER:SELECT SCOPE_IDENTITY()
MYSQL??SELECT?LAST_INSERT_ID()

CONCAT(‘1‘,‘‘,‘2‘)

sqlserver :SELECT ‘1‘+ ‘‘ + ‘2‘
mysql: ?SELECT CONCAT(‘1‘,‘\‘,‘2‘)

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