mysql设计前置课_第3.14课上课MySQL数据库进阶， 数据库设计

#多对多外键关联关系

# 货品表

CREATE TABLE e_goods(

id int PRIMARY KEY AUTO_INCREMENT, #id

title VARCHAR(200), #货品名称

brand_name VARCHAR(200), #品牌名称

price DOUBLE #价格

);

# 订单表

CREATE TABLE e_order (

id int PRIMARY KEY AUTO_INCREMENT,

order_code varchar(20) DEFAULT NULL,

create_date timestamp DEFAULT NOW()

);

#订单明细表 通过中间表建立多对多的关联关系

CREATE TABLE e_order_item(

order_id int, #订单编号

goods_id int, #物品编号

order_num int, #购买数量

PRIMARY KEY(order_id, goods_id), # 联合主键

CONSTRAINT FK_ORDER_ITEM_ORDER_ID FOREIGN KEY (order_id) REFERENCES e_order(id),

CONSTRAINT FK_ORDER_ITEM_GOODS_ID FOREIGN KEY (goods_id) REFERENCES e_goods(id)

);

insert into e_goods values(1,'手机','苹果',2000);

insert into e_order values(1,'168','2009-09-22');

insert into e_order_item value(1,1,2);

select * from e_goods;

select * from e_order;

select * from e_order_item;

# 先删除中间表才能够删除主表

DROP TABLE IF EXISTS e_order_item;

DROP TABLE IF EXISTS e_user;

DROP TABLE IF EXISTS e_order;