MySQL数据库,深入了解连接查询及原理(一) | 您所在的位置:网站首页 › 数据库sql连接查询 › MySQL数据库,深入了解连接查询及原理(一) |
当我们查询的数据来源于多张表的时候,我们需要⽤到连接查询,连接查询使⽤率⾮常⾼,是学习数据库必须掌握的。 内容提要: 1. 笛卡尔积 2. 内连接 这是Mysql系列第11篇。 环境:mysql5.7.25,cmd命令中进⾏演⽰。 3. 外连接 4. 左连接 5. 右连接 6. 表连接的原理 7. 使⽤java实现连接查询,加深理解 准备数据 2张表: t_team:组表。 temployee:员⼯表,内部有个teamid引⽤组表的id。 drop table if exists t_team; create table t_team( id int not null AUTO_INCREMENT PRIMARY KEY comment '组id', team_name varchar(32) not null default '' comment '名称' ) comment '组表'; drop table if exists t_employee; create table t_employee( id int not null AUTO_INCREMENT PRIMARY KEY comment '部门id', emp_name varchar(32) not null default '' comment '员⼯名称', team_id int not null default 0 comment '员⼯所在组id' ) comment '员⼯表表'; insert into t_team values (1,'架构组'),(2,'测试组'),(3,'java组'),(4,'前端 组'); insert into t_employee values (1,'路⼈甲Java',1),(2,'张三',2),(3,'李 四',3),(4,'王五',0),(5,'赵六',0); t_team表4条记录,如下:mysql> select * from t_team; +----+-----------+ | id | team_name | +----+-----------+ | 1 | 架构组 | | 2 | 测试组 | | 3 | java组 | | 4 | 前端组 | +----+-----------+ 4 rows in set (0.00 sec) t_employee表5条记录,如下: mysql> select * from t_employee; +----+---------------+---------+ | id | emp_name | team_id | +----+---------------+---------+ | 1 | 路⼈甲Java | 1 | | 2 | 张三 | 2 | | 3 | 李四 | 3 | | 4 | 王五 | 0 | | 5 | 赵六 | 0 | +----+---------------+---------+ 5 rows in set (0.00 sec) 笛卡尔积 介绍连接查询之前,我们需要先了解⼀下笛卡尔积。 笛卡尔积简单点理解:有两个集合A和B,笛卡尔积表⽰A集合中的元素和B集合中的元素 任意相互关联产⽣的所有可能的结果。 假如A中有m个元素,B中有n个元素,A、B笛卡尔积产⽣的结果有m*n个结果,相当于循 环遍历两个集合中的元素,任意组合。 java伪代码表⽰如下:for(Object eleA : A){ for(Object eleB : B){ System.out.print(eleA+","+eleB); } } 过程:拿A集合中的第1⾏,去匹配集合B中所有的⾏,然后再拿集合A中的第2 ⾏,去匹配集合B中所有的⾏,最后结果数量为m*n。 sql中笛卡尔积语法 select 字段 from 表1,表2[,表N]; 或者 select 字段 from 表1 join 表2 [join 表N]; ⽰例: mysql> select * from t_team,t_employee; +----+-----------+----+---------------+---------+ | id | team_name | id | emp_name | team_id | +----+-----------+----+---------------+---------+ | 1 | 架构组 | 1 | 路⼈甲Java | 1 | | 2 | 测试组 | 1 | 路⼈甲Java | 1 | | 3 | java组 | 1 | 路⼈甲Java | 1 | | 4 | 前端组 | 1 | 路⼈甲Java | 1 | | 1 | 架构组 | 2 | 张三 | 2 | | 2 | 测试组 | 2 | 张三 | 2 | | 3 | java组 | 2 | 张三 | 2 | | 4 | 前端组 | 2 | 张三 | 2 | | 1 | 架构组 | 3 | 李四 | 3 | | 2 | 测试组 | 3 | 李四 | 3 | | 3 | java组 | 3 | 李四 | 3 | | 4 | 前端组 | 3 | 李四 | 3 | | 1 | 架构组 | 4 | 王五 | 0 | | 2 | 测试组 | 4 | 王五 | 0 | | 3 | java组 | 4 | 王五 | 0 | | 4 | 前端组 | 4 | 王五 | 0 | | 1 | 架构组 | 5 | 赵六 | 0 || 2 | 测试组 | 5 | 赵六 | 0 | | 3 | java组 | 5 | 赵六 | 0 | | 4 | 前端组 | 5 | 赵六 | 0 | +----+-----------+----+---------------+---------+ 20 rows in set (0.00 sec) tteam表4条记录,temployee表5条记录,笛卡尔积结果输出了20⾏记录。 内连接 语法: select 字段 from 表1 inner join 表2 on 连接条件; 或 select 字段 from 表1 join 表2 on 连接条件; 或 select 字段 from 表1, 表2 [where 关联条件]; 内连接相当于在笛卡尔积的基础上加上了连接的条件。 当没有连接条件的时候,内连接上升为笛卡尔积。 过程⽤java伪代码如下: for(Object eleA : A){ for(Object eleB : B){ if(连接条件是否为true){ System.out.print(eleA+","+eleB); } } } 示例1:有连接条件 查询员⼯及所属部门 mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id;+---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | | 张三 | 测试组 | | 李四 | java组 | +---------------+-----------+ 3 rows in set (0.00 sec) mysql> select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id; +---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | | 张三 | 测试组 | | 李四 | java组 | +---------------+-----------+ 3 rows in set (0.00 sec) mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id; +---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | | 张三 | 测试组 | | 李四 | java组 | +---------------+-----------+ 3 rows in set (0.00 sec) 上⾯相当于获取了2个表的交集,查询出了两个表都有的数据。 示例2:⽆连接条件 ⽆条件内连接,上升为笛卡尔积,如下: mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2; +---------------+-----------+| emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | | 路⼈甲Java | 测试组 | | 路⼈甲Java | java组 | | 路⼈甲Java | 前端组 | | 张三 | 架构组 | | 张三 | 测试组 | | 张三 | java组 | | 张三 | 前端组 | | 李四 | 架构组 | | 李四 | 测试组 | | 李四 | java组 | | 李四 | 前端组 | | 王五 | 架构组 | | 王五 | 测试组 | | 王五 | java组 | | 王五 | 前端组 | | 赵六 | 架构组 | | 赵六 | 测试组 | | 赵六 | java组 | | 赵六 | 前端组 | +---------------+-----------+ 20 rows in set (0.00 sec) 示例3:组合条件进⾏查询 查询架构组的员⼯,3种写法 mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组'; +---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | +---------------+-----------+ 1 row in set (0.00 sec)mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组'; +---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | +---------------+-----------+ 1 row in set (0.00 sec) mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id and t2.team_name = '架构组'; +---------------+-----------+ | emp_name | team_name | +---------------+-----------+ | 路⼈甲Java | 架构组 | +---------------+-----------+ 1 row in set (0.00 sec) 上⾯3中⽅式解说。 ⽅式1:on中使⽤了组合条件。 ⽅式2:在连接的结果之后再进⾏过滤,相当于先获取连接的结果,然后使⽤where中的 条件再对连接结果进⾏过滤。 ⽅式3:直接在where后⾯进⾏过滤。 总结 内连接建议使⽤第3种语法,简洁: select 字段 from 表1, 表2 [where 关联条件]; |
今日新闻 |
推荐新闻 |
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 |