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,ename,deptno) values(2,'李四',20);
已创建 1 行。
SQL> insert into 员工表(empno,ename,deptno) values(3,'王五',10);
已创建 1 行。
SQL> select empno, ename, 员工表.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行。
SQL> select empno, ename, 员工表.empno, 部门表.deptno, dname from 部门表, 员工表 where 部门表.deptno = 员工表.deptno;
EMPNO ENAME EMPNO DEPTNO DNAME
1 张三 1 10 销售部
2 李四 2 20 人事部
3 王五 3 10 销售部
SQL> select empno, ename, 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, e.ename, e.job, d.dname from emp e, 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.ename, 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行。
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, 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
40 OPERATIONS 0
20 RESEARCH 6
30 SALES 6
SQL>
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>
SQL> select d.dname, e.ename, d.deptno, 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行。
SQL> select e.ename, e.sal, 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行。
SQL> SELECT e.ename, e.sal, 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, e.sal, 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行。
SQL> select e.ename, e.sal, 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行。
SQL> select e.ename, e.sal, 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行。
SQL> select e.ename, e.sal, 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行。
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, empno, 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行。
SQL> select deptno, empno, ename from emp01 union all select deptno, empno, 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行。
SQL> select deptno, empno, ename from emp01 intersect select deptno, empno, 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行。
SQL> select deptno, empno, ename from emp01 minus select deptno, empno, ename from emp02;
DEPTNO EMPNO ENAME
10 7782 CLARK
10 7839 KING
10 7934 MILLER
SQL> select deptno, empno, ename from emp01 union all select deptno, empno, 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, empno, ename from emp01 union all select deptno, empno, 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行。
网站题目:Oracle专题7之多表查询
网站路径:https://www.cdcxhl.com/article4/gpcjoe.html
成都网站建设公司_创新互联,为您提供网站策划、网站导航、、手机网站建设、企业建站、网站收录
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联