MYSQL学习笔记

DAY3

distinct

1、把查询结构去除重复记录，原表数据不会被修改

select distinct job from emp;

• distinct只能出现在所有字段的最前方
• distinct若出现在job、deptno两个字段之前，表示两个字段联合起来去重

统计工作岗位的数量?

select count(distinct job) from emp;

连接查询（重要）

1、什么是连接查询？

从一张表中单独查询，称为单表查询。

emp表和dept表联合起来查询数据，从emp表中取员工姓名，从dept表中取名字，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2、连接查询的分类

根据表连接的方式分类：

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接（左连接）
  • 右外连接（右连接）
• 全连接

根据语法的年代分类：

• SQL92
• SQL99

3、当两张表进行连接查询时，没有任何条件的限制，最终查询的结果条数，是两张表条数的乘积，这种现象被称为笛卡尔积现象。

怎么避免笛卡尔积现象？

连接时加条件，满足这个条件的记录筛选出来！

查询员工姓名和部门名称？

select ename,dname from emp,dept where emp.deptno = dept.deptno;
查询效率会高一些
select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;
表起别名,很重要 效率问题
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92语法

思考：最终查询的结果条数减少了，匹配次数减少了吗？其实次数没有减少，只是把满足条件的结果筛选出来。

通过笛卡尔积现象，得出表的连接次数越多效率越低，尽量避免表的连接次数

4、内连接之等值连接

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92语法
SQL92语法的缺点：表连接的条件和后期进一步筛选的条件，都放在where后面，混合在一起，结构不清晰

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;//SQL99语法
SQL99 优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where。
//inner 可以省略，带着inner可读性更好，可以看出是内连接
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;//SQL99语法

等值连接是因为条件是等量关系

5、内连接之非等值连接

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

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

条件不是一个等量关系，称为非等值连接。

6、内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？

select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;

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

技巧：将一张表看成两份，两张表。

7、外连接

内连接的特点：完成能够匹配上这个条件的数据查询出来

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;右外连接

right代表什么？

表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

在外连接中，两张表的连接，产生了主次关系。内连接中两张表是平等的，没有主次关系

左外连接:将right改成left

任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法。

//左右连接outer可以省略，带着可读性强
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;右外连接

外连接的查询结果条数一定是 >= 内连接的查询结果条数。

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;

全连接：两张表都是主表

8、三、四张表连接

语法：

select … from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d 的连接条件

一条SQL中内连接和外连接可以混合，都可以出现

案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

select e.ename,d.dname,e.sal,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;

子查询

1、什么是子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

2、子查询可以出现在哪里呢？

• select后面
• from后面
• where后面

3、where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

实现思路：

第一步，查询最低工资是多少？select min(sal) from emp; 800

第二步，找出>800的 select ename ,sal from emp where sal>800;

第三步，合并

select ename,sal from emp where sal > (select min(sal) from emp);

4、from子句中的子查询

技巧：from后面的子查询，可以将子查询的查询结果当做一张临时的表。

案例：找出每个岗位的平均工资的薪资等级

思路：①找出每个岗位的平均工资②把上一步的查询结果看成一张真实的表t

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

select t.*,s.grade from (select job,avg(sal) avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;

5、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;

注意：对于select后面的子查询来说，这个子查询只能一次返回一条结果，多于一条就报错。

union合并查询结果集

案例：查询工作岗位是MANAGER和SALESMAN的员工？

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

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

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

union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

比如：a（10条记录）连接b（10条记录）连接c（10条记录）

10*10*10=1000

a连接b一个结果：100 a连接c一个结果100

使用union是：100+100=200

union把乘法变成了加法运算

union注意事项：

• union在进行结果集合并时，要求两个结果集的列数相同
• 结果集合并时，列和列的数据类型也要一致

limit的使用(重要)

limit是将查询结果集的一部分取出来，通常使用在分页查询当中。

分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差，可以一页一页翻页看。

案例：按照薪资降序，取出排在前5名的员工？

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

limit 怎么用？

• 完整用法：limit startIndex，length。

• startIndex是起始下标，length是长度。

• 缺省用法：limit 5;取前五。

• 起始下标从0开始

mysql中limit在order by之后执行。

取出工资排名在[3-5]名的员工？

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

limit的使用

因为起始下标从0开始，2表示起始位置从下标2开始，就是第三条记录（0,1,2），长度为3（3,4,5）

分页

每页显示3条记录

第一页：limit（0,3）[0,1,2]

第二页：limit（3,3）[3,4,5]

第三页：limit（6,3）[6,7,8]

第四页：limit（9,3）[9,10,11]

每页显示pageSize条记录

第pageNo页： limit （pageNo - 1)*pagesize,pagesize

**DQL语句总结：**select … from …where… group by … having … order by …limit

执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit