文章目录
- 1. 内连接
- 2. 跨数据库连接
- 3. 自连接
- 4. 多表连接
- 5. 复合连接条件
- 6. 隐式连接语法
- 7. 外连接
- 8. 多表外连接
- 9. 自外连接
- 10. USING子句
- 11. 自然连接
- 12. 交叉连接
- 13. 联合
多表合并,依据主表和根据主表和连接表的属性相关性来生成新的关系表格。(可重复的并集)
1. 内连接
各表分开存放是为了保证最下实体单元内的属性联系更加紧密,但是优势需要将相互之间的关系连接,从而满足多表间的查询要求。如果两个关系之间有相同的属性,则可以用内连接将两个表合并。
虽然SELECT语句还是放在前面,但是我们写语句时从后往前看,假定已经将两个关系合并过了来操作SELECT语句。
NOTE:
- AS 也可以用来设置简化的别名,同时AS可省略。一旦用别名,所有地方都需要使用别名。
- 选择关系中相同的属性列时,必须指定关系表名前缀,不然会报错。良好的编程习惯是:明确属性不加前缀,不明确前加表名前缀。
- 编写SELECT的列时,可以先用*,确定好别名和需要选择的属性列后,再修改SELECT中选定的列名。
- 合并完后的表格,一般需要设置排序规则。
SELECT o.order_id, c.customer_id, c.first_name, c.last_name
-- 列前尽量加上表名前缀
FROM orders o -- 创建别名,省略AS
JOIN customers c
ON o.customer_id = c.customer_id
ORDER BY o.order_id ASC
2. 跨数据库连接
只需要在不属于当前数据库的表前面加上数据库名前缀即可。
USE关键字可以将操作转移到某个数据库,而.字符可以跨数据库访问表
USE sql_store;
SELECT *
FROM order_items oi
JOIN sql_inventory.products sp
ON oi.product_id = sp.product_id
3. 自连接
自连接和内连接的区别在于,内连接用的是两个不同的表,自连接用的是相同的表(表内属性列有相同属性数值),然后取不同的别名。都是采用相同的属性值连接起来。
USE sql_hr;
SELECT e.employee_id, e.first_name employee, m.first_name manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
4. 多表连接
一个主表采用FROM xxx 后面重复使用JOIN table ON attribute语句。
USE sql_store;
SELECT o.order_id, o.order_date, c.first_name, os.name
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 o.order_id
NOTE:选择合适的列输出,保证输出的数据不凌乱(编程时再处理,先是全部选取*)。
5. 复合连接条件
软件中属性码前黄色闪电表示为主码(候选码),复合主键
对于复合主键,即候选码,需要采用所有的候选码的属性来进行连接两个表格。
USE sql_store;
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)*/
6. 隐式连接语法
用FROM WHRER 来替代 FROM JOIN ON,但是尽量不要用隐式连接。因为如果忘记用关键词WHERE将产生“交叉连接”,两个10条记录的表格将产生100条记录新表格。
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
/*JOIN customers c
ON o.customer_id = c.customer_id*/
7. 外连接
- (INNER) JOIN 结果只包含两表的交集,注意“广播(broadcast)”效应,根据多列出的有重复的属性记录数值会进行扩充
- LEFT/RIGHT (OUTER) JOIN 结果里除了交集,还包含只出现在左/右表中的记录.左连接则包含主表全部信息,右连接则包含连接的表全部信息,没有连接的属性数值则用NULL进行填充。尽量少使用右连接RIGHT JOIN
- 连接过程涉及记录数据互补的问题
SELECT o.order_id, o.order_date, c.customer_id, c.first_name
FROM orders o
RIGHT JOIN customers c
ON o.customer_id = c.customer_id
8. 多表外连接
与内连接的多表连接类似,尽量不用RIGHT JOIN;
内连接和外连接可以混合使用。
SELECT o.order_date, order_id, c.first_name customer, s.name shipper, os.name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
LEFT JOIN shippers s -- 外连接的作用域大于内连接
ON o.shipper_id = s.shipper_id
JOIN order_statuses os -- 只要相关联,以多的数据为准
-- 内连接中,不同表中有没有关联的记录则不表示出来
ON o.status = os.order_status_id
9. 自外连接
自连接和外连接的综合使用
USE sql_hr;
SELECT e.employee_id, e.first_name employee, m.first_name manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to = m.employee_id
-- 根据主表条件来确定连接表的记录数值内容
10. USING子句
当作为合并条件 join condition 的列在两个表中有相同列名时,可用 USING (……, ……) 取代 ON …… AND …… 予以简化,内/外连接均可如此简化。(复合连接即候选码,则USING的(内部采用逗号隔开即可))
NOTE:
1)USING对复合主键的简化效果更加明显;
2)相同属性对应的不同列名必须采用ON…
USE sql_store;
SELECT o.order_id, c.first_name customer, s.name shipper
FROM orders o
JOIN customers c
-- ON o.customer_id = c.customer_id
USING (customer_id)
LEFT JOIN shippers s
USING (shipper_id)
11. 自然连接
NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。但是不建议使用,因为有时会产生意想不到的不可控的结果。
USE sql_store;
SELECT o.order_id, c.first_name customer
FROM orders o
NATURAL JOIN customers c
12. 交叉连接
CROSS JOIN得到两个表记录排列组合(两个表中的记录两两相互连接)后的所有记录情况,不需要合并条件(任何两个表都可以交叉连接,但是内连接和外连接不是任意两个表就可以连接的)。内连接和外连接产生的结果为交叉连接产生的结果的子集。
NOTE:隐式内合并忽略 WHERE 子句(即合并条件)的情况,交叉连接的隐式表达也就是把 CROSS JOIN 改为逗号,即 FROM A CROSS JOIN B 等效于 FROM A, B,更推荐显式语法,因为更清晰
USE sql_store;
SELECT o.order_id, c.first_name customer
FROM orders o
CROSS JOIN customers c
-- FROM orders o, customers c
ORDER BY o.order_id
13. 联合
FROM …… JOIN …… 可对多张表进行横向列合并,而 …… UNION …… 可用来按行纵向合并多个查询结果,这些查询结果可能来自相同或不同的表.
- 同一张表可通过UNION添加新的分类字段,即先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。
- 不同表通过UNION合并的情况如:将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示.
NOTE:
1)合并的查询结果必须列数相等,否则会报错;
2)合并表里的列名由排在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