--删除表
use [20130823_recource]
go
drop table my_table1,my_table2,my_table3
--创建表
use [20130823_recource]
go
if(exists(select * from sys.objects where))
drop table student1
go
create table student1
(
id int primary key identity(1,2) not null,
name nvarchar(30) not null,
age int not null,
mymoney decimal ,
createdatetime datetime default getdate()
)
--插入数据
insert into student values('zhangsan',34,2300,getdate())
insert into student
select 'zhangsi',23,4300 ,getdate()union
select 'zhangwu',33,5400,getdate() union
select 'zhanghong',12,2300,getdate()
--修改数据
update student set mymoney=10000 where age=12
--删除数据
delete student where age=12
truncate table student
--存储过程
if(exists(select * from sys.objects where))
drop proc proc_name
go
create proc proc_name(@number int,@number1 int output)
as
begin
select @number1=su.mymoney from student as su where su.id=@number
end
--执行存储过程
declare @num int
exec proc_name 3,@num output
print @num
--函数
if(exists(select * from sys.objects where))
drop function function_name
go
create function function_name(@number int)
returns int
as
begin
declare @number1 int
select @number1=su.mymoney from student as su where su.id=@number
return @number1
end
--执行函数
select dbo.function_name(3)
--视图
if(exists(select * from sys.objects where))
drop view view_name
go
create view view_name
as
select * from student where id=3
--执行函数
select * from view_name
--游标
declare cursor_name cursor scroll for
select su.name from student as su
open cursor_name
declare @name nvarchar(20)
fetch last from cursor_name into @name
print @name
fetch absolute 3 from cursor_name into @name
print @name
fetch relative 1 from cursor_name into @name
print @name
fetch prior from cursor_name into @name
print @name
fetch first from cursor_name into @name
while(@@fetch_status=0)
begin
print @name
fetch next from cursor_name into @name
end
close cursor_name
deallocate cursor_name
--事务
begin tran tran_name
declare @error int
set @error=0
begin try
update student set mymoney=mymoney+1000 where id=1
set @error=@error+@@error;
update student set mymoney =mymoney -1000 where id=2
set @error=@error +@@error;
end try
begin catch
print '错误号：'+error_number()+'错误信息:'+error_message()
set @error=@error+1;
end catch
if(@error>=1)
begin
rollback tran
print '失败'
end
else
begin
commit tran
print '成功'
end
--触发器
if(exists(select * from sys.objects where))
drop trigger trigger_name
go
create trigger trigger_name
on student
for delete
as
insert into student values('zhangsss',11,3400,getdate())
--执行触发器
delete student where id=1
--排名
select *,row_number() over(partition by name order by id) as ran from student
select *,rank() over(order by id) as ran from student
select *,dense_rank() over(order by id ) as ran from student
select *,ntile(2) over(order by id) as ran from student
--开窗函数
count（*）
--集合
select * from student
union--合并
select * from student1
select * from student
intersect--交集
select * from student1
select * from student
except--除去
select * from student1
--连接
select su.name,su1.name from student as su
inner join student1 as su1
on su.id=su1.id
select su.name,su1.name from student as su
left join student1 as su1
on su.id=su1.id
select su.name,su1.name from student as su
right join student1 as su1
on su.id=su1.id
--case
select *,case
when mymoney
when 2500when 4500end as ran
from student1
select distinct top 2 * from student --top,distinct
select isnull(null,2) --判断是否为null
select getdate() --获得日期
select datename(day,getdate())--获得日期的某一字段
select dateadd(month,1,getdate()) --当前日期加
select count(*),avg(su.mymoney),sum(su.mymoney),min(su.mymoney),max(su.mymoney) from student as su --系统函数
select * from student su where su.id5 -- 符合：、、
select * from student su where su.name like'%wu'--模糊查询:%、_、[]、^
select * from student su where su.id between 2 and 6 -- between and
select * from student su where su.id in(3,4,5)--in()
select age from student su group by su.age having age>22 --筛选分组
select * from student su order by su.id desc--排序
触发器的两个重要的表
对表的操作
inserted逻辑表
deleted逻辑表
增加记录（insert）
存放增加的记录
无
删除记录（delete）
无
存放被删除的记录
修改记录（update）
存放更新后的记录
存放更新前的记录
触发器回滚
if(exists(select * from sys.objects where name = 'tr_valid'))
drop trigger tr_valid
go
create trigger tr_valid
on mymsg
for insert
as
declare @age int;
select @age=age from inserted
if(@age>50)
begin
insert into mymsg select name,age from inserted
end
else
begin
print 'age数值不正确'
rollback tran;--数据不正确，就执行回滚业务
end
insert into mymsg values('zl68',51) --测试
，