# 二、Dual

select sysdate from dualselect user from dualselect 1+3 from dualselect to_char(sysdate,'YYYY-MM-DD") from dualselect jpbs2013.nextval from dual;

# 三、集合

union 会合并多重集
union alll 不会合并多重集
Intersect   交集
minus 差集

# 四、连接

select dname, loc, empno, ename from emp a, dept b where a.deptno = b.deptno and b.deptno = 20

select a.empno,a.sal,b.empno,b.ename.b.sal from emp a, emp b where a.mgr = b.empno and a.ename='SCOTT'

1、
select empno,ename,sal,grade from emp,salgrade where deptno=10 and sal between local and hisal

2、
select empno, ename, sal, grade from emp inner join salgrade on deptno=10 and sal between losal and hisal

-查询出所有部门的雇员信息select b.deptno, b.dname, a.* from emp a, dept b where a.deptno(+)=b.deptno;-等同select b.deptno, b.dname ,a.* from emp a left join dept b on a.deptno = b.deptno

--查询出所有部门的雇员数select b.deptno, count(*) from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno;--查询出所有部门的职位为经理的雇员数select b.deptno , count(empno) from emp a, dept b where a.deptno(+)=b.deptno and a.job(+)="MANAGER" group by b.deptno;-查询出没有雇员的部门select b.* from emp a, dept b where a.deptno(+) = b.deptno and a.empno is null

-查询部门不在纽约和达拉斯的雇员信息select * from emp where deptno not in (select deptno from dept where loc in("NEW YORK','DALLAS')-查询没有员工的部门信息select * from dept where deptno not in (select dept from emp)

-查询部门不在纽约和达拉斯的雇员信息
select * from emp where deptno not in (select deptno from dept where loc in("NEW YORK','DALLAS') select a.* from emp a, dept b where loc = 'NEW YORK' and a.deptno = b.deptno
-如果一个雇员的薪水高于2900 ，那么查出其所在部门的信息select * from dept a where exist(select 1 from emp b where a.deptno = b.deptno and b.sal>2900)-也可以这样写select a.* from dept a, emp b where a.deptno = b.deptno and b.sql >2900

# 五、with 语句

--构造出1到128with a as (select 1 from dual union all select 1 from dual)select rownum from a,a,a,a,a,a,a

--做一个5*5的乘法表with multiplier as (select rownum n from dual connect by rownum <6)select a.n||'*'||b.n||'='||(a.n*b.n) from multiplier a, multiplier b--还可以做算术题with a as (select 1 from dual union all select 1 from dual), b as (select rownum n from a,a,a,a) select x.n num_of_bull, y.n num_of_goose from b x, b y where x.n*4+y.n*2 = 42 and x.n + y.n = 15;