use mastergoif db_id('Student') is not nulldrop database Studentgocreate database Studentgouse Studentgocreate table UserInfo(userId int not null PRimary key identity,userName varchar(20) ,)gocreate table Class(userName varchar(12) not null)go

create table UserMoney(moneyId int not null primary key identity,[money] int )go

insert into UserMoney values(200)insert into UserMoney values(300)insert into UserMoney values(400)insert into UserMoney values(500)insert into UserMoney values(600)insert into UserMoney values(700)insert into UserMoney values(800)

insert into Class values('C101')insert into Class values('C102')insert into Class values('C103')insert into Class values('C104')insert into Class values('C105')

insert into userInfo values('liujie1')insert into userInfo values('liujie2')insert into userInfo values('liujie3')insert into userInfo values('liujie4')insert into userInfo values('liujie5')insert into userInfo values('liujie6')insert into userInfo values('liujie7')insert into userInfo values('liujie8')insert into userInfo values('liujie9')

select * from UserInfo

--創建索引--判斷索引是否存在if exists (select 1 from sys.indexes where name='IX_UserId')drop index IX_UserId on UserInfo--刪除首頁索引go--創建索引create Index IX_UserId on UserInfo(userId)go

--使用索引select * from UserInfo with (index = IX_UserId)

--創建視圖--判斷視圖是否存在if OBJECT_ID('v_UserInfo') is not nulldrop view v_UserInfo--刪除視圖go--開始創建視圖create view v_UserInfoasselect * from UserInfogo--查詢視圖select * from v_UserInfogo

--修改視圖alter view v_UserInfoasselect * from Classgo

--查詢視圖select * from v_UserInfogo

--事務的定義。系統在執行并發操作時，最小的執行單元--創建事務

begin transaction;insert into UserInfo values('chaomong');commit transaction--提交事務

select * from UserInfobegin transactiondelete UserInfo where userName = 'chaomong'rollback tran--回滾事務

--創建觸發器forif OBJECT_ID('tr_userMoney') is not nulldrop trigger tr_userMoneygocreate trigger tr_userMoneyon UserMoney for insertasbeginprint '添加';end

insert into UserMoney values('101')

--添加觸發器 instead ofif OBJECT_ID('tr_userMoney_1') is not nulldrop trigger tr_userMoney_1gocreate trigger tr_userMoney_1on UserMoney instead of insertasbeginprint '添加_1';end

insert into UserMoney values('102')

select * from UserMoney

--創建觸發器forif OBJECT_ID('tr_userMoney_2') is not nulldrop trigger tr_userMoney_2gocreate trigger tr_userMoney_2on UserMoney for Updateasbeginbegin transactionselect * from insertedselect * from deletedcommit tranend

update UserMoney set money = money +1 where moneyId = 8

select * from UserMoney