MYSQL学习笔记-连接

一、JOIN 语句

从多张表格选择列

USE sql_store;
SELECT order_id, o.product_id, name, quantity, o.unit_price
FROM order_items o
JOIN products p ON o.product_id = p.product_id

• 为了方便区分，改写表格的名称。如 order_items简写为o
• 这里的重命名写在FROM的位置，但SELECT里可以使用
• 改写后所有位置须使用新名称

二、JOIN 用法

1. 合并不同数据库的表格

将在不同数据库的表中的列提取出来

USE sql_store;
SELECT *
FROM order_items o
JOIN sql_inventory.products p 
	ON o.product_id = p.product_id

• 对不是当前数据库的表格需要加上前缀，例如此处的sql_inventory

2. 合并同一数据库的表格（自连接）

下表为employees表格：

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
40448	Mindy	Crissil	Staff Scientist	94860	37270	1
56274	Keriann	Alloisi	VP Marketing	110150	37270	1

• 通过employee_id和repors_to列找出该公司的员工级别关系。因此合并同一数据库内的表格employees。

USE sql_hr;
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

• 合并前的表格命名为e，在此基础上与m进行合并，使reports_to对应于employee_id，即可找出每个员工的上级，并将其命名为manager。
• 运行结果为：

employee_id	first_name	manager
33391	D’arcy	Yovonnda
37851	Sayer	Yovonnda
40448	Mindy	Yovonnda
56274	Keriann	Yovonnda

3. 多表连接

• payment表格如下;

payment_id	client_id	invoice_id	date	amount	payment_method
1	5	2	2019-02-12	8.18	1
2	1	6	2019-01-03	74.55	1
3	3	11	2019-01-11	0.03	1
4	5	13	2019-01-26	87.44	1
5	3	15	2019-01-15	80.31	1
6	3	17	2019-01-15	68.10	1
7	5	18	2019-01-08	32.77	1
8	5	18	2019-01-08	10.00	2

• payment_method表格如下

payment_method_id	name
1	Credit Card
2	Cash
3	PayPal
4	Wire Transfer

• clients 表格如下

client_id	name	address	city	state	number
1	Vinte	3 Nevada Parkway	Syracuse	NY	315-252-7305
2	Myworks	34267 Glendale Parkway	Huntington	WV	304-659-1170
3	Yadel	096 Pawling Parkway	San Francisco	CA	415-144-6037
4	Kwideo	81674 Westerfield Circle	Waco	TX	254-750-0784
5	Topiclounge	0863 Farmco Road	Portland	OR	971-888-9129

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

• 运行结果如下：

client_id	name	date	invoice_id	amount	payment_method
5	Topiclounge	2019-02-12	2	8.18	Credit Card
1	Vinte	2019-01-03	6	74.55	Credit Card
3	Yadel	2019-01-11	11	0.03	Credit Card
5	Topiclounge	2019-01-26	13	87.44	Credit Card
3	Yadel	2019-01-15	15	80.31	Credit Card
3	Yadel	2019-01-15	17	68.10	Credit Card
5	Topiclounge	2019-01-08	18	32.77	Credit Card
5	Topiclounge	2019-01-08	18	10.00	Cash

4. 复合连接条件

在单列无法唯一确定所属行时，考虑多列复合连接条件。

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_Id
    AND oi.product_id = oin.product_id    

• 使用AND连接两个条件

5. 单表外连接

-之前的连接JOIN是内连接，省略了INNER，使用外连接时同样可以省略OUTER，只写LEFT或者RIGHT

SELECT 
p.product_id, p.name, oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON oi.product_id = p.product_id
ORDER BY product_id
    

• 以FROM后面的这个表格作为基础，如果LEFT JOIN则表示选中所有products，不论是否产生订单
• 同理，若RIGHT JOIN则表示选中所有订单

6. 多表外连接

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

运行结果如下：

7. 自外连接

在2的例子中，缺少了一条CEO本人的记录，可以用自外连接的方式使其显示出来

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

得到的结果为：

employee_id	first_name	manager
33391	D’arcy	Yovonnda
37270	Yovonnda	Null
37851	Sayer	Yovonnda
40448	Mindy	Yovonnda
56274	Keriann	Yovonnda

普通的JOIN无法显示boss本人的记录，需要使用LEFT JOIN

8. USING子句

当不同的表格中的列名称相同时，可以用USING子句简化表达

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

内连接、外连接都可以用USING，但列名称必须一致

-- 简化 4.复合连接条件的代码
SELECT *
FROM order_items oi
JOIN order_item_notes oin
	--ON oi.order_id = oin.order_Id
    --AND oi.product_id = oin.product_id  
    USING (order_id, product_id)  

--简化 3.多表连接代码
SELECT 
	p.date,
    c.name AS client,
	p.amount,
    pm.name
FROM payments p
JOIN clients c
	USING (client_id)
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id

9. 自然连接

自动连接，无法控制

SELECT *
FROM orders o
NATURAL JOIN customers c

10. 交叉连接

显式语法

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

11. 联合

结合不同表单的行，用UNION连接
举例：要求得出不同用户分级，points<2000为青铜，2000<points<3000为白银，points>3000为黄金，按照用户first_name字母顺序排列。

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 first_name

运行结果：

注意点：

• UNION连接的不同select段后面不能加分号
• 区间的两个端点用关键词BETWEEN 和 AND