本文共 39322 字，大约阅读时间需要 131 分钟。
1查询emp表中数据量 14条数据
OracleSQL> select count(*) from emp; COUNT(*)---------- 14
PPAS
scott=# select count(*) from emp; count ------- 14(1 row)
2查询dept表中的数据库 4条数据
OracleSQL> select count(*) from dept; COUNT(*)---------- 4
PPAS
scott=# select count(*) from dept; count ------- 4(1 row)
3查询所有的雇员和部门的全部详细信息
OracleSQL> select * from emp,dept; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW YORK 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW YORK 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 10 ACCOUNTING NEW YORK 7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLAS 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 30 SALES CHICAGO 7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 30 SALES CHICAGO 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO 7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO 7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOSTON 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOSTON 7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOSTON 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 40 OPERATIONS BOSTON 7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 40 OPERATIONS BOSTON 7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON 7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON 7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON56 rows selected.
PPAS
scott=# select * from emp,dept; empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc -------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+---------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 10 | ACCOUNTING | NEW YORK 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 30 | SALES | CHICAGO 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 40 | OPERATIONS | BOSTON 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK 30 | 20 | RESEARCH | DALLAS| 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More-- 30 | 30 | SALES | CHICAGO 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More-- 30 | 40 | OPERATIONS | BOSTON| 22-FEB-81 00:00:00 | 1250.00 | 500.00 | --More-- 20 | 10 | ACCOUNTING | NEW YORK02-APR-81 00:00:00 | 2975.00 | | --More-- 20 | 20 | RESEARCH | DALLAS| 02-APR-81 00:00:00 | 2975.00 | | --More-- 20 | 30 | SALES | CHICAGO 02-APR-81 00:00:00 | 2975.00 | | --More-- 20 | 40 | OPERATIONS | BOSTON| 02-APR-81 00:00:00 | 2975.00 | | --More-- 30 | 10 | ACCOUNTING | NEW YORK28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More-- 30 | 20 | RESEARCH | DALLAS| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More-- 30 | 30 | SALES | CHICAGO 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More-- 30 | 40 | OPERATIONS | BOSTON| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | --More-- 30 | 10 | ACCOUNTING | NEW YORK01-MAY-81 00:00:00 | 2850.00 | | --More-- 30 | 20 | RESEARCH | DALLAS| 01-MAY-81 00:00:00 | 2850.00 | | --More-- 30 | 30 | SALES | CHICAGO 01-MAY-81 00:00:00 | 2850.00 | | --More-- 30 | 40 | OPERATIONS | BOSTON| 01-MAY-81 00:00:00 | 2850.00 | | --More-- 10 | 10 | ACCOUNTING | NEW YORK09-JUN-81 00:00:00 | 2450.00 | | --More-- 10 | 20 | RESEARCH | DALLAS| 09-JUN-81 00:00:00 | 2450.00 | | --More-- 10 | 30 | SALES | CHICAGO 09-JUN-81 00:00:00 | 2450.00 | | --More-- 10 | 40 | OPERATIONS | BOSTON| 09-JUN-81 00:00:00 | 2450.00 | | --More-- 20 | 10 | ACCOUNTING | NEW YORK19-APR-87 00:00:00 | 3000.00 | | --More-- 20 | 20 | RESEARCH | DALLAS| 19-APR-87 00:00:00 | 3000.00 | | --More-- 20 | 30 | SALES | CHICAGO 19-APR-87 00:00:00 | 3000.00 | | --More-- 20 | 40 | OPERATIONS | BOSTON| 19-APR-87 00:00:00 | 3000.00 | | --More-- 10 | 10 | ACCOUNTING | NEW YORK17-NOV-81 00:00:00 | 5000.00 | | --More-- 10 | 20 | RESEARCH | DALLAS| 17-NOV-81 00:00:00 | 5000.00 | | --More-- 10 | 30 | SALES | CHICAGO 17-NOV-81 00:00:00 | 5000.00 | | --More-- 10 | 40 | OPERATIONS | BOSTON| 17-NOV-81 00:00:00 | 5000.00 | | --More-- 30 | 10 | ACCOUNTING | NEW YORK08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More-- 30 | 20 | RESEARCH | DALLAS| 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More-- 30 | 30 | SALES | CHICAGO 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More-- 30 | 40 | OPERATIONS | BOSTON| 08-SEP-81 00:00:00 | 1500.00 | 0.00 | --More-- 20 | 10 | ACCOUNTING | NEW YORK23-MAY-87 00:00:00 | 1100.00 | | --More-- 20 | 20 | RESEARCH | DALLAS| 23-MAY-87 00:00:00 | 1100.00 | | --More-- 20 | 30 | SALES | CHICAGO 23-MAY-87 00:00:00 | 1100.00 | | --More-- 20 | 40 | OPERATIONS | BOSTON| 23-MAY-87 00:00:00 | 1100.00 | | --More-- 30 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 950.00 | | --More-- 30 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 950.00 | | --More-- 30 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 950.00 | | --More-- 30 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 950.00 | | --More-- 20 | 10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 3000.00 | | --More-- 20 | 20 | RESEARCH | DALLAS| 03-DEC-81 00:00:00 | 3000.00 | | --More-- 20 | 30 | SALES | CHICAGO 03-DEC-81 00:00:00 | 3000.00 | | --More-- 20 | 40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 3000.00 | | --More-- 10 | 10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 | | --More-- 10 | 20 | RESEARCH | DALLAS| 23-JAN-82 00:00:00 | 1300.00 | | --More-- 10 | 30 | SALES | CHICAGO 23-JAN-82 00:00:00 | 1300.00 | | --More-- 10 | 40 | OPERATIONS | BOSTON| 23-JAN-82 00:00:00 | 1300.00 | | --More--(56 rows)
4消除查询结果中的笛卡尔积
OracleSQL> select * 2 from emp,dept 3 where emp.deptno=dept.deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO14 rows selected.
PPAS
scott=# select * scott-# from emp,deptscott-# where emp.deptno=dept.deptno; empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc -------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+---------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK(14 rows)
5查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
OracleSQL> select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc 2 from emp,dept 3 where emp.deptno=dept.deptno; EMPNO ENAME JOB SAL DNAME LOC---------- ---------- ---------- ---------- --------------- ---------- 7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK 7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK 7934 MILLER CLERK 1300 ACCOUNTING NEW YORK 7566 JONES MANAGER 2975 RESEARCH DALLAS 7902 FORD ANALYST 3000 RESEARCH DALLAS 7876 ADAMS CLERK 1100 RESEARCH DALLAS 7369 SMITH CLERK 800 RESEARCH DALLAS 7788 SCOTT ANALYST 3000 RESEARCH DALLAS 7521 WARD SALESMAN 1250 SALES CHICAGO 7844 TURNER SALESMAN 1500 SALES CHICAGO 7499 ALLEN SALESMAN 1600 SALES CHICAGO 7900 JAMES CLERK 950 SALES CHICAGO 7698 BLAKE MANAGER 2850 SALES CHICAGO 7654 MARTIN SALESMAN 1250 SALES CHICAGO14 rows selected.
PPAS
scott=# select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.locscott-# from emp,deptscott-# where emp.deptno=dept.deptno; empno | ename | job | sal | dname | loc -------+--------+-----------+---------+------------+---------- 7369 | SMITH | CLERK | 800.00 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 1600.00 | SALES | CHICAGO 7521 | WARD | SALESMAN | 1250.00 | SALES | CHICAGO 7566 | JONES | MANAGER | 2975.00 | RESEARCH | DALLAS 7654 | MARTIN | SALESMAN | 1250.00 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 2850.00 | SALES | CHICAGO 7782 | CLARK | MANAGER | 2450.00 | ACCOUNTING | NEW YORK 7788 | SCOTT | ANALYST | 3000.00 | RESEARCH | DALLAS 7839 | KING | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK 7844 | TURNER | SALESMAN | 1500.00 | SALES | CHICAGO 7876 | ADAMS | CLERK | 1100.00 | RESEARCH | DALLAS 7900 | JAMES | CLERK | 950.00 | SALES | CHICAGO 7902 | FORD | ANALYST | 3000.00 | RESEARCH | DALLAS 7934 | MILLER | CLERK | 1300.00 | ACCOUNTING | NEW YORK(14 rows)
6通过别名查询雇员的编号、姓名、职位、基本工资、部门名称、部门位置
OracleSQL> select e.empno,e.ename,e.job,e.sal,d.dname,d.loc 2 from emp e,dept d 3 where e.deptno=d.deptno; EMPNO ENAME JOB SAL DNAME LOC---------- ---------- ---------- ---------- --------------- ---------- 7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK 7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK 7934 MILLER CLERK 1300 ACCOUNTING NEW YORK 7566 JONES MANAGER 2975 RESEARCH DALLAS 7902 FORD ANALYST 3000 RESEARCH DALLAS 7876 ADAMS CLERK 1100 RESEARCH DALLAS 7369 SMITH CLERK 800 RESEARCH DALLAS 7788 SCOTT ANALYST 3000 RESEARCH DALLAS 7521 WARD SALESMAN 1250 SALES CHICAGO 7844 TURNER SALESMAN 1500 SALES CHICAGO 7499 ALLEN SALESMAN 1600 SALES CHICAGO 7900 JAMES CLERK 950 SALES CHICAGO 7698 BLAKE MANAGER 2850 SALES CHICAGO 7654 MARTIN SALESMAN 1250 SALES CHICAGO14 rows selected.
PPAS
scott=# select e.empno,e.ename,e.job,e.sal,d.dname,d.locscott-# from emp e,dept dscott-# where e.deptno=d.deptno; empno | ename | job | sal | dname | loc -------+--------+-----------+---------+------------+---------- 7369 | SMITH | CLERK | 800.00 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 1600.00 | SALES | CHICAGO 7521 | WARD | SALESMAN | 1250.00 | SALES | CHICAGO 7566 | JONES | MANAGER | 2975.00 | RESEARCH | DALLAS 7654 | MARTIN | SALESMAN | 1250.00 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 2850.00 | SALES | CHICAGO 7782 | CLARK | MANAGER | 2450.00 | ACCOUNTING | NEW YORK 7788 | SCOTT | ANALYST | 3000.00 | RESEARCH | DALLAS 7839 | KING | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK 7844 | TURNER | SALESMAN | 1500.00 | SALES | CHICAGO 7876 | ADAMS | CLERK | 1100.00 | RESEARCH | DALLAS 7900 | JAMES | CLERK | 950.00 | SALES | CHICAGO 7902 | FORD | ANALYST | 3000.00 | RESEARCH | DALLAS 7934 | MILLER | CLERK | 1300.00 | ACCOUNTING | NEW YORK(14 rows)
7查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
OracleSQL> select e.empno,e.ename,e.hiredate,e.sal,s.grade 2 from emp e,salgrade s 3 where e.sal between s.losal and s.hisal; EMPNO ENAME HIREDATE SAL GRADE---------- ---------- ------------ ---------- ---------- 7369 SMITH 17-DEC-80 800 1 7900 JAMES 03-DEC-81 950 1 7876 ADAMS 23-MAY-87 1100 1 7521 WARD 22-FEB-81 1250 2 7654 MARTIN 28-SEP-81 1250 2 7934 MILLER 23-JAN-82 1300 2 7844 TURNER 08-SEP-81 1500 3 7499 ALLEN 20-FEB-81 1600 3 7782 CLARK 09-JUN-81 2450 4 7698 BLAKE 01-MAY-81 2850 4 7566 JONES 02-APR-81 2975 4 7788 SCOTT 19-APR-87 3000 4 7902 FORD 03-DEC-81 3000 4 7839 KING 17-NOV-81 5000 514 rows selected.
PPAS
scott=# select e.empno,e.ename,e.hiredate,e.sal,s.gradescott-# from emp e,salgrade sscott-# where e.sal between s.losal and s.hisal; empno | ename | hiredate | sal | grade -------+--------+--------------------+---------+------- 7369 | SMITH | 17-DEC-80 00:00:00 | 800.00 | 1 7876 | ADAMS | 23-MAY-87 00:00:00 | 1100.00 | 1 7900 | JAMES | 03-DEC-81 00:00:00 | 950.00 | 1 7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | 2 7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | 2 7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | 2 7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | 3 7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | 3 7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | 4 7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | 4 7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | 4 7788 | SCOTT | 19-APR-87 00:00:00 | 3000.00 | 4 7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | 4 7839 | KING | 17-NOV-81 00:00:00 | 5000.00 | 5(14 rows)
8使用DECODE()函数完成查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
OracleSQL> select e.empno,e.ename,e.hiredate,e.sal, 2 DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade 3 from emp e,salgrade s 4 where e.sal between s.losal and s.hisal; EMPNO ENAME HIREDATE SAL GRADE---------- ---------- ------------ ---------- -------------------- 7369 SMITH 17-DEC-80 800 E等工资 7900 JAMES 03-DEC-81 950 E等工资 7876 ADAMS 23-MAY-87 1100 E等工资 7521 WARD 22-FEB-81 1250 D等工资 7654 MARTIN 28-SEP-81 1250 D等工资 7934 MILLER 23-JAN-82 1300 D等工资 7844 TURNER 08-SEP-81 1500 C等工资 7499 ALLEN 20-FEB-81 1600 C等工资 7782 CLARK 09-JUN-81 2450 B等工资 7698 BLAKE 01-MAY-81 2850 B等工资 7566 JONES 02-APR-81 2975 B等工资 7788 SCOTT 19-APR-87 3000 B等工资 7902 FORD 03-DEC-81 3000 B等工资 7839 KING 17-NOV-81 5000 A等工资14 rows selected.
PPAS
scott=# select e.empno,e.ename,e.hiredate,e.sal,scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')gradescott-# from emp e,salgrade sscott-# where e.sal between s.losal and s.hisal; empno | ename | hiredate | sal | grade -------+--------+--------------------+---------+--------- 7369 | SMITH | 17-DEC-80 00:00:00 | 800.00 | E等工资 7876 | ADAMS | 23-MAY-87 00:00:00 | 1100.00 | E等工资 7900 | JAMES | 03-DEC-81 00:00:00 | 950.00 | E等工资 7521 | WARD | 22-FEB-81 00:00:00 | 1250.00 | D等工资 7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | D等工资 7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | D等工资 7499 | ALLEN | 20-FEB-81 00:00:00 | 1600.00 | C等工资 7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | C等工资 7566 | JONES | 02-APR-81 00:00:00 | 2975.00 | B等工资 7698 | BLAKE | 01-MAY-81 00:00:00 | 2850.00 | B等工资 7782 | CLARK | 09-JUN-81 00:00:00 | 2450.00 | B等工资 7788 | SCOTT | 19-APR-87 00:00:00 | 3000.00 | B等工资 7902 | FORD | 03-DEC-81 00:00:00 | 3000.00 | B等工资 7839 | KING | 17-NOV-81 00:00:00 | 5000.00 | A等工资(14 rows)
9查询每个雇员的姓名、职位、基本工资、部门名称、工资所在公司的工资等级
OracleSQL> select e.ename,e.job,e.sal,d.dname,s.grade, 2 DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade 3 from emp e,dept d,salgrade s 4 where e.deptno=d.deptno and e.sal between s.losal and s.hisal;ENAME JOB SAL DNAME GRADE GRADE---------- ---------- ---------- --------------- ---------- --------------------KING PRESIDENT 5000 ACCOUNTING 5 A等工资FORD ANALYST 3000 RESEARCH 4 B等工资SCOTT ANALYST 3000 RESEARCH 4 B等工资JONES MANAGER 2975 RESEARCH 4 B等工资BLAKE MANAGER 2850 SALES 4 B等工资CLARK MANAGER 2450 ACCOUNTING 4 B等工资ALLEN SALESMAN 1600 SALES 3 C等工资TURNER SALESMAN 1500 SALES 3 C等工资MILLER CLERK 1300 ACCOUNTING 2 D等工资WARD SALESMAN 1250 SALES 2 D等工资MARTIN SALESMAN 1250 SALES 2 D等工资ADAMS CLERK 1100 RESEARCH 1 E等工资JAMES CLERK 950 SALES 1 E等工资SMITH CLERK 800 RESEARCH 1 E等工资14 rows selected.
PPAS
scott=# select e.ename,e.job,e.sal,d.dname,s.grade,scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')gradescott-# from emp e,dept d,salgrade sscott-# where e.deptno=d.deptno and e.sal between s.losal and s.hisal; ename | job | sal | dname | grade | grade --------+-----------+---------+------------+-------+--------- SMITH | CLERK | 800.00 | RESEARCH | 1 | E等工资 ADAMS | CLERK | 1100.00 | RESEARCH | 1 | E等工资 JAMES | CLERK | 950.00 | SALES | 1 | E等工资 WARD | SALESMAN | 1250.00 | SALES | 2 | D等工资 MARTIN | SALESMAN | 1250.00 | SALES | 2 | D等工资 MILLER | CLERK | 1300.00 | ACCOUNTING | 2 | D等工资 ALLEN | SALESMAN | 1600.00 | SALES | 3 | C等工资 TURNER | SALESMAN | 1500.00 | SALES | 3 | C等工资 JONES | MANAGER | 2975.00 | RESEARCH | 4 | B等工资 BLAKE | MANAGER | 2850.00 | SALES | 4 | B等工资 CLARK | MANAGER | 2450.00 | ACCOUNTING | 4 | B等工资 SCOTT | ANALYST | 3000.00 | RESEARCH | 4 | B等工资 FORD | ANALYST | 3000.00 | RESEARCH | 4 | B等工资 KING | PRESIDENT | 5000.00 | ACCOUNTING | 5 | A等工资(14 rows)
表的连接操作
10向emp表中 添加一条新的记录OracleSQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 2 values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);1 row created.SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- 8888 SongYuejie CLERK 7369 22-JUN-16 800 100 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1015 rows selected.
PPAS
scott=# insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)scott-# values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);INSERT 0 1scott=# select * from emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+------------+-----------+------+--------------------+---------+---------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 | (15 rows)
11将emp和dept表联合查询，使用内连接（等值连接）
OracleSQL> select * 2 from emp e,dept d 3 where e.deptno=d.deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ---------- 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO14 rows selected.
PPAS
scott=# select *scott-# from emp e,dept d scott-# where e.deptno=d.deptno; empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc -------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+---------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK(14 rows)
12使用左外连接，显示雇员8888的信息
OracleSQL> select * 2 from emp e,dept d 3 where e.deptno=d.deptno(+); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ---------- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 8888 SongYuejie CLERK 7369 22-JUN-16 800 10015 rows selected.
PPAS
scott=# select *scott-# from emp e,dept d scott-# where e.deptno=d.deptno(+); empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc -------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+---------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 | 20 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 | 30 | SALES | CHICAGO 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 | 30 | SALES | CHICAGO 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK 8888 | SongYuejie | CLERK | 7369 | 22-JUN-16 11:09:17 | 800.00 | 100.00 | | | | (15 rows)
本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！”
转载地址：http://eadeo.baihongyu.com/