多表查询
多表查询 / 关联查询
1、指两个或更多个表一起完成查询操作
2、前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段
笛卡尔积 / 交叉连接 / CROSS JOIN
1、假设两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合
(1)第一个对象来自于 X,第二个对象来自于 Y 的所有可能,组合的个数即为两个集合中元素个数的乘积数
(2)任意表进行连接,即使这两张表不相关,在 MySQL 中也会出现笛卡尔积
2、产生条件
(1)省略多个表的连接条件(或关联条件)
(2)连接条件(或关联条件)无效
(3)所有表中的所有行互相连接
3、避免出现笛卡尔集:多表查询条件 >= 表的总个数 - 1
等值连接
1、多个连接条件与 AND 操作符
2、区分重复的列名:多个表中有相同列时,必须在列名之前加上表名前缀
3、表的别名
(1)使用别名可以简化查询
(2)列名前使用表名前缀可以提高查询效率
(3)使用了表的别名,在查询字段中、过滤条件中就只能使用别名进行代替,不能使用原有的表名,否则就会报错
(4)开发规范:对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定
4、连接多个表:连接 n 个表,至少需要 n-1 个连接条件
非等值连接
1、表与表之间没有相同项(或有相同但是不能用),但是表之间某个或某些项直接存在着一定的关系
2、在连接条件使用除等于运算符以外,其它比较运算符比较被连接的列的值
自连接
1、同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义
2、然后两个表再进行内连接,外连接等查询
内连接
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
1、合并具有同一列的两个以上的表的行
2、结果集中不包含一个表与另一个表不匹配的行
外连接
1、除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录
2、左(或右)外连接:两个表在连接过程中,除了返回满足连接条件的行以外,还返回左(或右)表中不满足条件的行
(1)没有匹配的行时,结果表中相应的列为 NULL
(2)如果是左外连接,则连接条件中左边的表称为主表,右边的表称为从表
(3)如果是右外连接,则连接条件中右边的表称为主表,左边的表称为从表
3、SQL92:使用 (+) 创建连接
(1)在 SQL92 中采用 (+) 代表从表所在的位置,即左或右外连接中,(+) 表示哪个是从表
(2)Oracle 支持 SQL92 外连接,而 MySQL 则不支持 SQL92 外连接
(3)在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接
4、SQL99 语法实现多表查询
(1)使用 JOIN...ON 子句创建连接的语法结构
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
(2)可以使用 ON 子句指定额外的连接条件
(3)这个连接条件是与其它条件分开的
(4)ON 子句使语句具有更高的易读性
(5)关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接
(6)LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在,只能用 (+) 表示。
5、左外连接
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
6、右外连接
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
7、满外连接
(1)满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
(2)SQL99 支持满外连接,使用 FULL JOIN 或 FULL OUTER JOIN 来实现
(3)MySQL 不支持 FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN代替
合并查询结果
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1、给出多条 SELECT 语句,并将它们的结果组合成单个结果集
(1)UNION:返回两个查询的结果集的并集,去除重复记录
(2)UNION ALL:返回两个查询的结果集的并集,对于两个结果集的重复部分,不去重
(3)执行 UNION ALL 语句时,所需要的资源比 UNION 语句少,若明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用 UNION ALL 语句,以提高数据查询的效率
2、合并时,两个表对应的列数和数据类型必须相同,并且相互对应
3、各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔
7 种 SQL JOINS 实现
1、内连接:A∩B
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
2、左外连接
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
3、右外连接
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
4、A - A∩B
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句;
5、B - A∩B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句;
6、满外连接:A∪B
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句
UNION
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
7、A∪B - A∩B 或 (A - A∩B)∪(B - A∩B)
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句
UNION
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句;
SQL99 语法新特性
1、NATURAL JOIN
(1)表示自然连接,等于 SQL92 中的 JOIN ON 等值连接
(2)自动查询两张连接表中所有相同的字段,然后进行等值连接
2、USING
(1)支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合 JOIN 一起使用
(2)与 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,需要在 USING 的 () 中填入要指定的同名字段
(3)同时使用 JOIN USING 可以简化 JOIN ON 的等值连接
SELECT table1.column, table2.column
FROM table1
JOIN table2
USING (table1 和 table2 的同名列)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战