CodeWithMosh-SQL进阶课程-第二章（多表检索数据）
2.1、内连接 | Inner Join
内连接（Inner Join）是数据库查询中最核心、最常用的表连接操作之一，它的作用是从多个表中精确匹配相关联的数据，相当于数学集合中的“交集”。
核心定义：
内连接会返回两个表中连接键完全匹配的所有行。只有当左表的某行数据，在右表中存在至少一个连接键相等的对应行时，这两行数据才会被组合并包含在最终结果中。不匹配的行将被完全排除。
-- 使用 INNER JOIN ，将两张表的数据进行合并查询想要的数据
mysql> SELECT order_id,customers.customer_id,first_name,last_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
+----------+-------------+------------+------------+
| order_id | customer_id | first_name | last_name |
+----------+-------------+------------+------------+
| 4 | 2 | Ines | Brushfield |
| 7 | 2 | Ines | Brushfield |
| 5 | 5 | Clemmie | Betchley |
| 8 | 5 | Clemmie | Betchley |
| 1 | 6 | Elka | Twiddell |
| 10 | 6 | Elka | Twiddell |
| 2 | 7 | Ilene | Dowson |
| 3 | 8 | Thacher | Naseby |
| 6 | 10 | Levy | Mynett |
| 9 | 10 | Levy | Mynett |
+----------+-------------+------------+------------+
可以通过给表设置别名，来降低SQL复杂度
内连接 | INNER JOIN 执行过程：
1.遍历 orders 表每一行数据
2.对于每行数据，取出 customer_id ，去 customer 表中查找相当的 customer_id
3.如果找到，就将 orders 表需要显示的列，和 customer 表需要显示的列合并成新的一行数据，放入结果集中
4.如果存在某个 orders 表中的 customer_id 列在 customer 表中不存在，则该行信息不会出现在结果中
练习题： 通过对照 order_items 表，把这张表和 products 表连接，返回产品 id 和名字，连同 order_items 表的数量和单价
mysql> SELECT b.product_id,b.name,a.quantity,a.unit_price FROM order_items AS a INNER JOIN products AS b ON a.product_id = b.product_id;
+------------+------------------------------+----------+------------+
| product_id | name | quantity | unit_price |
+------------+------------------------------+----------+------------+
| 4 | Brocolinni - Gaylan, Chinese | 4 | 3.74 |
| 1 | Foam Dinner Plate | 2 | 9.10 |
| 4 | Brocolinni - Gaylan, Chinese | 4 | 1.66 |
| 6 | Petit Baguette | 2 | 2.94 |
| 3 | Lettuce - Romaine, Heart | 10 | 9.12 |
| 3 | Lettuce - Romaine, Heart | 7 | 6.99 |
| 10 | Broom - Push | 7 | 6.40 |
| 2 | Pork - Bacon,back Peameal | 3 | 9.89 |
| 1 | Foam Dinner Plate | 4 | 8.65 |
| 2 | Pork - Bacon,back Peameal | 4 | 3.28 |
| 3 | Lettuce - Romaine, Heart | 4 | 7.46 |
| 5 | Sauce - Ranch Dressing | 1 | 3.45 |
| 3 | Lettuce - Romaine, Heart | 7 | 9.17 |
| 5 | Sauce - Ranch Dressing | 2 | 6.94 |
| 8 | Island Oasis - Raspberry | 2 | 8.59 |
| 6 | Petit Baguette | 5 | 7.28 |
| 1 | Foam Dinner Plate | 10 | 6.01 |
| 9 | Longan | 9 | 4.28 |
+------------+------------------------------+----------+------------+
2.2、跨数据库连接 | Joining
-- 通过对表添加库名，实现跨库查询
mysql> SELECT * FROM order_items a INNER JOIN sql_inventory.products b ON a.product_id = b.product_id;
+----------+------------+----------+------------+------------+------------------------------+-------------------+------------+
| order_id | product_id | quantity | unit_price | product_id | name | quantity_in_stock | unit_price |
+----------+------------+----------+------------+------------+------------------------------+-------------------+------------+
| 1 | 4 | 4 | 3.74 | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 2 | 1 | 2 | 9.10 | 1 | Foam Dinner Plate | 70 | 1.21 |
| 2 | 4 | 4 | 1.66 | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 2 | 6 | 2 | 2.94 | 6 | Petit Baguette | 14 | 2.39 |
| 3 | 3 | 10 | 9.12 | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 4 | 3 | 7 | 6.99 | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 4 | 10 | 7 | 6.40 | 10 | Broom - Push | 6 | 1.09 |
| 5 | 2 | 3 | 9.89 | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 6 | 1 | 4 | 8.65 | 1 | Foam Dinner Plate | 70 | 1.21 |
| 6 | 2 | 4 | 3.28 | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 6 | 3 | 4 | 7.46 | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 6 | 5 | 1 | 3.45 | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 7 | 3 | 7 | 9.17 | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 8 | 5 | 2 | 6.94 | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 8 | 8 | 2 | 8.59 | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 9 | 6 | 5 | 7.28 | 6 | Petit Baguette | 14 | 2.39 |
| 10 | 1 | 10 | 6.01 | 1 | Foam Dinner Plate | 70 | 1.21 |
| 10 | 9 | 9 | 4.28 | 9 | Longan | 67 | 2.26 |
+----------+------------+----------+------------+------------+------------------------------+-------------------+------------+
2.3、自连接 | Self Join
自连接（Self Join）是一种巧妙且强大的数据库表连接技术，它让一张表与自身进行连接，如同“灵魂出窍”般与自己对话，从而挖掘出数据内部隐藏的层级关系与内在关联
核心定义：
自连接并非一种独立的连接类型，而是将同一张数据表视为两个（或多个）逻辑上独立的“虚拟表”，然后通过别名（Alias）进行区分，并按特定条件进行连接（通常是内连接，但也可能是左连接等）的操作。它本质上是普通连接的一种特殊应用场景
-- 通过自连接，将同一张表数据表示为两个"虚拟表"，根据特定条件进行连接过滤
mysql> USE sql_hr;
Database changed
mysql> SELECT * FROM employees AS a INNER JOIN employees AS b ON a.reports_to = b.employee_id;
+-------------+------------+-----------+-----------------------------+--------+------------+-----------+-------------+------------+------------+---------------------+--------+------------+-----------+
| employee_id | first_name | last_name | job_title | salary | reports_to | office_id | employee_id | first_name | last_name | job_title | salary | reports_to | office_id |
+-------------+------------+-----------+-----------------------------+--------+------------+-----------+-------------+------------+------------+---------------------+--------+------------+-----------+
| 33391 | D'arcy | Nortunen | Account Executive | 62871 | 37270 | 1 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 37851 | Sayer | Matterson | Statistician III | 98926 | 37270 | 1 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 40448 | Mindy | Crissil | Staff Scientist | 94860 | 37270 | 1 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 56274 | Keriann | Alloisi | VP Marketing | 110150 | 37270 | 1 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 63196 | Alaster | Scutchin | Assistant Professor | 32179 | 37270 | 2 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 67009 | North | de Clerc | VP Product Management | 114257 | 37270 | 2 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 67370 | Elladine | Rising | Social Worker | 96767 | 37270 | 2 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 68249 | Nisse | Voysey | Financial Advisor | 52832 | 37270 | 2 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 72540 | Guthrey | Iacopetti | Office Assistant I | 117690 | 37270 | 3 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 72913 | Kass | Hefferan | Computer Systems Analyst IV | 96401 | 37270 | 3 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 75900 | Virge | Goodrum | Information Systems Manager | 54578 | 37270 | 3 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 76196 | Mirilla | Janowski | Cost Accountant | 119241 | 37270 | 3 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 80529 | Lynde | Aronson | Junior Executive | 77182 | 37270 | 4 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 80679 | Mildrid | Sokale | Geologist II | 67987 | 37270 | 4 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 84791 | Hazel | Tarbert | General Manager | 93760 | 37270 | 4 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 95213 | Cole | Kesterton | Pharmacist | 86119 | 37270 | 4 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 96513 | Theresa | Binney | Food Chemist | 47354 | 37270 | 5 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 98374 | Estrellita | Daleman | Staff Accountant IV | 70187 | 37270 | 5 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
| 115357 | Ivy | Fearey | Structural Engineer | 92710 | 37270 | 5 | 37270 | Yovonnda | Magrannell | Executive Secretary | 63996 | NULL | 10 |
+-------------+------------+-----------+-----------------------------+--------+------------+-----------+-------------+------------+------------+---------------------+--------+------------+-----------+
2.4、多表连接 | Joining Multiple Tables
mysql> USE sql_store;
Database changed
mysql> SELECT * FROM orders AS a INNER JOIN customers AS b ON a.customer_id = b.customer_id INNER JOIN order_statuses AS c ON a.status = c.order_status_id;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+-----------------+-----------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | order_status_id | name |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+-----------------+-----------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | 1 | Processed |
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | 2 | Shipped |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | 1 | Processed |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | 1 | Processed |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | 2 | Shipped |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | 1 | Processed |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | 2 | Shipped |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | 1 | Processed |
| 9 | 10 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | 2 | Shipped |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | 2 | Shipped |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+-----------------+-----------+
mysql> SELECT a.order_id,a.order_date,b.first_name,b.last_name,c.name FROM orders AS a INNER JOIN customers AS b ON a.customer_id = b.customer_id INNER JOIN order_statuses AS c ON a.status = c.order_status_id;
+----------+------------+------------+------------+-----------+
| order_id | order_date | first_name | last_name | name |
+----------+------------+------------+------------+-----------+
| 1 | 2019-01-30 | Elka | Twiddell | Processed |
| 2 | 2018-08-02 | Ilene | Dowson | Shipped |
| 3 | 2017-12-01 | Thacher | Naseby | Processed |
| 4 | 2017-01-22 | Ines | Brushfield | Processed |
| 5 | 2017-08-25 | Clemmie | Betchley | Shipped |
| 6 | 2018-11-18 | Levy | Mynett | Processed |
| 7 | 2018-09-22 | Ines | Brushfield | Shipped |
| 8 | 2018-06-08 | Clemmie | Betchley | Processed |
| 9 | 2017-07-05 | Levy | Mynett | Shipped |
| 10 | 2018-04-22 | Elka | Twiddell | Shipped |
+----------+------------+------------+------------+-----------+
练习题： 在 sql_invoicing 库下使用 payments 表和 payment_methods 表以及 clients 表进行连接，显示付款和更多详细信息，比如客户姓名、付款方式
mysql> SELECT c.name,c.phone,c.address,c.city,a.date,b.name,a.amount FROM payments AS a INNER JOIN payment_methods AS b ON a.payment_method = b.payment_method_id INNER JOIN clients AS c ON a.client_id = c.client_id;
+-------------+--------------+---------------------+---------------+------------+-------------+--------+
| name | phone | address | city | date | name | amount |
+-------------+--------------+---------------------+---------------+------------+-------------+--------+
| Topiclounge | 971-888-9129 | 0863 Farmco Road | Portland | 2019-02-12 | Credit Card | 8.18 |
| Vinte | 315-252-7305 | 3 Nevada Parkway | Syracuse | 2019-01-03 | Credit Card | 74.55 |
| Yadel | 415-144-6037 | 096 Pawling Parkway | San Francisco | 2019-01-11 | Credit Card | 0.03 |
| Topiclounge | 971-888-9129 | 0863 Farmco Road | Portland | 2019-01-26 | Credit Card | 87.44 |
| Yadel | 415-144-6037 | 096 Pawling Parkway | San Francisco | 2019-01-15 | Credit Card | 80.31 |
| Yadel | 415-144-6037 | 096 Pawling Parkway | San Francisco | 2019-01-15 | Credit Card | 68.10 |
| Topiclounge | 971-888-9129 | 0863 Farmco Road | Portland | 2019-01-08 | Credit Card | 32.77 |
| Topiclounge | 971-888-9129 | 0863 Farmco Road | Portland | 2019-01-08 | Cash | 10.00 |
+-------------+--------------+---------------------+---------------+------------+-------------+--------+
2.5、复合连接条件 | Compound Join Conditions
-- 通过多个字段条件来做复合连接，通过多个列的联合来做到唯一识别每一个订单项目
mysql> USE sql_store;
Database changed
mysql> SELECT * FROM order_items;
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
| 1 | 4 | 4 | 3.74 |
| 2 | 1 | 2 | 9.10 |
| 2 | 4 | 4 | 1.66 |
| 2 | 6 | 2 | 2.94 |
| 3 | 3 | 10 | 9.12 |
| 4 | 3 | 7 | 6.99 |
| 4 | 10 | 7 | 6.40 |
| 5 | 2 | 3 | 9.89 |
| 6 | 1 | 4 | 8.65 |
| 6 | 2 | 4 | 3.28 |
| 6 | 3 | 4 | 7.46 |
| 6 | 5 | 1 | 3.45 |
| 7 | 3 | 7 | 9.17 |
| 8 | 5 | 2 | 6.94 |
| 8 | 8 | 2 | 8.59 |
| 9 | 6 | 5 | 7.28 |
| 10 | 1 | 10 | 6.01 |
| 10 | 9 | 9 | 4.28 |
+----------+------------+----------+------------+
mysql> SELECT * FROM order_item_notes;
+---------+----------+------------+-------------+
| note_id | order_Id | product_id | note |
+---------+----------+------------+-------------+
| 1 | 1 | 2 | first note |
| 2 | 1 | 2 | second note |
+---------+----------+------------+-------------+
mysql> SELECT * FROM order_items AS a INNER JOIN order_item_notes AS b ON a.order_id = b.order_Id AND a.product_id = b.product_id;
Empty set (0.00 sec)
2.6、隐式连接语法 | Implicit Join Syntax
-- 使用隐式连接语法，简写 SQL ， 不建议使用
mysql> SELECT * FROM orders AS a INNER JOIN customers AS b ON a.customer_id = b.customer_id;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id | customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 9 | 10 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
mysql> SELECT * FROM orders AS a, customers AS b WHERE a.customer_id = b.customer_id;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id | customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 9 | 10 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
2.7、外连接 | Outer Join
外连接（Outer Join）是数据库查询中至关重要的连接操作，其核心使命是在连接表时，确保至少一张表的全部记录得以保留——无论它们在另一张表中是否有匹配项。它是数据分析中“保全数据完整性”的关键工具
核心定义：
外连接会返回一张表（或两张表）的所有行，并根据条件尝试与另一张表进行匹配。如果匹配成功，则合并行；如果匹配失败，则来自另一表的列将用 NULL 值填充。它对应数学集合论中的 “并集” 操作思想
两种外连接详解：
- 左外连接（LEFT JOIN）
它返回 左表（LEFT JOIN 左侧的表）的全部记录，以及右表中匹配的记录。如果右表无匹配，则右表所有列返回 NULL
mysql> SELECT a.customer_id,a.first_name,b.order_id FROM customers AS a LEFT JOIN orders AS b ON a.customer_id = b.customer_id;
+-------------+------------+----------+
| customer_id | first_name | order_id |
+-------------+------------+----------+
| 1 | Babara | NULL |
| 2 | Ines | 4 |
| 2 | Ines | 7 |
| 3 | Freddi | NULL |
| 4 | Ambur | NULL |
| 5 | Clemmie | 5 |
| 5 | Clemmie | 8 |
| 6 | Elka | 1 |
| 6 | Elka | 10 |
| 7 | Ilene | 2 |
| 8 | Thacher | 3 |
| 9 | Romola | NULL |
| 10 | Levy | 6 |
| 10 | Levy | 9 |
+-------------+------------+----------+
- 右外连接（RIGHT JOIN）
与左外连接逻辑完全相反，返回 右表（RIGHT JOIN 右侧的表）的全部记录，以及左表中匹配的记录。如果左表无匹配，则左表所有列返回 NULL
mysql> SELECT a.customer_id,a.first_name,b.order_id FROM customers AS a RIGHT JOIN orders AS b ON a.customer_id = b.customer_id;
+-------------+------------+----------+
| customer_id | first_name | order_id |
+-------------+------------+----------+
| 2 | Ines | 4 |
| 2 | Ines | 7 |
| 5 | Clemmie | 5 |
| 5 | Clemmie | 8 |
| 6 | Elka | 1 |
| 6 | Elka | 10 |
| 7 | Ilene | 2 |
| 8 | Thacher | 3 |
| 10 | Levy | 6 |
| 10 | Levy | 9 |
+-------------+------------+----------+
小知识：使用 LEFT JOIN 或者 RIGHT JOIN 时，必定是外连接方式，单纯使用 JOIN 是内连接方式
练习题： 查询生成三个列：产品 ID、名字、订单项目表里的数量，需要连接产品表和订单项目表
mysql> SELECT a.product_id,a.name,SUM(b.quantity) AS quantity FROM products AS a LEFT JOIN order_items AS b ON a.product_id = b.product_id GROUP BY a.product_id,a.name;
+------------+------------------------------+----------+
| product_id | name | quantity |
+------------+------------------------------+----------+
| 1 | Foam Dinner Plate | 16 |
| 2 | Pork - Bacon,back Peameal | 7 |
| 3 | Lettuce - Romaine, Heart | 28 |
| 4 | Brocolinni - Gaylan, Chinese | 8 |
| 5 | Sauce - Ranch Dressing | 3 |
| 6 | Petit Baguette | 7 |
| 7 | Sweet Pea Sprouts | NULL |
| 8 | Island Oasis - Raspberry | 2 |
| 9 | Longan | 9 |
| 10 | Broom - Push | 7 |
+------------+------------------------------+----------+
2.8、多表外连接 | Outer Join Between Multiple Tables
mysql> SELECT a.customer_id,a.first_name,b.order_id,c.name FROM customers AS a LEFT JOIN orders AS b ON a.customer_id = b.customer_id LEFT JOIN shippers AS c ON b.shipper_id = c.shipper_id ORDER BY a.customer_id;
+-------------+------------+----------+------------------------+
| customer_id | first_name | order_id | name |
+-------------+------------+----------+------------------------+
| 1 | Babara | NULL | NULL |
| 2 | Ines | 4 | NULL |
| 2 | Ines | 7 | Mraz, Renner and Nolan |
| 3 | Freddi | NULL | NULL |
| 4 | Ambur | NULL | NULL |
| 5 | Clemmie | 8 | NULL |
| 5 | Clemmie | 5 | Satterfield LLC |
| 6 | Elka | 10 | Schinner-Predovic |
| 6 | Elka | 1 | NULL |
| 7 | Ilene | 2 | Mraz, Renner and Nolan |
| 8 | Thacher | 3 | NULL |
| 9 | Romola | NULL | NULL |
| 10 | Levy | 9 | Hettinger LLC |
| 10 | Levy | 6 | NULL |
+-------------+------------+----------+------------------------+
练习题： 执行一个查询，查询中有如下几列：订单日期、订单 ID、顾客名字、发货人、状态列
mysql> SELECT a.order_date,a.order_id,b.first_name,c.name,a.status,d.name AS status_name FROM orders AS a LEFT JOIN customers AS b ON a.customer_id = b.customer_id LEFT JOIN shippers AS c ON a.shipper_id = c.shipper_id LEFT JOIN order_statuses AS d ON a.status = d.order_status_id;
+------------+----------+------------+------------------------+--------+-------------+
| order_date | order_id | first_name | name | status | status_name |
+------------+----------+------------+------------------------+--------+-------------+
| 2017-07-05 | 9 | Levy | Hettinger LLC | 2 | Shipped |
| 2018-04-22 | 10 | Elka | Schinner-Predovic | 2 | Shipped |
| 2017-08-25 | 5 | Clemmie | Satterfield LLC | 2 | Shipped |
| 2018-08-02 | 2 | Ilene | Mraz, Renner and Nolan | 2 | Shipped |
| 2018-09-22 | 7 | Ines | Mraz, Renner and Nolan | 2 | Shipped |
| 2019-01-30 | 1 | Elka | NULL | 1 | Processed |
| 2017-12-01 | 3 | Thacher | NULL | 1 | Processed |
| 2017-01-22 | 4 | Ines | NULL | 1 | Processed |
| 2018-11-18 | 6 | Levy | NULL | 1 | Processed |
| 2018-06-08 | 8 | Clemmie | NULL | 1 | Processed |
+------------+----------+------------+------------------------+--------+-------------+
2.9、自外连接 | Self Outer Joins
mysql> SELECT a.employee_id,a.first_name,b.first_name AS manager FROM employees AS a LEFT JOIN employees AS b ON a.reports_to = b.employee_id;
+-------------+------------+----------+
| employee_id | first_name | manager |
+-------------+------------+----------+
| 33391 | D'arcy | Yovonnda |
| 37270 | Yovonnda | NULL |
| 37851 | Sayer | Yovonnda |
| 40448 | Mindy | Yovonnda |
| 56274 | Keriann | Yovonnda |
| 63196 | Alaster | Yovonnda |
| 67009 | North | Yovonnda |
| 67370 | Elladine | Yovonnda |
| 68249 | Nisse | Yovonnda |
| 72540 | Guthrey | Yovonnda |
| 72913 | Kass | Yovonnda |
| 75900 | Virge | Yovonnda |
| 76196 | Mirilla | Yovonnda |
| 80529 | Lynde | Yovonnda |
| 80679 | Mildrid | Yovonnda |
| 84791 | Hazel | Yovonnda |
| 95213 | Cole | Yovonnda |
| 96513 | Theresa | Yovonnda |
| 98374 | Estrellita | Yovonnda |
| 115357 | Ivy | Yovonnda |
+-------------+------------+----------+
2.10、USING 子句
-- 通过 USING 子句，将表连接时候连接字段一样的列进行简化写法，适用于内连接和外连接
mysql> SELECT a.order_id,b.first_name,c.name AS shipper FROM orders AS a INNER JOIN customers AS b USING (customer_id) LEFT JOIN shippers AS c USING (shipper_id);
+----------+------------+------------------------+
| order_id | first_name | shipper |
+----------+------------+------------------------+
| 9 | Levy | Hettinger LLC |
| 10 | Elka | Schinner-Predovic |
| 5 | Clemmie | Satterfield LLC |
| 2 | Ilene | Mraz, Renner and Nolan |
| 7 | Ines | Mraz, Renner and Nolan |
| 1 | Elka | NULL |
| 3 | Thacher | NULL |
| 4 | Ines | NULL |
| 6 | Levy | NULL |
| 8 | Clemmie | NULL |
+----------+------------+------------------------+
如果有多个条件进行比较，并且列名称一样，可以在括号里面用逗号分隔，例如：USING (order_id,product_id)
练习题： 在 sql_invoicing 库进行，写一段查询，从支付表里选择支付生成包括：日期、客户、数额、付款方式
mysql> SELECT a.date,b.name,a.amount,c.name AS payment_method FROM payments AS a INNER JOIN clients AS b USING (client_id) INNER JOIN payment_methods AS c ON a.payment_method = c.payment_method_id;
+------------+-------------+--------+----------------+
| date | name | amount | payment_method |
+------------+-------------+--------+----------------+
| 2019-02-12 | Topiclounge | 8.18 | Credit Card |
| 2019-01-03 | Vinte | 74.55 | Credit Card |
| 2019-01-11 | Yadel | 0.03 | Credit Card |
| 2019-01-26 | Topiclounge | 87.44 | Credit Card |
| 2019-01-15 | Yadel | 80.31 | Credit Card |
| 2019-01-15 | Yadel | 68.10 | Credit Card |
| 2019-01-08 | Topiclounge | 32.77 | Credit Card |
| 2019-01-08 | Topiclounge | 10.00 | Cash |
+------------+-------------+--------+----------------+
2.11、自然连接 | Natural Joins
-- 让其自己选择 JOIN 方式和 JOIN 的字段，不建议使用
mysql> SELECT * FROM orders AS a NATURAL JOIN customers;
+-------------+----------+------------+--------+-----------------------------------------------------------------------+--------------+------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| customer_id | order_id | order_date | status | comments | shipped_date | shipper_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+----------+------------+--------+-----------------------------------------------------------------------+--------------+------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
| 6 | 1 | 2019-01-30 | 1 | NULL | NULL | NULL | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | 2 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | 3 | 2017-12-01 | 1 | NULL | NULL | NULL | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 2 | 4 | 2017-01-22 | 1 | NULL | NULL | NULL | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 10 | 6 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 2 | 7 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 5 | 8 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 10 | 9 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 6 | 10 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
+-------------+----------+------------+--------+-----------------------------------------------------------------------+--------------+------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+
2.12、交叉连接 | Cross Joins
mysql> SELECT a.first_name,b.name FROM customers AS a CROSS JOIN products AS b ORDER BY a.first_name;
+------------+------------------------------+
| first_name | name |
+------------+------------------------------+
| Ambur | Pork - Bacon,back Peameal |
| Ambur | Foam Dinner Plate |
| Ambur | Broom - Push |
| Ambur | Longan |
| Ambur | Island Oasis - Raspberry |
| Ambur | Sweet Pea Sprouts |
| Ambur | Petit Baguette |
| Ambur | Sauce - Ranch Dressing |
| Ambur | Brocolinni - Gaylan, Chinese |
| Ambur | Lettuce - Romaine, Heart |
| Babara | Petit Baguette |
| Babara | Sauce - Ranch Dressing |
| Babara | Brocolinni - Gaylan, Chinese |
| Babara | Lettuce - Romaine, Heart |
| Babara | Pork - Bacon,back Peameal |
| Babara | Foam Dinner Plate |
| Babara | Broom - Push |
| Babara | Longan |
| Babara | Island Oasis - Raspberry |
| Babara | Sweet Pea Sprouts |
| Clemmie | Broom - Push |
| Clemmie | Longan |
| Clemmie | Island Oasis - Raspberry |
| Clemmie | Sweet Pea Sprouts |
| Clemmie | Petit Baguette |
| Clemmie | Sauce - Ranch Dressing |
| Clemmie | Brocolinni - Gaylan, Chinese |
| Clemmie | Lettuce - Romaine, Heart |
| Clemmie | Pork - Bacon,back Peameal |
| Clemmie | Foam Dinner Plate |
| Elka | Sauce - Ranch Dressing |
| Elka | Brocolinni - Gaylan, Chinese |
| Elka | Lettuce - Romaine, Heart |
| Elka | Pork - Bacon,back Peameal |
| Elka | Foam Dinner Plate |
| Elka | Broom - Push |
| Elka | Longan |
| Elka | Island Oasis - Raspberry |
| Elka | Sweet Pea Sprouts |
| Elka | Petit Baguette |
| Freddi | Longan |
| Freddi | Island Oasis - Raspberry |
| Freddi | Sweet Pea Sprouts |
| Freddi | Petit Baguette |
| Freddi | Sauce - Ranch Dressing |
| Freddi | Brocolinni - Gaylan, Chinese |
| Freddi | Lettuce - Romaine, Heart |
| Freddi | Pork - Bacon,back Peameal |
| Freddi | Foam Dinner Plate |
| Freddi | Broom - Push |
| Ilene | Broom - Push |
| Ilene | Longan |
| Ilene | Island Oasis - Raspberry |
| Ilene | Sweet Pea Sprouts |
| Ilene | Petit Baguette |
| Ilene | Sauce - Ranch Dressing |
| Ilene | Brocolinni - Gaylan, Chinese |
| Ilene | Lettuce - Romaine, Heart |
| Ilene | Pork - Bacon,back Peameal |
| Ilene | Foam Dinner Plate |
| Ines | Broom - Push |
| Ines | Longan |
| Ines | Island Oasis - Raspberry |
| Ines | Sweet Pea Sprouts |
| Ines | Petit Baguette |
| Ines | Sauce - Ranch Dressing |
| Ines | Brocolinni - Gaylan, Chinese |
| Ines | Lettuce - Romaine, Heart |
| Ines | Pork - Bacon,back Peameal |
| Ines | Foam Dinner Plate |
| Levy | Broom - Push |
| Levy | Longan |
| Levy | Island Oasis - Raspberry |
| Levy | Sweet Pea Sprouts |
| Levy | Petit Baguette |
| Levy | Sauce - Ranch Dressing |
| Levy | Brocolinni - Gaylan, Chinese |
| Levy | Lettuce - Romaine, Heart |
| Levy | Pork - Bacon,back Peameal |
| Levy | Foam Dinner Plate |
| Romola | Pork - Bacon,back Peameal |
| Romola | Foam Dinner Plate |
| Romola | Broom - Push |
| Romola | Longan |
| Romola | Island Oasis - Raspberry |
| Romola | Sweet Pea Sprouts |
| Romola | Petit Baguette |
| Romola | Sauce - Ranch Dressing |
| Romola | Brocolinni - Gaylan, Chinese |
| Romola | Lettuce - Romaine, Heart |
| Thacher | Island Oasis - Raspberry |
| Thacher | Sweet Pea Sprouts |
| Thacher | Petit Baguette |
| Thacher | Sauce - Ranch Dressing |
| Thacher | Brocolinni - Gaylan, Chinese |
| Thacher | Lettuce - Romaine, Heart |
| Thacher | Pork - Bacon,back Peameal |
| Thacher | Foam Dinner Plate |
| Thacher | Broom - Push |
| Thacher | Longan |
+------------+------------------------------+
练习题： 做一个发货人和产品的交叉连接，先用隐式语法，然后用显示语法
mysql> SELECT * FROM shippers AS a,products AS b;
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
| shipper_id | name | product_id | name | quantity_in_stock | unit_price |
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
| 1 | Hettinger LLC | 1 | Foam Dinner Plate | 70 | 1.21 |
| 2 | Schinner-Predovic | 1 | Foam Dinner Plate | 70 | 1.21 |
| 3 | Satterfield LLC | 1 | Foam Dinner Plate | 70 | 1.21 |
| 4 | Mraz, Renner and Nolan | 1 | Foam Dinner Plate | 70 | 1.21 |
| 5 | Waters, Mayert and Prohaska | 1 | Foam Dinner Plate | 70 | 1.21 |
| 1 | Hettinger LLC | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 2 | Schinner-Predovic | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 3 | Satterfield LLC | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 4 | Mraz, Renner and Nolan | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 5 | Waters, Mayert and Prohaska | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 1 | Hettinger LLC | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 2 | Schinner-Predovic | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 3 | Satterfield LLC | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 4 | Mraz, Renner and Nolan | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 5 | Waters, Mayert and Prohaska | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 1 | Hettinger LLC | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 2 | Schinner-Predovic | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 3 | Satterfield LLC | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 4 | Mraz, Renner and Nolan | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 5 | Waters, Mayert and Prohaska | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 1 | Hettinger LLC | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 2 | Schinner-Predovic | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 3 | Satterfield LLC | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 4 | Mraz, Renner and Nolan | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 5 | Waters, Mayert and Prohaska | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 1 | Hettinger LLC | 6 | Petit Baguette | 14 | 2.39 |
| 2 | Schinner-Predovic | 6 | Petit Baguette | 14 | 2.39 |
| 3 | Satterfield LLC | 6 | Petit Baguette | 14 | 2.39 |
| 4 | Mraz, Renner and Nolan | 6 | Petit Baguette | 14 | 2.39 |
| 5 | Waters, Mayert and Prohaska | 6 | Petit Baguette | 14 | 2.39 |
| 1 | Hettinger LLC | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 2 | Schinner-Predovic | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 3 | Satterfield LLC | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 4 | Mraz, Renner and Nolan | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 5 | Waters, Mayert and Prohaska | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 1 | Hettinger LLC | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 2 | Schinner-Predovic | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 3 | Satterfield LLC | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 4 | Mraz, Renner and Nolan | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 5 | Waters, Mayert and Prohaska | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 1 | Hettinger LLC | 9 | Longan | 67 | 2.26 |
| 2 | Schinner-Predovic | 9 | Longan | 67 | 2.26 |
| 3 | Satterfield LLC | 9 | Longan | 67 | 2.26 |
| 4 | Mraz, Renner and Nolan | 9 | Longan | 67 | 2.26 |
| 5 | Waters, Mayert and Prohaska | 9 | Longan | 67 | 2.26 |
| 1 | Hettinger LLC | 10 | Broom - Push | 6 | 1.09 |
| 2 | Schinner-Predovic | 10 | Broom - Push | 6 | 1.09 |
| 3 | Satterfield LLC | 10 | Broom - Push | 6 | 1.09 |
| 4 | Mraz, Renner and Nolan | 10 | Broom - Push | 6 | 1.09 |
| 5 | Waters, Mayert and Prohaska | 10 | Broom - Push | 6 | 1.09 |
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
mysql> SELECT * FROM shippers AS a CROSS JOIN products AS b;
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
| shipper_id | name | product_id | name | quantity_in_stock | unit_price |
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
| 1 | Hettinger LLC | 1 | Foam Dinner Plate | 70 | 1.21 |
| 2 | Schinner-Predovic | 1 | Foam Dinner Plate | 70 | 1.21 |
| 3 | Satterfield LLC | 1 | Foam Dinner Plate | 70 | 1.21 |
| 4 | Mraz, Renner and Nolan | 1 | Foam Dinner Plate | 70 | 1.21 |
| 5 | Waters, Mayert and Prohaska | 1 | Foam Dinner Plate | 70 | 1.21 |
| 1 | Hettinger LLC | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 2 | Schinner-Predovic | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 3 | Satterfield LLC | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 4 | Mraz, Renner and Nolan | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 5 | Waters, Mayert and Prohaska | 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 1 | Hettinger LLC | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 2 | Schinner-Predovic | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 3 | Satterfield LLC | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 4 | Mraz, Renner and Nolan | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 5 | Waters, Mayert and Prohaska | 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 1 | Hettinger LLC | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 2 | Schinner-Predovic | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 3 | Satterfield LLC | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 4 | Mraz, Renner and Nolan | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 5 | Waters, Mayert and Prohaska | 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 1 | Hettinger LLC | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 2 | Schinner-Predovic | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 3 | Satterfield LLC | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 4 | Mraz, Renner and Nolan | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 5 | Waters, Mayert and Prohaska | 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 1 | Hettinger LLC | 6 | Petit Baguette | 14 | 2.39 |
| 2 | Schinner-Predovic | 6 | Petit Baguette | 14 | 2.39 |
| 3 | Satterfield LLC | 6 | Petit Baguette | 14 | 2.39 |
| 4 | Mraz, Renner and Nolan | 6 | Petit Baguette | 14 | 2.39 |
| 5 | Waters, Mayert and Prohaska | 6 | Petit Baguette | 14 | 2.39 |
| 1 | Hettinger LLC | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 2 | Schinner-Predovic | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 3 | Satterfield LLC | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 4 | Mraz, Renner and Nolan | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 5 | Waters, Mayert and Prohaska | 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 1 | Hettinger LLC | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 2 | Schinner-Predovic | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 3 | Satterfield LLC | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 4 | Mraz, Renner and Nolan | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 5 | Waters, Mayert and Prohaska | 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 1 | Hettinger LLC | 9 | Longan | 67 | 2.26 |
| 2 | Schinner-Predovic | 9 | Longan | 67 | 2.26 |
| 3 | Satterfield LLC | 9 | Longan | 67 | 2.26 |
| 4 | Mraz, Renner and Nolan | 9 | Longan | 67 | 2.26 |
| 5 | Waters, Mayert and Prohaska | 9 | Longan | 67 | 2.26 |
| 1 | Hettinger LLC | 10 | Broom - Push | 6 | 1.09 |
| 2 | Schinner-Predovic | 10 | Broom - Push | 6 | 1.09 |
| 3 | Satterfield LLC | 10 | Broom - Push | 6 | 1.09 |
| 4 | Mraz, Renner and Nolan | 10 | Broom - Push | 6 | 1.09 |
| 5 | Waters, Mayert and Prohaska | 10 | Broom - Push | 6 | 1.09 |
+------------+-----------------------------+------------+------------------------------+-------------------+------------+
2.13、联合 | Unions
-- 使用 UNION 运算符，合并多段数据查询
mysql> SELECT order_id,order_date,'Active' AS status FROM orders WHERE order_date >= '2019-01-01'
-> UNION
-> SELECT order_id,order_date,'Archived' AS status FROM orders WHERE order_date < '2019-01-01'
-> ;
+----------+------------+----------+
| order_id | order_date | status |
+----------+------------+----------+
| 1 | 2019-01-30 | Active |
| 2 | 2018-08-02 | Archived |
| 3 | 2017-12-01 | Archived |
| 4 | 2017-01-22 | Archived |
| 5 | 2017-08-25 | Archived |
| 6 | 2018-11-18 | Archived |
| 7 | 2018-09-22 | Archived |
| 8 | 2018-06-08 | Archived |
| 9 | 2017-07-05 | Archived |
| 10 | 2018-04-22 | Archived |
+----------+------------+----------+
练习题： 写一段查询，包含四列：顾客ID、名字、积分、类型。
类型分级别为：
• 积分小于 2000 为 'Bronze'
• 积分在 2000 至 3000 为 'Silver'
• 积分超过 3000 分为 'Gold'
mysql> SELECT customer_id,first_name,points,'Bronze' AS type FROM customers WHERE points < 2000
-> UNION
-> SELECT customer_id,first_name,points,'Silver' AS type FROM customers WHERE points BETWEEN 2000 and 3000
-> UNION
-> SELECT customer_id,first_name,points,'Gold' AS type FROM customers WHERE points > 3000
-> ORDER BY points;
+-------------+------------+--------+--------+
| customer_id | first_name | points | type |
+-------------+------------+--------+--------+
| 8 | Thacher | 205 | Bronze |
| 4 | Ambur | 457 | Bronze |
| 10 | Levy | 796 | Bronze |
| 2 | Ines | 947 | Bronze |
| 9 | Romola | 1486 | Bronze |
| 7 | Ilene | 1672 | Bronze |
| 1 | Babara | 2273 | Silver |
| 3 | Freddi | 2967 | Silver |
| 6 | Elka | 3073 | Gold |
| 5 | Clemmie | 3675 | Gold |
+-------------+------------+--------+--------+
浙公网安备 33010602011771号