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

SET NOCOUNT ON;

SQLServer cast()函数

默认值

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:

创建临时表

方法一：

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

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

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

UPDATE T1,T2

INNER JOIN T2 ON T1.C1 = T2.C1

SET T1.C2 = T2.C2,

T2.C3 = expr

WHERE condition

@@rowcount

--

insert into scr_menu_tree_t select *from scr_menu_tree_t_copy1;

SELECT row_count();

SQL Server中except和intersect用法

mysql 临时表 cann't reopen

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 FROMSCR_DRS_INTERFACE t2 FOR XML path ( '' )

mysql:    SELECT group_concat(t2.DRS_INTERFACE_NAME Separator ';')  FROMSCR_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

with as

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;

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

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;

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://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')