mysql设计收藏与标签

用户表--收藏表（多对多）

收藏表--标签表（多对多）

 

1.创建一个用户表
create table user(id int primary key auto_increment,name varchar(20));
2.创建一个收藏表
create table book(id int primary key auto_increment,title varchar(30));
3.创建一个标签表
create table tag(id int primary key auto_increment,tag varchar(20));
4.创建用户收藏表
create table u_b(u_id int,b_id int,foreign key(u_id) references user(id) on delete cascade on update cascade,
foreign key(b_id) references book(id) on delete cascade on update cascade) character set utf8;
5.创建收藏标签表
create table b_t(b_id int,t_id int,foreign key(b_id) references book(id) on delete cascade on update cascade,foreign key(t_id) references tag(id) on delete cascade on update cascade) character set utf8;
6.插入6本书
insert into book values(1,'红楼梦'),(2,'钢铁是怎样练成的'),(3,'西游记'),(4,'金瓶梅'),(5,'如何驯服两腿间的野马'),(6,'美国大城市的生与死');
7.插入3名用户
insert into user values(1,'李运通'),(2,'廖家浩'),(3,'杨启青');
8.插入7个标签
insert into tag values(1,'言情'),(2,'都市'),(3,'两性'),(4,'玄幻'),(5,'伦理'),(6,'名著'),(7,'励志');
9.用户收藏的书
insert into u_b values(1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(2,6),(3,3),(3,5),(3,6);
10.书的标签
insert into b_t values(1,1),(1,3),(1,5),(1,6),(2,6),(2,7),(3,4),(3,6),(4,2),(4,3),(4,5),(4,7),(5,1),(5,3),(5,7),(6,2),(6,6);
 

 

1.列出一个用户的某个标签下的所有收藏
select * from book where book.id in(select u_b.b_id from u_b right join user on u_b.u_id = user.id where user.name = '廖家浩')
and book.id in 
(select b_t.b_id from b_t
right join
tag on tag.id = b_t.t_id where tag.tag = '名著');

 

2.列出一本书下最热门的标签
select b_t.t_id,count(b_t.b_id) from b_t 
where b_t.t_id in (select tag.id from tag left join b_t on b_t.t_id = tag.id right join book on book.id = b_t.b_id where book.title = '金瓶梅' )
group by b_t.t_id order by -count(b_t.b_id) limit 2;