MySQL多表查询详解

多表查询

1. 表与表之间的关系

<1> 一对一

• 用户表和身份信息表，用户表是主表

• 男人表、女人表

  create table man(

  mid int primary key auto_increment,
  mname varchar(32),
  wid int unique        

  );

  create table woman(

  wid int primary key auto_increment,
  wname varchar(32)

  );

<2> 一对多

• 最常见得表关系，用户表和订单表

• 员工表、部门表
  create table emp(

  empno int primary key auto_increment,
  ename varchar(32)
  deptno int

  );

  create teble dept(

  deptno int primary key auto_increment,
  dname varchar(32)

  );

<3> 多对多

• 学生表和课程表，通常情况都是将多对多的关系拆分为一对多或者多对一的关系

• 至少需要三张表

  create table student(

  cid int primary key auto_increment,
  sname varchar(32)

  );

  insert into student (sname) values('小张');
  insert into student (sname) values('小李');
  insert into student (sname) values('小王');

  create table course(

  sid int primary key auto_increment,
  cname varchar(32)

  );

  insert into course (cname) values('语文');
  insert into student (sname) values('数学');
  insert into student (sname) values('英语');
  insert into student (sname) values('化学');

  create table s_c(

  cid int,
  sid int

  );

  insert into s_c(sid, cid) values (1, 1);
  insert into s_c(sid, cid) values (1, 2);
  insert into s_c(sid, cid) values (1, 3);
  insert into s_c(sid, cid) values (1, 4);
  insert into s_c(sid, cid) values (2, 2);
  insert into s_c(sid, cid) values (2, 4);
  insert into s_c(sid, cid) values (3, 1);
  insert into s_c(sid, cid) values (3, 3);

2. 为什么要使用多张表

• 避免出现大量的数据的冗余
• 并不是表拆的越多越好，需要根据实际情况进行拆分

3. 概念

• 同时查询多张表

4. 多表查询的分类

<1> 合并查询

• union, union all
• 合并结果集，就是把两个select语句的查询结果合并到一起。（相当于并集）

• 合并的两个结果，列数和列的顺序，类型需要一致

  create table emp(

  empno int primary key auto_increment,
  ename varchar(32)

  );

  create table dept(

  depeno int primary key auto_increment,
  dname varchar(32)

  );

  select from emp union select from dept;
  select from emp union all select from dept;

<2> 连接查询

-- 员工表
create table emp(
    empno int primary key auto_increment,  # 员工编号
    ename varchar(32),  # 员工姓名
    job varchar(32),      # 员工职位
    mgr int,                          # 上级编号
    hiredate date,          # 入职时间
    sal double,                  # 薪资
    comm double,              # 奖金
    deptno int                  # 员工所属部门
);

-- 部门表
create table dept(
    deptno int primary key auto_increment,  # 部门编号
    dname varchar(32),  # 部门名称
    loc varchar(32)          # 部门地址
);

• 内连接

  • inner join .... on 、 join , ,
  • inner join 是一个比较运算符，只返回符合条件的行

  • 例如：

    • select * from emp inner join dept on emp.deptno=dept.deptno;
    • select * from emp e, dept d where e.deptno = d.deptno;
    • select * from emp e join dept d where e.deptno = d.deptno;

• 外连接

  • 左外连接 LEFT OUTER JOIN | left join .... on

    • 代表查询，左边行的全部，右边没有则null
    • select * from emp e LEFT OUTER JOIN dept d on e.deptno = d.deptno;

  • 右外连接 right join | right outer join .... on

    • 有连接包含right join 右表所有的行，如果左表中某行在右表没有匹配，则结果中对应的左表的部分全部为空(null)
    • select * from emp e RIGHT OUTER JOIN dept d on e.deptno = d.deptno;

• 自连接

  • 自连接就是说，在同一个数据表中，看作是两个表，表示查找每个人的领导，如果没有领导，则显示无领导
  • 把一张表看作成两张表，一张员工表，一张领导表，都是emp表
  • select e.ename, el.ename from emp e left join emp el on e.mgr = el.empno;

• 自然连接: natural join (join) | natural left join (同 left join) | natural right join (同 right join)

  • 自然连接会自动判断，以两个表中相同的字段为连接条件，返回查询结果。
  • 注意： 内连接不写连接条件会出现笛卡尔积的结果，应该避免这种情况，而外连接不写连接条件会报错
  • select * from emp natural join dept;
  • select * from emp NATURAL left join dept;
  • select * from emp NATURAL right join dept;

<3> 子查询 (ANY子查询、IN子查询、SOME子查询、ALL子查询)

• 子查询解决的问题：

  • 谁的薪资比张三高

    • select sal from emp where ename='张三'

• 定义

  • 子查询允许把一个查询嵌套在另一个查询当中
  • 子查询又叫做内部查询，相当于内部查询，包含内部查询的就成为外部查询，子查询的结果被主查询所使用。

• 注意的问题：

  • 括号
  • 可以在主查询的where select having from 后面，都可以使用子查询
  • 不可以在group by 后面使用子查询

  • 主查询和子查询可以不是同一张表；只有子查询返回的值，主查询可以使用
    需求：查询部门名称是人力部的员工信息
    -- 第一种方式：利用子查询
    select * from emp where deptno=(select deptno from dept where dname='人力部')
    -- 第二种方式：利用关联查询
    select * from emp e, dept d where e.deptno = d.deptno and d.dname = '人力部';

    SQL优化: 尽量使用多表查询

         绝大部分的子查询在最终执行的时候都是转换成一个多表查询来执行的。 通过SQL的执行计划可以看出来
         通过SQL执行计划会发现两种方式执行的一样的。
    

  • from 后面的子查询
    需求：

    查询员工号  姓名  薪资
    select empno, ename, sal from emp;

  • 一般不在子查询中进行排序
  • 一般先执行子查询，再去执行主查询

ANY 关键字

假设any内部的查询返回结果个数是三个，如: result1, result2, result3, 那么
select .... from .... where a > any(....)
->
select .... from .... where a > result1 or a > result2 or a > result3

需求：
    查询工资比1号部门中任意一个员工高的员工信息
    select * from emp where sal > any(select sal from emp where deptno = 1);

ALL 关键字

• ALL 关键字与 ANY 关键字类似，只不过把上面的or 改成 and

  select .... from .... where a > all(....)
  ->
  select .... from .... where a > result1 and a > result2 and a > result3

  需求：

  查询工资比1号部门中所有员工高的员工信息

  select * from emp where sal > any(select sal from emp where deptno = 1);
  select * from emp where sal > all(select sal from emp where deptno = 1);

SOME 关键字

some 关键字和 any 关键字是一样的功能，所以：
select .... from .... where a > any(....)
->
select .... from .... where a > result1 or a > result2 or a > result3

IN 关键字

In 运算符用于where 表达式中，以列表项的形式支持多个选择，语法如下：
    where column in (v1, v2, v3 ....);
    where column not in (v1, v2, v3 ....);
    当in 前面加上not 运算符时，表示与in 相反的意思，即不在这些列表项中选择。
    

案例：
    查询部门名称是人力部和研发部的员工
select * from emp where deptno in (select deptno from dept where dname = '人力部' or dname = '研发部');