多个表中检索数据

INNER JOINS

-- orders保存的是不会经常变动的
-- customers保存的是每位顾客，很多属性会经常变动
-- 两个表连接起来
SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
	
-- 商品表查询:这个表中的列是订单编号,商品名称,数量和单价
SELECT order_id,p.`name`,quantity,o.unit_price
FROM order_items o
JOIN products p
	ON o.product_id = p.product_id

Joining Across Databases（跨库合并表）

-- 需要在不同库的表前面前置库名
SELECT *
FROM order_items o
JOIN sql_inventory.products p
	ON o.product_id = p.product_id

Self Joins（自联结）

use sql_hr;

-- employees表里面，既有管理者的id，也有雇员的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

Joining Multiple Table

-- 订单id、订单日期、客户的名字和姓、订单状态
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

-- 支付方式信息和客户名字	
SELECT 
	p.date AS payment_data,
	c.`name` AS client_name,
	pm.`name`	AS payment_methods
FROM payments p		-- payments 发票表 
JOIN payment_methods pm		-- payment_methods 发票方式
	ON p.payment_method = pm.payment_method_id
JOIN clients c
	ON p.client_id = c.client_id

Compound Join Conditions（复合合并的条件）

SELECT *
FROM order_items oi
JOIN order_item_notes oin		-- 订单备注:2个主键
	ON oi.order_id = oin.order_Id
	AND oi.product_id = oin.product_id

Implicit Join Syntax（隐式合并语法：不建议）

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
	
-- 跟上面相同(当忘记where语句时会出现语法错误)
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_id

Outer Joins（外连接）

SELECT 
	c.customer_id,
	c.first_name,
	o.order_id
FROM orders o
RIGHT JOIN customers c		-- 不管客户有没有订单都将其查出
	ON o.customer_id = c.customer_id
ORDER BY o.customer_id

-- 查询显示:产品id(无论有没有数量都显示出来)，产品名，数量
SELECT 
	p.product_id,
	p.`name`,
	oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id

Outer Joins Between Multiple Tables（多表间外连接）

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

Self Outer Joins（表的自我外连接）

SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS manage
FROM employees e
LEFT JOIN employees m		-- 如果用内连接会查不到CEO(即manager为空的情况)
	ON e.reports_to = m.employee_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DUmkv9UK-1589030409867)(D:\笔记\数据库\Mosh_数据库.assets\image-20200506091159244.png)]

The USING Clause（USING子句）

-- 适用于相同名字字段的表
SELECT 
	o.order_date,
	o.order_id,
	c.first_name,
	s.`name` AS shipper,
	os.`name` AS `status`
FROM orders o
JOIN customers c
-- 	ON o.customer_id = c.customer_id
	USING (customer_id)
LEFT JOIN shippers s
-- 	ON o.shipper_id = s.shipper_id
	USING (shipper_id)
JOIN order_statuses os
	ON o.`status` = os.order_status_id


-- 复合合并的使用
SELECT *
FROM order_items oi
JOIN order_item_notes oin		-- 订单备注:2个主键
	-- ON oi.order_id = oin.order_Id
	-- AND oi.product_id = oin.product_id
	USING (order_id,product_id)
	
-- 订单表
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

Cross Joins（交叉合并）

SELECT 
	s.`name`,
	p.`name` AS product,
	p.unit_price
FROM shippers s, products p
ORDER BY s.shipper_id

-- 等同于

SELECT 
	s.`name`,
	p.`name` AS product,
	p.unit_price
FROM shippers s
CROSS JOIN products p
ORDER BY s.shipper_id

Unions（联合查询：增加表类型）

-- 增加表类型
-- 日期是否小于2019年，如果是则归档不查出
SELECT 
	order_id,
	order_date,
	'Active' AS `status`
FROM orders
WHERE order_date >= '2019-01-01'
-- 连接另一个表:列数和类型一样
UNION
SELECT 
	order_id,
	order_date,
	'Active' AS `status`
FROM orders
WHERE order_date < '2019-01-01'

-- 列名以第一个查询的为主
SELECT first_name AS full_name
FROM customers
UNION
SELECT name
FROM shippers


-- 查询各种所属的类型
SELECT 
	customer_id,
	first_name,
	points,
	'Gold' AS type
FROM customers c
WHERE points > 3000
UNION
SELECT 
	customer_id,
	first_name,
	points,
	'Silver' AS type
FROM customers c
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT 
	customer_id,
	first_name,
	points,
	'Bronze' AS type
FROM customers c
WHERE points BETWEEN 100 AND 2000
ORDER BY first_name