MySQL老杜课程学习笔记（入门级）(二)

MySQL老杜课程学习笔记（入门级）(二)

1、distinct去重

mysql> select distinct job from emp; // distinct关键字去除重复记录。
mysql> select ename,distinct job from emp; //错误

注意：distinct关键字必须放在所有字段前方

mysql> select count(distinct job) from emp;//统计岗位的数量

2、连接查询

2.1、什么是连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。当一个表中需要很多字段时，难免存在数据冗余，比如说一张学生信息表，很多学生会属于一个班级，而这个班级字段就会造成大量重复，被称为数据冗余。

2.2、连接查询分类

根据语法出现的年代划分，包括：

• SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）

• SQL99（比较新的语法）

根据表的连接方式划分，包括：

• 内连接：

  • 等值连接

  • 非等值连接

  • 自连接

• 外连接：

  • 左外连接（左连接）
  • 右外连接（右连接）

• 全连接（本文不做介绍，很少用！）

2.3、笛卡尔积现象

笛卡尔积现象：当两张表进行连接查询的时候，若没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。 需要注意，即使利用where进行条件过滤，在连接查询过程，仍然逐一与where条件对比匹配，底层没有避免笛卡尔积现象，只是最终显示的结果没有笛卡尔积现象。

mysql> select e.ename,d.dname from emp e,dept d;//表也可以起别名，emp别名e，dept别名d

表起别名，可读性好，还可以提高执行效率。

2.4、内连接——等值连接

顾名思义，等值连接表示连接条件是等值关系。

内连接基本语法：
select
	..
from
	A
join
	B
on
	连接条件
where
	...

案例：查询每个员工的部门名称，要求显示员工名和部门名。

SQL92(版本):了解即可，看到这种脚本能看懂是连接查询，但是自己开发时不要使用，用99版本新语法
select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

SQL99(版本）：
select 
	e.ename,d.dname
from
	emp e
[inner] join         // inner一般省略。
	dept d
on
	e.deptno = d.deptno;

SQL99语法结构更清晰一些：表的连接条件和一般用于过滤的where条件分离了。

2.5、内连接——非等值连接

连接条件是非等值关系

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

select 
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

2.6、内连接——自连接

一张表看做两张表，自己连接自己

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

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

2.7、外连接

2.7.1 内连接和外连接的区别

内连接：

​ 假设A和B表进行连接，使用内连接的话，只显示出满足匹配条件的记录，AB两张表没有主副之分，两张表是平等的。

外连接：

​ 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主表中的记录全部显示，当主表中的记录在附表中没有满足匹配条件的记录，则自动补充NULL。

2.7.2 外连接

• 左外连接（左连接）：表示左边的表是主表。左右表示的是关于jion关键字的

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

左连接一定有右连接的写法，右连接也会有对应的左连接的写法。

案例：找出每个员工的上级领导（所有员工必须全部查询出来，最高领导不再有上级领导）

外连接：（左外连接/左连接）
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;
	
外连接：（右外连接/右连接）
select 
	a.ename '员工', b.ename '领导'
from
	emp b
right join
	emp a
on
	a.mgr = b.empno;

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

2.8、多张表联合查询

重复使用**jion…on…**关键字

....
	A
join
	B
on
	A和B连接条件
join
	C
on
	A和C连接条件

A表和B表先进行表连接，连接之后A表继续和C表进行连接。

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

select 
	e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp e1
on
	e.mgr = e1.empno;

3、子查询

3.1、子查询介绍

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

子查询可以出现：select语句后边、from语句后边、where语句后边

select
..(select).
from
..(select).
where
..(select).

3.2、where语句中的子查询

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

第一步：找出平均薪资

select avg(sal) from emp;

第二步：where过滤

select * from emp where sal > 第一部查出来的值

第一步和第二步合并：

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

3.3、from语句中的子查询

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

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

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

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：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.4、select语句中的子查询

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

传统写法：

select 
	e.ename,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 = 'MANAGER' or job = 'SALESMAN';

第二种：

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';

不同的表之间也可以用union合并查询结果集，但需要保证两个表字段保持一致。

5、limit 限制

limit是用来取结果集的部分数据显示，并没有改变查询的结果集，只是在结果集中进一步挑选出记录显示。limit是MySQL特有的，其他数据库中没有，不具有兼容性。

5.1 基本语法：

limit startIndex, length; //startIndex表示起始位置，从0开始，0表示第一条数据，length表示取几个

案例：取出工资前5名的员工

select ename,sal from emp order by sal desc limit 0, 5;
//0可以省略，默认从0开始，此处稍作提醒，sql语句中的substr取子串函数，下标使从1开始，多加注意！！！

limit语句是DQL语句中最后执行的语句

5.2、利用limit实现分页查询

pageSize：每页显示的记录条数

pageNo：页码

limit (pageNo - 1) * pageSize, pageSize //分页查询（显示）通用公式，可自行验证。