Mysql-【p17-30】表连接和联合

1. 表连接【JOIN】

   1. 内部连接【INNER JOINS】

      1. 数据库内表间连接

         1. 连接的关键在于要有接口【内容相同的列】 如订单表和顾客表想要连接，就必须找到两个表共有的列：customer_id
         2. 语法为FROM 表1 JOIN 表2 ON 表1.列a= 表1.列b【如 FROM orders JOIN customers ON orders.customer_id = customers.customer_id 】
         3. 注意，如果连接时如果两个表有列重名，如customer_id，则SELECT customer_id会报错（列不明确ambiguous），必须加上表名，如orders.customer_id

      2. 跨数据库表间连接【Joining Across Databases】

         1. -- 表达一
            USE sql_store;SELECT *
            FROM order_items AS o
            JOIN sql_inventory.products AS p 
                ON o.product_id = p.product_id
            
            -- 表达二
            USE sql_inventory;SELECT *
            FROM sql_store.order_items AS o
            JOIN products AS p 
                ON o.product_id = p.product_id

            两种表达等效

         2. 只需注意把不在本操作数据库（即USE语句中的数据库）的表加上数据库前缀

      3. 多表连接【Joining Multiple Tables】

         1. 使用场景：实际可能不止2张表需要连接可能超过10张表需要连接

         2. 语法：FROM表1 JOIN表2ON条件1 JOIN表3ON条件2......

         3. 注意点在于最后呈现几列有意义的信息，否则多表连接展示全部信息会显得繁杂

         4. 注意FROM是最后呈现的数据表，在此表基础上进行连接

      4. 复合连接条件

         1. 使用场景：有的表没有能唯一识别记录的列，需要用两列的值一起唯一识别每一个数据记录【如果打开属性表（鼠标悬浮到所需表时，点击第二个图标）会发现黄色闪电符号有两个，表明可以使用多个列形成复合主键】
         2. 语法：FROM 表1 JOIN 表2 ON 条件1 AND 条件2

      5. 隐式连接语法

         1. # 显式连接用法
            SELECT * 
            FROM orders o
            JOIN customers c
            	ON o.customer_id = c.customer_id
            
            # 等效的隐式连接用法
            SELECT * 
            FROM orders o, customers c
            WHERE o.customer_id = c.customer_id
            

            并不推荐隐式连接用法

   2. 自连接【self joins】

      1. 是一种特殊的内部连接
      2. 初学不太好理解，关键在于理解使用情景：表内行与行之间有所关联【类比链表就是每一行记录就像是链表的节点，节点不仅仅有自身id及数据 还有指向的老板 的id，但是与链表的不同之处在于链表是线状的】
      3. 举例说明 如图为emploee表的数据。每一个数据记录【以employee_id唯一识别】会有指向它的BOSS的ID【即reports_to】，而该BOSS的ID又是某个数据的employee_id。因此可以通过自连接将一个数据记录的全部信息与其boss的全部信息连接起来
      4. 同一张表起不同的表名以起到不同作用【在本例中 emploee表既作为员工表e 又作为BOSS表m】
      5. 具体代码：

         SELECT
         	e.employee_id,
             e.first_name,
             m.first_name AS BOSS # 避免列名冲突
         FROM employees e # 作为员工信息表
         JOIN employees m # 作为BOSS信息表
         	ON e.reports_to = m.employee_id 
             # 员工表中的reports_to为BOSS表中的employee_id

         运行结果：

   3. 外部连接

      1. 使用场景

         1. 内连接能将订单表与顾客信息相连接【通过订单表的顾客id与顾客表的顾客id相等 相当于取了交集】，但是不能呈现在顾客表但是不在订单表中的记录【言下之意是 要在顾客表的基础上补充订单表提供的信息】因此引入外连接 

      2. 语法规则

         1. FROM 表1【左表】 LEFT/RIGHT JOIN表2【右表】 ON 条件
            【以下图为例 内部连接返回C的信息，LEFT JOIN返回A+C的信息，RIGHT JOIN返回C+B的信息】

      3. USING用法【内外连接都能用】

         1. ON o.customer_id = c.customer_id
            等价于 USING (customer_id)【因为FROM JOIN已经表明了要连接到两个表 所以表可以省略】【必须是两个表目标列的列名完全相同才能用】
         2. 对于复合连接条件也能用：FROM 表1 JOIN 表2 ON 表1.列1=表2.列1 AND 表1.列2=表2.列2【可以简化为FROM 表1 JOIN 表2 USING(列1,列2)】

      4. 其他

         1. 尽量使用左链接 这是一般习惯

         2. 多表外连接：在多表连接的基础上，每一个内连接都要考虑是否需要按照需要改成外连接

         3. 自外连接：在子链接的基础上，考虑是否需要按照需要改成外连接

   4. 自然连接【NATURAL JOINs】

      1. 【随缘连接 不推荐 仅简单了解】
      2. 例子：FROM orders o
         NATURAL JOIN customers c
      3. 不用ON进行条件限制【全靠数据库引擎基于名字的列连接】

   5. 交叉连接【笛卡尔积】

      1. 应用场景：如颜色表和型号表做排列组合
      2. 显式写法如下：【更推荐】
         FROM orders o
         CROSS JOIN customers c
      3. 隐式写法如下：
         FROM orders o, customers c
          

2. 表联合【UNION】

   1. 联合和连接的区别

      1. 连接是结合多张表的列 联合是结合多张表的行 【也可以是一张表，一张表连接就是自连接 一张表联合很可能是分组的应用】

   2. 联合的错误方式和正确方式

      1. 如果只是简单把两份代码拼起来，第一段分号保留，会得到两张表result31 32【不能起到联合的效果】

      2. 如果是用联合的方式：加UNION之前要把第一段的分号去掉 表示第一段语句并未结束，得到一张表result33

   3. 其他

      1. 联合时select的列的数量要相同 否则报错

      2. 通过练习可知（如图），联合可以对一张表根据某一列的数值进行分组【原来的表数据没有type列】

3. 其他

   1. 关于SELECT和WHERE的区别：

      1. SELECT是筛选最后呈现出来的列，要么选部分列要么选全部列要么添加列，比较死板，WHERE是筛选符合条件的行，其中能用许多关键字如IN BETWEEN等进行灵活筛选

   2. 允许给表或者是列取别名以简化代码

      1. 如 FROM orders AS o JOIN customers AS c【给orders表取别名为 o 给customers表取别名为c 两处的AS都可以省略】
      2. 注意 给了别名 代码的所有地方都要改成别名（包括取别名代码的前面） 不能用原名

   3. 连接后漏行的可能原因

      1. 很可能是因为表间的列匹配错误，必须清晰每一匹配列的作用【如本次实验中犯了把订单编号和支付方式编号相匹配的错误】