MySQL 笔记（练习题）

#1#需求:查询所有货品信息SELECT * FROM product;#需求:查询所有货品的id,supplier,cutoffSELECT 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或者成本大于等于200SELECT 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) FROMproduct;#需求:查询分类为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 profitFROM product p,productdir pd,productstock pc WHERE p.dir_id=pd.id AND p.dir_id=pc.product_idORDER BY profit;#隐示内连接SELECT p.id,p.productName,p.salePrice,pd.dirName,(p.salePrice-p.costPrice)*pc.storeNum profitFROM product p JOIN productdir pd JOIN productstock pc ON p.dir_id=pd.id AND p.dir_id=pc.product_idORDER 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.1WHERE p.dir_id = pd.id AND pd.dirName = '有线鼠标' AND p.salePrice > 300;#分组 (主要是统计的功能) GROUP BY HAVINGSELECT 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;