SQL位运算
select 2|8 --10
select 2|8|1 --11
select 10&8 --8,包含,10=8+2
select 10&2 --2,包含,10=2+8
select 10&4 --0,不包含
select 19&16 --16,包含,19=16+2+1
select 19&8 --0,包含,19=16+2+1
select * from SqlBitOperation where FBitTags&4=4
在权限中的应用
1、两张表
（1）、操作与权限标志表
if exists (select * from sysobjects where id = OBJECT_ID('[RightTags]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [RightTags]
CREATE TABLE [RightTags] (
[Pkid] [int] IDENTITY (1, 1) NOT NULL,
[RightsName] [nvarchar] (50) NOT NULL,
[RightsTag] [int] NOT NULL DEFAULT (0))
ALTER TABLE [RightTags] WITH NOCHECK ADD CONSTRAINT [PK_RightTags] PRIMARY KEY NONCLUSTERED ( [Pkid] )
SET IDENTITY_INSERT [RightTags] ON
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 1,'吃饭',1)
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 2,'拉屎',2)
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 3,'打炮',4)
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 4,'泡妞',8)
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 5,'生子',16)
INSERT [RightTags] ([Pkid],[RightsName],[RightsTag]) VALUES ( 6,'包二奶',32)
SET IDENTITY_INSERT [RightTags] OFF
（2）、用户与权限表
if exists (select * from sysobjects where id = OBJECT_ID('[RightUsers]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [RightUsers]
CREATE TABLE [RightUsers] (
[Pkid] [int] IDENTITY (1, 1) NOT NULL,
[FUser] [nvarchar] (50) NOT NULL,
[UserRights] [int] NOT NULL DEFAULT (0))
ALTER TABLE [RightUsers] WITH NOCHECK ADD CONSTRAINT [PK_RightUsers] PRIMARY KEY NONCLUSTERED ( [Pkid] )
SET IDENTITY_INSERT [RightUsers] ON
INSERT [RightUsers] ([Pkid],[FUser],[UserRights]) VALUES ( 1,'张三',7)
INSERT [RightUsers] ([Pkid],[FUser],[UserRights]) VALUES ( 2,'李四',9)
INSERT [RightUsers] ([Pkid],[FUser],[UserRights]) VALUES ( 3,'王老板',63)
INSERT [RightUsers] ([Pkid],[FUser],[UserRights]) VALUES ( 4,'朱麻子',0)
SET IDENTITY_INSERT [RightUsers] OFF
2、访问判断是否有权限
--1.查询权限
declare @iRights as int
select @iRights=(select top 1 RightsTag from RightTags where RightsName='打炮')
--print @iRights
select * from RightUsers where UserRights&@iRights=@iRights --所有可以打炮的人
select * from RightUsers where FUser='李四' and UserRights&@iRights=@iRights --此人是否可以打炮(有无记录集)
select * from RightUsers where FUser='王老板' and UserRights&@iRights=@iRights --此人是否可以打炮(有无记录集)
go
3、设置权限
--2.设置权限
declare @yourRights int
select @yourRights=(select sum(RightsTag) from RightTags where RightsName in('吃饭','拉屎','打炮'))
--print @yourRights
update RightUsers set UserRights=@yourRights where FUser='张三'
go
4、查看此人的所有权限
--3.查看此人的所有权限
declare @yourRights as int
select @yourRights=(select UserRights from RightUsers where FUser='李四')
print @yourRights
select * from RightTags where @yourRights&RightsTag=RightsTag
go
5、检测权限表权限值是否为2的整数次方(操作权限标志的有效性:唯一+2的整数次方)
select power(2,6) --64
select power(2.000,5.500) --45.255
select log(64.000)/log(2.000)--6.0
------------------------------
declare @setNewOperateRightTags int --添加一个新操作权限
set @setNewOperateRightTags=64 --64是2的整数次方
--set @setNewOperateRightTags=63 --63不是2的整数次方
declare @number float
set @number=log(@setNewOperateRightTags)/log(2.000)
--print @number
if(ceiling(@number)=floor(@number))--取最大整数=取最小整数
begin
print cast(@setNewOperateRightTags as varchar(16)) + '是2的整数次方,可以用作权限标志值'
end
else
begin
print cast(@setNewOperateRightTags as varchar(16)) + '不是2的整数次方,不能用作权限标志值'
end
------------------------------