oracle子查询的一些技巧, 使用buf
SQL> ed
已写入 file afiedt.buf
SQL> /
使用:
1, SQL> ed 打开记事本, 编写SQL, 保存并关闭记事本
2, SQL> / 执行编写的SQL
注意:
使用 "/" 结束, 而不是 分号
组函数的嵌套, 最多只能嵌套两层。
一,子查询
在一个select语句里嵌套另一个select语句
1, 求工资最高的人
复制代码 代码示例:
SQL> select ename, sal from emp
2 where sal = (select max(sal) from emp);
2, 求高于平均工资的人
复制代码 代码示例:
SQL> select ename, sal from emp
2 where sal > (select avg(sal) from emp);
3, 每个部门里薪水最高的人
复制代码 代码示例:
SQL> select emp.deptno, ename, sal from emp
2 join (select deptno, max(sal) max_sal from emp group by deptno) t
3 on (emp.deptno = t.deptno and emp.sal = t.max_sal);
4, 每个部门平均薪水的薪水等级
复制代码 代码示例:
SQL> ed
已写入 file afiedt.buf
1 select t.deptno, t.avg_sal, s.grade from salgrade s
2 join (select deptno, avg(sal) avg_sal from emp group by deptno) t
3* on t.avg_sal between s.losal and s.hisal
SQL> /
DEPTNO AVG_SAL GRADE
---------- ---------- ----------
10 2916.66667 4
20 2347 4
30 1637.5 3
二, 连接查询
1, SQL1992 与 SQL1999 的连接查询的区别
① SQL1992
直接在where子句里写表连接的条件 与 过滤条件
可读性比较差, 结构不清晰
复制代码 代码示例:
SQL> select ename, dname, grade
2 from emp e, dept d, salgrade s
3 where e.deptno = d.deptno and e.sal between s.losal and s.hisal
4 and job <> 'CLERK';
② SQL1999
将表连接的条件 与 过滤条件 相分离
复制代码 代码示例:
SQL> select ename, dname, grade from emp e
2 join dept d on e.deptno = d.deptno
3 join salgrade s on e.sal between s.losal and s.hisal
4 where job <> 'CLERK';
2, 自连接
求雇员的名称及其经理的名称
复制代码 代码示例:
SQL> ed
已写入 file afiedt.buf
1 select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename
2 from emp e1, emp e2
3* where e1.mgr = e2.empno
SQL> /
EMPNO ENAME MGR EMPNO ENAME
---------- -------------------- ---------- ---------- -------
7788 SCOTT 7566 7566 JONES
7902 FORD 7566 7566 JONES
7654 MARTIN 7698 7698 BLAKE
7844 TURNER 7698 7698 BLAKE
3, 交叉连接 cross join
笛卡尔积
SQL> select count(*) from emp;
COUNT(*)
----------
13
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp
2 cross join dept;
COUNT(*)
----------
52
4, 内连接 [inner] join 表 on 连接条件
①等值内连接完整写法
复制代码 代码示例:
select ename, dname from emp
join dept on emp.deptno = dept.deptno;
②等值内连接简写形式(不推荐使用)
复制代码 代码示例:
select ename, dname from emp
join dept using (deptno)
③非等值连接
复制代码 代码示例:
select ename, grade from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
④三表连接
复制代码 代码示例:
select ename, dname, grade from emp e
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between losal and hisal
where ename not like '_A%'
5, 外连接 left|right|full [outer] join on
没匹配到的记录也拿出来
①左外连接
A. 内连接, KING 那条记录没有, 即匹配不到的不显示
显示所有雇员的名称,及其经理名称, 没有经理的雇员不显示
复制代码 代码示例:
select e1.ename, e2.ename from emp e1
join emp e2 on (e1.mgr = e2.empno)
B. 左表中没匹配到的记录 显示NULL
显示所有雇员的名称,及其经理名称, 没有经理的雇员也显示
复制代码 代码示例:
select e1.ename, e2.ename from emp e1
left join emp e2 on (e1.mgr = e2.empno)
②右外连接
复制代码 代码示例:
select ename, dname from emp e
right join dept d on (e.deptno = d.deptno)
③全外连接
复制代码 代码示例:
select ename, dname from emp e
full join dept d on (e.deptno = d.deptno)
