0100 MySQL03

1.distinct关键字

把查询结果去除重复记录，原表数据不会被修改,只能出现在字段的最前端

select distinct job from emp;

select distinct job,deptno from emp;//表示两个字段联合起来 去重

2.连接查询

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

两张表联合起来查询数据，如从emp表中取员工名字，从dept表中取部门名字，这样的跨表查询，称为连接查询

连接查询的分类：

1.根据语法年代分类

SQL92：19992年出现的语法

SQL99：1999年出现的语法（重点学习）

2.根据表连接方式分类

内连接：

        等值连接

        非等值连接

        自连接

外连接

        左外连接（左连接）

        右外连接（右连接）

全连接（两张表都是主表的连接，了解）

3.当两张表进行连接查询时，没有任何条件限制会发生什么？

如：查询每个员工所在部门的名称

select ename,dname from emp,dept;

发生笛卡尔积现象，最终查询结果条数=两张表条数的乘积

4.如何避免笛卡尔积现象？

加入条件限制，尽量降低表的连接次数

select ename,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语法

3.内连接之等值连接

条件是等量关系，称为等值连接

1.查询每个员工所在部门名称，显示员工名和部门名？

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92语法

缺点：结构不清晰，表的连接条件和 后期进一步筛选的条件 都放在了where之后

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;//SQL99语法

优点：表连接的条件独立。 from a join b on 连接条件 where 进一步筛选条件

select e.ename,d.dname from emp e (inner) join dept d on e.deptno = d.deptno;//省略inner

4.内连接之非等值连接

条件不是等量关系，称为等值连接

1.找出每个员工的薪资等级，要求显示员工名，薪资和薪资等级？

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

5.内连接之自连接

一张表之间的连接，称为自连接

1.查询员工的上级领导，要求显示员工名和领导名？

技巧：把一张表看成两张表。emp a 员工表，emp b 领导表

a.mgr = b.empno//员工的领导编号 = 领导的员工编号

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

6.外连接

内连接特点：把完全能够匹配限制条件的数据查询出来，两张表没有主次关系

外连接特点：两张表连接产生了主次关系

1.查询每个员工所在部门名称，显示员工名和部门名？

select e.ename,d.dname from emp e (inner) join dept d on e.deptno = d.deptno;//内连接

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

select e.ename,d.dname from dept d left (outer) join emp e on e.deptno = d.deptno;//左外连接

//right表示将join关键字右边的这张表看成主表，为了将这张表的数据全部查询出来，捎带关联查询左边的表，若左表没有与右表相匹配，用null代替。outer可省略

//外连接的查询结果条数一定 大于等于 内连接的查询结果条数

2.查询员工的上级领导，要求显示所有员工名和领导名？（所有：无领导的员工也要显示）

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

//员工表为主表，左连接

7.多张表的连接

语法：select...from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件...

1.找出每个员工的部门名称以及薪资等级，要求显示员工名，部门名，薪资和薪资等级？

emp e :取出员工名和薪资

dept d:取出部门名

salgrade s:取出薪资等级

e与d连接条件：部门编号

e与s连接条件：薪资区间

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

2.找出每个员工的部门名称、薪资等级和上级领导，要求显示员工名，领导名，部门名，薪资和薪资等级？

select e.ename,e.sal,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 left join emp l on e.mgr = l.empno;

//内连接与外连接在一条语句中可组合使用

8.子查询

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

子查询可出现的位置？

select ...(select)...from...(select)...where...(select)...//可出现在select，from，where之后

9.where中的子查询

1.找出比最低薪资高的员工姓名和薪资

第一步：查询最低工资是多少？

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;//先执行子查询

10.from中的子查询

技巧：from中的子查询，可将子查询的查询结果当作一张临时表

1.找出每个岗位的平均工资的薪资等级？

第一步：找出每个岗位的平均工资（岗位分组求平均值）

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

第二步：把以上查询结果当作一张临时表t,与薪资等级表s连接

条件：t.avg(sal) between s.losal and s.hisal;

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

//从t表中查所有，s表中查薪资等级

第三步：合并

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

//as avgsal 把列名当做函数，起别名

11.select中的子查询（了解）

1.找出每个员工的部门名，要求显示员工名，部门名

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

注意：select中的子查询只能返回一条结果，否则报错

12.union合并查询结果集

1.查询工作岗位是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,b,c都有10条记录。a 连接 b 连接 c 匹配次数10*10*10=1000

a连接b的结果：10*10=100，a连接c的结果：10*10=100

使用union：100+100=200（把乘法变加法）

2.使用注意事项

1.在进行结果集合并时，要求两个结果集的列数相同

select ename,job from emp where job = 'manager' union  select ename from emp where job = 'salesman';//错误

2.结果集合并时，列和列的数据类型也要相同（MySQL不相同也可，Oracle不行）

select ename,job from emp where job = 'manager' union  select ename,sal from emp where job = 'salesman';

13.limit

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

如：百度默认一页显示10条记录

分页的作用：提高用户体验，一页一页翻页看

使用：limit 起始下标，长度

1.按照薪资降序，取出排名前五的员工？

select ename,sal from emp order by sal desc limit 0,5;//完整用法，起始下标从0开始

select ename,sal from emp order by sal desc limit 5;//缺省用法

注：MySQL中limit在order by之后执行

2.取出工资排名在（3，5）名的员工

select ename,sal from emp order by sal desc limt 2,3;//起始位置2，长度3

14.通用分页

每页显示3条记录

第一页：limit 0,3

第二页：limit 3,3

第三页：limit 6,3

第四页：limit 9,3

每页显示pageSize条记录

第pageNo页：limit (pageNo-1)*pageSize,pageSize