Oracle学习03【持续更新】

前端之家收集整理的这篇文章主要介绍了Oracle学习03【持续更新】前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Oarcle继续学习中。。。。。。

/*

以下代码是对emp表/dept表/salgrade表进行显示宽度设置
*/
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
col dname for a14;
col loc for a14;
col grade for 9999;
set pagesize 20;

select * from emp;

  1. sql> select * from emp;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7369 SMITH CLERK 7902 17-12月-80 800 20
  6. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  7. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  8. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  9. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  10. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  11. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  12. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  13. 7839 KING PRESIDENT 17-11月-81 5000 10
  14. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  15. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  16. 7900 JAMES CLERK 7698 03-12月-81 950 30
  17. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  18. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  19.  
  20. 已选择14行。

select * from dept;

  1. sql> select * from dept;
  2.  
  3. DEPTNO DNAME LOC
  4. ------ -------------- --------------
  5. 10 ACCOUNTING NEW YORK
  6. 20 RESEARCH DALLAS
  7. 30 SALES CHICAGO
  8. 40 OPERATIONS BOSTON

select * from salgrade;

@H_404_30@sql> select * from salgrade; GRADE LOSAL HISAL ----- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

员工表和部门表的笛卡尔集(笛卡尔集表=列数之和,行数之积)
select * from dept,salgrade;
最终产生的表,列是各子表列之和,行是各子表行之积
笛卡尔集的结果无任何实际意思,我们得从笛卡尔集的结果中抽选中有意思的数据
如果通过表名.列名来取得某个表中的字段

  1. sql> select * from dept,salgrade;
  2.  
  3. DEPTNO DNAME LOC GRADE LOSAL HISAL
  4. ------ -------------- -------------- ----- ---------- ----------
  5. 10 ACCOUNTING NEW YORK 1 700 1200
  6. 10 ACCOUNTING NEW YORK 2 1201 1400
  7. 10 ACCOUNTING NEW YORK 3 1401 2000
  8. 10 ACCOUNTING NEW YORK 4 2001 3000
  9. 10 ACCOUNTING NEW YORK 5 3001 9999
  10. 20 RESEARCH DALLAS 1 700 1200
  11. 20 RESEARCH DALLAS 2 1201 1400
  12. 20 RESEARCH DALLAS 3 1401 2000
  13. 20 RESEARCH DALLAS 4 2001 3000
  14. 20 RESEARCH DALLAS 5 3001 9999
  15. 30 SALES CHICAGO 1 700 1200
  16. 30 SALES CHICAGO 2 1201 1400
  17. 30 SALES CHICAGO 3 1401 2000
  18. 30 SALES CHICAGO 4 2001 3000
  19. 30 SALES CHICAGO 5 3001 9999
  20. 40 OPERATIONS BOSTON 1 700 1200
  21. 40 OPERATIONS BOSTON 2 1201 1400
  22.  
  23. DEPTNO DNAME LOC GRADE LOSAL HISAL
  24. ------ -------------- -------------- ----- ---------- ----------
  25. 40 OPERATIONS BOSTON 3 1401 2000
  26. 40 OPERATIONS BOSTON 4 2001 3000
  27. 40 OPERATIONS BOSTON 5 3001 9999
  28.  
  29. 已选择20行。
等值连接,显示员工的编号,姓名,部门
select emp.empno,emp.ename,dept.dnamefrom emp,dept
where emp.deptno=dept.deptno;
  1. sql> select emp.empno,dept.dname from emp,dept where emp.deptno=
  2. 2 dept.deptno;
  3.  
  4. EMPNO ENAME DNAME
  5. ----- ---------- --------------
  6. 7782 CLARK ACCOUNTING
  7. 7839 KING ACCOUNTING
  8. 7934 MILLER ACCOUNTING
  9. 7566 JONES RESEARCH
  10. 7902 FORD RESEARCH
  11. 7876 ADAMS RESEARCH
  12. 7369 SMITH RESEARCH
  13. 7788 SCOTT RESEARCH
  14. 7521 WARD SALES
  15. 7844 TURNER SALES
  16. 7499 ALLEN SALES
  17. 7900 JAMES SALES
  18. 7698 BLAKE SALES
  19. 7654 MARTIN SALES
  20.  
  21. 已选择14行。
使用表别名,等值连接,显示员工的编号,姓名,部门
select e.empno,e.ename,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno;
oracle表名不能出现as关j键字,字段可以
  1. sql> select e.empno,d.dname from emp e,dept d where e.deptno=d.deptno;
  2.  
  3. EMPNO ENAME DNAME
  4. ----- ---------- --------------
  5. 7782 CLARK ACCOUNTING
  6. 7839 KING ACCOUNTING
  7. 7934 MILLER ACCOUNTING
  8. 7566 JONES RESEARCH
  9. 7902 FORD RESEARCH
  10. 7876 ADAMS RESEARCH
  11. 7369 SMITH RESEARCH
  12. 7788 SCOTT RESEARCH
  13. 7521 WARD SALES
  14. 7844 TURNER SALES
  15. 7499 ALLEN SALES
  16. 7900 JAMES SALES
  17. 7698 BLAKE SALES
  18. 7654 MARTIN SALES
  19.  
  20. 已选择14行。
不等值连接,显示员工的编号,姓名,月薪,级别,只要不是使用=号即就
select e.empno,e.sal,s.gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;
  1. sql> select e.empno,s.grade from emp e,salgrade s where e.sal betw
  2. een s.losal and s.hisal;
  3.  
  4. EMPNO ENAME SAL GRADE
  5. ----- ---------- ----- -----
  6. 7369 SMITH 800 1
  7. 7900 JAMES 950 1
  8. 7876 ADAMS 1100 1
  9. 7521 WARD 1250 2
  10. 7654 MARTIN 1250 2
  11. 7934 MILLER 1300 2
  12. 7844 TURNER 1500 3
  13. 7499 ALLEN 1600 3
  14. 7782 CLARK 2450 4
  15. 7698 BLAKE 2850 4
  16. 7566 JONES 2975 4
  17. 7788 SCOTT 3000 4
  18. 7902 FORD 3000 4
  19. 7839 KING 5000 5
  20.  
  21. 已选择14行。
【左外连接】,按部门,统计员工人数,显示部门号,部门名,人数
select d.deptno "部门号",d.dname "部门名",count(e.deptno) "人数" from dept d,emp e
where d.deptno=e.deptno(+) group by d.deptno,d.dname;
(+)号一定是出现在where子句中,
依据(+)出现在=号二个的位置不同,叫法不相:
(+)出现在=号左边,叫右[外]连接
(+)出现在=号右边,叫左[外]连接
依据=号二边,看哪一边少了值,就将(+)号加上少值的那边
  1. sql> select d.deptno "部门号",count(e.deptno) "人数" from dept
  2. d,emp e
  3. 2 where d.deptno=e.deptno(+) group by d.deptno,d.dname;
  4.  
  5. 部门号 部门名 人数
  6. ---------- ---------------------------- ----------
  7. 10 ACCOUNTING 3
  8. 40 OPERATIONS 0
  9. 20 RESEARCH 5
  10. 30 SALES 6
右外连接,按部门,统计员工人数,显示部门号,部门名,人数
select d.deptno "部门号",count(e.deptno) "人数"
from dept d,emp ewhere d.deptno(+)=e.deptnogroup by d.deptno,d.dname;
  1. sql> select d.deptno "部门号",count(e.deptno) "人数"
  2. 2 from dept d,emp e where d.deptno(+)=e.deptno group by d.deptno,d.dname;
  3.  
  4. 部门号 部门名 人数
  5. ---------- ---------------------------- ----------
  6. 10 ACCOUNTING 3
  7. 20 RESEARCH 5
  8. 30 SALES 6
自连接,显示"SMITH的老板是FORD"这种格式
select e.ename || '的老板是' || b.enamefrom emp e,emp bwhere e.mgr = b.empno;
原则:将一个表,看作二张不同的表
  1. sql> select e.ename || '的老板是' || b.ename from emp e,emp b where e.mgr = b
  2. .empno;
  3.  
  4. E.ENAME||'的老板是'||B.ENAME
  5. ----------------------------------------------------------------
  6. FORD的老板是JONES
  7. SCOTT的老板是JONES
  8. TURNER的老板是BLAKE
  9. ALLEN的老板是BLAKE
  10. WARD的老板是BLAKE
  11. JAMES的老板是BLAKE
  12. MARTIN的老板是BLAKE
  13. MILLER的老板是CLARK
  14. ADAMS的老板是SCOTT
  15. BLAKE的老板是KING
  16. JONES的老板是KING
  17. CLARK的老板是KING
  18. SMITH的老板是FORD
  19.  
  20. 已选择13行。


//------------------------------------------------------------------------------------------------------
查询工资比SCOTT高的员工信息
查询主要解决:条件是未知的查询,而且需要经过多个步骤才能完成。
1)SCOTT的工资?
select * from emp where ename='SCOTT';
  1. sql> select * from emp where ename='SCOTT';
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
2) 比3000高的员工信息?
select * from emp where sal>3000;
  1. sql> select * from emp where sal>3000;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7839 KING PRESIDENT 17-11月-81 5000 10
3)将1)和2)二步合二为一,形成主子查询
select *from emp where sal>(select salfrom empwhere ename='SCOTT');
  1. sql> select * from emp where sal>(select sal from emp where ename='SCOTT'
  2. );
  3.  
  4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  5. ----- ---------- ---------- ----- ------------ ----- ----- ------
  6. 7839 KING PRESIDENT 17-11月-81 5000 10
  1. <span style="font-family: Arial,Helvetica,sans-serif; background-color: rgb(255,255,255);"></span>
查询部门名为'ACCOUNTING'的员工信息(方式一:子查询
1)查询部门名为'ACCOUNTING'的部门号
select deptno from dept where dname='ACCOUNTING';
  1. sql> select deptno from dept where dname='ACCOUNTING';
  2.  
  3. DEPTNO
  4. ------
  5. 10
2)根据10号查询对应的员工信息
select * from emp where deptno=10;
  1. sql> select * from emp where deptno=10;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  6. 7839 KING PRESIDENT 17-11月-81 5000 10
  7. 7934 MILLER CLERK 7782 23-1 -82 1300 10
3)将1)和2)二步合二为一,形成主子查询
select *
from emp
where deptno=(
select deptno
from dept
where dname='ACCOUNTING');
  1. sql> select *
  2. 2 from emp
  3. 3 where deptno=(
  4. 4 select deptno
  5. 5 from dept
  6. 6 where dname='ACCOUNTING' );
  7.  
  8. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  9. ----- ---------- ---------- ----- ------------ ----- ----- ------
  10. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  11. 7839 KING PRESIDENT 17-11月-81 5000 10
  12. 7934 MILLER CLERK 7782 23-1 -82 1300 10
查询部门名为'ACCOUNTING'的员工信息(方式二:多表查询,优先)
select e.ename,e.deptnofrom emp e,dept d
where d.deptno=e.deptno and d.dname='ACCOUNTING';
  1. sql> select e.ename,e.deptno from emp e,dept d
  2. 2 where d.deptno=e.deptno and d.dname='ACCOUNTING';
  3.  
  4. ENAME SAL DEPTNO
  5. ---------- ----- ------
  6. CLARK 2450 10
  7. KING 5000 10
  8. MILLER 1300 10

查询工资最低的员工信息
1)查询工资最低是多少钱?
select min(sal) from emp;
  1. sql> select min(sal)from emp;
  2.  
  3. MIN(SAL)
  4. ----------
  5. 800
2)查询800的员工信息
select * from emp where sal=800;
  1. sql> select * from emp where sal=800;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7369 SMITH CLERK 7902 17-12月-80 800 20
3)将1)和2)二步合二为一,形成主子查询
select *from empwhere sal=(select min(sal)from emp);
  1. sql> select * from emp where sal=(select min(sal) from emp );
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7369 SMITH CLERK 7902 17-12月-80 800 20
查询部门名为'ACCOUNTING'或'SALES'的员工(方式一:子查询
select *
from emp
where deptno = (
select deptno
from dept
where dname='ACCOUNTING' or dname='SALES'
);
-------------------------【以上代码出错,原因在于主查询只需要一个值,但是子查询确返回多个值】
select *
from emp
where deptno in (
select deptno
from dept
where dname='ACCOUNTING' or dname='SALES'
);
  1. sql> select *
  2. 2 from emp
  3. 3 where deptno in (
  4. 4 select deptno
  5. 5 from dept
  6. 6 where dname='ACCOUNTING' or dname='SALES'
  7. 7 );
  8.  
  9. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  10. ----- ---------- ---------- ----- ------------ ----- ----- ------
  11. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  12. 7839 KING PRESIDENT 17-11月-81 5000 10
  13. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  14. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  15. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  16. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  17. 7900 JAMES CLERK 7698 03-12月-81 950 30
  18. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  19. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  20.  
  21. 已选择9行。
查询部门名为'ACCOUNTING'或'SALES'的员工(方式二:多表查询
select e.* from dept d,emp e

where d.deptno=e.deptno and d.dname in('ACCOUNTING','SALES');

  1. <pre name="code" class="sql">sql> select e.* from dept d,emp e
  2. 2 where d.deptno=e.deptno and d.dname in('ACCOUNTING','SALES');
  3.  
  4. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  5. ----- ---------- ---------- ----- ------------ ----- ----- ------
  6. 7782 CLARK MANAGER 7839 09-6月 -81 2450 10
  7. 7839 KING PRESIDENT 17-11月-81 5000 10
  8. 7934 MILLER CLERK 7782 23-1月 -82 1300 10
  9. 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
  10. 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
  11. 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
  12. 7900 JAMES CLERK 7698 03-12月-81 950 30
  13. 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
  14. 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
  15.  
  16. 已选择9行。


  1.  
查询工资比10号部门【任意】一个员工【低】的员工信息1)查询10号部门的员工工资 select * from emp where deptno=10;
  1. sql> select * from emp where deptno=10;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  6. 7839 KING PRESIDENT 17-11月-81 5000 10
  7. 7934 MILLER CLERK 7782 23-1 -82 1300 10
2)查询员工比2450或5000或1300低的员
select *
from emp
where sal<2450 or sal<5000 or sal<1300;
  1. sql> select *
  2. 2 from emp
  3. 3 where sal<2450 or sal<5000 or sal<1300;
  4.  
  5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ----- ---------- ---------- ----- ------------ ----- ----- ------
  7. 7369 SMITH CLERK 7902 17-12月-80 800 20
  8. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  9. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  10. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  11. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  12. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  13. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  14. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  15. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  16. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  17. 7900 JAMES CLERK 7698 03-12月-81 950 30
  18. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  19. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  20.  
  21. 已选择13行。
3)将1)和2)二步合二为一,形成主子查询
select *
from emp
where sal < any (select sal from emp where deptno=10);
<any 就相当于<最大值

  1. sql> select *
  2. 2 from emp
  3. 3 where sal < any (select sal from emp where deptno=10);
  4.  
  5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ----- ---------- ---------- ----- ------------ ----- ----- ------
  7. 7369 SMITH CLERK 7902 17-12月-80 800 20
  8. 7900 JAMES CLERK 7698 03-12月-81 950 30
  9. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  10. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  11. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  12. 7934 MILLER CLERK 7782 23-1 -82 1300 10
  13. 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30
  14. 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30
  15. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  16. 7698 BLAKE MANAGER 7839 01-5 -81 2850 30
  17. 7566 JONES MANAGER 7839 02-4 -81 2975 20
  18. 7788 SCOTT ANALYST 7566 19-4 -87 3000 20
  19. 7902 FORD ANALYST 7566 03-12月-81 3000 20
  20.  
  21. 已选择13行。
查询工资比10号部门【所有】员工【低】的员工信息
1)查询10号部门的员工工资
select * from emp where deptno=10;
  1. sql> select * from emp where deptno=10;
  2.  
  3. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  4. ----- ---------- ---------- ----- ------------ ----- ----- ------
  5. 7782 CLARK MANAGER 7839 09-6 -81 2450 10
  6. 7839 KING PRESIDENT 17-11月-81 5000 10
  7. 7934 MILLER CLERK 7782 23-1 -82 1300 10
2)查询员工比2450和5000和1300低的员
select *
from emp
where sal<2450 and sal<5000 and sal<1300;
  1. sql> select *
  2. 2 from emp
  3. 3 where sal<2450 and sal<5000 and sal<1300;
  4.  
  5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ----- ---------- ---------- ----- ------------ ----- ----- ------
  7. 7369 SMITH CLERK 7902 17-12月-80 800 20
  8. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  9. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  10. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  11. 7900 JAMES CLERK 7698 03-12月-81 950 30
3)将1)和2)二步合二为一,形成主子查询
select *
from emp
where sal < all (select sal from emp where deptno=10);
<all 就相当于<最小值
  1. sql> select *
  2. 2 from emp
  3. 3 where sal < all (select sal from emp where deptno=10);
  4.  
  5. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ----- ---------- ---------- ----- ------------ ----- ----- ------
  7. 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30
  8. 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30
  9. 7876 ADAMS CLERK 7788 23-5 -87 1100 20
  10. 7900 JAMES CLERK 7698 03-12月-81 950 30
  11. 7369 SMITH CLERK 7902 17-12月-80 800 20
查询'CLERK'的领导信息,那查询'PRESIDENT'的领导信息呢?说明null值问题
1)select ename,mgr from emp where job='CLERK'; 子查询---mgr
  1. sql> select ename,mgr from emp where job='CLERK';
  2.  
  3. ENAME MGR
  4. ---------- -----
  5. SMITH 7902
  6. ADAMS 7788
  7. JAMES 7698
  8. MILLER 7782
2)select ename from emp where empno=7902; 主查询---empno
  1. sql> select ename from emp where empno=7902;
  2.  
  3. ENAME
  4. ----------
  5. FORD
3)将1)和2)二步合二为一,形成主子查询
select ename "领导名"
from emp
where empno in (
select mgr
from emp
where job='CLERK'
);
  1. sql> select ename "领导名"
  2. 2 from emp
  3. 3 where empno in (
  4. 4 select mgr
  5. 5 from emp
  6. 6 where job='CLERK'
  7. 7 );
  8.  
  9. 领导名
  10. --------------------
  11. BLAKE
  12. CLARK
  13. SCOTT
  14. FORD

猜你在找的Oracle相关文章