Oracle专题7之多表查询

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle专题7之多表查询，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1、笛卡尔集

  • 笛卡尔集是集合中的一种。假设A和B都是集合，A和B的笛卡尔集用 A * B来表示。即A * B所形成的集合叫做笛卡尔集。
  • 下表中，部门表员工表即为部门表和员工表的笛卡尔集。（可以看出，部门表员工表中有三条数据并不正确。）

a、演示笛卡尔集

  • 创建部门表、和员工表并添加数据：
sql> create table 部门表(deptno number primary key,dname  varchar2(10));
		表已创建。
		sql> create table 员工表(empno number primary key,ename varchar2(10),deptno number,foreign key(deptno) references 部门表(deptno));
		表已创建。
		sql> insert into 部门表(deptno,dname) values(10,'销售部');
		已创建 1 行。
		sql> insert into 部门表(deptno,dname) values(20,'人事部');
		已创建 1 行。
		sql> insert into 员工表(empno,ename,deptno) values(1,'张三',10);
		已创建 1 行。
		sql> insert into 员工表(empno,deptno) values(2,'李四',20);
		已创建 1 行。
		sql> insert into 员工表(empno,deptno) values(3,'王五',10);
		已创建 1 行。
	```
	-  部门表和员工表的集合：
sql> select empno,员工表.deptno,部门表.deptno,dname from 部门表， 员工表;
			 EMPNO ENAME          DEPTNO     DEPTNO DNAME
					 1           张三                      10         10            销售部
					 2          李四                       20         10            销售部
					 3          王五                       10         10            销售部
					 1          张三                       10         20            人事部
					 2          李四                       20         20            人事部
					 3          王五                      10          20            人事部
	已选择6行。
```

b、sql语句多表查询显示满足条件的表的集合

sql> select empno,员工表.empno,dname from 部门表,员工表 where 部门表.deptno = 员工表.deptno;
			 EMPNO ENAME           EMPNO     DEPTNO DNAME
					 1             张三                1                 10             销售部
					 2            李四                2                  20            人事部
					 3            王五                3                  10            销售部
	```
-  为了避免笛卡尔集，可以在where子句中加入有效的连接条件。
-  连接条件至少有n-1个，n代表表的个数。
-  在表中有相同列时，在列名之前加上表名前缀。
# 2、等值与非等值连接
-   等值连接：是指使用等值比较符（=）指定连接条件的查询。举例：查询员工信息，要求显示：员工号、姓名、职位、部门名称。
-   使用表名前缀在多个表中区分相同的列：
sql> select empno,job,dname from emp,dept where emp.deptno = dept.deptno;
		 EMPNO ENAME      JOB       DNAME
			7934 MILLER     CLERK     ACCOUNTING
			7782 CLARK      MANAGER   ACCOUNTING
			7839 KING       PRESIDENT ACCOUNTING
			7566 JONES      MANAGER   RESEARCH
			7951 EASON      ANALYST   RESEARCH
			7369 G_EASON    CLERK     RESEARCH
			7902 FORD       ANALYST   RESEARCH
			7876 ADAMS      CLERK     RESEARCH
			7788 SCOTT      ANALYST   RESEARCH
			7499 ALLEN      SALESMAN  SALES
			7844 TURNER     SALESMAN  SALES
			7900 JAMES      CLERK     SALES
			7521 WARD       SALESMAN  SALES
			7698 BLAKE      MANAGER   SALES
			7654 MARTIN     SALESMAN  SALES
已选择15行。
```
  • 使用表名前缀可以提高执行效率：
sql> select emp.empno,emp.ename,emp.job,dept.dname from emp,dept where emp.deptno = dept.deptno;
			 EMPNO ENAME      JOB       DNAME
				7934 MILLER     CLERK     ACCOUNTING
				7782 CLARK      MANAGER   ACCOUNTING
				7839 KING       PRESIDENT ACCOUNTING
				7566 JONES      MANAGER   RESEARCH
				7951 EASON      ANALYST   RESEARCH
				7369 G_EASON    CLERK     RESEARCH
				7902 FORD       ANALYST   RESEARCH
				7876 ADAMS      CLERK     RESEARCH
				7788 SCOTT      ANALYST   RESEARCH
				7499 ALLEN      SALESMAN  SALES
				7844 TURNER     SALESMAN  SALES
				7900 JAMES      CLERK     SALES
				7521 WARD       SALESMAN  SALES
				7698 BLAKE      MANAGER   SALES
				7654 MARTIN     SALESMAN  SALES
	已选择15行。
	```
	-  使用表的别名，可以简化连接查询并提高查询性能：
sql> select e.empno,e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno;
		 EMPNO ENAME      JOB       DNAME
			7934 MILLER     CLERK     ACCOUNTING
			7782 CLARK      MANAGER   ACCOUNTING
			7839 KING       PRESIDENT ACCOUNTING
			7566 JONES      MANAGER   RESEARCH
			7951 EASON      ANALYST   RESEARCH
			7369 G_EASON    CLERK     RESEARCH
			7902 FORD       ANALYST   RESEARCH
			7876 ADAMS      CLERK     RESEARCH
			7788 SCOTT      ANALYST   RESEARCH
			7499 ALLEN      SALESMAN  SALES
			7844 TURNER     SALESMAN  SALES
			7900 JAMES      CLERK     SALES
			7521 WARD       SALESMAN  SALES
			7698 BLAKE      MANAGER   SALES
			7654 MARTIN     SALESMAN  SALES
已选择15行。
```
  • 多个连接条件时使用AND操作符：在连接查询中，当有多个连接条件时，使用AND指定其他条件。
sql> select e.empno,dept d where e.deptno = d.deptno and e.deptno = 10;
			 EMPNO ENAME      JOB       DNAME
				7782 CLARK      MANAGER   ACCOUNTING
				7839 KING       PRESIDENT ACCOUNTING
				7934 MILLER     CLERK     ACCOUNTING
	```
	-  不等值连接：不等值连接是指使用除等值比较符之外的其他比较操作符执行连接查询。举例：显示所有员工的员工号、姓名、工资以及其工资的等级。
sql> select * from salgrade;
		 GRADE      LOSAL      HISAL
				 1        700       1200
				 2       1201       1400
				 3       1401       2000
				 4       2001       3000
				 5       3001       9999
 

sql> select e.empno,e.sal,s.grade from emp e,salgrade s where e.sal between losal and hisal;
		 EMPNO ENAME             SAL      GRADE
			7369 G_EASON           800          1
			7900 JAMES             950          1
			7876 ADAMS            1100          1
			7521 WARD             1250          2
			7654 MARTIN           1250          2
			7934 MILLER           1300          2
			7844 TURNER           1500          3
			7499 ALLEN            1600          3
			7782 CLARK            2450          4
			7698 BLAKE            2850          4
			7566 JONES            2975          4
			7951 EASON            3000          4
			7902 FORD             3000          4
			7788 SCOTT            3000          4
			7839 KING             5000          5
已选择15行。
```
## 3、Oracle外连接
-  外连接是标准连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的部分记录。
-  举例：按照部门统计员工的人数，要求显示：部门号，部门名称，和人数。
-  普通的表连接：（发现没有显示DEPTNO为40的记录，原因是该部门没有员工，但是本例中需要显示该记录。）
 

sql> select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno = e.deptno group by d.deptno,d.dname;
    DEPTNO DNAME          COUNT(E.EMPNO)
        10 ACCOUNTING                  3
        20 RESEARCH                    6
        30 SALES                       6
 

 

     
 
  • 外连接是使用（+）操作符来完成的。
    •  
 
  • 右（外）连接语法格式：SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column(+) = table2.column;（返回table2表中的所有记录，返回table1表中所有满足条件的记录）
    •  
 
  • 左（外）连接语法格式：SELECT table1.column,table2 WHERE table1,table2 WHERE table1.column = table2.column(+);（返回table1表中的所有记录，返回table2表中所有满足条件的记录）
    •  

 

sql> select d.deptno,emp e where d.deptno = e.deptno(+) group by d.deptno,d.dname;
    DEPTNO DNAME          COUNT(E.EMPNO)
        10 ACCOUNTING                  3
        40 OPERATIONS                  0
        20 RESEARCH                    6
        30 SALES                       6
sql>
 

4、Oracle自连接
 

     
 
  • 自连接的实质是：将同一张表看成是多张表。
    •  
 
  • 举例：查询所有员工的姓名以及其直属上级的姓名。
    •  

 

sql> select e.ename 员工名,m.ename 直属上级 from emp e,emp m where e.mgr = m.empno;
	员工名     直属上级
	FORD       JONES
	SCOTT      JONES
	EASON      JONES
	JAMES      BLAKE
	TURNER     BLAKE
	MARTIN     BLAKE
	WARD       BLAKE
	ALLEN      BLAKE
	MILLER     CLARK
	ADAMS      SCOTT
	CLARK      KING
	BLAKE      KING
	JONES      KING
	G_EASON    FORD
	已选择14行。
	sql>
	```
	# 5、sql1999连接
-  为了简化连接查询，使得连接查询更加直观、更容易编写。sql：1999标准为连接查询提供新语法，如下所示：SELECT table1.column_name,table2.column_name FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)] | [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];。
## a、交叉连接cross join（了解）
-  返回笛卡尔集的操作：select d.dname,d.deptno,e.deptno from dept d,emp e;
-  crosss join连接也用于生成两张表的笛卡尔集（叉集）。即：select d.dname,e.deptno from dept d cross join emp e;
 

sql> select d.dname,e.deptno from dept d cross join emp e;
			DNAME          ENAME          DEPTNO     DEPTNO
			ACCOUNTING     EASON              10         20
			ACCOUNTING     G_EASON            10         20
			ACCOUNTING     ALLEN              10         30
			ACCOUNTING     WARD               10         30
			ACCOUNTING     JONES              10         20
			ACCOUNTING     MARTIN             10         30
			ACCOUNTING     BLAKE              10         30
			ACCOUNTING     CLARK              10         10
			ACCOUNTING     SCOTT              10         20
			ACCOUNTING     KING               10         10
			已选择60行。
 

## b、自然连接NATURAL JOIN
-  自然连接是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。语法格式为：SELECT table1.column_name,table2.column_name FROM table1 NATURAL JOIN table2;
-  举例：查询员工名、工资以及所在部门名称
 

sql> select e.ename,d.dname from dept d natural join emp e;
ENAME             SAL DNAME
MILLER           1300 ACCOUNTING
CLARK            2450 ACCOUNTING
KING             5000 ACCOUNTING
JONES            2975 RESEARCH
EASON            3000 RESEARCH
G_EASON           800 RESEARCH
FORD             3000 RESEARCH
ADAMS            1100 RESEARCH
SCOTT            3000 RESEARCH
ALLEN            1600 SALES
TURNER           1500 SALES
JAMES             950 SALES
WARD             1250 SALES
BLAKE            2850 SALES
MARTIN           1250 SALES
已选择15行。
 

## c、内连接
-  返回两个表中相匹配的数据（只返回满足条件的数据）。等值连接、非等值连接、自然连接都数据内连接。
-  使用USING子句建立相等连接。例如：SELECT e.ename,d.dname from dept d join emp e using(deptno);
 

sql> SELECT e.ename,d.dname from dept d join emp e using(deptno);
ENAME             SAL DNAME
MILLER           1300 ACCOUNTING
CLARK            2450 ACCOUNTING
KING             5000 ACCOUNTING
JONES            2975 RESEARCH
EASON            3000 RESEARCH
G_EASON           800 RESEARCH
FORD             3000 RESEARCH
ADAMS            1100 RESEARCH
SCOTT            3000 RESEARCH
ALLEN            1600 SALES
TURNER           1500 SALES
JAMES             950 SALES
WARD             1250 SALES
BLAKE            2850 SALES
MARTIN           1250 SALES
已选择15行。
```
 

 

     
 
  • 用ON子句建立相等连接。例如：SELECT e.ename,d.dname from dept d join emp e on e.deptno = d.deptno;
    •  

 

sql> SELECT e.ename,d.dname from dept d join emp e on e.deptno = d.deptno;
	ENAME             SAL DNAME
	MILLER           1300 ACCOUNTING
	CLARK            2450 ACCOUNTING
	KING             5000 ACCOUNTING
	JONES            2975 RESEARCH
	EASON            3000 RESEARCH
	G_EASON           800 RESEARCH
	FORD             3000 RESEARCH
	ADAMS            1100 RESEARCH
	SCOTT            3000 RESEARCH
	ALLEN            1600 SALES
	TURNER           1500 SALES
	JAMES             950 SALES
	WARD             1250 SALES
	BLAKE            2850 SALES
	MARTIN           1250 SALES
	已选择15行。
 

d、左（外）连接
 

     
 
  • 左连接用于返回满足连接条件的数据，以及不满足连接条件的左边表的其他数据。（即：左表的全部记录，右表满足条件的记录）
    •  
 
  • 在sql：1999标准中，左连接是通过LEFT[OUTER] JOIN选项来实现的。如：select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;
    •  

 

sql> select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;
	ENAME             SAL DNAME
	MILLER           1300 ACCOUNTING
	CLARK            2450 ACCOUNTING
	KING             5000 ACCOUNTING
	JONES            2975 RESEARCH
	EASON            3000 RESEARCH
	G_EASON           800 RESEARCH
	FORD             3000 RESEARCH
	ADAMS            1100 RESEARCH
	SCOTT            3000 RESEARCH
	ALLEN            1600 SALES
	TURNER           1500 SALES
	JAMES             950 SALES
	WARD             1250 SALES
	BLAKE            2850 SALES
	MARTIN           1250 SALES
												OPERATIONS
	已选择16行。
 

e、右（外）连接
 

     
 
  • 右连接用于返回满足连接条件的左边表的数据，以及不满足连接条件的右边表的其他数据。（即：右表中的全部记录，左表满足条件的记录。）示例：select e.ename,d.dname from dept d right join emp e on e.deptno = d.deptno;
    •  

 

sql> select e.ename,d.dname from dept d right join emp e on e.deptno = d.deptno;
		ENAME             SAL DNAME
		MILLER           1300 ACCOUNTING
		KING               5000 ACCOUNTING
		CLARK            2450 ACCOUNTING
		FORD             3000 RESEARCH
		ADAMS            1100 RESEARCH
		SCOTT            3000 RESEARCH
		JONES            2975 RESEARCH
		G_EASON       800 RESEARCH
		EASON            3000 RESEARCH
		JAMES             950 SALES
		TURNER         1500 SALES
		BLAKE            2850 SALES
		MARTIN           1250 SALES
		WARD             1250 SALES
		ALLEN             1600 SALES
		已选择15行。
 

f、完全（外）连接
 

     
 
  • 完全连接用于返回满足连接条件的数据，以及不满足链接条件的左表和右表中的其他数据。（即：左表和右表的全部记录。）
    •  
 
  • 在sql：1999标准中，完全连接是通过制定FULL [OUTER] JOIN 选项来实现的。示例：select e.ename,d.dname from dept d full join emp e on e.deptno = d.deptno;
    •  

 

sql> select e.ename,d.dname from dept d full join emp e on e.deptno = d.deptno;
	ENAME             SAL DNAME
	EASON            3000 RESEARCH
	G_EASON           800 RESEARCH
	ALLEN            1600 SALES
	WARD             1250 SALES
	JONES            2975 RESEARCH
	MARTIN           1250 SALES
	BLAKE            2850 SALES
	CLARK            2450 ACCOUNTING
	SCOTT            3000 RESEARCH
	KING             5000 ACCOUNTING
	TURNER           1500 SALES
	ADAMS            1100 RESEARCH
	JAMES             950 SALES
	FORD             3000 RESEARCH
	MILLER           1300 ACCOUNTING
												OPERATIONS
	已选择16行。
 

6、set运算符
 

     
 
  • set运算符，即集合运算符专门用于合并多条SELECT语句的结果，包括4种：UNION/UNION ALL 并集、INTERSECT 交集、MINUS 差集。
    •  
 
  • 例如有集合A和集合B，那么集合A有1、2、3，集合B有3、4。
    •  
 
  • A UNION B有：1、2、3、4；A UNION ALL B有：1、2、3、3 、4；
    •  
 
  • A INTERSECT B有：3；A MINUS B有：1、2；B MINUS A有：4； 
    •  

 

a、UNION
 

     
 
  • UNION操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果进行升序排序。
    •  
 
  • 举例：合并显示emp02表和emp02表中所有雇员的部门编号、员工号、员工姓名。
    •  

 

sql> create table emp01 as select * from emp where deptno in(10,20);
		表已创建。
		sql> create table emp02 as select * from emp where deptno in(20,30);
		表已创建。
		sql> select deptno,empno,ename from emp01 union select deptno,ename from emp02;
			DEPTNO      EMPNO ENAME
					10       7782 CLARK
					10       7839 KING
					10       7934 MILLER
					20       7369 G_EASON
					20       7566 JONES
					20       7788 SCOTT
					20       7876 ADAMS
					20       7902 FORD
					20       7951 EASON
					30       7499 ALLEN
					30       7521 WARD
					30       7654 MARTIN
					30       7698 BLAKE
					30       7844 TURNER
					30       7900 JAMES
	已选择15行。
 

b、UNION ALL
 

     
 
  • union all 操作符用于取得两个结果集的并集，单与union操作符不同，该操作符不会取消重复行，并且不会对结果集数据进行排序。
    •  

 

sql> select deptno,ename from emp01 union all select deptno,ename from emp02;
			DEPTNO      EMPNO ENAME
					20       7951 EASON
					20       7369 G_EASON
					20       7566 JONES
					......
					30       7844 TURNER
					20       7876 ADAMS
					30       7900 JAMES
					20       7902 FORD
	已选择21行。
 

c、INTERSECT
 

     
 
  • intersect操作符用于取得两个结果集的交集，当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列的结果进行升序排序。
    •  

 

sql> select deptno,ename from emp01 intersect select deptno,ename from emp02;
			DEPTNO      EMPNO ENAME
					20       7369 G_EASON
					20       7566 JONES
					20       7788 SCOTT
					20       7876 ADAMS
					20       7902 FORD
					20       7951 EASON
	已选择6行。
 

d、MINUS
 

     
 
  • minus操作符用于取得两个结果集中的差集，当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列的结果集进行升序排序。
    •  

 

sql> select deptno,ename from emp01 minus select deptno,ename from emp02;
			DEPTNO      EMPNO ENAME
					10       7782 CLARK
					10       7839 KING
					10       7934 MILLER
	```
## e、控制结果排序
-  当使用集合操作符UNION、INTERSECT和MINUS时，默认情况下会自动基于第一列进行升序排序；而当使用集合操作符UNION ALL时，不会进行排序，为了控制结果的排序顺序，可以使用ORDER BY子句。（如果两个表查询的结果中列名相同，则可以使用列名名称；如果两个表中的列名不同，则必须使用列位置，1表示基于第1列，2表示基于第2列）
-  示例：select deptno,ename from emp02 order by 2;
 

sql> select deptno,ename from emp02 order by empn
		DEPTNO      EMPNO ENAME
				20       7369 G_EASON
				20       7369 G_EASON
				30       7499 ALLEN
				30       7521 WARD
				20       7566 JONES
				......
				20       7902 FORD
				10       7934 MILLER
				20       7951 EASON
				20       7951 EASON
已选择21行。
sql> select deptno,ename from emp02 order by
		DEPTNO      EMPNO ENAME
				20       7369 G_EASON
				20       7369 G_EASON
				30       7499 ALLEN
				30       7521 WARD
			......
				20       7902 FORD
				10       7934 MILLER
				20       7951 EASON
				20       7951 EASON
已选择21行。
 

## f、使用set操作符的注意事项
-  在SELECT列表中的列名和表达式在数量和数据类型上要相对应。
 原文链接：https://www.f2er.com/oracle/206806.html

      

      
                 
              

            

          

          
          
          

            

              




            

          


          

            

              

                  
    
猜你在找的Oracle相关文章



                                    
[Oracle]记一次由sequence引发的enq sv-contention等待事件

                                
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...

                                
                                
                            



                                    
oracle统计信息的锁定与解锁

                                
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...

                                
                                
                            



                                    
【转】RMAN删除过期备份或非过期备份

                                
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...

                                
                                
                            



                                    
数据恢复顾问（DRA）

                                
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...

                                
                                
                            



                                    
RMAN备份与恢复（一）--认识RMAN

                                
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...

                                
                                
                            







                                    
RMAN备份与恢复（二）--RMAN常用操作学习

                                
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...

                                
                                
                            



                                    
ORA-00245问题总结

                                
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...

                                
                                
                            



                                    
使用RMAN对数据文件进行恢复

                                
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...

                                
                                
                            



                                    
Oracle使用fy_recover_data恢复truncate删除的数据

                                
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...

                                
                                
                            



                                    
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库

                                
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...

                                
                                
                            







            

          

        

        
        
        
        

          
          

            

              

                
                
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC

                

              

            

          

          
         
          

            

              
 



              

            

          

          
          
          
          

            

              

                
                
              

            

          

          


          
          

            

              

                
                
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问