--=======================================
--SQL 基础 --> 过滤和排序
--=======================================
/*
一、使用 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
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 ;
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 ;
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 = 3000 or sal = 5000 ;
SQL > select * from scott . emp where sal in ( 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 );
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%' ;
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%' ;
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 ;
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 ;
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
-- 按第个字段降序排列， 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
七、更多
