02 MySQL连接操作

在应用中查询往往设计到多个表；连接就实现了多表查询

连接操作在前面原理部分中提到了连接操作有好多种：

• 等值连接
• 非等值连接查询
• 外连接（左，右
• 自身连接

等值连接与非等值连接

表名1.列名 比较运算符 表名2.列名

运算符： > < = >= <= != <> BETWEEN...AND

=就是等值连接

SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno

其他的就是非等值

内连接

内连接分为隐式内连接和显示外连接

显示内连接

SELECT 目标列列表 FROM 表1 INNER JOIN 表2 ON 表1.连接列=表2.连接列 WHERE 查询条件;
ON后面表示的相当于是连接操作基于什么条件来做的（基于条件）

SELECT 目标列列表 FROM 表1 INNER JOIN 表2 ON 表1.连接列=表2.连接列 INNER JOIN 表2 ON 表3.连接列=表3.连接列 WHERE 查询条件;

SELECT order_id, first_name, last_name 
FROM orders INNER JOIN customers
ON orders.customer_id = customers.customer_id;

注意，如果进行操作的两个表都有的某一个属性，需要对改列加上.的前缀来唯一标识

SELECT order_id, first_name, last_name, customer_id -- 这里会报错
FROM orders INNER JOIN customers
ON orders.customer_id = customers.customer_id;

解决方法：

SELECT order_id, first_name, last_name, orders.customer_id
FROM orders INNER JOIN customers
ON orders.customer_id = customers.customer_id;

也可以使用别名方便操作：

SELECT order_id, first_name, last_name, o.customer_id
FROM orders o INNER JOIN customers c
ON o.customer_id = c.customer_id;

（赋予简称后，必选全部使用简称，否则会报错）

隐式内连接

就是直接选取两个表后采用where来进行判断来连接

自身连接

join

一个表对自身进行连接

例如在一个课程关系表中，有一列是课程号，还有一列是先修关系，那么就可以通过对这两列进行连接后得到每门课程的间接先修课

select * from employees e
join employees m
on e.reports_to = m.employee_id -- 查询领导关系

多表连接

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

外连接

左外连接、右外连接、全外连接

左外连接

以左表为基准；让左边表中的所有元组都出现在结果表中，若右边表中没有与之相匹配的元组，则结果元组中右表部分的字段值为NULL

LEFT JOIN

SELECT 目标列列表 FROM 表1 LEFT JOIN 表2 ON 表1.连接列 = 表2.连接列

select p.product_id, name, quantity
from products p
left join order_items oi
on p.product_id = oi.product_id

右外连接

右外连接也是类似的；只不过把左外连接的定义稍微变换一下

以右表为基准；让右边表中的所有元组都出现在结果表中，若左边表中没有与之相匹配的元组，则结果元组中左表部分的字段值为NULL

RIGHT JOIN

SELECT 目标列列表 FROM 表1 RIGHT JOIN 表2 ON 表1.连接列 = 表2.连接列

理解左外右外连接需要结合本身这句话的语义来理解

右外连接表2，就是表2不动，表1从左往右连接

同理理解左外连接就是看join两边的 表，谁在左谁就是左表，右边的表从右往左缝合

全外连接

FULL JOIN

就是上面的两个定义互相掺杂

SELECT 目标列列表 FROM 表1 FULL JOIN 表2 ON 表1.连接列 = 表2.连接列

多表外连接

在一个表连接on后面继续跟left join on

select 
	ord.order_id,
	ord.order_date,
    cs.first_name,
    sh.name,
	order_statuses.name as status
from
	orders ord
join
	customers cs
on
	ord.customer_id = cs.customer_id
left join
	shippers sh
on
	ord.shipper_id = sh.shipper_id
left join
	order_statuses
on
	ord.status = order_statuses.order_status_id

自外连接

同样的外连接也可以对自身进行连接操作

USING语句

在连接过程中，如果连接的两个表中有相同名称的列，可以使用USING对其进行等价变换

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

-- 等价于

select
	o.order_id,
	c.first_name
from
	orders o
join
	custmoers c
using (customer_id)

use sql_invoicing;
select
	p.date as date,
    c.name,
	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;

跨数据库连接

与表之间连接类似，只需要在选择表的时候在前面再加上一个.表名来源数据库就可以了

加不加前缀取决于当前use了哪个数据库，当前的数据库就不需要加

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id

交叉连接

笛卡尔积

cross join