SQL - Lee的学习笔记4

SQL - 学习笔记4

14、多表查询

(1) 多表查询 - 标量子查询

        子查询：嵌套在其他查询中的查询
        标量子查询：只返回一行一列（即一个单元格数据）的子查询。相当于是一个常数。

(2) 多表查询 - 关联子查询

        嵌套在其他查询中的查询。适用于组内比较
        子查询返回一列数据：子查询的结果，与主查询的目标列存在一定关联。

(3) 多表查询 - 普通子查询

        ① 嵌套在其他查询中的查询。
        子查询返回一列数据：将子查询的结果列，作为主查询的取值范围。
        ② 嵌套在其他查询中的查询。
        子查询返回二维表：将二维表作为主查询新的检索表。

(4) 多表查询 - 表联结

        关系型数据库是将数据保存在不同的表中
        原则：一类数据一个表；表之间相互联系
        分解数据为多个表能更有效方便地处理数据，但是检索数据时，则必须使用 - 联结
        联结：一种机制，关联多个表，返回一组输出。在一条SELECT语句中实现。
        主键：唯一标识，可以是ID或任何其他唯一值。
        SELECT [列名]                                -> 指定检索列，可以出自不同表
        FROM 表1，表2                              -> 从一个表拓展为两个表或更多表
        WHERE [表1.列a] = [表2.列b]         -> 过滤条件外多了一个联结条件，逐行匹配
① 多表查询 - 内部联结（等值联结）
        INNER JOIN … ON …
        联结方式 -> 内部联结 ->
        联结前提：必须有共同列
        联结结果：所有表的所有列、共同列重复的行
        创建联结规则：列出所有表、定义所有表关系
② 多表查询 - 自联结（特殊内联结）
        联结方式 -> 自联结 ->
        联结前提：联结表为同一个
        联结结果：返回所有数据
        创建联结规则：列出所有表、定义所有表关系
③ 多表查询 - 外部联结（特殊内联结）
        联结方式 -> 自联结 ->
        联结前提：必须有共同列
        联结结果：所有表的所有列、包含相关表中没有关联行的行
        创建联结规则：列出所有表、定义所有表关系
        左外部联结 - 包含左边表的所有行 LEFT OUTER JOIN
        右外部联结 - 包含右边表的所有行 RIGHT OUTER JOIN
        全外部联结 - 包含两边表的所有行 FULL OUTER JOIN
④ 多表查询 - 表联结

(5) 组合查询 - UNION

        1）自动过滤重复行；两条及两条以上SELECT语句，每两条之间UNION一次；UNION ALL关键字（不删重复的记录）。
        2）相同的列、表达式或聚集函数；数据类型必须兼容（允许隐式转换）；最终字段名由第一条SELECT语句决定。
        3）ORDER BY放在最后一条SELECT语句中， 作用整个检索结果；字段名由SELICT展示结果决定。

相关练习：

Q1：总结一下子查询的几种类别，及分别的用法和特征（包括子查询返回值的样式、子查询在主查询语句中的位置）。
        （1）标量子查询：子查询返回一行一列的一个值，即一个单元格数据；子查询结果可作为主查询语句的常数列或用于WHERE子句的过滤条件和分组的筛选条件HAVING子句中。
        （2）关联子查询：子查询返回一列数据，子查询的结果与主查询的字段存在一定关联。用于WHERE子句的过滤条件和分组的筛选条件HAVING子句中；关联条件需要写在子查询部分。
        （3）普通子查询：子查询返回一列数据，将子查询的结果列作为主查询不连续的取值范围；或者子查询返回二维表，将二维表作为主查询的新的检索表。

Q2：具体说明内联结和外联结的异同，以及联结的具体写法。
        同：都必须有共同列，创建联结规则都需要列出所有表并定义所有表之间的关系。
        异：内部联结只保留存在相等关系的行，外部联结哪边外，则保留其所有行，另一边只保留存在向党关系的行。
        SELECT … FROM … INNER JOIN … ON …
        SELECT … FROM … LEFT/RIGHT/FULL OUTER JOIN … ON …

Q3：按要求写出以下语句。
1、找出所有洁云牌抽纸的销售流水（使用标量子查询）。
        SELECT o.*
        FROM order_list o
        WHERE o.prod_id = (SELECT prod_id FROM prod_info p WHERE p.prod_name = ‘抽纸’ AND p.brand = ‘洁柔’);

2、找出各类商品中，价格高于该类商品均价的部分（使用关联子查询）。
        SELECT p.*
        FROM prod_info p
        WHERE p.sale_price > (
                SELECT AVG(p2.sale_price)
                FROM prod_info p2
                WHERE p.class = p2.class
        GROUP BY p2.class
        )

3、用联结的方法改写第一题。
        SELECT o.*
        FROM prod_info p INNER JOIN order_list o
        ON p.prod_id = o.prod_id AND p.brand = ‘洁柔’ AND p.prod_name = ‘抽纸’;

4、归总下所有商品的销量。
        SELECT p.prod_id, SUM(o.quantity)
        FROM prod_info p LEFT OUTER JOIN order_list o
        ON p.prod_id = o.prod_id
        GROUP BY p.prod_id