oracle数据库过滤数据,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