MySql 多表查询

多表查询：指从多张表中查询数据。

笛卡儿积：笛卡儿积是指在数学中，两个集合（A集合 和 B集合）的所有组合情况。

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据（包括两张表交集部分数据
    • 右外连接：查询右表所有数据（包括两张表交集部分数据
• 子查询

内连接

隐式内连接：

select 字段列表 from 表1,表2... where 条件...;

显示内连接：

select 字段列表 from 表1 [inner] join 表2 连接条件;

• 查询员工姓名及所属部门名称（隐式内连接

  SELECT tb_emp.name, tb_dept.name FROM tb_dept,tb_emp where tb_emp.dept_id = tb_dept.id;
  

  给表起别名

  SELECT e.name, d.name FROM tb_dept d,tb_emp e where e.dept_id = d.id;
  

• 查询员工姓名及所属部门名称（显式内连接

  SELECT tb_emp.name, tb_dept.name FROM tb_dept inner join tb_emp ON tb_dept.id = tb_emp.dept_id;
  

外连接

• 左外连接：

  select 字段列表 from 表1 left [outer] join 表2 on 连接条件;
  

• 右外连接：

  select 字段列表 from 表1 right [outer] join 表2 on 连接条件;
  

• 查询员工表 所有员工姓名和对应的部门名称（左外连接

  SELECT e.name, d.name FROM tb_emp e left join tb_dept d on e.dept_id = d.id;
  

• 查询部门表 所有部门名称和对应的员工名称（右外连接）

  SELECT e.name, d.name FROM tb_emp e right join tb_dept d on e.dept_id = d.id;
  

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

语法：

select * from t1 where column1 = (select column1 from t2...);

子查询外部的语句可以是insert，delete，select的任何一个。

分类：

• 标量子查询：子查询返回的结果为单个值
• 列子查询：子查询返回的结果为一列
• 行子查询：子查询返回的结果为一行
• 表子查询：子查询返回的结果为多行多列

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=。

• 查询教研部所有员工信息

  • 查询教研部的部门ID - tb-dept

    select id from tb_dept where name = '教研部';
    

  • 再查询该部门ID下的员工信息 - tb_emp

    select * from tb_emp where dept_id = 2;
    

  合并两个sql语句：

  select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
  

• 查询在“方东白”入职之后的员工信息

  • 查询 “方东白”的入职时间

    select entrydate from tb_emp where name = '方东白';
    

  • 查询在”方东白“入职之后的员工信息

    select * from tb_emp where entrydate > '2012-11-01';
    

  合并两个sql语句：

  select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
  

列子查询

子查询返回的结果是一列（可以是多行）。常用的操作符：in，not in等。

• 查询”教研部“和”咨询部“的所有员工信息

  • 查询”教研部“ 和 ”咨询部“ 的部门ID - tb_dept

  select id from tb_dept where name = '教研部' or name = '咨询部';
  

  • 根据部门ID，查询该部门下的员工信息 - tb_emp

    select * from tb_emp where dept_id in(3,2);
    

  合并两个sql语句：

  select * from tb_emp where dept_id in(select id from tb_dept where name = '教研部' or name = '咨询部');
  

行子查询

子查询返回的结果是一行（可以是多列）。常用的操作符：= , <>, in, not in。

• 查询与”韦一笑“的入职日期及职位都相当的员工信息

  • 查询”韦一笑“的入职日期及职位

    select entrydate, job from tb_emp where name = '韦一笑';
    

  • 查询与其入职日期及职位都相同的员工信息

    select * from tb_emp where entrydate = '2007-01-01' and job = 2;
    

  合并两个sql语句：

  select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑');
  

表子查询

子查询返回的结果是多行多列，常作为临时表。常见操作符：in。

• 查询入职日期是”2006-01-01“之后入职的员工信息及其部门名称

  • 查询入职日期是”2006-01-01“之后的员工信息

    select * from tb_emp where entrydate > '2006-01-01';
    

  • 查询这部分员工信息及其部门名称

    select e.*, d.name from (select * from tb_emp where entrydate > '2006-01-01') e, tb_dept d where e.dept_id = d.id;