Oracle sql语句学习笔记

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数：
本文字数： 6.1k | 阅读时长 ≈ 6 分钟

Oracle sql语句学习笔记

本笔记记录本人学习过程中常用到的知识

1.分页查询

oracle的分页查询有多重书写方式（深入学习oracle语句请参考官方文档），不同的书写方式效率也不同,下面是一种效率较高的写法。/ 这个表示注释/

这个方法嵌套一层，速度快，当数据量大时，速度也很快。这种方法，在里层嵌套控制的查询的最大值，在最外层控制的最小值。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM 
    (
        SELECT 
            ROWNUM AS rowno, t.*
        FROM 
            SqlLearn /*(待查询的数据表）*/ t
        WHERE 
            Edition BETWEEN '2018'  AND '2022'
        AND 
            ROWNUM <= 15 /*（要查询的分页  本例从10到15）*/
    ) SqlLearn_page
 WHERE 
    SqlLearn_page.rowno >= 10;

也可以这样写：这时候在最外层控制最大值和最小值 ，相对来说第一种方法效率较高一点

1
2
3
4
5
6
7
8
9
10
SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM SqlLearn  WHERE 
            Edition BETWEEN '2018'  AND '2022') a)

 WHERE rn BETWEEN 10 AND 15

2.日期处理

TO_DATE格式(以时间:2019-05-23 17:45:25为例)

1.日期和字符转换函数用法（to_date,to_char）

1
2
3
4
5
6
7
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串  
select to_char(sysdate,'yyyy')  as nowYear   from dual;   //获取时间的年  
select to_char(sysdate,'mm')    as nowMonth  from dual;   //获取时间的月  
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日  
select to_char(sysdate,'hh24')  as nowHour   from dual;   //获取时间的时  
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分  
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

2.字符串和时间互转

1
2
select to_date('2019-05-23 17:45:25','yyyy-mm-dd hh24:mi:ss') from dual //显示23-MAY-19
select to_char( to_date(222,'J'),'Jsp') from dual //显示Two Hundred Twenty-Two

3.字符处理

1.字符截取 substr（”字符”，a，b）

只要 |a| ≤ b,取a的个数；当 |a| ≥ b时，才取b的个数，由a决定截取位置

1
2
3
4
5
6
7
8
select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
select substr('HelloWorld',5,3) value from dual; //返回结果：oWo
select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。）
select substr('HelloWorld',-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个 ）
select substr('HelloWorld',-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
select substr('HelloWorld',-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）

当只有两个参数时；不管是负几，都是从最后一个开始 往回截取

1
2
3
4
5
6
7
select substr('HelloWorld',0) value from dual;  //返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',1) value from dual;  //返回结果：HelloWorld，截取所有字符
select substr('HelloWorld',2) value from dual;  //返回结果：elloWorld，截取从“e”开始之后所有字符
select substr('HelloWorld',3) value from dual;  //返回结果：lloWorld，截取从“l”开始之后所有字符
select substr('HelloWorld',-1) value from dual;  //返回结果：d，从最后一个“d”开始 往回截取1个字符
select substr('HelloWorld',-2) value from dual;  //返回结果：ld，从最后一个“d”开始 往回截取2个字符
select substr('HelloWorld',-3) value from dual;  //返回结果：rld，从最后一个“d”开始 往回截取3个字符

4.常用函数

1.decode()

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
……
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF

实例：

select *from users;

img

select id, username, age, decode(sex,0,’男’,1,’女’) from users;

img

2.nvl() 为空值赋值

函数声明：nvl(col,val)

说明：当col为空时取val作为返回值，当col不为空时取col值。

用处：最主要的是格式化数据，比如计算金额时，不想出现空数据，可以使用nvl(JINE,0)来得到0。由于null+(或-,*,/）数字等于null，所以在表达式中对可能为空的值要使用nvl由于null!=null，有时对可能为空的列进行条件查询时，可能出现结果集丢失数据问题，加上nvl就不会了。

nvl2（）

Oracle在NVL函数的功能上扩展，提供了NVL2函数。

NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，否则返回E2。E2和E3类型不同的话，E3会转换为E2的类型。

限制：

​ 1) E1可以是任意类型，E2,E3不能是long类型。

​ 2) 如果 E2是字符类型，那么E3转为字符型再比较（null除外）。

​ 3) 如果 E2是数值类型，那么E3也转为对应的数值类型。

​ 4) 各个参数都不能是逻辑表达式

nullif（）

格式： nullif(expr1,expr2) 等价于 “case when expr1 = expr 2 then null else expr1 end”,相等返回NULL，不等返回expr1.
限制： expr1不能是标识符null，录入nullif(null,expr2)那么会提示错误。
expr1,expr2 都必须是一个变量或者是一个常量表达式，不能是逻辑表达式。

lnnvl(a)

a是一个表达式
lnnvl只能用于where子句中；表达式的操作符号不能包含 AND, OR, BETWEEN。
如果a的结果是false或者是unknown,那么lnnvl返回true;如果a的结果是true,返回false.
“如果a的结果是false或者是unknown,那么lnnvl返回true”,这个很重要，因为一个空值或者unknown的值和另外一个常量或者有值的变量比较的时候，返回的总是unknown,所以
lnnvl(a>10) 等价于 nvl(a,0)<=10 等价于 a<=10 or a is null （假设a number(10))
说白了，lnnvl是一种特定的用于简化表达式的函数，orcle解析的时候，应该会解析为 “a<=10 or a is null”.

3 case when

1. CASE WHEN 表达式有两种形式

1
2
3
4
5
6
7
8
9
10
11
12
13
--简单Case函数  

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END  

--Case搜索函数  

CASE
WHEN sex = '1' THEN '男'  
WHEN sex = '2' THEN '女'  
ELSE '其他' END

2. CASE WHEN 在语句中不同位置的用法

2.1 SELECT CASE WHEN 用法

1
2
3
4
5
6
7
SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/
                       ELSE NULL
                       END) 男生数,
                COUNT (CASE WHEN sex = 2 THEN 1
                       ELSE NULL
                       END) 女生数
    FROM students GROUP BY grade;

2.3 WHERE CASE WHEN 用法

1
2
3
4
5
6
7
8
9
10
SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
                   T1.SOME_TYPE LIKE 'NOTHING%'
                THEN 1
              WHEN T2.COMPARE_TYPE != 'A' AND
                   T1.SOME_TYPE NOT LIKE 'NOTHING%'
                THEN 1
              ELSE 0
           END) = 1

2.4 GROUP BY CASE WHEN 用法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END salary_class, -- 别名命名
COUNT(*)  
FROM    Table_A  
GROUP BY  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END;

3.1 在WHERE中特殊实现

1
2
3
4
5
SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
         OR
        (T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')

这种方法也是在特殊情况下使用，要多注意逻辑，不要弄错。

-------------本文结束感谢您的阅读-------------