Mysql-表关系多对多设计

/*
	多对多：
		* 如：订单 和 商品
		* 一个商品对应多个订单，一个订单包含多个商品
	实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键	
*/

DROP TABLE tb_order;
DROP TABLE tb_goods;
DROP TABLE tb_order_goods;


CREATE TABLE tb_order(
	id INT PRIMARY KEY auto_increment,
	payment DOUBLE(10,2),
	payment_type TINYTEXT,
	status TINYTEXT
);

CREATE TABLE tb_goods(
	id INT PRIMARY KEY auto_increment,
	title VARCHAR(100),
	price DOUBLE(10,2)
);

CREATE TABLE tb_order_goods(
	id INT PRIMARY KEY auto_increment,
	order_id INT,
	goods_id INT,
	count INT
);

INSERT INTO tb_order(payment,payment_type,status)
	VALUES
		(7376.00,'微信支付','未付款'),
		(7376.00,'支付宝支付','已付款');
	
INSERT INTO tb_goods(title,price)
	VALUES
		('华为P40手机',5988),
		('海天酱油',9.9),
		('华为GT2手表',1388);
		
INSERT INTO tb_order_goods(order_id,goods_id,count)
	VALUES
		(1,1,1),
		(1,3,1),
		(2,1,10);

SELECT * FROM tb_order; 
SELECT * FROM tb_goods; 
SELECT * FROM tb_order_goods; 

ALTER TABLE tb_order_goods DROP FOREIGN KEY fk_order_id;
ALTER TABLE tb_order_goods DROP FOREIGN KEY fk_goods_id;

ALTER TABLE tb_order_goods ADD CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES tb_order(id);

ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods_id FOREIGN KEY (goods_id) REFERENCES tb_goods(id);

-- 查询1号订单的商品信息
SELECT a.id '订单号',title '商品',b.id '商品号',count '数量',price '单价',payment '单类合计',payment_type '支付方式',status '是否支付' 
FROM tb_order a,tb_goods b,tb_order_goods c 
WHERE a.id=c.order_id AND b.id=goods_id AND c.order_id=1;