MySQL----商品表及商品分类表例子

1.建库

2.建表

新建一个查询，粘贴下列代码，选中执行

#商品分类表

CREATE TABLE category(

cid INT PRIMARY KEY,

cname VARCHAR(50)

);

#商品表

CREATE TABLE products(

pid INT PRIMARY KEY,

pname VARCHAR(50),

price DOUBLE,

flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架

category_id INT,

CONSTRAINT products_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','化妆品');

##########商品### #商品id#商品名称#商品价格#商品状态#商品所属分类

#全为上架状态

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('1','联想',5000.00,'1',1);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('2','海尔',3000.00,'1',1);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('3','雷神',7000.00,'1',1);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('4','JACK_JONES',800.00,'1',2);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('5','真维斯',200.00,'1',2);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('6','花花公子',450.00,'1',2);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('7','劲霸',700.00,'1',2);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('8','香奈儿',2680.00,'1',3);

INSERT INTO products(pid,pname,price,flag,category_id) VALUES('9','相宜本草',590.00,'1',3);

商品分类表：（主键没有自增）

商品表：（主键没有自增）

模型图：

3.交叉查询