MySQL多对多的操作

一、多对多的解释

其实多对多就是利用第三种表,创建一对多的关系,联合查询的

• 1、创建表及插入数据

  ```sql create table article( article_id int primary key auto_increment comment "文章id", title varchar (100) comment "文章标题" );

  create table tag( tag_id int primary key auto_increment comment "tag id", name varchar (100) comment "tag名字" );

create table article_tag( id int primary key auto_increment comment "id", article_id int not null comment "文章id", tag_id int not null comment "tag id", foreign key(article_id) references article(article_id) on delete set null , foreign key(tag_id) references tag(tag_id) on delete set null );

insert into article(title) values ("文章一"), ("文章二"), ("文章三"); insert into tag(name) values ("java"), ("web"), ("python"); insert into article_tag(article_id, tag_id) values (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 1);

* 2、查询文章的标签(查询了三张表,根据关联来查询)

  ```sql
  mysql> select article.title, tag.name from article, tag, article_tag where article.article_id = article_tag.article_id and tag.tag_id = article_tag.tag_id;
  +-----------+--------+
  | title     | name   |
  +-----------+--------+
  | 文章一    | java   |
  | 文章一    | web    |
  | 文章一    | python |
  | 文章二    | web    |
  | 文章二    | python |
  | 文章三    | java   |
  +-----------+--------+
  6 rows in set (0.00 sec)

  mysql>