SQL基础之复杂查询

DB浅入浅出

发布日期: 2019-02-06

更新日期: 2020-12-18

文章字数: 2.2k

阅读时长: 9 分

阅读次数:

本文主要是阅读《SQL 基础教程》这本书总结而来，有需求的话可以阅读原著。

SQL基础之复杂查询

1. 视图

从SQL的角度来看，视图和表是相同的
视图和表的实质区别在于是否保存的是实际数据，视图中保存的是select语句

1.1 视图和表

表中存放的是实际数据
视图中存放的是从表中取出数据所使用的select语句
视图的优点
- 由于视图无需保存数据，因此可以节省设备的容量
- 将频繁使用的select语句保存成为视图，方便使用

1.2 创建视图的方法

语法格式

create view 视图名称 ( <视图列名1>, <视图列名2>, ... )
    as
    <>
    <select语句>;

实例演示

mysql> select shohin_bunrui, count(*) from Shohin group by shohin_bunrui;
+---------------+----------+
| shohin_bunrui | count(*) |
+---------------+----------+
| 衣服        |        2 |
| 办公用品  |        2 |
| 厨房用具  |        4 |
+---------------+----------+
3 rows in set (0.01 sec)

# 创建视图
mysql> create view ShohinSum (shohin_bunrui, cnt_shohin)
    -> as
    -> select shohin_bunrui, count(*)
    -> from Shohin
    -> group by shohin_bunrui;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ShohinSum;
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 衣服        |          2 |
| 办公用品  |          2 |
| 厨房用具  |          4 |
+---------------+------------+
3 rows in set (0.00 sec)

使用视图的查询
- 首先，执行定义视图的select语句
- 根据得到的结果，在执行在from子句中使用视图的select语句

mysql> select shohin_bunrui, cnt_shohin
    -> from ShohinSum
    -> where shohin_bunrui = '办公用品';
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 办公用品  |          2 |
+---------------+------------+
1 row in set (0.01 sec)

# 对视图创建视图
# 多重视图可以降低SQL的性能
mysql> create view ShohinSumJim (shohin_bunrui, cnt_shohin)
    -> as
    -> select shohin_bunrui, cnt_shohin
    -> from ShohinSum
    -> where shohin_bunrui = '办公用品';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ShohinSumJim;
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 办公用品  |          2 |
+---------------+------------+
1 row in set (0.01 sec)

1.3 视图的限制

限制 1：定义视图时不能使用 order by 子句

这是因为视图和表一样，数据行都是没有顺序的
其实有些DBMS是可以使用order by子句的，但是并非通用

限制 2：对视图进行更新

在标准的SQL中是这样规定：如果定义视图的select语句能够满足某些条件，那么这个视图就可以被更新，以下是代表性条件
- select子句中未使用distinct
- from子句中只有一张表
- 未使用group by子句
- 未使用having子句
实例演示

# 在我们向ShohinSum插入数据的时候，会发现报错了？
# 这是因为视图ShohinSum是通过group by子句对原表进行聚合得到的
# 这也是保证数据的一致性的一个例子
mysql> insert into ShohinSum values ('电器制品', 5);
ERROR 1471 (HY000): The target table ShohinSum of the INSERT is not insertable-into

能够更新视图的情况

# 不是通过聚合得到的视图就可以使用更新
mysql> create view ShohinJim (shohin_id, shohin_mei, shohin_bunrui,
    -> hanbai_tanka, shiire_tanka, torokubi)
    -> as
    -> select *
    -> from Shohin
    -> where shohin_bunrui = '办公用品';
Query OK, 0 rows affected (0.00 sec)

# ShohinJim视图内容
mysql> select * from ShohinJim;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0002      | 打孔器  | 办公用品  |          500 |          320 | 2009-09-11 |
| 0008      | 圆珠笔  | 办公用品  |          100 |         NULL | 2009-11-11 |
+-----------+------------+---------------+--------------+--------------+------------+
2 rows in set (0.00 sec)

# 插入数据
mysql> insert into ShohinJim values ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
Query OK, 1 row affected (0.00 sec)

# 查看视图
mysql> select * from ShohinJim;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0002      | 打孔器  | 办公用品  |          500 |          320 | 2009-09-11 |
| 0008      | 圆珠笔  | 办公用品  |          100 |         NULL | 2009-11-11 |
| 0009      | 印章     | 办公用品  |           95 |           10 | 2009-11-30 |
+-----------+------------+---------------+--------------+--------------+------------+
3 rows in set (0.00 sec)

# 查看原表，发现也增加一行，保证数据的一致性
mysql> select * from Shohin;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T恤衫    | 衣服        |         1000 |          500 | 2009-09-20 |
| 0002      | 打孔器  | 办公用品  |          500 |          320 | 2009-09-11 |
| 0003      | 运动T恤 | 衣服        |         4000 |         2800 | NULL       |
| 0004      | 菜刀     | 厨房用具  |         3000 |         2800 | 2009-09-20 |
| 0005      | 高压锅  | 厨房用具  |         6800 |         5000 | 2009-09-15 |
| 0006      | 叉子     | 厨房用具  |          500 |         NULL | 2009-09-20 |
| 0007      | 切菜板  | 厨房用具  |          880 |          790 | 2008-09-20 |
| 0008      | 圆珠笔  | 办公用品  |          100 |         NULL | 2009-11-11 |
| 0009      | 印章     | 办公用品  |           95 |           10 | 2009-11-30 |
+-----------+------------+---------------+--------------+--------------+------------+
9 rows in set (0.00 sec)

1.4 删除视图

语法格式

drop view 视图名称 (<视图列名1>, <视图列名2>, ... );

实例演示

mysql> drop view ShohinSum;
Query OK, 0 rows affected (0.00 sec)

2. 子查询

子查询就是一次性的视图(select语句)，执行完成之后就会消失
由于子查询需要命名，因此需要根据处理内容来指定恰当的名称
标量子查询就是只能返回一行一列的子查询

2.1 子查询和视图

子查询就是一张一次性视图
子查询就是将用来定义视图的select语句直接用于from子句当中

# 普通的视图创建方式
mysql> create view ShohinSum (shohin_bunrui, cnt_shohin)
    -> as
    -> select shohin_bunrui, count(*)
    -> from Shohin
    -> group by shohin_bunrui;
Query OK, 0 rows affected (0.00 sec)

mysql> select shohin_bunrui, cnt_shohin from ShohinSum;
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 衣服        |          2 |
| 办公用品  |          3 |
| 厨房用具  |          4 |
+---------------+------------+
3 rows in set (0.00 sec)

# 子查询就是括号内的内容
# 子查询就是将用来定义视图的select语句直接用于from子句当中
mysql> select shohin_bunrui, cnt_shohin
    -> from ( select shohin_bunrui, count(*) as cnt_shohin
    -> from Shohin
    -> group by shohin_bunrui)
    -> as ShohinSum;
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 衣服        |          2 |
| 办公用品  |          3 |
| 厨房用具  |          4 |
+---------------+------------+
3 rows in set (0.00 sec)

# 增加子查询的层数
# 尽量避免使用多重子查询
mysql> select shohin_bunrui, cnt_shohin
    -> from ( select *
    -> from (select shohin_bunrui, count(*) as cnt_shohin
    -> from Shohin
    -> group by shohin_bunrui) as ShohinSum
    -> where cnt_shohin = 4) as ShohinSum2;
+---------------+------------+
| shohin_bunrui | cnt_shohin |
+---------------+------------+
| 厨房用具  |          4 |
+---------------+------------+
1 row in set (0.00 sec)

2.2 标量子查询

注意事项
- 标量子查询则是有一个特殊的限制，那就是必须而且只能返回1行1列的结果
- 由于返回的是单一的值，所以可以使用<>或者=这样需要但一直的比较运算符中使用
在 where 子句中使用标量子查询

# 如果我们想得到大于平均售价的商品呢？
# 使用下列的语句，将会出错的
mysql> select shohin_id, shohin_mei, hanbai_tanka
    -> from Shohin
    -> where hanbai_tanka > avg(hanbai_tanka);
ERROR 1111 (HY000): Invalid use of group function

# 正确的avg语句
mysql> select avg(hanbai_tanka)
    -> from Shohin;
+-------------------+
| avg(hanbai_tanka) |
+-------------------+
|         1875.0000 |
+-------------------+
1 row in set (0.00 sec)

# 标量子查询
mysql> select shohin_id, shohin_mei, hanbai_tanka
    -> from Shohin
    -> where hanbai_tanka > (
    -> select avg(hanbai_tanka) from Shohin);
+-----------+------------+--------------+
| shohin_id | shohin_mei | hanbai_tanka |
+-----------+------------+--------------+
| 0003      | 运动T恤 |         4000 |
| 0004      | 菜刀     |         3000 |
| 0005      | 高压锅  |         6800 |
+-----------+------------+--------------+
3 rows in set (0.01 sec)

标量子查询的书写位置
- 标量子查询的书写位置并不仅仅局限于where子句中，通常任何可以使用单一值的位置都可以使用
- 能够使用常数或者列名的地方，无论是select、group by、order by等

# 在select子句中使用标量子查询
mysql> select shohin_id, shohin_mei, hanbai_tanka,
    -> (select avg(hanbai_tanka) from Shohin) as avg_tanka
    -> from Shohin;
+-----------+------------+--------------+-----------+
| shohin_id | shohin_mei | hanbai_tanka | avg_tanka |
+-----------+------------+--------------+-----------+
| 0001      | T恤衫    |         1000 | 1875.0000 |
| 0002      | 打孔器  |          500 | 1875.0000 |
| 0003      | 运动T恤 |         4000 | 1875.0000 |
| 0004      | 菜刀     |         3000 | 1875.0000 |
| 0005      | 高压锅  |         6800 | 1875.0000 |
| 0006      | 叉子     |          500 | 1875.0000 |
| 0007      | 切菜板  |          880 | 1875.0000 |
| 0008      | 圆珠笔  |          100 | 1875.0000 |
| 0009      | 印章     |           95 | 1875.0000 |
+-----------+------------+--------------+-----------+
9 rows in set (0.00 sec)

# 在having子句中使用标量子查询
mysql> select shohin_bunrui, avg(hanbai_tanka)
    -> from Shohin    -> group by shohin_bunrui    -> having avg(hanbai_tanka) > (select avg(hanbai_tanka) from Shohin);
+---------------+-------------------+
| shohin_bunrui | avg(hanbai_tanka) |
+---------------+-------------------+
| 衣服        |         2500.0000 |
| 厨房用具  |         2795.0000 |
+---------------+-------------------+
2 rows in set (0.00 sec)

标量子查询绝对不能返回多行结果
- 如果子查询返回多行的话，那么它就不是标量子查询，而仅仅是一个普通的子查询了
- 因此不能使用在=或者<>等需要单一值得运算符当中，也能在select当中

# 实例报错
mysql> select shohin_id, shohin_mei, hanbai_tanka,
    -> (select avg(hanbai_tanka) from Shohin
    -> group by shohin_bunrui) as avg_tanka
    -> from Shohin;
ERROR 1242 (21000): Subquery returns more than 1 row

3. 关联子查询

关联子查询会在细分的组内进行比较时使用
关联子查询和group by子句一样，也可以对表中的数据进行切分
关联子查询的结合条件如果未出现在子查询之中就会发生错误

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

查找商品分类中高于其平均价的商品
结合条件一定要写在子查询中

# 普通子查询无法做到
# 我们知道该子查询会返回3行结果，所以并不是标准子查询，所以在where子句中无法使用
mysql> select shohin_id, shohin_mei, hanbai_tanka
    -> from Shohin
    -> where hanbai_tanka > (select avg(hanbai_tanka) from Shohin
    -> group by shohin_bunrui);
ERROR 1242 (21000): Subquery returns more than 1 row

# 关联子查询的功能就可以解决上述问题

# 以shohin_bunrui作为分类计算平均值
mysql> select avg(hanbai_tanka) from Shohin
    -> group by shohin_bunrui;
+-------------------+
| avg(hanbai_tanka) |
+-------------------+
|         2500.0000 |
|          231.6667 |
|         2795.0000 |
+-------------------+
3 rows in set (0.00 sec)

# where S1.shohin_bunrui = S2.shohin_bunrui
# 用来在select遍历的时候，使分类相同的进行比较，发现avg会返回一个值
mysql> select shohin_id, shohin_mei, hanbai_tanka
    -> from Shohin as S1
    -> where hanbai_tanka > (select avg(hanbai_tanka)
    -> from Shohin as S2
    -> where S1.shohin_bunrui = S2.shohin_bunrui
    -> group by shohin_bunrui);
+-----------+------------+--------------+
| shohin_id | shohin_mei | hanbai_tanka |
+-----------+------------+--------------+
| 0002      | 打孔器  |          500 |
| 0003      | 运动T恤 |         4000 |
| 0004      | 菜刀     |         3000 |
| 0005      | 高压锅  |         6800 |
+-----------+------------+--------------+
4 rows in set (0.00 sec)