常用Sql Server 语法大全

  本人经常使用Sql Server数据库，总结了一些常用的Sql Server语法含增删查改，存储过程，触发器等语法。提供新手学习及语法查找，文件简短，省去大量查找资料时间，不足之处有待完善。

                                      
                                                                  --常用Sql Server 语法大全
--建数据库命令
if exists (select * from sys.databases where name='test')
drop database test       --如存在相同名称数据库则删除数据库
Go
create database test on primary(
name=N'test',            --数据库名称
filename=N'D:\test.mdf', --文件地址
size=3MB,                --初始大小3MB(不能小于3MB)
maxsize=unlimited,       --文件最大容量值
filegrowth=1MB           --文件增长率
)
log on                   --数据库日志文件
(
name=N'test_log',        --日志文件名称
filename=N'D:\test_log.ldf',--日志文件地址
size=1MB,                --日志文件初始大小1MB
maxsize=1GB,             --日志文件最大容量值
filegrowth=10%           --日志文件增长率
)
                               --数据类型
--bit--smallint--int --char--float--double
--nchar--datetime--smalldatetime--varchar--nvarchar--image
--money--smallmoney--binary--varbinary--text--decimal(3,3)
--建表
if exists(select * from sys.objects where name='mytest' and type='U')
drop table mytest
go
create table mytest
(
id int identity(1,1) primary key,         --主键自增
[name] varchar(50) unique,                --唯一值不能重复
[sex] varchar(2) not null default('男'),  --默认为’男‘
address varchar(343) not null,            --不能为空
age smallint check(age<120 and age>0),     --约束不能大于120小于0岁
date datetime
)
insert into mytest([name],[sex],address,age,date) values('yi','男','于璐璐',39,getdate())
insert into mytest([name],[sex],address,age,date) values('yib','男','于璐璐',3,getdate())
insert into mytest([name],[sex],address,age,date) values('yibi','女','于璐璐',34,getdate())
insert into mytest([name],[sex],address,age,date) values('yibin','女','于璐璐',25,getdate())
insert into mytest([name],[sex],address,age,date) values('yibing','男','易津锐',30,getdate())
insert into mytest([name],[sex],address,age,date) values('yibing2','男','易津锐',26,getdate())
insert into mytest([name],[sex],address,age,date) values('y2','男','津锐',26,getdate())

go
--显示年龄在20-30之间的信息
select * from mytest where id not in(select id from mytest where age<30 and age>20)
select * from mytest where id not in(select id from mytest where age between 20 and 30)

--显示重复的记录方法一

select count(*) 相同个数 address from mytest group by address having count(address)>1
--显示重复的记录方法二

select a.* from mytest a where exists(
select 1 from mytest b where a.id<>b.id and a.address=b.address )

--显示重复的记录方法三
select distinct t.id,t.[name], max(t.address) from mytest t, mytest t2
group by t.id,t2.id,t.address,t2.address,t.[name] having t.id<>t2.id and t.address=t2.address

--显示重复的记录方法四
select distinct t1.* from mytest t1,mytest t2 where t1.id<>t2.id and t1.address=t2.address
--select * from 表名
--GROuP BY  姓名
--HAVING COUNT(姓名)>=2
--显示重复的记录方法五

select * from mytest where
--显示重复的记录个数方法
select count(*) 相同个数,a.address from (select t.* from mytest t where exists(select id from mytest t2 where t.id<>t2.id and t.address=t2.address))as a
group by a.address
--显示自定义序列号
select row_number() over(order by date ) 序列号,* from mytest

select * from mytest t1 where exists(select 1 from mytest t2 where t1.id<>t2.id and t1.address=t2.address)

select * from mytest where address like'_津%'      --模糊查询%%,_%,%_
select * from mytest where date between'2011-09-22 16:01:07.678' and '2011-09-29' --规定范围之间取值
select * from mytest where date >'2011-09-25 16:01:07.678' and date<'2011-09-26' --规定范围之间取值
select distinct * from mytest                      --distinct消除重复记录
select * from mytest order by id asc               --升序
select * from mytest order by id desc              --降序
select id,sex,age,count(*) as 个数,address from mytest group by sex,age,address,id
having id='1'  --分组
                              --数据查询
select t.*,t2.* from mytest t left join mytest t2 on t.id=t2.id--左连接
select t.*,t2.* from mytest t right join mytest t2 on t.id=t2.id--右连接
select t.*,t2.* from mytest t full join mytest t2 on t.id=t2.id--完全连接
select * from mytest where id not in(select id from mytest where id>2 and sex='女')--字查询
select * from mytest where id in(select id from mytest where id>2 and sex='女')--字查询
select * from mytest where exists(select id from mytest where id>2 and sex='女')--字查询
select * from mytest where not exists(select id from mytest where id>2 and sex='女')--字查询
select * from mytest where id < any(select id from mytest where id>2 and sex='女')--字查询
select * from mytest where id < all(select id from mytest where id>2 and sex='女')--字查询
< any--小于查询结果的某个值  >=any <=any <>any >any
< all--小于查询结果的所有值  >=all <=all <>all >all
select * from mytest union select * from mytest
--联合查询,自动过滤重复值,获取两表的并集,按第一列进行排序,合并两表的数据,前提两表结构必须相同
select * from mytest union all select * from mytest--联合查询,不过滤重复值,不排序
select * from mytest intersect select * from mytest
--联合查询,自动过滤重复值,获取两表的交集,同时存在的数据，进行排序
select id,[name],[sex],address,age into mytest3 from mytest
--select into语法,将mytest表复制备份到mytest3表中
--行列转换
select * from mytest3
--方法一case函数
select id,address,age,
   (case sex when '男' then [name] else'' end) 男,
   (case sex when '女' then [name] else'' end) 女
from mytest
--方法一case函数
select id,address,age,
   (case  when sex='男' then [name] else'' end) 男,
   (case  when sex='女' then [name] else'' end) 女
from mytest
--方法二pivot函数
select * from mytest pivot(max([name]) for sex in(男,女))a
--方法三left join左连接
select r.id,address,age,男,女 from mytest r
left join
(select id,[name] as 男 from mytest where sex='男') t on r.id=t.id
left join
(select id,[name] as 女 from mytest where sex='女') t2 on r.id=t2.id

                                --高级T_sql
--定义变量
declare @str varchar(30)
set @str='123'     --赋值
print @str         --打印输出
select @str        --表形式输出
--全局变量@@形式
 select @@identity
 select @@connections
--函数
select abs(-23.44)    --取绝对值23.44
select ceiling(34.56) --返回大于等于参数的最小整数35
select floor(34.56)   --返回小于等于参数的最大整数34
select round(2312.32534,2)--四舍五入运算，2为保留的小数位2312.33000
--字符串
select ltrim(' 234')  --去掉左边空格
select charindex('a','s3dda33')--返回字符'a'在字符串's3dda33'中的位置5，从1开始计数
select len('234wefwef')--返回字符串的长度9
select substring('we我rweff',3,5)--从字符串位置3开始截取5个字符返回'我rwef'
select stuff('we我rweff',3,5,'已替换掉了')--从字符串位置3开始替换五个后面参数的字符输出‘we已替换掉了f’
--时间函数
--datepart时间参数表达式
--yy,yyyy(1753-9999)--年份
--qq,q   (1-4)--季度
--mm,m   (1-12)月份
--dy,y   (1-366)一年的第几天
--dd,d   (1-31)一月的第几天
--wk,ww  (1-7)星期几
--hh     (0-23)小时
--mi,n   (0-59)分钟
--ss,s   (0-59)秒
--ms     (0-999)毫秒
select getdate()    --获得当前时间
select dateadd(yy,3,'2011-2-2')--为yy年份添加整数3输出2014-02-02 00:00:00.000
select dateadd(mm,3,'2011-2-2')--为mm月份添加整数3输出2011-05-02 00:00:00.000
--时间差函数
select datediff(yy,'1922-1-3','2011-2-3')--返回两个时间参数的年份的时间差输出89
select datediff(mm,'1922-1-3','2011-2-3')--返回两个时间参数的月份的时间差输出1069
select datename(yyyy,'2011-2-3')         --返回年份
select datename(mm,'2011-2-3')           --返回月份
select datename(ww,'2011-2-3')           --返回星期几
select datepart(ss,'2011-2-3 23:44:09')  --返回秒整数
select day('2011-2-3 23:44:09')          --返回天数
select month('2011-2-3 23:44:09')        --返回月数
select year('2011-2-3 23:44:09')         --返回年数
--系统函数
select cast(233.44 as int)--cast函数强制将整数233.44转换整数233输出
select convert(varchar,getdate(),101) as '101'--convert将时间转换为字符输出
--聚合函数
 sum(),max(),min(),avg(),count(),
begin
end--程序运行范围
--case 表达式语法
select distinct id,address,age,
   (case sex when '男' then [name] else'' end) 男,
   (case sex when '女' then [name] else'' end) 女
from mytest
                             --建立视图
create view v_test
as
select *from mytest --建立视图v_test
                             --建立索引
create index sex on mytest(sex)--在mytest表的sex字段上建立了索引
create unique index id on mytest(id)--在mytest表的id字段上建立了唯一索引
create clustered index sex2 on mytest(id)--在mytest表的id字段上建立了聚集索引
create nonclustered index sex3 on mytest(id)--在mytest表的id字段上建立了非聚集索引
                             --建立存储过程
if exists(select 1 from sys.objects where [name]='insertest' and type='P')
drop procedure insertest
go
create procedure insertest
   @name varchar(30),
   @sex varchar(2)='男',   --默认值为男
   @address varchar(200),
   @age int=25             --指定默认值为25
as
begin
   insert into mytest([name],[sex],address,age) values(@name,@sex,@address,@age)
end
go
exec insertest '李利利','男','榆林路93弄2号211室',90  --exec执行存储过程
                          --返回类型的存储过程
go
if exists(select 1 from sys.objects where [name]='insertest2' and type='P')
drop procedure insertest2
go
create procedure insertest2
   @name varchar(30),
   @sex varchar(4)='男',   --默认值为男
   @address varchar(200),
   @age int=25,            --指定默认值为25
   @id int output         --定义返回类型
as
begin
   insert into mytest([name],[sex],address,age) values(@name,@sex,@address,@age)
   set @id=@@identity      --将自增字段数据赋值给@id并返回  
end
go
declare @putid int
exec insertest2 '王利利','男','XX路',90,@putid output  --exec执行存储过程
print cast(@putid as varchar)                  --将@putid转换为varchar输出             
                            --带事务存储过程
go
if exists(select 1 from sys.objects where [name]='insertest3' and type='P')
drop procedure insertest3
go
create procedure insertest3
   @name varchar(30),
   @sex varchar(2)='男',   --默认值为男
   @address varchar(200),
   @age int=25,            --指定默认值为25
   @id int output          --定义返回类型
as
begin try                  --开始监视程序
   begin tran              --准备事务
    insert into mytest([name],[sex],address,age) values(@name,@sex,@address,@age)
    set @id=@@identity     --将自增字段数据赋值给@id并返回  
   commit tran             --不出错则提交事务
end try                    --结束监视程序
begin catch  
   set @id=-1             --程序出错处理开始
   rollback tran           --程序出错则回滚事务,不执行插入表sql语句
end catch                  --程序出错处理结束

                           --建立触发器
--inserted表，在执行（插入）insert或（更新）update时之前保留的一个副本
--deleted表，存储在（删除）delete或（更新）update时保留所删除数据的一个副本

--                  inserted表与deleted表的关系图

--触发器类型   |         inserted表              |          deleted表
--insert      | 存储所插入的记录的副本           |            无
--update      | 存储更新了的记录的副本           |  存储更新前的原始记录的副本
--delete      |        无                       |  存储所删除的原始记录的副本

if exists(select * from sys.all_objects where [name]='triggtest' and [type]='TR')
drop trigger triggtest
go
create trigger triggtest
on mytest                   --触发器建立对象在mytest表上
for insert,update,delete    --建立插入,修改,删除类型触发器
as
begin
    delete from trigtesttab --先删除监视表trigtesttab
    insert into trigtesttab --插入数据
    select count(name),avg(age),getdate() from mytest
    group by age            --age分组
end
  --测试语句
  insert into mytest([name],[sex],address,age) values('pt58','h','t',55)
  delete from mytest where id='1'

--建立触发器状态监视表
create table trigtesttab
(
namecount int,
ageavg int,
date datetime
)
select * from mytest
select * from trigtesttab
                            --建立列级触发器
if exists(select * from sys.all_objects where [name]='triggtest2' and [type]='TR')
drop trigger triggtest2
go
create trigger triggtest2
on mytest                   --触发器建立对象在mytest上
for update                  --建立修改触发器  
as
if update([name])           --在[name]字段上建立列级修改触发器
begin
  print'不能修改[name]列'
  rollback tran
end
--测试
update mytest  set [name]='123' where id='3'    --无法修改成功

--删除具有主外键关系的触发器
create table tuser
(
id int primary key identity(1,1),
uname varchar(50),
utele varchar(100)
)
go
create table torder
(
id int primary key identity(1,1),
pname varchar(50),
price decimal(5,2),
number int,
tuser_id int foreign key  references tuser(id)--ON DELETE CASCADE 加上关键字可自动连级删除
)
go
insert into tuser values('yi','1342424425')
insert into tuser values('yibin','13436546747')
insert into tuser values('yijinrui','13436546747')
go
insert into torder values('net编程','213','1',1)
insert into torder values('PHP编程','123','1',1)
insert into torder values('Java编程','323','1',2)
insert into torder values('Vb编程','323','1',2)
insert into torder values('jQuery编程','323','2',2)
go
select * from torder
select * from tuser
go
select uname,pname,price,number from torder,tuser where torder.tuser_id=tuser.id

go
create procedure insertuser
   @uname varchar(50),
   @utele varchar(100)
as
   insert into tuser values(@uname,@utele)
go
exec insertuser 'li','11133544'
go
drop trigger deletetuser on tuser
instead of delete         --建立instead of类型的delete触发器替代delete执行语句
as
declare @uid int          --定义tuser的id变量
begin  try
 select @uid=id from deleted   --获得删除tuser表的id
 delete from torder where tuser_id=@uid  --删除torder表的引用id号@uid
 delete from tuser where id=@uid     --执行主表的删除动作
end try
begin catch
   rollback tran
end catch
go
--测试
delete from torder where id=1
delete from tuser where id=1
go
year | quarter | month | week | day | hour | minute | second | millisecond
declare @date varchar,@date2 varchar
set @date='2011-2-12 6:34:06'
set @date2='2012-12-10'
select cast(datediff(year,@date,@date2) as varchar)+'年'+
       cast(datediff(month,'2011-2-12 6:34:06','2012-12-10') as varchar)+'月'+
       cast(datediff(day,'2011-2-12 6:34:06','2012-12-10') as varchar)+'天'+
     cast(cast(datediff(hour,'2011-2-12 6:34:06','2012-12-10') as int)%24 as varchar)+'小时'+
     cast(cast(datediff(minute,'2011-2-12 6:34:06','2012-12-10') as int)%60 as varchar)+'分'+
     cast(cast(datediff(second,'2011-2-12 6:34:06','2012-12-10') as int)%60 as varchar)+'秒'
 as 时间差 from tuser