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