设计数据库

一、设计数据库

ER图：Entity-Relation实体关系图

RDBMS关系型数据库管理系统里不支持多对多的关系，只支持一对一或一对多关系

1、数据库三大范氏

第一范式：每个字段不能再分

第二范式：每个表都有主键

第三范式：从表通过外键与主表的主键联系

2、数据库的权限设计（5张表）

（1）user表

（2）permission表

（3）user_role表

（4）role_permission表

3、对这5张表相关的授权操作

（1）角色授权   

    -- 角色：系统管理员 授权权限：删除会员

      insert into role_permission(role_id,permission_id)

      select a.id, b.id from

      (select id from role where name='系统管理员') a,

       (select id from permission where name='删除会员') b

（2）检查角色是否拥有权限

     -- 角色：系统管理员 是否拥有权限：增加会员

      select rp.role_id,rp.permission_id from role_permission rp, role r, permission p

       where rp.role_id=r.id and rp.permission_id=p.id and r.name='系统管理员' and p.name='增加会员'

    （3）收回角色权限

    -- 角色：系统管理员 收权权限：删除会员

delete from role_permission

where role_id in (select id from role where name='系统管理员')

and permission_id in (select id from permission where name='删除会员')

（4）用户授权

insert into user_role(user_id, role_id) values(1, 1)

（5）用户是否拥有权限

-- 用户：张三 是否拥有权限：增加会员

select u.id,u.username from user u, permission p, user_role ur, role_permission rp

where u.id=ur.user_id and p.id = rp.permission_id and ur.role_id=rp.role_id

and u.username='张三' and p.name='增加会员'

（6）用户收回角色

delete from user_role where user_id=1 and role_id=1

（7）增删改查用户、角色或权限

删除主表数据时，如果其他表的外键引用了被删除记录的主键值，就需要首先删除其他表的记录，再删除主表的这条数据

4、存储过程

（1）编写存储过程

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `grantRolePermission`(

IN `roleName` VARCHAR(50),

IN `permissionName` VARCHAR(50)

)

BEGIN

-- 声明两个临时变量

DECLARE roleId INT;

DECLARE permissionId INT;

-- 查询结果存入临时变量

select id into roleId from role where name=roleName;

select id into permissionId from permission where name=permissionName;

-- 将临时变量值插入表

insert into role_permission(role_id,permission_id) values(roleId, permissionId);

END

;;

DELIMITER ;

（2）存储过程JDBC调用

CallableStatement cs = cn.prepareCall("{CALL grantRolePermission(?,?)}");

cs.setString(1, "系统管理员");

cs.setString(2, "删除会员"); cs.execute(); // 执行存储过程