MySQL完成1创建products表字段包括商品id 、商品名、商品价格、商品分类分类值是c001 c002c0032基于products表的数据进行聚合函数查询3商品汇总总价等查询。4自己添加测试数据

创建products表

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  price DECIMAL(8,2),
  category ENUM('c001', 'c002', 'c003')
);

聚合函数查询

统计各个商品分类的数量和总价

SELECT category, COUNT(*) AS count, SUM(price) AS total_price
FROM products
GROUP BY category;

统计所有商品的平均价格和总价

SELECT AVG(price) AS avg_price, SUM(price) AS total_price
FROM products;

商品汇总，总价等查询

查询所有商品的id、名称和价格

SELECT id, name, price
FROM products;

查询所有商品的id、名称、价格和分类

SELECT id, name, price, category
FROM products;

查询价格在100元以上的商品

SELECT id, name, price
FROM products
WHERE price > 100;

查询分类为c001的商品

SELECT id, name, price
FROM products
WHERE category = 'c001';

添加测试数据

INSERT INTO products (name, price, category) VALUES ('商品1', 100, 'c001');
INSERT INTO products (name, price, category) VALUES ('商品2', 200, 'c001');
INSERT INTO products (name, price, category) VALUES ('商品3', 50, 'c002');
INSERT INTO products (name, price, category) VALUES ('商品4', 150, 'c002');
INSERT INTO products (name, price, category) VALUES ('商品5', 300, 'c003');
``