mysql 04: 子查询

子查询

• 什么是子查询？可以出现在哪里？

  • select 嵌套select，被嵌套的select语句是子查询

  • 位置

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

  • where后使用子查询

    • 查询工资高于平均工资的员工信息：select * from emp where sal > (select avg(sal) from emp);

  • from后使用子查询

    • 找出每个部门平均薪资的薪资等级：

      • step1：select deptno, avg(sal) avgsal from emp group by deptno; //查询结果作为临时新表，再和其余表连接查询

      • step2：

        select
        	t.*, s.grade
        from
        	(select deptno, avg(sal) avgsal from emp group by deptno) t
        	--from后只能跟表，但是这个表可以是select的查询结果
        join
        	salgrade s
        on
        	t.avgsal between s.losal and s.hisal;
        

    • 找出每个部门平均的薪水等级

      • step1：找出每个员工的薪水等级

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

      • step2：其实没必要看作一个新表放在select后，直接对已有数据group by即可

        select
        	e.deptno, avg(s.grade)
        from
        	emp e
        join
        	salgrade s
        on
        	e.sal between s.losal and s.hisal
        group by
        	e.deptno;
        

  • select后使用子查询

    • 找出每个员工所在的部门名称，要求显示员工名和部门名

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

      • 改进写法

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

union

• 可以将查询结果相加

  • 示例：找出工作岗位是salesman 和 manager的员工

    • 使用or或者in

    • 使用union

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

  • 两张不相干表中的数据拼接在一起

    • select ename from emp
      union
      select dname from dept;
      

    • union前后查询出的列数必须相同，才可以拼接

limit

• 特点：

  • mysql特有的，其他数据库没有 (oracle 中有一个相似的机制，rownum)
  • 分页查询全靠它
  • 取结果集中的部分数据

• 语法机制：

  • limit satrtIndex, length

    • startIndex：表示开始位置
    • length：表示取几个

  • limit是sql中最后执行的语句

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

• 示例：取出工资前5名员工

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

• 示例：找出工资排名在第4到第9名的员工

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

• 通用的标准分页sql？

  每页显示3条记录
  page1：0，3
  page2：3，3
  page3：6，3
  page4：12，3
  
  每页显示pageSize条记录：
  第pageNo页：(pageNo - 1) * pageSize，pageSize
  
  pageNo:显示第几页
  pageSize:每页显示几条