【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“T-SQL事务索引视图”一文，供大家参考学习
IFDB_ID(N'STU')ISNOTNULL
DROPDATABASESTU
GO
CREATEDATABASESTU
GO
IFOBJECT_ID(N'BANK')ISNOTNULL
DROPTABLEBANK
GO
CREATETABLEBANK
(IDINTPRIMARYKEYIDENTITY,
NAMENVARCHAR(10),
BANLANCEMONEYCHECK(BANLANCE>0)
)
GO
IFOBJECT_ID(N'STU')ISNOTNULL
DROPTABLESTU
GO
CREATETABLESTU
(IDINTPRIMARYKEYIDENTITY,
NAMENVARCHAR(10),
SEXNVARCHAR(3)CHECK(SEX='男'ORSEX='女')
)
GO
IFOBJECT_ID(N'GRADE')ISNOTNULL
DROPTABLEGRADE
GO
CREATETABLEGRADE
(G_IDINTPRIMARYKEYIDENTITY,
COURSENAMENVARCHAR(10),
CHENGJIINTCHECK(CHENGJIBETWEEN0AND100),
CHENGJITWOINTCHECK(CHENGJITWOBETWEEN0AND100),
EXAMDATEDATETIME)
GO
--简单插入
INSERTBANKVALUES('张三',1000)
INSERTBANKVALUES('李四',10000)
INSERTSTUVALUES('张三','男')
INSERTSTUVALUES('李四','女')
INSERTSTUVALUES('王二','女')
INSERTSTUVALUES('姜一','男')
INSERTGRADEVALUES('VB',55,44,'2012.10.5')
INSERTGRADEVALUES('C',90,86,'2012.10.10')
INSERTGRADEVALUES('SQL',80,74,'2012.2.11')
INSERTGRADEVALUES('VB',50,44,'2012.11.5')
INSERTGRADEVALUES('C',44,86,'2012.12.10')
INSERTGRADEVALUES('SQL',97,74,'2012.9.11')
INSERTGRADEVALUES('C',66,86,'2012.12.10')
--简单查询
SELECTID,CHENGJI,CHENGJITWO,(CHENGJI+CHENGJITWO)/2,COURSENAME,NAME
FROMSTUINNERJOINGRADEONSTU.ID=GRADE.G_ID
SELECTAVG(CHENGJITWO),AVG(CHENGJI)FROMGRADE
SELECTNAME,SEX,CASE
WHENCHENGJIISNULLTHEN'缺考'
ELSE
CONVERT(NVARCHAR,CHENGJI)
END
FROMSTULEFTOUTERJOINGRADEONSTU.ID=GRADE.G_ID
DELETEGRADE
SELECT*FROMGRADE
IFNOTEXISTS(
SELECT*FROMSTULEFTOUTERJOINGRADEONSTU.ID=GRADE.G_ID
WHERECHENGJI>60ANDCHENGJITWO>60ANDEXAMDATE=(SELECTMAX(EXAMDATE)FROMGRADE)
)
BEGIN
UPDATEGRADESETCHENGJI=CHENGJI+2
UPDATEGRADESETCHENGJI=95WHERECHENGJI>95
END
SELECT*FROMGRADE
SELECTMAX(EXAMDATE)FROMGRADE
DELETEBANK
SELECT*FROMBANK
DELETESTU
SELECT*FROMSTU
DELETEGRADE
SELECT*FROMGRADE
--事务引入
--方法一
BEGINTRAN
SETXACT_ABORTON
UPDATEBANKSETBANLANCE=BANLANCE+5000
WHERENAME='张三'
UPDATEBANKSETBANLANCE=BANLANCE-5000
WHERENAME='李四'
COMMITTRAN
SELECT*FROMBANK
--方法二
BEGINTRAN--SACTION
SELECT@@ERROR
DECLARE@SUMINT
PRINT'查看转帐前的有余额'
SELECT*FROMBANK
UPDATEBANKSETBANLANCE=BANLANCE-300
WHERENAME='张三'
SET@SUM=@@ERROR
UPDATE
ELSEBANKSETBANLANCE=BANLANCE+300
WHERENAME='李四'
SET@SUM=@SUM+@@ERROR
PRINT'查看转帐过程中的有余额'
SELECT*FROMBANK
IF(@SUM=0)
BEGIN
PRINT'交易成功'
COMMITTRAN
END
BEGIN
PRINT'交易失败'
ROLLBACKTRAN
END
--索引
CREATEINDEXS
ONSTU(NAMEDESC)
GO
SELECT*FROMSTU
WITH(INDEX(S))
GO
--视图
IFOBJECT_ID('T')ISNOTNULL
DROPVIEWT
GO
CREATEVIEWT
--WITHENCRYPTION
AS
SELECT*FROMSTU
WHEREIDNOTIN(SELECTIDFROMGRADE)
GO
SELECT*FROMT
GO
SELECT*FROMsys.sql_modules
