sql server 与mysql差异(innodb)

时间:2021-01-10 14:51:49








 

MySQL

SQL SERVER

CHAR_LENGTH(str)

LEN(character_expression)

CONCAT(str1,str2,...)

str1+str2

INSERT(str,pos,len,newstr)

STUFF ( character_expression , start , length ,character_expression )

LOCATE(substr,str [,pos])

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

LEFT(str,len)

LEFT ( character_expression , integer_expression )

LENGTH(str)

DATALENGTH ( expression )

LTRIM(str)

LTRIM(character expression)

REPEAT(str,count)

REPLICATE(character_expression,times)

REPLACE(str,from_str,to_str)

REPLACE(character_expression,searchstring,replacementstring)

REVERSE(str)

REVERSE(character_expression)

RIGHT(str,len)

RIGHT(character_expression,integer_expression)

RTRIM(str)

RTRIM(character expression)

SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)

SUBSTRING(character_expression, position, length)

SUBSTRING_INDEX(str,delim,count)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
TRIM([remstr FROM] str)

 

 

 

MySQL

SQL SERVER

CURDATE()

CURTIME()

DATE(expr)

DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)

DATEADD(datepart, number, date)

DAY(date)

DATEPART('dd', date)

HOUR(time)

DATEPART('hh', date)

MINUTE(time)

DATEPART('mi', date)

MONTH(date)

DATEPART('mm', date)

NOW()

GETDATE()

SECOND(time)

DATEPART('ss', date)

TIME(expr)

YEAR(date)

DATEPART('yy', date)

 

 

 

 

 

 

v在创建视图时需要注意:

如果表Test中有A、B两个字段,创建视图如下

Create view v_test as select * from Test;

此时视图实际的定义如下:

Create view v_test as select A,B from Test;

也就是说Test将来增加的字段并不在视图里面。

 

在SQL SERVER中,如果创建视图如下

Create view v_test as select * from Test;

此时视图实际的定义就是:

Create view v_test as select * from Test;

视图中永远都包含Test的所有字段。