ai训练营sql task2 代码笔记

主要学习了视图，子查询（关联子查询☆），谓词（case表达式☆）

视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

单表视图

CREATE VIEW productsum (product_type, cnt_product)

AS

SELECT product_type, COUNT(*)

  FROM product

 GROUP BY product_type ;                不使用order by语句

多表视图

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)

AS

SELECT product_type, sale_price, shop_name

  FROM product,

       shop_product

 WHERE product.product_id = shop_product.product_id;

修改视图：

ALTER VIEW productSum

    AS

        SELECT product_type, sale_price

          FROM Product

         WHERE regist_date > '2009-09-11';

删除视图  DROP VIEW productSum;

标量子查询：返回一个值

SELECT product_id, product_name, sale_price

  FROM product

 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

关联子查询：查询与子查询的关系

SELECT product_type, product_name, sale_price

  FROM product ASp1

 WHERE sale_price > (SELECT AVG(sale_price)

   FROM product ASp2    WHERE p1.product_type =p2.product_type

   GROUP BY product_type);  选取出各商品种类中高于该商品种类的平均销售单价的商品

习题：

Create view avgpricebytype as

Select product_id,product_name,product_type,sale_price,(select avg(sale_price)from product group by product_type)as avg_sale_price from product;  错误。子查询并没有与外层查询的每一行（即每个产品）建立关联,仅仅返回相同值（每种产品类型的平均价格）

Create view avgpricebytype as

Select product_id,product_name,product_type,sale_price,(select avg(sale_price)from product p2 where p1.product_type=p2.product_type group by p1.product_type)as avg_sale_price from product p1;

Select * from avgpricebytype

谓词：

cast转换函数  SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

like：

SELECT *

FROM samplelike

WHERE strcol LIKE 'ddd%';查询ddd起始的字符串   %字符个数不限；_只代表一个字符

between：

SELECT product_name, sale_price

FROM product

WHERE sale_price BETWEEN 100 AND 1000; 查范围闭区间

in：

SELECT product_name, purchase_price

FROM product

WHERE purchase_price IN (320, 500, 5000); or的简便使用，in后也可以添加子查询结果

exist：

EXIST 的左侧并没有任何参数

WHERE EXISTS (SELECT *

                 FROM shopproduct AS sp

                WHERE sp.shop_id = '000C'

                  AND sp.product_id = p.product_id);

case表达式：

CONCAT 拼接函数，语法：CONCAT(str1, str2, str3)

SELECT  product_name,

        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)

             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)

             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)

             ELSE NULL

        END AS abc_product_type

  FROM  product;

习题：

SELECT product_name, purchase_price

  FROM product

 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);  错误 ：当使用NOT IN子句时，如果指定的值列表中包含NULL，则结果可能不会如预期般工作。

改成
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000) AND purchase_price IS NOT NULL;

习题：

select sum(case when `sale_price` <= 1000 then 1 else 0 end) as low_price,
sum(case when `sale_price` > 1000 and `sale_price` <= 3000 then 1 else 0 end) as mid_price,
sum(case when `sale_price` > 3000 then 1 else 0 end) as high_price
from product;    对返回的1进行求和