MS-SQL 基本语法大全

--创建新表 testTable1--
--字段Id int类型 主键自增 非空，
--字段name nvarchar类型(50)长度 可以为空，
--字段age int类型 非空 默认值0
 create table testTable1 ( Id int not null primary key identity(1,1), name nvarchar(50) null, age int not null default 0 ) 

--初始化table还原主键Id-- 
truncate table testTable1 

--根据旧表创建新表--
select * into newtable from testTable1 where 1<>1 

--拷贝表数据-- 
insert into a(a, b, c) select d,e,f from b 

--使用between查询数据范围--
select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2

--两张关联表，删除主表中已经在副表中没有的信息--
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

--日程安排提前五分钟提醒 -- 
select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 

--随机取出10条数据-- 
select top 10 * from tablename order by newid() 

--选择从10到15的记录-- 
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

--SQL SERVER中直接循环写入数据-- 
declare @i int set @i=1 while @i<30 begin     insert into test (userid) values(@i)     set @i=@i+1 end 

--按姓氏笔画排序,从少到多- 
Select * From testTable1 Order By name Collate Chinese_PRC_Stroke_ci_as 

--表name列的内容按拼音a-z排序-- 
select * from testTable1 order by name collate chinese_prc_cs_as_ks_ws 

--查看硬盘分区-- 
EXEC master..xp_fixeddrives 

--获取当前数据库中的所有用户表--
select Name from sysobjects where xtype='u' and status>=0 

--获取某一个表的所有字段-- 
select name from syscolumns where id=object_id('testTable1') select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'testTable1') 

--查看当前数据库中所有存储过程-- 
select name as 存储过程名称 from sysobjects where xtype='P'


--创建带参数 和 事务回滚的存储过程--  
if (object_id('proc_find_stu', 'P') is not null)  
    drop proc proc_find_stu     --判断存储过程名称是否存在，如果存在就drop 
go
create proc proc_find_stu(@startId int, @endId int)  --创建存储过程
as
  begin transaction tran1    -- 事务开始
  save transaction tran1     --保存事务
    select * from student where id between @startId and @endId   --数据操作
  commit transaction tran1   --提交事务
  if(@@ERROR<>0)       -- 判断是否有错
  begin
     raiserror('Select data error 100054!',16,1)  --自定义错误输出
     rollback transaction tran1        -- 事务回滚
  end
go

--调用、执行存储过程
exec proc_find_stu 2, 4;

--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;
--调用、执行存储过程
exec pro_stu 2, 2;


--创建insert插入类型触发器--
USE [数据库名] 
GO 
/****** Object:  Trigger [dbo].[tgr_insert]    Script Date: 08/15/2013 17:22:57 ******/
 if (object_id('tgr_insert', 'TR') is not null) 
/**如果已经存在这个触发器名就先删除**/
     drop trigger tgr_insert go
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 create trigger [dbo].[触发器名] 
   ON  [dbo].[表1]   for insert 
AS
  declare @rowcount int
  
  select @rowcount = @@rowcount
if@rowcount=1
  BEGIN
declare @变量1 int
select @变量1=主键ID from inserted 
insert into 表2 select 字段1,字段2,字段3 from 表1 where ID=@变量1
  END
if@rowcount>1
BEGIN
   rollback transaction        --事务回滚
   raiserror('每次只能操作一条记录',16,1)  --自定义错误输出
   
 END
GO

--创建delete删除类型触发器-- 
USE [数据库名] 
GO 
/****** Object:  Trigger [dbo].[tgr_del]    Script Date: 08/15/2013 17:25:18 ******/
 SET ANSI_NULLS ON
 GO

SET QUOTED_IDENTIFIER ON
 GO

create trigger [dbo].[触发器名] on [dbo].[表1] for delete
 as 
begin
 declare @变量1 int
 select @变量1=主键ID from deleted 
delete 表2 where ID=@变量1 
end 
GO

--创建update更新类型触发器-- 
USE [表名] 
GO

/****** Object:  Trigger [dbo].[tgr_update]    Script Date: 08/15/2013 17:33:10 ******/ 
SET ANSI_NULLS ON
 GO

SET QUOTED_IDENTIFIER ON 
GO

CREATE trigger [dbo].[触发器名] on [dbo].[表1] for update
 as 
if(UPDATE(字段2)(字段3)) 
begin 
declare @变量1 int, @变量2 nvarchar(50) @变量3 nvarchar(50) 
select @变量1=主键ID,@变量2=字段2,@变量3=字段3 from inserted
 update 表2 set 字段2=@变量2,字段3=@变量3 where ID=@变量1 
end 
GO

--新建标量值函数--  
create function FUNC_Sum1  (      @a int,      @b int ) 
 returns int 
as 
begin   
  return @a+@b 
 end 
--新建内联表值函数
  create function FUNC_UserTab_1  (     @myId int )
  returns table as return (select * from ST_User where ID<@myId) 
--新建多语句表值函数  
create function FUNC_UserTab_2  (      @myId int ) 
 returns @t table (     [ID] [int] NOT NULL,     [Oid] [int] NOT NULL,     [Login] [nvarchar](50) NOT NULL,     [Rtx] [nvarchar](4) NOT NULL,     [Name] [nvarchar](5) NOT NULL,     [Password] [nvarchar](max) NULL,     [State] [nvarchar](8) NOT NULL )  
as
 begin  
   insert into @t select * from ST_User where ID<@myId 
    return
 end 
--调用表值函数 
select * from dbo.FUNC_UserTab_1(15) 
--调用标量值函数
  declare @s int set @s=dbo.FUNC_Sum1(100,50) 
 print @s  
--删除标量值函数
  drop function FUNC_Sum1
--查询用户创建的所有数据库--
 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 
或者 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 

--查询某一个表的字段和数据类型-- 
select column_name,data_type from information_schema.columns where table_name = 'testTable1' 

--查询所有汉字存到#t-- 
select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b 
--查询#t的所有汉字按笔画排序-- 
select code,nchar(code) as cnword  from #t  order by nchar(code) collate chinese_prc_stroke_cs_as_ks_ws,code 

--条件语句--
 --if,else条件分支
  if(1+1=2)  
begin    
 print '对' 
end 
else
 begin  
   print '错' 
end 
--when then条件分支
  declare @today int declare @week nvarchar(3)  set @today=3  set @week=case 
    when @today=1 then '星期一' 
    when @today=2 then '星期二'
     when @today=3 then '星期三'
     when @today=4 then '星期四' 
    when @today=5 then '星期五' 
    when @today=6 then '星期六'
     when @today=7 then '星期日' 
    else '值错误' 
end
 print @week

--自定义排序

select * from 表名 order by CHARINDEX(','+CONVERT(varchar(10),字段名)+',','9,8,7,6,5,4,3,2,1,')

--查找A表内重复的数据
select ID,Name from A where ID in (select ID from A group by ID having count(1) >= 2)

 --计算产品分类指定时间销售总额
select c.TypeName,SUM(a.saleCount*b.Prices) as '04年到06年每个产品的销售总金额' from tb_sale a
 join tb_Price b on a.TypeId=b.TypeId join tb_Type c on b.TypeId=c.TypeId 
where a.saleDate between '2004-01-01 11:06:08.000' and '2006-12-12 19:11:08.000' group by c.TypeName