sqlserver常用知识点

--删除表 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) no

--删除表

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<=2500 then '穷人'

when 2500

when 4500

end 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.Id<>5 -- 符合：<、>、<>、<=、=>

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) --测试

，