SQL复杂查询

参考资料: 《SQL基础教程》

复杂查询

视图

视图和表

从SQL的角度来看,视图就是一张表,两者的区别在于是否保存了实际的数据。

  1. 创建表时,会通过INSERT语句将数据保存到数据库中,而数据库中的数据实际上会保存到计算机的存储设备。
  2. 使用视图时,并不会将数据保存到任何地方,实际上视图保存的是SELECT语句。从视图读取数据时,视图会在内部执行该SELECT语句并创建出一张临时表。

视图和表

总结成一句话:表中存储的是实际数据,视图本质上就是SELECT语句。

视图的优点

  1. 视图无需保存数据,节省存储设备容量;
  2. 可以将频繁使用的SELECT语句保存成视图,就不用每次都重新书写了。

创建和使用视图

创建视图的CREATE VIEW语句

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

注意:SELECT语句中列的排列顺序和视图中列的排列顺序相同,SELECT语句中的第1列就是视图中的第1列,以此类推。视图的列名在视图名称之后的列表中定义。

创建ProductSum视图

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

使用视图

SELECT
    product_type,
    cnt_product
FROM ProductSum;

执行结果:

FROM子句中使用视图查询的两个步骤

  1. 创建视图:执行定义视图的SELECT语句;
  2. 使用视图:根据得到的结果,再执行在FROM子句中使用视图的SELECT语句。

当然,我们还可以以视图为基础再创建视图,因此,使用视图的查询通常需要执行2条以上的SELECT语句。但是,多重视图会降低SQL的性能,因此希望大家使用单一视图。

视图的限制

定义视图时不能使用ORDER BY子句

为什么不能使用ORDER BY子句? 因为视图和表一样,数据行都是没有顺序的。

对视图进行更新

SELECT语句中,视图可以和表一样使用,那么,更新语句(INSERTDELETEUPDATE)呢? 标准SQL中规定:如果定义视图的SELECT语句能够满足某些条件,那么这个视图就可以被更新。 几个具有代表性的条件:

  1. SELECT子句中未使用DISTINCT
  2. FROM子句中只有一张表
  3. 未使用GROUP BY子句
  4. 未使用HAVING子句

删除视图

删除视图的DROP VIEW语句

DROP VIEW 视图名称

删除视图ProductSum

DROP VIEW ProductSum;

子查询

子查询和视图

子查询就是一次性的视图(SELECT语句),子查询在SELECT语句执行完毕之后就会消失。

子查询

-- 和上面使用ProductSum视图实现相同功能的子查询语句
SELECT
    product_type,
    cnt_product
FROM (
SELECT
    product_type,
    COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
) AS ProductSum;

执行结果:

子查询SELECT语句的执行顺序

由内到外:

  1. 首先执行FROM子句中的SELECT语句;
  2. 根据1的结果执行外层的SELECT语句。

注意:子查询的层数原则上没有限制,可以无限嵌套下去,但是,随着层数增加,SQL语句会变得越来越难读懂,性能也会越来越差。因此,尽量避免使用多层嵌套的子查询。

子查询的名称 原则上子查询必须设定名称,设定名称是需要使用AS关键字,有时也可以省略。

标量子查询

标量子查询就是返回单一值的子查询,必须而且只能返回表中某一行的某一列的值。

WHERE子句中使用标量子查询

如何查询出销售单价高于平均销售单价的商品? 我们可能会想到以下错误的SQL语句

-- 在WHERE子句中不能使用聚合函数
SELECT
    product_id,
    product_name,
    sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

虽然这样的SELECT语句看上去能够满足我们的要求,但是由于在WHERE子句中不能使用聚合函数,因此这样的SELECT语句是错误的。 这时,标量子查询就可以发挥它的功效了。

计算平均销售单价的标量子查询

SELECT AVG(sale_price)
FROM Product;

执行结果:

选出销售单价高于平均单价的商品

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

执行结果:

标量子查询的书写位置

通常任何可以使用单一值的位置都可以使用标量子查询,也就是说,能够使用常数或者列名的地方,无论是SELECT子句、GROUP BY子句、HAVING子句,还是ORDER BY子句,都可以使用。

注意:如果子查询返回了多行结果,那么它就不再是标量子查询,而仅仅只是一个普通的子查询,因此不能被用在需要单一输入值的地方。

关联子查询

通过关联子查询按照商品种类对平均销售单价进行比较

SELECT
    product_id,
    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);

执行结果:

这里起关键作用的就是在子查询中添加WHERE子句的条件,该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SELECT basics | SQL刷题

    本系列文章的主要目的在于记录本人的刷题过程,尽量在文章中展示本人所想到的答案,或者是别人的参考答案。

    数据医生
  • SELECT from nobel | SQLZOO刷题

    Change the query shown so that it displays Nobel prizes for 1950.

    数据医生
  • SELECT from world | SQL刷题

    Observe the result of running this SQL command to show the name, continent and p...

    数据医生
  • 第38次文章:数据库结尾

    本周介绍一下数据库中的最后一种语言,TCL事务控制语言。以及存储过程和函数,还有最后的流程过程结构。顺利结束数据库的基础内容。同时祝各位同学国庆快乐呀!

    鹏-程-万-里
  • Hacking with iOS: SwiftUI Edition 视图和修饰符项目——挑战

    这个技术项目旨在深入探究特定的SwiftUI主题,我希望您在这里学到了很多有关视图和修饰符的知识——为什么SwiftUI的视图使用结构体,为什么some Vie...

    韦弦zhy
  • 关系数据库之视图

    The secret of change is to focus all of your energy, not on fighting the old but...

    小闫同学啊
  • MVC模式

    MVC代表ModelViewController(模型、视图、控制器 )模式。这种模式应用于应用程序的分层开发。

    祝你万事顺利
  • iOS的MVC框架之控制层的构建(下)

    在我的iOS的MVC框架之控制层的构建(上)一文中介绍了一些控制层的构建方法,而这篇文章则继续对一些方法进行展开讨论。MVC被众多开发者所诟病的C层的膨胀,究其...

    欧阳大哥2013
  • 不看后悔 —— 视图详细介绍

    在MySQL中,视图可能是我们最常用的数据库对象之一了。那么你知道视图和表的区别吗?你知道创建及使用视图要注意哪些点吗?可能很多人对视图只是一知半解,想详细了解...

    MySQL技术
  • 快速学习Oracle-视图

    我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。

    cwl_java

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动