MySQL学习记录2--内、外连接查询

学习内容来自B站SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
1 内连接INNER JOIN (JOIN)

-- 内连接 orders 和 customers, 以order_id升序显示相应信息 （o是orders的别名,也可在o前面加AS）
SELECT order_id,first_name,o.customer_id
FROM orders o
JOIN customers
	ON o.customer_id = customers.customer_id
ORDER BY order_id;

2.跨数据库连接

-- 使用当前数据库，连接sql_inventory中的相同名字表products，显示相应的数据
SELECT *
FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id = p.product_id;

3.自连接

-- 使用 sql_hr这个数据库
USE sql_hr; 
-- 使用该数据库的employees表进行自连接，找到管理员id
SELECT e.employee_id, e.first_name, m.first_name AS manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id;

4.多表连接

-- 使用sql_invoicing 数据库
USE sql_invoicing
-- 将clients与paymen_methods与payments表连接并显示一些信息
SELECT p.amount,c.name,pm.name
FROM payments p
JOIN clients c ON c.client_id = p.client_id
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;

5.复合连接查询条件

-- 当表中不只一个主键的时候使用
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.隐式连接语法

-- 隐式连接查询
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_id;
-- 等同于之前的内连接(尽量使用下面的)
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

7.外连接 OUTER JOIN

-- OUTER 关键字可有可无，跟内连接的INNER类似
-- LEFT JOIN （左外连接）所有customers 的内容会被返回，不管orders有没有记录
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;

-- RIGHT JOIN（右外连接）所有orders的内容会被返回，不管customers有没有记录
SELECT c.customer_id,c.first_name,o.order_id
FROM customers c
RIGHT JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY o.order_id;

8.多表外连接

-- 尽量避免使用右连接，因为当多表外连接时，混合使用左、右连接会使代码很复杂
SELECT c.customer_id,c.first_name,o.order_id,sh.name
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id;

9.自外连接(与自内连接对比，会显示出m.employee_id不能匹配e.reports_to的数据)

USE sql_hr

SELECT 
	e.employee_id,
    e.first_name,
    m.employee_id AS manager
FROM employees e
LEFT JOIN employees m
	ON e.reports_to = m.employee_id;

10.USING 子句

-- USING 后面等同于前面 ON 后面（必须连接的两个表的属性名称相同）
SELECT o.order_id,
	c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
	USING (customer_id)
LEFT JOIN shippers sh
	USING (shipper_id);

11.自然连接 （由数据库引擎自己连接，容易出现错误）

SELECT 
	o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c;

12.交叉连接

-- 执行表的组合
SELECT 
	c.customer_id,
    p.product_id
FROM customers c
CROSS JOIN products p;

– 交叉连接的隐式写法

SELECT 
	c.customer_id,
    p.product_id
FROM customers c,products p;

13. UNION （联合多个查询）

-- 多个select语句会去除相同数据
-- 每个select语句列数要一样，否则报错
-- 本题通过将customers中按points点数分类 为青铜，白银，黄金，使用UNION联合查询，最后ORDER BY 通过用户first_name排序，ORDER BY前不写UNION。
SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Bronze' AS type
FROM customers c
WHERE c.points < 2000
UNION
SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Silver' AS type
FROM customers c
WHERE c.points >= 2000 AND c.points < 3000
UNION
SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Gold' AS type
FROM customers c
WHERE points > 3000
ORDER BY first_name;