MySQL(B站CodeWithMosh)——2024.10.3(6)

ZZZZZZ目的
ZZZZZZ代码
ZZZZZZ重点
ZZZZZZ操作（非代码，需要自己手动）

5- 复合连接条件 | Compound Join Conditions_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=22&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 打开sql_store中的order_items表格，可以看到这个表格没有一列没有重复的值，也就是无法用单一列来准确识别某张表里的情况。再打开order_items表格右边的工具按钮，可以看见order_id和product_id两个前面都有⚡，表明这两列都有主键，称之为复合主键，复合主键包含超过一列。
2. 将order_item表和order_item_notes表中的订单id和产品id合并在一起
   SELECT *
   FROM order_items oi
   JOIN order_item_notes oin 
            ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
   代码可以正常运行，表格数据里面就没有匹配的，所以输出为空

6- 隐式连接语法 | Implicit Join Syntax_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=23&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 将orders和customers通过customer_id连接起来
   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
   下面的代码是隐式连接语法，但是更建议用上面的那种，因为下面的代码如果忘记写WHERE会造成orders的数据和customers的数据每一条相互匹配，会生成很多新的数据

7- 外连接 | Outer Joins_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=24&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 将orders和customers通过customer_id连接起来，输出顾客id，顾客的名和订单id，还要保证每个顾客（无论是否有订单）都要输出，并按照顾客顾客id的顺序排序
   customers表中是所有的顾客信息，但是因为不是所有的顾客都有订单，所以在orders表中只有一部分顾客的id，当用JOIN orders o ON c.customer_id = o.customer_id这样的内连接时，会将没有订单的顾客自动删除，所以这是需要用到外连接
   SELECT c.customer_id, c.first_name, o.order_id
   FROM customers c
   LEFT JOIN orders o ON c.customer_id = o.customer_id
   ORDER BY c.customer_id
   外连接分为两种，一种是“LEFT JOIN”，它会将左表的所有记录（也就是本案例customers表）全部返回，不管条件是否正确，可以看到，所有的顾客都输出了；如果用另外一种外连接“RIGHT JOIN”，输出和用内连接的一样，如果想用“RIGHT JOIN”将所有的顾客信息输出，那么就可以将两个表的顺序换一下：
   SELECT c.customer_id, c.first_name, o.order_id
   FROM orders o
   RIGHT JOIN customers c ON c.customer_id = o.customer_id
   ORDER BY c.customer_id
   强调一下，右连接不等于内连接，只是这个案例碰巧了而已
   有的人用“LEFT OUTER JOIN”和"RIGHT OUTER JOIN“，这里的OUTER写不写都可以

【练习题】
将products和order_items表格通过产品id合并，输出，产品名称，数量和所有产品id
SELECT p.product_id, p.name, quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id

8- 多表外连接 | Outer Join Between Multiple Tables_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=25&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 将orders和customers通过customer_id连接起来，orders和shippers（发货人）通过shipperid连接起来，输出顾客id，顾客的名和订单id，还要保证每个顾客（无论是否有订单）都要输出，并按照顾客顾客id的顺序排序
   SELECT c.customer_id, c.first_name, o.order_id
   FROM customers c
   LEFT JOIN orders o ON c.customer_id = o.customer_id
   LEFT JOIN shippers sh ON sh.shipper_id = o.shipper_id
   ORDER BY c.customer_id
   在书写代码的时候，尽量使用左连接”LEFT JOIN“，方便查看表格是怎么连接的

【练习题】
如下图所示，输出订单时间，所有订单id，顾客名，发货人和状态

SELECT order_date, order_id, first_name, sh.name AS shipper, os.name AS status
FROM orders o
LEFT JOIN shippers sh ON sh.shipper_id = o.shipper_id
LEFT JOIN customers c ON c.customer_id = o.customer_id
JOIN order_statuses os ON os.order_status_id = o.status
ORDER BY status, order_id

9- 自外连接 | Self Outer Joins_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=26&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. sql_hr中employees表格中employee_id是员工编码，reports_to是指该员工对应的管理人员编码，现在将表中的employee_id和reports_to结合在一起，方便知道某员工的上级是谁，并只输出员工id，员工的名和管理人员的名，给管理人员的名那一列命名为“manager”，要求输出所有人的信息，无论这名员工有没有管理人员
   USE sql_hr;
   SELECT e.employee_id, e.first_name, m.first_name AS manager
   FROM employees e
   LEFT JOIN employees m ON e.reports_to = m.employee_id
   这里要想明白为什么要用m.first_name作为manager：虽然都是employees表，但是一个作为员工表，一个作为管理人员表，所以要用管理人员表的first_name，这里的管理人员表是已经按照员工对应管理人员编码进行排序了的，所以输出的就是管理人员的名

10- USING子句 | The USING Clause_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=27&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 合并orders表格和customers表格中的列，并指输出订单编号、姓和名
   SELECT order_id, first_name, o.customer_id
   FROM orders o
   JOIN  customers c ON o.customer_id = c.customer_id
   或者
   SELECT order_id, first_name
   FROM orders o
   JOIN customers c USING (customer_id)
   如果两个表的列名称是完全一样的，那么就可以用USING子句来替换ON子句
2. 将order_item表和order_item_notes表中的订单id和产品id合并在一起
   SELECT *
   FROM order_items oi
   JOIN order_item_notes oin 
            ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
   或者
   SELECT *
   FROM order_items oi
   JOIN order_item_notes oin USING (order_id, product_id)

【练习题】
在sql_invoicing数据库中，生成如同下图一样的结果

USE sql_invoicing;
SELECT date, c.name AS client, amount, pm.name AS name
FROM payments p
JOIN clients c USING (client_id)
JOIN payment_methods pm ON pm.payment_method_id = p.payment_method 

11- 自然连接 | Natural Joins_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=28&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 让orders表和customers表自己合并在一起
   SELECT order_id, first_name, o.customer_id
   FROM orders o
   NATURAL JOIN customers c
   这种方法很简单，但是一般不建议这么写，因为并不知道是怎么合并的，所以会产生不知道的结果

12- 交叉连接 | Cross Joins_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=29&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 将customers表和products表中的每条信息连接起来，将顾客的姓作为”customer“、产品的名称作为”product“输出，并将结果按照顾客的名排序
   SELECT c.first_name AS customer, p.name AS product
   FROM customers c
   CROSS JOIN products p
   ORDER BY c.first_name
   或者
   SELECT c.first_name AS customer, p.name AS product
   FROM customers c, products p
   ORDER BY c.first_name
   交叉连接可以结合或连接第一个表的每条记录和第二个表的每条记录
   第一种代码是显式语法，第二种是隐式语法

【练习题】
分别用显式语法和隐式语法将shippers和products的每条信息连接起来
显式语法：
SELECT *
FROM shippers s
CROSS JOIN products p
隐式语法：
SELECT *
FROM shippers s, products p

13- 联合 | Unions_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1UE41147KC?p=30&vd_source=eaeec77dfceb13d96cce76cc299fdd08

1. 根据orders表格，将里面所有的订单旁边都有一个标签”status“，如果订单时2019年的，就叫active（活跃的），如果是2019年以前的，就叫archived（存档），并输出订单id，订单日期和status
   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'
   注意，UNION前选择的列数要和UNION后选择的列数一样，不然会报错
   比如下面的这段代码，UNION选择了两列(first_name和last_name)，UNION后面只选择了一列(name)
   SELECT first_name, last_name
   FROM customers
   UNION
   SELECT name
   FROM shippers
2. MySQL会将列名定义为最先UNION的名字，例如下面的代码输出的列名为first_name
   SELECT first_name
   FROM customers
   UNION
   SELECT name
   FROM shippers

【练习题】
输出顾客id，顾客的名，积分和类型，类型中有”Bronze“（青铜）、”Silver“和”Gold“，2000积分以下是青铜，2000-3000是白银，3000以上是黄金，并按照名的顺序进行排序
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 >= 2000 AND points < 3000
UNION
SELECT customer_id, first_name, points, "Gold" AS type
FROM customers
WHERE points >= 3000 
ORDER BY first_name
注意，SELECT customer_id, first_name, points, "Bronze" AS type这里的"Bronze"是需要用引号的，因为这是一个新的字符串