Oracle学习(二):过滤和排序

Oracle学习(二):过滤和排序
强烈推介IDEA2020.2破解激活,IntelliJ IDEA 注册码,2020.2 IDEA 激活码

1.知识点:可以对照下面的录屏进行阅读

SQL> --字符串大小写敏感
SQL> --查询名叫KING的员工信息
SQL> select *
  2  from emp
  3  where ename = 'KING';

SQL> --日期格式敏感
SQL> --查询入职日期为17-11月-81的员工
SQL> select *
  2  from emp
  3  where hiredate='17-11月-81';	--正确例子
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 9i之前,日期格式 DD-MON-YY  1998 --> 98  2098 --> 98 ; Oracle 9i之后  DD-MON-RR
SQL> --当前时间
SQL> select sysdate from dual;  

SQL> --查询系统参数
SQL> select * from v$nls_parameters;
SQL> --v$nls_parameters 数据字典

SQL> --修改日期格式
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

SQL> --between ..and:在...之间
SQL> -- 1. 含边界  2. 小值在前,大值在后
SQL> --查询薪水1000~2000的员工
SQL> select *
  2  from emp
  3  where sal between 1000 and 2000;

SQL> --in: 在集合中
SQL> --查询10和20号部门的员工
SQL> select *
  2  from emp
  3  where deptno in (10,20);

SQL> --查询不是10和20号部门的员工
SQL> select *
  2  from emp
  3  where deptno not in (10,20)
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where deptno not in (10,20,null)	--在not in的作用范围里添加null字段后,显示结果为空
SQL> --结论:如果集合中含有null值,不能使用not in操作符;但可以使用in

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

SQL> --查询名字是四个字的员工
SQL> select *
  2  from emp
  3  where ename like '____';	--4个_

SQL> --查询名字中含义下划线的员工
SQL> --使用转义字符
SQL> select *
  2  from emp
  3  where  ename like '%\_%' escape '\'  --转义字符不固定,但通常用’\’

SQL> --逻辑运算符
SQL> --and or
SQL> --SQL优化:2. 解析顺序: 从右至左

SQL> --排序
SQL> --查询员工信息,按照月薪排序
SQL> select *
  2  from emp
  3  order by sal;

SQL> --a命令  append,追加命令
SQL> a   desc 	--a命令后空格至少2个空格
  3* order by sal  desc

SQL> --order by 后面 + 列名, 表达式, 别名, 序号
SQL> select ename,sal,comm,sal*12+nvl(comm,0)
  2  from emp
  3  order by sal*12+nvl(comm,0);	--列名,表达式
SQL> ed
已写入 file afiedt.buf

  1  select ename,sal,comm,sal*12+nvl(comm,0) 年收入		
  2  from emp
  3* order by 年收入	--别名
SQL> /
SQL> ed
已写入 file afiedt.buf

  1  select ename,sal,comm,sal*12+nvl(comm,0) 年收入
  2  from emp
  3* order by 4	--序号
SQL> /

SQL> --order by跟多列:作用于后面所有的列
SQL> --desc离他最近的一列
SQL> select *
  2  from emp
  3  order by deptno,sal desc	--先按deptno升序排列,然后按sal降序排列
SQL> ed
已写入 file afiedt.buf

  1  select  *
  2  from emp
  3  order by comm desc	--按comm降序排序
  4* nulls last		--空值放最后


2.在Sqlplus下实际执行的结果录屏:

SQL> --查询10号部门员工
SQL> select *
  2  from emp
  3  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> --查询名叫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
已写入 file afiedt.buf

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

未选定行

SQL> --日期格式敏感
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> 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> Oracle 9i之前,日期格式
SQL> DD-MON-YY  1998 --> 98
SQL>            2098 --> 98
SQL> 
SQL> Oracle 9i之后
SQL> DD-MON-RR
SQL> */
SQL> --当前时间
SQL> select sysdate from dual;

SYSDATE                                                                                                                 
--------------                                                                                                          
22-10月-12                                                                                                              

SQL> --查询系统参数
SQL> select * from v$nls_parameters;

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_LANGUAGE                                                                                                            
SIMPLIFIED CHINESE                                                                                                      
                                                                                                                        
NLS_TERRITORY                                                                                                           
CHINA                                                                                                                   
                                                                                                                        
NLS_CURRENCY                                                                                                            
¥                                                                                                                      
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_ISO_CURRENCY                                                                                                        
CHINA                                                                                                                   
                                                                                                                        
NLS_NUMERIC_CHARACTERS                                                                                                  
.,                                                                                                                      
                                                                                                                        
NLS_CALENDAR                                                                                                            
GREGORIAN                                                                                                               
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_DATE_FORMAT                                                                                                         
DD-MON-RR                                                                                                               
                                                                                                                        
NLS_DATE_LANGUAGE                                                                                                       
SIMPLIFIED CHINESE                                                                                                      
                                                                                                                        
NLS_CHARACTERSET                                                                                                        
ZHS16GBK                                                                                                                
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_SORT                                                                                                                
BINARY                                                                                                                  
                                                                                                                        
NLS_TIME_FORMAT                                                                                                         
HH.MI.SSXFF AM                                                                                                          
                                                                                                                        
NLS_TIMESTAMP_FORMAT                                                                                                    
DD-MON-RR HH.MI.SSXFF AM                                                                                                
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_TIME_TZ_FORMAT                                                                                                      
HH.MI.SSXFF AM TZR                                                                                                      
                                                                                                                        
NLS_TIMESTAMP_TZ_FORMAT                                                                                                 
DD-MON-RR HH.MI.SSXFF AM TZR                                                                                            
                                                                                                                        
NLS_DUAL_CURRENCY                                                                                                       
¥                                                                                                                      
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_NCHAR_CHARACTERSET                                                                                                  
AL16UTF16                                                                                                               
                                                                                                                        
NLS_COMP                                                                                                                
BINARY                                                                                                                  
                                                                                                                        
NLS_LENGTH_SEMANTICS                                                                                                    
BYTE                                                                                                                    
                                                                                                                        

PARAMETER                                                                                                               
----------------------------------------------------------------                                                        
VALUE                                                                                                                   
----------------------------------------------------------------                                                        
NLS_NCHAR_CONV_EXCP                                                                                                     
FALSE                                                                                                                   
                                                                                                                        

已选择19行。

SQL> set linesize 150
SQL> col parameter for a 20
SP2-0246: 非法的 FORMAT 字符串"a"
SQL> col parameter for a20
SQL> select * from v$nls_parameters;

PARAMETER            VALUE                                                                                                                            
-------------------- ----------------------------------------------------------------                                                                 
NLS_LANGUAGE         SIMPLIFIED CHINESE                                                                                                               
NLS_TERRITORY        CHINA                                                                                                                            
NLS_CURRENCY         ¥                                                                                                                               
NLS_ISO_CURRENCY     CHINA                                                                                                                            
NLS_NUMERIC_CHARACTE .,                                                                                                                               
RS                                                                                                                                                    
                                                                                                                                                      
NLS_CALENDAR         GREGORIAN                                                                                                                        
NLS_DATE_FORMAT      DD-MON-RR                                                                                                                        
NLS_DATE_LANGUAGE    SIMPLIFIED CHINESE                                                                                                               
NLS_CHARACTERSET     ZHS16GBK                                                                                                                         

PARAMETER            VALUE                                                                                                                            
-------------------- ----------------------------------------------------------------                                                                 
NLS_SORT             BINARY                                                                                                                           
NLS_TIME_FORMAT      HH.MI.SSXFF AM                                                                                                                   
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM                                                                                                         
NLS_TIME_TZ_FORMAT   HH.MI.SSXFF AM TZR                                                                                                               
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR                                                                                                     
MAT                                                                                                                                                   
                                                                                                                                                      
NLS_DUAL_CURRENCY    ¥                                                                                                                               
NLS_NCHAR_CHARACTERS AL16UTF16                                                                                                                        
ET                                                                                                                                                    
                                                                                                                                                      

PARAMETER            VALUE                                                                                                                            
-------------------- ----------------------------------------------------------------                                                                 
NLS_COMP             BINARY                                                                                                                           
NLS_LENGTH_SEMANTICS BYTE                                                                                                                             
NLS_NCHAR_CONV_EXCP  FALSE                                                                                                                            

已选择19行。

SQL> col parameter for a30
SQL> host cls

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> --v$nls_parameters 数据字典
SQL> --修改日期格式
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

会话已更改。

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

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

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

会话已更改。

SQL> host cls

SQL> --between ..and 在。。。之间
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 13-7月 -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> /*
SQL> 1. 含边界
SQL> 2. 小值在前,大值在后
SQL> */
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 13-7月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -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> ed
已写入 file afiedt.buf

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

未选定行

SQL> --结论:如果集合中含义null值,不能使用not in操作符;但可以使用in
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 13-7月 -87      3000                    20                                                                   

SQL> --查询名字是四个字的员工
SQL> select *
  2  from emp
  3  where ename like '____';

     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> host cls

SQL> insert into emp
  2  (empno,ename,sal,deptno)
  3  values(1001,'Tom_123',2000,10);

已创建 1 行。

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 13-7月 -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 13-7月 -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                                                                   
      1001 Tom_123                                       2000                    10                                                                   

已选择15行。

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

     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 13-7月 -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 13-7月 -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                                                                   
      1001 Tom_123                                       2000                    10                                                                   

已选择15行。

SQL> --使用转义字符
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where  ename like '%\_%' escape '\'
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_123                                       2000                    10                                                                   

SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* where  ename like '%a_%' escape 'a'
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      1001 Tom_123                                       2000                    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 13-7月 -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 13-7月 -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> host cls

SQL> --逻辑运算符
SQL> --and or
SQL> --SQL优化:2. 解析顺序: 从右至左
SQL> host cls

SQL> --排序
SQL> --查询员工信息,按照月薪排序
SQL> select *
  2  from emp
  3  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 13-7月 -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 13-7月 -87      3000                    20                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   

已选择14行。

SQL> --a命令  append
SQL> a   desc
  3* order by sal  desc
SQL> /

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7902 FORD     ANALYST         7566 03-12月-81      3000                    20                                                                   
      7788 SCOTT    ANALYST         7566 13-7月 -87      3000                    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                                                                   
      7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30                                                                   
      7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30                                                                   
      7934 MILLER   CLERK           7782 23-1月 -82      1300                    10                                                                   
      7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30                                                                   
      7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30                                                                   

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                   
---------- -------- --------- ---------- -------------- ----- ---------- ----------                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -87      1100                    20                                                                   
      7900 JAMES    CLERK           7698 03-12月-81       950                    30                                                                   
      7369 SMITH    CLERK           7902 17-12月-80       800                    20                                                                   

已选择14行。

SQL> host cls

SQL> --order by 后面 + 列名, 表达式, 别名, 序号
SQL> select ename,sal,comm,sal*12+nvl(comm,0)
  2  from emp
  3  order by sal*12+nvl(comm,0);

ENAME      SAL       COMM SAL*12+NVL(COMM,0)                                                                                                          
-------- ----- ---------- ------------------                                                                                                          
SMITH      800                          9600                                                                                                          
JAMES      950                         11400                                                                                                          
ADAMS     1100                         13200                                                                                                          
WARD      1250        500              15500                                                                                                          
MILLER    1300                         15600                                                                                                          
MARTIN    1250       1400              16400                                                                                                          
TURNER    1500          0              18000                                                                                                          
ALLEN     1600        300              19500                                                                                                          
CLARK     2450                         29400                                                                                                          
BLAKE     2850                         34200                                                                                                          
JONES     2975                         35700                                                                                                          

ENAME      SAL       COMM SAL*12+NVL(COMM,0)                                                                                                          
-------- ----- ---------- ------------------                                                                                                          
SCOTT     3000                         36000                                                                                                          
FORD      3000                         36000                                                                                                          
KING      5000                         60000                                                                                                          

已选择14行。

SQL> ed
已写入 file afiedt.buf

  1  select ename,sal,comm,sal*12+nvl(comm,0) 年收入
  2  from emp
  3* order by 年收入
SQL> /

ENAME      SAL       COMM     年收入                                                                                                                  
-------- ----- ---------- ----------                                                                                                                  
SMITH      800                  9600                                                                                                                  
JAMES      950                 11400                                                                                                                  
ADAMS     1100                 13200                                                                                                                  
WARD      1250        500      15500                                                                                                                  
MILLER    1300                 15600                                                                                                                  
MARTIN    1250       1400      16400                                                                                                                  
TURNER    1500          0      18000                                                                                                                  
ALLEN     1600        300      19500                                                                                                                  
CLARK     2450                 29400                                                                                                                  
BLAKE     2850                 34200                                                                                                                  
JONES     2975                 35700                                                                                                                  

ENAME      SAL       COMM     年收入                                                                                                                  
-------- ----- ---------- ----------                                                                                                                  
SCOTT     3000                 36000                                                                                                                  
FORD      3000                 36000                                                                                                                  
KING      5000                 60000                                                                                                                  

已选择14行。

SQL> ed
已写入 file afiedt.buf

  1  select ename,sal,comm,sal*12+nvl(comm,0) 年收入
  2  from emp
  3* order by 4
SQL> /

ENAME      SAL       COMM     年收入                                                                                                                  
-------- ----- ---------- ----------                                                                                                                  
SMITH      800                  9600                                                                                                                  
JAMES      950                 11400                                                                                                                  
ADAMS     1100                 13200                                                                                                                  
WARD      1250        500      15500                                                                                                                  
MILLER    1300                 15600                                                                                                                  
MARTIN    1250       1400      16400                                                                                                                  
TURNER    1500          0      18000                                                                                                                  
ALLEN     1600        300      19500                                                                                                                  
CLARK     2450                 29400                                                                                                                  
BLAKE     2850                 34200                                                                                                                  
JONES     2975                 35700                                                                                                                  

ENAME      SAL       COMM     年收入                                                                                                                  
-------- ----- ---------- ----------                                                                                                                  
SCOTT     3000                 36000                                                                                                                  
FORD      3000                 36000                                                                                                                  
KING      5000                 60000                                                                                                                  

已选择14行。

SQL> ed
已写入 file afiedt.buf

  1  select ename,sal,comm,sal*12+nvl(comm,0) 年收入
  2  from emp
  3* order by 5
SQL> /
order by 5
         *
第 3 行出现错误: 
ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目 


SQL> host cls

SQL> --order by跟多列
SQL> select *
  2  from emp
  3  order by deptno,sal;

     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 13-7月 -87      1100                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7788 SCOTT    ANALYST         7566 13-7月 -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> --order by跟多列:作用于后面所有的列
SQL> ed
已写入 file afiedt.buf

  1  select *
  2  from emp
  3* order by deptno,sal 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 13-7月 -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 13-7月 -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> --desc离他最近的一列
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 13-7月 -87      3000                    20                                                                   
      7566 JONES    MANAGER         7839 02-4月 -81      2975                    20                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -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> host cls

SQL> --查询员工信息,按照奖金排序
SQL> select  *
  2  from emp
  3  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 13-7月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -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> select  *
  2  from emp
  3  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 13-7月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -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  *
  2  from emp
  3* 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 13-7月 -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 13-7月 -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 13-7月 -87      3000                    20                                                                   
      7839 KING     PRESIDENT            17-11月-81      5000                    10                                                                   
      7876 ADAMS    CLERK           7788 13-7月 -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> spool off

本文来源程序员囧辉,由javajgs_com转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/8790

发表评论