各位用户为了找寻关于规范化的SQL数据修改语句总结的资料费劲了很多周折。这里教程网为您整理了关于规范化的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