mysql+exercise+3_exercise.sql

/*

1.基本增删改查操作

*/

-- INSERT INTO product(product_name,sale_price) VALUES ('苹果手机',5000);

-- DELETE FROM product WHERE id=20;

-- DELETE FROM product WHERE id=20 AND product_name='联想M115';

-- DELETE FROM product WHERE id=20 OR product_name='联想M115';

-- DELETE FROM product WHERE product_name!='联想M115';

-- UPDATE product SET sale_price=200,product_name='特殊商品' WHERE cost_price>100;

-- SELECT * FROM product;

-- SELECT * FROM product WHERE id<8;

-- SELECT id,product_name,sale_price FROM product WHERE id<8;

/*

2.简单查询之列操作

*/

-- SELECT * FROM product;

-- SELECT id,product_name,sale_price FROM product;

-- SELECT classify_id FROM product;

-- SELECT id,product_name,sale_price-cost_price FROM product;

-- SELECT id,product_name,sale_price*cost_price FROM product;

-- SELECT id,product_name,cost_price*50 FROM product;

-- SELECT id,product_name,sale_price-cost_price 每零售1个产品所赚取的钱 FROM product;

-- SELECT id,product_name,(cost_price+1)*50 各进50个，并且每个运费1元的成本 FROM product;

-- SELECT product_name,sale_price-cost_price 每零售1个产品所赚取的钱 FROM product;

-- SELECT CONCAT(product_name,'商品的零售价为：',sale_price) FROM product;

/*

3.带条件查询

*/

#SELECT * FROM product WHERE sale_price>199;

#SELECT * FROM product WHERE sale_price=199;

#SELECT * FROM product WHERE product_name='联想G9X';

#SELECT * FROM product WHERE product_name!='联想G9X';

#SELECT * FROM product WHERE product_name!='联想G9X';

#SELECT * FROM product WHERE classify_id!=2;

#SELECT product_name,sale_price FROM product WHERE sale_price<=200;

#SELECT id,product_name,sale_price*cutoff 批发价 FROM product WHERE sale_price*cutoff >350;

#SELECT id,product_name,sale_price*cutoff 批发价 FROM product WHERE sale_price*cutoff BETWEEN 300 AND 400;

#SELECT id,product_name,classify_id FROM product WHERE classify_id=2 OR classify_id=4;

#SELECT id,product_name,classify_id FROM product WHERE classify_id!=2;

#SELECT id,product_name,classify_id,sale_price,cost_price FROM product WHERE sale_price>=250 OR cost_price>=200;

#SELECT id,product_name,sale_price*cutoff 批发价 FROM product WHERE sale_price*cutoff BETWEEN 300 AND 400;

#SELECT id,product_name,sale_price*cutoff 批发价 FROM product WHERE sale_price*cutoff NOT BETWEEN 300 AND 400;

#SELECT id,product_name,classify_id FROM product WHERE classify_id IN (2,4);

#SELECT id,product_name,classify_id FROM product WHERE classify_id NOT IN (2,4);

#SELECT * FROM product WHERE product_name IS NULL;

#SELECT id,product_name FROM product WHERE product_name LIKE '%联想M9_';

#SELECT id, product_name,classify_id,sale_price FROM product WHERE sale_price>=200 AND product_name LIKE '%联想M1__';

#SELECT id,product_name,classify_id,sale_price FROM product ORDER BY sale_price DESC;

#SELECT id,product_name,classify_id,sale_price FROM product ORDER BY classify_id ASC,sale_price ASC;

#SELECT id,product_name M系列,classify_id,sale_price,supplier,brand,cutoff,cost_price,sale_price*cutoff 批发价 FROM product WHERE product_name LIKE '%M%' ORDER BY sale_price*cutoff ASC;

#SELECT *,sale_price*cutoff 批发价 FROM product WHERE classify_id=2 ORDER BY sale_price*cutoff ASC;

/*

4.函数

*/

#SELECT AVG(sale_price) 所有商品平均零售价 FROM product;

#SELECT COUNT(id) 商品总记录数 FROM product;

#SELECT SUM(classify_id=2) 分类为2的商品总数 FROM product;

#SELECT MIN(sale_price) 商品的最小零售价,MAX(sale_price) 最高零售价,SUM(sale_price) 所有商品零售价总和 FROM product;

/*

5.分组

*/

#SELECT classify_id 商品分类编号,AVG( sale_price) 各自的平均零售价 FROM product GROUP BY classify_id;

#SELECT classify_id 商品分类编号,COUNT(id) 商品总数 FROM product GROUP BY classify_id;

#SELECT classify_id 商品分类编号,COUNT(id) 零售价大于100的商品总数 FROM product WHERE sale_price>100 GROUP BY classify_id;

#SELECT classify_id 商品分类编号,SUM(sale_price) 总零售价和 FROM product GROUP BY classify_id HAVING SUM(sale_price)>1500;

/*

6.综合查询

*/

-- SELECT p.*,pc.classify_name FROM product p INNER JOIN product_classify pc ON p.classify_id=pc.id;

-- SELECT /* p.id, */p.product_name 商品的名称,pc.classify_name 分类名称 FROM product p INNER JOIN product_classify pc ON p.classify_id=pc.id;

-- SELECT p.id,p.product_name 货品名称,pc.classify_name 分类名称 FROM product p INNER JOIN product_classify pc ON p.classify_id=pc.id;

-- SELECT p.id,p.product_name,p.sale_price,pc.classify_name FROM product p INNER JOIN product_classify pc ON p.classify_id=pc.id WHERE sale_price>200;

-- SELECT p.id,p.product_name 商品的名称,p.sale_price 零售价,pc.classify_name 分类名称 FROM product p INNER JOIN product_classify pc ON p.classify_id=pc.id WHERE sale_price>200;

-- SELECT p.id,p.product_name 商品的名称,pc.classify_name 分类名称,ps.store_num 库存 FROM product p INNER JOIN product_classify pc INNER JOIN product_stock ps ON p.classify_id=pc.id AND p.id=ps.product_id;

-- SELECT p.id,p.product_name 商品的名称,p.sale_price 零售价,pc.classify_name 货品分类,(p.sale_price-p.cost_price)*ps.store_num 利润 FROM product p INNER JOIN product_classify pc INNER JOIN product_stock ps ON p.classify_id=pc.id AND p.id=ps.product_id ORDER BY (p.sale_price-p.cost_price)*ps.store_num DESC;

-- SELECT * FROM product_classify a LEFT JOIN product_classify b ON (a.parent_id=b.id);

-- SELECT * FROM product WHERE sale_price>(SELECT sale_price FROM product WHERE product_name='联想MX1100' );

-- SELECT classify_id,cutoff FROM product WHERE product_name='联想M100';

-- SELECT p.classify_id,pc.classify_name,COUNT(1),AVG(p.sale_price) FROM product p LEFT JOIN product_classify pc ON (p.classify_id=pc.id) GROUP BY p.classify_id

一键复制

编辑

Web IDE

原始数据

按行查看

历史