mysql--多表查询

目录

一、联合查询
案例1，UNION
案例2，UNION  ALL
二、表连接查询
（一）内连接
（二）外连接
1.左外连接
2.右外连接 
3.全外连接
去重关键字 distinct 
三、自连接
案例1：
案例2：
四、子查询 

---

一、联合查询

• 作用：合并结果集就是把两个select语句的查询结果合并到一起！
  
• 合并结果集有两种方式：
  

   UNION：合并并去除重复记载，例如：SELECT * FROM t1 UNION SELECT * FROM t2；  
  UNION  ALL：合并但不去除重复记载，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
  案例1，UNION

   select * from emp where deptno=30
union
select * from emp where job='SALESMAN';
  查询结果会去重：

案例2，UNION  ALL

   select * from emp where deptno=30
union all
select * from emp where job='SALESMAN';
  查询结果不会去重： 

   注意：
  如果多条查询语句查询出来的结果，字段数量差别等，在进行union/union all联合查询时，将会报错。  
  mysql实现交集结果：通过子查询
   create table emp_clerk as select * from emp where job='CLERK';
  create table emp_30 as select * from emp where deptno=30;
  select *  from emp_clerk where empno in(
    select empno from emp_30
    );
  查询结果：

 差集：
存在与30部门表中的数据, 但是不存在于文员表的记载
   select * from emp_30 where empno not in(
    select empno from emp_clerk
    );
  查询结果：

二、表连接查询

多张表之间进行两两连接，两张表的每条记载都相互连接一次，连接查询会产生笛卡尔积
笛卡尔积  tb_class:4条数据  tb_student：1  总记载 4*1=4条
   select  *   from  tb_class,tb_student;
  查询结果： 

数据太多，无法从中找到我们需要的数据，那么就要从笛卡尔积筛选需要的数据，添加条件
内连接，外连接：都连接条件，从笛卡尔积结果集筛选
（一）内连接

   

• [inner] join  A内连接B  查询结果集中记载  满意条件才显示
  
• A的记载一定有一条与B对应
  

  

---

   标准sql语法：  select   *   /  列..  from  表1  [innner] join  表2
                        on  连接条件;      推荐
非sql标准语法： select * /  列..  from  表1  表2 where 连接条件;
  （二）外连接

   外连接的特点：让某些表的数据数据全部显示，不管是否满意条件
  1.左外连接

   SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
  join  左边(前面)的表全部显示   left  [outer]  join  
  

---

举例：
   查询部门以及员工信息, 全部部门都显示
  select * from  dept d  left join emp1 e
    on e.deptno = d.deptno;
  2.右外连接 

   SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
  join  右边(前面)的表全部显示  right  [outer]  join   
  

---

举例： 
   查询部门以及员工信息, 全部部门都显示
  select * from emp1 e right join dept d
    on e.deptno = d.deptno;
  3.全外连接

   两张表的数据全部显示   full  outer   join(MYSQL不支持它的写法) 
  

---

   mysql全连接： 把左外连接  联合(并集)  右外连接
  select  *  from  emp1  e  left  join  dept  d
        on  e.deptno =  d.depton
  union
  select  *  from  emp1  e  right  join  dept  d
        on  e.deptno =  d.depton
  去重关键字 distinct 

   distinct 列名,列名2...   多个列组合去重
    select distinct dname from emp e join dept d
on e.deptno = d.deptno;
  三、自连接

自己连接自己
   SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
  案例1：

列出受雇日期早于直接上级的全部员工的编号、姓名、部门名称
   1.找表： emp  e1  emp  e2  dept  d
  2.找条件：受雇日期早于直接上级的  连接条件  e1.mgr=e2.empno 
                   emp  e1=dept  d连接条件：e1.deptno=d,deptno
  3.找编号、姓名、部门
  

---

   select e1.empno,e1.ename,d.dname from emp e1 join dept d
on e1.deptno=d.deptno
join emp e2
on e1.mgr=e2.empno and e1.hiredate < e2.hiredate;
  结果：

案例2：

 列出全部员工的姓名及其直接上级的姓名
   select e.ename,w.ename
  from emp e join emp w on e.empno=w.mgr;
  结果：

四、子查询 

     子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
语法:
   SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
  子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
分类:
子查询出现的位置：

• where后，作为条件的一部分；
  

• from后，作为被查询的一条表；
  
• SELECT之后 ,作为被查询的一列;
  

当子查询出如今where后作为条件时，还可以使用如下关键字：

• any
  

• all
  

---

   查询员工的信息,以及他的部门信息,以及该部门的人数 count(1)
  

• 找表:  emp dept
  
• 找条件: 表连接条件
  
• 找列:  员工的信息,以及他的部门信息,以及该部门的人数
  
• 子查询作为表使用, 一定要给子查询取别名
  

    select  e.*,d.*,num from emp e join  dept d
on  e.deptno = d.deptno
join  (select deptno,count(1) num from  emp group by  deptno) t
on  e.deptno = t.deptno;
  

---

    共同子查询使用的关键字  
  

• any /some 子查询返回列表中，有任意一个满意即可
  
• all 子查询返回列表的全部值都必须满意
  

  > any(select 子查询)    > 最小值
< any(select 子查询)    < 最大值
  > all(select 子查询)   > 最大值
< all(select 子查询)   < 最小值
   案例1：
查询薪水比30部门任意一个小的员工信息
   select * from emp where sal < any(select sal from emp where deptno = 30 );
   案例2：
查询薪水比30部门全部人薪水都高的员工
   select * from emp where sal > all(select sal from emp where deptno = 30);
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。