[阿里云天池龙珠计划] SQL训练营-Task03：复杂查询方法-视图、子查询、函数等

最新推荐文章于 2023-06-08 16:44:09 发布

库乐

最新推荐文章于 2023-06-08 16:44:09 发布

阅读量239

点赞数

分类专栏： sql 文章标签： sql

原文链接：https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9059755190.6.73dc4cb31W2lwN&postId=167460

版权

sql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

课程链接：https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9059755190.6.34b14cb3MR7X4p&postId=167460

文章目录

理论学习
代码实践
- 3.1
- 3.2
- 3.3
- 3.4
- 3.5
- 3.6
- 3.7
学习心得

理论学习

3.1 视图

3.1.1 什么是视图

视图是一个虚拟的表，是依据SELECT语句来创建的，，然后在这张虚拟表上做SQL操作。

3.1.2 视图与表有什么区别

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
在这里插入图片描述

图片来源：《sql基础教程第2版》

3.1.3 为什么会存在视图

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

3.1.4 如何创建视图

基本语法如下：

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

在这里插入图片描述图片来源：《sql基础教程第2版》
虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

注意事项：定义视图时不能使用ORDER BY语句！
因为和表一样，数据行都是没有顺序的。在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 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;

创建的视图如下图所示
在这里插入图片描述

3.1.5 如何修改视图结构

基本语法如下：

ALTER VIEW <视图名> AS <SELECT语句>

我们修改上方的productSum视图为

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

此时productSum视图内容如下图所示
在这里插入图片描述

3.1.6 如何更新视图内容（不推荐）

以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。

如果原表可以更新，那么视图中的数据也可以更新。

尝试更新一下视图

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

此时我们再查看productSum视图，可以发现数据已经更新了
在这里插入图片描述

此时观察原表也可以发现数据也被更新了

在这里插入图片描述 product_type='办公用品’的商品的sale_price=5000，为什么原表的数据只有一条做了修改呢？

还是因为视图的定义，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

3.1.7 如何删除视图

基本语法如下：

DROP VIEW <视图名1> [ , <视图名2> …]

3.2 子查询

3.2.1 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

3.2.2 子查询和视图的关系

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

3.2.3 嵌套子查询

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

3.2.4 标量子查询

标量就是单一的意思,所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表

product_id | product_name | sale_price
------------ + ------------- + ----------
0003 | 运动T恤 | 4000
0004 | 菜刀 | 3000
0005 | 高压锅 | 6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。

3.2.5 标量子查询有什么用

通过标量子查询语句查询出销售单价高于平均销售单价的商品。

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。

我们还可以这样使用标量子查询：

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

3.2.6 关联子查询

先看一个例子：

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (	SELECT AVG(sale_price)
                       	FROM product AS p2
                      	WHERE p1.product_type = p2.product_type
   						GROUP BY product_type);

先来看一下这个例子的执行结果
在这里插入图片描述
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。

查询出销售单价高于平均销售单价的商品，这个例子的SQL语句如下

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

需求选取出各商品种类中高于该商品种类的平均销售单价的商品 的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);

在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。

简要的概括为：

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

3.3 各种各样的函数

函数大致分为如下几类：

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

3.3.1 算数函数

ABS – 绝对值
语法：ABS( 数值 )。当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD – 求余数
语法：MOD( 被除数，除数 )
ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )

3.3.2 字符串函数

CONCAT – 拼接
语法：CONCAT(str1, str2, str3)
LENGTH – 字符串长度
语法：LENGTH( 字符串 )
LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。
UPPER 函数用于大写转换
REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）。使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

举例：
在这里插入图片描述

SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串，分隔符，n)。该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql                                |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com                                 |
+-------------------------------------------+
1 row in set (0.00 sec)

获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 1) |
+------------------------------------------+
| www                                      |
+------------------------------------------+
1 row in set (0.00 sec)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql                                                              |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

3.3.3 日期函数

CURRENT_DATE – 获取当前日期

SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2020-08-08   |
+--------------+
1 row in set (0.00 sec)

CURRENT_TIME – 当前时间

SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 17:26:09     |
+--------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP – 当前日期和时间

SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2020-08-08 17:27:07 |
+---------------------+
1 row in set (0.00 sec)

EXTRACT – 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)。使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
+---------------------+------+-------+------+------+--------+--------+
| now                 | year | month | day  | hour | MINute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)

3.3.4 转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。

CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
+---------+
| int_col |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)

COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)。COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
+-------+-------------+------------+
| col_1 | col_2       | col_3      |
+-------+-------------+------------+
|    11 | hello world | 2020-11-01 |
+-------+-------------+------------+
1 row in set (0.00 sec)

3.4 谓词

3.4.1 什么是谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

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

创建一张表：

-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
);
-- DML ：插入数据
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');

SELECT * FROM samplelike;

在这里插入图片描述

前方一致：选取出以“ddd”起始的字符串

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';

结果：选取出“dddabc”
其中的%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。

中间一致：含有“ddd”的字符串，无论哪个位置：

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';

结果：选取出“abcddd”,“dddabc”,“abdddc”

后方一致：选取出以“ddd”结尾的字符串

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';

结果：选取出选取出“abcddd“

_下划线匹配任意 1 个字符

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc_d';

结果选出：‘abcdd’

3.4.3 BETWEEN谓词 – 用于范围查询

BETWEEN 可以进行范围查询，需要使用 3 个参数，是闭区间！
举例：

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

如果不想让结果中包含临界值，那就必须使用 < 和 >

SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;

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

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

想要选取 NULL 以外的数据时，需要使用IS NOT NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

3.4.5 IN谓词 – OR的简便用法

IN(值1, 值2, 值3, …)来替换下述 SQL 语句。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以用NOT IN来实现。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);

IN 和 NOT IN 时是无法选取出NULL数据的!!

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

一下两个sql语句结果都是一样的：
取出大阪门店的在售商品 product_id ;取出大阪门店在售商品的销售单价 sale_price

-- 子查询展开后的结果
SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

SELECT product_name, sale_price
FROM product
WHERE product_id IN (	SELECT product_id
					  	FROM shopproduct
                       	WHERE shop_id = '000C');

既然 in 谓词也能实现，那为什么还要使用子查询呢？
某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句！！！！

3.4.7 EXIST 谓词

EXIST（存在）谓词的主语是“记录”。使用 EXIST 选取出大阪门店在售商品的销售单价。

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT *
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);

由于通过条件“SP.product_id = P.product_id”将 product 表和 shopproduct 表进行了联接，因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。

因此，使用下面的查询语句，查询结果也不会发生变化。

SELECT product_name, sale_price
  FROM product AS p
 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
                 FROM shopproduct AS sp
                WHERE sp.shop_id = '000C'
                  AND sp.product_id = p.product_id);

NOT IN 也可以用NOT EXIST来替换。当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

3.5 CASE 表达式

3.5.1 什么是 CASE 表达式？

语法：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

3.5.2 CASE表达式的使用方法

应用场景1：根据不同分支得到不同列值

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;

在这里插入图片描述

应用场景2：实现列方向上的聚合
如下代码实现行的方向上不同种类的聚合：

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;

聚合函数 + CASE WHEN 表达式即可实现该效果

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;

应用场景3：实现行转列

-- CASE WHEN 实现数字列 score 行转列
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;

也可以实现文本列 subject 的行转列

-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
       MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
       MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
       MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
  FROM score
 GROUP BY name;

在这里插入图片描述

总结：

当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

代码实践

3.1

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date
-------------- + ------------ + ------------
T恤衫 | 　 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20

回答：

CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price > 1000 AND regist_date = '2009-09-20';

3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (’ 刀子 ', 300, ‘2009-11-02’);

回答：插入时报错，视图中插入数据，原表也会插入。但是原表有三列需要满足NOT NULL的约束条件。

3.3

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

product_id | product_name | product_type | sale_price | sale_price_all
------------ + ------------- + -------------- + ------------ + ---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000

回答：

SELECT 	product_id, product_name, product_type, sale_price, 
		(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;

3.4

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

product_id | product_name | product_type | sale_price | avg_sale_price
------------±------------±-------------±-----------±--------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000

提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

回答：

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;

3.5

运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
回答：正确

3.6

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

回答：
购买价格不在500,2800，5000的产品，不包含purchase_price 为NULL

②

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

回答：
返回0条记录，NOT IN参数，不能包含 NULL

3.7

按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
---------- + ----------- + ------------
5 | 1 | 2

SELECT SUM(SELECT CASE WHEN sale_price<=1000 THEN 1 ELSE 0 END) AS low_price ,
SUM(SELECT CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price , 
SUM(SELECT CASE WHEN sale_price>=3001 THEN 1 ELSE 0 END) AS high_price,
FROM product;

学习心得

这一章的内容好多，而且相互结合可以解决很多问题。需要多见识一些例子。

库乐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[阿里云天池龙珠计划] SQL训练营-Task03：复杂查询方法-视图、子查询、函数等

课程链接：https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9059755190.6.34b14cb3MR7X4p&postId=167460文章目录理论学习3.1 视图3.1.1 什么是视图3.1.2 视图与表有什么区别3.1.3 为什么会存在视图3.1.4 如何创建视图基于单表的视图基于多表的视图3.1.5 如何修改视图结构3.1.6 如何更新视图内容（不推荐）3.1.7 如何删除视图3.2 子查询3.2.1 什么是
复制链接

扫一扫