MySQL 多表查询
- 1、内连接(Inner Joins)
- 2、跨数据库连接(Joining Across Databases)
- 3、自连接(Self Joins)
- 4、多表连接(Joining Multiple Tables)
- 5、复合连接条件(Compound Join Conditions)
- 6、隐式连接(Implicit Join Syntax)
- 7、外连接(Outer Joins)
- 8、多表外连接(Outer Join Between Multiple Tables)
- 9、自外连接(Self Outer Joins)
- 10、USING子句(The USING Clause)
- 11、自然连接(Natural Joins)
- 12、交叉连接(Cross Joins)
- 13、联合(Unions)
- 14、心得体会
1、内连接(Inner Joins)
SELECT last_name, first_name
FROM sql_store.customers
JOIN orders
ON customers.customer_id = orders.customer_id;
-- 还可以起别名,如下
SELECT last_name, first_name
FROM sql_store.customers c
JOIN orders o
ON c.customer_id = o.customer_id;
2、跨数据库连接(Joining Across Databases)
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id;
3、自连接(Self Joins)
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、多表连接(Joining Multiple Tables)
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
5、复合连接条件(Compound Join Conditions)
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
6、隐式连接(Implicit Join Syntax)
-- 显式连接
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、外连接(Outer Joins)
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
8、多表外连接(Outer Join Between Multiple Tables)
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
9、自外连接(Self Outer Joins)
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子句(The USING Clause)
SELECT
o.order_id,
c.first_name,
c.last_name,
sh.name AS shipper
FROM orders o
JOIN customers c
USING (customer_id)
LEFT JOIN shippers sh
USING (shipper_id)
11、自然连接(Natural Joins)
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
12、交叉连接(Cross Joins)
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
13、联合(Unions)
SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers
14、心得体会
大概回顾了一下sql中的一些基本内容,很久不怎么写了,有的忘了,有的还记着,在这里做一个笔记,忘记的话回来翻看一下好了!!!