一些SQL记录
都是很简单的。 但平时可能用的也比较多。 为了方便查找。故而记录于此，
truncate table users --删除所有记录，性能高于delete
print @@version --系统版本
print @@servername --服务器名
if @@error=245
print 'insert wrong'
print @@language --版本语言信息
print @@datefirst --一周的第一天从星期几算起
print @@identity
declare @num int --局部变量
set @num=12
declare @i int --if条件判断
set @i=9
if (@i>10)
begin
print 'i morethan 10'
end
else
begin
print 'i lessthan 10'
end
declare @i int --while循环控制
set @i=12
--print @i
while (@i<18)
begin
print @i
set @i=@i+1
if @i<17
continue;
if @i>15
break;
end
--这样的交集。也就是取得相同项。
intersect 用于两个集合间。
--使用case分支判断
select username,'管理员' as rank from users where username='admin'
select username,'普通用户' as rank from users where username='mrhu'
select username,
case username
when 'admin' then '管理员'
when 'mrhu' then '普通用户'
else username
end as rank
from users
--系统函数
print ascii('ABC') --获取指定字符串中左起第一个字符的ASC码
print char(75) --根据给定的ASC码获取相应的字符
print len('abcde') --获取给定字符串的长度
print lower('ABCDE') --转小写
print upper('abcde') --转大写
print ltrim(' abc abc d') --过滤左空格
print rtrim(' abc abc ') --过滤右空格
print abs(-123) --绝对值
print power(2,3) --2的3次方
print rand()*1000 --获取0--1000的随机数
print pi() --圆周率
print getdate() --系统时间
print dateadd(day,-3,getdate()) --3天前时间
print dateadd(hh,4,getdate()) --加上4小时，hour/hh,minute/mi,second/ss
print datediff(year,'2005-1-1',getdate()) --指定时间和现在时间的年差
print datediff(mi,'2005-1-1','2006-1-1') --minute/mi,second/ss
print 'abc'+cast(456 as varchar) --字符串转换合并
print 'abc'+convert(varchar,456) --字符串连接要保持类型一致
print convert(varchar(12), '2005-01-01')
print year(getdate()) --获取指定时间部分，year,month,day
print datepart(year,getdate())
print datepart(hh,getdate()) --小时
print datepart(mi,getdate()) --分钟
print datepart(ss,'2005-2-1 12:30:50') --秒
print datepart(ms,getdate()) --毫秒
print host_id() --返回工作站标识号
print host_name() --获取主机名
print db_id('master') --获取数据库编号
print db_name(4) --获取数据库名
select stuff('ABCDEF',2,1,'GH')as test --填充函数
create table student
(
sname varchar(30),
sbirthday datetime --sbirthday datetime default (getdate())
)
-- 利用系统函数作为默认值约束
alter table student add constraint df_student_sbirthday default (getdate()) for sbirthday
insert into student(sname) values('mrhu')
insert into student values('admin',default)
select * from student
alter table student drop df_student_sbirthday --删除约束
sp_help student --显示表信息
create function countstudent(@sname varchar(12)) --自定义函数
returns int
begin
return (select count(*) from student where sname=@sname)
end
select dbo.countstudent('admin') as counts --调用自定义函数
select * from sysobjects where xtype='FN'
if object_id('student2') is not null
drop function student2
create function student2(@sname varchar(12)) --返回内联表值函数
returns table
as
return
(
select * from student where sname=@sname
)
select * from dbo.student2('admin') --调用函数
create function student3(@sname varchar(12)) --表值函数
returns @studentTest table
(
用户名 varchar(12),
注册时间 datetime
)
as
begin
insert @studentTest
select * from student as s where sname=@sname
return
end
select * from student3('mrhu') --调用函数
drop function student3
select distinct sname from student --剔除重复
select * from users where id>all(select id from users where id<3)
select * from users where id>=any(select id from users)
if exists(select * from users where username='mrhu')
print 'exists'
else
print 'not exists'
select username,id from users where username='mrhu'
union
select '合计:',sum(id) from users
declare @str varchar(200) --执行带变量的sql
declare @i int
set @i=4
set @str='select top '+cast(@i as nvarchar(20))+' from users'
--exec(@str)
exec sp_executesql @str
declare cur_exp cursor for select * from users --游标定义
open cur_exp
fetch cur_exp --提取游标
fetch next from cur_exp
close cur_exp
deallocate cur_exp --释放游标
select @@fetch_status --游标执行状态0(fetch执行成)，-1(执行失败或行不存在)，-2(行不存在)
create table Book
(
title varchar(50),
price numeric(9,3)
)
insert into Book values('book1',95.00)
insert into Book values('book2',45.00)
insert into Book values('book3',65.00)
insert into Book values('book4',99.00)
update Book set price=155.00 where title='book2'
delete from Book
select * from Book
=====================================================
以下部分需要细看
=====================================================
--使用冒泡排序找出Book表中最贵的书
declare cur_book cursor for select title,price from Book
open cur_book
declare @title varchar(50)
declare @price numeric(9,3)
declare @title_temp varchar(50)
declare @price_temp numeric(9,3)
fetch cur_book into @title,@price
fetch cur_book into @title_temp,@price_temp
while @@fetch_status=0
begin
if @price<@price_temp
begin
set @title=@title_temp
set @price=@price_temp
end
fetch cur_book into @title_temp,@price_temp
end
close cur_book
deallocate cur_book
print '最贵的书是：'+@title+' 价格:'+convert(varchar(20),@price)
--通过存储过程，使用冒泡排序寻找最贵的书
create proc Book_GetMaxprice
as
declare cur_book cursor for select title,price from Book
open cur_book
declare @title varchar(50)
declare @price numeric(9,3)
declare @title_temp varchar(50)
declare @price_temp numeric(9,3)
fetch cur_book into @title,@price
if @@fetch_status<>0
begin
print '没有图书记录'
close cur_book
deallocate cur_book
return
end
fetch cur_book into @title_temp,@price_temp
if @@fetch_status<>0
begin
print '最贵的书是：'+@title+' 价格:'+convert(varchar(20),@price)
close cur_book
deallocate cur_book
return
end
while @@fetch_status=0
begin
if @price<@price_temp
begin
set @title=@title_temp
set @price=@price_temp
end
fetch cur_book into @title_temp,@price_temp
end
close cur_book
deallocate cur_book
print '最贵的书是：'+@title+' 价格:'+convert(varchar(20),@price)
drop proc Book_GetMaxprice
exec Book_GetMaxprice
select * from users
insert into users values(10,'2;5;9')
delete from users where id=10
select * from users where username like '%'+cast(id as varchar(12))+'%'
create trigger myTrigger
select * from sys.triggers
select * from
--创建触发器
create trigger mytrigger
on student
for insert
as
insert into student(sname) values('mrhu')
drop trigger mytrigger
sp_helptext mytrigger
alter database test set recursive_triggers off
create table emp_mgr
(
Emp int primary key
)
select * from student
insert into student(sname) values('admin')
sp_helptrigger student --查看与表相关的触发器
select * from users
--这里是事务
begin transaction
delete from users where id=12
if @@error <>0
rollback tran
insert into users values(10,'test')
if @@error<>0
begin
print '执行错误！'
rollback tran
end
else
commit tran
