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的員工資訊。

/*錯誤的寫法*/

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之間的員工資訊。

      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;

/*IN的用法，以下兩條語句等同*/

SQL> select * from scott.emp where sal in (3000,5000); ——薪水值為3000或者5000的員工資訊。

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

--not in的用法

SQL> select * from scott.emp where sal not in (3000,5000);

——薪水不值為3000或者5000的員工資訊。

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

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

SQL> select * from scott.emp where ename like 'A%';

——員工名字首字母為A的員工資訊。别忘了加單引号。

SQL> select * from scott.emp where ename like '_L%'; ——員工名字第二個字母為A的員工資訊。

--日期格式：未修改前

SQL> select sysdate from dual;

SYSDATE

---------

28-MAR-10

/*日期格式的修改，僅對目前會話有效*/

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

Session altered.

--檢視修改後的日期格式

----------

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的員工資訊，預設升序排列。

--多字段排序  

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

字母順序排序。

--按第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