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

七、更多

SQL 基础-->SELECT 查询

Sqlplus 常用命令

Oralce 相关