sql学习笔记

ifexists(select*fromsysdatabaseswherename='student')
dropdatabasestudent--如果该数据库已经存在就删除
go
createdatabasestudentsysdatabases
go
usestudent
go
ifexists(select*fromsysobjectswherename='stuinfo')
droptablestuinfo--如果该表已经存在就删除
go
createtablestuinfo
(
sidintprimarykey,
snamevarchar(40)notnull,
ssexvarchar(20)notnull
)
go
ifexists(select*fromsysobjectswherename='scoreinfo')
droptablescoreinfo
createtablescoreinfo
(
idintprimarykey,
sidintreferencesstuinfo(sid),--外键
scoreintnotnull
)
go
insertintostuinfovalues(0001,'张三','男')
insertintostuinfovalues(0002,'李思','女')
go
insertintoscoreinfovalues(01,0001,85)
insertintoscoreinfovalues(02,0002,80)
go
--演示多表联结查询(必须有主外键关系)
usestudent
go
--(1)内联结查询--》特点：能取出公共字段的“共同值”
select*fromstuinfoasstuinnerjoinscoreinfoasscoonstu.sid=sco.sid
--(2)左外联接--》特点：能取出公共字段和“左表”的全部值
select*fromstuinfoasstuleftouterjoinscoreinfoasscoonstu.sid=sco.sid
--(3)右外联接--》特点：能取出公共字段和"右表"的全部值
select*fromstuinfoassturightouterjoinscoreinfoasscoonstu.sid=sco.sid

--演示子查询----查询出姓名为‘张三’的人的分数
selectscorefromscoreinfowheresid=(selectsidfromstuinfowheresname='张三')

--演示排序函数

--集合运算：
--UNION和UNIONALL:并集
--UNION:把两张表合为一张表，去掉重复数据
--UNIONALL:把两张表合为一张表,不去掉重复数据
--注意:两张是同一个表
select*fromstumarkswherewrittenexam>70
union
select*fromstuinfowherewrittenexam>60
select*fromstumarkswherewrittenexam>70
unionall
select*fromstumarkswherewrittenexam>60
--INTERSECT:交集
--把两张表合为一张表，去掉不重复数据，保留重复数据
select*fromstumarkswherewrittenexam>70
intersect
select*fromstumarkswherewrittenexam>60
--EXCEPT:差集
--把两张表相减,保留不重复的数据
--注意:前一个表比后一个表的数据要多才行
select*fromstumarkswherewrittenexam>60
except
select*fromstumarkswherewrittenexam>70



--带输入参数的存储过程
ifobject_id('test','p')isnotnull
dropproctest--如果该存储过程已经存在就删除
go
createproctest
@aint,@bint
as
select@a-@b
go
exectest@b=15,@a=20--执行存储过程（传参数）



--带输出参数的存储过程
ifobject_id('demo','p')isnotnull
dropprocdemo
go
createprocdemo
@num1int,@num2int,@num3intoutput
as
set@num3=@num1+@num2

go
declare@tempint--定义临时变量
execdemo@num1=20,@num2=15,@num3=@tempoutput
select@temp

--存储过程完成分页
ifobject_id('page','p')isnotnull
dropprocpage
go
createprocpage
--输入参数：当前页，每页显示的条数，表名，字段
@currentpageint=1,
@countint=10,
@tablenamevarchar(20),
@columnvarchar(20)
as
declare@sqlnvarchar(1000)

set@sql='selecttop'+str(@count)+'*from'+@tablename+'where'+@column+'notin'+
('selecttop'+str((@currentpage-1)*@count)+@column+'from'+@tablename)

exec(@sql)
go

execpage2,3,'bookinfo','bookid'
--计算出总页数

总页数=（总条数（每页显示的条数-1））/每页显示的条数



--sql语句查询第N页数据（每页显示M条数据）

usebookshop
go
i=(n-1)*m
selecttopn*frombookinfowherebookidnotin(selecttopibookidfrombookinfo)



--事务与游标
--模拟汇款操作，典型的事务举例(其实sql中的关键字go就是一个事务)
begintransactiontran_bank--transaction可以简写为tran
--定义一个记录错误的变量
declare@tran_errorint
set@tran_error=0
--在三毛的账户减去钱
updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='三毛'
set@tran_error=@tran_erro+@@error
--在小毛的账户中增加钱
updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='小毛'
set@tran_error=@tran_error+@@error
if@tran_error<>0
begin
--执行出错，立即回滚事务
rollbacktransaction
print'转账失败，双方交易取消'
end
else
begin
--没有发现出错
committransaction
print'转账成功，双方交易完成'
end
go

--视图与索引

--如果已经存在就删除
ifexists(
select*fromdbo.sysobjectwherename='view_stuInfo_stuMarks'
)
dropviewview_stuInfo_stuMarks--删除视图的语法
go
createviewview_stuInfo_stuMarks
as
--内联接查询语句
select*fromstuinfoasstuinnerjoinscoreinfoasscoonstu.sid=sco.sid
go
--查看视图
select*fromview_stuInfo_stuMarks

--触发器

--insert触发器
--卡表
createtablecard
(
idintprimarykey,
card_namevarchar(40),
banlancemoney
)
go
insertintocardvalues(001,'张三',2000)
insertintocardvalues(002,'李四',8000)
insertintocardvalues(003,'王五',5000)
--交易表
createtabletranslate
(
t_idintprimarykey,
card_idint,
t_datedatetime,
t_typevarchar(4),
salarymoney
)
insertintotranslatevalues(001,001,getdate(),'存',400)
insertintotranslatevalues(002,002,getdate(),'取',400)
insertintotranslatevalues(0004,003,getdate(),'存',400)
go

ifobject_id('ex')isnotnull
droptriggerex
go
createtriggerex
ontranslateforinsert
as
declare@idint
declare@cmoney
declare@typevarchar(4)
--inserted逻辑表
select@id=card_id,@c=salary,@type=t_typefrominserted
if@type='存'
begin
updatecardsetbanlance=banlance+@cwhereid=@id
end
else
begin
updatecardsetbanlance=banlance-@cwhereid=@id
end