Select

来源:转载

一、SQL语句执行顺序

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

经典的Selectselect 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 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 差集
集合操作的一些限制:不允许的列类型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到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;



分享给朋友:
您可能感兴趣的文章:
随机阅读: