基础
--先复习sql基础
--查询语句执行顺序
--from→where→group by→having→select(column(列)→distinct→top)
select * from Info;
--将数据取出存放到临时表中 #temp局部临时表 ##temp全局临时表
select * into #temp from(select * from Info) as t;
--查询临时表
select * from #temp;
--删除临时表
drop table #temp;
--删除数据
delete from Info where uId>1000000
--与delete区别:truncate删除数据快,会重置自增长主键的默认值,不触发delete触发器
truncate table Info
--变量
--用@来声明局部变量 先声明,后赋值 如:@Id
--用@@来声明全局变量 内置常用的有:@error 返回错误号
-- @identity 返回上次插入的主键Id、
-- @rowcount 返回上次操作影响的行数
--定义变量并初始化值不然,就是null
declare @a int set @a=0
print @a
declare @b int set @b=1
print @b
declare @sum int set @sum=@a+@b
print @sum
--if语句
if @sum>@a
begin
print '城管强'
end
else
begin
print '拆迁队牛'
end
--while语句
declare @value int set @value=0
declare @sum int set @sum=0
while @a<100
begin
set @a=@a+1
set @sum=@sum+@a
end
print @sum
上面主要是为了存储过程、及游标做准备的!
视图
--先复习sql基础
--查询语句执行顺序
--from→where→group by→having→select(column(列)→distinct→top)
select * from Info;
--将数据取出存放到临时表中 #temp局部临时表 ##temp全局临时表
select * into #temp from(select * from Info) as t;
--查询临时表
select * from #temp;
--删除临时表
drop table #temp;
--删除数据
delete from Info where uId>1000000
--与delete区别:truncate删除数据快,会重置自增长主键的默认值,不触发delete触发器
truncate table Info
--变量
--用@来声明局部变量 先声明,后赋值 如:@Id
--用@@来声明全局变量 内置常用的有:@error 返回错误号
-- @identity 返回上次插入的主键Id、
-- @rowcount 返回上次操作影响的行数
--定义变量并初始化值不然,就是null
declare @a int set @a=0
print @a
declare @b int set @b=1
print @b
declare @sum int set @sum=@a+@b
print @sum
--if语句
if @sum>@a
begin
print '城管强'
end
else
begin
print '拆迁队牛'
end
--while语句
declare @value int set @value=0
declare @sum int set @sum=0
while @a<100
begin
set @a=@a+1
set @sum=@sum+@a
end
print @sum
存储过程
--存储过程是一段可执行服务端程序(类似方法)
--优点:执行速度更快;允许模块化程序设计(复用);提高系统安全(防止SQL注入);减少网络流通量
create proc usp_UserDefineProcedure
@paremeterA int,
@paremeterB int,
@totalSum int output
as
begin
set @totalSum=@paremeterA+@paremeterB
if(@totalSum < 10)
begin
print 'so easy!'
end
else
begin
print 'too difficult'
end
end
--声明变量
declare @Sum int
exec usp_UserDefineProcedure 1,8,@totalSum = @Sum OUTPUT --执行存储过程 有参数的后跟参数(有输出参数 要先定义变量)
print @Sum
--删除存储过程
drop proc usp_UserDefineProcedure
事务
--事务允许你定义一个操作单元,要么全部成功,要么全部失败
--开启事务
begin tran
declare @error int =0 --声明一个局部变量 接收全局@error
set @error=@error+@@ERROR
update TblScore set tEnglish=59 where tScoreId=1
set @error=@error+@@ERROR
if(@error<>0)
begin
rollback tran;
print 'update failed'
end
else
begin
commit tran;
print 'success'
end
游标
--简单使用
--定义游标 有多种游标 fast_forward for/
declare cur_MyInfo cursor fast_forward for select * from Info
--打开游标
open cur_MyInfo
--对游标进行操作
--单行操作
--fetch next from cur_MyInfo
--多行操作
--@@FETCH_STATUS说明 0代表 fetch语句执行成功 -1fetch语句失败或行不在结果集中 -2提取的行不存在
while @@FETCH_STATUS=0
begin
fetch next from cur_MyInfo
end
--关闭游标
close cur_MyInfo
--释放游标
deallocate cur_MyInfo
多表连接加分页查询
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY tree.Id ) AS rownum ,
tree.Id AS tid ,
tree.Name AS NAME ,
inde.Info AS info ,
inde.Title AS title
FROM dbo.T_Tree AS tree
INNER JOIN dbo.T_IndexInfo AS inde ON tree.Id = inde.Id
) AS t
WHERE rownum BETWEEN 1 AND 2
常用的分页语句
--取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的
--以上免为例(很多面试都会有这道题)
--第一种 not in
--先查询一部分数据排序,排除在外;适合用与sql server 2000及更高版本
select top 10 * from Info where uId not in (select top 30 uId from Info order by uId)order by uId
--第二种 先查出一部分数据 排序 用Max找出最大Id 然后将其Max(uId)最大排除在外;适合用与sql server 2000及更高版本
select top 10 * from Info
where uId >
(
select ISNULL(MAX(uId),0)
from
(
select top 30 uId from Info order by uId
) a
)
order by uId
--第三种 row_number
--把所有的数据查询出来,进行重新编号,通过where条件进行筛选数据;适用于sql server2005及更高版本
select top 10 * from (select row_number() over (order by uId) as rowId,* from Info)t where rowId>30
--第四种 offset fetch
--offset后参数 越过多少条 fetch next后参数 取多少条;适用于sql server2012
select * from Info order by uId offset (30)row fetch next 10 rows only
下面表格简单数据量不大的测试
方式 | 取1000排除10000 | 取1000排除100000 | 取1000排除500000 | 取10000排除100000 | 取10000排除500000 | 取10000排除900000 |
---|---|---|---|---|---|---|
not in | 75 | 133 | 1085 | 377 | 719 | 1035 |
max | 82 | 76 | 236 | 205 | 270 | 1037 |
row_number | 126 | 955 | 662 | 1040 | 4785 | 2618 |
offset fetch | 79 | 407 | 186 | 180 | 239 | 882 |
offset要比row_number/not in/max方式要好,简洁更给力,not in/max(更通用)
小数据的情况下 max最好 offset次之 紧跟not in 最后row_number