Oracle数据库之五 限定查询和排序显示

五、限定查询和排序显示

5.1、限定查询

5.1.1 认识限定查询

• 例如：如果一张表中有 100w 条数据，一旦执行了 “ select * from 表 ” 语句之后，则将在屏幕上显示表中全部数据行的记录，这样既不方便浏览，也可能造成死机的问题，所以此时就必须对查询的结果进行筛选，只选出对自己有用的数据即可，那么就可以通过 where 指定查询的筛选条件。
• 这么多条数据一起显示肯定是无法浏览的。另外，如果数据量显示的过多，那么有可能出现死机的问题。所以下面首先来观察数据量大对于查询显示会存在什么问题。
• 在进行 oracle 数据库安装的时候已经安装了样本方案数据库，所以现在必须将容器由 cdb 切换到 pdb 之中。

范例：以 nolog 的方式打开 sqlplus (在命令行终端)

sqlplus /nolog

范例：使用 sys 管理员登录

conn sys/chagne_on_install as sysdba;

范例：切换到 pdb 之中

alter session set container=pdbmldn;

范例：打开 pdb

alter database pdbmldn open;

范例：查看 sh 用户的数据表内容

select count(*) from sh.sales;

• 现在这张表中存在有 9w 多条记录，如果直接发出如下指令：

select * from sh.sales;

• 现在显示结果一直不停变换，无法查看，按 ctrl+c 停止。所以现在数据量一大，那么是不可能这样直接查看全部数据的，所以全部数据行的显示根本就不可能使用。很多时候往往需要针对所需要的数据进行筛选，而筛选就是限定查询的功能。
• 现在连接到 c##scott 用户:

conn c##scott/tiger;

5.1.2 限定查询语法：

select [distinct] * | 列名称 [as] [列别名],列名称 [as] [列别名],...
from 表名称[表别名]
[where 条件( s )];

• 在这个语法之中，就是比之前的语法多了一个 where 子句，在 where 子句之中可以设置一系列的过滤条件。而这些条件可以设置多个，那么这多个条件之间就可以利用逻辑运算进行连接。

• 逻辑运算符共有以下三种：

  • 与（and）：连接多个条件，多个条件同时满足时才返回 true，有一个条件不满足结果就是 false ；
  • 或（or）：连接多个条件，多个条件之中只要有一个返回 true ，结果就是 true ，如果多个条件返回的都是 false ，结果才是 false ；
  • 非（not）：求反操作，可以将 true 变 false ，false 变 true 。

  逻辑真值表：

no.	条件 x	条件 y	x and y	x or y	not x
1	true	true	true	true	false
2	true	null	null	true	false
3	true	false	false	true	false
4	null	true	null	true	null
5	null	null	null	null	null
6	null	false	false	null	null
7	false	true	false	true	true
8	false	null	false	null	true
9	false	false	false	true	true

范例：统计出基本工资高出 1500 的全部雇员信息

• 现在的查询已经出现了一个条件要求，所以在这种情况下就必须使用 where 子句进行条件的设置。

select * from emp where sal>1500;

• 现在可以发现并不是所有的数据都显示了，只是部分的数据显示，而且这部分都是满足条件的数据。

• 现在对于 sql 语法而言，就具备了三个子句：
  • 第一步：执行 from 子句，来控制数据的来源
  • 第二步：执行 where 子句，使用限定符进行数据行的过滤
  • 第三步：执行 select 子句，确定要显示的数据列

​

5.1.3 对数据进行限定查询

• 在之前所使用的 “ > ” 是一个关系运算符，在标准 sql 之中定义了许多的运算符。
• 常用限定运算符：

no	运算符	符号	描述
1	关系运算符	>、<、>=、<=、=、!=、<>	进行大小或相等的比较，其中不等于有两种：!= 和 <>
2	判断 null	is null 、is not null	判断某一列的内容是否是 null
3	逻辑运算符	and 、or、not	and 表示多个条件必须同时满足，or 表示只需要有一个条件满足即可，not 表示条件取反，即：真变假，假变真
4	范围查询	between 最小值 and 最大值	在一个指定范围中进行查找，查找结果为：“ 最小值 <= 内容 <= 最大值 ”
5	范围查询	in	通过 in 可以指定一个查询的范围
6	模糊查询	like	可以对指定的字段进行模糊查询

5.1.3.1 关系运算符

• 关系运算就是确定大小、相等关系的比较。

范例：要求查询出所有基本工资小于等于2000的全部雇员信息

select *
form emp
where sal<=2000;

范例：根据之前的查询结果发现 smith 的工资最低，现在希望可以取得 smith 的详细资料。

select *
form emp
where ename='smith';

范例：查询出所有办事员（clerk）的雇员信息

select *
form emp
where job='clerk';

• 但是在使用关系运算符判断字符数据的时候请一定要主要大小写的编写问题。因为 oracle 是区分大小写的。

范例：错误的代码

select *
form emp
where job='clerk';  // 不会有结果返回

范例：取得了所有办事员的资料之后，为了和其他职位的雇员对比，现在决定再查询出所有不是办事员的雇员信息。

• 既然现在职位不是办事员，那么肯定使用不等于符号（<>, !=）

  • 实现一：

  select * 
  form emp
  where job<>'clerk';   

  • 实现二：

  select * 
  form emp
  where job!='clerk';

范例：查询出工资范围在 1500 ~ 3000 （都包含）的全部雇员信息

• 现在这个判断是两个条件，而且这两个条件肯定需要同时满足，那么就使用 and 进行条件的连接

select *
form emp
where sal>=1500 and sal<=3000;

范例：查询职位是销售，并且基本工资高于 1200 的所有雇员信息

select * 
form emp
where job='salesman' and sal>1200;

范例：查询出 10 部门中的经理或者是 20 部门的业务员的信息

select * 
form emp
where (deptno=10 and job='manager') or (deptno=20 and job='clerk');

范例：查询不是办事员的且基本工资大于 2000 的全部雇员信息

• 实现一：基本实现

select * 
form emp
where job!='clerk' and sal>2000;

select * 
form emp
where job<>'clerk' and sal>2000;

• 实现二：使用 not 对条件求反

select * 
form emp
where not(job='clerk' or sal<=2000);

5.1.3.2 范围查询

范例：使用 between...and... 操作符查询工资范围在 1500（含）~ 3000（含）的全部雇员信息

select * 
form emp
where sal between 1500 and 3000;

范例：查询出在 1981 年雇佣的全部雇员信息

select * 
form emp
where hiredate between '01-1月-81' and '31-12月-1981';

​ 实际上这里就实现了日期和字符串数据之间的转换操作的功能。

5.1.3.3 null 判断：

• 判断内容是否为 null ： is null , is not null

• 语法：

  • 判断为 null：字段|值 is null;

  • 判断不为 null：字段|值 is not null; ( not 字段|值 is null; )

    null 是一个未知的数据，所以对于 null 的处理，如果直接利用关系运算判断，是不会有结果的

范例：利用 = 进行 null 比较

select * 
form emp
where comm=null and empno=7369;

• 结果显示没有任何数据返回，因为 null 不能使用 = 判断。

范例：查询出所有领取佣金的雇员的完整信息

• 佣金的字段是 comm，领取佣金的概念就属于佣金不为null。

  • 实现一：直接使用is not null 完成

  select * 
  form emp
  where comm is not null;

  • 实现二：使用is null 并使用not 求反完成

  select * 
  form emp
  where not comm is null;

范例：查询出所有不领取佣金的雇员的完整信息

select * 
form emp
where comm is null;

范例：列出所有的不领取奖金的雇员，而且同时要求这些雇员的基本工资大于2000的全部雇员信息

select * 
form emp
where comm is null and sal > 2000;

范例：找出不领取佣金或领取的佣金低于100的员工

select * 
form emp
where comm is null or comm < 100;

范例：找出收取佣金的员工的不同工作

• 既然现在要找的是职位，那么很有可能出现重复，重复发数据就必须使用 distinct 消除。

select distinct job 
form emp
where comm is not null;

5.1.3.4 列表范围查找：in ， not in

• 所谓的列表范围指的是给了用户固定的几个参考值，只要符合这个值就满足条件。

• 语法：
  • 在指定数据范围内：字段|值 in (值,值,...);
    
  • 不在指定数据范围内：字段|值 not in (值,值,...);

范例：查询出雇员编号是7369,7788,7566的雇员信息

• 那么面对这样的操作，如果此时不使用in判断符就可以利用多个条件并且使用or 进行连接。

select * 
form emp
where empno = 7369 or empno = 7788 or empno = 7566;

• 下面使用优秀代码 in 实现

select * 
form emp
where empno in (7369,7788,7566);

范例：现在查询除了7369,7788,7566之外的雇员信息

select * 
form emp
where empno not in (7369,7788,7566);

• 但是在使用not in 操作的时候有一点需要注意，关于null的问题：

  • 如果使用的是in 操作符判断的范围数据之中包含了null，那么不会影响最终的查询结果。

  select * 
  form emp
  where empno in (7369,7788,null);

  • 但是如果使用的是not in，里面有null，直接的后果就是没有任何数据显示。

  select * 
  form emp
  where empno not in (7369,7788,null);

• 使用not in 或 in 其目的只是显示部分内容，如果说现在有一列数据不可能为null，并且 not in 里面判断 null 的条件满足了，那么就表示的是查询全部数据。这样就有可能导致取得的数据量过多导致程序死机。

• 所以，not in 中强加了一个限制，不能有 null，有 null 就没有数据，这是一个死限制。

5.1.3.4 模糊查询: like ，not like

• 语法：
  • 满足模糊查询：字段|值 like 匹配标记
  • 不满足模糊查询：字段|值 not like 匹配标记
• 如果现在想对查询某一列进行模糊查询，可以使用 like 子句完成，通过 like 可以进行关键字的模糊查询，在 like 子句中有两个通配符：
  • 百分号（%）：可以匹配任意类型和长度的字符，如果是中文则使用两个百分号（%%）
  • 下划线（_）：匹配单个任意字符，它常用来限制表达式的字符长度

范例：查询出雇员姓名是以 s 开头的全部雇员信息

• 证明 s 之后的内容可以是任意的数据，可能是 0位、1位或者多位。

select * 
form emp
where ename like 's%';

范例：查询出雇员姓名的第二个字母是 m 的全部雇员信息

• 现在只是第二个字母，那么证明第一个字母可以任意了，所以使用 “_” 。

select * 
form emp
where ename like '_m%';

范例：查询出雇员姓名中任意位置包含字母 f 的雇员信息

• 现在可能是开头，也可能是结尾，或者是在中间，所以就必须考虑到前后都有的问题，那么使用% 。

select * 
form emp
where ename like '%f%';

范例：查询雇员姓名长度为6或者是超过6个的雇员信息。

• 姓名的长度为6，那么肯定可以写6个 “_”，如果可以超过6，就加一个% 。

select * 
form emp
where ename like '______%';

范例：查询出雇员基本工资中包含1 或者是在81年雇佣的全部雇员

• 在之前的所有查询之中都是针对于字符数据进行的操作，而对于 like 而言，也可以在数字或者；日期类型上使用。

select * 
form emp
where sal like '%1%' or hiredate like (%81%);

• 但是有一点需要提醒的是，如果在设置模糊查询的时候不设置关键字，就表示查询全部，如：

select * 
form emp
where sal like '%%' 
    or hiredate like (%%) 
    or ename like '%%' 
    or job like '%%';

​ 结果显示全部数据。

范例：找出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料，并且要求这些雇员姓名之中含有字母 s 或字母 k。

• 现在存在于以下几个条件：
  • 条件一：10部门的经理
  • 条件二：20部门的办事员
  • 条件三：不是经理和办事员，但是薪金大于或等于2000
  • 条件四：以上所有条件满足之后再过滤，包含字母 s 或字母 k

select * 
form emp
where ((deptno = 10 and job = 'manager')
        or (deptno = 20 and job = 'clerk')
        or (job not in (manager,clerk) and sal >= 2000))
        and (ename like '%s% or ename like '%k%');

• 小结：
  • 限定排序主要使用 where 子句，用于对选取的数据进行控制。
  • 限定查询主要的运算符：关系运算、between...and、in、is null、like

5.2、排序显示

5.2.1 认识排序

• 传统数据查询的时候只会按照设置的主键排序。如果现在希望对指定的列进行排序的操作，那么就必须通过 order by 子句完成控制。

5.2.2 排序语法

select [distinct] * |列名称 [as] 列别名,列名称 [as] 列别名
form 表名称 表别名
[where 条件(s)]
[order by 排序的字段|列索引序号 asc | desc,
        排序的字段2 asc | desc ...]...;

• 在 order by 子句之中可以指定要进行排序的字段，而后字段有两种排序模式：
  • 升序：asc ，默认
  • 降序：desc ，需要编写的。
• 在所有的子句之中，一定要记住，order by 子句是放在查询语句的最后一行，是最后一个执行的，它的执行顺序：from、where、select、order by ，既然 order by 在 select 之后执行，那么就表示 order by 子句可以使用 select 子句之中设置的别名。

范例：查询雇员的完整信息并且按照基本工资由高到低进行排序

select * 
form emp
order by sal desc;

范例：修改之前的查询，要求按照基本工资由低到高进行排序

• 实现一：

select * 
form emp
order by sal;

• 实现二：

select * 
form emp
order by sal asc;

范例：查询出所有办事员（clerk）的详细资料，并且按基本工资由低到高排序

• 现在不再是针对所有的数据进行排序，需要对数据执行筛选，那么就利用 where 子句完成。

select * 
form emp
where job = 'clerk'
order by sal;

范例：查询所有雇员信息，要求按照基本工资由高到低排序，如果工资相等则按照雇佣日期进行排序，按照由早到晚的顺序

• 现在的排序需要设置两个排序的字段：sal（desc），hiredate（asc）

select * 
form emp
order by sal desc,hiredate asc;

• 显示结果中有点小问题，本程序的语法没有问题，有问题的是在于数据上。因为现在的数据都是后期处理的结果，如果要想正常的观察数据，那么可以将数据库切换到 pdb 中，找到原始的 scott 数据。
• 方法：在 dba_connection 中先执行下面两行代码后继续查询

alter session set container = pdbmldn;
alter database pdbmldn open;

select * 
from scott.emp
order by sal desc ,hiredate asc;

• 但是对于排序，除了使用字段之外，也可以设置一个序号，但是此操作不建议使用。

select empno, ename, sal, job  
form emp
order by sal desc;

select empno, ename, sal, job  
form emp
order by 3 desc;

​ 上面两个语句执行效果一样，但个人认为，在编写排序的时候还是建议写上字段，不要用序号，不方便。

• 小结

  • sql 语法：

  selectl [distinct] * |列 [as][别名], 列 [as][别名],...
  from 表名称 [别名]
  [where 限定条件(s)]
  [order by 排序字段 [asc|desc][,排序字段[asc|desc]...]];

  • 使用 order by 子句可以对查询结果进行排序，order by 子句一定要写在所有查询语句的最后。

说明：本学习资料是根据李兴华的oracle开发实战经典整理