MySQL 笔记（练习题）

朴灿烈づ我的快乐病毒、 2022-06-09 07:14 179阅读 0赞

#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;