TASK-03复杂查询方法-视图、子查询、函数等

最新推荐文章于 2024-10-13 10:00:12 发布

ALVIN23333

最新推荐文章于 2024-10-13 10:00:12 发布

阅读量1.3k

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/ALVIN23333/article/details/123435661

版权

本文档介绍了阿里云天池龙珠计划SQL训练营中的视图概念、创建与查询、子查询的嵌套与关联，以及常用函数如CONCAT、LENGTH等。通过实例展示了如何运用视图和子查询进行数据操作，适合SQL初学者进阶实践。

摘要由CSDN通过智能技术生成

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

一、视图

1.视图的概念

视图本质上是一个虚拟的表，是没有数据的，是原表的一个窗口

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

2.创建视图

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

3.对视图进行查询

SELECT sale_price, shop_name FROM view_shop_product WHERE product_type = '衣服'; #本质查询逻辑跟普通的表查询一致#

4.修改和删除视图

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

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

二、子查询

1.嵌套子查询

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;

2.标量子查询

SELECT product_id, product_name, sale_price FROM product WHERE sale_price > (

SELECT AVG(sale_price) FROM product ） #这一部分就是为标量子查询#

3.关联子查询

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 #这一部分就是将一个表中的不同条件关联起来了，先执行父查询，再执行子查询#

);

练习题-第一部分代码

1.create view ViewPractice5_1

SELECT product_type, sale_price, regist_date
FROM product
WHERE sale_price>=1000 AND regist_date='2009-09-20';

2.首先视图里面会多出一行为刀子，300，2009-11-02，其次数据原表里面对应的字段也会更新

3.SELECT product_id, product_name, product_type, sale_price,( SELECT avg( sale_price ) FROM product ) AS sale_price_all
FROM product；

4.CREATE VIEW avgpricebytype AS SELECT
product_id,
product_name,
product_type,
sale_price,(
   SELECT
       avg( sale_price )
   FROM
       product b
   WHERE
       a.product_type = b.product_type
   GROUP BY
       a.product_type
   ) avg_sale_price
FROM
   product a

三、各种各样的函数

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

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

LENGTH – 字符串长度

语法：LENGTH( 字符串 )

LOWER – 小写转换

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

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

REPLACE – 字符串的替换

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

SUBSTRING – 字符串的截取

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

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

（扩展内容）SUBSTRING_INDEX – 字符串按索引截取

语法：SUBSTRING_INDEX (原始字符串，分隔符，n)

可以嵌套SUBSTRING_INDEX（SUBSTRING_INDEX (原始字符串，分隔符，n)，分隔符，n）

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

不同DBMS的日期函数语法各有不同，本课程介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。

CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”

“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
COALESCE – 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS
四、CASE 表达式.
语法

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

注意：无论多么庞大的 CASE 表达式，最后也只会返回一个值。

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

练习题-第二部分

1.是

2.第一个运行成功，第二个因为有NULL所以全部返回NULL

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