create database lcsy_setting
go
Use lcsy_setting
go
----用户
create table lcsy_settingAccount
(
id int identity(1,1) primary key,--用户主建
SellerId bigint,--卖家ID
SellerNick nvarchar(300), --卖家昵称
LoginName nvarchar(50),--登录名
LoginPass nvarchar(200),--密码
--roleNumber int not null,-- 给用户分角色role
rid int,-- 给用户分角色role
status int ,--状态
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(1,'tom',1)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(2,'mary',2)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(3,'yy',3)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(4,'tt',3)
--查询用户
select * from lcsy_settingAccount
------2.编写用户表,的增删改查接口。
--(1)根据用户名称和用户ID返回用户信息。以json格式返回。
select * from lcsy_settingAccount where SellerId=1 and SellerNick='tom'
-- (2)根据用户id 和用户名称修改用户信息。
update lcsy_settingAccount set SellerNick='tom1' where SellerId=1 and SellerNick='tom'
---角色role
create table lcsy_SettingRole
(
rid int identity(1,1) primary key, --角色
--roleNumber int not null,--角色的编号
RoleName varchar(300),--角色的名称
pid int,--权限表
PType as rid,----权限类型(不同的角色有不同的权限)
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_SettingRole(RoleName) values('超级管理员')
insert into lcsy_SettingRole(RoleName) values('管理员')
insert into lcsy_SettingRole(RoleName) values('普通用户')
insert into lcsy_SettingRole(RoleName) values('VIP')
select * from lcsy_SettingRole
--查询用户的角色
select * from lcsy_SettingRole sr,lcsy_settingAccount sa
where sa.rid=sr.rid
--权限
create table lcsy_SettingPrivilege
(
pid int identity(1,1) primary key, --权限的主键
PType int not null,--角色的编号 权限(不同的角色有不同的权限)
status varchar(200),--0/1 是否可用
SellerId bigint,--卖家ID
SellerNick nvarchar(300), --卖家昵称
mtype int ,--菜单(不同的权限有不同的菜单)famquanxian
modName varchar(300),--模块名称
picture varchar(1000),--图片大
minipicture varchar(1000),--图片小
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
---给用户开权限
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,1)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,2)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,3)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,4)
---插入数据到权限表。
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(3,3, 1,4)
select * from lcsy_SettingPrivilege
--查询不同角所拥有的权限 (开权限)
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId
--=========
---根据用户ID查询该用户的所有应用Id
select SellerId,SellerNick,rid from lcsy_settingAccount
select rid,PType from lcsy_SettingRole --分配角色和设置类型
select pid,PType,status,mtype from lcsy_SettingPrivilege
select meid,mtype from lcsy_SettingMenu
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sp.mtype,sp.status
from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sa.SellerId=3
---根据用户Id更新用户的权限。
select * from lcsy_SettingPrivilege
update lcsy_SettingPrivilege set mtype=3 where SellerId=3 and pid=5
---插入数据到权限表。(开权限)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(4,3, 1,4)
--==========
---菜单表
create table lcsy_SettingMenu
(
meid int identity(1,1) primary key,--菜单的主键
mtype varchar(200),--菜单的权限设置
mename varchar(200),--菜单的名称
Url nvarchar(300),--url
status varchar(200),--0/1 是否可用
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
select * from lcsy_SettingMenu
select
sa.SellerId,
sp.picture,
sp.minipicture,
sp.modName,
sm.mename ,
sm.mtype,
sm.Url,
sm.described
from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
insert into lcsy_SettingMenu(mtype,mename)values(1,'定时分析')
insert into lcsy_SettingMenu(mtype,mename)values(1,'定制报告')
insert into lcsy_SettingMenu(mtype,mename)values(2,'趋势分析')
insert into lcsy_SettingMenu(mtype,mename)values(2,'活跃用户')
insert into lcsy_SettingMenu(mtype,mename)values(2,'留存用户')
insert into lcsy_SettingMenu(mtype,mename)values(3,'实时抽样')
insert into lcsy_SettingMenu(mtype,mename)values(3,'用户属性')
insert into lcsy_SettingMenu(mtype,mename)values(3,'地域分布')
insert into lcsy_SettingMenu(mtype,mename)values(3,'终端分析')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问页面')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问路径')
--菜单查询
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp,lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
----========
select * from lcsy_SettingPrivilege
select * from lcsy_SettingMenu
--根据模块名称查询
select sp.modName,sm.mename from lcsy_SettingMenu sm,lcsy_SettingPrivilege sp
where sp.mtype=sm.mtype and sp.modName='定制分析'
--根据模块Id查询
select * from lcsy_SettingMenu where mtype=1
---根据模块类型查询
select * from lcsy_SettingMenu where mtype=1
--1.根据用户ID查询用户所具有的服务市场的菜单权限
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
select sa.SellerId,sp.modName,sm.mename ,sm.mtype,sm.Url
from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
go
Use lcsy_setting
go
----用户
create table lcsy_settingAccount
(
id int identity(1,1) primary key,--用户主建
SellerId bigint,--卖家ID
SellerNick nvarchar(300), --卖家昵称
LoginName nvarchar(50),--登录名
LoginPass nvarchar(200),--密码
--roleNumber int not null,-- 给用户分角色role
rid int,-- 给用户分角色role
status int ,--状态
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(1,'tom',1)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(2,'mary',2)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(3,'yy',3)
insert into lcsy_settingAccount(SellerId,SellerNick,rid) values(4,'tt',3)
--查询用户
select * from lcsy_settingAccount
------2.编写用户表,的增删改查接口。
--(1)根据用户名称和用户ID返回用户信息。以json格式返回。
select * from lcsy_settingAccount where SellerId=1 and SellerNick='tom'
-- (2)根据用户id 和用户名称修改用户信息。
update lcsy_settingAccount set SellerNick='tom1' where SellerId=1 and SellerNick='tom'
---角色role
create table lcsy_SettingRole
(
rid int identity(1,1) primary key, --角色
--roleNumber int not null,--角色的编号
RoleName varchar(300),--角色的名称
pid int,--权限表
PType as rid,----权限类型(不同的角色有不同的权限)
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
insert into lcsy_SettingRole(RoleName) values('超级管理员')
insert into lcsy_SettingRole(RoleName) values('管理员')
insert into lcsy_SettingRole(RoleName) values('普通用户')
insert into lcsy_SettingRole(RoleName) values('VIP')
select * from lcsy_SettingRole
--查询用户的角色
select * from lcsy_SettingRole sr,lcsy_settingAccount sa
where sa.rid=sr.rid
--权限
create table lcsy_SettingPrivilege
(
pid int identity(1,1) primary key, --权限的主键
PType int not null,--角色的编号 权限(不同的角色有不同的权限)
status varchar(200),--0/1 是否可用
SellerId bigint,--卖家ID
SellerNick nvarchar(300), --卖家昵称
mtype int ,--菜单(不同的权限有不同的菜单)famquanxian
modName varchar(300),--模块名称
picture varchar(1000),--图片大
minipicture varchar(1000),--图片小
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
---给用户开权限
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,1)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,2)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,3)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(1,12, 1,4)
---插入数据到权限表。
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(3,3, 1,4)
select * from lcsy_SettingPrivilege
--查询不同角所拥有的权限 (开权限)
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId
--=========
---根据用户ID查询该用户的所有应用Id
select SellerId,SellerNick,rid from lcsy_settingAccount
select rid,PType from lcsy_SettingRole --分配角色和设置类型
select pid,PType,status,mtype from lcsy_SettingPrivilege
select meid,mtype from lcsy_SettingMenu
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sp.mtype,sp.status
from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sa.SellerId=3
---根据用户Id更新用户的权限。
select * from lcsy_SettingPrivilege
update lcsy_SettingPrivilege set mtype=3 where SellerId=3 and pid=5
---插入数据到权限表。(开权限)
insert into lcsy_SettingPrivilege(SellerId,PType,status,mtype)values(4,3, 1,4)
--==========
---菜单表
create table lcsy_SettingMenu
(
meid int identity(1,1) primary key,--菜单的主键
mtype varchar(200),--菜单的权限设置
mename varchar(200),--菜单的名称
Url nvarchar(300),--url
status varchar(200),--0/1 是否可用
described nvarchar(50),--描述
CreateDate datetime,--添加时间
UpdateDate datetime --修改时间
)
select * from lcsy_SettingMenu
select
sa.SellerId,
sp.picture,
sp.minipicture,
sp.modName,
sm.mename ,
sm.mtype,
sm.Url,
sm.described
from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
insert into lcsy_SettingMenu(mtype,mename)values(1,'定时分析')
insert into lcsy_SettingMenu(mtype,mename)values(1,'定制报告')
insert into lcsy_SettingMenu(mtype,mename)values(2,'趋势分析')
insert into lcsy_SettingMenu(mtype,mename)values(2,'活跃用户')
insert into lcsy_SettingMenu(mtype,mename)values(2,'留存用户')
insert into lcsy_SettingMenu(mtype,mename)values(3,'实时抽样')
insert into lcsy_SettingMenu(mtype,mename)values(3,'用户属性')
insert into lcsy_SettingMenu(mtype,mename)values(3,'地域分布')
insert into lcsy_SettingMenu(mtype,mename)values(3,'终端分析')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问页面')
insert into lcsy_SettingMenu(mtype,mename)values(4,'访问路径')
--菜单查询
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from lcsy_SettingRole sr,lcsy_settingAccount sa,lcsy_SettingPrivilege sp,lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
----========
select * from lcsy_SettingPrivilege
select * from lcsy_SettingMenu
--根据模块名称查询
select sp.modName,sm.mename from lcsy_SettingMenu sm,lcsy_SettingPrivilege sp
where sp.mtype=sm.mtype and sp.modName='定制分析'
--根据模块Id查询
select * from lcsy_SettingMenu where mtype=1
---根据模块类型查询
select * from lcsy_SettingMenu where mtype=1
--1.根据用户ID查询用户所具有的服务市场的菜单权限
select sr.RoleName,sr.PType,sa.SellerId,sa.SellerNick,sm.mename from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1
select sa.SellerId,sp.modName,sm.mename ,sm.mtype,sm.Url
from
lcsy_SettingRole sr,
lcsy_settingAccount sa,
lcsy_SettingPrivilege sp,
lcsy_SettingMenu sm
where sa.rid=sr.rid and sp.PType=sr.PType and sa.SellerId=sp.SellerId and sm.mtype=sp.mtype and sa.SellerId=1