Oracle 数据库入门之----------------------过滤和排序

过滤和排序

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 mep

  3  /

from mep

     *

第 2 行出现错误: 

ORA-00942: 表或视图不存在 

SQL> ed

已写入 file afiedt.buf

  1  select *

  2  from emp

  3* where ename='KING'

SQL> /

     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> --修改日期格式

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 200

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