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

概述介绍《Mysql入门规范化的SQL数据修改语句总结》开发教程，希望对您有用。

《MysqL入门规范化的sql数据修改语句总结》要点：
本文介绍了MysqL入门规范化的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,
@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
总结

以上是内存溢出为你收集整理的Mysql入门规范化的SQL数据修改语句总结全部内容，希望文章能够帮你解决Mysql入门规范化的SQL数据修改语句总结所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。