mysql 代理修改sql语句_规范化的SQL数据修改语句总结

1 增加字段

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('QueryDataSource') and name = 'IsCrossTable')

BEGIN

ALTER TABLE QueryDataSource ADD IsCrossTable bit default(0) --插入字段

END

2 存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLSystem_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[PLSystem_Insert]

go

CREATE PROCEDURE dbo.PLSystem_Insert

(

@PLSystemID smallint,

@PLSystemName varchar(20),

@PLSystemFlag char(2)

)

AS

INSERT INTO dbo.PLSystem

(

PLSystemID,

PLSystemName,

PLSystemFlag

)

VALUES

(

@PLSystemID,

@PLSystemName,

@PLSystemFlag

)

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLSystem_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[PLSystem_Delete]

go

CREATE PROCEDURE dbo.PLSystem_Delete

(

@PLSystemID smallint

)

AS

DELETE FROM dbo.PLSystem

WHERE

PLSystemID = @PLSystemID

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLSystem_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[PLSystem_Update]

go

CREATE PROCEDURE dbo.PLSystem_Update

(

@PLSystemID smallint,

@PLSystemName varchar(20),

@PLSystemFlag char(2)

)

AS

UPDATE dbo.PLSystem SET

PLSystemName = @PLSystemName,

PLSystemFlag = @PLSystemFlag

WHERE

PLSystemID = @PLSystemID

go

3 创建表

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PLSystem]') AND type in (N'U'))

DROP TABLE [dbo].[PLSystem]

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PLSystem]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[PLSystem](

[PLSystemID] [tinyint] NOT NULL,

[PLSystemName] [varchar](20) NULL,

[PLSystemFlag] [char](2) NULL,

CONSTRAINT [XPKPLSystem] PRIMARY KEY CLUSTERED

(

[PLSystemID] ASC

)

) ON [PRIMARY]

END

GO

4 游标

Begin

declare @PluserID varchar(8)

declare pluserCurse Cursor for select PLUserID from PLUser where PCancelJudge=0

open pluserCurse --打开游标

fetch next from pluserCurse into @PluserID

while @@FETCH_STATUS=0

begin

print (@PluserID)

insert into PLUserActorDepart(DepartID,PLActorSystemID,PLUserID)

select DepartmentID,PLSystemID,PLUserID from MSUserRight where MSUserRight.DepartmentID not in

(

select DepartmentID from PLDepartment where PLDepartment.DParent=(select DepartmentID from PLUser where PLUserID=@PluserID )

OR PLDepartment.DepartmentID=(select DepartmentID from PLUser where PLUserID =@PluserID)

) AND MSUserRight.PLUserID=@PluserID

fetch next from pluserCurse into @PluserID

end

close pluserCurse

deallocate pluserCurse

end

5 用while实现for循环

Create Proc InsertSQL

AS

Begin

DECLARE @Count int

set @Count=0;

While(@Count<200000)

Begin

Print(@Count);

Insert into Student values('YOUNG','M',100,'FUJIANXIAMENT');

set @Count=@Count+1;

END

END