mosh数据库——第三章

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

1. join可以把两个表格链接在一起（按顺序）
2. select 可以选择新生成表格的所有列（A/B里的都ok）
3. A join B  把B添加在A后面
4. 空格后面可以添加别名
5. 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.自连接

将表和自己连接和将表与其他表连接大致相同，区别：

1. 要使用不同的别名
2. 还要给选择的每列前增加别名

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的所有数据），列名是基于第一段查询