规范化的SQL数据修改语句总结

时间:2022-03-09 02:18:10

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