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