一、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;