【H2O2|全栈】MySQL的基本操作（三）

前言

开篇语

准备工作

案例准备

多表查询

笛卡尔积

等值连接

外连接

内连接

自连接

子查询

存在和所有

含于

分页查询

建表语句

结束语

前言

开篇语

本篇继续讲解MySQL的一些基础的操作——数据字段的查询中的多表查询和分页查询，与单表查询相比，这些在面试时会更加常用。

准备工作

软件：【参考版本】Navicat Premium 16 / SQLyog - 32 bit

语言：MySQL

版本信息：MySQL5.7

案例准备

本次的案例为四张表，创建的语句比较长，放在本章的最后，请自行跳转至对应位置复制创建。

参考库结构——

dept部门表结构（4行3列）——

emp员工表结构（14行8列）——

多表查询

笛卡尔积

概念

笛卡尔积查询的结果是两张被查询的表的列数相加，行数相乘。

比如说一张三列四行的表和一张四列六行的表查询，在不进行过滤去重的条件下，查询的结果就是一张七列二十四行的新表，相同的列不会合并。

案例

1.员工表emp和部门表dept的笛卡尔积

select emp.*,dept.*

from emp,dept

可以看到，多表查询的from语句里不止有一张表。查询的结果是11*56，相同的列deptno（部门编号并未被合并）。

等值连接

概念

当过滤条件中涉及到两张表的某一条件对应相同（=）时，就可以使用等值连接查询。

而其余的情况就是非等值连接，比如使用>=，<=，<>，betwen and等作为过滤条件。

关键字

=

案例

1.使用等值连接，显示员工的编号，姓名，部门编号，部门名

select empno, ename, e.deptno, dname
from emp e, dept d where e.deptno = d.deptno
order by e.deptno

由于部门编号实际上在两张表中都有，所以我们可以将该列作为等值连接的过滤条件。

注意，由于这里有两张表，所以要指定使用哪一张表的部门编号，否则会提醒你不知道要找的是哪一张表的这一列——

外连接

概念

外连接实际上分为左外连接和右外连接两种，一般来说，以左表作为基准就是左外连接，反之就是右外连接。

那么，什么是以左表为基准呢？实际上就是左表中有的条件，右表中可能是没有的，但是我们需要把右表强行对应拼到左表中，如果拼接位置找不到对应的数据，则结果里的该位置处为NULL。

即以谁为基准，就应当保证结果中有谁的全部数据，而不保证另一侧。

关键字

LEFT JOIN

ON

RIGHT JOIN

ON

案例

1.外连接查询员工编号员工姓名部门编号部门名称

这里以左外连接为例——

select empno, ename, e.deptno, d.dname
from emp e left join dept d

on e.deptno = d.deptno order by e.deptno

可以看到，外连接和笛卡尔积查询的结果非常相似，只是把并列的两张表变成了外连接，然后把过滤条件放到ON中了。

实质上，笛卡尔积查询和外连接查询可以相互转化。

内连接

概念

当数据项既满足左外连接，又满足右外连接，即为两表有关联（不会补NULL）的数据项时，就是内连接了。

在数学上，我们也可以把内连接的结果看做是两表的交集。

关键字

INNER JOIN

ON

案例

1.内连接查询员工编号员工姓名部门编号部门名称

select empno,ename,e.deptno, d.dname
from emp e inner join dept d on e.deptno = d.deptno order by e.deptno

自连接

概念

自连接实际上就是相当于将自身复制一份，然后让自身和复制表之间实现连接。

案例

1.使用自连接，显示"XXX的上级是XXX"这种格式，对于没有上级的，显示为“无”

select concat(u.ename, '的上级是', ifnull(boss.ename, "无")) 附属关系
from emp u
left join emp boss
on u.mgr = boss.empno;

实际上，这里就是将一张表作为员工表，取的是员工姓名；

而部分员工是有下属的，所以员工表也可以作为上级表使用，两表的连接条件就是员工表的mgr编号与上级表的empno编号一致。

而部分员工可能没有上级，比如说KING的mgr为NULL，说明员工表实际上是外连接中的基准表。

使用IFNULL()来处理NULL的问题，否则整个varchar的值在拼接后会变为NULL。

子查询

概念

子查询的作用是，查询条件未知的事物，将查询的结果作为父查询的条件使用。

一般来说，子查询有下面的要点——

1) 子查询与父查询可以针对不同的表，也可以针对同一张表
2) 子查询与父查询在传参时，参数数量、类型和含义要相同

说白了，子查询可以看做一个（组）参数，或代表一张表。

但是子查询的效率是很低的，应当尽量少用。

案例

1.查询出高于10号部门的平均工资的员工信息

select *

from emp

where sal > (select avg(sal) from emp where deptno = 10)

实际上，我们需要做两次查表的操作，第一次是查询所有员工的信息（父查询），第二次是查询10号部门的平均工资（子查询）。

然后，将子查询的结果（avg(sal)）作为父查询的过滤条件，完成查询需求。

2.查询出有哪些部门的平均工资高于30号部门的平均工资，部门编号部门名称平均工资

select e.deptno, dname, avg(sal)

from emp e, dept d

where e.deptno <> 30 and e.deptno = d.deptno

group by e.deptno, dname

having avg(sal) > (select avg(sal) from emp where deptno = 30)

对于多行函数AVG()，该过滤条件需要添加到HAVING分组过滤关键字中。

不要忘记两表公有的列一定要指定是哪一张表的该列。

3.查询各部门中工资比本部门平均工资高的员工的员工号姓名工资

select empno, ename, sal
from emp e, (select avg(sal) avsa, deptno from emp group by deptno) f
where e.deptno = f.deptno
and sal > avsa

子查询的结果实际上也可以看成一张表，为了使这张新表 f 能够与员工表 e 有关联，我们可以为新表添加一个公共列deptno。

存在和所有

概念

存在是指，在结果集中至少有一个结果符合条件就符合；

所有是指，结果集的所有结果都符合条件才符合。

关键字

ANY()

ALL()

案例

1.查询出比20号部门任一员工薪资高的员工信息

select *

from emp

where sal > any(select sal from emp where deptno = 30)

这里涉及到员工表和只有30号部门员工的子表，所以用到的是子查询。

而过滤条件里要求查询的工资比任一结果高，即至少有一个符合（最低薪资）即可，所以可以使用ANY关键字。

2.查询出比30号部门任何员工薪资高的员工信息

select *

from emp

where sal > all(select sal from emp where deptno = 30)

这里要求比任何30号部门员工工资都高，所以使用ALL关键字。

实际上，在部分情况下，ANY和ALL与MIN()和MAX()函数的作用是相同。

含于

概念

含于是指某个（些）筛选内容包含在结果集中，筛选内容需要与结果集中内容对应。

关键字

IN()

案例

1.查询工作和工资与MARTIN完全相同的员工信息

select *

from emp

where (job, sal) in(select job, sal from emp where ename = "MARTIN")

我们的子查询查询出了两列的内容，而筛选条件job和sal与这两列一一对应。

分页查询

概念

分页查询是指查询指定表中的某一条至某一条的行。

关键字

LIMIT start, number

其中start为开始的索引值（从0开始），number为查询的条数。

案例

1.查询工资排名第4到8名的员工的信息

select *

from emp

order by sal

limit 3, 5

建表语句

员工表

CREATE TABLE `emp` (
  `empno` INT(4) NOT NULL COMMENT '员工编号',
  `ename` VARCHAR(10) DEFAULT NULL COMMENT '员工姓名',
  `job` VARCHAR(9) DEFAULT NULL COMMENT '员工岗位',
  `mgr` INT(4) DEFAULT NULL COMMENT '领导编号',
  `hiredate` DATE DEFAULT NULL COMMENT '入职时间',
  `sal` DECIMAL(7,2) DEFAULT NULL COMMENT '工资',
  `comm` DECIMAL(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` INT(2) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

员工表数据

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7369', 'SMITH', 'CLERK', '7902','1980-12-17', '800', NULL, '20');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-06-13', '3000', NULL, '20');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-06-13', '1100', NULL, '20');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');

部门表

CREATE TABLE dept(
  deptno INT(2) PRIMARY KEY NOT NULL,
  dname  VARCHAR(14),
  loc    VARCHAR(13)
);

部门表数据

INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('10', 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('20', 'RESEARCH', 'DALLAS');

INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('30', 'SALES', 'CHICAGO');

INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('40', 'OPERATIONS', 'BOSTON');