Mosh教程SQL：重学SQL2

内连接：

# 在使用内连接时可以省略 inner
SELECT `order_id`,`first_name`,`last_name`
FROM `orders`
JOIN `customers`
	ON `orders`.`customer_id`=`customers`.`customer_id`;

# 在FROM中也可以起别名,可省略AS
# 如果多个表有相同的列名，请带上表名.
SELECT `order_id`,o.`customer_id`,`first_name`,`last_name`
FROM `orders` o
JOIN `customers` c
	ON o.`customer_id`=c.`customer_id`;
	
# 练习：
SELECT p.`product_id`,p.`name`,o.`quantity`,o.`unit_price`
FROM `products` p
JOIN `order_items` o
	ON p.`product_id`=o.`product_id`

跨数据库连接：

# 当你需要使用其他数据库表时，请加上其他数据库名称.
USE `sql_store`;
SELECT *
FROM `order_items` o
JOIN `sql_inventory`.`products` p
	ON o.`product_id`=p.`product_id`;

自连接：

# 自己连接自己
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`;
	
# 练习：
USE `sql_invoicing`;

SELECT 
	p.`date`,
	p.`invoice_id`,
	p.`amount`,
	c.`name`,
	pm.name
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`;

复合连接：

# 复合连接在于条件的复合
SELECT *
FROM `order_items` oi 
JOIN `order_item_notes` oin
	ON oi.`order_id` = oin.`order_Id`
	AND oi.`product_id` = oin.`product_id`;

隐式连接：

# 隐式连接（原写法）
SELECT *
FROM `orders` o
JOIN `customers` c
	ON o.`customer_id` = c.`customer_id`;
	
# 隐式连接是指MYSQL特有的语法如下：(不推荐)
SELECT *
FROM `orders` o ,`customers` c
WHERE o.`customer_id` = c.`customer_id`;

外连接：

# 内连接写法：
SELECT 
	c.`customer_id`,
	c.`first_name`,
	o.`order_id`
FROM `customers` c
JOIN `orders` o
	ON c.`customer_id` = o.`customer_id`
ORDER BY c.`customer_id`;

# 外连接分为：左连接LEFT 右链接RIGHT 
# 左连接则以左边为主表全部显示，右边按照条件显示
# OUTER是可以省略的，左连接全写为：LEFT OUTER JOIN
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 c.`customer_id`;

# 练习：
SELECT 
	p.`product_id`,
	p.`name`,
	oi.`quantity`
FROM `products` p
LEFT JOIN `order_items` oi
	ON p.`product_id` = oi.`product_id`
ORDER BY p.`product_id`;

多表外连接：

# 客户查订单，订单查发货
# 友情提示：多表连接尽量使用左连接
SELECT 
	c.`customer_id`,
	c.`first_name`,
	o.`order_id`,
	sh.`name` AS shipper
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`
ORDER BY c.`customer_id`;

# 练习：
SELECT 
	o.`order_date`,
	o.`order_id`,
	c.`first_name`,
	sh.`name`,
	os.`name`
FROM `orders` o
LEFT JOIN `customers` c
	ON o.`customer_id` = c.`customer_id`
LEFT JOIN `shippers` sh 
	ON o.`shipper_id` = sh.`shipper_id`
LEFT JOIN `order_statuses` os
	ON o.`status` = os.`order_status_id`;

自外连接：

# 自外连接 = 自连接 + 外连接
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`;

USING子句：

# USING子句能代替ON语句简化写法
SELECT 
	o.`order_id`,
	c.`first_name`,
	sh.`name` AS shipper
FROM `orders` o
JOIN `customers` c
	# ON o.`customer_id` = c.`customer_id`;
	USING (customer_id)
LEFT JOIN `shippers` sh
	USING (shipper_id);
	
# 复合连接也可以简化写法
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)
	
# 练习：
USE `sql_invoicing`;

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`;

自然连接：

# NATURAL JOIN  自然连接会自动去找相同的列(不推荐)
SELECT 
	o.`order_id`,
	c.`first_name`
FROM `orders` o
NATURAL JOIN `customers` c;

交叉连接：

-- CROSS JOIN交叉连接
# 貌似这种注释才是正确的
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`;

# 练习：
SELECT 
	sh.`name` AS shipper,
	p.`name` AS product
FROM `products` p
CROSS JOIN `shippers` sh
ORDER BY sh.`name`;

SELECT 
	sh.`name` AS shipper,
	p.`name` AS product
FROM `products` p,`shippers` sh
ORDER BY sh.`name`;

UNION联合：

# UNION联合可以使得多个结果合并到一起
SELECT 
	o.`order_id`,
	o.`order_date`,
	'Active' AS STATUS
FROM `orders` o
WHERE `order_date` >='2019-01-01'
UNION
SELECT 
	o.`order_id`,
	o.`order_date`,
	'Archived' AS STATUS
FROM `orders` o
WHERE `order_date` <'2019-01-01';

# 他也可以联合不同的列到一起
SELECT `first_name`
FROM `customers`
UNION
SELECT NAME
FROM `shippers`;

# 练习：
# 一万头曹尼玛 最后一行用c.`first_name`就不行，离谱
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` BETWEEN 2000 AND 3000
UNION
SELECT 
	c.`customer_id`,
	c.`first_name`,
	c.`points`,
	'Gold' AS TYPE
FROM `customers` c
WHERE c.`points` > 3000
ORDER BY `first_name`;