多表（二）
- 多对多
- 分析
一个订单中可以有多种商品
一种商品可以被添加到多个订单上。
如：
订单1中只买了一双皮鞋
订单2中买了一双皮鞋一条裤子
此时我们需要设计第三张表来描述 订单和商品的对应关系
- 商品和订单多对多关系，将拆分成两个一对多。
- product商品表，为其中一个一对多的主表，需要提供主键pid
- order订单表，为另一个一对多的主表，需要提供主键oid
- orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid
- 实现：订单和商品
数据准备 已有数据：
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, 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;
- 常见多对多表设计
- 用户和角色
- 角色和权限
