USE sql_store;
-- 多表查询
-- 一张表需要连接另一张表,之所以信息要分开写是因为如果要写在一张表中
-- 那如果更改了一个客户信息,那么所有的订单都要修改对应所有的信息,所以要分开写,连接靠id
-- 连接join + 连接的表名 on +连接的条件
-- INNER JOIN 和 JOIN 是一样的 
SELECT *
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id;

-- 多表输出指定的列
-- 不同的表的相同列需要带上表名
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id;

-- 使用别名
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers
	ON o.customer_id = customers.customer_id;

-- 使用别名
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id;

-- 练习:订单order_items和产品表products
-- 注意用别名后只能表只能用别名了,其他的不行
SELECT o.order_id, o.quantity, o.unit_price, o.quantity*o.unit_price AS 'total_price',p.product_id,p.`name`,p.unit_price
FROM order_items o
JOIN products p
	ON o.product_id = p.product_id;

-- join可以跨库查询
SELECT *
FROM order_items o
JOIN sql_inventory.products p
	ON o.product_id = p.product_id;

-- join可以连接同一个表
-- 此处右边为null的自动删除
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;

-- join 连接多个表

USE sql_store;

SELECT 
	o.order_id,
	o.order_date,
	c.first_name,
	c.last_name,
	os.`name` AS `status`
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.`status` = os.order_status_id;

-- 练习: join 连接多个表
USE sql_invoicing;

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

-- 连接带有复合主键的表
USE sql_store;

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