mysql数据库迁移到sqlserver时,存储过程和建表中用法的差别【未完】
1.标识符:
mysql: ``
sqlserver:[]
2.创建存储过程CREATE PROC:
2.1不带参数:
mysql:
DROP PROCEDURE IF EXISTS `up_init_orgdata`; DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `up_init_orgdata`() <要加括号>
sqlserver:
if (exists (select * from sys.objects where name = 'up_init_orgdata')) begin drop proc up_init_orgdata; end go <注意写go> CREATE PROCEDURE up_init_orgdata <不用加括号>
2.2带参数:
mysql:
DROP PROCEDURE IF EXISTS `存储过程名称`; <直接DROP proc IF EXISTS> DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `存储过程名称`(currenttime DATETIME, fromsys NVARCHAR(32)) <不用加AS>
sqlserver:
if (object_id('存储过程名称', 'P') is not null) begin drop 存储过程名称 END go if (object_id(存储过程名称, 'P') is not null) begin drop proc 存储过程名称 END go CREATE PROCEDURE 存储过程名称 (@currenttime DATETIME, @fromsys NVARCHAR(32)) AS
3.退出存储过程LEAVE:
mysql:LEAVE proc;
sqlserver:return;
4.条件判断IF:
mysql:
IF....THEN /*语句*/ END IF
sqlserver:THEN换成BEGIN ,END IF换成END
IF 条件 BEGIN /*语句*/ END
5.拼接字符串:
mysql:CONCAT(str1,str2,str3)
sqlserver:直接用+
str1+str2+str3
6.查询限定个数 LIMIT,BETWEEN:
mysql:select *from xxxxxxxx limit 0,xxxx
sqlserver:select top xxxx from
如果是 limit num1,num2
则用
select *from (select ROW_NUMBER()OVER(order by xxx) as row ,* from table_name as table_nameEXT ) T
where row between num1 and num2 ;
实现
7.往参数中插入数据 INTO:
mysql:
SELECT COUNT(*) INTO vtemrecordcount FROM CTI_TempPhoneRecord
sqlserver:
SELECT @vtemrecordcount =COUNT(*) FROM CTI_TempPhoneRecord
8.获得时间:
mysql:NOW()
sqlserver:getdate()
9.更新UPDATE:
有重命名表名时,在sqlserver中要用update s set xxxxx from table_name as s的写法,mysql中为update table_name as stu set xxxxxxx
mysql:
UPDATE StudentSearch AS stu JOIN ( SELECT SUM(DISTINCT CASE PayStatus WHEN 3 THEN 4 ELSE PayStatus END) AS PayStatus,StudentID FROM CustomerContractInfo WHERE ContractStatus=3 AND PayStatus IN (1,2,3) AND StudentID=student_id -- 在sqlserver中,出现在聚合函数后面的字段必须在最后用group by分组,在mysql中则不用 ) AS con ON stu.StudentID=con.StudentID SET stu.PayStatus=con.PayStatus WHERE stu.StudentID=student_id;
sqlserver:
UPDATE stu SET stu.PayStatus=con.PayStatus From StudentSearch as Stu JOIN ( SELECT SUM(DISTINCT CASE PayStatus WHEN 3 THEN 4 ELSE PayStatus END) AS PayStatus,StudentID FROM CustomerContractInfo WHERE ContractStatus=3 AND PayStatus IN (1,2,3) AND StudentID=student_id group by StudentID ) AS con ON stu.StudentID=con.StudentID WHERE stu.StudentID=student_id;
10.mysql中的
FIND_IN_SET(str,strlist)
**在sqlserver中,不可以用charindex代替
charindex(convert(varchar,xxx),xxxxx) 错
写两个函数
CREATE function [dbo].[Split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) -- 在临时表@t中插入@c中的第一个字符串(用@split <,>分割) set @c = stuff(@c,1,charindex(@split,@c),'') -- 第一个字符串@c中删除从第 1 个位置开始的charindex(@split,@c)长的字符,然后在删除的起始位置插入第二个字符串(''),从而创建并返回一个字符串。并更新@c<这里就是删除第一个字符串(包括,)> end insert @t(col) values (@c) -- 插入@c中最后一个字符串(因为最后一个字符串后面没有,了,所以不走while循环) return end ALTER FUNCTION [dbo].[Find_In_Set] ( -- Add the parameters for the function here @str1 varchar(100), @str2 varchar(100) ) RETURNS int AS BEGIN declare @result int ; select @result=COUNT(*) from Split(@str2,',') where col=@str1 return @result; END
11.mysql中的CROUP_CONCAT()
12.ISNULL()和IFNULL()
mysql中的ISNULL()只有一个参数,只能返回1或0,sqlserver中的ISNULL(ex1,ex2)是ex1如果为空则返回ex2,否则返回ex1
那么mysql中的IFNULL()在sqlserver中就是ISNULL()
mysql中的ISNULL在sqlserver中用case when then else end实现
case when ex1 is null then 0 else 1 end
13.字符串截取
mysql:
INSERT(b.Mobile,3,5,'xxxxx')
sqlserver:
SUBSTRING(b.Mobile,1,3)+'xxxxx'+SUBSTRING(b.Mobile,8,3)
14.创建索引:
mysql:
INDEX `IX_StaffID` USING BTREE (`StaffID`) ; -- 且这句话跟在CREATE TABLE的括号内
sqlserver:CREATE INDEX 索引名 ON 表明(列名)
CREATE INDEX AK_Key_1 ON FamilyRelationDictionary (ID)
15.自增长列:
mysql:AUTO_INCREMENT
sqlserver:identity(1,1)
PS.
sqlserver出现在聚合函数后面的字段必须在最后用group by分组,mysql不用
类型:
mysql中的int,bigint等后面可以跟(20)长度,
sqlserver中直接int,bigint就成了
mysql中真假可以用TRUE或FALSE,
sqlserver只能用1或0
其他:
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
CHECKSUM=0
ROW_FORMAT=Dynamic
DELAY_KEY_WRITE=0
这些在sqlserver都没用,可以删掉