【MySql 学习笔记】MySql连接盘点

MySql连接盘点

等值连接、非等值连接

• 等值连接

  • 判断条件是 以 ’ = ’ 判断

  • 举个例子

    emp 员工表
    dep 部门表
    
    //查询员工所对应的部门的整体信息
    select * form emp dep 
    	where emp.depid = dep.id;
    ...
    判断条件为 'emp.depid = dep.id' 以此类推
    
    

• 非等值连接

  • 判断条件是 不以 ’ = ’ 判断

  • 举个例子

    emp 员工表
    
    //查询满足工资范围的员工信息
    select * form emp where emp.salary >= 5000
    select * form emp where emp.salary < 5000
    select * form emp where emp.salary != 5000
    select * form emp where emp.salary between 2000 and 6000
    
    
    ...
    判断条件 '不为相等的' 以此类推
    
    

自连接、非自连接

• 自连接

  • 顾名思义，自己跟自己连接 类比 自恋

  • 举个例子

    • emp 员工表
      
      //查询 员工 及其 经理 的信息 (经理也属于员工,类比: 班长头上有营长 但都是军人)
      select * form emp e, emp mgr 
      	where e.managerId = mag.id;
      
      ...
      
      'from 表1 表2 表3 ... 都是一张表', 以此类推
      
      

  • 非自连接

  • 顾名思义，多张表一起连接 类比 3…(咳咳)

  • 举个例子

    • emp 员工表
      dep 部门表
      
      //查询员工所对应的部门的整体信息
      select * form emp dep 
      	where emp.depid = dep.id;
      ...
      'from 表1 表2 表3 ... 存在有不同表', 以此类推
      
      

内连接、外连接

大头来辽~

看图更加清晰

• 内连接

  • 在图的正中间，也就是两张表的交集(注意！内连接可以多张表 那么就是多张表的交集)

  • emp 员工表
    dep 部门表
    loc 城市表
    
    //查询员工所对应的部门的整体信息
    一、
    select * form emp dep 
    	where emp.depid = dep.id;
    	
    二、
    select * form emp 
    	Join dep 
    		on emp.depid = dep.id;
    
    三、
    select * form emp dep loc
    	where emp.depid = dep.id and emp.locId = loc.id;
    	
    四、
    select * form emp 
    	Join dep 
    		on emp.depid = dep.id,
    	Join loc
    		on emp.locId = loc.id;
    	
    ...
    -----------------------------
    '查询工作地址为 上海 的所有员工信息'
     select e1.id '员工Id', e1.name '员工姓名', e1.salary '员工工资', d1.loc '工作地点'
     	from emp e1, dep d1 
    		 where e1.depId = d1.Id and d1.loc = '上海';
    			
    '表1 表2 表3 ... 任意表的交集', 以此类推
    
    
    

大头来了！

• 外连接

  • 左 外连接，看大图中的 左上图

    emp 员工表
    dep 部门表
    
    //查询所有员工所对应的部门的整体信息(包括有的员工没有对应的部门 也就是为Null, 类比 马化腾 他不属于腾讯中任意部门吧!)
    	
    二、
    select * form emp 
    	Left Join dep 
    		on emp.depid = dep.id;
    	'emp.depid = Null 也会被查询出来'
    ...
    
    '表1 Left Join 表2, 说明 表1的特定值可以为 Null', 以此类推
    
    
    

  • 右 外连接，看大图中的 右上图

    emp 员工表
    dep 部门表
    
    
    //查询所有部门所对应的的整体信息
    	
    二、
    select * form emp 
    	Right Join dep 
    		on emp.depid = dep.id;
    	'dep.id = Null 也会被查询出来'
    ...
    
    '表1 Right Join 表2, 说明 表2的特定值可以为 Null', 以此类推
    
    '其实你可能已经发现了！没错！左右连接是可以互换的
    其实下面这个sql 依然可以实现上面的功能：'
    
    select * form dep  
    	Left Join emp
    		on dep.id = emp.depid;
    		
    所以一般按照常人思维, 首选左外连接就没错啦！
    
    
    
    

接下来我们来实现左中图和右中图

• 左中图 和 右中图

  • 由图可知，也就是去除AB的交集，这两个我们就一起实现了

  • 
    emp 员工表
    dep 部门表
    
    
    // 查询没有部门的员工(what? 有着号人? 有啊,马化腾 还有那个 对钱不感兴趣的男人)
    	
    二、
    select * form emp 
    	Left Join dep 
    		on emp.depid = dep.id;
    		 where dep is NULL;
    '只会查询出 dep.id = Null 的内容'
    
    ...
    // 查询没有员工的部门
    select * form emp 
    	Right Join dep 
    		on emp.depid = dep.id;
    		 where emp is NULL;
    '只会查询出 emp.depid = Null 的内容'
    
    其实可以发现，这两个图 其实依然可以只用左 或 只用 右 就可以表示，这里就不赘述了，大家可以试试一下！
    
    
    

UNION、 UNION ALL 关键字

其实大家都可以发现，这张图已经很贴心的为我们写好了，sql语句。

但是！ 对于左下图 和 右下图 对于我们正在学的Mysql数据库 是不支持的！

不信大家可以去试试 哈哈！ 但是这个sql语句不支持为什么还罗列出来呢？

那你Mysql不支持，我Oracle就不能支持啦？ 所以 有些数据库的是支持的！

那么问题来了，Oracle 到底支不支持呢？ 有兴趣大家可以去试试哈，这里就讨论的是Mysql 哈哈 跑题了 现在！拉回正题！

• 满连接 UNION

  • 这个关键字用于两表之间查询后的结果 自动只保留一个共同的部分

  • 举个例子： 也就是 左上图 和 右上图 合在一起就是 左下图(满红) ，这是UNIOS关键字会去除 左上图 交集的部分 或者 右上图交集的部分，但最终会保留交集。

  • 
    emp 员工表
    dep 部门表
    
    
    //查询所有员工 对应 所有部门 的所有信息
    	
    
    select * form emp 
    	Left Join dep 
    	 on emp.depid = dep.id;
    UNION
    select * form emp 
    	Right Join dep 
    	 on emp.depid = dep.id;
    
    ...
    
    
    

• 满连接 UNION ALL

  • 相对于UNION 就是不去重，有多少就直接拼一起，也就是多个交集结果 我依然保留。

  • 相对于UNION ，那UNION ALL出现的意义是什么呢？答案就是！ UNION ALL 性能好 ！效率高呀！因为除去了去重的步骤

  • 
    emp 员工表
    dep 部门表
    
    '眼尖的同学就会发现了! 左中图和右上图拼接一起不就是满连接了嘛！ 相应的 右中图 和 左上图也是一个道理'
    //查询所有员工 对应 所有部门 的所有信息
    
    一、
    '左中图和右上图拼接一起的满连接'
    select * form emp 
    	Left Join dep 
    	 on emp.depid = dep.id
    	  where dep.id is NULL;
    UNION ALL
    select * form emp 
    	Right Join dep 
    	 on emp.depid = dep.id;
    	 
    二、	 
    '右中图和左上图拼接一起的满连接'
    select * form emp 
    	Left Join dep 
    	 on emp.depid = dep.id
    UNION ALL
    select * form emp 
    	Right Join dep 
    	 on emp.depid = dep.id;
    	  where emp.depid is NULL;
    	
    ...
    
    

NATURAL 自然连接、USING关键字

• 自然连接 NATURAL JOIN

  • 两张表全部相同的字段进行 等值连接 的结果

  • 
    emp {//员工表
    	id,
    	name,
    	...
    }
    dep {//部门表
    	id,
    	name,
    	...
    }
    
    //查询 员工表和部门表 相同字段 相等时的 结果
    
    select * from emp NATURAL JOIN dep;
    
    相当于
    select * from emp dep where emp.id = dep.id and dep.name = emp.name and ... ;
    
    '这种方法局限性较高，所以日常开发中 用的比较少'
    ...
    
    

• USING( )关键字

  • 向USING( )填入的字段 是 两个表都具有相同的字段且进行等值连接，与自然连接相比，这个具有选择性

  • 必须配合JOIN， JOIN…USING( )

  • 
    emp 员工表{
    	id,
    	name,
    	...
    }
    dep 部门表{
    	id,
    	name,
    	...
    }
    
    //查询 员工表和部门表 相同字段 相等时的 结果
    
    select * from emp JOIN dep USING( id, name );
    
    相当于
    
    select * from emp dep where emp.id = dep.id and dep.name = emp.name;
    
    ...
    
    

终于！结束了 连接，写得我手都麻了。