2013.9.21.TSQL学习

TSQL学习：    视图，事务，存储过程
--视图（view）
create view v_name
as
select。。--select 字段 from  v_name
[with check option]--约束不能改变视图的内容
 
alter view  v_name
as
select。。


drop view v_name
-------------------------索引(index)  页面


create nonclustered index IX_name
on table_name(field_name)
with fillfactor=30 --百分比


-----------------------------
1. 事务(关键字： Tran[saction])
定义： 定义一组操作，该组操作遵循“要么都执行，要么都不执行”的规律

示例： 关于银行转账


语法：
包含事务的开始和结束两部分
开始： begin tran
.....同一个事务中的操作
结束： commit [tran]  提交 / rollback [tran] 回滚




create database UserData
go
use UserData
drop table Users
create table Users(
uid int primary key,
uName varchar(20),
balance money check( balance>0)
)
go
go
--使用提交的方式结束事务
begin tran 
insert into Users values(1001,'宋宇')
commit
go


select * from users


--使用回滚的方式结束事务
begin tran 
insert into Users values(1002,'周欢')
rollback 
go


insert into Users values(1001,'宋宇',100000)
insert into Users values(1002,'邓龙',1)


--转账的sql操作
--成功 
update Users set balance=balance-1000 where uid = 1001
update Users set balance=balance+1000 where uid = 1002


--失败 宋宇把余额全部转给邓龙
update Users set balance=balance-99000 where uid = 1001
update Users set balance=balance+99000 where uid = 1002




--利用事务改写上述过程，防止出现异常后的数据异常
begin tran
declare @errorCount int
set @errorCount = 0
update Users set balance=balance-99000 where uid = 1001
set @errorCount =@@error+@errorCount
update Users set balance=balance+99000 where uid = 1002
set @errorCount =@@error+@errorCount
if(@errorCount>0)
begin
print '发生了错误，操作被撤消';
rollback tran;
end
else
begin
print '操作完成';
commit tran;
end


--事务的特点：原子性(事务最典型的特点)
隔离性
并发性
持久性

--事务的分类
1. 显式事务模式： begin .... commit/rollback
2. 隐式事务模式：set implicit_transactions off开启隐式模式的开关
开启之后，sql操作中，遇到的第一个select/update/delete/create。。。作为开始的标志
接下来的所有sql操作都会被认为在同一个“事务”(隐式事务)中
结束的标志依然是 commit/rollback
作用是：比显式模式简便

select * from users
insert into Users values(1003,'周游',100000)
update Users set balance=balance-99000 where uid = 1001
print @@trancount
commit
print @@trancount


3.自动提交事务
该事务模式是sql server类型数据库中的默认事务模式

insert into Users values(1007,'侯平',100000)
insert into Users values(1005,'姚东')
insert into Users values(1006,'吴毅恒',100000)




2. 存储过程(procedure 重点)
要点： 在SQL Server中掌握存储过程的操作、java操作存储过程的方法
定义：声明在数据库中的用于保存一组操作的数据库对象
和函数的区别： 函数有返回值，存储过程有传出参数
函数只能有一个返回，存储过程可以有多个传出
函数未经编译，存储过程会先编译再保存在数据库中
函数可以返回通过select得到的表对象，存储过程不行


作用：定义了一组操作，且可以根据参数的变化产生执行的不同效果，同时将该过程事先编译


语法：
create proc p_name
[@varName 类型,@varName 类型]
as
sql操作1
sql操作2
...
go


--声明存储过程，不带参数
create proc p_select_authors
as
select * from authors


--执行该存储过程： 通过关键字 execute executeUpdate executeQuery
--如果存储过程的执行操作是批中的第一条操作，那么exec可以省略
execute p_select_authors
--p_select_authors


--和单纯的查询比较: 速度会变快，因为会执行编译过后的查询
--和视图的查询比较：视图专做查询，存储过程除了查询还可以执行别的操作
--SET STATISTICS TIME on


--声明存储过程，带一个参数
create proc p_select_state_authors
@state char(2)
as
select * from authors where [state] = @state


--执行带参数的存储过程：
exec p_select_state_authors 'CA'
exec p_select_state_authors 'KS'
exec p_select_state_authors 'UT'


--练习： 声明一个带一个参数的存储过程
--可以查询authors表中作者信息，
--作者的姓是以指定字母开头的模糊查询


alter proc p_sel_state_authors
@n char(1)
as
select * from authors where au_lname like ''+@n +'%'
exec p_sel_state_authors 'W'
exec p_sel_state_authors 'r'
select * from authors where au_lname like  'W%'


--创建一个存储过程，带有两个参数
create proc p_select
@state char(2),
@lname varchar(40)
as
select * from authors where [state] = @state and au_lname=@lname


--调用带两个参数的存储过程


exec p_select 'CA','O''Leary'
exec p_select 'CA','white'


--创建一个存储过程，带有两个参数，且含有默认值
alter proc p_select
@state char(2) ='CA',      --给参数@state赋默认值
@lname varchar(40)='white'
as
select * from authors where [state] = @state and au_lname=@lname


--调用含有默认值的存储过程
exec p_select    --直接使用默认值完成操作
exec p_select 'CA','O''Leary'
exec p_select default,'Green'
exec p_select @state='CA'
exec p_select @state='CA',@lname='White'


--声明一个带有传出类型参数的存储过程  output
--利用output关键字将某个或某几个参数声明为传出类型
select * from titles


--根据指定的类型，查询所有该类型的书籍信息，同时统计总价并传出
create proc p_type_titles
@type varchar(80) ,
@sumPrice money output
as
 select * from titles where [type]=@type
 select @sumPrice = SUM(price) from titles where [type]=@type
go


--调用带有传出参数的存储过程
 --调用时准备一个变量接收传出值，且该变量也需要用output声明
declare @s money
exec p_type_titles 'business',@s output 
print '该类型书籍的总价是；'+cast(@s as varchar)


--作业相关
--动态生成表的方法
execute('create table tableName(字段 数据类型)')


--获取最新的一个自动增长的值
print @@identity