83 lines
3.0 KiB
MySQL
83 lines
3.0 KiB
MySQL
|
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD>AuthorityDB sql
|
|||
|
|
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'MACLogs') and xtype='U')
|
|||
|
|
drop table MACLogs
|
|||
|
|
go
|
|||
|
|
create table MACLogs(
|
|||
|
|
Id int identity(1,1),
|
|||
|
|
HostsId int not null,
|
|||
|
|
MAC nvarchar(50) null,
|
|||
|
|
userid int not null,
|
|||
|
|
location nvarchar(100) null,
|
|||
|
|
Ip nvarchar(50) null,
|
|||
|
|
ActionId INT null,
|
|||
|
|
[type] int default 0, -- 0 <20><><EFBFBD><EFBFBD> 1 <20><><EFBFBD> 2 <20><><EFBFBD>İ<EFBFBD>
|
|||
|
|
CreateTime datetime default getdate()-- 0 <20><><EFBFBD><EFBFBD> 1 <20><><EFBFBD>
|
|||
|
|
)
|
|||
|
|
go
|
|||
|
|
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD>AuthorityDB sql
|
|||
|
|
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'ErrorInfo') and xtype='U')
|
|||
|
|
drop table ErrorInfo
|
|||
|
|
go
|
|||
|
|
create table ErrorInfo(
|
|||
|
|
Id int identity(1,1),
|
|||
|
|
HostsId int not null,
|
|||
|
|
MAC nvarchar(50) null,
|
|||
|
|
userid int not null,
|
|||
|
|
[type] int default 0, -- 0 <20><><EFBFBD>䱻<EFBFBD><E4B1BB> 1 MAC<41><43><EFBFBD><EFBFBD>
|
|||
|
|
[status] int default 0,--<EFBFBD>Ƿ<EFBFBD><EFBFBD>Ѿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> 0 δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> 1 <EFBFBD>Ѿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CreateTime datetime default getdate()-- ʱ<><CAB1>
|
|||
|
|
)
|
|||
|
|
go
|
|||
|
|
|
|||
|
|
select * from Hosts where id = 14939
|
|||
|
|
|
|||
|
|
SELECT * FROM MACLogs
|
|||
|
|
if EXISTS(SELECT 1 FROM sys.views WHERE name='View_MAClog')
|
|||
|
|
begin
|
|||
|
|
drop view View_MAClog
|
|||
|
|
end
|
|||
|
|
go
|
|||
|
|
create view View_MAClog
|
|||
|
|
as
|
|||
|
|
select m.*,u.[Uid] ,ht.Name,Hotelsid = ht.Id
|
|||
|
|
from MACLogs m
|
|||
|
|
left join Hotels ht on ht.id = m.HotelID
|
|||
|
|
left join UserInfo u on u.id = m.userid
|
|||
|
|
go
|
|||
|
|
select *,CreateTime from MACLogs
|
|||
|
|
SELECT * FROM View_MAClog
|
|||
|
|
SELECT * FROM ErrorInfo --WHERE
|
|||
|
|
SELECT * FROM UserInfo WHERE ID = 212
|
|||
|
|
--<EFBFBD><EFBFBD> sql
|
|||
|
|
select * from tb_Hosts where hotelid = 3
|
|||
|
|
|
|||
|
|
update tb_Hosts set MAC=MAC where MAC= '34-D0-B8-11-10-4D';update tb_Hosts set MAC='34-D0-B8-11-10-4D' where Id = 14810;
|
|||
|
|
DECLARE @MAC int;
|
|||
|
|
DECLARE @ID int;
|
|||
|
|
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD> @MAC <EFBFBD>µ<EFBFBD>MAC @ID Ϊ<EFBFBD><EFBFBD>Ҫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>mac<EFBFBD><EFBFBD>id
|
|||
|
|
begin tran
|
|||
|
|
DECLARE @HostsId int;
|
|||
|
|
DECLARE @oldmac nvarchar(50);
|
|||
|
|
DECLARE @HostsIdoldmac nvarchar(50);
|
|||
|
|
SET @HostsId = -1;
|
|||
|
|
SET @oldmac = '-1';
|
|||
|
|
SET @HostsIdoldmac = '-1';
|
|||
|
|
SELECT TOP 1 @HostsId = id, @oldmac= MAC FROM tb_Hosts where MAC = @MAC
|
|||
|
|
SELECT TOP 1 @HostsIdoldmac = MAC FROM tb_Hosts where Id = @ID and mac is not null and mac!=''
|
|||
|
|
PRINT convert(varchar,@HostsId) +'&'+ @oldmac+'&'+@HostsIdoldmac
|
|||
|
|
update tb_Hosts set MAC='' where id = @HostsId
|
|||
|
|
update tb_Hosts set MAC=@MAC where Id = @ID
|
|||
|
|
if @@ERROR>0
|
|||
|
|
begin
|
|||
|
|
rollback tran
|
|||
|
|
end
|
|||
|
|
ELSE
|
|||
|
|
BEGIN
|
|||
|
|
COMMIT TRAN
|
|||
|
|
END
|
|||
|
|
|
|||
|
|
;
|
|||
|
|
|
|||
|
|
go
|
|||
|
|
|
|||
|
|
select * from Hosts where Id = 14939
|