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

最新推荐文章于 2023-05-20 17:40:22 发布

刚哥的大数据之路

最新推荐文章于 2023-05-20 17:40:22 发布

阅读量115

点赞数

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

本文链接：https://blog.csdn.net/qq_43508211/article/details/116489568

版权

中文专栏收录该内容

31 篇文章 0 订阅

订阅专栏

Task03：复杂查询方法-视图、子查询、函数等
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 什么是子查询
3.2.2 子查询和视图的关系
3.2.3 嵌套子查询
3.2.4 标量子查询
3.2.5 标量子查询有什么用
3.2.6 关联子查询
小结
练习题-第一部分
3.1
3.2
3.3
3.4
3.3 各种各样的函数
3.3.1 算数函数
3.3.2 字符串函数
3.3.3 日期函数
3.3.4 转换函数
3.4 谓词
3.4.1 什么是谓词
3.4.2 LIKE谓词 – 用于字符串的部分一致查询
3.4.3 BETWEEN谓词 – 用于范围查询
3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
3.4.5 IN谓词 – OR的简便用法
3.4.6 使用子查询作为IN谓词的参数
3.4.7 EXIST 谓词
3.5 CASE 表达式
3.5.1 什么是 CASE 表达式？
3.5.2 CASE表达式的使用方法
练习题-第二部分
3.5
3.6
3.7

SQL训练营页面地址：https://tianchi.aliyun.com/specials/promotion/aicampsql
天池龙珠计划训练营地址：https://tianchi.aliyun.com/specials/promotion/aicamps

之前接触了sql基本的查询用法，接下来介绍一些相对复杂的用法。

SELECT stu_name FROM view_students_info;

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

“视图不是表，视图是虚表，视图依赖于表”

3.1.4 如何创建视图
需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。
这是因为视图和表一样，数据行都是没有顺序的。

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

单表视图和多表视图

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

3.1.6 如何更新视图内容
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
修改视图的内容也要修改表中的内容，这样才能做到表和视图的数据一致

DROP VIEW productSum;

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

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

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

标量子查询=单一子查询
关联子查询

在这里插入图片描述

-- 创建视图的语句
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;

sql函数篇
3.3.1 算数函数
ABS( 数值 )
MOD( 被除数，除数 ) 求余数
ROUND( 对象数值，保留小数的位数 ) 四舍五入

3.3.2 字符串函数
CONCAT(str1, str2, str3) 拼接字符串
LENGTH( 字符串 )
LOWER – 小写转换
UPPER 函数用于大写转换
REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

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

（扩展内容）SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串，分隔符，n)

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

3.3.3 日期函数
CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
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;

3.3.4 转换函数
cast:1.类型转换 2.值的转换
语法：CAST（转换前的值 AS 想要转换的数据类型）

COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)

3.4 谓词
TRUE / FALSE / UNKNOWN
LIKE 模糊查询
BETWEEN 范围查询
IS NULL、IS NOT NULL 用于判断是否为NULL
IN OR的简便用法
需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。
EXISTS （难度较高）

3.5 CASE 表达式

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

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;