SQL 数据库

复杂SQL解析

针对于复杂SQL,我们如何去写

Posted by 憧憬 on March 20, 2019

SQL复杂查询

这边主要说到几种SQL的复杂查询

统计

我们对一些网站都会做一些统计数据,针对这些数据我们需要些一些统计sql 我们在对应代码中去请求数据库

这里可以使用SQL里面的视图 来方便查询

视图究竟是什么呢？如果用一句话概述的话，就是“从 SQL 的角度 来看视图就是一张表”。

那么视图和表到底有什么不同呢？区别只有一个，那就是“是否保存 了实际的数据

通常，我们在创建表时，会通过 INSERT 语句将数据保存到数据库 之中，而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。因此，我们通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出 一张临时表

表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。

视图无需保存数据,因此可以节省存储设备的容量, 还有一个优点就是可以将频繁使用的sql保存,利于复用

这里就不对视图的基本操作赘述 但是我们使用视图还是需要注意几点

  1. 视图是可以派生的 我们称为多重视图

    但是我们不推荐使用 这样不利于理解 并且多重视图会降低SQL性能 应该避免在视图的基础上创建视图。

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

    1
2

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

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

  3. 尽量不要对视图进行更新

    尽管他和表很像,也不是不可以对它进行更新 当它满足特定条件可以对视图进行更新 但是我们没有必要去操作视图, 一般我们直接对数据表进行数据修改或添加即可

SQL子查询

  1. 子查询对于SQL的执行顺序
1
2
3
4
5

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

子查询作为内层查询会首先执行。

例如这个查询  先执行FROM后面的()    可以想象成先查询一张表的数据出来  再对查询出来的表进行查询

由于子查询的层数没有上限,我们可以无限子查询,这让我想起面试题 写一个你见过最长的SQL 抛开性能的话 我们可以写很长

  1. 标量子查询

    Q: 什么是标量? 标量就是单一的意思，在数据库之外的领域也经常

    而标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值

说一个例子就明白了

比较常见一个问题: 查询出销售单价高于平均销售单价的商品

1
2
3
4
5
6
7
8
9
10
11

SELECT product_id, product＿name, sale_price  FROM Product WHERE sale_price > AVG(sale_price);

可是这样是不被允许的  WHERE子句后面是不可以带聚合函数的 那我们怎么去编写一个符合条件的SQL呢
这个时候我们用到标量子查询

1. SELECT AVG(sale_price)  FROM Product;  这是一条查商品平均值的SQL
SELECT product_id, product_name, sale_price  FROM Product  WHERE sale_price > (SELECT AVG(sale_price)  FROM Product);
然后这样就可以了

还有一个就是
标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。

使用标量子查询时的注意事项:

那就是该子查询绝对不能返回多行结果。也就是说，如果子查询返回了多行结果，那么它就 不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或 者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中

1
2
3
4
5
6
7
8
9

SELECT product_id, product_name, sale_price,(SELECT AVG(sale_price) FROM Product GROUP BY product_type) AS avg_price  FROM Product;
例如这样
 avg
----------------------
  2500.0000000000000000
  300.0000000000000000
  2795.0000000000000000
  
  这个子查询会返回多条数据 那碰到这种怎么办呢?  我们可以使用关联子查询

关联子查询

普通的子查询和关联子查询的区别

按此前所学，使用子查询就能选取出销售单价（sale_price）高于全部商品平均销售单价的商品。这次我们稍稍改变一下条件，选取出各商品种类中高于该商品种类的平均销售单价的商品。

  • 按照商品种类与平均销售单价进行比较

按照商品种类计算平均价格

1

SELECT AVG(sale_price)  FROM Product GROUP BY product_type;

如果我们使用前面（标量子查询）的方法，直接把上述SELECT 语句使用到 WHERE 子句当中的话，就会发生错误。

– 发生错误的子查询

1
2
3

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

出错原因上面已经说明过了

– 我们看一下修改后的SQL

1

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

这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件

该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。

这次由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。因此，使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这样的语言，例如本次示例就是限定“商品种类”对平均单价进行比较。

在细分的组内进行比较时，需要使用关联子查询 写关联子查询时 结合条件一定要写在子查询中

CATALOG
    FEATURED TAGS
    网络 linux php nginx 数据库 App react-native redis
    FRIENDS