09-Oracle入门之多表查询
发布时间:2020-12-12 15:28:34 所属栏目:百科 来源:网络整理
导读:笛卡尔集 笛卡尔集会在下面条件下产生: 省略连接条件 连接条件无效 所有表中的所有行互相连接 为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。 在实际运行环境下,应避免使用全笛卡尔集。 列相加,行相乘 SQL ed 已写入 file afiedt.buf 1 select cou
笛卡尔集
SQL> ed
已写入 file afiedt.buf
1 select count(*) 2* from dept,emp SQL> / COUNT(*)
----------
56
通过连接条件可以避免笛卡尔积 1 select * 2 from dept d,emp e 3* where e.deptno = d.deptno SQL> /
连接的类型使用连接在多个表中查询数据的一般套路: SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column1 = table2.column2;
等值连接一般套路SELECT employees.employee_id,employees.last_name,employees.department_id,departments.department_id,departments.location_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;
多个连接条件与 AND 操作符区分重复的列名
表的别名
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id;
连接多个表非等值连接SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
查询员工信息,员工号,姓名,月薪,薪水级别 SQL> ed
Wrote file afiedt.buf
1 select e.empno,e.ename,e.sal,s.grade
2 from emp e,SALGRADE s
3* where e.sal >= s.losal and e.sal <=s.hisal
4 /
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 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
7902 FORD 3000 4
7788 SCOTT 3000 4
7839 KING 5000 5
1 tom_abc 8000 5
15 rows selected.
外连接
SELECT table1.column,table2 WHERE table1.column(+) = table2.column;
SELECT table1.column,table2 WHERE table1.column = table2.column(+);
SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id(+) = d.department_id ;
按部门统计员工人数: 部门号 部门名称 各部门人数
SQL> ed
Wrote file afiedt.buf
1 select d.deptno,d.dname,count(*) 2 from dept d,emp e 3 where d.deptno = e.deptno 4* group by d.deptno,d.dname SQL> / DEPTNO DNAME COUNT(*)
---------- -------------- ----------
10 ACCOUNTING 4
20 RESEARCH 5
30 SALES 6
40号部门为什么没有被统计 SQL> ed
Wrote file afiedt.buf
1 select d.deptno,d.dname,count(e.empno)
2 from dept d,emp e
3 where d.deptno = e.deptno(+)
4* group by d.deptno,d.dname
SQL> /
DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 4
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6
对于这类希望把不满足等值条件(这里是where d.deptno = e.deptno)的数据,也显示出来,需要用到外链接。
自连接SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker,employees manager WHERE worker.manager_id = manager.employee_id ;
查询员工信息,老板信息,显示: xxx的老板是xxx
不能显示大老板: select e.ename || '的老板是'|| b.ename
from emp e,emp b
where e.mgr = b.empno
显示大老板: select e.ename || '的老板是'|| b.ename
from emp e,emp b
where e.mgr = b.empno(+)
优化显示: QL> ed
Wrote file afiedt.buf
1 select e.ename || ' works for '|| nvl(b.ename,'himself')
2 from emp e,emp b
3* where e.mgr = b.empno(+)
SQL> /
E.ENAME||'WORKSFOR'||NVL(B.ENAME,---------------------------------
FORD works for JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
JONES works for KING
SMITH works for FORD
KING works for himself
tom_abc works for himself
15 rows selected.
使用SQL: 1999 语法连接使用连接从多个表中查询数据 SELECT table1.column,table2.column 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)];
叉集
SELECT last_name,department_name FROM employees CROSS JOIN departments ;
自然连接
SELECT department_id,department_name,location_id,city FROM departments NATURAL JOIN locations ;
使用 USING 子句创建连接
USING 子句 SELECT e.employee_id,e.last_name,d.location_id FROM employees e JOIN departments d USING (department_id) ;
使用ON 子句创建连接
SELECT e.employee_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
使用 ON 子句创建多表连接 SELECT employee_id,city,department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;
内连接和外连接再深入
左外联接SELECT e.last_name,e.department_id,d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
右外联接SELECT e.last_name,d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
满外联接SELECT e.last_name,d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |