提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

【MySQL】多对多练习案例

mysql案例 练习
2023-06-13 09:17:14 时间

多表（二）

  • 多对多
    1. 分析

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

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

如：

订单1中只买了一双皮鞋

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

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

  1. 商品和订单多对多关系，将拆分成两个一对多。
  2. product商品表，为其中一个一对多的主表，需要提供主键pid
  3. order订单表，为另一个一对多的主表，需要提供主键oid
  4. orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid
    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, 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. 角色和权限

相关文章