MySQL中连接查询和子查询的问题

目录
  • 多表连接的基本语法
  • 交叉连接和笛卡尔积现象
    • 交叉连接
    • 笛卡尔积现象
  • 内连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接
    • 子查询

      多表连接的基本语法

      多表连接,就是将几张表拼接为一张表,然后进行查询

      select 字段1, 字段2, ...
      from 表1 {inner|lift|right} join 表2
      on 连接条件;

      有如下两张表:部门表和员工表

      交叉连接和笛卡尔积现象

      交叉连接

      交叉连接,又名无条件内连接/笛卡尔连接

      第一张表种的每一项会和另一张表的每一项依次组合

      select * from employee,department;

      上述结果肯定不是我们想晓得的,左表中每一个人都有4个部门,仔细观察这4条记录,正好是左表每一条记录和右表一一匹配后的结果。

      笛卡尔积现象

      笛卡尔积现象产生的原因:两张表没有有效的连接条件。既然你没有连接条件,本表中的第一行肯定是能和另外表中的所有行进行一一匹配,同理,本表中的第二行肯定是能和另外表中的所有行进行一一匹配,以此类推,本表中的最后一行m也可以和另外表中的所有行进行一一匹配。若另外一张表有n行,那么最后显示的行数,肯定就是m*n行了。

      如果不想产生笛卡尔积现象,就需要添加有效的表连接条件。拿上述例子来说,左表dep_id只有和右表id相等时,才代表他们的部门。

      内连接

      内连接(inner join)是找几张表的交集,即根据条件筛选出来正确的结果。

      select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
      from employee as emp inner join department as dep
      on emp.dep_id=dep.id;

      由于部门表中没有id=5的部门,所以员工表dep_id=5的这条记录没有返回;而由于行政部没有员工,所以这条记录也没返回。

      外连接

      左外连接

      左连接(left join)是以左表为准,如果右表中没有合适的记录,用null补全;其本质是在内连接的基础上增加左表有结果而右表没有的记录(内连接时,这种情况的记录会忽略)。

      select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
      from employee as emp left join department as dep
      on emp.dep_id=dep.id;

      右外连接

      跟左连接正好相反,右连接(right join)是以右表为准,如果左表中某些字段没有合适的结果,用null补全;其本质是在内连接的基础上增加右表有结果而左表没有的记录(内连接时,这种情况的记录会忽略)。

      select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
      from employee as emp right join department as dep
      on emp.dep_id=dep.id;

      全外连接

      全外连接,在内连接的基础上,展示左右表的所有的记录,而左右表中缺省记录以null补全。

      mysql中并没有全外连接的full join语法,而是借助union/union all语句实现。

      unionunion all的区别,union具有去重功能。

      select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
      from employee as emp left join department as dep
      on emp.dep_id=dep.id
      union
      select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
      from employee as emp right join department as dep
      on emp.dep_id=dep.id;

      子查询

      • 子查询是将一个查询语句嵌套再另一个查询语句中的查询方式:
      • 子查询的内层查询结果,可以作为外层查询语句提供查询条件。
      • 子查询中可以包含innot inandallexistsnot exists等关键字。

      子查询中还可以包含比较运算符,如=!=><等。

      -- 查询平均年龄在20以上的部门名称
      select name
      from department
      where id in (
      select dep_id
      from employee
      group by dep_id
      having avg(age) > 20);
      
      -- 查询财务部员工姓名
      select name 
      from employee
      where dep_id in (
      select id 
      from department 
      where name='财务部');
      
      
      -- 查询所有大于平均年龄的员工的年龄和姓名
      select name,age 
      from employee 
      where age > (
      select avg(age) from employee);

      到此这篇关于mysql中连接查询和子查询的问题的文章就介绍到这了,更多相关mysql连接查询和子查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

      (0)
      上一篇 2022年3月21日
      下一篇 2022年3月21日

      相关推荐