用户管理模块数据库设计

转载

mb5fe190725e8a3 2017-01-22 16:50:00

--用户管理模块数据库设计

drop database UsersDBTest
--------------创建数据库----------------------------
create database UsersDBTest  --sqlserver这一句话就可以创建数据库，其它默认值。

on  primary  -- 默认就属于primary文件组,可省略
(
/*--数据文件的具体描述--*/
    name='UsersDBTest_data',  -- 主数据文件的逻辑名称
    filename='E:\DB\UsersDBTest_data.mdf', -- 主数据文件的物理名称和地址
    size=5mb, --主数据文件的初始大小
    maxsize=100mb, -- 主数据文件增长的最大值
    filegrowth=15%--主数据文件的增长率
)
log on
(
/*--日志文件的具体描述,各参数含义同上--*/
    name='UsersDBTest_log',
    filename='E:\DB\UsersDBTest_log.ldf',
    size=2mb,
    filegrowth=1mb
  )
  
  
  use  UsersDBTest -- 使用数据库
  
 -------------创建用户、角色、权限表------------------ 
  create table UserInfo
  (
    ID int identity(1,1) not null, 
    Uname nvarchar(max) not null,
    Pwd nvarchar(max) not null,
    ShowName nvarchar(max) null,

  );
    
  create table RoleInfo
  (
    ID int identity(1,1) not null,
    RoleName nvarchar(max) not null,
  );


  create table ActionInfo
  (
    ID int identity(1,1)  not null,
    ActionName nvarchar(max) not null
  );
  
  --------------创建关系表----------------
  
  create table UserInfoRoleInfo
  (
    UserInfo_ID int not null,
    RoleInfo_ID int not null
    
  );
  
  create table RoleInfoActionInfo
  (
    RoleInfo_ID int not null,
    ActionInfo_ID int not null
  );
  
  --如何让不拥有A角色的人拥有A角色的某个权限，让拥有A角色所有权限的人失去某个权限?
  --跨角色授权或削权
  create table R_UserInfo_ActionInfo  
  (
    ID int identity(1,1) not null,
    HasPermisson smallint not null,
    UserInfo_ID int not null,
    ActionInfo_ID int not null
  );
  
 ---------------创建所有主键约束-----------
 
 Alter table UserInfo
    add constraint PK_UserInfo  --给主键约束取别名,寓意:UserInfo表的主键约束。取别名的好处是1删除方便2为多个列定义同时约束，即主键组。
    Primary Key Clustered(ID Asc); --主键约束 ，对ID列聚集索引，根据ID升序排序
  
 Alter table RoleInfo
    add constraint PK_RoleInfo
    Primary Key Clustered(ID Asc);
  
 Alter table ActionInfo
    add constraint PK_ActionInfo 
    Primary Key Clustered(ID Asc);
  
 Alter table UserInfoRoleInfo
  add constraint PK_UserInfoRoleInfo
    Primary Key Clustered(UserInfo_ID,RoleInfo_ID Asc);
  
 Alter table RoleInfoActionInfo
  add constraint PK_RoleInfoActionInfo
    Primary Key Clustered(RoleInfo_ID,ActionInfo_ID Asc);


 Alter table R_UserInfo_ActionInfo
  add constraint PK_R_UserInfo_ActionInfo
    Primary Key Clustered(ID Asc);
    

--主键的简单创建方式：
  --create table ActionInfo
  --(
   -- ID int identity(1,1) primary key not null,  --方式1
   -- ActionName nvarchar(max) not null,
   -- --primary key(ID)  --方式2

  --);
  
--添加主键
 -- alter table UserInfo add constraint PK_UserInfo primary key(ID,Uname) --主键组
--删除主键： 
--Alter table UserInfo drop constraint PK_UserInfo 
--必须先删除相关外键约束



    
------------创建所有外键--------------------

 Alter table UserInfoRoleInfo
  add constraint FK_UserInfoRoleInfo_UserInfo --给外键取别名
    Foreign Key(UserInfo_ID) References UserInfo(ID) --让外键表UserInfoRoleInfo的 UserInfo_ID列引用UserInfo表的ID列 
    On Delete no Action On Update no Action; --注意此处的Action不是ActionInfo表！！！
    
  
 Alter table UserInfoRoleInfo
  add constraint FK_UserInfoRoleInfo_RoleInfo --注意名称不同
    Foreign Key(RoleInfo_ID) References RoleInfo(ID) 
    On Delete no Action On Update no Action;
  
  
 Alter table RoleInfoActionInfo
  add constraint FK_RoleInfoActionInfo_RoleInfo 
  Foreign Key(RoleInfo_ID) References RoleInfo(ID)
  On Delete no Action On Update no Action;
  
 Alter table RoleInfoActionInfo
  add constraint FK_RoleInfoActionInfo_ActionInfo
  Foreign Key(ActionInfo_ID) References ActionInfo(ID)
  On Delete no Action On Update no Action;


  
 Alter table R_UserInfo_ActionInfo
  add constraint FK_UserInfoR_UserInfo_ActionInfo
  Foreign Key(UserInfo_ID) References UserInfo(ID)
  On Delete no Action On Update No Action;
  

  
  Alter table R_UserInfo_ActionInfo
  add constraint FK_ActionInfoR_UserInfo_ActionInfo
  Foreign Key(ActionInfo_ID) References ActionInfo(ID)
  On Delete no Action On Update No Action;
  
   
--ON DELETE NO ACTION 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 DELETE 语句。
--ON UPDATE NO ACTION 指定如果试图更新某一行中的键值，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚 UPDATE 语句。 
  
--外键（引用）
--作用：保持数据一致性，完整性，
--主要目的：控制存储在外键表中的数据。 
--使两张表形成关联，外键只能引用外表中的列的值或使用空值。
--1如果不使用外键，成绩表的学号字段插入一个值（比如13341321），
--但是这个值在学生表中并没有，此时数据库允许插入，并不会对插入的数据做关系检查。
--问题是这样很可能出现学生表和成绩表不对应，在整合学生信息时出现遗漏或多余等问题。
--2在设置外键的情况下，你要插入成绩表学号字段的值必须要求在学生表的学号字段能找到。 
--你要删除学生表的某个学号，必须保证成绩表中没有引用该值所在的列（外键），否则就没法删除。
--这就是所谓的保持数据的一致性和完整性。


------------创建索引------------------------  


 Create Index IX_FK_UserInfoRoleInfo_RoleInfo --给索引取名字
  on UserInfoRoleInfo(RoleInfo_ID); --需要建立索引的列

 Create Index IX_FK_RoleInfoActionInfo_ActionInfo
  on RoleInfoActionInfo(ActionInfo_ID);


 Create Index IX_FK_UserInfoR_UserInfo_ActionInfo
   on R_UserInfo_ActionInfo(UserInfo_ID);


 Create Index IX_FK_ActionInfoR_UserInfo_ActionInfo
  on R_UserInfo_ActionInfo(ActionInfo_ID);
  

   

--外键是怎么发挥作用的？把两张表放一些数据增删改看看.