关联查询、子查询

多表查询、关联查询

笛卡尔积：将两张表完整的、无差别的乘积连接

select * from emp_zld,dept_zld;--无意义

如果第一张表有6个数据，第二张表有8个数据，最后得到48个数据

–通过笛卡尔积获得有效连接数据，
–等值连接，通过两张表中的共同字段，来进行匹配筛选

select * from emp_zld,dept_zld
where emp_zld.deptno = dept_zld.deptno;

--获得员工和他所在的部门名称
--公共字段必须添加表名前缀，唯一字段可以不加前缀
select e.ename,d.dname from emp_zld e,dept_zld d
where e.deptno = d.deptno;

–内连接、外连接
–内连接等同于等值连接

select E.DEPTNO,ENAME,DNAME
    FROM EMP_ZLD E
   INNER JOIN DEPT_ZLD D--inner可以省略
    ON E.DEPTNO = D.DEPTNO;

–左外连接、右外连接、全外连表
–驱动表：让哪张表的数值完全显示，哪张表就做驱动表

–左外连接

SELECT e.EMPNO,e.ENAME,e.DEPTNO,
    d.DEPTNO,d.LOC
FROM EMP_ZLD e LEFT OUTER JOIN DEPT_ZLD d
on e.DEPTNO = d.DEPTNO;

–右外连接

SELECT e.EMPNO,e.ENAME,e.DEPTNO,
    d.DEPTNO,d.LOC
FROM EMP_ZLD e right OUTER JOIN DEPT_ZLD d
on e.DEPTNO = d.DEPTNO;

–全外连接

SELECT e.EMPNO,e.ENAME,e.DEPTNO,
    d.DEPTNO,d.LOC
FROM EMP_ZLD e 
full OUTER JOIN DEPT_ZLD d ---full MYSQL不支持
on e.DEPTNO = d.DEPTNO;

子查询

–子查询
–根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

–子查询在select中

--用子查询查询员工所在部门名称
select ename,e.deptno,(select dname from dept_zld d where d.deptno =e.deptno)
    from emp_zld e;

–子查询在from中
–也称作行内视图或者匿名视图。

--查询出薪水比本部门平均薪水高的员工信息

select e.ename,e.sal
from emp_zld e join
(select empno,avg(sal) AVG_SAL
from emp_zld
group by empno
) b
on e.empno = b.empno and e.sal>b.avg_sal;

‐‐如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。
–子查询在where，having中

--查询列出最低薪水高于部门30的最低薪水的部门信息
select dname,loc,deptno 
from dept_zld 
where deptno in(
                    select deptno
                    from emp_zld 
                    group by deptno
                    having(min(sal)>( 
                    select min(sal) 
                        from emp_zld 
                            where deptno = 30
                                      )
                            )
                 );

‐在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。

--例如列出来那些有员工的部门信息：  
SELECT deptno, dname FROM dept d 
WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno);

–子查询效率比关联查询效率高
–子查询小结–from/where/having/select
–结果是单记录 - 一行一列，where 直接比较
–一列，多行记录 - 集合 any in all
–多行多列，作为一张临时表使用

分页查询

–分页查询
–分页查询又叫top10查询 每页显示多少条数据pageSize 当前第几页currentPage
– 当前显示第一条号码
–(page-1)pageSize+1~pagepageSize
–Oracle 自带 rownum 表中原始内容排号

select sal,ename,rownum
from emp
order by sal desc;

–查询前四条记录

select sal,ename,rownum
from emp;
--where rownum <= 4;

–rownum只能从1开始，只能查n条
–解决方案：将rownum查询出来后，添加别名，作为子查询表

select sal,ename,e.n
from (select sal,ename,rownum n from emp) e
where 
    e.n between 5 and 8;