SQL基础-->过滤和排序
--=======================================
--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
七、更多
