mysql 连接树 mysql 连接语句

INNER JOIN

内连接，inner可以省略，join默认为inner join

同一数据库中连接

 若需选中的某一列在两张表中都存在，则在列前需添加表前缀

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers ON orders.customer_id=customers.customer_id
-- 连接orders表和customers表，使orders表中的customers_id和orders表中的相同

为了简化可以给表取别名（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 *
FROM order_items oi
JOIN sql_inventory.products p 
	ON oi.product_id=p.product_id
--  当前使用的是sql_store数据库（图中加粗），若要连接sql_inwentory数据库中的表需要加数据库前缀

 若当前使用sql_inventory数据库

USE sql_inventory;

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

自连接

自连接和其它连接大致相同，但是在自连接中同个表要取不同的别名，且引用每一列前都需要加别名前缀。

USE sql_hr;

SELECT *
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id
-- 将员工表中被雇佣者和表中相应的管理者连接

USE sql_hr;

SELECT 
	e.employee_id,
    e.first_name,
    m.first_name manager
    
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id

多表连接

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

 复合条件连接

有两个以上主键为复合主键，需要通过两列以上确定行

当表中有复合主键时，和其它表相连为复合连接

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

隐式写法如下： 

SELECT *
FROM orders o, customers c
WHERE o.customer_id =c.customer_id

注意：谨慎使用，因为若忘写where会得到交叉连接 

OUTER JOIN

left outer join/ right outer join，其中outer可有可无

LEFT JOIN／RIGHT JOIN

左（右）连接：左（右）边表中所有行的选中列都会返回，不管有没有满足ON后面的条件

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id =o.customer_id
ORDER BY c.customer_id

 

多表外连接

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name
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_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
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

两个表中如果有相同名称的列可以用 JOIN...USING...来替代 JOIN...ON...

SELECT 
    c.first_name,
    o.order_id,
    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)

 复合条件连接改用using

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)

NATURAL JOIN

自然连接

不用具体列名，数据库引擎会基于共同的列连接自己看着办

SELECT *
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

UNION

联合
可以合并多个查询结果，注意两个结果返回的列数要相同，且最终返回结果以第一个返回结果的列名为最终列名

SELECT 
	order_id,
    order_date,
    'Active' AS status
FROM orders o
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'Archived' AS status
FROM orders o
WHERE order_date < '2019-01-01'