05-Oracle学习_子查询和连接查询

落日映苍穹つ 2021-11-01 11:56 473阅读 0赞

# 技巧, 使用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)