4.有关日期格式属性修改常识,v$nls_parameters,between and,查询指定部门的员工信息,in和null,like模糊查询,order by后面可以跟:列名、表达式、别名、序号



1 有关日期格式属性修改常识

NLS_DATE_FORMAT           DD-MON-RR

select sysdate from dual;

        

NLS_CURRENCY                

可以通过下面的方式查到上面的格式默认参数:

2 v$nls_parameters

        SQL>select * from v$nls_parameters;

                  结果略:

3修改默认参数

         alter session set NLS_DATE_FORMAT='DD-MON-RR';

         alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

         

----查询比8111入职晚的员工

        select*

        fromemp

where hiredate > '01-1 -81';

        运行结果略:

   注意:

          ------- oracle支持隐式类型转换 ..eg char****====>date,抛砖

          -------- 日期转换的函数.....

          ------ 日期和字符串''

          -------日期是格式敏感

4语法格式:

select ....

from .....

where col > 30

          col  in()

          col between a and b .. a要小b [] 

         

5查询工资在1000~2000之间的员工信息

          select *

          from emp

          where sal between 1000 and 2000;

 

          等价:

          select *

          from emp

          where sal >=1000 and sal <=2000 

         

               EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

---------- ---------- --------- ------------------------ ---------- ---------- ----------

     7499 ALLEN     SALESMAN        7698 20-2 -81          1600        300         30

     7521 WARD      SALESMAN        7698 22-2 -81          1250        500         30

     7654 MARTIN    SALESMAN        7698 28-9 -81          1250       1400         30

     7844 TURNER    SALESMAN        7698 08-9 -81          1500          0         30

     7876 ADAMS     CLERK           7788 23-5 -87          1100                    20

     7934 MILLER    CLERK           7782 23-1 -82          1300                    10

 

错误案例:

 1 select *

 2      from emp

 3*     where sal between 1000 and200

SQL> /

 

6查询10 20号部门的员工信息

select * from emp

        whereDEPTNO in (10, 20)

 

7 查询不是10 20号部门的员工信息

        select* from emp

        whereDEPTNO not in (10, 20)

          

                                EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

                           -------------------- --------- ---------- -------------- ---------- ---------- ----------

                                 7369 SMITH      CLERK          7902 17-12-80           800                    20

                                 7566 JONES      MANAGER        7839 02-4 -81          2975                    20

                                 7782 CLARK      MANAGER        7839 09-6 -81          2450                    10

                                 7788 SCOTT      ANALYST        7566 19-4 -87          3000                    20

                                 7839 KING       PRESIDENT           17-11-81          5000                    10

                                 7876 ADAMS      CLERK          7788 23-5 -87          1100                    20

                                 7902 FORD       ANALYST        7566 03-12-81          3000                    20

                                 7934 MILLER     CLERK          7782 23-1 -82          1300                    10

                                                            

                                                       

                                                       

8  in 中是一个集合可以有null

 select * from emp

           where DEPTNO in (10, 20, null)

          

            select * from emp

           where DEPTNO not in (10, 20)           

          

           ======>in集合中遇见null (in集合和空值可以在一起...)

           ======>in (集合中含有空值 )查询结果不受影响

           ======>not in (集合中含有空值 )查询结果受影响

         

9 like模糊查询

        A   % 代表零个或多个字符(任意个字符)

        B   _ 代表一个字符。

        C   查询名字S开头的员工信息

        select* from emp  where ENAME like 'S%'’; ---''单引号中的字符串是区分大小写的....

                           SQL>/

                                EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

                           -------------------- --------- ---------- -------------- ---------- ---------- ----------

                                 7369 SMITH      CLERK          7902 17-12-80           800                    20

                                 7788 SCOTT      ANALYST        7566 19-4 -87          3000                    20

 

                  

        D  查询名字含有4个字母的员工

         select * from emp where ENAME like '____';

                       EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

                  -------------------- --------- ---------- -------------- ---------- ---------- ----------

                        7521 WARD       SALESMAN       7698 22-2 -81          1250        500         30

                        7839 KING       PRESIDENT           17-11-81          5000                    10

                        7902 FORD       ANALYST        7566 03-12-81          3000                    20

 

        

        E   查询名字含有下划线的员工 (查询的内容含有转义字符)        

        insertinto emp(empno, ename, sal , DEPTNO) values(1, 'tom_abc', 8000, 10)

        

        select* from emp where ename like '%\_%' escape '\'

                       EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

                  -------------------- --------- ---------- -------------- ---------- ---------- ----------

                           1 tom_abc                                             8000                   10

 

10  order by 默认按照排序从小到大(升序)

 

11 order by后面可以跟:列名、表达式、别名、序号;

 

        A 按照薪水,查询员工信息

        select*from emp  order by sal

        selectempno, ename, sal, sal*12 年薪 from emp order by 年薪;

 

    EMPNO ENAME            SAL       年薪

---------- ---------- ---------- ----------

     7369 SMITH            800       9600

     7900 JAMES            950      11400

     7876 ADAMS           1100      13200

     7521 WARD            1250      15000

     7654 MARTIN          1250      15000

     7934 MILLER          1300      15600

     7844 TURNER          1500      18000

     7499 ALLEN           1600      19200

     7782 CLARK           2450      29400

     7698 BLAKE           2850      34200

     7566 JONES           2975      35700

     7902 FORD            3000      36000

     7788 SCOTT           3000      36000

     7839 KING            5000      60000

        1 tom_abc         8000      96000

 

        B  按照年薪,查询员工信息

        2order by 遇上空值

                  1任何表达式和null运算都为空

                  2null != null  (is null )

                  3where子句中(in集合和空值在一起...)

                  4

          

           按照奖金,查询员工信息

          

    selectempno, ename, comm from emp  orderby 3 desc  nulls last;

                                    SQL>/

                                    

                                         EMPNO ENAME            COMM

                                    -------------------- ----------

                                          7654 MARTIN           1400

                                          7521 WARD              500

                                          7499 ALLEN             300

                                           7844 TURNER             0

                                          7782 CLARK

                                          7788 SCOTT

                                          7839 KING

                                          7876 ADAMS

                                          7900 JAMES

                                          7902 FORD

                                          7698 BLAKE

                                          7566 JONES

                                          7934 MILLER

                                             1 tom_abc

                                          7369 SMITH

 

C order by 后面有多个列,

        如果多个列中desc修饰,只作用于最近的那一列

        按照部门排序

        select* from emp  order by deptno desc, sal desc;

        

        按照部门和工资排序

        select* from emp  order by deptno desc, saldesc;

   运行结果:

                                         EMPNO ENAME      JOB             MGR HIREDATE              SAL       COMM    DEPTNO

                           -------------------- --------- ---------- -------------- ---------- ---------- ----------

                                 7698 BLAKE      MANAGER        7839 01-5 -81          2850                    30

                                 7499 ALLEN      SALESMAN       7698 20-2 -81          1600        300         30

                                 7844 TURNER     SALESMAN       7698 08-9 -81          1500          0         30

                                 7521 WARD       SALESMAN       7698 22-2 -81          1250        500         30

                                 7654 MARTIN    SALESMAN        7698 28-9 -81          1250       1400         30

                                 7900 JAMES      CLERK          7698 03-12-81           950                    30

                                 7902 FORD       ANALYST        7566 03-12-81          3000                    20

                                 7788 SCOTT      ANALYST        7566 19-4 -87          3000                    20

                                 7566 JONES      MANAGER        7839 02-4 -81          2975                    20

                                 7876 ADAMS      CLERK          7788 23-5 -87          1100                    20

                                 7369 SMITH      CLERK          7902 17-12-80           800                    20

                                    1 tom_abc                                             8000                   10

                                 7839 KING       PRESIDENT           17-11-81          5000                    10

                                 7782 CLARK      MANAGER        7839 09-6 -81          2450                    10

                                 7934 MILLER     CLERK          7782 23-1 -82          1300                    10

                           

                           已选择15行。

 

上一篇:ApiBoot - ApiBoot Alibaba Sms 使用文档


下一篇:android init.rc中service console option的含义、作用