sql cookbook实验记录（Second）

环境介绍：

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

目前可以下载到的最新版本啦！

按模式搜索：

for example: 比如我想返回10，20部门中的
ename中间包含I字符的，或者job以ER结尾的。

scott@ORCL>SELECT ename, job
  FROM emp
 WHERE deptno IN (10, 20) AND (ename LIKE '%I%' OR job LIKE '%ER')  2    3  
  4  /

ENAME	   JOB
---------- ---------
SMITH	   CLERK
JONES	   MANAGER
CLARK	   MANAGER
KING	   PRESIDENT
MILLER	   CLERK

请注意这里的小括号。如果不加的话，那么返回结果就不对了。

SELECT ename, job
  FROM emp
 WHERE deptno IN (10, 20) AND ename LIKE '%I%' OR job LIKE '%ER'

这里的通配符%是不限定位置的，但是如果想限定位置的话。就用下划线’_’

scott@ORCL>SELECT ename, job
  FROM emp
 WHERE ename LIKE '_A%'  

ENAME	   JOB
---------- ---------
WARD	   SALESMAN
MARTIN	   SALESMAN
JAMES	   CLERK

这里ename第二个字符是A的名字。

以上是查询部分想要说的。这部分结束。

排序部分

如下三个语句其实是等价的。

 select ename,job,sal from emp where deptno=10 order by sal asc;
 select ename,job,sal from emp where deptno=10 order by 3 asc;
 select ename,job,sal from emp where deptno=10 order by 3 ;

这里面有三个要说明的：
1 order by 默认升序 asc 可写，可忽略。
2 列明可以用位置指定
3 为了代码可读性，以上都不建议。

  select ename,job,sal from emp where deptno=10 order by 3  desc ;

降序的关键字desc。英文单词是Descending。
desc = Descending
asc = Ascending
这里普及下高中英语

不过这里插一句，group by不能用简写
select count(ename),deptno from emp group by deptno;

多字段排序

 select ename,job,sal,deptno from emp  order by deptno,sal desc   

当然也可以这样写，但是这不容易读懂。

 select ename,job,sal,deptno from emp  order by 4,3 desc   ; 

按子串排序：

 select ename,job from emp order by substr(job,length(job)-2)

这里引用下substr，几乎每个编程语言都提供很多字符串截取的方法。
不过很少有人会这么用罢了。

oracle的substr函数的用法 取得字符串中指定起始位置和长度的字符串 substr( string,
start_position, [ length ] )

这里造一个视图：

做一些截取字符串的操作：

SELECT REPLACE (TRANSLATE (data, '0123456789', '#########'), '#', '') FROM v

也可以在反向提取下：

SELECT REPLACE (
           data,
           REPLACE (TRANSLATE (data, '0123456789', '#########'), '#', ''),
           '')
  FROM v;

输出部分：

NULL排序
Oracle为null值排序开发了几个关键字

如果Oracle没有为我们开发这个函数，那就自己开发。
比如：

  SELECT *
    FROM (SELECT ename,
                 comm,
                 CASE WHEN comm IS NULL THEN 0 ELSE 1 END  AS comnstatus
            FROM emp) v
ORDER BY v.comnstatus

等于自己实现了nulls first

排序里面也可以搞逻辑判断：

  SELECT ename,
         sal,
         job,
         comm
    FROM emp
ORDER BY CASE WHEN job = 'SALESMAN'  THEN comm   ELSE sal  END

order by当然可以当成列值传进sql，重新排序

 SELECT ename,
         sal,
         job,
         comm,
         CASE WHEN job = 'SALESMAN'  THEN comm   ELSE sal  END as ordered
    FROM emp order by ordered 

查询结果排序的部分就结束了。