Sql在一万条数据中无增长段查询第三千到五千数据
SELECT * FROM (SELECT row_number() over(ORDER BY createDt ASC) 'RN',* FROM dbname) t WHERE RN BETWEEN 3000 AND 5000
在一万条数据中,增长字段ReeId,找出表的第31到第40条记录
select top 10 ReeId from dbname where ReeId not in(select top 30 ReeId from dbname)
Sql分组条件查询
SELECT tname, SUM(tscor)
FROM dbo.testScore
GROUP BY tname
HAVING SUM(tscor)>100
Sql创建视图
CREATE VIEW view_name
AS
<select语句>
IF EXISTS (SELECT * FROM view_name /*检测是否存在*/
name = 'view_name')
DROP VIEW view_name /*删除视图*/
GO
CREATE VIEW...
SQl创建存储过程为了提高语句执行性能,对数据库进行复杂操作时(如对多个表同时进行Update,Insert,Query,Delete时
CREATE PROC selectUser
AS
BEGIN
SELECT * FROM users WHERE age>15
END
--执行存储过程的语句
exec selectUser
事务
Eg:
create proc qiantaoProc
@asd nchar(10)
as
begin
begin try
begin tran innerTrans
save tran savepoint --创建事务保存点
insert into shiwu (asd) values (@asd);
commit tran innerTrans
end try
begin catch
rollback tran savepoint --回滚到保存点
commit tran innerTrans
end catch
end
go
begin tran outrans
exec qiantaoProc 'asdasd';
rollback tran outrans
Eg:
---开启事务
begin tran
--错误扑捉机制,看好啦,这里也有的。并且可以嵌套。
begin try
--语句正确
insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)
--加入保存点
save tran pigOneIn
--Numb为int类型,出错
insert into lives (Eat,Play,Numb) values ('猪肉','足球',2)
--语句正确
insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)
end try
begin catch
select Error_number() as ErrorNumber, --错误代码
Error_severity() as ErrorSeverity, --错误严重级别,级别小于10 try catch 捕获不到
Error_state() as ErrorState , --错误状态码
Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
Error_line() as ErrorLine, --发生错误的行号
Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务
rollback tran ---由于出错,这里回滚事务到原点,第一条语句也没有插入成功。
end catch
if(@@trancount>0)
rollback tran pigOneIn --如果成功Lives表中,将会有3条数据。
--表本身为空表,ID ,Numb为int 类型,其它为nvarchar类型
select * from lives
在5秒内运行2个事务模块
begin tran
update Earth set Animal='老虎'
waitfor delay '0:0:5' --等待5秒执行下面的语句
update lives set play='羽毛球'
commit tran
select * from lives
select * from Earth