alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from dbo.Operate
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from dbo.Operate )ta where newid =@num
if(@num>1)
set @ctitle+=@title+','
else
set @ctitle+=@title
set @num=@num-1
end
Declare @sql varchar(8000)
set @sql='select * from(select 1 "isture",rolename,modulename,operatename,role_ID,Module_id from vw_userrole group by rolename,modulename,operatename,role_ID,Module_id)a pivot(count(isture)for operatename in('+@ctitle+')) b'
exec(@sql)
分页器存储过程,分页存储过程
复制代码 代码如下:
alter proc cutpage
(
@tablename nvarchar(100),----分页的表
@columnname nvarchar(100), ----分页的列
@ordertype nvarchar(100)='asc', ----排序方式
@pageindex int =1,
@pagecount int =1
)
as
begin
declare @aa nvarchar(max);
set @aa= 'select * from
(select *,ROW_NUMBER() over(order by '+@columnname+' '+@ordertype+') as uprow from '+@tablename+' ) as newtable
where uprow between '+cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(nvarchar(100), @pageindex*@pagecount)
exec (@aa) --这里@aa必须加括号（）
end
exec cutpage 'SM_Class', 'classid'
事务 Transaction
复制代码 代码如下:
---事务关键语句讲解----
BEGIN TRANSACTION ---事务关键字transaction
DECLARE @errorSum INT
SET @errorSum=0 --初始化为，即无错误
update bank SET money=money+1000 where
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where
SET @errorSum=@errorSum+@@error --累计是否有错误(@@error是非零)
if @errorSum<>0
begin
print '不成功，有错误,错误代号是：'
print @errorsum
rollback transaction
end
else
begin
print '成功'
select * from Bank
commit TRANSACTION
end
触发器 Trigger
复制代码 代码如下:
--概念：一种特殊的存储过程,将本表中的数据更改(增删改)，就会自动执行实现定义的语句
--特点：跨越相关表的级联修改
--关键字：trigger
alter trigger trigger_name_f
on SM_Class
for update --(for是在增删改之前执行after在增删改之后执行，instead of 是所有【增|删|改】都不执行,之前触发，但不改值)
as
begin
if update(remark) ---判断SM_Class表中remark列是否发生数据变化
begin
select * from inserted ---存放修改的新值的表
select * from deleted ----存放修改的旧值的表
print 'remark列发生更改'
end
else
print '其他列发生变化'
print '测试触发器，当修改表SM_Class时，显示这句话,且for时这句话在前'
end
游标 Cursor
复制代码 代码如下:
--游标类似于sql dateReader 一行一行的读取
--一般是在万不得已的情况下使用, 时间长,服务器压力大,吃更多的内存,宽带,
--游标是对一个集合的遍历读取
declare cur_test cursor
for
select pid,pname from ns_product
open cur_test
declare @id uniqueidentifier;
declare @name nvarchar(50);
--读取一行(第一次读取当然是第一行了)
fetch next from cur_test into @id,@name
--判断一下是否读到数据了,状态为零说明读到数据了(@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
--然后读取下一行
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test
零散知识点
1)截取字符串: substring(字段名, 起始位置(首位为1),截取的长度 )
select SUBSTRING(age,1,1) from person where ID=2
2)关于GUID:
select NEWID()
insert person values('','',NEWID())
3)将一张表(person2)插入到另一张表(person1)中( 列数要对应)
insert person1
select列,列,列 from person2
4)条件语句 (注意: C#中的大括号{}在sql中用begin end 代替 )
复制代码 代码如下:
declare @x int ,@y int
set @x=1
set @y =2
if @x>@y
print 'x>y'
else
print 'x<y'
select code,name,grade,
case Grade
when '1' then '省'
when '2' then '市'
when '3' then '县'
end '等级'
from SM_PostCode
-------------------------------------------------------------
while (select MAX(DEGREE) from SCORE)<85
begin
if (select MAX(DEGREE) from SCORE where CNO='3-105')>=100
break
end
5)判断是否存在if exists( select * from TBName where CityName='22')
6) 添加自动增长列row_number over(order by **) ROW_NUMBER是关键字 over指根据哪一列排序
select ROW_NUMBER() over(order by classID) ,* from SM_Class
select rank() over(order by classID) ,* from SM_Class ---也是自动增长的行号，如果出现重复会并列行号，下一个值会自动加二
--一条查询语句返回的结果作为另外一条查询语句的数据源表
select * from ( select ROW_NUMBER() over(order by 列名1)此处新列名, * from TBName) 此处新表名where 新列名between 1 and 3
7)临时表
declare @table table(id uniqueidentifier,name varchar(50))
--执行插入操作(插入条数据),并将这条数据的hid字段返回并插入到@table表id属性中
insert images(Hname,Himage) output inserted.Hid into @table(id) values(@hname,@Himage)
declare @picid uniqueidentifier
select @picid=id from @table
------------------------------------------------------------
--下面是执行效率的对比
--sql 语句一：执行需要s
declare @tempTable table(id varchar(20),name int,score datetime)
insert @tempTable(id,name,score)
select userID,userName,userScore from scoreTable
select * from @tempTable
--sql 语句二：执行只需要s
DROP TABLE #Bicycles
SELECT userID,userName,userScore
INTO #Bicycles
from scoreTable
select * from #Bicycles
8)关于日期时间的操作
--数据库中获取北京时间和国际时间
select getdate(), getutcdate()
--时间的增加（增加的类型[年/月/日]，增量，给谁加[当前时间/日期]）dateAdd
select dateadd(YEAR,2,GETDATE()) ----将当前的年份加上两年
--时间的减法DateDiff
select DATEDIFF(HOUR,getdate(),getutcdate()) --国际时间的小时减去当 前北京时间的小时（后边减前边）
--获取时间中的年份, 月份, 天等同理
select year(getdate())-year(birthday())
select year(getdate())-year('1988-10-07')
9)行列转换
select * from ( select * from TableName) a pivot(count(stuName)) for columnName in('aa','bb','cc','dd')
10) 双引号只能用于表名和列名(不加双引号也可以)
set @aa='select ClassName "sd" from SM_Class' --注意：'' 里面原来的'sd' 现在要写成"sd"
exec (@aa)
-----------------这里要多加注意------------------------------
declare @bb nvarchar(max);
--在使用数据值时只能用''电商二班''
set @bb ='select * from SM_Class where ClassName=''电商二班''' --注意：原来的'电商二班'要写成''电商二班''
exec (@bb)
11) --快速创建表结构
select c.Cid,c.Ccount into newTB1 from ns_comment c where 1<>1
12) --重点再记一下
declare @na nvarchar(10),@str nvarchar(max);
set @str=' select top 1 @bb=ClassID from SM_Class '
--@str包含SQL语句的变量，对@str中的变量进行定义N标明是字符串，用来接收@str中变量的变量(即@na=@bb)
exec sp_executesql@str, N'@bb nvarchar(10) output',@na output
select @na,@str
13) -------------并发问题--------
--概念：多个用户同时和一个对象(库,表)交互
--出现问题：脏数据,非重复读取,丢失更新,幻影读取）
--解决：SQL Server使用锁来确保事务完整性（共享锁,排它锁,更新锁,意向锁,架构锁,批量更新锁）
14)
您可能感兴趣的文章: