SQL语句的转换？

0 0

SQL语句的转换？5

在SQL2000数据库中可以用下面语句创建数据及表，我想在Mysql数据库也用此语句创建，但会出现语法错误。请大家帮一下忙，把改成在MYSQL数据库也能运行的语句，但不能改变其他的（如表的字段等）。只修改语法错误。。。

--1
CREATE DATABASE [FFMS]

--2
USE [FFMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Account]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Account](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL,
[accountitemid] [int] NULL,
[amount] [decimal](18, 2) NULL,
[accountdate] [datetime] NULL,
[remarks] [varchar](256) NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[User]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[User](
[id] [int] IDENTITY(1,1) NOT NULL,
[loginname] [varchar](50) NULL,
[password] [varchar](50) NULL,
[usernumber] [varchar](50) NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AccountItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[AccountItem](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NULL,
[accountitemnumber] [varchar](50) NULL,
[name] [varchar](50) NULL,
[type] [int] NULL,
CONSTRAINT [PK_AccountItem] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ViewAccount]') AND OBJECTPROPERTY(id, N'IsView') = 1)
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ViewAccount]
AS
SELECT     dbo.Account.id, dbo.Account.amount, dbo.Account.accountdate, dbo.Account.remarks, dbo.Account.userid, dbo.[User].loginname,
                      dbo.[User].usernumber, dbo.[User].name AS username, dbo.Account.accountitemid, dbo.AccountItem.accountitemnumber,
                      dbo.AccountItem.name AS accountitemname, dbo.AccountItem.type AS accountitemtype, dbo.AccountItem.pid AS asaccountitempid,
                      ParentAccountItem.accountitemnumber AS accountitempnumber, ParentAccountItem.name AS accountitempname
FROM         dbo.Account INNER JOIN
                      dbo.[User] ON dbo.Account.userid = dbo.[User].id INNER JOIN
                      dbo.AccountItem ON dbo.Account.accountitemid = dbo.AccountItem.id INNER JOIN
                      dbo.AccountItem AS ParentAccountItem ON dbo.AccountItem.pid = ParentAccountItem.id
'
GO