Oracle怎么过滤查询,oracle学习笔记二——查询之过滤和排序

二、过滤和排序

1.过滤：where

(1)等值查询：

--查询10号部门的员工

SQL>select * from emp where deptno=10;

--字符串大小写敏感(字符串必须用单引号括起来，和别名使用的双引号不同)

SQL>select * from emp where ename='KING';

和下面的语句不一样

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

就本例(第一条有值，第二条没有值)

--日期格式敏感

SQL> --查询入职日期为17-11月-81的员工信息

SQL> select * from emp where hiredate='17-11月-81';

注意：

1)hiredate是日期格式(Date),这里隐藏了一个隐式转换[to_date('17-11月-81','DD-MON-RR')]，我们在查询时应尽可能使用显示转换，显式指定to_date('17-11月-81','DD-MON-RR')

2)oracle中表示日期，只有Date(没有其他数据库，如mysql的date和datetime的区别)，可以通过指定显示格式，只显示日期还是连时间一起显示

3)那么这里的参数format('DD-MON-RR')怎么来的？

补充：查询oracle数据库的默认参数

SQL> --查询参数

SQL>select * from v$nls_parameters;

结果示例：

NLS_LANGUAGE                                                    SIMPLIFIED CHINESE

NLS_DATE_FORMAT                                                  DD-MON-RR

注：以’v$‘打头的表是数据库管理员提供的表和上面的tab表类似

SQL> --修改日期格式(alter session)

SQL>alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

(2)不等值查询

--between ..and : 在。。。之间

--查询月薪在1000~2000的员工

SQL> select * from emp where sal between 1000 and 2000;

注：between and: 小值在前 大值在后

--in 在集合中

--查询部门号是10和20的员工

SQL> select * from emp where deptno in (10,20);

同理，not in

SQL> select * from emp where deptno not in (10,20);

补：关于Oracle书写SQL中的null值:

3. 如果集合中含义null，不能使用not in

(3)模糊查询

--like:%表示任意多个字符，_表示一个字符

--查询名字以S打头的员工信息

SQL> select * from emp where ename like 'S%';

--查询名字是4个字的员工

SQL> select * from emp where ename like '____';

--查询名字中含义下划线的员工

由于“_”在oracle中有特殊的含义，因此需要对“_”进行转义，注意escape关键字后面指定的字符可以是任意的，只是习惯上使用“\”(写代码的应该都知道)

错的：SQL> select *  from emp  where ename like '%_%';

对的：SQL> select *  from emp where ename like '%\_%'escape'\'

补：关于事务

oracle默认自动开启事务

不像mysql等需要手动开启，通过start transaction

2.排序：order by

--查询员工信息，按照月薪排序(升序)

SQL> select * from emp order by sal;

--查询员工信息，按照月薪排序(倒序)

SQL> select * from emp order by sal desc;

注1：order by后面可以跟哪些内容：

1)列名

2)表达式

--按照年薪排序

SQL> select ename,sal,sal*12 from emp order by sal*12;

3)别名

SQL> select ename,sal,sal*12 年薪 from emp order by 年薪

4)序号

SQL> select ename,sal,sal*12 年薪 from emp order by 3

--->如果是order by 4则会报错，因为只查了3列

注2：order by后跟多列的情况：

SQL> select * from emp order by deptno,sal;

则先按deptno升序排列，然后内部再根据sal升序排列，那如果像下面这样呢？

SQL> select * from emp order by deptno,sal desc;

则先按deptno升序排列，然后内部再根据sal降序排列，也就是说desc只作用于离它最近的列，并不作用于她前面的所有列(这点和distinct作用于其后所有列不同)，如果deptno也要降序，应该像下面这样

SQL> select * from emp order by deptno desc,sal desc;

注3：order by后面的列如果有空值会怎么样？

--查询员工信息：按照奖金排序

SQL> select * from emp order by comm;

含有空值的列——升序

-->升序没问题

SQL> select * from emp order by comm desc;

-->降序,发现前面的记录该列都是空，这就要命了，如果在代码里写循环，判断该列为空就退出，后面的数据就取不到了，这是个bug,得治，这就用到了nulls last

含有空值的列——降序

SQL> select * from emp order by comm desc nulls last;

-->好了，comm降序，并且空值都移到最后了

含有空值的列——降序，使用nulls last