Oracle 技能强化 Part 3 操作多个表

最新推荐文章于 2024-03-31 10:57:17 发布

DB架构

最新推荐文章于 2024-03-31 10:57:17 发布

阅读量305

点赞数

分类专栏： Oracle Administration 文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/u011868279/article/details/122476202

版权

Oracle Administration 专栏收录该内容

66 篇文章 16 订阅

订阅专栏

1、UNION ALL 合并并现实记录集

SQL> SELECT empno as 编码，ename as 名称，nvl(mgr,deptno) as 上级编码 from emp
2 union all
3 SELECT deptno as 编码,dname as 名称，null as 上级编码 from dept;

编码名称上级编码
---------- -------------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING 10
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

18 rows selected.

SQL>

2、UNION 与OR

3、组合相关的行

4、自相联

5、IN、EXISTS、INNER JOIN

SQL>
SQL> create table emp2 as select ename,job,sal from emp where job = 'CLERK';

Table created.

SQL> EXPLAIN PLAN FOR SELECT empno,ename,job,sal,deptno from emp where (ename,job,sal) in (select ename,job,sal from emp2);

Explained.

SQL> select * from table(dbms_xplan.display());

SQL> EXPLAIN PLAN FOR SELECT empno,ename,job,sal,deptno from emp a where exists(select null from emp2 b where b.ename=a.ename and b.job = a.job and b.sal = a.sal);

Explained.

SQL> select * from table(dbms_xplan.display());

SQL> EXPLAIN PLAN FOR SELECT a.empno,a.ename,a.job,a.sal,a.deptno from emp a inner join emp2 b on (b.ename = a.ename and b.job = a.job and b.sal = a.sal);

Explained.

SQL> select * from table(dbms_xplan.display())

6、NOT IN、NOT EXISTS、LEFT JOIN

7、INNER JOIN、LEFT JOIN 、RIGHT JOIN、FULL JOIN 解析

SQL> DROP TABLE L PURGE;
DROP TABLE L PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> DROP TABLE R PURGE;
DROP TABLE R PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE L AS
2 SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
SELECT 'left_2','2' AS v FROM dual UNION ALL
4 SELECT 'left_3','3' AS v FROM dual UNION ALL
5 SELECT 'left_4','4' AS v FROM dual;

Table created.

SQL> CREATE TABLE R AS
2 SELECT 'right_3' AS str,'3' AS v,1 AS status FROM dual UNION ALL
3 SELECT 'right_4' AS str,'4' AS v,0 AS status FROM dual UNION ALL
SELECT 'right_5' AS str,'5' AS v,0 AS status FROM dual UNION ALL
5 SELECT 'right_6' AS str,'6' AS v,0 AS status FROM dual;

Table created.

SQL> SELECT L.str as left_str,R.str as right_str from L inner join R on L.v = R.v order by 1,2;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4

SQL> select L.str as left_str,R.str as r_right from L,R WHERE L.v = R.v order by 1,2;

LEFT_S R_RIGHT
------ -------
left_3 right_3
left_4 right_4

SQL> select L.str as left_str,R.str as right_str from L left join R on L.v = R.v order by 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4

SQL> select L.str as left_str,R.str as right_str from L,R where L.v = R.v(+) ORDER BY 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4

SQL> select L.str as left_str,R.str as right_str from L right join R on L.v = R.v order by 1,2;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
right_5
right_6

SQL> select L.str as left_str,R.str as right_str from L,R where L.v(+) = R.v order by 1,2;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
right_5
right_6

SQL> select L.str as left_str,R.str as right_str from L full join R on R.v = L.v order by 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
right_5
right_6

6 rows selected.

SQL>

8、外联接中的条件不要乱放

SQL>
SQL> select l.str as left_str,r.str as right_str
2 from l
3 left join r on (l.v = r.v and r.status = 1)
4 order by 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4

SQL>
SQL> select l.str as left_str,r.str as right_str
2 from l, r
3 where l.v = r.v(+)
4 and r.status(+) = 1
5 order by 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4

SQL>

9、检测两个表中的数据及对应数据的条数是否相同

10、聚集与内联接

11、聚集与外联接

12、从多个表中返回丢失的数据

SQL> INSERT INTO emp
2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT 1111, 'YODA', 'JEDI', NULL, hiredate, sal, comm, NULL
4 FROM emp
5 WHERE ename = 'KING';

1 row created.

SQL> SELECT emp.empno, emp.ename, dept.deptno, dept.dname
2 FROM emp
3 INNER JOIN dept ON dept.deptno = emp.deptno;

EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING

14 rows selected.

SQL> SELECT emp.empno, emp.ename, dept.deptno, dept.dname
2 FROM emp
3 FULL JOIN dept ON dept.deptno = emp.deptno;

16 rows selected.

SQL> SELECT emp.empno, emp.ename, dept.deptno, dept.dname
2 FROM emp
LEFT JOIN dept ON dept.deptno = emp.deptno
4 UNION ALL
5 SELECT emp.empno, emp.ename, dept.deptno, dept.dname
6 FROM emp
7 RIGHT JOIN dept ON dept.deptno = emp.deptno
8 WHERE emp.empno IS NULL;

EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7902 FORD 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7844 TURNER 30 SALES
7900 JAMES 30 SALES
1111 YODA
40 OPERATIONS