【MySQL】多对多练习案例

多表（二）

• 多对多

 

1. 1. 分析

一个订单中可以有多种商品

一种商品可以被添加到多个订单上。

如：

订单1中只买了一双皮鞋

订单2中买了一双皮鞋一条裤子

此时我们需要设计第三张表来描述 订单和商品的对应关系

 

1. 商品和订单多对多关系，将拆分成两个一对多。
2. product商品表，为其中一个一对多的主表，需要提供主键pid
3. order订单表，为另一个一对多的主表，需要提供主键oid
4. orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid

1. 1. 实现：订单和商品

数据准备  已有数据：

CREATE DATABASE day04pre

Use day04pre;

###创建分类表

CREATE TABLE category(

  cid INT(32) PRIMARY KEY ,

  cname VARCHAR(100) #分类名称

);

# 商品表

CREATE TABLE product(

  pid INT PRIMARY KEY ,

  pname VARCHAR(50),

  price INT,

  flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架

  category_id INT,

  CONSTRAINT product_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

#分类

INSERT INTO category(cid,cname) VALUES('1','家电');

INSERT INTO category(cid,cname) VALUES('2','服饰');

INSERT INTO category(cid,cname) VALUES('3','化妆品');

#商品

INSERT INTO product(pid, pname,price,flag,category_id) VALUES('1','联想',5000,'1',1);

INSERT INTO product(pid, pname,price,flag,category_id) VALUES('2','海尔',3000,'1',1);

INSERT INTO product(pid, pname,price,flag,category_id) VALUES('3','雷神',5000,'1',1);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('4','JACK JONES',800,'1',2);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('5','真维斯',200,'1',2);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('6','花花公子',440,'1',2);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('7','劲霸',2000,'1',2);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('8','香奈儿',800,'1',3);

INSERT INTO product (pid, pname,price,flag,category_id) VALUES('9','相宜本草',200,'1',3);

新数据：

### 订单表

create table `order`(

  oid int PRIMARY KEY ,

  oname varchar(50)

);

### 订单项表

create table orderitem(

  oid int,-- 订单id

  pid int(50)-- 商品id

);

###---- 订单表和订单项表的主外键关系

ALTER TABLE orderitem ADD CONSTRAINT orderitem_order_fk FOREIGN KEY (oid) REFERENCES `order`(oid)

###---- 商品表和订单项表的主外键关系

alter table orderitem add constraint orderitem_product_fk foreign key (pid) references product(pid);

1. 1. 操作

#1 测试数据准备

--  向订单表中添加数据

三条数据:  1 订单1, 2  订单2,3  订单3

INSERT INTO `order` (oid,oname) VALUES(1,'订单1'),(2,'订单2'),(3,'订单3');

--向中间表添加数据(合理数据)

设定好的订单跟商品的关系

INSERT INTO orderitem(oid,pid) VALUES(1,1),(1,2),(1,3) ;

INSERT INTO orderitem(oid,pid) VALUES(2,1),(2,3),(3,3) ;

#需求

 1.查询 二表联查 商品表和订单项表 显示订单的id和 商品的名称

期望展示效果如下格式:

 

SELECT oid ,pname  FROM product p ,orderitem od WHERE  p.pid = od.pid ORDER BY oid

2. 三表联查 ， 查询三个表 商品表, 订单表,订单项表，显示订单编号，订单名称和该订单下所对应的商品

显示格式如下:

 

SELECT o.oid AS 订单编号,o.oname AS 订单名,p.pname AS 商品名 FROM product p,orderitem od ,`order` o WHERE p.pid=od.pid AND od.oid =o.oid

观察如下语句的执行效果

#4删除中间表的数据

DELETE FROM orderitem WHERE pid=2  AND oid = 1;

#5向中间表添加数据(数据不存在) -- 执行异常 也就是说 要往中间表中插入的数据是受到两边表的限制

INSERT INTO orderitem(pid,oid) VALUES(20, 30);

#6删除商品表的数据 -- 执行异常  因为这个id在第三张表中被使用了，因为约束的限制不能直接删除

DELETE FROM product WHERE pid = 1;

1. 1. 常见多对多表设计
      1. 用户和角色

 

1. 1. 1. 角色和权限