多表查询、关联查询
笛卡尔积:将两张表完整的、无差别的乘积连接
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;