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;

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

select * from dept;

sql> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- --------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    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;
最终产生的表，列是各子表列之和，行是各子表行之积
笛卡尔集的结果无任何实际意思，我们得从笛卡尔集的结果中抽选中有意思的数据
如果通过表名.列名来取得某个表中的字段

sql> select * from dept,salgrade;
 
DEPTNO DNAME          LOC            GRADE      LOSAL      HISAL
------ -------------- -------------- ----- ---------- ----------
    10 ACCOUNTING     NEW YORK           1        700       1200
    10 ACCOUNTING     NEW YORK           2       1201       1400
    10 ACCOUNTING     NEW YORK           3       1401       2000
    10 ACCOUNTING     NEW YORK           4       2001       3000
    10 ACCOUNTING     NEW YORK           5       3001       9999
    20 RESEARCH       DALLAS             1        700       1200
    20 RESEARCH       DALLAS             2       1201       1400
    20 RESEARCH       DALLAS             3       1401       2000
    20 RESEARCH       DALLAS             4       2001       3000
    20 RESEARCH       DALLAS             5       3001       9999
    30 SALES          CHICAGO            1        700       1200
    30 SALES          CHICAGO            2       1201       1400
    30 SALES          CHICAGO            3       1401       2000
    30 SALES          CHICAGO            4       2001       3000
    30 SALES          CHICAGO            5       3001       9999
    40 OPERATIONS     BOSTON             1        700       1200
    40 OPERATIONS     BOSTON             2       1201       1400
 
DEPTNO DNAME          LOC            GRADE      LOSAL      HISAL
------ -------------- -------------- ----- ---------- ----------
    40 OPERATIONS     BOSTON             3       1401       2000
    40 OPERATIONS     BOSTON             4       2001       3000
    40 OPERATIONS     BOSTON             5       3001       9999
 
已选择20行。

等值连接，显示员工的编号，姓名，部门
select emp.empno,emp.ename,dept.dnamefrom emp,dept
where emp.deptno=dept.deptno;

sql> select emp.empno,dept.dname from emp,dept where emp.deptno=
  2  dept.deptno;
 
EMPNO ENAME      DNAME
----- ---------- --------------
 7782 CLARK      ACCOUNTING
 7839 KING       ACCOUNTING
 7934 MILLER     ACCOUNTING
 7566 JONES      RESEARCH
 7902 FORD       RESEARCH
 7876 ADAMS      RESEARCH
 7369 SMITH      RESEARCH
 7788 SCOTT      RESEARCH
 7521 WARD       SALES
 7844 TURNER     SALES
 7499 ALLEN      SALES
 7900 JAMES      SALES
 7698 BLAKE      SALES
 7654 MARTIN     SALES
 
已选择14行。

使用表别名，等值连接，显示员工的编号，姓名，部门
select e.empno,e.ename,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno;
oracle表名不能出现as关j键字，字段可以

sql> select e.empno,d.dname from emp e,dept d where e.deptno=d.deptno;
 
EMPNO ENAME      DNAME
----- ---------- --------------
 7782 CLARK      ACCOUNTING
 7839 KING       ACCOUNTING
 7934 MILLER     ACCOUNTING
 7566 JONES      RESEARCH
 7902 FORD       RESEARCH
 7876 ADAMS      RESEARCH
 7369 SMITH      RESEARCH
 7788 SCOTT      RESEARCH
 7521 WARD       SALES
 7844 TURNER     SALES
 7499 ALLEN      SALES
 7900 JAMES      SALES
 7698 BLAKE      SALES
 7654 MARTIN     SALES
 
已选择14行。

不等值连接，显示员工的编号，姓名，月薪，级别，只要不是使用=号即就
select e.empno,e.sal,s.gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;

sql> select e.empno,s.grade from emp e,salgrade s where e.sal betw
een s.losal and s.hisal;
 
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
 7788 SCOTT       3000     4
 7902 FORD        3000     4
 7839 KING        5000     5
 
已选择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子句中，
依据(+)出现在=号二个的位置不同，叫法不相：
(+)出现在=号左边，叫右[外]连接
(+)出现在=号右边，叫左[外]连接
依据=号二边，看哪一边少了值，就将(+)号加上少值的那边

sql> select d.deptno "部门号",count(e.deptno) "人数" from dept
d,emp e
  2  where d.deptno=e.deptno(+) group by d.deptno,d.dname;
 
    部门号 部门名                             人数
---------- ---------------------------- ----------
        10 ACCOUNTING                            3
        40 OPERATIONS                            0
        20 RESEARCH                              5
        30 SALES                                 6

右外连接，按部门，统计员工人数，显示部门号，部门名，人数
select d.deptno "部门号",count(e.deptno) "人数"
from dept d,emp ewhere d.deptno(+)=e.deptnogroup by d.deptno,d.dname;

sql> select d.deptno "部门号",count(e.deptno) "人数"
  2  from dept d,emp e where d.deptno(+)=e.deptno group by d.deptno,d.dname;
 
    部门号 部门名                             人数
---------- ---------------------------- ----------
        10 ACCOUNTING                            3
        20 RESEARCH                              5
        30 SALES                                 6

自连接，显示"SMITH的老板是FORD"这种格式
select e.ename || '的老板是' || b.enamefrom emp e,emp bwhere e.mgr = b.empno;
原则：将一个表，看作二张不同的表

sql> select e.ename || '的老板是' || b.ename from   emp e,emp b where  e.mgr = b
.empno;
 
E.ENAME||'的老板是'||B.ENAME
----------------------------------------------------------------
FORD的老板是JONES
SCOTT的老板是JONES
TURNER的老板是BLAKE
ALLEN的老板是BLAKE
WARD的老板是BLAKE
JAMES的老板是BLAKE
MARTIN的老板是BLAKE
MILLER的老板是CLARK
ADAMS的老板是SCOTT
BLAKE的老板是KING
JONES的老板是KING
CLARK的老板是KING
SMITH的老板是FORD
 
已选择13行。

//------------------------------------------------------------------------------------------------------
查询工资比SCOTT高的员工信息
子查询主要解决：条件是未知的查询，而且需要经过多个步骤才能完成。
1）SCOTT的工资?
select * from emp where ename='SCOTT';

sql> select * from emp where ename='SCOTT';
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7788 SCOTT      ANALYST     7566 19-4月 -87    3000           20

2) 比3000高的员工信息？
select * from emp where sal>3000;

sql> select * from emp where sal>3000;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7839 KING       PRESIDENT        17-11月-81    5000           10

3）将1)和2)二步合二为一，形成主子查询
select *from emp where sal>(select salfrom empwhere ename='SCOTT');

sql>  select *  from emp   where sal>(select sal  from emp  where ename='SCOTT'
);
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7839 KING       PRESIDENT        17-11月-81    5000           10

<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';

sql> select deptno from dept where dname='ACCOUNTING';
 
DEPTNO
------
    10

2）根据10号查询对应的员工信息
select * from emp where deptno=10;

sql> select * from emp where deptno=10;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7782 CLARK      MANAGER     7839 09-6月 -81    2450           10
 7839 KING       PRESIDENT        17-11月-81    5000           10
 7934 MILLER     CLERK       7782 23-1月 -82    1300           10

3）将1)和2)二步合二为一，形成主子查询
select *
from emp
where deptno=(
select deptno
from dept
where dname='ACCOUNTING');

sql> select *
  2     from emp
  3     where deptno=(
  4           select deptno
  5           from dept
  6           where dname='ACCOUNTING' );
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7782 CLARK      MANAGER     7839 09-6月 -81    2450           10
 7839 KING       PRESIDENT        17-11月-81    5000           10
 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';

sql> select e.ename,e.deptno from emp e,dept d
  2  where d.deptno=e.deptno and d.dname='ACCOUNTING';
 
ENAME        SAL DEPTNO
---------- ----- ------
CLARK       2450     10
KING        5000     10
MILLER      1300     10

查询工资最低的员工信息
1）查询工资最低是多少钱?
select min(sal) from emp;

sql> select min(sal)from emp;
 
  MIN(SAL)
----------
       800

2）查询800的员工信息
select * from emp where sal=800;

sql> select * from emp where sal=800;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7369 SMITH      CLERK       7902 17-12月-80     800           20

3）将1)和2)二步合二为一，形成主子查询
select *from empwhere sal=(select min(sal)from emp);

sql>    select *  from emp where sal=(select min(sal) from emp );
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 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'
);

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

查询部门名为'ACCOUNTING'或'SALES'的员工（方式二：多表查询）
select e.* from dept d,emp e

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

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

查询工资比10号部门【任意】一个员工【低】的员工信息1）查询10号部门的员工工资 select * from emp where deptno=10;

sql>   select * from emp where deptno=10;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7782 CLARK      MANAGER     7839 09-6月 -81    2450           10
 7839 KING       PRESIDENT        17-11月-81    5000           10
 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;

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

3）将1)和2)二步合二为一，形成主子查询
select *
from emp
where sal < any (select sal from emp where deptno=10);
<any 就相当于<最大值

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

查询工资比10号部门【所有】员工【低】的员工信息
1）查询10号部门的员工工资
select * from emp where deptno=10;

sql> select * from emp where deptno=10;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7782 CLARK      MANAGER     7839 09-6月 -81    2450           10
 7839 KING       PRESIDENT        17-11月-81    5000           10
 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;

sql> select *
  2     from emp
  3     where sal<2450 and sal<5000 and sal<1300;
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7369 SMITH      CLERK       7902 17-12月-80     800           20
 7521 WARD       SALESMAN    7698 22-2月 -81    1250   500     30
 7654 MARTIN     SALESMAN    7698 28-9月 -81    1250  1400     30
 7876 ADAMS      CLERK       7788 23-5月 -87    1100           20
 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 就相当于<最小值

sql> select *
  2     from emp
  3     where sal < all (select sal from emp where deptno=10);
 
EMPNO ENAME      JOB          MGR HIREDATE       SAL  COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
 7521 WARD       SALESMAN    7698 22-2月 -81    1250   500     30
 7654 MARTIN     SALESMAN    7698 28-9月 -81    1250  1400     30
 7876 ADAMS      CLERK       7788 23-5月 -87    1100           20
 7900 JAMES      CLERK       7698 03-12月-81     950           30
 7369 SMITH      CLERK       7902 17-12月-80     800           20

查询'CLERK'的领导信息，那查询'PRESIDENT'的领导信息呢？说明null值问题
1）select ename,mgr from emp where job='CLERK'; 子查询---mgr

sql> select ename,mgr from emp where job='CLERK';
 
ENAME        MGR
---------- -----
SMITH       7902
ADAMS       7788
JAMES       7698
MILLER      7782

2）select ename from emp where empno=7902; 主查询---empno

sql> select ename from emp where empno=7902;
 
ENAME
----------
FORD

3）将1)和2)二步合二为一，形成主子查询
select ename "领导名"
from emp
where empno in (
select mgr
from emp
where job='CLERK'
);

sql> select ename "领导名"
  2     from emp
  3     where empno in (
  4    select mgr
  5           from emp
  6           where job='CLERK'
  7     );
 
领导名
--------------------
BLAKE
CLARK
SCOTT
FORD

Oracle学习03【持续更新】

猜你在找的Oracle相关文章