SQL基础2——过滤和排序

原文转自一沙弥的世界的博客：

一、使用WHERE字句实现对数据的过滤

    用法：SELECT *|{[DISTINCT] column|expression [alias],...}

          FROM table

          [WHERE condition(s)];

二、多条件连接

    AND    同时满足

    OR      满足其中一个

 

三、比较符

    ＞      大于

    ＞＝ 大于等于

    ＜      小于

    ＜＝ 小于等于

    ＜＞ 不等于

    ！=     不等于

    IS NULL 是否为空值

    IS NOT NULL 不为空值

    NOT         条件为FALSE时返回TRUE

 

    BETWEEN x AND y   大于等于X，小于等于Y

    IN （集合）      在这个集合中，或者讲在这个列表中

    NOT IN（集合）  不在这个集合中，或者讲不在这个列表中

 

    LIKE   使用LIKE操作符来执行有效搜索字符串数值的通配符搜索

    ％      通配任意字符

    _       能配单个字符

   

四、日期和字符串的处理

    字符串和日期值使用单引号标记嵌入

    字符数值是大小写有关，而日期数值是格式化的。

    ORACLE默认显示的日期格式为：DD－MON－RR

    可以用alter session set nls_date_format='yyyy-mm-dd';修改日期格式

    永久性修改可以用：

    alter system set nls_date_format='yyyy-mm-dd' scope=spfile;

 

    日期相加减：

        日期＋（－）数字  返回一个往前或往后的天数的日期

               ＋往后  －往前

 

        日期－日期      得到两个日期之间相差的天数

      

五、order by 排序

    默认的排序方式：升序

    控制排序方式：   ASC    升序

                                   DESC 降序

   

六、演示

/*使用WHERE条件查询*/

SQL> select * from scott.emp where sal > 2000  ——薪水大于2000的员工所有信息。

  2  ;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

 

/*多条件连接*/

SQL> select * from scott.emp where deptno = 20 and sal > 2000;——部门2中薪水大于2000的员工信息。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

 

/*错误的写法*/

SQL> select * from scott.emp where 1000<=sal<=2000;

select * from scott.emp where 1000<=sal<=2000

                                                                            *

ERROR at line 1:

ORA-00933: SQL command not properly ended

 

/*正确的写法*/

SQL> select * from scott.emp where sal >= 1000 and sal <= 2000; ——薪水在1000到2000之间的员工信息。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

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

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

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

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

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

/*BETWEEN的用法*/

SQL> select * from scott.emp where sal between 1000 and 2000;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

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

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

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

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

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

/*IN的用法，以下两条语句等同*/

SQL> select * from scott.emp where sal in (3000,5000); ——薪水值为3000或者5000的员工信息。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

 

SQL> select * from scott.emp where sal = 3000 or sal = 5000;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

 

--not in的用法

SQL> select * from scott.emp where sal not in (3000,5000); ——薪水不值为3000或者5000的员工信息。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

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

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

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

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

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

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

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

/*LIKE、%、_ 运算符的用法*/

SQL> select * from scott.emp where ename like 'A%'; ——员工名字首字母为A的员工信息。别忘了加单引号。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

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

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

 

SQL> select * from scott.emp where ename like '_L%'; ——员工名字第二个字母为A的员工信息。

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

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

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

 

--日期格式：未修改前

SQL> select sysdate from dual;

 

SYSDATE

---------

28-MAR-10

 

/*日期格式的修改，仅对当前会话有效*/

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

 

Session altered.

 

--查看修改后的日期格式

SQL> select sysdate from dual;

 

SYSDATE

----------

2010-03-28

 

--永久性修改，将其修改写到参数文件中。

alter system set  nls_date_format =  'yyyy_mm_dd' scope = spfile;

 

--日期的加减

--     日期＋（－）数字  返回往前或往后的天数日期

--     ＋往前，－往后

--     日期－ 日期

 

SQL> select sysdate + 10 from dual;

 

SYSDATE+10

----------

2010-04-07

 

SQL> select sysdate - 10 from dual;

 

SYSDATE-10

----------

2010-03-18

 

--日期相减(为相差的天数)

SQL> select empno,ename,sysdate,sysdate - hiredate from scott.emp;

 

     EMPNO ENAME      SYSDATE    SYSDATE-HIREDATE

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

      7369 SMITH      2010-03-28       10693.4346

      7499 ALLEN      2010-03-28       10628.4346

      7521 WARD       2010-03-28       10626.4346

      7566 JONES      2010-03-28       10587.4346

      7654 MARTIN     2010-03-28       10408.4346

      7698 BLAKE      2010-03-28       10558.4346

      7782 CLARK      2010-03-28       10519.4346

      7788 SCOTT      2010-03-28       8379.43456

      7839 KING       2010-03-28       10358.4346

      7844 TURNER     2010-03-28       10428.4346

      7876 ADAMS      2010-03-28       8345.43456

 

--使用null值过滤  

SQL> select * from scott.emp where comm is null;

 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 1980-12-17        800                    20

      7566 JONES      MANAGER         7839 1981-04-02       2975                    20

      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20

      7839 KING       PRESIDENT            1981-11-17       5000                    10

      7876 ADAMS      CLERK           7788 1987-05-23       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03        950                    30

      7902 FORD       ANALYST         7566 1981-12-03       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23       1300                    10

 

/*ORDER BY排序（默认为升序）*/

SQL> select * from scott.emp where sal > 2000 order by sal;——查询工资高于2000的员工信息，默认升序排列。

 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO

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

      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10

      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30

      7566 JONES      MANAGER         7839 1981-04-02       2975                    20

      7902 FORD       ANALYST         7566 1981-12-03       3000                    20

      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20

      7839 KING       PRESIDENT            1981-11-17       5000                    10

 

--多字段排序  

SQL> select * from scott.emp where sal > 2000 order by sal desc ,ename asc;——先按照sal排序，sal相同的再按照ename 字母顺序排序。

 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO

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

      7839 KING       PRESIDENT            1981-11-17       5000                    10

      7902 FORD       ANALYST         7566 1981-12-03       3000                    20

      7788 SCOTT      ANALYST         7566 1987-04-19       3000                    20

      7566 JONES      MANAGER         7839 1981-04-02       2975                    20

      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10

 

--按第4个字段降序排列，order by 4 desc   

SQL> select empno,ename,job,sal from scott.emp where sal > 2000 order by 4 desc ; ——数字表示按照第几列排序。

 

     EMPNO ENAME      JOB              SAL

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

      7839 KING       PRESIDENT       5000

      7902 FORD       ANALYST         3000

      7788 SCOTT      ANALYST         3000

      7566 JONES      MANAGER         2975

      7698 BLAKE      MANAGER         2850

      7782 CLARK      MANAGER         2450