💖✨MySQL一万字深度总结，基础+进阶(四)

1.内连接之等值连接

1.1 最大的特点是 : 条件是等量关系。

1.2查询每个员工的部门名称，要求显示员工名和部门名

SQL92: (太老，不用了)

select 
   e.ename,d.dname
from
   emp e,dept d
where 
   e.deptno = d.deptno;
复制代码

SQL99: (常用的)

这个是省略inner的写法.inner可以省略，带着inner的目的是可读性好一些。

select 
  e.ename,d.dname
from 
  emp e
join 
  dept d
on 
  e.deptno = d.deptno;
复制代码

这是不省略inner的写法，

select 
  e.ename,d.dname
from 
  emp e
inner join 
  dept d
on 
  e.deptno = d.deptno;
复制代码

语法格式 ：

...
  A
join 
  B
on 
  连接条件
where 
   ...
复制代码

SQL99语法结构更清晰一些 ： 表的连接条件和后来的where条件分离。

2.内连接之非等值连接

2.1最大的特点是 : 连接条件中的关系是非等量关系。

2.2案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

// 省略了 inner
select 
   e.ename,e.sal,s.grade
from 
   emp e
join 
  salgrade s
on 
  e.sal between s.losal and s.hisal;
复制代码

3.内连接之自连接

3.1最大的特点 : 一张表看做两张表。自己连接自己。

3.2找出每个员工的上级领导，要求显示员工和对应的领导名

select empno,ename,mgr from emp;

EMP a表 : 员工表

EMP b表 : 领导表

员工的领导编号 = 领导的员工编号

select 
   a.ename as '员工名',b.ename as '领导名'
from
   emp a
inner join 
   emp b
on 
   a.mgr = b.empno;
复制代码

注意： 这个有13条数据，因为 KING 没有上级领导

4.外连接

4.1什么是外连接，和内连接的区别

4.2找出每个员工的上级领导（所有的员工都要查询出来，KING也要查询出来）

EMP a表 : 员工表

EMP b表 : 领导表

内连接:

select 
  a.ename '员工',b.ename '领导'
from
  emp a
join 
  emp b
on 
  a.mgr = b.empno;
复制代码

外连接 : (左外连接/左连接)

select 
  a.ename '员工',b.ename '领导'
from 
  emp a
left outer join
  emp b
on 
  a.mgr = b.empno;
其中 outer 可以省略
复制代码

外连接：(右外连接/右连接)

select 
  a.ename '员工',b.ename '领导'
from 
  emp b
right outer join
  emp a
on 
  a.mgr = b.empno;
其中 outer 可以省略
复制代码

重点 : left 和 right 区分的是以左表还是以右表为主表。

注意 : 这个有14条数据，其中 KING 没有上级 ，会自动匹配NULL

外连接最重要的特点是 ： 主要的数据无条件的全部查询出来。

4.3案例 : 找出哪个部门没有员工

select 
   d.*
from 
   emp e
right join 
   dept d
on 
   e.deptno = d.deptno
where 
   e.sal is null;
复制代码

5.三张表连接查询

5.1案例：找出每一个员工的部门名称以及工资等级

select 
  e.ename,d.dname,s.grade
from 
  emp e
join 
  dept d 
on 
  e.deptno = d.deptno
join 
  salgrade s
on 
  e.sal between s.losal and s.hisal;
复制代码

注意 : emp 表先和dept表连接，连接完成之后在和 salgrade 表连接

5.2找出每一个员工的部门名称、工资等级、以及上级领导。

select 
  e.ename,d.dname,s.grade
from
  emp e
join 
  dept d
on 
  e.deptno = d.deptno
join 
  salgrade s 
on 
  e.sal between s.losal and hisal
left join 
  emp e1
on
  e.mgr = e1.empno;
复制代码

6.子查询

6.1 什么是子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

6.2 子查询可以出现在哪里

select 
   ..(slect).
from 
   ..(select).
where 
   ..(select).
复制代码

6.3 where 子句中使用子查询

案例 ：找出高于平均工资的员工信息

// 错误写法，where 后面不能直接使用分组函数。

select * from emp where sal > avg(sal);

第一步: 找出平均工资

select avg(sal) from emp;

第二步 : where过滤

select * from emp where sal > 2073.214286;

第一步和第二步合并 :

select * from emp where sal > (select avg(sal) from emp);

6.4 from后面嵌套子查询

案例 : 找出每个部门平均薪水的薪资等级。

第一步 : 找出每个部门平均薪水 (按照部门编号分组，求sal的平均值)

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步 : 将上边的查询结果当作临时表 t,让 t 表和 salgrade表连接，条件是 ：t.avgsal between s.losal and s.hisal;

select 
  t.*,s.grade
from 
  (select deptno,avg(sal) as avgsal from emp group by deptno) t
join 
  salgrade s
on 
  t.avgsal between s.losal and s.hisal;
复制代码

6.5 在select后面嵌套子查询

案例 ： 找出每个员工所在的部门名称，要求显示员工名和部门名

select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

7. union (可以将查询结果集相加)

案例 : 找出工作岗位是SALESMAN和MANAGER的员工

第一种 : or

select ename,job from emp where job = 'MANAGER' or 'SALESMAN';

第二种 : in

select ename,job from emp where job in('MANAGER','SALESMAN');

第三种 : union

select ename,job from emp where job = 'MANAGER'

union  
 select ename,job from emp where job = 'SALESMAN';
复制代码

注意 : 两张不相干的表中的数据拼接在一起显示。

select ename from emp
union 
select dname from dept;
复制代码

8. limit (通用分页 / 分页查询)

8.1 limit (重点中的重点，以后分页查询全靠他了)

8.2 limit是mysql特有的，其他是数据库中没有，不通用。

(oracle中也有一个相同的机制，叫做rownum)

8.3 limit取结果中的部分数据，这是他的作用。

8.4 语法机制 ：

limit startIndex,length
复制代码

注意 :

• startIndex代表起始位置吗，从0开始，0代表第一条数据。
• length代表取几个。

8.5 案列 : 取出工资前5名的员工(思路: 降序取出前5)

第一步 : 将工资降序排序

select ename,sal from emp order by sal desc ;

第二步 : 取出工资前5的员工

select ename.sal from emp order by sal desc limit 0 , 5;

**8.5 limit是sql语句最后执行的一个环节 : **

select    5
   ...
from      1
   ...
where     2
   ...  
group by  3
   ...
having    4
   ... 
order by  6
   ...
limit     7
   ...;
复制代码

8.6 案列 ： 找出工资排名在第四到第九名的员工

select ename,sal from emp order by sal desc limit 3,6;

8.7 通用的标准分页sql

每页显示3条记录 :

第1页 : 0 , 3

第2页 : 3 , 3

第3页 : 6 , 3

第4页 : 9 , 3

第5页 : 12 , 3

重点 : 每页显pagesize条记录 ：

第pageNO页 : (pageNo - 1) * pagesize , pagesize

pagesize是什么？是每页显示多少条记录。

pageNo是什么？ 显示第几页。

int pageNo = 2; // 页码是2
   int pagesize = 10; // 每页显示10条
   limit 10,10;