Oracle--条件查询、模糊查询和排序详解

心已赠人 2021-08-13 21:22 794阅读 0赞

#### 使用where语句对结果进行过滤 ####

SELECT *|{
       [DISTINCT] column|expression [alias],...}
    FROM table
    WHERE condition(s)};

#### 比较运算符 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">操作符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">=</td> 
   <td align="center">等于（<span><span><span> 不 是 = = \color{red}{不是==} </span><span><span><span style="height: 0.36687em; vertical-align: 0em;"></span><span style="color: red;"><span style="color: red;">不</span><span style="color: red;">是</span><span style="color: red; margin-right: 0.277778em;"></span><span style="color: red;">=</span><span style="color: red;">=</span></span></span></span></span></span>）</td> 
  </tr> 
  <tr> 
   <td align="center">&gt;</td> 
   <td align="center">大于</td> 
  </tr> 
  <tr> 
   <td align="center">&gt;=</td> 
   <td align="center">大于、等于</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;</td> 
   <td align="center">小于</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;=</td> 
   <td align="center">小于、等于</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;&gt;</td> 
   <td align="center">不等于（<span><span><span> 也 可 以 是 ! = \color{red}{也可以是 !=} </span><span><span><span style="height: 0.69444em; vertical-align: 0em;"></span><span style="color: red;"><span style="color: red;">也</span><span style="color: red;">可</span><span style="color: red;">以</span><span style="color: red;">是</span><span style="color: red;">!</span><span style="color: red; margin-right: 0.277778em;"></span><span style="color: red;">=</span></span></span></span></span></span>）</td> 
  </tr> 
 </tbody> 
</table>

#### 其它比较运算符 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">操作符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">BETWEEN…AND…</td> 
   <td align="center">在两个值之间（<span><span><span> 包 含 边 界 \color{red}{包含边界} </span><span><span><span style="height: 0em; vertical-align: 0em;"></span><span style="color: red;"><span style="color: red;">包</span><span style="color: red;">含</span><span style="color: red;">边</span><span style="color: red;">界</span></span></span></span></span></span>）</td> 
  </tr> 
  <tr> 
   <td align="center">IN(set)</td> 
   <td align="center">等于值列表中的一个</td> 
  </tr> 
  <tr> 
   <td align="center">LIKE</td> 
   <td align="center">模糊查询</td> 
  </tr> 
  <tr> 
   <td align="center">IS NULL</td> 
   <td align="center">空值</td> 
  </tr> 
 </tbody> 
</table>

#### 逻辑运算符 ####

### Where语句示例 ###

##### 1. 字符和日期 #####

*  字符和日期要包含在单引号中。
 *   字 符 大 小 写 敏 感 ， 日 期 格 式 敏 感 。 \\color\{red\}\{字符大小写敏感，日期格式敏感。\} 字符大小写敏感，日期格式敏感。
 *  默认的日期格式是 D D − M O N − R R 。 \\color\{red\}\{DD-MON-RR。\} DD−MON−RR。

示例：查询名叫KING的员工

select * from emp where ename='KING';

![在这里插入图片描述][20190908200118544.png]

示例：查询入职日期是17-11月-81的员工

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

![在这里插入图片描述][20190908200317333.png]

##### 2. 非空和空的限制 #####

示例：查询每月能得到奖金的雇员  
分析：只要字段中存在内容表示不为空，如果不存在内容就是null。  
语法：列名 IS NOT NULL  
为空：列名 IS NULL

select * from emp where sal is not null;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]  
示例：查询工资大于1500或者有奖金的雇员。  
分析：多个查询条件或满足，条件之间使用“OR”

select * from emp where sal > 1500 or comm is not null;

![在这里插入图片描述][20190908203442613.png]  
示例：查询工资不大于1500和没有奖金的人  
语法：NOT（查询条件）

select * from emp where not(sal > 1500) and comm is null;

![在这里插入图片描述][20190908203714527.png]

##### 范围限制 #####

示例：基本工资大于1500但是小于3000的全部雇员  
分析：sal>1500, sal<3000

select * from emp where sal>1500 and sal<3000;

![在这里插入图片描述][201909082043443.png]  
 b e t w e e n \\color\{red\}\{between\} between  a n d \\color\{red\}\{and\} and 等价于 sal>=1500 and sal <= 3000

select * from emp where sal between 1500 and 3000;

![在这里插入图片描述][2019090820460615.png]

示例：查询1981-1-1到1981-12-31号入职的雇员  
分析：between and 不仅可以使用在数值之间，也可以用在日期的区间

select * from emp where hiredate between '1-1月-1981' and '31-12月-1981';

![在这里插入图片描述][20190908204814422.png]

示例：查询雇员编号是7369，7499，7521的雇员编号的具体信息  
分析：此时指定了查询范围，那么可以使用 I N \\color\{red\}\{IN\} IN关键字  
语法：

*  列名 IN （值1，值2，…）
 *  列名 NOT IN （值1，值2，…）

其中的值不仅可以是数值类型，也可以是字符串

select * from emp where empno in (7369，7499，7521);

![在这里插入图片描述][20190908205025772.png]

示例：查询雇员姓名是‘SMITH’，‘ALLEN’，‘WARD’的雇员具体信息

select * from emp where ename in ('SMIT','ALLEN','WARD');

![在这里插入图片描述][20190908205426225.png]

注 意 : 如 果 集 合 中 含 有 n u l l ， 不 能 使 用 n o t \\color\{red\}\{注意:如果集合中含有null，不能使用not\} 注意:如果集合中含有null，不能使用not  i n ; 但 可 以 使 用 i n \\color\{red\}\{ in;但可以使用in\} in;但可以使用in  
示例：查询不是10和20号部门的员工

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

![在这里插入图片描述][20190908205815368.png]  
添加一个null到集合中

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

![在这里插入图片描述][20190908205858622.png]

##### 模糊查询 #####

在常用的网站中经常会有模糊查询，即：输入一个关键字，把符合的内容全部的查询出来，在sql中使用 L I K E \\color\{red\}\{LIKE\} LIKE语句完成。  
在LIKE中主要使用一下两种通配符

1.  “%” : 可以匹配任意长度的内容
2.  “\_” : 可以匹配一个长度的内容

示例：查询出所有雇员姓名中第二个字符包含“M”的雇员

select * from emp where ename like '_M%';

![在这里插入图片描述][20190908215802345.png]

在LIKE中如果没有关键字表示查询全部

示例：查询名字中带有“M”的雇员

select * from emp where ename like '%M%';

![在这里插入图片描述][20190908220012308.png]

在oracle中不等号的用法可以有两种形式“<>”和"!="  
示例：查询雇员编号不是7369的雇员信息

select * from emp where empno <> 7369;
    select * from emp where empno != 7369;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

查 询 带 有 特 殊 字 符 的 关 键 字 \\color\{red\}\{查询带有特殊字符的关键字\} 查询带有特殊字符的关键字  
插入一条数据：

insert into emp (empno,ename) values(14,'TOM_CAT');

示例：查询名字含有下划线的员工  
分析：使用escape关键字声明转义字符，一般使用“\\”

select * from emp where ename like '%\_%' escape '\';

![在这里插入图片描述][20190908221123239.png]

**SQL优化**

select * from emp where condition1 and condition2

在 O r a c l e 中 ， w h e r e 子 句 是 从 右 向 左 进 行 解 析 的 。 \\color\{red\}\{在Oracle中，where子句是从右向左进行解析的。\} 在Oracle中，where子句是从右向左进行解析的。  
所以，在and条件中，我们应尽量把可能为false的条件放在右边，这样就可以短路计算提升效率。

#### 使用order by对结果排序 ####

**排序的语法：**  
在sql中可以使用ORDER BY 对查询结果进行排序  
语法：

SELECT * | 列名 FROM 表名 {
       WHERE 查询条件} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC

示例：查询雇员的工资从低到高  
分析：ORDER BY 列名 默认的排序规则是升序排列，可以不指定ASC，如果按着降序排列必须指定DESC

select * from emp order by sal;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]  
如果存在多个排序字段可以用逗号分隔。  
asc和desc作用于距离最近的列。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]  
order by后面可以 + 列、表达式、别名、序号

select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]

注 意 ： O R D E R \\color\{red\}\{注意：ORDER \} 注意：ORDER  B Y 语 句 要 放 在 最 后 执 行 \\color\{red\}\{BY 语句要放在最后执行 \} BY语句要放在最后执行

示例：查询雇员信息，按奖金升序排序：

select * from emp order by comm;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5][]示例：查询雇员信息，按奖金降序排序：

select * from emp order by comm desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]  
显然排序结果出现了错误， 因 为 在 O r a c l e 中 n u l l 最 大 \\color\{red\}\{因为在Oracle中null最大\} 因为在Oracle中null最大  
解决方法：  
可以使用 null first， nulls last 来指定null的值显示的位置。

select * from emp order by comm desc nulls last;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]

[20190908200118544.png]: /images/20210813/1a4f9328f85c443d8b28e4bf2f5b7b98.png
[20190908200317333.png]: /images/20210813/2389060407344f53854dd129eb819791.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/abbb234556354e499773b6886094d388.png
[20190908203442613.png]: /images/20210813/9dac21ed7ce94e5d858d48d4cef215ef.png
[20190908203714527.png]: /images/20210813/0ff9e77d7c3f453989c27cb4d1b4cceb.png
[201909082043443.png]: /images/20210813/affada6ff682459cbd49d359b9d66489.png
[2019090820460615.png]: /images/20210813/6509bd3a7b044edd8223286862a38ce9.png
[20190908204814422.png]: /images/20210813/37e252e8add64f13ae1f139dbaae8fed.png
[20190908205025772.png]: /images/20210813/b08563253cbf46fb8822d9c0b0ccb649.png
[20190908205426225.png]: /images/20210813/c03b5da16a2f4270ae91f4956b58ae83.png
[20190908205815368.png]: /images/20210813/164d637d6a604daf9c4eb8f85aa1c45c.png
[20190908205858622.png]: /images/20210813/ebda331888d94ce08c9a129b108f2845.png
[20190908215802345.png]: /images/20210813/e03fcd3872924d85a662bddf5d953195.png
[20190908220012308.png]: /images/20210813/acd8043e08ca445e90a65e2df7ed99f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/f198695f7b1549cabe4d869a16711279.png
[20190908221123239.png]: /images/20210813/8a3178cf5d4d425091134fd8ace30c17.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/c2b23ce4bf984046b4679f659e5b1aa4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/b81b90f617834780b734fdf9cd3976cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]: /images/20210813/ab6d4d6c9f2b42aaaeba719f781ff511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]: /images/20210813/2d1d3fc636ed434883ad7e1fbac79517.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]: /images/20210813/2556d0f2d29144b6a71f0c400c262f99.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]: /images/20210813/a2bcf3498d344855a73d17e63ef67054.png