编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE goods(
    id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '商品编号',
    type VARCHAR(30) NOT NULL COMMENT '商品类别',
    name VARCHAR(30) UNIQUE COMMENT '商品名称',
    price DECIMAL(7,2) COMMENT '商品价格',
    num INT(11) DEFAULT 0 COMMENT '商品库存',
    add_time DATETIME COMMENT '添加时间'
);
INSERT INTO goods(id, type, name, price, num, add_time)  VALUES
(1, '书籍', '西游记', 50.4, 20, '2018-01-01 13:40:40'),
(2, '糖类', '牛奶糖', 7.5, 200, '2018-02-02 13:40:40'),
(3, '糖类', '水果糖', 2.5, 100, null),
(4, '服饰', '休闲西服', 800, null, '2018-04-04 13:40:40'),
(5, '饮品', '果汁', 3, 70, '2018-05-05 13:40:40');
INSERT INTO goods(id, type, name, price, num, add_time)
VALUES(6,'书籍','论语',109,50,'2018-01-03 13:40:40'),
(7,'水果','西瓜',1.5,null,'2018-02-05 13:40:40'),
(8,'水果','苹果',3,100,'2018-03-05 13:40:40'),
(9,'服饰','牛仔裤',120,10,'2018-05-04 13:40:40'),
(10,'书籍','红楼梦',50.5,15,'2018-05-06 13:40:40');
SELECT * FROM goods;
SELECT type,count(*) FROM goods group by type;