阿里云——天池SQL训练营Task03

最新推荐文章于 2024-07-03 12:13:04 发布

m0_56694233

最新推荐文章于 2024-07-03 12:13:04 发布

阅读量109

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/m0_56694233/article/details/124214775

版权

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

一.视图

1.1什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

1.2视图与表的区别

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

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

1.3为什么存在视图

（1）通过定义视图可以将频繁使用的SELECT语句保存以提高效率。

（2）通过定义视图可以使用户看到的数据更加清晰。

（3）通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。

（4）通过定义视图可以降低数据的冗余。

1.4如何创建视图

基本语法：

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

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

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。

1.5如何修改视图结构

基本语法：

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

视图名在数据库中需要是唯一的，不能与其他视图和表重名。也可以通过将当前视图删除然后重新创建的方式达到修改的效果。

1.6如何更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

(1)聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

(2)DISTINCT 关键字。

(3)GROUP BY 子句。

(4)HAVING 子句。

(5)UNION 或 UNION ALL 运算符。

(6)FROM 子句中包含多个表。

1.7如何删除视图

基本语法：

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

注意：需要有相应的权限才能成功删除。

二.子查询

2.1什么是子查询

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

2.2子查询与视图的关系

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

2.3嵌套子查询

嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

2.4标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询，所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行或某一列。

2.5标量子查询的作用

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。

2.6关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。

简要概括为：

（1）首先执行不带WHERE的主查询

（2）根据主查询讯结果匹配product_type，获取子查询结果

（3）将子查询结果再与主查询结合执行完整的SQL语句

三.各种各样的函数

函数大致分类：

（1）算术函数（用来进行数值计算的函数）

（2）字符串函数（用来进行字符串操作的函数）

（3）日期函数（用来进行日期操作的函数）

（4）转换函数（用来转换数据类型和值的函数）

（5）聚合函数（用来进行数据聚合的函数）

3.1算数函数

+ - * /四则运算在之前的章节介绍过，此处不再赘述。

为了演示其他的几个算数函数，在此构造samplemath表

-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);

-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| m | n | p |
+----------+------+------+
| 500.000 | 0 | NULL |
| -180.000 | 0 | NULL |
| NULL | NULL | NULL |
| NULL | 7 | 3 |
| NULL | 5 | 2 |
| NULL | 4 | NULL |
| 8.000 | NULL | 3 |
| 2.270 | 1 | NULL |
| 5.555 | 2 | NULL |
| NULL | 1 | NULL |
| 8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)

ABS – 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

当 ABS 函数的参数为NULL时，返回值也是NULL。

MOD – 求余数

语法：MOD( 被除数，除数 )

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

ROUND – 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。

注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

3.2字符串函数

字符串函数也经常被使用，为了学习字符串函数，在此我们构造samplestr表。

-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',   'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',   '月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',   NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;
+-----------+------+------+
| str1 | str2 | str3 |
+-----------+------+------+
| opx | rt | NULL |
| abc | def | NULL |
| 太阳 | 月亮 | 火星 |
| aaa | NULL | NULL |
| NULL | xyz | NULL |
| @!#$% | NULL | NULL |
| ABC | NULL | NULL |
| aBC | NULL | NULL |
| abc哈哈 | abc | ABC |
| abcdefabc | abc | ABC |
| micmic | i | I |
+-----------+------+------+
11 rows in set (0.00 sec)

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

MySQL中使用 CONCAT 函数进行拼接。

LENGTH – 字符串长度

语法：LENGTH( 字符串 )

LOWER – 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。

REPLACE – 字符串的替换

语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取

语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

3.3日期函数

CURRENT_DATE – 获取当前日期

CURRENT_TIME – 当前时间

CURRENT_TIMESTAMP – 当前日期和时间

EXTRACT – 截取日期元素

3.4转换函数

CAST – 类型转换

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

COALESCE – 将NULL转换为其他值

语法：COALESCE(数据1，数据2，数据3……)

`四.谓词`

4.1什么是谓词

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

谓词主要有以下几个：

（1）LIKE

（2）BETWEEN

（3）IS NULL、IS NOT NULL

（4）IN

（5）EXISTS

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

当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

4.3BETWEEN谓词 – 用于范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。

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

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

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

4.5IN谓词 – OR的简便用法

多个查询条件取并集时可以选择使用or语句。

随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时，我们就可以使用IN 谓词。

需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。

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

IN和子查询

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。我们已经在 5-2 节中学习过了，子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

既然 in 谓词也能实现，那为什么还要使用子查询呢？这里给出两点原因：

①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；

②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

4.7EXIST 谓词

EXIST谓词的使用方法：

谓词的作用就是 “判断是否存在满足某种条件的记录”。

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXIST（存在）谓词的主语是“记录”。

EXIST的参数：
之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

子查询中的SELECT *
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。

使用NOT EXIST替换NOT IN
就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

五.case表达式

5.1什么是CASE表达式？

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

5.2CASE表达式的使用方法

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

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显示地写出 ELSE 子句。
此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。

应用场景2：实现列方向上的聚合

聚合函数 + CASE WHEN 表达式即可实现在列的方向上展示不同种类额聚合值。

应用场景3：实现行转列

聚合函数 + CASE WHEN 表达式即可实现该转换

总结：

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

练习

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

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

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
答：插入时报错，视图中插入数据，原表也会插入。但是原表有三列需要满足NOT NULL的约束条件。

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;

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;

5.运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

答：正确.

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

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

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

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

（2）返回0条记录，NOT IN参数，不能包含 NULL

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;

m0_56694233

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
阿里云——天池SQL训练营Task03

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；一.视图1.1什么是视图视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。1.2视图与表的区别区别在于：是否保存了实际的数据。视图并不是数据库真实存储的数据表，它可以看作是一个窗口
复制链接

扫一扫