--创建新表 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