三、查询02

MySQL学习

三、查询（2）（DQL 数据查询语言）

1. 查询结果集的去重

​ distinct关键字去除重复记录

​ select distinct JOB from emp;

​ ▲distinct关键字只能出现在所有字段的最前方

​ select distinct DEPTNO,JOB from emp; // distinct出现在所有字段最前方所表示的是所有字段联合去除重复记录。

​ 案例：统计岗位的数量。select count(distinct JOB) from emp;

---

2. 连接查询

2.1 什么是连接查询？

​ 在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询，取出最后的结果。在实际开发中，一般一个业务都会对应多张表，比如：学生和班级对应两张表。学生信息和班级信息存储到一张表中，会导致数据存在大量的重复，导致数据冗余。

2.2 连接查询的分类

​ 根据语法出现的年代划分：SQL92、SQL99（比较新的语法）

​ 根据表的连接方式来划分：

​ 内连接：

​ 等值连接：

​ 非等值连接：between…and…

​ 自连接：

​ 外连接：

​ 左外连接：

​ 右外连接：

​ 全连接：（不讲，很少用！）

2.3 连接查询的笛卡尔积现象（笛卡尔乘积现象）。

​ 两张表查询没有条件的限制，最终的查询结果是两张表记录条数的乘积。

​ 案例：找出每个员工(ENAME-emp表)的部门(DNAME-dept表)名称，要求显示员工名和部门名（ENAME-DEPTON-DNAME,DEPTON是两张表共有的）。DEPTON是部门 编号。

​ select e.ENAME,e.DEPTNO,d.DNAME from emp e,dept d; // 给emp和dept表起别名ename和dename要联合起来一起显示

​ 关于表的别名的好处：①执行效率高②可读性好

2.4 如何避免笛卡尔积现象

​ 加条件进行过滤。

​ 避免了笛卡尔积现象，不会减少记录的匹配次数，只不过显示的是有效记录。

​ 找出每个员工的部门名称，要求显示员工名和部门名：select
​

	e.ename,e.deptno,d.dname 

		from 

				emp e,dept d 

		where 
				// SQL92，以后不用
				e.deptno = d.deptno; 

2.5 内连接之等值连接

​ 最大的特点：条件是等量关系。

​ 案例：找出每个员工的部门名称，要求显示员工名和部门名（SQL92太老了，不用了）

​ SQL99（常用的）：

select 

		e.ename,d.dname 

from 

		emp e 

inner join

		dept d 

on

		e.emp = d.dept;

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

2.6 内连接之非等值连接

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

​ 案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级（工资在最低区间到最高区间之内为一个等级）。

select 

		e.ename,s.grade,e.sal  

from 

		emp e 

join 

		salgrade s 

on 

		e.sal between s.losal and s.hisal;

2.7 内连接之自连接

​ 最大特点：一张表看作两张表，自己连接自己。（相当于把一张表复制一份变成两张表用）自连接等值非等值均可。

​ 案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

select 

		a.ename as '员工',b.ename as '领导'

from 

		emp a 

join 

		emp b 

on 

		a.mgr = b.empno;

2.8 外连接

2.8.1 定义

​ 内连接：假设A和B表连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，AB两张表没有主副之分，两张表是平等的。

​ 外连接：假设A和B表连接，使用外连接的话，AB两张表中，有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表中的数据。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。——> 主表的数据是无论如何都要查出来的

2.8.2 外连接（outer）的分类

​ 左外连接（左连接）：表示左边的表是主表。left join表示坐标的表是主表。

​ 右外连接（右连接）：表示右边的表是主表

​ 左连接有右连接的写法，右连接有左连接的写法。

​ 案例：找出每个员工的上级领导。左外连接：

​

select 

		a.ename as '员工',b.ename as '领导' 

from 

		emp a

left outer join 

		emp b 

on 

		a.mgr = b.empno;

​ KING在内连接查询时是没有数据的。

​ 右外连接写法：

select 

		a.ename as '员工' ，b.ename as '领导' 

from 

		emp b 

right outer join 

		emp a

on 

		a.mgr = b.empno;

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

​ 案例：找出哪个部门没有员工。

​ 用外连接（dept表是主表）：

select 

		d.*

from 

		dept d 

left join 

		emp e 

on 

		e.deptno = d.deptno

where

		e.empno is null;

2.9 三张表的连接查询

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

​ 注意：… A join B join C on …表示A表和B表先进行表连接，连接之后和C表继续进行表连接。（一个join加一个on，连接一次给一次连接条件）

select 

		e.ename,e.deptno,s.grade

from

		emp e

join 

		dept d

on

		e.deptno = d.deptno

join

		algrade s

on

		e.sal between s.losal and s.hisal; 

​ 案例：找出每个员工的部门名称以及工资等级以及上级领导。（KING必须呗=被查出来，所以只能用外连接）

select 

		e.ename,d.deptno,e1.ename

from

		emp e

left join 

		dept d

on

		e.deptno = d.deptno

left join

		emp e1

on

		e.mgr = a.empno; 

🔺注意分清什么时候用内连接查询，什么时候用外连接查询！

---

3.1 定义

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

​ 子查询可以出现的位置：

select

		..(select)..

from

		..(select)..

where

		..(select)..

3.2 where子句中使用子查询

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

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

3.3 from后面嵌套子查询

​ 用括号括起来一个语句形成的新表，可以当作一个新的表使用。

​ from后面只能跟表，但这个表可以是一条SQL语句。

​ 3.3.1 案例：找出每个部门平均薪水的等级。

​ 每个部门的平均薪水（按照部门编号分组）：select deptno,avg(sal) as avgsal from emp group by deptno;(起别名)

​ 把第一步得出的表当作临时表t，和薪资表连接使用，条件是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;

​ 3.3.2 案例：找出每个部门平均的薪水等级。

​ 第一步：找出每个人薪水的等级。select e.ename,e.deptno,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

​ 第二步：分部门。第一步得到的作为新表，按照部门编号的字段分组求等级的平均值。select t.deptno,avg(t.grade) as avggrade from (select e.ename,e.deptno,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by deptno; // ❌冗余做法，数据都有，不用当作临时表

select 

		e.deptno,avg(s.grade )

from

		emp e 

join 

		salgrade s 

on 

		e.sal between s.losal and s.hisal

group by

		e.deptno;

3.4 在select后面嵌套子查询

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

​ 之前的写法：select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;

​ 子查询的写法：select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

---

4. union（可以将查询结果集相加）

​ 案例：找出工作岗位是SALESMAN和MANAGER的员工。

​ 第一种写法：select ename,job from emp where job = ‘salesman’ or job = ‘manager’;

​ 第二种写法：select ename,job from emp where job in (‘salesman’,‘manager’);

​ 第三种写法（使用union）：

select 

		ename,job 
		
from 

		emp 
where 
		job = 'salesman' 

union

select 
		
		ename,job 
		
from 
		emp 
		
where 

		job = 'manager' ;

​ select ename,job from emp where job = ‘salesman’ union select ename,job from emp where job = ‘manager’ ;（与前两种写法不同的是，job的结果是按照两种联合顺序的，一样的job是挨着的）

​ union可以将两张不相干的表中的数据拼接在一起显示。两次查询结构的列数需要一样。

---

5. limit（★重点★分页查询必备）

5.1 特点

​ limit是MySQL中特有的，其他数据库中都没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

5.2 作用

​ limit取结果集中的部分数据。

5.3 语法机制

​ limit startIndex,length

​ startIndex表示起始位置，从0开始，0表示第一条数据。（不写起始位置默认是0）

​ length表示取几个。

​ 案例：取数工资前5名的员工。select ename,sal from emp order by sal desc limit 0,5;

​ ▲limit是SQL语句中最后执行的环节。

select			5

		....

from			1

		...

where			2

		...

group by		3

		...

having			4

		...

order by		6

		...

limit				7

		...;

5.4 通用的标准分页SQL

​ 每页显示3条记录：第一页0，3；第二页3，3；第三页：6，3；…

​ 每页显示pagesize条记录，第pageNo页：（pageNo-1）*pagesize，pagesize