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

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

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

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

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

--查詢視圖select * from v_UserInfogo

--事務(wù)的定義。系統(tǒng)在執(zhí)行并發(fā)操作時，最小的執(zhí)行單元--創(chuàng)建事務(wù)

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

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

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

insert into UserMoney values('101')

--添加觸發(fā)器 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

--創(chuàng)建觸發(fā)器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