一对多

CREATE TABLE category (分类
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
); 
CREATE TABLE products(商品
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
插入INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');  
显示内连接：select * from A inner join B on 条件 where 条件;
-- 查询那些分类的商品已经上架
-- 隐式内连接
SELECT DISTINCT
	c.cname，p.*,count(*),count(c.name)
FROM
	category c,
	products p
WHERE
	c.cid = p.category_id
	And p.flag=1;
 WHERE
	c.cid = p.id
	And  c.name="化妆品;
	group by c.cid
	group by c.name
-- 显示内连接
SELECT DISTINCT
	c.cname
FROM
	category c INNER JOIN products p
	INNER可省 
ON  c.cid = p.category_id
where c.name='化妆品'