mssql与mysql对比_mysql和mssql对比

由于工作原因，程序需要适配两种类型的数据，所以把一些sql语句写法对比总结一下

本篇及后续随笔都将使用一个极其简单的场景(课室，学生，1对多)来演示，请先创建表

mysql

CREATE TABLE IF NOT EXISTS `class` (

`Id` int(11) NOT NULL,

`Name` varchar(50) NOT NULL DEFAULT ‘0‘,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `student` (

`Id` int(11) NOT NULL,

`ClassId` int(11) NOT NULL,

`Name` varchar(50) NOT NULL DEFAULT ‘0‘,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mssql

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Class](

[Id] [int] NOT NULL,

[Name] [nvarchar](10) NULL,

CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Student](

[Id] [int] NOT NULL,

[ClassId] [int] NULL,

[Name] [nvarchar](10) NULL,

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PA