1.1.6 分组查询（重点）
- 概念：在实际的应用之中，可能有这样的需求，需要先进行分组，之后对每一个分组进行操作，这个时候就要用到分组查询
- 使用having子句 ，可以对分完组之后的数据进一步过滤 ， 不能单独使用，要和group by一起使用 ，并且位置不能改变，不能代替where
- 格式： select 字段名 from 表名 group by ......
- 格式 ： select 字段名 from 表名 where 条件 group by ...... order by 排序 ；
【案例】：计算每个工作岗位的工资和 select job , sum(sal) from emp group by job ; 【案例】：计算每个工作的平均薪资 select job , avg(sal) from emp group by job ; 【案例】：计算每个部门的最高薪资 select deptno , max(sal) from emp group by deptno ; [注意]：在一条select语句后面有 group by ，则在 select 后面只能跟分组的字段，以及分组函数 【案例】：找出每个部门，不同工作岗位的最高工资 select deptno , job , max(sal) from emp group by deptno , job order by deptno asc; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+ 【案例】：找出每个部门的最高薪资，要求显示最高薪资大于3000 select deptno , max(sal) from emp where sal > 3000 group by deptno ;(效率比下面高) 使用having子句 ，可以对分完组之后的数据进一步过滤, 不能单独使用，要和group by一起使用 ，并且位置不能改变，不能代替where select deptno , max(sal) from emp group by deptno having max(sal) > 3000 ;(效率低) 【案例】：找出每个部门平均薪资，要求显示最高薪资大于2500 【分析】：因为是平均薪资，无法提前过滤，where失效，用having select deptno , avg(sal) from emp group by deptno having avg(sal) > 250
1.1.7 单表查询总结
- 格式: select...from....where....group by....having....order by......
- 执行顺序： from --- where --- group by ---- having ----- select ----- order by ；
【案例】：找出每个岗位的平均薪资，要求现实平均工资大于1500的，除manager岗位之外，要求按照平均薪资的降序排列 select job , avg(sal) as avgsal from emp where job != 'manager' group by job having avg(sal) > 1500 order by avgsal desc ;
1.1.8 去除重复记录
- 格式：select distinct 字段名 from 表明 ;
- 注意：distinct 只能出现在所有字段的最前面 ， 字段有多个时 ， 表示联合去重
【案例】：统计工作岗位的数量 select distinct job from emp ;
1.1.9 连接查询（重点）
- 概念：两张表或多张表中联合起来查询数据，从一个表中取一个信息，从另外的表中查询其余信息的查询方式叫做连接查询，联合查询
- 分类：
- 按照年代分类：SQL92 、 SQL99（主要学习）
- 按照表的连接方式分为：内连接、外连接、全连接
- 内连接：等值连接、非等值连接、自连接
- 外连接：左外链接（左连接）、右外链接（右链接）
- 特殊现象：当两张表连接查询，如无限制，会发生笛卡尔积现象
当两张表进行连接查询，没有限制的话，结果是两张表条数的乘积；
// 演示笛卡尔积现象 【案例】：查询两张表的信息 select ename ,empno from emp ,dept ; +--------+-------+ | ename | empno | +--------+-------+ | SMITH | 7369 | | SMITH | 7369 | | SMITH | 7369 | | SMITH | 7369 | | ALLEN | 7499 | | ALLEN | 7499 | | ALLEN | 7499 | | ALLEN | 7499 | | WARD | 7521 | | WARD | 7521 | | WARD | 7521 | | WARD | 7521 | | JONES | 7566 | | JONES | 7566 | | JONES | 7566 | | JONES | 7566 | | MARTIN | 7654 | | MARTIN | 7654 | | MARTIN | 7654 | | MARTIN | 7654 | | BLAKE | 7698 | | BLAKE | 7698 | | BLAKE | 7698 | | BLAKE | 7698 | | CLARK | 7782 | | CLARK | 7782 | | CLARK | 7782 | | CLARK | 7782 | | SCOTT | 7788 | | SCOTT | 7788 | | SCOTT | 7788 | | SCOTT | 7788 | | KING | 7839 | | KING | 7839 | | KING | 7839 | | KING | 7839 | | TURNER | 7844 | | TURNER | 7844 | | TURNER | 7844 | | TURNER | 7844 | | ADAMS | 7876 | | ADAMS | 7876 | | ADAMS | 7876 | | ADAMS | 7876 | | JAMES | 7900 | | JAMES | 7900 | | JAMES | 7900 | | JAMES | 7900 | | FORD | 7902 | | FORD | 7902 | | FORD | 7902 | | FORD | 7902 | | MILLER | 7934 | | MILLER | 7934 | | MILLER | 7934 | | MILLER | 7934 | +--------+-------+ 56 rows in set (0.00 sec) 【分析】：当两张表进行连接查询，没有限制的话，结果是两张表条数的乘积； 工作原理：两张表会从一张表的所有信息分别匹配另一张表的所有信息 【如何避免笛卡尔积现象】：进行表连接时增加条件 select emp.ename , dept.dname from emp , dept where emp.deptno = dept.deptno ; 【注意】：匹配的次数并没有减少，只是显示的记录减少了 【注意2】：进行表查询的时候，注意起别名 select e.ename , d.dname from emp , dept where e.deptno = d.deptno ; // 92语法 【注意3】：表的连接次数越多、效率越低；尽量减少表的连接
- 内连接 (inner) join
- 分类：等值连接、非等值连接、自连接
- 特点：完全匹配上条件的数据可以查出来
// 等值连接 ····// 条件是一种等量关系 【案例】：查询每个员工所在部门名称，显示员工名和部门名 select emp.ename , dept.dname from emp , dept where emp.deptno = dept.deptno; //92 select emp.ename , dept.dname from emp join dept on emp.deptno = dept.deptno; //99 //非等值连接 ····// 条件不是一种等量关系 【案例】：找出每个员工的薪资等级 ， 要求显示员工名，薪资，薪资等级 select e.ename , e.sal , s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal ; //自连接 ····// 一张表看作两张表，起别名加以区分 【案例】：查询员工的上级领导，显示对应地员工名和领导名 select a.ename , b.ename mgrname from emp a join emp b on a.mgr = b.empno ; 【案例2】：查询员工的上级领导，显示对应地员工名和领导名。要求名称首字母大写，其余小写 select concat(upper(substr(a.ename , 1 , 1)) , lower(substr(a.ename , 2 , length(a.ename) - 1)) ) as ename , concat(upper(substr(b.ename , 1 , 1)) , lower(substr(b.ename , 2 , length(b.ename) - 1)) ) as mgrname from emp a join emp b on a.mgr = b.empno;
- 外连接 ：left / right（outer）join
- 分类：左外链接（左连接）、右外链接（右链接）
- 左外连接：select ... from ... left join .... on ...... 其中的left表示join左面的表的信息全部显示
- 右外连接：select ... from ... right join .... on ...... 其中的right表示join右面的表的信息全部显示
// 左外连接 ····// 表示左表为主表，主表的信息会全部显示出 【案例】：查询员工的上级领导，显示对应地员工名和领导名。要求名称首字母大写，其余小写.要求显示所有的名 select concat(upper(substr(a.ename , 1 , 1)) , lower(substr(a.ename , 2 , length(a.ename) - 1)) ) as ename , concat(upper(substr(b.ename , 1 , 1)) , lower(substr(b.ename , 2 , length(b.ename) - 1)) ) as mgrname from emp a left join emp b on a.mgr = b.empno; // 右外连接 ···· // 表示右表为主表，主表的信息会全部显示出 【案例】：查询每个员工所在部门名称，显示员工名和部门名。要求显示所有的部门名 select e.ename , d.deptno from emp e right join d.deptno on e.deptno = d.depto ;
- 多表连接：
- 格式：select.... from a join b on ..... join c on ...... join d on ..... ;
【案例】：找出每个部门的员工名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级 select e.ename , d.dname , e.sal , s.grade from emp e left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal ; 【案例】找出每个员工的员工名称以及工资等级、上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级 select e.ename , a.ename mgrname , d.dname , e.sal , s.grade from emp e left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal left join emp a on e.mgr = a.empno;
1.1.10 子查询
- 概念：select 语句的嵌套 ， 被嵌套的语句称为子查询
- 位置：select 、 from 、 where 后面可以出现
//where字句中的子查询 【案例】：找出比最低工资高的员工姓名和薪资 select ename , sal from emp where sal > (select min(sal) from emp); //from字句中的子查询 ····//from后面的子查询可以将子查询的结果当做一张查询表来看待 【案例】：找出每个岗位的平均工资的薪资等级 select job , a.avgsal , s.grade from (select job , avg(sal) avgsal from emp group by job) as a join salgrade s on a.avgsal between s.losal and hisal ; //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 ;
1.1.11 union 合并查询结果
- 注意：union 在进行结果集的合并的时候，列数要相同，列的数据类型也相同
【案例】：查询工作岗位是manager和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' ; // 效率更高，因为可以减少匹配的次数
1.1.12 limit（重点）
- 概念：limit 是将查询的一部分取出来，通常使用在分页查询之中
- 分页的作用是提高用户的体验
- 格式：
- 完整用法： limit（startIndex , length） startIndex：起始下标，默认从0开始 ; 没有括号
- 缺省用法：limit + 数字，表示前几；
- 注意：在mysql之中，limit在order by 之后 执行
【案例】：按照薪资降序，输出排名在前5的员工 select ename , sal from emp order by sal desc limit 5; 【案例】：按照薪资降序，输出薪资排名 [3 , 5] 的员工 select ename , sal from emp order by sal desc limit 2 , 3 ; 【案例】：按照薪资降序，输出薪资排名 [5 , 9] 的员工 select ename , sal from emp order by sal desc limit 4 , 5 ; // 通用分页的写法 每页显示pageSize条数据 第pageNo页 ： limit (pageNo - 1) * pageSize , pageSize ; Java写法： public static void main(String[] args){ // 用户提交过来的一个页码，以及每页显示的记录条数 int pageNo = 5 ; // 第5页 int pageSize = 10 ; // 每页显示10条 int startIndex = (pageNo - 1) * pageSize ; String sql = "secelt ... limit" + startIndex + "," + pageSize ; }
1.2 DQL的总结
- 格式：select .... from... where .... group by .... having .... order by .... limit ....
- 执行顺序：from ---- where ---- group by ---- having ---- select ---- order by ---- limit
