学习内容来自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;
- 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;