第三章 1-内连接inner joins--Mosh mySQL

1.将数据库中的两个表合并在一起，注意主键要一致，因为暂时不涉及outner join ,所以这里的inner join也可以写成join

select *
from orders
inner join customers on orders.customer_id = customers.customer_id

2，order_id和对应的first_name,last_name：

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

3，Error Code: 1052. Column 'customer_id' in field list is ambiguous   

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

这是因为我们两张表都有customer_id,所以不知道从哪个表选择这个。因此提示列不明确，需要改为orders.customer_id或者customers.customer_id

4，让代码更简单，这里省略了as o,as c

用o表示orders,c表示customers

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表，和product表连接，每笔订单都返回product_id和order_id，连同orderitems里面的数量和单价。记得用别名简化代码

为什么用oi呢，因为产品可能价格变动，所以以订单为准

SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
join products p on oi.product_id =p.product_id