MySQL学习笔记（五）

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

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) > 2500 ;

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