Oracle专题7之多表查询
发布时间：2020-12-12 14:11:55 所属栏目：百科 来源：网络整理
导读：什么是多表查询？ 多表查询就是从多个表中获取数据。 1、笛卡尔集 笛卡尔集是集合中的一种。假设A和B都是集合，A和B的笛卡尔集用 A * B来表示。即A * B所形成的集合叫做笛卡尔集。 下表中，部门表 员工表即为部门表和员工表的笛卡尔集。（可以看出，部门表
1、笛卡尔集
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行。 ```
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
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行。 ```
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> 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、右（外）连接
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> 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运算符
a、UNION
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
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
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
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列表中的列名和表达式在数量和数据类型上要相对应。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |