写点什么

MySQL 多表查询详解

用户头像
若尘
关注
发布于: 2021 年 04 月 12 日
MySQL多表查询详解

多表查询

1. 表与表之间的关系

<1> 一对一

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

  • 男人表、女人表

<2> 一对多

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

  • 员工表、部门表 create table emp(empno int primary key auto_increment,ename varchar(32)deptno int);

<3> 多对多

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

  • 至少需要三张表

2. 为什么要使用多张表

  • 避免出现大量的数据的冗余

  • 并不是表拆的越多越好,需要根据实际情况进行拆分

3. 概念

  • 同时查询多张表

4. 多表查询的分类

<1> 合并查询

  • union, union all

  • 合并结果集,就是把两个 select 语句的查询结果合并到一起。(相当于并集)

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

<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 = '人力部';

  • 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

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 = '研发部');
复制代码


发布于: 2021 年 04 月 12 日阅读数: 12
用户头像

若尘

关注

还未添加个人签名 2021.01.11 加入

还未添加个人简介

评论

发布
暂无评论
MySQL多表查询详解