SQL----chapter3:retrieving data from multiple tables

1.内连接

use sql_store;

select order_id, o.customer_id, first_name, last_name
from orders o
join customers c on o.customer_id = c.customer_id

use sql_store;

select *
from order_items oi
join products p on oi.product_id = p.product_id

2.跨数据库连接（合并）

use sql_store;

select *
from order_items oi
join sql_inventory.products p on oi.product_id = p.product_id

或

use sql_inventory;

select *
from sql_store.order_items oi
join products p on oi.product_id = p.product_id

3.自连接

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

4.多表连接

from A
join B on AB的关系
join C on AC的关系
join D on AD的关系

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.invoice_id, p.date, p.amount, c.name, pm.name as payment_method
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

 5.复合连接条件

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

 6.隐含连接语法

--用 from where 取代 from join on 

use sql_store;

select *
from orders o
join customers c on o.customer_id = c.customer_id

即

select *
from orders o, customers c
where o.customer_id = c.customer_id

7.外连接

use sql_store;

select c.customoer_id, c.first_name, o.order_id
from customers c
join orders o on o.customer_id = c.customer_id
order by 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

8.多表外连接

use sql_store;

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.chipper_id = sh.shipper_id
order by customer_id

use sql_store;

select o.order_id, o.order_date, c.first_name as customer, 
       sh.name as shipper, os.name as status
from orders o
join customers c on o.customer_id = c.customer_id
left join shippers sh on o.shipper_id = sh.shipper_id
join order_statuses os on o.status = os.order_status_id

9.自我外部连接

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

10.using 子句

当作为合并条件 join condition 的列在两个表中有相同列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简 化，内/外连接均可如此简化。

using 后面接 ( )，易忘

select o.order_id, c.first_name, sh.name as shipper
from orders o 
join customer c using (customer_id)
left join shippers sh using (shipper_id)
order by order_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, pm.name as payment_method, p.amount
from payment p
join clients c using (client_id)
join payment_methods pm on p.payment_method = pm.payment_method_id

11.自然连接

最好不用

use sql_store;

select o.order_id, c.first_name
from orders o
natural join customers c

12.交叉连接

use sql_store;

select c.first_name as customer, p.name as product
from customers c
cross join products p
order by c.first_name

---显式语法


use sql_store;

select c.first_name, p.name
from customers c, products p
order by c.first_name

---隐式语法
---其实就是隐式内合并忽略 where 子句（即合并条件）的情况，也就是把 cross join 改为逗号，即 from A cross join B 等效于 from A, B，Mosh 更推荐显式语法，因为更清晰

use sql_store;

select sh.name as shippers, p.name as product
from shippers sh
cross join products p
order by sh.name

or

select sh.name as shippers, p.name as product
from shippers sh, products p
order by sh.name

13.联合

FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表。

• 同一张表可通过UNION添加新的分类字段，即先通过分类查询并添加新的分类字段再UNION合并为带分类字段 的新表。
• 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示。
• 注意：合并的查询结果必须列数相等，否则会报错；合并表里的列名由排在UNION前面的决定

---给订单表增加一个新字段——status，用以区分今年的和以前的订单

use sql_store;

select order_id, order_date, 'Active' as status
from orders
where order_date >= '2019-01-01'
union
select order_id, order_date, 'Archived' as status
from orders
where order_date < '2019-01-01'

---合并不同表的例子：同一列表里显示所有顾客以及商品名

use sql_store;

select first_name as name_of_all ---新列名由排union前面的决定
from customers
union
select name 
from products

---给顾客按积分大小分类，添加新字段type，并按顾客id排序

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 customer_id

总结:

• 感觉本质上可以将查询语句的任何一步和任何一个层次，包括（按实际执行顺序排列）：
• 1. 选取表 FROM ……
• 2. 横向连接合并 …… JOIN ……
• 3. 纵向筛选 WHERE ……
• 4. 横向筛选 SELECT ……
• 5. 纵向连接合并 …… UNION ……
• 6. 排序、限制，ORDER BY …… LIMIT ……