Select

一、SQL语句执行顺序

在一个SQL语句中，Where语句是最先执行的，然后执行group by,最后order by

经典的Select
select deptno,to_char(hiredate,'yyyy') hireyear, count(*) from emp group by deptno, to_char(hiredate',yyyy') order by deptno,hireyear;
update emp set sal = sal* 1.2 where exists (select 1 from dept where deptno = emp.deptno and loc = 'DALLAS')
--层次
-查询7788雇员的下属的下属
select level,t.* from emp t start with empno=7788 connect by prior empno=mgr;
--查询7788雇员上司的上司
select level,t.* from emp t start with empno=7788 connect by empno= prior mgr;

二、Dual

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

三、集合

union 会合并多重集
union alll 不会合并多重集
Intersect   交集
minus 差集
集合操作的一些限制：不允许的列类型blob,clob,bfile,varray 和嵌套表

四、连接

等值连接

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'

笛卡尔积
内连接(简单连接/连接)
内连接有两种写法,其实就是SQL的标准不一样，结果是一样的
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

外连接
可以用(+)书写或者join，官方建议用join

-查询出所有部门的雇员信息
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 语句

为一个子查询命名并可以在后续的sql中调用 
优势：
代码模块化
可读性增强
相同查询唯一化

--构造出1到128
with 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;