02.SQL基础-->过滤和排序
一、使用WHERE字句实现对数据的过滤
|
1
2
3
|
用法：SELECT *|{[DISTINCT] column|expression [alias],...}FROM table<br>[WHERE condition(s)]; |
二、多条件连接
|
1
2
|
AND 同时满足OR 满足其中一个 |
三、比较符
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
＞ 大于＞＝ 大于等于＜ 小于＜＝ 小于等于＜＞ 不等于！= 不等于IS NULL 是否为空值IS NOT NULL 不为空值NOT 条件为FALSE时返回TRUEBETWEEN x AND y 大于等于X，小于等于YIN （集合） 在这个集合中，或者讲在这个列表中NOT IN（集合） 不在这个集合中，或者讲不在这个列表中LIKE 使用LIKE操作符来执行有效搜索字符串数值的通配符搜索％ 通配任意字符_ 能配单个字符 |
四、日期和字符串的处理
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
字符串和日期值使用单引号标记嵌入字符数值是大小写有关，而日期数值是格式化的。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 排序
|
1
2
3
|
默认的排序方式：升序控制排序方式： ASC 升序DESC 降序 |
六、演示*
使用WHERE条件查询*
select * from scott.emp where sal > 2000;
|
1
2
3
4
5
6
7
8
9
10
|
idle> select * from scott.emp where sal > 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 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 7902 FORD ANALYST 7566 1981-12-03 3000 20 |
/*多条件连接*/
select * from scott.emp where deptno = 20 and sal > 2000;
|
1
2
3
4
5
6
7
|
idle> select * from scott.emp where deptno = 20 and sal > 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 1981-04-02 2975 20 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7902 FORD ANALYST 7566 1981-12-03 3000 20 |
/*错误的写法*/
select * from scott.emp where 1000<=sal<=2000;
select * from scott.emp where 1000<=sal<=2000 ;
|
1
2
3
4
5
6
7
8
9
10
|
idle> select * from scott.emp where 1000<=sal<=2000 *ERROR at line 1:ORA-00933: SQL command not properly endedidle> idle> select * from scott.emp where 1000<=sal<=2000 *ERROR at line 1:ORA-00933: SQL command not properly ended |
/*正确的写法*/
select * from scott.emp where sal >= 1000 and sal <= 2000;
|
1
2
3
4
5
6
7
8
9
10
|
idle> select * from scott.emp where sal >= 1000 and sal <= 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 |
/*BETWEEN的用法*/
select * from scott.emp where sal between 1000 and 2000;
|
1
2
3
4
5
6
7
8
9
10
|
idle> select * from scott.emp where sal between 1000 and 2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 |
/*IN的用法，以下两条语句等同*/
select * from scott.emp where sal = 3000 or sal = 5000;
select * from scott.emp where sal in (3000,5000);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
idle> select * from scott.emp where sal = 3000 or sal = 5000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20idle> select * from scott.emp where sal in (3000,5000); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 |
select * from scott.emp where sal = 3000 or sal = 5000;
|
1
2
3
4
5
6
7
|
idle> select * from scott.emp where sal = 3000 or sal = 5000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7902 FORD ANALYST 7566 1981-12-03 3000 20 |
--not in的用法
select * from scott.emp where sal not in (3000,5000);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
idle> select * from scott.emp where sal not in (3000,5000); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7934 MILLER CLERK 7782 1982-01-23 1300 10 |
/*LIKE、%、_ 运算符的用法*/
select * from scott.emp where ename like 'A%';
|
1
2
3
4
5
6
|
idle> select * from scott.emp where ename like 'A%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7876 ADAMS CLERK 7788 1987-05-23 1100 20 |
select * from scott.emp where ename like '_L%';
|
1
2
3
4
5
6
7
|
idle> select * from scott.emp where ename like '_L%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 |
--日期格式：未修改前
|
1
2
3
4
5
6
7
|
SQL> select sysdate from dual; ##自己的日期格式已经更改，故此显示结果采用乐沙弥大师的实验结果SYSDATE---------28-MAR-10 |
/*日期格式的修改，仅对当前会话有效*/
|
1
|
SQL> alter session set nls_date_format = 'yyyy-mm-dd'; |
--查看修改后的日期格式
SQL> select sysdate from dual;
|
1
2
3
4
5
|
idle> select sysdate from dual;SYSDATE----------2017-12-22 |
--永久性修改，将其修改写到参数文件中。
|
1
|
alter system set nls_date_format = 'yyyy_mm_dd' scope = spfile; |
|
1
2
3
4
|
--日期的加减--日期＋（－）数字 返回往前或往后的天数日期--＋往前，－往后-- 日期－ 日期 |
select sysdate + 10 from dual;
|
1
2
3
4
5
|
idle> select sysdate + 10 from dual;SYSDATE+10----------2018-01-01 |
select sysdate - 10 from dual;
|
1
2
3
4
5
|
idle> select sysdate - 10 from dual;SYSDATE-10----------2017-12-12 |
--日期相减(为相差的天数)
select empno,ename,sysdate,sysdate - hiredate from scott.emp;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
idle> select empno,ename,sysdate,sysdate - hiredate from scott.emp; EMPNO ENAME SYSDATE SYSDATE-HIREDATE---------- ---------- ---------- ---------------- 7369 SMITH 2017-12-22 13519.383 7499 ALLEN 2017-12-22 13454.383 7521 WARD 2017-12-22 13452.383 7566 JONES 2017-12-22 13413.383 7654 MARTIN 2017-12-22 13234.383 7698 BLAKE 2017-12-22 13384.383 7782 CLARK 2017-12-22 13345.383 7788 SCOTT 2017-12-22 11205.383 7839 KING 2017-12-22 13184.383 7844 TURNER 2017-12-22 13254.383 7876 ADAMS 2017-12-22 11171.383 7900 JAMES 2017-12-22 13168.383 7902 FORD 2017-12-22 13168.383 7934 MILLER 2017-12-22 13117.383 |
--使用null值过滤
select * from scott.emp where comm is null;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
idle> 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排序（默认为升序）*/
select * from scott.emp where sal > 2000 order by sal;
|
1
2
3
4
5
6
7
8
9
10
|
idle> 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 |
--多字段排序
select * from scott.emp where sal > 2000 order by sal desc ,ename asc;
|
1
2
3
4
5
6
7
8
9
10
|
idle> 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 |
--按第4个字段降序排列，order by 4 desc
select empno,ename,job,sal from scott.emp where sal > 2000 order by 4 desc ;
|
1
2
3
4
5
6
7
8
9
10
|
idle> 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 |
