【SQL03】复杂一点的查询

最新推荐文章于 2023-01-03 09:00:00 发布
最新推荐文章于 2023-01-03 09:00:00 发布
阅读量173 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/apple_50678962/article/details/111439306
版权

感谢datawhale开展的每月组队学习，本章教材ref:SQL Task03

视图

在这里插入图片描述

常见操作

** 创建视图**

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

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

我们可以直接从数据表创建视图，也可以从视图创建视图（但，不要这样，性能会很慢）
视图名必须是unique的，不可以和别的视图/表重名

** 修改视图 **

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

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

在这里插入图片描述

  • 视图的列名会随着SELECT改变诶，不用自己去写

** 更新视图 **
在这里插入图片描述
原本的product表的这个地方也会随之改变。这有点像shallow copy浅拷贝的意思？视图像是一个reference。

虽然这样可以修改成功，但是不建议使用这种方式。
我们在创建视图时也尽量不要通过视图修改表
是的，shallow copy、deep copy虽然好理解，但是有的时候在实际应用中是很容易没注意就把原数据改了。还是不要用更新视图内容比较好
在这里插入图片描述

删除视图
在这里插入图片描述

子查询

在这里插入图片描述

  • 查询出销售单价高于平均销售单价的商品

    -- 销售单价高于平均销售单价的商品
SELECT 
	*
FROM 
	product
WHERE 
	sale_price > (SELECT AVG(sale_price) FROM product);

  • 查询出注册日期最晚的那个商品

    -- 查询出注册日期最晚的那个商品
SELECT *
FROM product
WHERE regist_date = (SELECT MAX(regist_date) FROM product);

  • 你能猜到这段代码的运行结果是什么吗？运行一下看看与你想象的结果是否一致

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

    从product表中获取三个列信息：product_id, product_name, sale_price, 算出表中所有产品的平均售价放在最后一列

  • 你能理解这个例子在做什么操作么

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

    用JOIN可以改写成

    	SELECT
   	a.product_type, product_name, sale_price
FROM
	product AS a
	LEFT JOIN (
  	SELECT b.product_type, AVG(sale_price) as average_price
	FROM product AS b
	GROUP BY b.product_type
) as c
ON
a.product_type = c.product_type
WHERE
	sale_price > average_price;

    教材给出的query结构截图可能有点问题…因为在UPDATE VIEW更新视图的时候，原表就已经被改动掉了，办公用品类的有两个：一个打孔器500元，圆珠笔被改成了5000元了已经。但是这里给出的output截图，比自己产品类平均值还高的商品还是打孔器（500元）。
    emmm… 也有可能是写教材的大佬做了什么不commit/rollback操作…

各种各样的函数

算数函数

在这里插入图片描述

字符串函数

在这里插入图片描述

日期函数

在这里插入图片描述

转换函数

在这里插入图片描述

谓语

在这里插入图片描述

CASE 表达式

在这里插入图片描述

练习题

  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

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

    小tips：
    可以在创建视图之前先执行一下后面的SELECT语句，看一下是否正确。
    我一开始粗心大于等于没写等于，执行结果少一行，再drop view，再重新创建… 也算是做个删除视图的练习吧 😅
    在这里插入图片描述

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

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

    我的猜想：大概是会在原表中也增加一行？那没有value的那些其他列的值会为NULL？❌
    我的猜想是错误的，因为：这个视图是原表的一个窗口，而原表product中 我们设置product_id, product_type应当是 NOT NULL的，如果我们通过这个视图以这样的方式insert数据，我们无法给要求不能为空的product_id和product_type赋值。因此MySQL不允许我们这样做。
    从给出的报错信息来看，如果我们给product_id和product_type提前设置一个默认值，比如product_id自动increment或许是可以实现的。
    在这里插入图片描述
    如果要创建一个updatable的视图的话，创建的那个SELECT子句里不能包含：

    • 聚合函数
    • DISTINCT
    • GROUP BY 子句
    • HAVING 子句
    • UNION、UNION ALL 子句
    • LEFT JOIN、OUTER JOIN
    • SELECT 或是 WHERE 子句 中有 subquery 子查询
    • 从另一个不可updatable的视图创建
    • reference only to literal values?
    • multiple references to any column of the base table

    可以使用

    	SELECT
    	table_name,
    	is_updatable
	FROM
    	information_schema.VIEWS
	WHERE
 		TABLE_SCHEMA = 'classicmodels';

    来查看视图是不是updatable

  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

    CREATE VIEW AvgPriceByType
AS
SELECT
       product_id,
       product_name,
       a.product_type,
       a.sale_price,
       b.avg_sale_price
FROM
     product a,
     (
     SELECT
        product_type, AVG(sale_price) as avg_sale_price
    FROM
         product
    GROUP BY
        product_type
         ) b
WHERE
    a.product_type = b.product_type;

    思路：
    在这里插入图片描述

  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

    SELECT
       product_id,
       product_name,
       a.product_type,
       a.sale_price,
       b.avg_sale_price
FROM
     product a,
     (SELECT
        product_type, AVG(sale_price) as avg_sale_price
	  FROM
	    product
	  GROUP BY
        product_type) b
WHERE
    a.product_type = b.product_type;

    思路
    在这里插入图片描述

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

    感觉… emmm是的吧，最起码大部分是的
    可以慢慢总结一下
    函数中含有NULL 结果全都变为NULL的有:
    - CONCAT(str1, str2, NULL) --> NULL

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

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

    从表product中获取产品名称和购买价格, 取出的产品应满足：购买价格不是 500元，不是2800元，不是5000元（统统只要一块钱，不是
    在这里插入图片描述

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

    空
    因为 WHERE purchase_price NOT IN (500, 2800, 5000, NULL) 转换成OR来写的话就是

    	WHERE purchase_price <> 500
	OR purchase_price <> 2800
	OR purchase_price <> 5000
	OR purchase_price <> NULL

    那我们上期学过了，取NULL或取非NULL如果用 = 或 <> 结果都会是空

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

    • 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
    • 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
    • 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

    请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

    执行结果

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

    	-- solution 1
SELECT COUNT(CASE WHEN sale_price<=1000 THEN product_id ELSE NULL END) as low_price,
       COUNT(CASE WHEN 1000<sale_price AND sale_price<=3000 THEN product_id ELSE NULL END) as mid_price,
       COUNT(CASE WHEN sale_price>3000 THEN product_id ELSE NULL END) as high_price
FROM product;

-- solution 2
SELECT SUM(CASE WHEN sale_price<=1000 THEN 1 ELSE 0 END) as low_price,
       SUM(CASE WHEN 1000<sale_price AND sale_price<=3000 THEN 1 ELSE 0 END) as mid_price,
       SUM(CASE WHEN sale_price>3000 THEN 1 ELSE 0 END) as high_price
FROM product;

    方法一:
    我们通过CASE表达式获得满足条件的所有product_id, 然后使用COUNT()函数去统计总共有多少个product_id，使用AS 赋予别名
    在这里插入图片描述
    ⚠️ ELSE NULL，对于COUNT()函数，如果有NULL

    • COUNT(*) 包含NULL的数据行数
    • COUNT(<列名>) 不包含NULL的数据函数

    方法二
    第二个方法是用到了SUM()函数。一样，先通过CASE表达式获得满足条件的项？并赋予其值为1，不满足的为0。这个时候其实跟第一个方法意思一样的，但是第一个方法可能如果表里有重复商品的话就更好一点，第二个方法就没办法去除重复的部分。最后利用加法SUM()就可以计算出满足条件的商品数量
    在这里插入图片描述

    • 满足条件为1
    • 不满足条件为0
    • 求和
J_caicaicai
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
天池SQL训练营task03练习
shady_with_8miles的博客
05-04 295
/*3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。 条件 1：销售单价大于等于 1000 日元。 条件 2：登记日期是 2009 年 9 月 20 日。 条件 3：包含商品名称、销售单价和登记日期三列。*/ CREATE VIEW ViewPractice5_1(product_name, sale_price, regist_date) AS SELECT product_name, s.
学习笔记与练习 | SQL入门（三）| 复杂一点的查询
choconeverlate的博客
12-20 241
系列文章目录 提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加 例如：第一章 Python 机器学习入门之pandas的使用 提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录系列文章目录视图创建视图修改视图二、使用步骤1.引入库2.读入数据总结 视图 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。 视图不仅可以基
sql学习第3天复杂一些的查询
知道不_zkl的博客
11-21 1356
知识点 关联子查询 关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的，接下来我们就一起看一下关联子查询的具体内容吧。 关联子查询与子查询的联系 还记得我们之前的那个例子么查询出销售单价高于平均销售单价的商品，这个例子的SQL语句如下 SELECT product_id, product_name, sale_price FROM product WHERE sale_price > (SELECT AVG(sale_price) FROM product); 我们再来看
SQL训练营-task03
sinat_29139947的博客
07-19 540
3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。 条件 1：销售单价大于等于 1000 日元。 条件 2：登记日期是 2009 年 9 月 20 日。 条件 3：包含商品名称、销售单价和登记日期三列。 对该视图执行 SELECT 语句的结果如下所示。 SELECT * FROM ViewPractice5_1; 执行结果 product_name | sale_price |
第三章 复杂一点的查询
ZZYYhzhy的博客
11-21 321
3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。 • 条件 1：销售单价大于等于 1000 日元。 • 条件 2：登记日期是 2009 年 9 月 20 日。 • 条件 3：包含商品名称、销售单价和登记日期三列。 对该视图执行 SELECT 语句的结果如下所示。 SELECT * FROM ViewPractice5_1; create view ViewPractice5_1(prod.
sql-复杂查询语句
weixin_45720616的博客
07-12 2092
复杂的查询语句1 视图1.1 创建视图1.2 修改视图1.3 更新视图1.4 删除视图2 子查询2.1 关联子查询练习题 数据来源 1 视图 定义：视图与表的功能是差不多的，区别在于，视图是虚拟的，并不是真实存在的，他相当于一个窗口，通过视图，我们可以观察到真实的表，从而对其进行操作。 1.1 创建视图 创建视图的语法： CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句> 比如:创建一个名为productsum
SQL学习之“复杂一点的查询”
KDmanbu的博客
03-21 1407
本节将学习一些SQL查询语句的复杂用法， 在SQL中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表，但它实际不是真实的表。视图与表的区别在于“是否保存了实际的数据”，其数据来源均建立在真实表的基础上。 1、视图介绍 创建视图的语法如下： CREATE VIEW 视图名 AS SELECT 列名
用SQL语句进行数据库查询(复杂查询)
@初阶牛的博客
01-03 7170
数据查询的习题,以及答案
SQL查询语句之查询数据
热门推荐
weixin_45441862的博客
11-12 2万+
查询数据是指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。在MySQL 中是使用SELECT语句来查询数据的。 1、基本查询语句 查询数据是数据库操作中最常用的操作。通过对数据库的查询，用户可以从数据库中获取需要的数据。数据库中可能包含着无数的表，表中可能包含着无数的记录。因此，要获得所需的数据并非易事。MySQL中可以使用SELECT 语句来查询数据。根据查询的条件的不同，数据库系统
ch03_ 复杂一点的查询1
08-03
【SQL查询进阶：复杂查询与视图】 在掌握了SQL基本查询技巧之后，我们需要了解一些更高级的概念，以便处理更为复杂的数据操作。本章节主要探讨的是视图的使用，它是数据库管理中的一个重要工具。 **3.1 视图** ...
sql 复杂查询，一些经典案例
07-12
sql 复杂查询，一些经典查询，希望对朋友有帮助！一些经典案例
php使用pdo连接并查询sql数据库的方法
10-25
2. **执行查询**：使用PDO实例的`query()`方法执行SQL查询。例如： ```php $stmt = $dbh->query('SELECT * FROM test'); ``` 3. **处理结果**：通过循环遍历查询结果集，例如： ```php foreach ($stmt as $row) { ...
sqlserver和oracle中对datetime进行条件查询的一点区别小结
09-11
在SQL Server和Oracle数据库中，对DateTime类型进行条件查询时，两者存在一些差异，这主要体现在日期范围的处理以及日期格式上。对于一个系统需要同时支持这两种数据库的情况，需要特别注意这些不同点来编写兼容的...
解析关于SQL语句Count的一点细节
09-10
在SQL中，COUNT()函数是一个非常重要的聚合函数，用于计算指定列或表达式的行数。本文将深入探讨SQL语句中COUNT()的一些细节，帮助...在处理大数据集时，了解这些细节对于优化查询性能和编写高效的SQL语句至关重要。
【信号分解】基于蝗虫优化算法GOA-CEEMDAN实现信号去躁附matlab代码.rar
07-19
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
【信号分解】基于北方苍鹰优化算法NGO-CEEMDAN实现信号去躁附matlab代码.rar
07-19
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
基于鱼鹰优化算法OOA-VMD实现信号去噪目标函数为包络信息熵 包络熵 排列熵 样本熵最小附matlab代码.rar
07-19
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
全国大学生电子设计竞赛（电赛）相关资源（流程、模板、心得、历届资料等）
07-19
近些年，电赛规则、限制不断修改，该放开的差不多都放开了。那么现在的电赛四旋翼相关的赛题，有想法完赛（不打酱油）的话，感觉至少具备以下能力。｛2023年的G题劝退了多少组去玩E题？ ｝ 熟练的单片机编程能力。如果连KEIL、VOFA+都没听说过的话，那拉倒吧，你退赛吧。 基本的自动控制素养。如果连反馈、滤波、PID控制、串级PID控制等都没听说过的话，那拉倒吧，你很难完赛。。 对四旋翼飞行器的飞控、控制系统非常熟悉。如果是自研的飞控，那是绝佳的。当然用Pixhawk、无名、ANO匿名等开源飞控也没问题，现在组委会也不管了。如果比赛时现去调飞控，或者以为飞行器上去给个指令就能飞，那拉倒吧，你对电赛有错误的认知。 对各种传感器的应用和编程都要熟练掌握，包括但不限于激光测距、光流、超声波测距、Intel T265深度定位镜头、气压高度计、陀螺仪/加速度计（MPU6050、BMI088...）、磁力计（AK8975...）等。如果认为传感器反馈都是现成的，没必要去学的话，那拉倒吧，你不适合大学生电赛。 对板载计算机（树莓派、英伟达NX、英特尔NUC...）、UBUNTU操作系统、ROS等要非常
精英stm32f103开发板寄存器版本MP3播放器实验.zip.zip
最新发布
07-19
精英stm32f103开发板寄存器版本MP3播放器实验.zip.zip
仓储管理系统语句的实现复杂一点的
05-24
以下是一个仓储管理系统中的复杂 SQL 查询语句，用于查询某个时间范围内的入库商品数量和出库商品数量： ``` SELECT date_trunc('day', warehouse_transactions.timestamp) AS transaction_date, SUM(CASE WHEN warehouse_transactions.type = 'IN' THEN warehouse_transactions.quantity ELSE 0 END) AS total_in, SUM(CASE WHEN warehouse_transactions.type = 'OUT' THEN warehouse_transactions.quantity ELSE 0 END) AS total_out FROM warehouse_transactions WHERE warehouse_transactions.timestamp >= '2021-01-01' AND warehouse_transactions.timestamp < '2021-02-01' GROUP BY transaction_date ORDER BY transaction_date ASC; ``` 该语句使用了 PostgreSQL 的 date_trunc 函数将时间戳转换为日期，并使用了聚合函数和条件语句来计算入库和出库商品数量。同时，它还使用了 WHERE 子句来限制查询的时间范围，并使用了 GROUP BY 子句来按日期对结果进行分组。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值