/*
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
原始数据
按行查看
历史