1.内连接|INNER JOIN
连接分为两种:内连接和外连接
SELECT order_id , O.customer_id, first_name , last_name
FROM ORDERS O
JOIN customers C
ON O.customer_id = C.customer_id
- join可以把两个表格链接在一起(按顺序)
- select 可以选择新生成表格的所有列(A/B里的都ok)
- A join B 把B添加在A后面
- 空格后面可以添加别名
- O.oooo 表示O表格里的oooo列,如果列名重复需添加表格前缀说明特定列
2.跨数据库连接
- 需要给不在当前数据库的表加前缀
- !!注意现在选择的是哪个数据库(USE XXXXX;)
我们写的查询语句会根据当前数据库的变化而变化(以下两个运行结果相同)
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p
ON oi.product_id = p.product_id
USE sql_store
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
3.自连接
将表和自己连接和将表与其他表连接大致相同,区别:
- 要使用不同的别名
- 还要给选择的每列前增加别名
USE sql_hr;
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
-- e里面听汇报的人是他们的管理者
4.多表连接
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
order by order_id
- 用多个join on 串在一起
- select选择的列名重复的要加前缀
5.复合连接条件
存在我们无法用单一列来识别某张表里列的情况
打开设计模式,这个表中黄颜色的表示主键,超过一个可以称为复合主键
使用多个条件来连接两个表格【用AND连接】 A JION B ON a AND b
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
6.隐式连接语法
尽量使用显式连接语法,因为这个(显式)语法会让你不得不输入连接条件
SELECT*
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
-- Implpicit Join Syntax
SELECT*
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
一定要记得打WHERE,否则订单中的每条记录和顾客表里的每条记录都会链接,这就是“交叉”
7.外连接
用 JOIN ON 时只返回符合这个连接条件的记录
某些数据连接条件没有被满足,所以不会返回
SELECT
c.customer_id,
first_name,
order_id
FROM customers c
JOIN corders o
ON o.customer_id = c.customer_id -- 其他的那些顾客没有订单所以显示的结果没有他们
ORDER BY c.customer_id
使用左连接|LEFT JOIN
SELECT
c.customer_id,
first_name,
order_id
FROM customers c
LEFT JOIN orders o -- 左表显示全
ON o.customer_id = c.customer_id
ORDER BY c.customer_id
使用右连接|RIGHT JOIN
SELECT
c.customer_id,
first_name,
order_id
FROM customers c
RIGHT JOIN orders o
ON o.customer_id=c.customer_id -- 会返回右边的所有记录
ORDER BY c.customer_id
也可以直接换表的顺序来切换
8.多表外连接
最好避免使用右连接,使表达混乱
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.自外连接
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
与3.3对比记忆
10.USING语句
两个表中都有名字相同的列时,
用USING代替ON 加上后面相同的标签【USING (列名)】
-- ON o.customer_id = c.customer_id
USING (customer_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 order_items oi JOIN order_item_notes oin USING (order_id, product_id)
11.自然连接|NATURAL JOIN
NATURAL JOIN 数据库引擎会自己看着办,基于共同的列连接(相同的列名)
很危险,因为我们让数据库引擎自己猜该怎么连接 我们无法控制它,出于这种原因,自然连接可以生成意外的结果,所以不推荐使用
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
12.交叉连接|CROSS JOIN
显式语法,将CROSS JOIN 写出来(推荐使用)
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
隐式语法,from 后面将需要交叉连接的表写出来(用逗号连接)
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c, orders o
ORDER BY c.first_name
+WHERE之后不是交叉连接
13.联合|UNION
UNION可以合并多段查询的记录
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'
也可以基于不同的表格写查询,并将结果合并到同一个结果集(现实中没有什么作用)
SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers
结果只返回一列(f 和 n的所有数据),列名是基于第一段查询