SQL语句（练习题）

#1
#需求:查询所有货品信息
SELECT * FROM product;
#需求:查询所有货品的id,supplier,cutoff
SELECT id,supplier,cutoff FROM product;
SELECT ID,DIR_ID FROM product;
SELECT DISTINCT dir_id FROM product;
SELECT DIR_ID PS FROM product;
SELECT saleprice*3 FROM product
#2.过滤查询
#需求: 选择id，货品名称，零售价在300-400之间的货品
SELECT id,productName,salePrice FROM product WHERE salePrice>300 AND salePrice<400;
#需求: 选择id，货品名称，分类编号为2或4的所有货品
SELECT id,productName,dir_id xid FROM product WHERE dir_id=2 OR dir_id=4;
#需求: 选择id，货品名称，分类编号不为2的所有商品
SELECT id,productName,dir_id FROM product WHERE dir_id!=2;
SELECT id,productName,dir_id FROM product WHERE dir_id<>2;
#需求: 选择id，货品名称，分类编号的货品零售价大于等于250或者成本大于等于200
SELECT id,productName,dir_id,salePrice,costPrice FROM product
WHERE salePrice>=250 OR costPrice>=200;
#3.其余过滤查询
#需求: 选择id，货品名称，零售价在300-400之间的货品
SELECT id,productName,salePrice FROM product WHERE salePrice BETWEEN 300 AND 400;
#需求: 选择id，货品名称，零售价不在300-400之间的货品
SELECT id,productName,salePrice FROM product WHERE salePrice NOT BETWEEN 300 AND 400;
#需求:选择id，货品名称，分类编号为2或4的所有货品
SELECT id,productName,dir_id FROM product WHERE dir_id IN (2 OR 4);
#需求:选择id，货品名称，分类编号不为2或4的所有货品
SELECT id,productName,dir_id FROM product WHERE dir_id NOT IN (2 OR 4);
#需求:查询商品名为NULL的所有商品信息
SELECT id,productName FROM product WHERE productName IS NULL;
#需求: 查询货品名称带有 'M' 的所有信息
SELECT id,productName From product WHERE productName LIKE '%M%';
#需求: 查询匹配货品名称 '罗技M9?' 的所有信息
SELECT id,productName From product WHERE productName LIKE '罗技M9_';
#需求: 查询匹配货品名称 '罗技M9??' 的所有信息
SELECT id,productName From product WHERE productName LIKE '罗技M9__';
#4
#需求：选择所有信息并且按零售价升序/降序排序
SELECT * FROM product ORDER BY salePrice;
SELECT * FROM product ORDER BY salePrice ASC;
SELECT * FROM product ORDER BY salePrice DESC;
#需求：选择id，货品名称，分类编号,零售价先按分类编号排序,编号相同的再按零售价降序排序
SELECT id,productName,dir_id,salePrice FROM product ORDER BY dir_id,salePrice DESC;
#需求:查询M系列并按照批发价排序(加上别名)
SELECT id,productName,salePrice pf FROM product WHERE productName LIKE '%M%' ORDER BY pf DESC;
#需求:查询分类为2并按照批发价升序/降序排序(加上别名)
SELECT id,productName,dir_id,salePrice pf FROM product WHERE dir_id=2 ORDER BY salePrice;
SELECT id,productName,dir_id,salePrice pf FROM product WHERE dir_id=2 ORDER BY salePrice DESC;
#5.
#需求:查询所有商品平均零售价
SELECT AVG(salePrice) FROM product;
#需求:查询商品总记录数
SELECT COUNT(id) FROM product;
#需求:查询分类为2的商品总数
SELECT COUNT(id) FROM product WHERE dir_id=2;
#需求:查询商品的最小零售价，最高零售价，以及所有商品零售价总和
SELECT MIN(salePrice),MAX(salePrice),SUM(salePrice) FROM product;

#6.-------------------------------------------
#需求:查询所有的货品信息+对应的货品分类信息----------
#显示内连接
SELECT * FROM product,productdir WHERE product.dir_id=productdir.id;
#隐示内连接
SELECT * FROM product p JOIN productdir pd ON p.dir_id=pd.id;

#需求: 查询零售价大于200的无线鼠标------------
#显示内连接
SELECT * FROM product,productdir WHERE salePrice>200 AND dirName='无线鼠标' AND product.dir_id=productdir.id;
#隐示内连接
SELECT * FROM product p JOIN productdir pd ON salePrice>200 AND dirName='无线鼠标' AND p.dir_id=pd.id;

#需求: 查询每个货品对应的分类以及对应的库存---------
#显示内连接
SELECT p.productName,pd.dirName,pc.storeNum FROM product p,productdir pd,productstock pc WHERE p.dir_id=pd.id AND p.dir_id=pc.product_id;
#隐示内连接
SELECT p.productName,pd.dirName,pc.storeNum FROM product p JOIN productdir pd JOIN productstock pc ON p.dir_id=pd.id AND p.dir_id=pc.product_id;

#需求: 如果库存货品都销售完成,按照利润(profit)从高到低查询货品名称,零售价,货品分类（三张表）.-------------
#显示内连接
SELECT p.id,p.productName,p.salePrice,pd.dirName,(p.salePrice-p.costPrice)*pc.storeNum profit
FROM product p,productdir pd,productstock pc
WHERE p.dir_id=pd.id AND p.dir_id=pc.product_id
ORDER BY profit;
#隐示内连接
SELECT p.id,p.productName,p.salePrice,pd.dirName,(p.salePrice-p.costPrice)*pc.storeNum profit
FROM product p JOIN productdir pd JOIN productstock pc
ON p.dir_id=pd.id AND p.dir_id=pc.product_id
ORDER BY profit;

#需求:查询出所有商品编号对于商品的所有信息
SELECT * FROM product p LEFT JOIN productdir pd ON p.dir_id=pd.id;

SELECT * FROM product p RIGHT JOIN productdir pd ON p.dir_id = pd.id;

#7.
#需求: 查询每个商品分类的名称和父分类名称:
#显示内连接
SELECT p.id,p.dirName,pt.dirName FROM productdir p,productdir pt WHERE p.parent_id=pt.id;
#隐示内连接
SELECT p.id,p.dirName,pt.dirName FROM productdir p JOIN productdir pt ON p.parent_id=pt.id;

#需求: 查询出所有的顶级分类
SELECT * FROM productdir WHERE parent_id IS NULL;

SELECT * FROM product WHERE supplier IS NULL;

#8.
#1.插入完整数据记录/空值
INSERT INTO productdir VALUES(101,'机械键盘',5);
#2.插入数据记录一部分
INSERT productdir (id,dirName,parent_id) VALUES(NULL,'老人键盘',5);
INSERT productdir(dirName) VALUES('键盘');
INSERT productdir(dirName) VALUES(NULL);
#3.插入查询出来的结果
INSERT productdir(dirName,parent_id)
SELECT dirName,parent_id FROM productdir;

#删除数据
DELETE FROM productdir WHERE id = 105;

DELETE FROM productdir WHERE id > 100;

#需求:将零售价大于300的商品的零售价上调0.2倍
UPDATE product SET salePrice = salePrice*1.2 WHERE salePrice > 300;

UPDATE product SET salePrice = salePrice/1.2 WHERE salePrice > 300;
#需求:将零售价大于300的有线鼠标的零售价上调0.1倍
UPDATE product p,productdir pd SET salePrice = salePrice*1.1
WHERE p.dir_id = pd.id AND pd.dirName = '有线鼠标' AND p.salePrice > 300;

#分组 (主要是统计的功能) GROUP BY HAVING

SELECT dir_id,COUNT(id) FROM product GROUP BY dir_id;

SELECT dir_id,COUNT(id) AS '统计数量' FROM product
GROUP BY dir_id HAVING dir_id > 2;