MySQL基础笔记 二

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中的一些基本内容，很久不怎么写了，有的忘了，有的还记着，在这里做一个笔记，忘记的话回来翻看一下好了！！！