mysql sqlserver语法_SqlServer与MySql语法比较

1、复制表(包括表结构、表数据)

SqlServer：

Select * into user_copy from user

MySql：

CREATE TABLE user_copy LIKE user;

INSERT INTO user_copy SELECT * FROM user;

2、多表连接做update

SqlServer：

UPDATE s1 SET s1.salary= s2.salary

FROM (SELECT * FROM s WHERE age>10) AS s1 JOIN s2 ON s1.uname= s2.uname

WHERE s1.uname = 'xx'

MySql：(无法直接对目标表的查询数据直接做更新，只能针对目标表操作)

8、SqlServer和Mysql插入记录前判断是否存在，存在则插入，不存在则修改

SqlServer：

if NOT exists(SELECT * FROM Setting WHERE ID=0)

INSERT INTO Setting(ID,Title,SearchKeys, ServiceIntervalSecond,SleepMillisecondPerSearch) VALUES(0,@title,@searchKeys,@serviceIntervalSecond,@sleepMillisecondPerSearch)

ELSE

UPDATE Setting SET Title=@title,SearchKeys=@searchKeys,ServiceIntervalSecond=@serviceIntervalSecond, SleepMillisecondPerSearch=@sleepMillisecondPerSearch

MySqL：

INSERT INTO `his_examine_result` (Mid,His_Examine_Mid, His_File_Mid, ResultType, His_Employee_Mid,

His_Employee_Name, ExamineResult, ExamineItemName, ExamineStandardName,

ExamineItemUnit, ExamineHighValue, ExamineLowValue, ExamineDiscription, Discription)

VALUES ($mid,$examineMid,$fileMid,1,null,null,'',$itemName,$standName,$itemUnit,$highValue,$lowValue,'','')

ON DUPLICATE KEY UPDATE ExamineResult=$result,ExamineDiscription=$dis

9、创建索引

SqlServer：

IF NOT EXISTS(SELECT * FROM sysindexes WHERE id = OBJECT_ID('T_Person_info') and name = 'idx_pCode')

CREATE NONCLUSTERED INDEX idx_pCode ON [dbo].T_Person_info

(

pCode ASC

) ON [PRIMARY];