乐趣区

关于mysql:MySQL多表查询详解

jiezi

多表查问

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 = '研发部');
退出移动版