day43_Oracle学习笔记_02

八、子查询

示例代码如下: 子查询.txt

SQL> --rownum 行号
SQL> select rownum,empno,ename,sal from emp;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             
         9       7839 KING             5000                                                                                                                                                             
        10       7844 TURNER           1500                                                                                                                                                             
        11       7876 ADAMS            1100                                                                                                                                                             

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        12       7900 JAMES             950                                                                                                                                                             
        13       7902 FORD             3000                                                                                                                                                             
        14       7934 MILLER           1300                                                                                                                                                             

已选择 14 行。

SQL> select rownum,empno,ename,sal
  2  from emp
  3  where rownum<=3
  4  order by sal desc;        --按照薪水降序排列

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             

SQL> /*
SQL> 关于行号rownum
SQL>     1. rownum永远按照默认的顺序生成
SQL>     2. rownum只能使用 < <=; 不能使用 > >=
SQL> */
SQL> select rownum,empno,ename,sal from emp order by sal desc;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         9       7839 KING             5000                                                                                                                                                             
        13       7902 FORD             3000                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
        10       7844 TURNER           1500                                                                                                                                                             
        14       7934 MILLER           1300                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        11       7876 ADAMS            1100                                                                                                                                                             
        12       7900 JAMES             950                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             

已选择 14 行。

SQL> --第一题:找到员工表中工资最高的前三名
SQL> select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)        --子查询得到新表
  3  where rownum<=3;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7839 KING             5000                                                                                                                                                             
         2       7788 SCOTT            3000                                                                                                                                                             
         3       7902 FORD             3000                                                                                                                                                             

SQL> --2. rownum只能使用 < <=; 不能使用 > >=
SQL> --分页
SQL> select rownum,empno,ename,sal from emp
  2  where rownum>=5 and rownum<=8;

未选定行

SQL> select rownum,empno,ename,sal from emp
  2  where rownum>=5;

未选定行

SQL> 原因:Oracle数据库是行式数据库,NOSQL数据库是列式数据库。
SQL> ed
已写入 file afiedt.buf

  1  select rownum,empno,ename,sal from emp
  2* where rownum<=8
SQL> /

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             

已选择 8 行。

SQL>  select *
  2   from     (select rownum r,e1.*
  3       from (select * from emp order by sal) e1   --r是e1表的行号,是e2表的列
  4        where rownum<=8
  5      )
  6   where r>=5;

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                   
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                   
         5       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                   
         6       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                   
         7       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                   
         8       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                   

SQL> /*
SQL> 临时表:
SQL>        1. 手动创建:create global temporary table *****
SQL>        2. 自动创建: order by   排序
SQL>        临时表的特点:当事务或者会话结束的时候,表中的数据会自动删除,但表结构还存在。
SQL>          在Oracle中,事务提交了,数据不一定保存下来了。
SQL> */
SQL> create global temporary table test2
  2  (tid number,tname varchar2(20))
  3  on commit delete rows;

表已创建。

SQL> insert into test2 values(1,'Tom');

已创建 1 行。

SQL> select * from test2;

       TID TNAME                                                                                                                                                                                        
---------- --------------------                                                                                                                                                                         
         1 Tom                                                                                                                                                                                          

SQL> commit;

提交完成。

SQL> select * from test2;

未选定行

SQL> desc test2
 名称                                                                                                              是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TID                                                                                                                        NUMBER
 TNAME                                                                                                                      VARCHAR2(20)

SQL> host cls

SQL> --第二题:找到员工表中薪水大于本部门平均薪水的员工。
SQL> select e.empno,e.ename,e.sal,d.avgsal
  2  from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
  3  where e.deptno=d.deptno and e.sal > d.avgsal;

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7902 FORD             3000       2175                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7839 KING             5000 2916.66667                                                                                                                                                             

已选择 6 行。

SQL> --相关子查询:将主查询中的值作为参数传递给子查询。
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
  2  from emp e
  3  where sal > (select avg(sal) from emp where deptno=e.deptno);

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7839 KING             5000 2916.66667                                                                                                                                                             
      7902 FORD             3000       2175                                                                                                                                                             

已选择 6 行。

SQL> host cls

SQL> --第三题:统计每年入职的员工个数。不能使用子查询。
SQL> select hiredate from emp;

HIREDATE                                                                                                                                                                                                
--------------                                                                                                                                                                                          
17-12月-80                                                                                                                                                                                              
20-2月 -81                                                                                                                                                                                              
22-2月 -81                                                                                                                                                                                              
02-4月 -81                                                                                                                                                                                              
28-9月 -81                                                                                                                                                                                              
01-5月 -81                                                                                                                                                                                              
09-6月 -81                                                                                                                                                                                              
19-4月 -87                                                                                                                                                                                              
17-11月-81                                                                                                                                                                                              
08-9月 -81                                                                                                                                                                                              
23-5月 -87                                                                                                                                                                                              

HIREDATE                                                                                                                                                                                                
--------------                                                                                                                                                                                          
03-12月-81                                                                                                                                                                                              
03-12月-81                                                                                                                                                                                              
23-1月 -82                                                                                                                                                                                              

已选择 14 行。

SQL> /*
SQL> 思路讲解:
SQL> select count(*) Total,
SQL> 
SQL>        sum(if 是81年 then +1 else +0) "1981",
SQL> 
SQL> from emp;
SQL> 
SQL> HIREDATE      count81 number:=0;
SQL> ---------------------------
SQL> 17-12月-80     0
SQL> 20-2月 -81     1
SQL> 22-2月 -81     1
SQL> 02-4月 -81     1
SQL> 28-9月 -81     1
SQL> 01-5月 -81     1
SQL> 09-6月 -81     1
SQL> 19-4月 -87     0
SQL> 17-11月-81     1
SQL> 08-9月 -81     1
SQL> 23-5月 -87     0
SQL> 03-12月-81     1
SQL> 03-12月-81     1
SQL> 23-1月 -82     0
SQL> ---------------------
SQL>               10
SQL> */
SQL> host cls


SQL> --行转列函数
SQL> -- wm_concat(varchar2) 这是一个组函数,注意:对于组函数,没有包含在该函数中的列,必须要在group by语句的后面。
SQL> select deptno,wm_concat(ename) nameslist
  2  from emp
  3  group by deptno;    --按部门号分组

    DEPTNO                                                                                                                                                                                              
----------                                                                                                                                                                                              
NAMESLIST                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        10                                                                                                                                                                                              
CLARK,KING,MILLER                                                                                                                                                                                       

        20                                                                                                                                                                                              
SMITH,FORD,ADAMS,SCOTT,JONES                                                                                                                                                                            

        30                                                                                                                                                                                              
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD                                                                                                                                                                    


SQL> col nameslist for a60
SQL> select deptno,wm_concat(ename) nameslist
  2  from emp
  3  group by deptno;

    DEPTNO NAMESLIST                                                                                                                                                                                    
---------- ------------------------------------------------------------                                                                                                                                 
        10 CLARK,KING,MILLER                                                                                                                                                                            
        20 SMITH,FORD,ADAMS,SCOTT,JONES                                                                                                                                                                 
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD                                                                                                                                                         

SQL> spool off

课堂练习:

SQL> --rownum 行号
SQL> select rownum,empno,ename,sal from emp;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             
         9       7839 KING             5000                                                                                                                                                             
        10       7844 TURNER           1500                                                                                                                                                             
        11       7876 ADAMS            1100                                                                                                                                                             

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        12       7900 JAMES             950                                                                                                                                                             
        13       7902 FORD             3000                                                                                                                                                             
        14       7934 MILLER           1300                                                                                                                                                             

已选择 14 行。

SQL> select rownum,empno,ename,sal
  2  from emp
  3  where rownum<=3
  4  order by sal desc;        --按照薪水降序排列

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             

SQL> /*
SQL> 关于行号rownum
SQL>     1. rownum永远按照默认的顺序生成
SQL>     2. rownum只能使用 < <=; 不能使用 > >=
SQL> */
SQL> select rownum,empno,ename,sal from emp order by sal desc;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         9       7839 KING             5000                                                                                                                                                             
        13       7902 FORD             3000                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
        10       7844 TURNER           1500                                                                                                                                                             
        14       7934 MILLER           1300                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
        11       7876 ADAMS            1100                                                                                                                                                             
        12       7900 JAMES             950                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             

已选择 14 行。

SQL> --第一题:找到员工表中工资最高的前三名
SQL> select rownum,empno,ename,sal
  2  from (select * from emp order by sal desc)        --子查询得到新表
  3  where rownum<=3;

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7839 KING             5000                                                                                                                                                             
         2       7788 SCOTT            3000                                                                                                                                                             
         3       7902 FORD             3000                                                                                                                                                             

SQL> --2. rownum只能使用 < <=; 不能使用 > >=
SQL> --分页
SQL> select rownum,empno,ename,sal from emp
  2  where rownum>=5 and rownum<=8;

未选定行

SQL> select rownum,empno,ename,sal from emp
  2  where rownum>=5;

未选定行

SQL> 原因:Oracle数据库是行式数据库,NOSQL数据库是列式数据库。
SQL> ed
已写入 file afiedt.buf

  1  select rownum,empno,ename,sal from emp
  2* where rownum<=8
SQL> /

    ROWNUM      EMPNO ENAME             SAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
         1       7369 SMITH             800                                                                                                                                                             
         2       7499 ALLEN            1600                                                                                                                                                             
         3       7521 WARD             1250                                                                                                                                                             
         4       7566 JONES            2975                                                                                                                                                             
         5       7654 MARTIN           1250                                                                                                                                                             
         6       7698 BLAKE            2850                                                                                                                                                             
         7       7782 CLARK            2450                                                                                                                                                             
         8       7788 SCOTT            3000                                                                                                                                                             

已选择 8 行。

SQL>  select *
  2   from     (select rownum r,e1.*
  3       from (select * from emp order by sal) e1   --r是e1表的行号,是e2表的列
  4        where rownum<=8
  5      )
  6   where r>=5;

         R      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                   
---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                   
         5       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                   
         6       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                   
         7       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                   
         8       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                   

SQL> /*
SQL> 临时表:
SQL>        1. 手动创建:create global temporary table *****
SQL>        2. 自动创建: order by   排序
SQL>        临时表的特点:当事务或者会话结束的时候,表中的数据会自动删除,但表结构还存在。
SQL>          在Oracle中,事务提交了,数据不一定保存下来了。
SQL> */
SQL> create global temporary table test2
  2  (tid number,tname varchar2(20))
  3  on commit delete rows;

表已创建。

SQL> insert into test2 values(1,'Tom');

已创建 1 行。

SQL> select * from test2;

       TID TNAME                                                                                                                                                                                        
---------- --------------------                                                                                                                                                                         
         1 Tom                                                                                                                                                                                          

SQL> commit;

提交完成。

SQL> select * from test2;

未选定行

SQL> desc test2
 名称                                                                                                              是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TID                                                                                                                        NUMBER
 TNAME                                                                                                                      VARCHAR2(20)

SQL> host cls

SQL> --第二题:找到员工表中薪水大于本部门平均薪水的员工。
SQL> select e.empno,e.ename,e.sal,d.avgsal
  2  from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
  3  where e.deptno=d.deptno and e.sal > d.avgsal;

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7902 FORD             3000       2175                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7839 KING             5000 2916.66667                                                                                                                                                             

已选择 6 行。

SQL> --相关子查询:将主查询中的值作为参数传递给子查询。
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
  2  from emp e
  3  where sal > (select avg(sal) from emp where deptno=e.deptno);

     EMPNO ENAME             SAL     AVGSAL                                                                                                                                                             
---------- ---------- ---------- ----------                                                                                                                                                             
      7499 ALLEN            1600 1566.66667                                                                                                                                                             
      7566 JONES            2975       2175                                                                                                                                                             
      7698 BLAKE            2850 1566.66667                                                                                                                                                             
      7788 SCOTT            3000       2175                                                                                                                                                             
      7839 KING             5000 2916.66667                                                                                                                                                             
      7902 FORD             3000       2175                                                                                                                                                             

已选择 6 行。

SQL> host cls

SQL> --第三题:统计每年入职的员工个数。不能使用子查询。
SQL> select hiredate from emp;

HIREDATE                                                                                                                                                                                                
--------------                                                                                                                                                                                          
17-12月-80                                                                                                                                                                                              
20-2月 -81                                                                                                                                                                                              
22-2月 -81                                                                                                                                                                                              
02-4月 -81                                                                                                                                                                                              
28-9月 -81                                                                                                                                                                                              
01-5月 -81                                                                                                                                                                                              
09-6月 -81                                                                                                                                                                                              
19-4月 -87                                                                                                                                                                                              
17-11月-81                                                                                                                                                                                              
08-9月 -81                                                                                                                                                                                              
23-5月 -87                                                                                                                                                                                              

HIREDATE                                                                                                                                                                                                
--------------                                                                                                                                                                                          
03-12月-81                                                                                                                                                                                              
03-12月-81                                                                                                                                                                                              
23-1月 -82                                                                                                                                                                                              

已选择 14 行。

SQL> /*
SQL> 思路讲解:
SQL> select count(*) Total,
SQL> 
SQL>        sum(if 是81年 then +1 else +0) "1981",
SQL> 
SQL> from emp;
SQL> 
SQL> HIREDATE      count81 number:=0;
SQL> ---------------------------
SQL> 17-12月-80     0
SQL> 20-2月 -81     1
SQL> 22-2月 -81     1
SQL> 02-4月 -81     1
SQL> 28-9月 -81     1
SQL> 01-5月 -81     1
SQL> 09-6月 -81     1
SQL> 19-4月 -87     0
SQL> 17-11月-81     1
SQL> 08-9月 -81     1
SQL> 23-5月 -87     0
SQL> 03-12月-81     1
SQL> 03-12月-81     1
SQL> 23-1月 -82     0
SQL> ---------------------
SQL>               10

SQL> select count(*) Total,
  2         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
  3         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
  4         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
  5         sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
  6  from emp;

     TOTAL       1980       1981       1982       1987                          
---------- ---------- ---------- ---------- ----------                          
        14          1         10          1          2    
SQL> */
SQL> host cls


SQL> --行转列函数
SQL> -- wm_concat(varchar2) 这是一个组函数,注意:对于组函数,没有包含在该函数中的列,必须要在group by语句的后面。
SQL> select deptno,wm_concat(ename) nameslist
  2  from emp
  3  group by deptno;    --按部门号分组

    DEPTNO                                                                                                                                                                                              
----------                                                                                                                                                                                              
NAMESLIST                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        10                                                                                                                                                                                              
CLARK,KING,MILLER                                                                                                                                                                                       

        20                                                                                                                                                                                              
SMITH,FORD,ADAMS,SCOTT,JONES                                                                                                                                                                            

        30                                                                                                                                                                                              
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD                                                                                                                                                                    


SQL> col nameslist for a60
SQL> select deptno,wm_concat(ename) nameslist
  2  from emp
  3  group by deptno;

    DEPTNO NAMESLIST                                                                                                                                                                                    
---------- ------------------------------------------------------------                                                                                                                                 
        10 CLARK,KING,MILLER                                                                                                                                                                            
        20 SMITH,FORD,ADAMS,SCOTT,JONES                                                                                                                                                                 
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD                                                                                                                                                         

SQL> spool off

九、集合运算

描述集合运算符,如下图所示:

示例代码如下: 集合运算.txt

SQL> /*
SQL> 查询10和20号部门的员工
SQL> 1. select * from emp where deptno in (10,20);
SQL> 2. select * from emp where deptno=10 or deptno=20;
SQL> 3. 集合运算
SQL>    select * from emp where deptno=10
SQL>      加上
SQL>    select * from emp where deptno=20
SQL> */
SQL> select * from emp where deptno=10
  2  union
  3  select * from emp where deptno=20;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              
      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                                                                                                              
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                                                              
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                                                                                              

已选择 8 行。

SQL> host cls

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        10                 8750                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        20                10875                                                                                                                                                                         
        30 CLERK            950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        5600                                                                                                                                                                         

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        30                 9400                                                                                                                                                                         
                          29025                                                                                                                                                                         

已选择 13 行。

SQL>  select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3   select deptno,sum(sal) from emp group by deptno
  4   union
  5   select sum(sal) from emp;
 select deptno,sum(sal) from emp group by deptno
 *
第 3 行出现错误: 
ORA-01789: 查询块具有不正确的结果列数 


SQL> /*
SQL> 集合运算需要注意的问题:
SQL>     1. 参与运算的各个集合必须列数相同且类型一致
SQL>     2. 采用第一个集合作为最后的表头,即列的别名要起在第一个集合
SQL>     3. order by 永远在最后一句查询语句后面
SQL>     4. 使用括号改变集合运算顺序
SQL> */
SQL>  select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3   select deptno,to_char(null),sum(sal) from emp group by deptno
  4   union
  5   select to_number(null),to_char(null),sum(sal) from emp;

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        10                 8750                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        20                10875                                                                                                                                                                         
        30 CLERK            950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        5600                                                                                                                                                                         

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        30                 9400                                                                                                                                                                         
                          29025                                                                                                                                                                         

已选择 13 行。

SQL> --break on deptno skip 2
SQL> host cls

SQL> --SQL 语句执行时间
SQL> set timing on        --查看SQL 语句执行时间的开关
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        10                 8750                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        20                10875                                                                                                                                                                         
        30 CLERK            950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        5600                                                                                                                                                                         

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        30                 9400                                                                                                                                                                         
                          29025                                                                                                                                                                         

已选择 13 行。

已用时间:  00: 00: 00.02
SQL>  select deptno,job,sum(sal) from emp group by deptno,job
  2   union
  3   select deptno,to_char(null),sum(sal) from emp group by deptno
  4   union
  5   select to_number(null),to_char(null),sum(sal) from emp;

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
        10 MANAGER         2450                                                                                                                                                                         
        10 PRESIDENT       5000                                                                                                                                                                         
        10                 8750                                                                                                                                                                         
        20 ANALYST         6000                                                                                                                                                                         
        20 CLERK           1900                                                                                                                                                                         
        20 MANAGER         2975                                                                                                                                                                         
        20                10875                                                                                                                                                                         
        30 CLERK            950                                                                                                                                                                         
        30 MANAGER         2850                                                                                                                                                                         
        30 SALESMAN        5600                                                                                                                                                                         

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        30                 9400                                                                                                                                                                         
                          29025                                                                                                                                                                         

已选择 13 行。

已用时间:  00: 00: 00.04
SQL> --SQL 语句优化原则
SQL>    5. 尽量不要使用集合运算,原因:随着参与结合运算的集合越多,效率越低。
SQL> set timing off
SQL> spool off

十、数据处理

数据碎片图解:

示例代码如下: 数据处理.txt

SQL> select count(*) Total,
  2         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
  3         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
  4         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
  5         sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
  6  from emp;

     TOTAL       1980       1981       1982       1987                          
---------- ---------- ---------- ---------- ----------                          
        14          1         10          1          2                          

SQL> /*
SQL> SQL 语句的类型
SQL>     1. DML(data manipulation Language 数据操作语言): insert update delete select
SQL>     2. DDL(Data Definition Language 数据定义语言): create table,alter table,drop table,truncate table
SQL>                                                   create/drop view,sequence(序列),index,synonym(同义词)
SQL>     3. DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)
SQL> */
SQL> --插入 insert
SQL> insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);

已创建 1 行。

SQL> --隐式插入空值:在列名表中省略该列的值。
SQL> --显式插入空值:在values子句中指定空值。

SQL> --之前的学习JDBC中有一个接口PreparedStatement,可以预编译sql语句,可以防止sql注入问题。
SQL> --PreparedStatement pst = "insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";

SQL> --在Oracle数据库中
SQL> --地址符 &  相当于 ?
SQL> insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值:  1002
输入 ename 的值:  'Mary'
输入 sal 的值:  2000
输入 deptno 的值:  30
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1002,'Mary',2000,30)

已创建 1 行。

SQL> /
输入 empno 的值:  1003
输入 ename 的值:  'Mike'
输入 sal 的值:  5000
输入 deptno 的值:  20
原值    1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值    1: insert into emp(empno,ename,sal,deptno) values(1003,'Mike',5000,20)

已创建 1 行。

SQL> 在我们学习的所有sql语句中都可以使用地址符
SQL> select empno,ename,sal,&t
  2  from emp;
输入 t 的值:  job
原值    1: select empno,ename,sal,&t
新值    1: select empno,ename,sal,job

     EMPNO ENAME             SAL JOB                                            
---------- ---------- ---------- ---------                                      
      7369 SMITH             800 CLERK                                          
      7499 ALLEN            1600 SALESMAN                                       
      7521 WARD             1250 SALESMAN                                       
      7566 JONES            2975 MANAGER                                        
      7654 MARTIN           1250 SALESMAN                                       
      7698 BLAKE            2850 MANAGER                                        
      7782 CLARK            2450 MANAGER                                        
      7788 SCOTT            3000 ANALYST                                        
      7839 KING             5000 PRESIDENT                                      
      7844 TURNER           1500 SALESMAN                                       
      7876 ADAMS            1100 CLERK                                          

     EMPNO ENAME             SAL JOB                                            
---------- ---------- ---------- ---------                                      
      7900 JAMES             950 CLERK                                          
      7902 FORD             3000 ANALYST                                        
      7934 MILLER           1300 CLERK                                          
      1001 Tom              3000                                                
      1002 Mary             2000                                                
      1003 Mike             5000                                                

已选择 17 行。

SQL> select * from &t;
输入 t 的值:  dept
原值    1: select * from &t
新值    1: select * from dept

    DEPTNO DNAME          LOC                                                   
---------- -------------- -------------                                         
        10 ACCOUNTING     NEW YORK                                              
        20 RESEARCH       DALLAS                                                
        30 SALES          CHICAGO                                               
        40 OPERATIONS     BOSTON                                                

SQL> rollback;    --回滚掉之前插入的数据

回退已完成。

SQL> host cls

SQL> --批处理
SQL> create table emp10 as select * from emp where 1=2;     --as的作用:可以把emp结果集的结构拷贝到新的表emp10上,由于where的条件结果恒为假,所以只拷贝表结构,没有拷贝表数据。

表已创建。

SQL> desc emp10
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select * from emp10;    --说明新创建的表emp10中没有数据

未选定行

SQL> --一次性将表emp中的所有10号部门的员工插入到emp10中,对于海量数据,效率比较低。如何解决呢?
SQL> insert into emp10 select * from emp where deptno=10;

已创建 3 行。

SQL> select * from emp10;

     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                                                                      


SQL> /*
SQL> Oracle中如何海量拷贝数据
SQL>     1. 数据泵(datapump)程序包 --> plsql程序
SQL>     2. SQL*Loader
SQL>     3. (数据仓库)外部表
SQL>     4. 可传输的表空间
SQL> */
SQL> host cls

SQL> /*
SQL> delete和truncate的区别:
SQL>     1. delete逐条删除,truncate先摧毁表,再重建表
SQL>     2. (根本区别)delete是DML,truncate是DDL
SQL>      (可以回滚)           (不可以回滚)
SQL>     3. delete不会释放空间,truncate会释放空间
SQL>     4. delete可以闪回,truncate不可以闪回
SQL>       (flashback)
SQL>     5. delete会产生碎片,truncate不会产生碎片
SQL> */

SQL> set feedback off    --由于演示插入的数据很多,所以我们先把插入回显信息关掉,暂时不让它回显了。

SQL> @d:\temp\testdelete.sql
SQL> select count(*) from testdelete;

  COUNT(*)                                                                      
----------                                                                      
      5000      
SQL> set timing on
SQL> delete from testdelete;
已用时间:  00: 00: 00.06
SQL> set timing off

SQL> drop table testdelete purge;    --

SQL> @d:\temp\testdelete.sql
SQL> select count(*) from testdelete;

  COUNT(*)                                                                      
----------                                                                      
      5000                                                                      
SQL> set timing on
SQL> truncate table testdelete;
已用时间:  00: 00: 00.15
SQL> set timing off

SQL> --原因:
SQL> --Oracle中的undo数据(还原数据)

SQL> set feedback off
SQL> host cs

SQL> /*
SQL> Oracle数据库事务的标志:
SQL>     1. 起始标志:事务中第一条DML语句,例如:insert update delete select
SQL>     2. 结束标志:提交:显式:commit
SQL>                       隐式:正常退出(exit),DDL语句,DCL语句
SQL>                   回滚: 显式:rollback
SQL>                       隐式:非正常退出,掉电,宕机(死机)
SQL> */

SQL> create table testsavepoint
  2  (tid number,tname varchar2(20));
SQL> set feedback on
SQL> insert into testsavepoint values(1,'Tom');

已创建 1 行。

SQL> insert into testsavepoint values(2,'Mary');

已创建 1 行。

SQL> --定义保存点(存储点)
SQL> savepoint a;

保存点已创建。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> insert into testsavepoint values(3,'Maake');

已创建 1 行。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 
         3 Maake                                                                

已选择 3 行。

SQL> rollback to savepoint a;    --回滚到保存点a

回退已完成。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> commit;

提交完成。

SQL> --SQL99标准提供了4中事务隔离级别。
SQL> --MySql中支持4种事务隔离级别:read uncommitted、read commited、repeatable read(默认的隔离级别) 和 serializable
SQL> --Oracle中支持3种事务隔离级别:read commited(默认的隔离级别) 、serializable 和 read only

SQL> set transaction read only;        --做一个事务的时候,不想要别的事务来打扰我,该怎么办?答:在Oracle中设置事务的隔离级别为只读,隔离级别:read only,为Oracle中所特有的。

事务处理集。

SQL> select * from testsavepoint;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL>  insert into testsavepoint values(3,'Maake');
 insert into testsavepoint values(3,'Maake')
             *
第 1 行出现错误: 
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作 


SQL> rollback;

回退已完成。

SQL> spool off

十一、创建和管理表

示例代码如下: 创建和管理表.txt

SQL> --使用DDL语句创建和管理表
SQL> --一共学习10个数据库对象
SQL> --create/drop table,view,sequence(序列),index,synonym(同义词)
SQL> --存储过程、存储函数、触发器、包、包体

SQL> --Oracle数据库默认存储表名是存为大写
SQL> create table test3
  2  (tid number,
  3   tname varchar2(20),
  4   hiredate date default sysdate);

表已创建。

SQL> insert into test3(tid,tname) values(1,'Tom');

已创建 1 行。

SQL> select * from test3;

       TID TNAME                HIREDATE                                        
---------- -------------------- --------------                                  
         1 Tom                  23-11月-15                                      

已选择 1 行。

SQL> --数据类型
varchar(20)            可变长字符数据
char(20)            定长字符数据
number()            可变长数值数据
data                日期型数据
long                可变长字符数据,最大可达到2G
clob                字符数据,最大可达到4G
raw and long raw    原始的二进制数据
blow                二进制数据,最大可达到4G
bfile                存储外部文件的二进制数据,最大可达到4G
rowid                行地址

SQL> host cls

SQL> --行地址 rowid 是一个伪列
SQL> select rowid,empno,ename,sal from emp;

ROWID                   EMPNO ENAME             SAL                             
------------------ ---------- ---------- ----------                             
AAAMfPAAEAAAAAgAAA       7369 SMITH             800                             
AAAMfPAAEAAAAAgAAB       7499 ALLEN            1600                             
AAAMfPAAEAAAAAgAAC       7521 WARD             1250                             
AAAMfPAAEAAAAAgAAD       7566 JONES            2975                             
AAAMfPAAEAAAAAgAAE       7654 MARTIN           1250                             
AAAMfPAAEAAAAAgAAF       7698 BLAKE            2850                             
AAAMfPAAEAAAAAgAAG       7782 CLARK            2450                             
AAAMfPAAEAAAAAgAAH       7788 SCOTT            3000                             
AAAMfPAAEAAAAAgAAI       7839 KING             5000                             
AAAMfPAAEAAAAAgAAJ       7844 TURNER           1500                             
AAAMfPAAEAAAAAgAAK       7876 ADAMS            1100                             

ROWID                   EMPNO ENAME             SAL                             
------------------ ---------- ---------- ----------                             
AAAMfPAAEAAAAAgAAL       7900 JAMES             950                             
AAAMfPAAEAAAAAgAAM       7902 FORD             3000                             
AAAMfPAAEAAAAAgAAN       7934 MILLER           1300                             

已选择 14 行。

SQL> select empno,ename,sal from emp where rowid='AAAMfPAAEAAAAAgAAK';

     EMPNO ENAME             SAL                                                
---------- ---------- ----------                                                
      7876 ADAMS            1100                                                

已选择 1 行。

SQL> --创建表:用来保存20号部门的员工的数据 set linesize 150
SQL> set linesize 150
SQL> create table emp20
  2  as
  3  select * from emp where deptno=20;        --as的作用:可以把emp结果集的结构拷贝到新的表emp10上,由于where的条件结果恒为假,所以只拷贝表结构,没有拷贝表数据。如果where的条件结果为真,则一并拷贝数据。

表已创建。

SQL> select * from emp20;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                                                        

已选择 5 行。

SQL> --创建表:员工号 姓名 月薪 年薪 部门名称
SQL> create table empinfo
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e,dept d
  5  where e.deptno=d.deptno;
SQL> 

表已创建。

SQL> select * from empinfo;

     EMPNO ENAME             SAL     ANNSAL DNAME                               
---------- ---------- ---------- ---------- --------------                      
      7369 SMITH             800       9600 RESEARCH                            
      7499 ALLEN            1600      19200 SALES                               
      7521 WARD             1250      15000 SALES                               
      7566 JONES            2975      35700 RESEARCH                            
      7654 MARTIN           1250      15000 SALES                               
      7698 BLAKE            2850      34200 SALES                               
      7782 CLARK            2450      29400 ACCOUNTING                          
      7788 SCOTT            3000      36000 RESEARCH                            
      7839 KING             5000      60000 ACCOUNTING                          
      7844 TURNER           1500      18000 SALES                               
      7876 ADAMS            1100      13200 RESEARCH                            

     EMPNO ENAME             SAL     ANNSAL DNAME                               
---------- ---------- ---------- ---------- --------------                      
      7900 JAMES             950      11400 SALES                               
      7902 FORD             3000      36000 RESEARCH                            
      7934 MILLER           1300      15600 ACCOUNTING                          

已选择 14 行。

SQL> --创建一个视图
SQL> create view empinfoview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e,dept d
  5  where e.deptno=d.deptno;
SQL> --下节课讲解

SQL> host cls

SQL> --修改表:追加新列,修改列,删除列,重命名列,重命名表
SQL> desc test3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TID                                                NUMBER
 TNAME                                              VARCHAR2(20)
 HIREDATE                                           DATE

SQL> --追加新列
SQL> alter table test3 add photo blob;

表已更改。

SQL> desc test3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TID                                                NUMBER
 TNAME                                              VARCHAR2(20)
 HIREDATE                                           DATE
 PHOTO                                              BLOB

SQL> --修改列
SQL> alter table test3 modify tname varchar2(40);

表已更改。

SQL> --删除列
SQL> alter table test3 drop column photo;

表已更改。

SQL> --重命名列
SQL> alter table test3 rename column tname to username;

表已更改。

SQL> --重命名表
SQL> rename test3 to test5;

表已重命名。

SQL> host cls

SQL> --删除表
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                               
------------------------------ ------- ----------                               
DEPT                           TABLE                                            
EMP                            TABLE                                            
BONUS                          TABLE                                            
SALGRADE                       TABLE                                            
EMP10                          TABLE                                            
TEST2                          TABLE                                            
TESTSAVEPOINT                  TABLE                                            
TESTDELETE                     TABLE                                            
EMP20                          TABLE                                            
EMPINFO                        TABLE                                            
TEST5                          TABLE                                            

已选择 11 行。

SQL> drop table test5;    --drop没有把表删除掉

表已删除。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                               
------------------------------ ------- ----------                               
DEPT                           TABLE                                            
EMP                            TABLE                                            
BONUS                          TABLE                                            
SALGRADE                       TABLE                                            
EMP10                          TABLE                                            
TEST2                          TABLE                                            
TESTSAVEPOINT                  TABLE                                            
TESTDELETE                     TABLE                                            
BIN$3yTwhJ0vSH268YZ8mlL3zw==$0 TABLE                                            
EMP20                          TABLE                                            
EMPINFO                        TABLE                                            

已选择 11 行。

SQL> --Oracle的回收站
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
TEST5            BIN$3yTwhJ0vSH268YZ8mlL3zw==$0 TABLE        2015-11-23:15:03:42
SQL> purge recyclebin;    --清空回收站

回收站已清空。

SQL> select * from TESTSAVEPOINT;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> drop table TESTSAVEPOINT;

表已删除。

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
TESTSAVEPOINT    BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE        2015-11-23:15:07:06
SQL> select * from TESTSAVEPOINT;
select * from TESTSAVEPOINT
              *
第 1 行出现错误: 
ORA-00942: 表或视图不存在 


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                               
------------------------------ ------- ----------                               
DEPT                           TABLE                                            
EMP                            TABLE                                            
BONUS                          TABLE                                            
SALGRADE                       TABLE                                            
EMP10                          TABLE                                            
TEST2                          TABLE                                            
TESTDELETE                     TABLE                                            
BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE                                            
EMP20                          TABLE                                            
EMPINFO                        TABLE                                            

已选择 10 行。

SQL> select * from BIN$+0nemp5PSe2adQphqd6t4A==$0;
select * from BIN$+0nemp5PSe2adQphqd6t4A==$0
                  *
第 1 行出现错误: 
ORA-00933: SQL 命令未正确结束 


SQL> select * from "BIN$+0nemp5PSe2adQphqd6t4A==$0";

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> --注意:不是所有的用户都有回收站,管理员就没有回收站,回收站只针对普通用户。
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME          
---------------- ------------------------------ ------------ -------------------
TESTSAVEPOINT    BIN$+0nemp5PSe2adQphqd6t4A==$0 TABLE        2015-11-23:15:07:06

SQL> --闪回删除
SQL> flashback table TESTSAVEPOINT to before drop;

闪回完成。

SQL> show recyclebin
SQL> select * from TESTSAVEPOINT;

       TID TNAME                                                                
---------- --------------------                                                 
         1 Tom                                                                  
         2 Mary                                                                 

已选择 2 行。

SQL> --约束=数据的完整性
SQL> --分为:列级约束(一般外键)和表级约束(联合主键)
SQL> --约束的类型:
    not null
    unique
    primary key
    foreign key
    check

SQL> create table test5
  2  (tid number,
  3   tname varchar2(20),
  4   gender varchar2(2) check (gender in ('男','女')),
  5   sal number check (sal > 0)
  6  );

表已创建。

SQL> insert into test5 values(1,'Tom','男',1000);

已创建 1 行。

SQL> insert into test5 values(2,'Mike','啊',1000);
insert into test5 values(2,'Mike','啊',1000)
*
第 1 行出现错误: 
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005393) 


SQL> --primary key 非空且唯一
SQL> --我们查询表中的数据,通过主键来查询最快,为什么呢?
SQL> --答:primary key 非空且唯一,本身就是unique约束,而unique约束本身就是一个索引。所以主键说到底是一个唯一性的索引。
SQL> --为什么通过索引来查询最快呢?
SQL> --答:...
SQL> --注意:子表的外键必须是父表的主键。

SQL> create table student
  2  (
  3    sid number constraint student_pk primary key,    --constraint 约束
  4    sname varchar2(20) constraint student_name_notnull not null,
  5    gender varchar2(2) constraint student_gender check (gender in ('男','女')),
  6    email varchar2(40) constraint student_email_unique unique
  7                       constraint student_email_notnull not null,    --可以在一个类型上定义多个约束
  8    deptno number constraint student_fk references dept(deptno) on delete set null    --references:指定用部门表(父表)的主键作为学生表(子表)的外键
  9  );

表已创建。

SQL> on delete cascade:当删除父表时,级联删除子表记录,该动作危险,
SQL> on delete set null:将子表的相关依赖记录的外键值置为null,一般情况下,使用这句:级联置空

SQL> insert into student values(1,'Tom','男','tom@126.com',10);

已创建 1 行。

SQL> insert into student values(2,'Mike','男','tom@126.com',10);
insert into student values(2,'Mike','男','tom@126.com',10)
*
第 1 行出现错误: 
ORA-00001: 违反唯一约束条件 (SCOTT.STUDENT_EMAIL_UNIQUE) 


SQL> spool off

十二、其他数据库对象

示例代码如下: 其他数据库对象.txt

SQL> --常见的数据库对象
SQL> --表          基本的数据存储集合,由行和列组成。(物理概念)
SQL> --视图        从表中抽出的逻辑上相关的数据集合。(逻辑概念)
SQL> --序列        提供有规律的数值。
SQL> --索引        提高查询的效率。
SQL> --同义词      给对象起别名。

SQL> --视图view
SQL> create view empinfoview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
  4  from emp e,dept d
  5  where e.deptno=d.deptno;
create view empinfoview
            *
第 1 行出现错误: 
ORA-01031: 权限不足 


SQL> --需要管理员设置权限:grant create view to scott
SQL> /

视图已创建。

SQL> --视图详解:
SQL> --        1. 视图是一种虚表。
SQL> --        2. 视图是建立在已有表的基础上,视图赖以建立的这些表称为基表。
SQL> --        3. 向视图是建立在已有表的基础上,视图赖以建立的这些表称为基表。数据内容的语句为select语句,可以将视图理解为存储起来的 select 语句。
SQL> --        4. 视图向用户提供基表数据的另一种表现形式。
SQL> --    视图的优点:
SQL> --        1. 简化复杂查询,但视图不能提高性能。
SQL> --        2. 同样的数据,可以有不同的显示方式。
SQL> --        3. 提高数据的相互独立。
SQL> --        4. 限制数据访问。

SQL> desc empinfoview    --查看视图结构
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 ANNSAL                                             NUMBER
 DNAME                                              VARCHAR2(14)

SQL> select * from empinfoview;

     EMPNO ENAME             SAL     ANNSAL DNAME                               
---------- ---------- ---------- ---------- --------------                      
      7369 SMITH             800       9600 RESEARCH                            
      7499 ALLEN            1600      19200 SALES                               
      7521 WARD             1250      15000 SALES                               
      7566 JONES            2975      35700 RESEARCH                            
      7654 MARTIN           1250      15000 SALES                               
      7698 BLAKE            2850      34200 SALES                               
      7782 CLARK            2450      29400 ACCOUNTING                          
      7788 SCOTT            3000      36000 RESEARCH                            
      7839 KING             5000      60000 ACCOUNTING                          
      7844 TURNER           1500      18000 SALES                               
      7876 ADAMS            1100      13200 RESEARCH                            

     EMPNO ENAME             SAL     ANNSAL DNAME                               
---------- ---------- ---------- ---------- --------------                      
      7900 JAMES             950      11400 SALES                               
      7902 FORD             3000      36000 RESEARCH                            
      7934 MILLER           1300      15600 ACCOUNTING                          

已选择 14 行。

SQL> create or replace view empinfoview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname    --视图中的子查询可以是复杂的 select 语句
  4  from emp e,dept d
  5  where e.deptno=d.deptno
  6  with read only;    --定义约束

视图已创建。

SQL> --不建议通过视图对表进行修改。因为会有很多限制。

SQL> --物化视图:可以缓存数据,自学。
SQL> host cls

SQL> --序列:sequence
SQL> --序列的作用:可供多个用户用来产生唯一数值的数据库对象。
SQL> --序列的好处:
SQL> --        1. 自动提供唯一的数值
SQL> --        2. 共享对象
SQL> --        3. 主要用于提供主键值
SQL> --        4. 将序列值装入内存可以提高访问效率

SQL> create sequence myseq;

序列已创建。

SQL> create table testseq
  2  (tid number,tname varchar2(20));

表已创建。

SQL> 序列的两个伪列(属性):currval 和 nextval

SQL> select myseq.currval from dual;
select myseq.currval from dual
       *
第 1 行出现错误: 
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义 


SQL> select myseq.nextval from dual;

   NEXTVAL                                                                      
----------                                                                      
         1                                                                      

已选择 1 行。

SQL> select myseq.currval from dual;

   CURRVAL                                                                      
----------                                                                      
         1                                                                      

已选择 1 行。

SQL> insert into testseq values(myseq.nextval,'aaa');

已创建 1 行。

SQL> /

已创建 1 行。

SQL> /

已创建 1 行。

SQL> /

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from testseq;

       TID TNAME                                                                
---------- --------------------                                                 
         2 aaa                                                                  
         3 aaa                                                                  
         4 aaa                                                                  
         5 aaa                                                                  

已选择 4 行。

SQL> --序列会在下列情况下出现裂缝(即序列不连续):
SQL> --        回滚
SQL> --        系统异常:停电
SQL> --        多个表同时使用同一序列

SQL> insert into testseq values(myseq.nextval,'aaa');

已创建 1 行。

SQL> /

已创建 1 行。

SQL> rollback;

回退已完成。

SQL> insert into testseq values(myseq.nextval,'aaa');

已创建 1 行。

SQL> select * from testseq;

       TID TNAME                                                                
---------- --------------------                                                 
         2 aaa                                                                  
         3 aaa                                                                  
         4 aaa                                                                  
         5 aaa                                                                  
         8 aaa                                                                  

已选择 5 行。

SQL> --修改序列:alter sequence 
SQL> --修改序列的增量、最大值、最小值、循环选项、或是装入内存。
SQL> --注意:修改序列,只会影响将来的值,已经被取走的值,不会受到影响。

SQL> host cls

SQL> --索引 index
SQL> create index myindex
  2  on emp(deptno);

索引已创建。

SQL> --同义词(别名)
SQL> --为emp表起别名
SQL> create synonym myemp for emp;
create synonym myemp for emp
*
第 1 行出现错误: 
ORA-01031: 权限不足 


SQL> /

同义词已创建。

SQL> select * from myemp;

     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

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      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 行。                                                                   

SQL> --同义词的作用:1. 安全    2. 缩短对象名字的长度   3. 方便访问其他用户的对象
SQL> --同义词的分类:1. 私有同义词(只能自己用)         2. 公有同义词(任何用户都可以使用)


SQL> create synonym myemp1 for hr.employees;

同义词已创建。

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
第 1 行出现错误: 
ORA-00942: 表或视图不存在 


SQL> select count(*) from hr.employees;

  COUNT(*)                                                                      
----------                                                                      
       107                                                                      

已选择 1 行。

SQL> select count(*) from myemp1;

  COUNT(*)                                                                      
----------                                                                      
       107                                                                      

已选择 1 行。

SQL> spool off

索引图解:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

30750
来自专栏乐沙弥的世界

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

12930
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

28790
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

31540
来自专栏乐沙弥的世界

INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是...

12320
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

25040
来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

34870
来自专栏乐沙弥的世界

SQL 基础--> 子查询

ORA-01427: single-row subquery returns more than one row

7020
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

32720
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

21750

扫码关注云+社区

领取腾讯云代金券