sql server常用知识点

–删除表 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

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

，