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 name='Student1'))
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 name='proc_Name'))
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
--统计存储过程写法
create procedure proc_name@CountDate datetime=null --统计时间as begin if(@CountDate is null) --统计时间为空则赋值 begin set @CountDate=dateadd(day,-1,getdate()); end if exists(select top 1* from 表 where datediff(day,Countdate,@Countdate)=0) --如果统计过了则退出 return;end--函数
if(exists(select * from sys.objects where name='function_Name'))
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 name='view_Name'))
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 name='trigger_Name'))
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<MyMoney and MyMoney<=4500 then '资产'
when 4500<MyMoney then '富人'
end as ran
from Student1
--while循环
while(@number>0)
begin
end
--常用函数
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--排序
select isnull(a.id,0)--为空就为0
触发器的两个重要的表
|
对表的操作 |
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) --测试
读取相关文章数据
Select top 10 * from stu order by newid()--获得随机10条
Select top 1 * from stu where id>@0 order by id asc--下一条//@0为传入参数
Select top 1 * from stu where id<@0 order by id desc--上一条//@0为传入参数
--日期处理
DATEADD(s,CreatedAt,'1970-1-1 08:00:00') --秒转换成日期
convert(smalldatetime,convert(varchar(10),getdate(),120))--字符串转换为日期
dateadd(mm,datediff(mm,0,'2015-02-11'),0)--月第一天
dateadd(ms,-3,dateadd(mm,datediff(m,0,'2015-02-11')+1,0))--月最后一天
dateadd(mm,datediff(mm,0,'2015-02-11')+1,0)--下个月第一天
dateadd(mm,datediff(mm,0,'2015-02-11')+2,-1)--下个月最后一天
dateadd(yy,datediff(yy,0,getdate()),0)--一年中第一天
dateadd(day,7,dateadd(day,2-(case when datepart(weekday,'2015-02-11')=1 then 8 else datepart(weekday,'2015-02-11') end),'2015-02-11'))--周一
dateadd(day,7,dateadd(day,8-(case when datepart(weekday,'2015-02-11')=1 then 8 else datepart(weekday,'2015-02-11') end),'2015-02-11'))--周日
字符串截取
case when Len(UserDesc)>3 then substring(UserDesc,0,3)+'...'
when Len(UserDesc)<=3 then UserDesc
end as UserDesc
