数据库查询语句三

一 连接查询(JOIN)

1等值连接语法

select job,sal,comm,ioc from emp,dept where ename='SMIITH' and emp.deptno = dept.deptno;

2 外连接语法

假设emp表中有个全空的元素，它可以可任意元素匹配，满足连接条件，这样就是外连接

select ename,dept.deptno,Ioc from emp,dept wehre emp.deptno(+) =dept.deptno;

二 集合操作

包括UNION(集合并) INTERSECT集合交 MINUS集合差操作;

1 Union

select employee_name,age from development where age>35 union select employee_name,age from information where age>35;

2 intersect

select job from development intersect select job from informatio;

 

3 minus

select job from development minus select job from informatio;

三 子查询

1 多级子查询

select ename,deptno from emp where deptno=(select deptno from dept where ioc='BOSTON');

2 多个子查询语句

select ename,job,sal from emp where job =(select job from emp where ename ='CLARK') or sal>(select sal from emp where ename ='CLARK');

3 子查询有多个基表

select ename,job ,sal from emp ,dept where ioc='NEW WORK' and emp.deptno= dept.deptno and sal>(select sal from emp where ename='SCOTT');

4 子查询有聚合函数

select ename,hiredate from emp where hiredate=(select min(hiredate) from emp);

5 当子查询返回不止一个时，可以使用ALL ANY IN NOT IN

select ename,sal from emp where sal>any(select sal from emp where deptno=30) and deptno!=30;

6 当子查询返回值多于一个列时，主查询where 子句中的列名用括号括起来。

select ename,job,sal from emp where (job,sal) =(select job,sal from emp where ename='ford');