查询
- 连接查询分为内连接、外连接和自连接。
- 内连接也叫连接,是最早的一种连接。 还可以被称为普通连接或者自然连接, 内连接是从结果表中删除与其他被连接表中没有匹配行的所有行, 所以内连接可能会丢失信息。
标准sql: select e.*, d.* from emp e inner join dept d on e.deptno = d.deptno;
另一种写法: select e.*, d.* from emp e, dept d where e.deptno = d.deptno; - 外连接分为三种:左外连接,右外连接,全外连接。
对应SQL:LEFT/RIGHT/FULL OUTER JOIN。
通常我们省略outer 这个关键字。写成:LEFT/RIGHT/FULL JOIN。 - 在左外连接和右外连接时都会以一张表为基表, 该表的内容会全部显示,然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。
- 对于外连接, 也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项:
- (+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。
- 当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件, 则必须在所有条件中都包含(+)操作符。
- (+)操作符只适用于列,而不能用在表达式上。
- (+)操作符不能与or和in操作符一起使用。
- (+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。
- 没有(+)操作符的那张表为基表
- 左外连接举例:
标准sql:select e.*, d.* from emp e left outer join dept d on e.deptno = d.deptno;
用(+)操作符:select e.*, d.* from emp e, dept d where e.deptno = d.deptno(+); - 右外连接举例:
标准sql:select e.*, d.* from emp e right outer join dept d on e.deptno = d.deptno;
用(+)操作符:select e.*, d.* from emp e, dept d where e.deptno(+) = d.deptno; - 全外连接:左表和右表都不做限制,所有的记录都显示, 两表不足的地方用null 填充。 全外连接不支持(+)这种写法。
- 全外连接举例:
标准sql:select e.*, d.* from emp e full outer join dept d on e.deptno = d.deptno; - 自连接(self join)是SQL语句中经常要用的连接方式, 使用自连接可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
- 自连接举例:select e1.*, e2.* from emp e1, emp e2 where e1.mgr = e2.empno;
- 子查询:当一个查询是另一个查询的条件时,称之为子查询。
- 子查询举例:select * from emp where sal > (select sal from emp where ename = 'JAMES');
- 多行子查询运算符
- 多行子查询运算符举例:
in: select * from emp where deptno in (select deptno from dept where loc = 'DALLAS' or loc = 'BOSTOM');
all: select * from emp where sal > all(select sal from emp where ename = 'JAMES' or ename = 'FORD');
any: select * from emp where sal > any(select sal from emp where ename = 'JAMES' or ename = 'FORD');
exists: select * from emp where exists (select sal from emp where ename = 'JAMES' or ename = 'FORD'); - rownum:是一个伪列,在物理上这个列并不存在,只是在查询时才构造出来。 它是 oracle 系统顺序分配为从查询返回的行的编号,第一行为1,第二行为2,依次类推。 在web项目中分页会用到它。
- rownum举例:select rownum, emp.* from emp where rownum <= 5;
- 查询结果集合操作:
union操作用于计算两个结果集的并集,它在取并集时会自动去掉结果集的重复行。
select * from emp where sal <= 1600 union select * from emp where sal >= 1500;
intersect操作于计算两个结果集的交集
select * from emp where sal <= 1600 intersect select * from emp where sal >= 1500;
minus操作用于计算两个结果集的差集(只返回在第一个结果集中存在,但在第二个集中不存在的数据)
select * from emp where sal <= 1600 minus select * from emp where sal >= 1500;