Oracle 学习之 SQL(二) Restricting and Sorting Data

限制SELECT语句的返回行

语法：

使用WHERE关键字来限制数据行的返回，WHERE子句跟在FROM子句的后面。

SQL> select * from emp where deptno=10;     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7782 CLARK      MANAGER        7839 1981/06/09 00:00:00       2450                   10      7839 KING       PRESIDENT         1981/11/17 00:00:00           5000                   10      7934 MILLER     CLERK       7782 1982/01/23 00:00:00       1300                   10

当WHERE后面的表达式为true时，则返回行，否则跳过改行。

SQL> select * from emp where ename='KING';     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7839 KING       PRESIDENT         1981/11/17 00:00:00           5000                   10

字符串是大小写敏感的

SQL> select * from emp where ename='king';no rows selected

条件表达式支持的比较操作符有如下

SQL> select * from emp where sal<2000;     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK          7902 1980/12/17 00:00:00        800              20      7499 ALLEN      SALESMAN       7698 1981/02/20 00:00:00       1600       300            30      7521 WARD       SALESMAN          7698 1981/02/22 00:00:00       1250       500            30      7654 MARTIN     SALESMAN            7698 1981/09/28 00:00:00       1250      1400         30      7844 TURNER     SALESMAN            7698 1981/09/08 00:00:00       1500         0          30      7876 ADAMS      CLERK          7788 1987/05/23 00:00:00       1100                   20      7900 JAMES      CLERK          7698 1981/12/03 00:00:00        950              30      7934 MILLER     CLERK       7782 1982/01/23 00:00:00       1300                   10

BETWEEN AND 只要值在这个范围内，就返回值

SQL> select * from emp where sal between 1500 and 2000;     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7499 ALLEN      SALESMAN       7698 1981/02/20 00:00:00       1600       300            30      7844 TURNER     SALESMAN            7698 1981/09/08 00:00:00       1500         0          30

IN 只要列的值出现在列表中即返回行

SQL> SELECT * FROM EMP WHERE ENAME IN ('SMITH','KING');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20

7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10

LIKE 模糊查询

查询所有姓名以S开头的人

SQL> select * from emp where ename like 'S%';     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK          7902 1980/12/17 00:00:00        800              20      7788 SCOTT      ANALYST        7566 1987/04/19 00:00:00       3000                   20

% 通配符，表示0或多个任意字符

查询所有姓名包含S的人

SQL> select * from emp where ename like '%S%';     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK          7902 1980/12/17 00:00:00        800              20      7566 JONES      MANAGER        7839 1981/04/02 00:00:00       2975                   20      7788 SCOTT      ANALYST        7566 1987/04/19 00:00:00       3000                   20      7876 ADAMS      CLERK          7788 1987/05/23 00:00:00       1100                   20      7900 JAMES      CLERK          7698 1981/12/03 00:00:00        950              30

_ 通配符表示任意一个字符

查询名字的第二个字符为O的人

SQL> select * from emp where ename like '_O%';     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7566 JONES      MANAGER        7839 1981/04/02 00:00:00       2975                   20      7902 FORD       ANALYST           7566 1981/12/03 00:00:00       3000                   20

如果列的值本身就包含%或_字符呢？ 用escape关键字转义。

查询名字中含有_的人

SQL> select * from emp where ename like '%\_%' escape '\';no rows selected

过滤NULL值

过滤NULL值不能使用= <>来查询，null有一个专有的判断条件 IS NULL

SQL> SELECT * FROM EMP WHERE COMM IS NULL;     EMPNO ENAME      JOB          MGR HIREDATE                   SAL COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK          7902 1980/12/17 00:00:00        800              20      7566 JONES      MANAGER        7839 1981/04/02 00:00:00       2975                   20      7698 BLAKE      MANAGER        7839 1981/05/01 00:00:00       2850                   30      7782 CLARK      MANAGER        7839 1981/06/09 00:00:00       2450                   10      7788 SCOTT      ANALYST        7566 1987/04/19 00:00:00       3000                   20      7839 KING       PRESIDENT         1981/11/17 00:00:00           5000                   10      7876 ADAMS      CLERK          7788 1987/05/23 00:00:00       1100                   20      7900 JAMES      CLERK          7698 1981/12/03 00:00:00        950              30      7902 FORD       ANALYST           7566 1981/12/03 00:00:00       3000                   20      7934 MILLER     CLERK       7782 1982/01/23 00:00:00       1300                   10