【MySQL】多对多练习案例
多表(二)
- 多对多
-
- 分析
一个订单中可以有多种商品
一种商品可以被添加到多个订单上。
如:
订单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;
-
- 常见多对多表设计
- 用户和角色
- 常见多对多表设计
-
-
- 角色和权限
-
相关文章
- mysql索引总结(2)-MySQL聚簇索引和非聚簇索引
- MySQL存储过程详解 mysql 存储过程
- IIS下MySQL停止和启动的方法
- mysql绿色版制作
- 【MySQL高级】MySql中常用工具及Mysql 日志
- mysql远程连接 Host * is not allowed to connect to this MySQL server
- 【MySQL进阶-04】深入理解mysql事务本质(超级详解)
- MySQL运维案例分析:Binlog中的时间戳
- 使用MyBatis搭建一个访问mysql数据库的简单示例
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- 转 【MySQL】常用拼接语句 shell 下执行mysql 命令
- MySQL存储过程详解 mysql 存储过程
- mysql导出数据报错The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- Mysql_mysql多个TimeStamp设置
- MySQL数据库:表结构优化
- Amoeba for MySQL 非常好用的mysql集群软件
- 【查阅】mysql配置文件/参数文件重要参数笔录(my.cnf)
- (4.1)mysql备份还原——mysql常见故障
- mysql中的日志
- Mac OS X 10.10 Yosemite下面解决XAMPP无法开启mysql的问题
- MySQL中的排序(ORDER BY)