数据库管理权限

 一.用户角色多对多关联
1.角色表

create table role(
    id int auto_increment primary key,
    name varchar(100)
);

2.用户表

create table user_role(
    id int auto_increment primary key,
    name varchar(100)
);

3.角色用户中间表

create table user_role(
    id int auto_increment primary key,
    user_id int,
    role_id int
);

4.查询所有的用户，以及他所对应的角色
select u.id,u.name,r.name from user u left join user_role ur on ur.user_id=u.id left join role r on ur.role_id=r.id;

5.查询所有的用户，以及每个用户所拥有的角色数量
select u.id,u.name,count(r.id) from user u left join user_role ur on ur.user_id=u.id left join role r on ur.role_id=r.id group by u.id;

6.查询所有的角色，以及每个角色所包含的用户数量
select r.id,r.name,count(u.id) from role r left join user_role ur on ur.role_id=r.id left join user u on ur.user_id=u.id group by r.id;

二.角色跟功能多对多关系
1.创建功能表

create table function(
    id int auto_increment primary key,
    name varchar(100)
);

2.创建角色功能中间表

create table role_func(
    id int auto_increment primary key,
    role_id int,
    func_id int
);

3.查询所有的角色，以及他所对应的功能
select r.id,r.name,f.name from role r left join role_func rf on r.id=rf.role_id left join function f on f.id=rf.func_id;

4.查询所有的用户，以及他所能使用的所有功能
select distinct u.id,u.name,f.name from user u left join user_role ur on ur.user_id=u.id left join role r on ur.role_id=r.id  left join role_func rf on rf.role_id=r.id left join function f on f.id=rf.func_id order by u.id;

5.查询所有的用户，以及他所能使用的功能数量
select u.id,u.name,count(distinct f.id) from user u left join user_role ur on ur.user_id=u.id left join role r on ur.role_id=r.id  left join role_func rf on rf.role_id=r.id left join function f on f.id=rf.func_id group by u.id order by u.id;

三.商品类目解决方案

1.类别表

create table category(
    id bigint auto_increment primary key,
    parent_id smallint,
    name varchar(100)
);

+----+------------------+--------------+
|id |parent_id | name   |
+----+------------------+--------------+
| 1 |     NULL | 电脑办公 |
| 2 |     NULL | 办公文具 |
|  3 |            1 | 电脑整机  |
|  4 |            1 | 电脑配件  |
|  5 |            2 | 办公设备  |
|  6 |            2 | 文具耗材  |
|  7 |            3 | 笔记本    |
|  8 |            3 | 台式机    |
|  9 |            4 | 显示器    |
| 10 |            4 | 机箱      |
| 11 |            5 | 打印机    |
| 12 |            5 | 点钞机    |
| 13 |            6 | 学生文具  |
| 14 |            6 | 纸类      |
+----+------------------+--------------+

2.商品表

create table item(
    id bigint auto_increment primary key,
    category_id smallint,
    name varchar(100),
    price decimal(11,4),
    stock int,
    image varchar(500),
    detail varchar(500),
    upload_time timestamp
);

3.查询所有的分类，以及他们的父分类
select c.id one,c.name,p.id two,p.name from category c left join category p on c.parent_id=p.id;

4.查询所有的二级分类，以及他们的父分类（一级父类为空）
select c.id one,c.name,p.id two,p.name from category c left join category p on c.parent_id=p.id where p.id in(select id from category where parent_id is null);

5.查询所有的三级分类，以及他们的父分类
select c.id one,c.name,p.id two,p.name from category c left join category p on c.parent_id=p.id where c.id in(select category_id from item);

6.查询所有的三级分类，以及他们的父分类二级分类，还有一级分类

select c.id one,c.name,p.id two,p.name,g.id three,g.name from category c left join category p on c.parent_id=p.id left join category g on p.parent_id=g.id where c.id in(select category_id from item);

7.查询所有商品的分类树
select concat(g.name,'>',p.name,'>',c.name) 分类树,i.id 商品ID,i.name 商品名称,i.price 商品单价,i.stock 商品库存,i.image 商品图片 
from  item i left join category c on i.category_id=c.id left join category p on c.parent_id=p.id left join category g on p.parent_id=g.id where c.id in(select category_id from item);