day42_Oracle学习笔记_01

一、Oracle Database 的基本概念

1.1、一个Oracle服务器

详解如下:

一个Oracle服务器是一个关系型数据管理系统(RDBMS),它提供开放的,全面的,近乎完整的信息管理。

    什么叫做关系型数据库呢?
        答:基于关系模型所提出来的一种数据库,叫做关系型数据库。
    什么叫做关系模型呢?
        答:用二维表的形式(行和列的方式)来保存数据的模型,叫做关系模型。

NOSQL:Hadoop、HBase、Redis、MongDB,非关系型数据库,不是以行和列的方式来保存数据的,而是以key=value的方式。

一个 Oracle服务器由一个 Oracle数据库 和多个 Oracle实例 组成。

orcl数据库在Oracle里面是一个物理概念,指的是一堆文件,即在物理上就能找到orcl数据库的存在,这里的物理指的是:虚拟机的硬盘。如下图所示:

虚拟机上的orcl数据库,所在位置:C:\app\Training\oradata\orcl

继续

里面的一堆文件就是orcl数据库,数据就在这里面。这些文件都是二进制文件,不能够用记事本打开,那么我们想要做增删改查的操作,该怎么办呢?
    答:我们把这些文件读到内存中去,这些文件,即 orcl数据库 在内存中的镜像就叫做 Oracle实例(逻辑概念)。

我们通过 Oracle实例 来操作 Oracle数据库。Oracle数据库 和 Oracle实例 之间的关系至少是一对一。一般而言是:一(Oracle数据库)对多(Oracle实例)的关系。

如果他们是 一(Oracle数据库)对多(Oracle实例)的关系,就叫做 Oracle数据库的集群。

什么叫数据库集群呢?
我们先以Tomcat集群为例(Tomcat6以后就支持集群了),如下图所示:

Tomcat数据库集群图解:

Oracle数据库集群图解:

1.2、DBA

Oracle的三级认证:

    OCA(Oracle Certified Associate),是入门级别的资格证书;
    OCP(Oracle Certified Professionals),是专业证书;
    OCM(Oracle Certified Master),是新的高级资格证书,授予拥有最高专业技术的甲骨文认证专家。

二、编写基本 SQL SELECT 语句

示例代码如下: 基本查询.txt

SQL> spool e:\基本查询.txt

SQL> --Linux下的清屏命令
SQL> --host clear

SQL> --Windows下的清屏命令
SQL> host cls

SQL> --显示当前用户
SQL> show user
USER 为 "SCOTT"
SQL> --查询当前用户下的表
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                               
------------------------------ ------- ----------                               
DEPT                           TABLE                                            
EMP                            TABLE                                            
BONUS                          TABLE                                            
SALGRADE                       TABLE                                            

SQL> --查询员工表的结构    desc:describe
SQL> desc emp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL 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> --查询所有的员工信息
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300 
        30                                                                      

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500 
        30                                                                      


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


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


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0 
        30                                                                      

      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      

      7900 JAMES      CLERK           7698 03-12月-81            950            
        30                                                                      


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM 
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           3000            
        20                                                                      

      7934 MILLER     CLERK           7782 23-1月 -82           1300            
        10                                                                      


已选择 14 行。

SQL> --显示行宽
SQL> show linesize
linesize 80
SQL> --设置行宽
SQL> set linesize 150
SQL> --设置列宽
SQL> column ename format a8
SQL> col sal for 9999
SQL> /

     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> select empno,ename,job,mgr,hiredate,sal,comm,deptno
  2  from emp;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   

     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> /*
SQL> SQL 语句优化(数据库的优化)
SQL>    1. 尽量使用列名代替星号
SQL> */
SQL> --查询员工信息:员工号  姓名  月薪
SQL> select empno,ename,sal
  2  form emp;
form emp
     *
第 2 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 


SQL> --c命令:change
SQL> 2
  2* form emp
SQL> c /form/from
  2* from emp
SQL> /

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

     EMPNO ENAME      SAL                                                                                                                             
---------- -------- -----                                                                                                                             
      7900 JAMES      950                                                                                                                             
      7902 FORD      3000                                                                                                                             
      7934 MILLER    1300                                                                                                                             

已选择 14 行。

SQL> --演示SQL语句支持算式表达式
SQL> --查询员工信息:员工号  姓名  月薪  年薪
SQL> select empno,ename,sal,sal*12
  2  from emp;

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

     EMPNO ENAME      SAL     SAL*12                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7900 JAMES      950      11400                                                                                                                  
      7902 FORD      3000      36000                                                                                                                  
      7934 MILLER    1300      15600                                                                                                                  

已选择 14 行。

SQL>  --查询员工信息:员工号  姓名  月薪  年薪  奖金  年收入
SQL> select empno,ename,sal,sal*12,comm,sal*12+comm
  2  from emp;

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+COMM                                                                                           
---------- -------- ----- ---------- ---------- -----------                                                                                           
      7369 SMITH      800       9600                                                                                                                  
      7499 ALLEN     1600      19200        300       19500                                                                                           
      7521 WARD      1250      15000        500       15500                                                                                           
      7566 JONES     2975      35700                                                                                                                  
      7654 MARTIN    1250      15000       1400       16400                                                                                           
      7698 BLAKE     2850      34200                                                                                                                  
      7782 CLARK     2450      29400                                                                                                                  
      7788 SCOTT     3000      36000                                                                                                                  
      7839 KING      5000      60000                                                                                                                  
      7844 TURNER    1500      18000          0       18000                                                                                           
      7876 ADAMS     1100      13200                                                                                                                  

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+COMM                                                                                           
---------- -------- ----- ---------- ---------- -----------                                                                                           
      7900 JAMES      950      11400                                                                                                                  
      7902 FORD      3000      36000                                                                                                                  
      7934 MILLER    1300      15600                                                                                                                  

已选择 14 行。

SQL> host cls

SQL> /*
SQL> SQL中的null值问题(非常重要):
SQL>     1. 包含null的表达式都为null
SQL>     2. null永远!=null
SQL>         1). 空值是无效的,未指定的,未知的或不可预知的值。
SQL>         2). 空值不是空格或者0。
SQL> */
SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)    --用到Oracle的滤空函数nvl()和nvl2(),准确的说法叫做通用函数
  2  from emp;

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                                    
---------- -------- ----- ---------- ---------- ------------------                                                                                    
      7369 SMITH      800       9600                          9600                                                                                    
      7499 ALLEN     1600      19200        300              19500                                                                                    
      7521 WARD      1250      15000        500              15500                                                                                    
      7566 JONES     2975      35700                         35700                                                                                    
      7654 MARTIN    1250      15000       1400              16400                                                                                    
      7698 BLAKE     2850      34200                         34200                                                                                    
      7782 CLARK     2450      29400                         29400                                                                                    
      7788 SCOTT     3000      36000                         36000                                                                                    
      7839 KING      5000      60000                         60000                                                                                    
      7844 TURNER    1500      18000          0              18000                                                                                    
      7876 ADAMS     1100      13200                         13200                                                                                    

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                                    
---------- -------- ----- ---------- ---------- ------------------                                                                                    
      7900 JAMES      950      11400                         11400                                                                                    
      7902 FORD      3000      36000                         36000                                                                                    
      7934 MILLER    1300      15600                         15600                                                                                    

已选择 14 行。

SQL> --2. null永远!=null
SQL> --查询奖金为null的员工
SQL> select *
  2  from emp
  3  where comm=null;    --因为null永远!=null

未选定行

SQL> select *
  2  from emp
  3  where comm is null;

     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                                                                   
      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                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

已选择 10 行。

SQL> host cls

SQL> --给列起别名
SQL> --给列起别名的作用:
SQL>     1. 可以重命名一个列。
SQL>     2. 方便计算。
SQL>     3. 紧跟列名。也可以在列名和别名之间假如关键字 as ,别名使用双引号,以便在别名中包含空格或特殊的字符,并区分大小写。
SQL>     4. as 可以省略。

SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
  2  from emp;

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                                    
---------- -------- ----- ---------- ---------- ------------------                                                                                    
      7369 SMITH      800       9600                          9600                                                                                    
      7499 ALLEN     1600      19200        300              19500                                                                                    
      7521 WARD      1250      15000        500              15500                                                                                    
      7566 JONES     2975      35700                         35700                                                                                    
      7654 MARTIN    1250      15000       1400              16400                                                                                    
      7698 BLAKE     2850      34200                         34200                                                                                    
      7782 CLARK     2450      29400                         29400                                                                                    
      7788 SCOTT     3000      36000                         36000                                                                                    
      7839 KING      5000      60000                         60000                                                                                    
      7844 TURNER    1500      18000          0              18000                                                                                    
      7876 ADAMS     1100      13200                         13200                                                                                    

     EMPNO ENAME      SAL     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                                    
---------- -------- ----- ---------- ---------- ------------------                                                                                    
      7900 JAMES      950      11400                         11400                                                                                    
      7902 FORD      3000      36000                         36000                                                                                    
      7934 MILLER    1300      15600                         15600                                                                                    

已选择 14 行。

SQL> ed        --edit,可以把上一条SQL语句放到系统默认的编辑器里面,修改完毕后,保存即可
已写入 file afiedt.buf

  1  select empno as "员工号",ename "员工姓名",sal 月    薪,sal*12,comm,sal*12+nvl(comm,0)
  2* from emp
SQL> /
select empno as "员工号",ename "姓名",sal 月    薪,sal*12,comm,sal*12+nvl(comm,0)
                                                *
第 1 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字 

SQL> --注意:如果别名中含有关键字或者特殊字符或者纯数字,需要加双引号。
SQL> ed
已写入 file afiedt.buf

  1  select empno as "员工号",ename "姓名",sal "月    薪",sal*12,comm,sal*12+nvl(comm,0)
  2* from emp
SQL> /

    员工号 员工姓名       月  薪     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                         
---------- ---------- ---------- ---------- ---------- ------------------                                                                             
      7369 SMITH             800       9600                          9600                                                                             
      7499 ALLEN            1600      19200        300              19500                                                                             
      7521 WARD             1250      15000        500              15500                                                                             
      7566 JONES            2975      35700                         35700                                                                             
      7654 MARTIN           1250      15000       1400              16400                                                                             
      7698 BLAKE            2850      34200                         34200                                                                             
      7782 CLARK            2450      29400                         29400                                                                             
      7788 SCOTT            3000      36000                         36000                                                                             
      7839 KING             5000      60000                         60000                                                                             
      7844 TURNER           1500      18000          0              18000                                                                             
      7876 ADAMS            1100      13200                         13200                                                                             

    员工号 员工姓名       月  薪     SAL*12       COMM SAL*12+NVL(COMM,0)                                                                          
---------- ---------- ---------- ---------- ---------- ------------------                                                                             
      7900 JAMES             950      11400                         11400                                                                             
      7902 FORD             3000      36000                         36000                                                                             
      7934 MILLER           1300      15600                         15600                                                                             

已选择 14 行。

SQL> host cls

SQL> --distinct 的作用:去掉重复记录
SQL> select deptno from emp;

    DEPTNO                                                                                                                                            
----------                                                                                                                                            
        20                                                                                                                                            
        30                                                                                                                                            
        30                                                                                                                                            
        20                                                                                                                                            
        30                                                                                                                                            
        30                                                                                                                                            
        10                                                                                                                                            
        20                                                                                                                                            
        10                                                                                                                                            
        30                                                                                                                                            
        20                                                                                                                                            

    DEPTNO                                                                                                                                            
----------                                                                                                                                            
        30                                                                                                                                            
        20                                                                                                                                            
        10                                                                                                                                            

已选择 14 行。

SQL> select distinct deptno from emp;

    DEPTNO                                                                                                                                            
----------                                                                                                                                            
        30                                                                                                                                            
        20                                                                                                                                            
        10                                                                                                                                            

SQL> select job from emp;

JOB                                                                                                                                                   
---------                                                                                                                                             
CLERK                                                                                                                                                 
SALESMAN                                                                                                                                              
SALESMAN                                                                                                                                              
MANAGER                                                                                                                                               
SALESMAN                                                                                                                                              
MANAGER                                                                                                                                               
MANAGER                                                                                                                                               
ANALYST                                                                                                                                               
PRESIDENT                                                                                                                                             
SALESMAN                                                                                                                                              
CLERK                                                                                                                                                 

JOB                                                                                                                                                   
---------                                                                                                                                             
CLERK                                                                                                                                                 
ANALYST                                                                                                                                               
CLERK                                                                                                                                                 

已选择 14 行。

SQL> select distinct job from emp;

JOB                                                                                                                                                   
---------                                                                                                                                             
CLERK                                                                                                                                                 
SALESMAN                                                                                                                                              
PRESIDENT                                                                                                                                             
MANAGER                                                                                                                                               
ANALYST                                                                                                                                               

SQL> select distinct deptno, job from emp;

    DEPTNO JOB                                                                                                                                        
---------- ---------                                                                                                                                  
        20 CLERK                                                                                                                                      
        30 SALESMAN                                                                                                                                   
        20 MANAGER                                                                                                                                    
        30 CLERK                                                                                                                                      
        10 PRESIDENT                                                                                                                                  
        30 MANAGER                                                                                                                                    
        10 CLERK                                                                                                                                      
        10 MANAGER                                                                                                                                    
        20 ANALYST                                                                                                                                    

已选择 9 行。

SQL> --distinct 作用的范围:后面所有的列
SQL> host cls

SQL> /*
SQL> 注意事项:
SQL>     1. SQL语言大小写不敏感。
SQL>     2. SQL可以写在一行或者多行。
SQL>     3. 关键字不能被缩写也不能分行。
SQL>     4. 各子句一般要分行写。
SQL>     5. 使用缩进提高语句的可读性。
SQL> */

SQL> --连接符:作用可以用来"合成列"。
SQL> --MySQL中的concat()函数
SQL> select concat('Hello',' World');    --在MySQL中这种写法是可以的,但是在Oracle中是不可以的。
select concat('Hello',' World')            --我们的MySQL和Oracle都遵循一个标准:SQL99,该标准定义了在上面那条语句必须要写from关键字。
                                        --注意:Hadoop中有个数据仓库hive遵循 SQL92 标准
                               *
第 1 行出现错误: 
ORA-00923: 未找到要求的 FROM 关键字


SQL> --使用concat()函数的方式显示 Hello World
SQL> select concat('Hello',' World') from emp;

CONCAT('HELL                                                                                                                                          
------------                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          

CONCAT('HELL                                                                                                                                          
------------                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          
Hello World                                                                                                                                          

已选择 14 行。

SQL> select concat('Hello',' World') from dual;        --当我们的一个操作跟任何表都没有关系时,Oracle提供了一张dual表(伪表)

CONCAT('HELL                                                                                                                                          
------------                                                                                                                                          
Hello World                                                                                                                                          

SQL> select 3+2 from dual;

       3+2                                                                                                                                            
----------                                                                                                                                            
         5                                                                                                                                            

SQL> --dual 表:伪表,该表存在的意义:仅仅是为了满足select语句的语法要求。
SQL> (伪列)
  2  /
(伪列)
 *
第 1 行出现错误: 
ORA-00928: 缺失 SELECT 关键字 

SQL> --使用连接符的方式显示 Hello World
SQL> select 'Hello'||' World' 字符串 from dual;

字符串                                                                                                                                                
------------                                                                                                                                          
Hello World                                                                                                                                          

SQL> --查询员工信息:xxx的薪水是xxx
SQL> select ename||'的薪水是'||sal 信息 from emp;

信息                                                                                                                                                  
----------------------------------------------------------                                                                                            
SMITH的薪水是800                                                                                                                                      
ALLEN的薪水是1600                                                                                                                                     
WARD的薪水是1250                                                                                                                                      
JONES的薪水是2975                                                                                                                                     
MARTIN的薪水是1250                                                                                                                                    
BLAKE的薪水是2850                                                                                                                                     
CLARK的薪水是2450                                                                                                                                     
SCOTT的薪水是3000                                                                                                                                     
KING的薪水是5000                                                                                                                                      
TURNER的薪水是1500                                                                                                                                    
ADAMS的薪水是1100                                                                                                                                     

信息                                                                                                                                                  
----------------------------------------------------------                                                                                            
JAMES的薪水是950                                                                                                                                      
FORD的薪水是3000                                                                                                                                      
MILLER的薪水是1300                                                                                                                                    

已选择 14 行。

SQL> host cls

SQL> --字符串:单引号表示字符串或者日期,双引号表示列的别名。
SQL> select * from emp;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   

     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> spool off
SQL> --spool是录屏命令,录出的屏是一个文本文件。

三、SQL 和 SQLPlus

详解如下图所示:

示例:

SQL                 SQLPlus

select              ed(edit)
insert              c(change)
update              col(column)
delete              for(format)
                    desc(describe)
......              ......

如下图所示:

四、iSQLPlus

详解如下: 使用iSQLPlus可以:

    描述表结构。
    编辑SQL语句。
    执行SQL语句。
    将SQL保存在文件中并将SQL语句执行结果保存在文件中。
    在保存的文件中执行语句。
    将本机文件装入SQLPlus编辑窗口。
    以本机为例:http://localhost:5560/isqlplus/

    注意1:iSQLPlus这个工具只在 Oracle 9i 和 Oracle 10g 才有, 在 Oracle 11g 和 Oracle 12c 中没有,为什么呢?
        答:因为它有问题,iSQLPlus是一个网页的工具,它使用的传输协议是http,是明文协议,不安全。可以通过抓包工具(httpwatch)进行抓取。

    注意2:
        Oracle 10g中网页版的企业管理器(EM)的访问网址为:http://192.168.56.102:1158/em/
        Oracle 11g中网页版的企业管理器(EM)的访问网址为:https://192.168.56.102:1158/em/    

五、过滤和排序

5.1、在查询中过滤行 + 在查询中对行进行排序

示例代码如下: 过滤和排序.txt

SQL> spool e:\过滤和排序.txt

SQL> --查询10号部门的员工
SQL> --注意:where语句紧随from子句。
SQL> select * from emp
  2  where deptno=10;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

SQL> --字符和日期要包含在单引号中。      
SQL> --字符大小写敏感
SQL> --查询员工名叫KING的员工
SQL> select *
  2  from emp
  3  where ename='KING';

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   

SQL> ed        --edit,可以把上一条SQL语句放到系统默认的编辑器里面,修改完毕后,保存即可
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where ename='King'
SQL> /

未选定行

SQL> --日期格式敏感
SQL> --默认日期格式是:DD-MON-RR
SQL> --查询入职日期是17-11月-81的员工
SQL> select *
  2  from emp
  3  where hiredate='17-11月-81';

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where hiredate='1981-11-17'
SQL> /
where hiredate='1981-11-17'
               *
第 3 行出现错误: 
ORA-01861: 文字与格式字符串不匹配 


SQL> --查询Oracle的日期格式
SQL> select * from v$nls_parameters;    

PARAMETER                                                        VALUE                                                                                
---------------------------------------------------------------- ----------------------------------------------------------------                     
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE                                                                   
NLS_TERRITORY                                                    CHINA                                                                                
NLS_CURRENCY                                                     ¥                                                                                   
NLS_ISO_CURRENCY                                                 CHINA                                                                                
NLS_NUMERIC_CHARACTERS                                           .,                                                                                   
NLS_CALENDAR                                                     GREGORIAN                                                                            
NLS_DATE_FORMAT                                                  DD-MON-RR                                                                            
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE                                                                   
NLS_CHARACTERSET                                                 ZHS16GBK                                                                             
NLS_SORT                                                         BINARY                                                                               
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM                                                                       

PARAMETER                                                        VALUE                                                                                
---------------------------------------------------------------- ----------------------------------------------------------------                     
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM                                                             
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR                                                                   
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR                                                         
NLS_DUAL_CURRENCY                                                ¥                                                                                   
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                                            
NLS_COMP                                                         BINARY                                                                               
NLS_LENGTH_SEMANTICS                                             BYTE                                                                                 
NLS_NCHAR_CONV_EXCP                                              FALSE                                                                                

已选择 19 行。

SQL> --修改日期格式(system表示在全局有效)
SQL> alter system set NLS_DATE_FORMAT='yyyy-mm-dd';

SQL> --修改日期格式(session表示在当前会话中有效)
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

会话已更改。

SQL> select *
  2  from emp
  3  where hiredate='1981-11-17';

     EMPNO ENAME    JOB              MGR HIREDATE     SAL       COMM     DEPTNO                                                                       
---------- -------- --------- ---------- ---------- ----- ---------- ----------                                                                       
      7839 KING     PRESIDENT            1981-11-17  5000                    10                                                                       

SQL> select *
  2  from emp
  3  where hiredate='17-11月-81';
where hiredate='17-11月-81'
               *
第 3 行出现错误: 
ORA-01861: 文字与格式字符串不匹配 


SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';

会话已更改。

SQL> host cls

SQL> --比较运算(比较运算符)
SQL> --特别注意:
SQL> --        java中:int a = 0;
SQL> --        pl/sql中:a number := 0;


SQL> --between xxx and xxx
SQL> --查询薪水1000~2000之间的员工
SQL> select *
  2  from emp
  3  where sal between 1000 and 2000;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

已选择 6 行。

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where sal between 2000 and 1000
SQL> /

未选定行

SQL>  --between xxx and xxx        1. 含有边界     2. 小值在前 大值在后
SQL> host cls

SQL> --in 在集合中
SQL> --查询部门编号为10和20号的员工
SQL> select *
  2  from emp
  3  where deptno in (10,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> --查询不是部门编号为10和20号的员工
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where deptno not in (10,20)
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   

已选择 6 行。

SQL> --null 值    3. 如果集合中含有null,不能使用not in; 但可以使用in
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where deptno not in (10,20,null)
SQL> /

未选定行

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where deptno in (10,20,null)
SQL> /

     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> --思考:上面的原因是什么?
SQL> host cls

SQL> --like 模糊查询
SQL> --查询员工名字以S打头的员工
SQL> select *
  2  from emp
  3  where ename like 'S%';

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   

SQL> --查询员工名字是4个字的员工
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where ename like '____'
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   

SQL> insert into emp(empno,ename,sal,deptno) values(1001,'Tom_AB',3000,10);

已创建 1 行。

SQL> select * from emp;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10                                                                   
      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                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

已选择 15 行。

SQL> --查询员工名字中含有下划线的员工
SQL> select *
  2  from emp
  3  where ename like '%_%';

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10                                                                   
      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                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

已选择 15 行。

SQL> --百分号和下划线在模糊查询中都有特殊的含义
SQL> --转意字符
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where ename like '%\_%' escape '\'        --使用escape声明转义字符,前后一致即可
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_AB                                        3000                    10                                                                   

SQL> rollback;

回退已完成。

SQL> select * from emp;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   

     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> --MySQL中通过start transaction开启事务的,属于手动开启事务。
SQL> --Oracle中是自动开启事务的。

SQL> --SQL 语句优化(数据库的优化)
SQL> --        2. where的解析顺序:从右 --> 到左,所以我们应该尽量把为假的条件放在右边,让它先被解析
SQL> host cls

SQL> --单列的排序
SQL> --查询员工信息:按照月薪排序(默认为升序:从小到大)
SQL> select * from emp order by sal;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   

已选择 14 行。

SQL> --order by 后面 + 列/表达式/列的别名/列的序号(列的序号从1开始)
SQL> select empno,ename,sal,sal*12
  2  from emp
  3  order by sal*12 desc;        --desc降序(从大到小)

     EMPNO ENAME      SAL     SAL*12                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7839 KING      5000      60000                                                                                                                  
      7902 FORD      3000      36000                                                                                                                  
      7788 SCOTT     3000      36000                                                                                                                  
      7566 JONES     2975      35700                                                                                                                  
      7698 BLAKE     2850      34200                                                                                                                  
      7782 CLARK     2450      29400                                                                                                                  
      7499 ALLEN     1600      19200                                                                                                                  
      7844 TURNER    1500      18000                                                                                                                  
      7934 MILLER    1300      15600                                                                                                                  
      7521 WARD      1250      15000                                                                                                                  
      7654 MARTIN    1250      15000                                                                                                                  

     EMPNO ENAME      SAL     SAL*12                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7876 ADAMS     1100      13200                                                                                                                  
      7900 JAMES      950      11400                                                                                                                  
      7369 SMITH      800       9600                                                                                                                  

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select empno,ename,sal,sal*12 年薪
  2  from emp
  3* order by 年薪 desc
SQL> /

     EMPNO ENAME      SAL       年薪                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7839 KING      5000      60000                                                                                                                  
      7902 FORD      3000      36000                                                                                                                  
      7788 SCOTT     3000      36000                                                                                                                  
      7566 JONES     2975      35700                                                                                                                  
      7698 BLAKE     2850      34200                                                                                                                  
      7782 CLARK     2450      29400                                                                                                                  
      7499 ALLEN     1600      19200                                                                                                                  
      7844 TURNER    1500      18000                                                                                                                  
      7934 MILLER    1300      15600                                                                                                                  
      7521 WARD      1250      15000                                                                                                                  
      7654 MARTIN    1250      15000                                                                                                                  

     EMPNO ENAME      SAL       年薪                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7876 ADAMS     1100      13200                                                                                                                  
      7900 JAMES      950      11400                                                                                                                  
      7369 SMITH      800       9600                                                                                                                  

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select empno,ename,sal,sal*12 年薪
  2  from emp
  3* order by 4 desc    --将第4列的数据按照降序排序
SQL> /

     EMPNO ENAME      SAL       年薪                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7839 KING      5000      60000                                                                                                                  
      7902 FORD      3000      36000                                                                                                                  
      7788 SCOTT     3000      36000                                                                                                                  
      7566 JONES     2975      35700                                                                                                                  
      7698 BLAKE     2850      34200                                                                                                                  
      7782 CLARK     2450      29400                                                                                                                  
      7499 ALLEN     1600      19200                                                                                                                  
      7844 TURNER    1500      18000                                                                                                                  
      7934 MILLER    1300      15600                                                                                                                  
      7521 WARD      1250      15000                                                                                                                  
      7654 MARTIN    1250      15000                                                                                                                  

     EMPNO ENAME      SAL       年薪                                                                                                                  
---------- -------- ----- ----------                                                                                                                  
      7876 ADAMS     1100      13200                                                                                                                  
      7900 JAMES      950      11400                                                                                                                  
      7369 SMITH      800       9600                                                                                                                  

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select empno,ename,sal,sal*12 年薪
  2  from emp
  3* order by 5 desc
SQL> /
order by 5 desc
         *
第 3 行出现错误: 
ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目 


SQL> host cls


SQL> --多个列的排序
SQL> select *
  2  from emp
  3  order by deptno,sal;        --原因:order by 升序作用于后面的所有的列

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* order by deptno,sal desc    --原因:order by 降序只作用于距离desc最近的列
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* order by deptno desc,sal desc
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7788 SCOTT    ANALYST         7566 19-4月 -87      3000                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   

已选择 14 行。

SQL> --order by 升序作用于后面所有的列;desc降序只作用于距离desc最近的列
SQL> host cls

SQL> --查询员工信息:按照奖金排序
SQL> --null值  4. null的排序
SQL> select * from emp order by comm;

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      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                                                                   
      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                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   

已选择 14 行。

SQL> set pagesize 20    --设置每页显示的行数
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      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                                                                   
      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                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1* select * from emp order by comm desc
SQL> /

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

已选择 14 行。

SQL> ed
已写入 file afiedt.buf

  1  select * 
  2  from emp 
  3  order by comm desc
  4* nulls last                --降序排序时,使得所有的空值在后面
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      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                                                                   
      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                                                                   
      7698 BLAKE    MANAGER         7839 01-5月 -81      2850                    30                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   
      7782 CLARK    MANAGER         7839 09-6月 -81      2450                    10                                                                   

已选择 14 行。

SQL> --为什么降序排序时,null值默认排在前面呢?
SQL> --原因:在Oracle中null值最大。
SQL> spool off

六、函数

学习函数的目的:为了简化操作。 注意:   1. 函数可以没有参数,但必须要有返回值。   2. 我们操作数据库,效率最高的是SQL语句。   3. 我们以后的很多业务逻辑都不是在Java代码中写的,而是在SQL语句当中或者pl/sql程序中写的。

6.1、单行函数

示例代码如下: 单行函数.txt

SQL> spool e:\单行函数.txt

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-字符函数
SQL> select lower('Hello World') 大写转小写,upper('Hello World') 小写转大写,initcap('hello world') 首字母大写
  2  from dual;

大写转小写  小写转大写  首字母大写                                              
----------- ----------- -----------                                             
hello world HELLO WORLD Hello World                                             

SQL> --substr(a,b)        从字符串a中,第b位开始取
SQL> select substr('Hello World',3) 子串 from dual;

子串                                                                            
---------                                                                       
llo World                                                                       

SQL> --substr(a,b,c)    从字符串a中,第b位开始取,取c位
SQL> select substr('Hello World',3,4) 子串 from dual;

子串                                                                            
----                                                                            
llo                                                                             

SQL> --instr(a,b)        从字符串a中查找b字符串的位置(下标从1开始)
SQL> select instr('Hello World','ll') 位置 from dual;

      位置                                                                      
----------                                                                      
         3                                                                      

SQL> --length 字符数    lengthb 字节数
SQL> select length('Hello World') 字符数,lengthb('Hello World') 字节数 from dual;

    字符数     字节数                                                           
---------- ----------                                                           
        11         11                                                           

SQL> ed
已写入 file afiedt.buf

  1* select length('北京') 字符,lengthb('北京') 字节 from dual
SQL> /

    字符数     字节数                                                           
---------- ----------                                                           
         2          4                                                           

SQL> --lpad 左填充        rpad 右填充
SQL> -- 示例:abcd --> 10 位
SQL> select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;

左填充     右填充                                                                   
---------- ----------                                                           
******abcd abcd******                                                           

SQL> --trim    去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR                                                                      
----------                                                                      
ello World                                                                      

SQL> --replace 替换
SQL> select replace('Hello World','l','*') from dual;

REPLACE('HE                                                                     
-----------                                                                     
He**o Wor*d                                                                     

SQL> host cls

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-数值函数
SQL> --四舍五入
SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,
  2         round(45.926,-1) 四,round(45.926,-2) 五
  3  from dual;

        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.93       45.9         46         50          0                          

SQL> --截断
SQL> ed
已写入 file afiedt.buf

  1  select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,
  2         trunc(45.926,-1) 四,trunc(45.926,-2) 五
  3* from dual
SQL> /

        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.92       45.9         45         40          0                          

SQL> --查询系统当前时间
SQL> --在MySQL中:select now()
SQL> --在Oracle中,如下:
SQL> select sysdate from dual;

SYSDATE
--------------
12-9月 -18

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2018-09-12 12:24:46                                                            

SQL> --昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

昨天           今天           明天
-------------- -------------- --------------
11-9月 -18     12-9月 -18     13-9月 -18                                      

SQL> --计算员工的工龄:天  星期  月  年
SQL> select ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
  2                         (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
  3  from emp;

ENAME      HIREDATE               天       星期         月         年           
---------- -------------- ---------- ---------- ---------- ----------           
SMITH      17-12月-80     12758.6314 1822.66163 425.287713 34.9551545           
ALLEN      20-2月 -81     12693.6314 1813.37591 423.121046 34.7770723           
WARD       22-2月 -81     12691.6314  1813.0902  423.05438 34.7715928           
JONES      02-4月 -81     12652.6314 1807.51877  421.75438 34.6647435           
MARTIN     28-9月 -81     12473.6314 1781.94734 415.787713 34.1743326           
BLAKE      01-5月 -81     12623.6314 1803.37591 420.787713 34.5852915           
CLARK      09-6月 -81     12584.6314 1797.80448 419.487713 34.4784422           
SCOTT      19-4月 -87     10444.6314  1492.0902  348.15438 28.6154285           
KING       17-11月-81     12423.6314 1774.80448 414.121046 34.0373463           
TURNER     08-9月 -81     12493.6314 1784.80448  416.45438 34.2291271           
ADAMS      23-5月 -87     10410.6314 1487.23306 347.021046 28.5222778           

ENAME      HIREDATE               天       星期         月         年           
---------- -------------- ---------- ---------- ---------- ----------           
JAMES      03-12月-81     12407.6314 1772.51877 413.587713 33.9935107           
FORD       03-12月-81     12407.6314 1772.51877 413.587713 33.9935107           
MILLER     23-1月 -82     12356.6314 1765.23306 411.887713 33.8537846           

已选择 14 行。

SQL> select sysdate+hiredate from emp;
select sysdate+hiredate from emp
              *
第 1 行出现错误: 
ORA-00975: 不允许日期 + 日期 

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-日期函数
SQL> --months_between 两个日期相差的月数,是准确值
SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二
  2  from emp;

ENAME      HIREDATE               一         二                                 
---------- -------------- ---------- ----------                                 
SMITH      17-12月-80     425.287784 419.181726                                 
ALLEN      20-2月 -81     423.121117 417.084952                                 
WARD       22-2月 -81     423.054451        417                                 
JONES      02-4月 -81     421.754451 415.665597                                 
MARTIN     28-9月 -81     415.787784 409.826888                                 
BLAKE      01-5月 -81     420.787784 414.697855                                 
CLARK      09-6月 -81     419.487784 413.439791                                 
SCOTT      19-4月 -87     348.154451  343.11721                                 
KING       17-11月-81     414.121117 408.181726                                 
TURNER     08-9月 -81     416.454451 410.472049                                 
ADAMS      23-5月 -87     347.021117 341.988178                                 

ENAME      HIREDATE               一         二                                 
---------- -------------- ---------- ----------                                 
JAMES      03-12月-81     413.587784 407.633339                                 
FORD       03-12月-81     413.587784 407.633339                                 
MILLER     23-1月 -82     411.887784 405.988178                                 

已选择 14 行。

SQL> --add_months 在指定日期中加上若干月数,示例:当前系统日期加上56个月后
SQL> select add_months(sysdate,56) from dual;

ADD_MONTHS(SYS                                                                  
--------------                                                                  
22-7月 -20      

SQL> --last_day 本月的最后一天
SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA                                                                  
--------------                                                                  
30-11月-15                                                                      

SQL> --next_day 指定日期的下一个日期
SQL> --示例:下一个星期日
SQL> select next_day(sysdate,'星期日') from dual;

NEXT_DAY(SYSDA                                                                  
--------------                                                                  
29-11月-15                                                                      

SQL> --示例:下一个星期一
SQL> select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA                                                                  
--------------                                                                  
23-11月-15                                                                      

SQL> /*
SQL> next_day的应用:设定每个星期一自动备份数据
SQL>     1. 分布式数据库:异地容灾,最成功案例,911
SQL>     2. 快照或者使用触发器做数据库的备份和同步
SQL> */
SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;

ROUND(SYSDATE, ROUND(SYSDATE,                                                   
-------------- --------------                                                   
01-12月-15     01-1月 -16                                                       

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-转换函数
SQL> --2015-11-22 15:32:12今天是星期日
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI                                              
----------------------------------                                              
2015-11-22 15:33:55今天是星期日                                                 

SQL> --查询员工薪水:两位小数 千位符 本地货币代码
SQL> select to_char(sal,'L9,999.99') from emp;

TO_CHAR(SAL,'L9,999                                                             
-------------------                                                             
           ¥800.00                                                             
         ¥1,600.00                                                             
         ¥1,250.00                                                             
         ¥2,975.00                                                             
         ¥1,250.00                                                             
         ¥2,850.00                                                             
         ¥2,450.00                                                             
         ¥3,000.00                                                             
         ¥5,000.00                                                             
         ¥1,500.00                                                             
         ¥1,100.00                                                             

TO_CHAR(SAL,'L9,999                                                             
-------------------                                                             
           ¥950.00                                                             
         ¥3,000.00                                                             
         ¥1,300.00                                                             

已选择 14 行。

SQL> host cls

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-通用函数
SQL> --适用于任何数据类型,同时也适用于空值。
SQL> --nvl2(a,b,c)    当a=null的时候,返回c;否则返回b
SQL> select sal*12+nvl2(comm,comm,0) from emp;

SAL*12+NVL2(COMM,COMM,0)                                                        
------------------------                                                        
                    9600                                                        
                   19500                                                        
                   15500                                                        
                   35700                                                        
                   16400                                                        
                   34200                                                        
                   29400                                                        
                   36000                                                        
                   60000                                                        
                   18000                                                        
                   13200                                                        

SAL*12+NVL2(COMM,COMM,0)                                                        
------------------------                                                        
                   11400                                                        
                   36000                                                        
                   15600                                                        

已选择 14 行。

SQL> --nullif(a,b)    当a=b的时候,返回null;否则返回a
SQL> select nullif('abc','abc') 值 from dual;

值                                                                              
---                                                                             


SQL> select nullif('abc','abcd') 值 from dual;

值                                                                              
---                                                                             
abc                                                                             

SQL> --coalesce(a,b,c,...,n)    从左到右找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值"
  2  from emp;

      COMM        SAL 第一个不为null的值                                        
---------- ---------- ------------------                                        
                  800                800                                        
       300       1600                300                                        
       500       1250                500                                        
                 2975               2975                                        
      1400       1250               1400                                        
                 2850               2850                                        
                 2450               2450                                        
                 3000               3000                                        
                 5000               5000                                        
         0       1500                  0                                        
                 1100               1100                                        

      COMM        SAL 第一个不为null的值                                        
---------- ---------- ------------------                                        
                  950                950                                        
                 3000               3000                                        
                 1300               1300                                        

已选择 14 行。

SQL> host cls

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --单行函数-条件表达式
SQL> --涨工资,总裁1000 经理 800 其他 400
SQL> set linesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                              
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                              
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                              
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                              
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                              
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                              
      7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                              
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                              
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              

     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> select ename,job,sal 涨前,
  2         case job when 'PRESIDENT' then sal+1000
  3                  when 'MANAGER' then sal+800
  4                  else sal+400
  5         end 涨后
  6  from emp;

ENAME      JOB             涨前       涨后                                                                                                                                                              
---------- --------- ---------- ----------                                                                                                                                                              
SMITH      CLERK            800       1200                                                                                                                                                              
ALLEN      SALESMAN        1600       2000                                                                                                                                                              
WARD       SALESMAN        1250       1650                                                                                                                                                              
JONES      MANAGER         2975       3775                                                                                                                                                              
MARTIN     SALESMAN        1250       1650                                                                                                                                                              
BLAKE      MANAGER         2850       3650                                                                                                                                                              
CLARK      MANAGER         2450       3250                                                                                                                                                              
SCOTT      ANALYST         3000       3400                                                                                                                                                              
KING       PRESIDENT       5000       6000                                                                                                                                                              
TURNER     SALESMAN        1500       1900                                                                                                                                                              
ADAMS      CLERK           1100       1500                                                                                                                                                              

ENAME      JOB             涨前       涨后                                                                                                                                                              
---------- --------- ---------- ----------                                                                                                                                                              
JAMES      CLERK            950       1350                                                                                                                                                              
FORD       ANALYST         3000       3400                                                                                                                                                              
MILLER     CLERK           1300       1700                                                                                                                                                              

已选择 14 行。

SQL> select ename,job,sal 涨前,
  2         decode(job,'PRESIDENT',sal+1000,
  3                    'MANAGER',sal+800,
  4                              sal+400) 涨后
  5  from emp;

ENAME      JOB             涨前       涨后                                                                                                                                                              
---------- --------- ---------- ----------                                                                                                                                                              
SMITH      CLERK            800       1200                                                                                                                                                              
ALLEN      SALESMAN        1600       2000                                                                                                                                                              
WARD       SALESMAN        1250       1650                                                                                                                                                              
JONES      MANAGER         2975       3775                                                                                                                                                              
MARTIN     SALESMAN        1250       1650                                                                                                                                                              
BLAKE      MANAGER         2850       3650                                                                                                                                                              
CLARK      MANAGER         2450       3250                                                                                                                                                              
SCOTT      ANALYST         3000       3400                                                                                                                                                              
KING       PRESIDENT       5000       6000                                                                                                                                                              
TURNER     SALESMAN        1500       1900                                                                                                                                                              
ADAMS      CLERK           1100       1500                                                                                                                                                              

ENAME      JOB             涨前       涨后                                                                                                                                                              
---------- --------- ---------- ----------                                                                                                                                                              
JAMES      CLERK            950       1350                                                                                                                                                              
FORD       ANALYST         3000       3400                                                                                                                                                              
MILLER     CLERK           1300       1700                                                                                                                                                              

已选择 14 行。

SQL> spool off

6.2、多行函数(组函数/分组函数)

示例代码如下: 多行函数.txt

SQL> host cls

SQL> --//////////////////////////////////////////////////////////////////////////
SQL> --常用的组函数:sum()、count()、avg()、max()、min()
SQL> --查询员工的工资总额
SQL> select sum(sal) from emp;

  SUM(SAL)                                                                                                                                                                                              
----------                                                                                                                                                                                              
     29025                                                                                                                                                                                              

SQL> --查询员工的人数
SQL> select count(*) from emp;

  COUNT(*)                                                                                                                                                                                              
----------                                                                                                                                                                                              
        14                                                                                                                                                                                              

SQL> --查询员工的平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

        一         二                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
2073.21429 2073.21429                                                                                                                                                                                   

SQL> --查询员工的平均奖金
SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三
  2  from emp;

        一         二         三                                                                                                                                                                        
---------- ---------- ----------                                                                                                                                                                        
157.142857        550        550                                                                                                                                                                        

SQL> select count(*),count(comm) from emp;

  COUNT(*) COUNT(COMM)                                                                                                                                                                                  
---------- -----------                                                                                                                                                                                  
        14           4                                                                                                                                                                                  

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                              
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                              
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                              
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                              
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                              
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                              
      7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                              
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                              
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              

     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> --null值    5.组函数/多行函数,会自动滤空。
SQL> select count(*),count(nvl(comm,0)) from emp;    --把所有的null值变为0

  COUNT(*) COUNT(NVL(COMM,0))                                                                                                                                                                           
---------- ------------------                                                                                                                                                                           
        14                 14                                                                                                                                                                           

SQL> --null值    5.对于组函数/多行函数,会自动滤空。我们可以嵌套滤空函数,来屏蔽该函数的滤空功能。
SQL> host cls

SQL> --求emp表中每个部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno;

    DEPTNO   AVG(SAL)                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        30 1566.66667                                                                                                                                                                                   
        20       2175                                                                                                                                                                                   
        10 2916.66667    

SQL> --注意:Oracle中的规定,在select列表中所有未包含在组函数中的列都应该包含在 group by 子句中。
SQL> --注意:Oracle中的规定,包含在 group by 子句中的列不必包含在select列表中。

SQL> --多个列的分组:如果第一个列相同,再按照第二列进行分组...
SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by deptno,job
  4  order by 1;

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

已选择 9 行。

SQL> --过滤分组
SQL> --求平均工资大于2000的部门
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having avg(sal) > 2000;

    DEPTNO   AVG(SAL)                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        20       2175                                                                                                                                                                                   
        10 2916.66667                                                                                                                                                                                   

SQL> --where和having的区别:where子句不能使用多行函数。
SQL> --查询10号部门的平均工资
SQL> select deptno,avg(sal)
  2  from emp
  3  group by deptno
  4  having deptno=10;

    DEPTNO   AVG(SAL)                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        10 2916.66667                                                                                                                                                                                   

SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sal)
  2  from emp
  3  where deptno=10
  4* group by deptno
SQL> /

    DEPTNO   AVG(SAL)                                                                                                                                                                                   
---------- ----------                                                                                                                                                                                   
        10 2916.66667                                                                                                                                                                                   

SQL> --SQL 语句优化
SQL> --        3. 尽量使用where
SQL> host cls

SQL> /*
SQL> group by 语句的增强
SQL> select deptno,job,sum(sal) from emp group by deptno,job
SQL> +
SQL> select deptno,sum(sal) from emp group by deptno
SQL> +
SQL> select sum(sal) from emp
SQL> 
SQL> ===
SQL> 
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
SQL> 
SQL> 抽象
SQL> group by rollup(a,b)
SQL> ==
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL> 
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 行。

SQL> break on deptno skip 2
SQL> /

    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         
        10 CLERK           1300                                                                                                                                                                         
           MANAGER         2450                                                                                                                                                                         
           PRESIDENT       5000                                                                                                                                                                         
                           8750                                                                                                                                                                         


        20 CLERK           1900                                                                                                                                                                         
           ANALYST         6000                                                                                                                                                                         
           MANAGER         2975                                                                                                                                                                         
                          10875                                                                                                                                                                         


    DEPTNO JOB         SUM(SAL)                                                                                                                                                                         
---------- --------- ----------                                                                                                                                                                         

        30 CLERK            950                                                                                                                                                                         
           MANAGER         2850                                                                                                                                                                         
           SALESMAN        5600                                                                                                                                                                         
                           9400                                                                                                                                                                         


                          29025                                                                                                                                                                         



已选择 13 行。

SQL>  break on null
SQL> /

    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> spool off

七、多表查询

笛卡尔积 图解:

在实际运行环境中,应避免使用笛卡尔全集。 连接条件至少有n-1个。(n表示表的个数)

学习多表查询,就是学习连接条件,根据连接条件的不同,可以把Oracle中的多表查询划分为以下几种:
    Oracle的连接        
        等值连接:连接条件之间是等号。
        不等值连接
        外连接
        内连接
    SQL99的连接
        参考即可,有空可以看看。

示例代码如下: 多表查询.txt

SQL> host cls

SQL> --多表查询
SQL> --等值连接
SQL> --查询员工信息:员工号 姓名 月薪 部门名称
SQL> set linesize 80
SQL> desc dept        --查询部门表的结构
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> select e.empno,e.ename,e.sal,d.dname
  2  from emp e,dept d
  3  where e.deptno=d.deptno;

     EMPNO ENAME             SAL DNAME                                          
---------- ---------- ---------- --------------                                 
      7369 SMITH             800 RESEARCH                                       
      7499 ALLEN            1600 SALES                                          
      7521 WARD             1250 SALES                                          
      7566 JONES            2975 RESEARCH                                       
      7654 MARTIN           1250 SALES                                          
      7698 BLAKE            2850 SALES                                          
      7782 CLARK            2450 ACCOUNTING                                     
      7788 SCOTT            3000 RESEARCH                                       
      7839 KING             5000 ACCOUNTING                                     
      7844 TURNER           1500 SALES                                          
      7876 ADAMS            1100 RESEARCH                                       

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

已选择 14 行。

SQL> --不等值连接
SQL> --查询员工信息:员工号 姓名 月薪 工资级别
SQL> select * from salgrade;

     GRADE      LOSAL      HISAL                                                
---------- ---------- ----------                                                
         1        700       1200                                                
         2       1201       1400                                                
         3       1401       2000                                                
         4       2001       3000                                                
         5       3001       9999                                                

SQL> select e.empno,e.ename,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

     EMPNO ENAME             SAL      GRADE                                     
---------- ---------- ---------- ----------                                     
      7369 SMITH             800          1                                     
      7900 JAMES             950          1                                     
      7876 ADAMS            1100          1                                     
      7521 WARD             1250          2                                     
      7654 MARTIN           1250          2                                     
      7934 MILLER           1300          2                                     
      7844 TURNER           1500          3                                     
      7499 ALLEN            1600          3                                     
      7782 CLARK            2450          4                                     
      7698 BLAKE            2850          4                                     
      7566 JONES            2975          4                                     

     EMPNO ENAME             SAL      GRADE                                     
---------- ---------- ---------- ----------                                     
      7788 SCOTT            3000          4                                     
      7902 FORD             3000          4                                     
      7839 KING             5000          5                                     

已选择 14 行。

SQL> host cls

SQL> --外连接
SQL> --按部门统计员工人数:部门号 部门名称 员工人数
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 员工人数
  2  from emp e,dept d
  3  where e.deptno=d.deptno
  4  group by d.deptno,d.dname;

    部门号 部门名称         员工人数                                            
---------- -------------- ----------                                            
        10 ACCOUNTING              3                                            
        20 RESEARCH                5                                            
        30 SALES                   6                                            

SQL> select * from dept;

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

SQL> select * from emp where deptno=40;

未选定行

SQL> /*
SQL> 我们希望:对于某些不成立的记录,仍然希望包含在最后的结果中
SQL> 左外连接:当where e.deptno=d.deptno不成立的时候,等号左边的表仍然被包含
SQL>     写法:where e.deptno=d.deptno(+)
SQL> 右外连接:当where e.deptno=d.deptno不成立的时候,等号右边的表仍然被包含
SQL>     写法:where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门编号,d.dname 部门名称,count(e.empno) 员工人数
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno,d.dname
  5  order by d.deptno;

  部门编号 部门名称         员工人数
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                5
        30 SALES                   6
        40 OPERATIONS              0

SQL> host cls

SQL> --自连接
SQL> --查询员工信息:员工姓名 老板姓名
SQL> set linesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                                                                                                              
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                                                                                              
      7369 SMITH      CLERK           7902 17-12月-80            800                    20                                                                                                              
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30                                                                                                              
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30                                                                                                              
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                                                                                              
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                                                                                              
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                                                                                              
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                                                                                              
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                                                                                              
      7839 KING       PRESIDENT            17-11月-81           5000                    10                                                                                                              
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30                                                                                                              
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                                                                                              

     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> --自连接:通过表的别名,将同一张表视为多张表
SQL> select e.ename 员工姓名,b.ename 老板姓名
  2  from emp e,emp b
  3  where e.mgr=b.empno;    --员工的老板号=老板的员工号

员工姓名   老板姓名                                                                                                                                                                                     
---------- ----------                                                                                                                                                                                   
FORD       JONES                                                                                                                                                                                        
SCOTT      JONES                                                                                                                                                                                        
JAMES      BLAKE                                                                                                                                                                                        
TURNER     BLAKE                                                                                                                                                                                        
MARTIN     BLAKE                                                                                                                                                                                        
WARD       BLAKE                                                                                                                                                                                        
ALLEN      BLAKE                                                                                                                                                                                        
MILLER     CLARK                                                                                                                                                                                        
ADAMS      SCOTT                                                                                                                                                                                        
CLARK      KING                                                                                                                                                                                         
BLAKE      KING                                                                                                                                                                                         

员工姓名   老板姓名                                                                                                                                                                                     
---------- ----------                                                                                                                                                                                   
JONES      KING                                                                                                                                                                                         
SMITH      FORD                                                                                                                                                                                         

已选择 13 行。


SQL> 注意:自连接查询的缺点:在性能上有问题,效率不高,因为在本质是,自连接查询属于多表查询。会产生笛卡尔全集。
SQL> 注意:自连接查询的优点:查询的结果直观。

SQL> select count(*)
  2  from emp e,emp b;

  COUNT(*)                                                                                                                                                                                              
----------                                                                                                                                                                                              
       196                                                                                                                                                                                              

SQL> --所以自连接查询不适合操作大表。

SQL> --层次查询
SQL> --注意:层次查询只有一张表,因为只有在一张表的情况下,才不会产生笛卡尔集。
SQL> --所以层次查询在本质上属于单表查询。不属于多表查询。
SQL> set pagesize 20
SQL> select level 深度,empno 员工编号,ename 员工姓名,mgr 员工老板编号    --level深度,伪列之一,Oracle中伪列的特点:想要得到伪列的值,必须要在查询语句中写出来。
  2  from emp
  3  connect by prior empno=mgr        --上一层的员工号=当前层的老板号
  4  start with mgr is null            --起始条件:empno=7939 或者 只有根节点可以这样写
  5  order by 1;                    --将第1列的数据按照升序排序(默认升序)

      深度   员工编号 员工姓名   员工老板编号
---------- ---------- ---------- ------------
         1       7839 KING
         2       7566 JONES              7839
         2       7698 BLAKE              7839
         2       7782 CLARK              7839
         3       7902 FORD               7566
         3       7521 WARD               7698
         3       7900 JAMES              7698
         3       7934 MILLER             7782
         3       7499 ALLEN              7698
         3       7788 SCOTT              7566
         3       7654 MARTIN             7698
         3       7844 TURNER             7698
         4       7876 ADAMS              7788
         4       7369 SMITH              7902

已选择 14 行。

SQL> --注意:不是每种操作是万能的,每一种操作都有优缺点。
SQL> --注意:层次连接查询的缺点:查询的结果不够直观。
SQL> --注意:层次连接查询的优点:不会产生笛卡尔全集。
SQL> --实际工作中,见招拆招,随机应变!

SQL> spool off

层次查询图解:

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------多表查询

  SUM(SAL)                                                                      ...

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

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

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

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

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

21750
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------基本查询

TNAME                          TABTYPE  CLUSTERID                               

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

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

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

30750
来自专栏数据库新发现

OPTIMIZER_INDEX_COST_ADJ与成本计算

OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间. 该参数表示索引扫描和全表扫描成本的比较。缺省值1...

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

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

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

28990
来自专栏数据库新发现

如何从结果集中获得随机结果

全表扫描(Full table Scan) 全表扫描返回表中所有的记录。 执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Orac...

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

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

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

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

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

36340

扫码关注云+社区

领取腾讯云代金券