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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

探索ASH 第一篇

老是在用ASH,对它的依赖感觉已经大于AWR,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。 先看看v$active_sessi...

45480
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

42040
来自专栏杨建荣的学习笔记

生产系统调优之_毫秒级的改进 (92天)

生产中有一个sql语句,做了union-all操作,对于时间的要求是极其严格的,目前已经从2秒的改进调整到了1秒以内,在此基础上还想做进一步的调整,因为极其频繁...

35980
来自专栏乐沙弥的世界

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储;其次 是由于这个特...

11720
来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

35570
来自专栏乐沙弥的世界

Oracle字符集与字符类型存储空间占用

10720
来自专栏Jerry的SAP技术分享

How to update BOL entity property value via ABAP code

Suppose I have one product with ID I042416 which could be found in CRM WebClient...

8200
来自专栏杨建荣的学习笔记

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

29670
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(一) (r3笔记第37天)

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

30750
来自专栏数据库新发现

字符集问题的初步探讨(三)

http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm

8920

扫码关注云+社区

领取腾讯云代金券