阿里云天池机器学习task3

目录

Task03：复杂查询方法-视图、子查询、函数等

一、视图

二、子查询

标量子查询

 关联子查询

三、实用函数

算数函数

字符串函数

日期函数

转换函数

四、谓词

LIKE谓词 – 用于字符串的部分一致查询

BETWEEN谓词 – 用于范围查询

IS NULL、 IS NOT NULL – 用于判断是否为NULL

IN谓词 – OR的简便用法

使用子查询作为IN谓词的参数

EXIST 谓词

五、CASE 表达式

六、练习题

七、总结

Task03：复杂查询方法-视图、子查询、函数等
一、视图
视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍）。

视图与表的最大区别即：是否保存了实际的数据。视图可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

视图最主要的优势就是通过定义视图可以将频繁使用的SELECT语句保存以提高效率。

/*创建视图的基本语法如下：
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>  
*/
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图，但是这种操作应当避免，因为多重视图会降低效能。

定义视图时一般不能使用ORDER BY语句。因为视图和表一样，数据行都是没有顺序的。

基于单表的视图
我们在product表的基础上创建一个视图，如下：

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;
创建的视图如下图所示：

基于多表的视图
为了学习多表视图，我们再创建一张表，相关代码如下：

CREATE TABLE shop_product
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',    '东京',        '0001',    30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',    '东京',        '0002',    50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',    '东京',        '0003',    15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',    '名古屋',    '0002',    30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',    '名古屋',    '0003',    120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',    '名古屋',    '0004',    20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',    '名古屋',    '0006',    10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',    '名古屋',    '0007',    40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',    '大阪',        '0003',    20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',    '大阪',        '0004',    50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',    '大阪',        '0006',    90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',    '大阪',        '0007',    70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',    '福冈',        '0001',    100);
我们在product表和shop_product表的基础上创建视图。

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;
创建的视图如下图所示

我们可以在这个视图的基础上进行查询

SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';
查询结果为：

视图的修改、更新、删除操作（一般皆不常用）如下：

/* 修改视图语句：
ALTER VIEW <视图名> AS <SELECT语句>
*/
 
-- 其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。
 
/*一般不建议更新视图(update),不允许通过视图来修改表，例如：
UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';
视图虽然更新，但原表可能未按预期结果修改。
*/
 
/* 删除视图语句：
DROP VIEW <视图名1> [ , <视图名2> …]
*/
二、子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

标量子查询
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表，执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800
查询出销售单价高于平均销售单价的商品语句：

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);  -- 由于WHERE子句中不能使用聚合函数，因此，要使用子查询
上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;                      -- 因为先计算子查询，得出的结果在外部SELECT中当成常数，最后筛选出所有行的数据。
 
SELECT product_id,
       product_name,
       sale_price,
       AVG(sale_price) as avg_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);
该SQL语句将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。运行结果如下图所示

关联查询的执行过程可以简要的概括如下：

首先执行不带WHERE的主查询
根据主查询结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句
在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可，不用刻意记忆。

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);
再介绍一个工作中实用的with方法：

with p1 as     -- with <别名> as (select 语句) 其实是一种视图命名的方法
(
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);)
 
select product_type from p1   -- 调用起来极其方便，减少了大量嵌套子查询的书写过程。
三、实用函数
以下介绍SQL中的常用函数