#创建数据库
CREATE DATABASE shangdian;
USE shangdian;
#商品表,主表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
cid VARCHAR(32)
);
#添加数据
INSERT INTO products VALUES ('p002','皮大衣',3000.0,'c001');
INSERT INTO products VALUES ('p003','袜子',20.0,'c003');
#订单表,主表
CREATE TABLE orders(
oid VARCHAR(32) PRIMARY KEY,#主键
totalprice DOUBLE #总计
);
#添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');
#订单表项,从表
CREATE TABLE orderitem(
oid VARCHAR(50),#订单id
pid VARCHAR(50) #商品id
);
#商品id
# alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
ALTER TABLE orderitem ADD CONSTRAINT oim_products_fk1 FOREIGN KEY(pid) REFERENCES products(pid);
# 订单id 外键
ALTER TABLE orderitem ADD CONSTRAINT oim_orders_fk2 FOREIGN KEY(oid) REFERENCES orders(oid);
#向从表添加数据(数据存在),必须两个主表都满足的情况,才能添加成功
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#为满足,添加不成功
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
CREATE DATABASE shangdian;
USE shangdian;
#商品表,主表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
cid VARCHAR(32)
);
#添加数据
INSERT INTO products VALUES ('p002','皮大衣',3000.0,'c001');
INSERT INTO products VALUES ('p003','袜子',20.0,'c003');
#订单表,主表
CREATE TABLE orders(
oid VARCHAR(32) PRIMARY KEY,#主键
totalprice DOUBLE #总计
);
#添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');
#订单表项,从表
CREATE TABLE orderitem(
oid VARCHAR(50),#订单id
pid VARCHAR(50) #商品id
);
#商品id
# alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
ALTER TABLE orderitem ADD CONSTRAINT oim_products_fk1 FOREIGN KEY(pid) REFERENCES products(pid);
# 订单id 外键
ALTER TABLE orderitem ADD CONSTRAINT oim_orders_fk2 FOREIGN KEY(oid) REFERENCES orders(oid);
#向从表添加数据(数据存在),必须两个主表都满足的情况,才能添加成功
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#为满足,添加不成功
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');